Introduction

Normal From是数据库理论里面最恶心的问题，从1NF到6NF，加上BCNF，DKNF以及UF。不像编程作业，有运行结果作参考，做这种理论题简直是受罪，每一题都得反复推理。

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?