Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

ECS165A Normal Form/BCNF/3NF/4NF

Requirement

1) For this problem you will consider following relation and set of FDs:

R(A,B,C,D,E)

AB → E

B → C

CE → A

AE → D

a) What are the keys of R?

b) Show that the set of FDs is a minimal basis; if it is not a minimal basis, find a minimal basis for the FDs.

c) What are all of the BCNF violations that follow from the FDs? (Don’t just consider the listed FDs, but all that follow).

d) Decompose R into BCNF. For each relation, list the keys, and make sure the set of FDs for each is a minimal basis.

e) What are all of the 3NF violations that follow from the FDs? (Don’t just consider the listed FDs, but all that follow).

f) Decompose R into 3NF. For each relation, list the keys, and make sure the set of FDs for each is a minimal basis.

g) What are all of the 4NF MVD violations that follow from the FDs?

h) Decompose R into 4NF. For each relation, list the keys, FDs and MVD.

i) If you project the relation R onto S(A,B,C) what nontrivial FDs and MVDs hold in S?

2) For this problem you will consider the following relations and FDs:

Courses(C,T,H,R,S,G)

C → T

HR → C

HT → R

HS → R

CS → G

Where C, T, H, R, S, and G are the course, teacher, hour, room, student, and grade respectively. With a proposed decomposition of:

R1(C, T, H)

R2(C, H, R, G)

R3(C, R, S)

a) Use the chase test to prove/disprove that holds HS → G in Courses.

b) Use the chase test to prove/disprove that holds THR → S in Courses.

c) Use the chase test to prove/disprove the proposed decomposition has a lossless join.

d) Decompose Courses into 3NF.

e) Which of your relations in 3NF are not in BCNF?

f) Which of your relations in 3NF are not in 4NF?

3) Design an academic genealogy database with one entity set: Academics.

Consider academics with doctoral degrees only. The information to record about academics includes their name, discipline, year of graduation, institution, dissertation title, dissertation chair, dissertation committee members (assume two others distinct from chair), and academic progeny.