Functional Dependency
UNF
Records [Customer_Name, Customer_Addr, Customer_Phone, Artist_ID, Arts_Title, Purchase_Date, Price]
1st Normal Form:
Criteria:
- Each cell to be Single Valued;
- Entries in a column are same type;
- Rows uniquely identified – Add Unique ID, or Add more columns to make unique;
- No Composite attribute;
- No multivalued attribute;
- No nested relations; attributes whose value for an individual tuple are non-atomic;
Records [Record_Id, Customer_Name, Customer_Addr, Customer_Phone, Artist_ID, Arts_Title, Purchase_Date, Price]
2nd Normal Form:
Criteria:
- All attributes (Non-Key Column) dependent on the key;
Customer [Customer_Id, Customer_Name, Customer_Addr, Customer_Phone]
Arts [Arts_Id, Artist_Name, Arts_Title]
Purchace : [Purchase_Id, Customer_Id, Arts_Id, Purchase_Date, Price]
3nd Normal Form:
Criteria:
- All fields (columns) can only be determined by the key in the table and no other column.
Customer [Customer_Id, Customer_Name, Customer_Addr, Customer_Phone]
Artist [Artist_Id, Artist_Name]
Arts [Arts_Id, Arts_Title, Arts_AuthorId]
Purchace [Purchase_Id, Customer_Id, Arts_Id, Purchase_Date, Price]
Answer from professor:
UNF:
customer [ custno, cust_name, cust_addr, cust_phone, ( artist_id, artist_name, art_title, pur_date, price) ]
1NF:
customer [ custno, cust_name, cust_addr, cust_phone]
cust_art [ custno, art_code, pur_date, artist_id, artist_name, art_title, price ]
2NF:
customer [ custno, cust_name, cust_addr, cust_phone]
cust_art [ custno, art_code, pur_date, price ]
art [ art_code, art_title, artist_id, artist_name ]
3NF:
customer [ custno, cust_name, cust_street, cust_city, cust_prov, cust_pstlcd, cust_phone]
cust_art [ custno, art_code, pur_date, price ]
art [ art_code, art_title, artist_id(FK) ]
artist [ artist_id, artist_fname, artist_lname ]
note: the key chosen for the repeating group is the piece of art itself (a code was assigned), however because a piece of art may be bought by a customer more than once, the purchase date was added as part of the key to make the rows unique.
BCNF:
http://www.cnblogs.com/ybwang/archive/2010/06/04/1751279.html