Database代做编程辅导：ECS165A Normal Form/BCNF/3NF/4NF

全文链接：tecdat.cn/?p=29690

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?

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.

Summary

最后还是想说一下，这样的多个解的作业真的相当恶心，做的时候需要反复推导，查阅各种资料来证明，而且第二个问的chase test完全就是slides里面的方法，学习理解也花了不少时间。