Relational database schema for a library Database: Publisher((PBID, PB_NAME, PB_

Question

Relational database schema for a library Database: Publisher((PBID, PB_NAME, PB_STREET, PB_PHONE, PH_ZIPCODE, PB_CITY, PB_STATE): (PBID rightarrow PB_NAME, PB_STREET, PH_ZIPCODE, rightarrow PB_CITY, PB_STATE): BOOK_COPIES((LBID, LB_NAME, LB_STREET, LB_ZIPCODE, LB_CITY, LB_STATE, BKID, BK_TITLE, NO_OF_COPIES). (BKID, LBID rightarrow LB_NAME, LB_STREET, LB_ZIPCODE: BKID rightarrow BK_TITLE:) Assuming that each of the Relations above is in 1NF: determine the highest NF for each Relation Convert each Relation that is not in 2NF to Relations that are each in 2NF. Do a Non-Loss (Lossless) Decomposition of each Relation that is not in 2NF. Convert each Relation that is not in 3NF to Relations that are each in 3NF.

Explanation / Answer

A relation is said to be in 2NF if partial dependencies are not there in the relation , partial dependencies means the dependency between a part of key to a non key attributes.

A relation is said to be in 3NF if transitive dependencies are not there in the relation,transitive dependencies means the dependency between a non key to a non key attributes.

Answers :-

1)

For the relation Publisher, PBID is the key and we get the same by finding closure set of attributes method

on finding closure set of attribute to a particular attribute all other other attributes in the table should be derived

then that attribute is the key to the table.

PBID+ = PB_ID,PB_NAME,PB_ZIPCODE,PB_STREET,PB_PHONE (from the dependency

PBID----> PB_NAME,PB_ZIPCODE,PB_STREET,PB_PHONE )

= PB_ID,PB_NAME,PB_ZIPCODE,PB_STREET,PB_PHONE,PB_CITY,PB_STATE (from the dependency

PB_ZIPCODE----> PB_CITY,PB_STATE )

Here Dependency 1 is in 2NF,3NF as it is from key to nonkeys and Dependency 2 is in 2 NF but not in 3NF as it is from non key to non key.

Hence the highest normal form accepted by the relation Publisher is 2 NF.

For the relation Book_Copies, BKID,LBID together is the key and we get the same by finding closure set of

attributes method on finding closure set of attribute to a particular attribute all other other attributes in the table

should be derived then that attribute is the key to the table.

BKID,LBID+ = BKID,LBID,NO_OF_COPIES (from the dependency

BKID,LBID----> NO_OF_COPIES )

= BKID,LBID,NO_OF_COPIES,BK_TITLE,LB_NAME,LB_STREET,LB_ZIPCODE (from the dependencies

BKID----> BK_TITLE, LBID ---> LB_NAME,LB_STREET,LB_ZIPCODE )

= BKID,LBID,NO_OF_COPIES,BK_TITLE,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY, LB_STATE (from the dependency LB_ZIPCODE---->LB_CITY,LB_STATE)

Here if we see BKID----> BK_TITLE this is partial dependency as it is from part of key to non key.

Hence the highest normal form accepted by the relation Publisher is 1 NF.

For the relation BOOK_Authors, BKID,AUID together is the key and we get the same by finding closure set of

attributes method on finding closure set of attribute to a particular attribute all other other attributes in the table

should be derived then that attribute is the key to the table.

BKID,AUID+ = BKID,AUID,POSITION (from the dependency BKID,AUID ----> POSITION )

= BKID,AUID,POSITION,BK_TITLE,BK_LIST,PRICE,PBID,AU_NAME,AU_DOB (from the dependencies

BKID---->BK_TITLE,BK_LIST,PRICE,PBID and AUID -----> AU_NAME,AU_DOB )

Here if we see AUID----> AU_NAME,AU_DOB this is partial dependency as it is from part of key to non key.

Hence the highest normal form accepted by the relation Publisher is 1 NF.

2)

Here the relations Book_Copies and BOOK_Authors are not in 2NF , to convert these relations in to 2 NF we

need to eliminate partial dependencies and this can be done by removing the partial dependent attributes from the

main table and placing those attributes in a separate table

For the relation Book_Copies :-

  BKID----> BK_TITLE and LBID ---> LB_NAME,LB_STREET,LB_ZIPCODE these dependencies are not in 2 NF

Find BKID+ and LBID+ here

  BKID+ = BKID,BK_TITLE (so make a table BKID,BK_TITLE with BKID as key)

  LBID+ = LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE

  (so make a table LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE with LBID as key)

Finally we left with only BKID,LBID,NO_OF_COPIES make these 3 as one talbe with BKID,LBID as key

After Converting to 2 NF, tables are :-

1) BKID,BK_TITLE

2) LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE

3) BKID,LBID,NO_OF_COPIES

For the relation BOOK_Authors :-

BKID----> BK_TITLE,BK_LIST,PRICE,PBID and AUID ---> AU_NAME,AU_DOB these dependencies are not in 2 NF

Find BKID+ and AUID+ here

  BKID+ = BKID,BK_TITLE,BK_LIST,PRICE,PBID

(so make a table BKID,BK_TITLE,BK_LIST,PRICE,PBID with BKID as key)

AUID+ = AUID,AU_NAME,AU_DOB (so make a table AUID,AU_NAME,AU_DOB with AUID as key)

Finally we left with only BKID,AUID,POSITION make these 3 as one talbe with BKID,AUID as key

After Converting to 2 NF, tables are :-

1) BKID,BK_TITLE,BK_LIST,PRICE,PBID

2) AUID,AU_NAME,AU_DOB

3) BKID,AUID,POSITION

3)

This is already answered in answer 2 After converting loss less decomposition we get tables as mentioned

For the relation Book_Copies :-

1) BKID,BK_TITLE

2) LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE

3) BKID,LBID,NO_OF_COPIES

For the relation BOOK_Authors :-

1) BKID,BK_TITLE,BK_LIST,PRICE,PBID

2) AUID,AU_NAME,AU_DOB

3) BKID,AUID,POSITION

4)

If we see the relations Book_Copies, BOOK_Authors these tables are already in 3 NF as there in no transitive dependncy but consider the table Publisher here PB_ZIPCODE----> PB_CITY,PB_STATE is a transitive dependncy hence we need to eliminate it

PB_ZIPCODE+ = PB_ZIPCODE,PB_CITY,PB_STATE

(so make a table PB_ZIPCODE,PB_CITY,PB_STATE with PB_ZIPCODE as key)

we left with the attributes PB_ID,PB_NAME,PB_ZIPCODE,PB_STREET,PB_PHONE hence we will make this a table with PB_ID as key

After Converting to 3 NF, tables are :-

1) PB_ZIPCODE,PB_CITY,PB_STATE

2) PB_ID,PB_NAME,PB_ZIPCODE,PB_STREET,PB_PHONE

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---