1.
Suppos Supposee a data databas basee has has the the follo followin wing g table table for suppli suppliers ers,, order orders, s, item itemss order ordered ed and and part partss combi combine ned d where (OrderNo, ItemNo, PartName) is the primary key and this is the complete table.
a. Give Give an exam exampl plee of of an an insertion anomaly for Suppliers. Answer
Can’t insert Supplier information without having the Supplier on an order.
b. Using row 1, give an example example of a deletion anomaly. anomaly. Answer
If order 100 is deleted, all information about Supplier 112 is lost and visa versa.
c.
Using ing ro rows 2 and 3, 3, gi give an an ex example of of an an update anomaly. anomaly.
Answer
If the name of a Supplier is changed, it must be changed in both places. or If the order date is changed, it must be changed in all three places.
2.
Fill in the blank: a. Dependencie Dependenciess based on only a part part of a composite composite primary key key are called ____par ____partial__ tial___ _ dependencie dependencies. s.
b. Design relation schemas so that they can be joined with equality conditions on attributes that are either primary keys or foreign keys keys in a way which guarantees guarantees that no ____spurious____ ____spurious____ tuples are generated. generated. c. ____ X → Y _____ Y _____ means that whenever two tuples in R agree on all the attributes in X , they must also agree on all attributes of Y of Y , where X where X and and Y are Y are sets of attributes in a relation R relation R..
3.
Cons Consid ideer the the follo ollowi win ng relat elatio ion n R(A, R(A, B, C, D, E)
a.
Is R relation in 1NF? Why
and FDs = {C → E, B → D}
Yes Why?
b.
All attributes are simple and single
Is R relation in 2NF?
No Why?
4.
Because of B D
partial dependency
Consider re relation R with five attributes ABCDE, and the following functional dependencies: A → B
BC → D
D → E
Give the complete closure for AC AC , i.e., find ( AC )+. ABCDE
5.
Cons Consid ideer a relat elatio ion n R(A R(A,, B, B, C, C, D) with with FD’s D’s AB AB → C, C → D, and D → A. List the two keys for R. R.
{A, B}+ = {A, B, C, D} {B, C}+ = {B, C, D, A}
{B, D}+ = {B, C, D, A}
6. For relation R relation R(( A, A, B, B, C ), ), suppose AB suppose AB → C and C and C → B. B. List all normal forms (if any) that R that R is in. You only need to consider 3NF, 4NF, and BCNF. 3NF
7. We perform perform decomposi decomposition tion to normaliz normalizee an original original schema schema to be of certain certain normal normal forms. forms. For such a decomposition to be “equivalent” to the original schema, it is desirable to be lossless. lossless. To study this concept, let’s consider an original schema R schema R(( A, A, B, B, C ). ). Suppose we decompose R decompose R into R1( R1( A, A, B) and R2( A, A, C ). ). a. Is this decomposition always lossless? Answer yes yes or no or no and briefly explain why.No. Unless A Unless A → B or A A →
b.
C is C is satisfied in R in R,, R1 R1 ∞ R2 R2 may generate entries that do not belong to R to R..
Give an example instance of R R (i.e., an example table with 2 tuples) and demonstrate its decomposition, to support your answer in (a). R1
8.
A
B
C
A
1
2
3
1
1
4
5
1
R1
R2
B
∞
R2
A
C
A
B
C
2
1
3
1
2
3
4
1
5
1
2
5
1
4
3
1
4
5
Consid Consider er a rela relatio tion n R with with five five attrib attribute utess ABC ABCDE. DE. The follow following ing depend dependenc encies ies are given: given: A → B The keys for R R are
a.
R1
BC → E
ED → A.
CDE
ACD
BCD
Is R in 3NF? Briefly explain why. YES, it is in 3NF because B, E, and A all parts of keys
b.
Is R Is R in BCNF? If yes, please explain why. Otherwise, decompose R decompose R into relations that are in BCNF.
No. For example, BC → E violates BCNF. If we decompose using this FD (BC → E), we get BCE, and ABCD. Further decomposing ABCD using A
→
Therefore, it could be decomposed into
B we get AB, ACD. BCE,
AB,
ACD.
9. Consider the relation R(A, B, C) with wi th MVD C ->> B and C ->> A. R currently has the following tuples: C
a.
A
1 a x 1 a y 1 b x 1 b y 2 c x 2 c z 2 d x 2 d z The The abo above ve rela relati tion on is not not in in 4NF 4NF.. Nor Norma mali lize ze it into into 4NF. 4NF. R1(C, B)
b.
B
and
R2(C, A)
What is the table look like after after decomposition? C 1 1 2 2
B a b c d
C 1 1 2 2
A
x y x z
