Given the following schema of a relation R(A,B,C,D,E) with the following functio
ID: 3831975 • Letter: G
Question
Given the following schema of a relation R(A,B,C,D,E) with the following functional dependencies: AB C, C D, D B, D E, and the following data:
A
B
C
D
E
1
1
2
3
2
1
2
3
1
2
1
3
5
2
5
2
1
2
3
2
2
2
3
1
2
2
3
7
2
5
3
1
1
3
2
3
2
4
1
2
3
3
5
2
5
(a) Decompose R into tables in BCNF.
(b) Project the data of the original table into the new tables.
(c) Join the data of the new tables to obtain one table and compare it to the original data.
A
B
C
D
E
1
1
2
3
2
1
2
3
1
2
1
3
5
2
5
2
1
2
3
2
2
2
3
1
2
2
3
7
2
5
3
1
1
3
2
3
2
4
1
2
3
3
5
2
5
Explanation / Answer
a)
1NF – Table is having atomic values so it is already in INF
2NF – As AB is a composite key and there is no other non key attribute which is determined by part of composite key. So there is no partial dependency. So relation is in 2NF.
3NF -
Consider AB-> C, C->D and C D, D B, D E
So there are transitive dependencies,
So we need to break tables into
R1(CD) {C->D}
R2(ABC) {AB->C}
R3(DBE) {D->B, D->E}
Where underlined attributes are primary keys.
BCNF – There is no non-prime attribute on left side. So it is in BCNF also.
So after breaking R is decomposed into R1(CD), R2(ABC) and R3(DBE)
b)
Data of R1: -
C
D
2
3
3
1
5
2
7
2
1
3
4
1
Data of R2: -
A
B
C
1
1
2
1
2
3
1
3
5
2
1
2
2
2
3
2
3
7
3
1
1
3
2
4
3
3
5
Data of R3: -
D
B
E
3
1
2
1
2
2
2
3
5
c)
R2 JOIN R1 ON C
A
B
C
D
1
1
2
3
1
2
3
1
1
3
5
2
2
1
2
3
2
2
3
1
2
3
7
2
3
1
1
3
3
2
4
1
3
3
5
2
We name this table R4
Now R4 JOIN R3 on D
A
B
C
D
E
1
1
2
3
2
1
2
3
1
2
1
3
5
2
5
2
1
2
3
2
2
2
3
1
2
2
3
7
2
5
3
1
1
3
2
3
2
4
1
2
3
3
5
2
5
Comparing with the original data, it looks same, so it is lossless decomposition.
C
D
2
3
3
1
5
2
7
2
1
3
4
1
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.