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

results matching ""

    No results matching ""