第一、二、三范式的应用

第一、二、三范式的应用

• Question1、Assume a relational schemas R(A,B,C,D,E)，FDs set of it is F={AB，BC，CDE, E->A} :
  （1）Give the results of A+，ED+
  （2）find the candidate keyword of R and write a simple process；
  （3）determine which Norm Form R belongs to and explain the reason.
• Answer1 ：
  (1)A+=ABC; ED+=ABCDE;
  (2) Candidate key: AD,BD，CD，ED; because AD+=ABCDE, ED+=ABCDE;。。。
  (3)3NF;因为不存在非主属性，所以也不存在非主属性对主键的部分依赖和传递依赖，所以3nF。
• Question2、Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies F = { AB→C, A→DE, B→F, F→GH, D→IJ }. What is the key for R? Decompose R into 2NF and then 3NF relations.
• Answer2 ：
  (1) AB is the key for R. Because AB+=ABCDEFGHIJ; R属于1NF，因存在非主属性D和E都部分依赖于主键AB
  (2) ABC,ADEIJ,BFGH:2NF
  ABC, ADE,DIJ, BF,FGH ：3NF
• Question3、Consider the following relation for published books:
  BOOK (Book_title , Author_name , Book_type , List_price , Author_affil ,
  Publisher )
  Author_affil refers to the affiliation of author. Suppose the following depen-dencies exist:
  Book_title → Publisher , Book_type
  Book_type → List_price
  Author_name → Author_affil
  a.What normal form is the relation in? Explain your answer.
  (Book_title , Author_name) is the candidate key of the relation. Book_title → Publisher, so it is in 1NF.
  b. Apply normalization until you cannot decompose the relations further.State the reasons
  behind each decomposition.
  R1: Book_title , Author_name
  R2: Book_title → Publisher , Book_type
  R3: Book_type → List_price
  R4: Author_name → Author_affil
• Answer3：R1,R2,R3,R4 are all in 3NF.