Given the unnormalized table below, answer the following questions step-by-step
ID: 3717939 • Letter: G
Question
Given the unnormalized table below, answer the following questions step-by-step in the given order. Note that capital letters (A, B, …) represent attribute names and lower-case letters their values.
A
B
C
D
E
F
a
b
c
h
d
i
e
j
f
k
l
m
h
d
e
f
n
o
c
r
i
s
j
e
k
f
a) Bring the table into the first normal form (1NF).
b) Now, identify all functional dependencies on the table which is in 1NF and determine all the candidate keys.
c) Bring the table into the second normal form (2NF).
d) Bring the table(s) into the third normal form (3NF).
e) Then, determine all primary and foreign keys for each table obtained in part (d).
f) Finally, draw the complete ER-diagram
A
B
C
D
E
F
a
b
c
h
d
i
e
j
f
k
l
m
h
d
e
f
n
o
c
r
i
s
j
e
k
f
Explanation / Answer
A
B
C
D
E
F
a
b
c
h
d
i
e
j
f
k
l
m
h
d
e
f
n
o
c
r
i
s
j
e
k
f
a)
Table contains multivalued attributes.
So let’s make this into 1NF.
A
B
C
D
E
F
a
b
c
d
e
f
a
b
h
i
j
k
l
m
h
d
e
f
n
o
c
i
j
k
n
o
r
s
e
f
No multivalued dependencies. So it is in 1NF
b)
Let’s find out functional dependencies:
Need to identify attributes based on following conditions:
Assume t1, t2 are two different rows of table.
If t1.attribute=t1.attribute then t2.attribute = t2.attribute
R(ABCDEF)
A->B
B->A
E->F
AC->BD
AD->BC
AE->BCD
AEF->BCD
CD->BE
CANDIDATE KEYS ARE minimal number of attributes through which we can determine all attributes.
These are:
AE
ACE
BE
ADE
AEF
ACD
C)
All are prime attributes, so table is in 2NF
D)
All are prime attributes, so table is in 3NF
E)
Among the available candidate keys we can make anyone of them as primary key.
F)
Only one table in the system. Further decomposition of tables is not required.
A
B
C
D
E
F
a
b
c
h
d
i
e
j
f
k
l
m
h
d
e
f
n
o
c
r
i
s
j
e
k
f
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.