Relational Database Schema for a Library Database: PUBLISHER({PBID, PB_NAME, PB_
ID: 3839060 • Letter: R
Question
Relational Database Schema for a Library Database: PUBLISHER({PBID, PB_NAME, PB_STREET, PB_PHONE, PB_ZIPCODE, PB_CITY, PB_STATE}; {PBID rightarrow PB_NAME, PB_STREET, PB_ZIPCODE, PB_PHONE; PB_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 NO_OF_COPIES; LB_ZIPCODE rightarrow LB_CITY, LB_STATE; LBID rightarrow LB_NAME, LB_STREET, LB_ZIPCODE; BKID rightarrow BK_TITLE:} Book_AUTHORS({BKID, BK_TITLE, BK_LIST_PRICE, PBID, AUID, AU_NAME, AU_DOB, POSITION}, {BKID rightarrow BK_TITLE, BK_LIST_PRICE, PBID; AUID rightarrow AU_NAME, AU_DOB; BKID, AUID rightarrow POSITION}) 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)
PUBLISHER(PBID,PB_NAME,PB_STREET,PB_PHONE,PB_ZIPCODE,PB_CITY,PB_STATE)
FUNCTIONAL DEPENDENCIES:
PBID->PBNAME,PB_STREET,PB_ZIPCODE,PB_PHONE
PB_ZIPCODE->PB_CITY,PB_STATE
Rightside columns:
{PB_CITY,PB_STATE,PBNAME,PB_STREET,PB_ZIPCODE,PB_PHONE}
columns that are not in right side prodcutions:
{PBID}
(PBID)+={PBNAME,PB_STREET,PB_ZIPCODE,PB_PHONE,PB_CITY,PB_STATE}
So PBID Is A Key
There is not partial dependancy in this relation
There is a transitive dependancy in
PB_ZIPCODE->PB_CITY,PB_STATE (no-key to no-key)
PB_ZIPCODE->PB_CITY (no-key to no-key)
PB_ZIPCODE->PB_STATE (no-key to no-key)
1)highest nf it holds is 2NF
2)Given relation is alredy in 2NF
3)Given relation is alredy in 2NF
4)Given relation is not in 3NF
this is because
PB_ZIPCODE->PB_CITY (no-key to no-key)
PB_ZIPCODE->PB_STATE (no-key to no-key)
Now
(PB_ZIPCODE)+={PB_ZIPCODE,PB_CITY,PB_STATE}
Therefore
PUBLISHER(PBID,PB_NAME,PB_STREET,PB_PHONE,PB_ZIPCODE,PB_CITY,PB_STATE) is decomposed into
R1:(PBID,PB_NAME,PB_STREET,PB_PHONE,PB_ZIPCODE)
FD:
PBID->PBNAME,PB_STREET,PB_ZIPCODE,PB_PHONE
R2:(PB_ZIPCODE,PB_CITY,PB_STATE)
FD:
PB_ZIPCODE->PB_CITY ,PB_STATE
PB_ZIPCODE IN COMMON Therefor it is lossless decomposition
and it is also functionally preserved since we can derive all fd
BOOK_COPIES(LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE,BKID,BK_TITLE,NO_OF_COPIES)
FD:
BKID,LBID->NO_OF_COPIES
LB_ZIPCODE->LB_CITY,LB_STATE
LBID->LB_NAME,LB_STREET,LB_ZIPCODE
BKID->BK_TITLE
Right handed attributes :
{NO_OF_COPIES,LB_CITY,LB_STATE,LB_NAME,LB_STREET,LB_ZIPCODE,BK_TITLE}
attributes that are not in Right handed side:
(LBID,BKID)
(LBID)+={LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE}
BKID,NO_OF_COPIES are not in (LBID)+ therefore it is not a key
(BKID)+={BK_TITLE} it is also not a key
(LBID,BKID)+=(LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE,BKID,BK_TITLE,NO_OF_COPIES)
all attributes are present so LBID,BKID is a key
FD:
BKID,LBID->NO_OF_COPIES
LB_ZIPCODE->LB_CITY,LB_STATE
LBID->LB_NAME,LB_STREET,LB_ZIPCODE (LBID is in (LBID,BKID) Therefore Partial dependency )
BKID->BK_TITLE (BKID is in (LBID,BKID) Therefore Partial dependency )
Therefore it is not in 2nf
1)Heighest normal form it holds is : 1NF
2)Parital dependancy fd's in
FD:
BKID,LBID->NO_OF_COPIES
LB_ZIPCODE->LB_CITY,LB_STATE
LBID->LB_NAME,LB_STREET,LB_ZIPCODE (LBID is in (LBID,BKID) Therefore Partial dependency )
BKID->BK_TITLE (BKID is in (LBID,BKID) Therefore Partial dependency )
Therefore it is not in 2nf are
BKID->BK_TITLE (BKID is in (LBID,BKID) Therefore Partial dependency )
(BKID)+ = (BKID,BK_TITLE)
LB_ZIPCODE->LB_CITY,LB_STATE (LBID is in (LBID,BKID) Therefore Partial dependency )
(LBID)+= (LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE)
THEREFORE
BOOK_COPIES(LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE,BKID,BK_TITLE,NO_OF_COPIES) decomposed into
R1:(LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE)
FD:
LB_ZIPCODE->LB_CITY,LB_STATE
LBID->LB_NAME,LB_STREET,LB_ZIPCODE
R2:(BKID,BK_TITLE)
FD:
BKID->BK_TITLE
R3:(LBID,BKID,No_of_copes)
FD:
BKID,LBID->NO_OF_COPIES
3)
R1,R2,R3 are loseless decomposition
becuse R1,R3 has LBID as common and LBID is key for R1
R1,R2 has BKID as common and BKID is key for R2
4)
if we observe
R1:(LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE)
FD:
LB_ZIPCODE->LB_CITY,LB_STATE
LBID->LB_NAME,LB_STREET,LB_ZIPCODE
here LBID is key
LB_ZIPCODE is not a key
LB_ZIPCODE->LB_CITY,LB_STATE (Transitive dependancy non key-non key)
LBID->LB_NAME,LB_STREET,LB_ZIPCODE
(LB_ZIPCODE)+=(LB_ZIPCODE,LB_CITY,LB_STATE)
R1:(LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE) DECOMPOSED INTO
R11:(LBID,LB_NAME,LB_STREET,LB_ZIPCODE)
FDS:
LBID->LB_NAME,LB_STREET,LB_ZIPCODE
R12:(LB_ZIPCODE,LB_CITY,LB_STATE)
fDS:
LB_ZIPCODE->LB_CITY,LB_STATE
BOOK_AUTHORS(BKID,BK_TITLE,BK_LIST_PRICE,PBID,AUID,AU_NAME,AU_DOB,POSITION)
FD:
BKID->BK_TITLE,BK_LIST_PRICE,PBID
AUID->AU_NAME,AU_DOB
BK_ID,AUID->POSITION
Right hand attributes: {BK_TITLE,BK_LIST_PRICE,PBID,AU_NAME,AU_DOB,POSITION}
attributes not in RHS side: {BKID,AUID}
(BKID)+=(BK_TITLE,BK_LIST_PRICE,PBID) Not key
(AUID)+=(AU_NAME,AU_DOB) not key
(BKID,AUID)+=(BKID,BK_TITLE,BK_LIST_PRICE,PBID,AUID,AU_NAME,AU_DOB,POSITION)
Therefore (BKID,AUID) is key
FD:
BKID->BK_TITLE,BK_LIST_PRICE,PBID (partial dependency BKID is part of key)
AUID->AU_NAME,AU_DOB (partial dependency AUID is part of key)
BK_ID,AUID->POSITION
There fore it is not in 2nf
1)Heighest nomral form it satisifie is 1NF
2)
BKID->BK_TITLE,BK_LIST_PRICE,PBID (partial dependency BKID is part of key)
AUID->AU_NAME,AU_DOB (partial dependency AUID is part of key)
BK_ID,AUID->POSITION
(BKID)+=(BK_TITLE,BK_LIST_PRICE,PBID)
(AUID)+=(AU_NAME,AU_DOB)
There fore
BOOK_AUTHORS(BKID,BK_TITLE,BK_LIST_PRICE,PBID,AUID,AU_NAME,AU_DOB,POSITION) decomose into
R1:(BK_ID,BK_TITLE,BK_LIST_PRICE,PBID)
FD:
BKID->BK_TITLE,BK_LIST_PRICE,PBID
R2:(AUID,AU_NAME,AU_DOB)
FD:
AUID->AU_NAME,AU_DOB
R3:(BK_ID,AUID,POSITION)
FD:
BK_ID,AUID->POSITION
3)R1,R2,R3 all are loselless deomposition of BOOK_AUTHORS
BKID is in common for R1,R3 and key for R1
AUID is in common for R2,R3 and key for R2
4)
R1,R2,R3 all are in 3NF also therfore we dont need further decoposition to achieve 3NF
A)
PUBLISHER(PBID,PB_NAME,PB_STREET,PB_PHONE,PB_ZIPCODE,PB_CITY,PB_STATE)
FUNCTIONAL DEPENDENCIES:
PBID->PBNAME,PB_STREET,PB_ZIPCODE,PB_PHONE
PB_ZIPCODE->PB_CITY,PB_STATE
Rightside columns:
{PB_CITY,PB_STATE,PBNAME,PB_STREET,PB_ZIPCODE,PB_PHONE}
columns that are not in right side prodcutions:
{PBID}
(PBID)+={PBNAME,PB_STREET,PB_ZIPCODE,PB_PHONE,PB_CITY,PB_STATE}
So PBID Is A Key
There is not partial dependancy in this relation
There is a transitive dependancy in
PB_ZIPCODE->PB_CITY,PB_STATE (no-key to no-key)
PB_ZIPCODE->PB_CITY (no-key to no-key)
PB_ZIPCODE->PB_STATE (no-key to no-key)
1)highest nf it holds is 2NF
2)Given relation is alredy in 2NF
3)Given relation is alredy in 2NF
4)Given relation is not in 3NF
this is because
PB_ZIPCODE->PB_CITY (no-key to no-key)
PB_ZIPCODE->PB_STATE (no-key to no-key)
Now
(PB_ZIPCODE)+={PB_ZIPCODE,PB_CITY,PB_STATE}
Therefore
PUBLISHER(PBID,PB_NAME,PB_STREET,PB_PHONE,PB_ZIPCODE,PB_CITY,PB_STATE) is decomposed into
R1:(PBID,PB_NAME,PB_STREET,PB_PHONE,PB_ZIPCODE)
FD:
PBID->PBNAME,PB_STREET,PB_ZIPCODE,PB_PHONE
R2:(PB_ZIPCODE,PB_CITY,PB_STATE)
FD:
PB_ZIPCODE->PB_CITY ,PB_STATE
PB_ZIPCODE IN COMMON Therefor it is lossless decomposition
and it is also functionally preserved since we can derive all fd
BOOK_COPIES(LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE,BKID,BK_TITLE,NO_OF_COPIES)
FD:
BKID,LBID->NO_OF_COPIES
LB_ZIPCODE->LB_CITY,LB_STATE
LBID->LB_NAME,LB_STREET,LB_ZIPCODE
BKID->BK_TITLE
Right handed attributes :
{NO_OF_COPIES,LB_CITY,LB_STATE,LB_NAME,LB_STREET,LB_ZIPCODE,BK_TITLE}
attributes that are not in Right handed side:
(LBID,BKID)
(LBID)+={LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE}
BKID,NO_OF_COPIES are not in (LBID)+ therefore it is not a key
(BKID)+={BK_TITLE} it is also not a key
(LBID,BKID)+=(LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE,BKID,BK_TITLE,NO_OF_COPIES)
all attributes are present so LBID,BKID is a key
FD:
BKID,LBID->NO_OF_COPIES
LB_ZIPCODE->LB_CITY,LB_STATE
LBID->LB_NAME,LB_STREET,LB_ZIPCODE (LBID is in (LBID,BKID) Therefore Partial dependency )
BKID->BK_TITLE (BKID is in (LBID,BKID) Therefore Partial dependency )
Therefore it is not in 2nf
1)Heighest normal form it holds is : 1NF
2)Parital dependancy fd's in
FD:
BKID,LBID->NO_OF_COPIES
LB_ZIPCODE->LB_CITY,LB_STATE
LBID->LB_NAME,LB_STREET,LB_ZIPCODE (LBID is in (LBID,BKID) Therefore Partial dependency )
BKID->BK_TITLE (BKID is in (LBID,BKID) Therefore Partial dependency )
Therefore it is not in 2nf are
BKID->BK_TITLE (BKID is in (LBID,BKID) Therefore Partial dependency )
(BKID)+ = (BKID,BK_TITLE)
LB_ZIPCODE->LB_CITY,LB_STATE (LBID is in (LBID,BKID) Therefore Partial dependency )
(LBID)+= (LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE)
THEREFORE
BOOK_COPIES(LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE,BKID,BK_TITLE,NO_OF_COPIES) decomposed into
R1:(LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE)
FD:
LB_ZIPCODE->LB_CITY,LB_STATE
LBID->LB_NAME,LB_STREET,LB_ZIPCODE
R2:(BKID,BK_TITLE)
FD:
BKID->BK_TITLE
R3:(LBID,BKID,No_of_copes)
FD:
BKID,LBID->NO_OF_COPIES
3)
R1,R2,R3 are loseless decomposition
becuse R1,R3 has LBID as common and LBID is key for R1
R1,R2 has BKID as common and BKID is key for R2
4)
if we observe
R1:(LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE)
FD:
LB_ZIPCODE->LB_CITY,LB_STATE
LBID->LB_NAME,LB_STREET,LB_ZIPCODE
here LBID is key
LB_ZIPCODE is not a key
LB_ZIPCODE->LB_CITY,LB_STATE (Transitive dependancy non key-non key)
LBID->LB_NAME,LB_STREET,LB_ZIPCODE
(LB_ZIPCODE)+=(LB_ZIPCODE,LB_CITY,LB_STATE)
R1:(LBID,LB_NAME,LB_STREET,LB_ZIPCODE,LB_CITY,LB_STATE) DECOMPOSED INTO
R11:(LBID,LB_NAME,LB_STREET,LB_ZIPCODE)
FDS:
LBID->LB_NAME,LB_STREET,LB_ZIPCODE
R12:(LB_ZIPCODE,LB_CITY,LB_STATE)
fDS:
LB_ZIPCODE->LB_CITY,LB_STATE
BOOK_AUTHORS(BKID,BK_TITLE,BK_LIST_PRICE,PBID,AUID,AU_NAME,AU_DOB,POSITION)
FD:
BKID->BK_TITLE,BK_LIST_PRICE,PBID
AUID->AU_NAME,AU_DOB
BK_ID,AUID->POSITION
Right hand attributes: {BK_TITLE,BK_LIST_PRICE,PBID,AU_NAME,AU_DOB,POSITION}
attributes not in RHS side: {BKID,AUID}
(BKID)+=(BK_TITLE,BK_LIST_PRICE,PBID) Not key
(AUID)+=(AU_NAME,AU_DOB) not key
(BKID,AUID)+=(BKID,BK_TITLE,BK_LIST_PRICE,PBID,AUID,AU_NAME,AU_DOB,POSITION)
Therefore (BKID,AUID) is key
FD:
BKID->BK_TITLE,BK_LIST_PRICE,PBID (partial dependency BKID is part of key)
AUID->AU_NAME,AU_DOB (partial dependency AUID is part of key)
BK_ID,AUID->POSITION
There fore it is not in 2nf
1)Heighest nomral form it satisifie is 1NF
2)
BKID->BK_TITLE,BK_LIST_PRICE,PBID (partial dependency BKID is part of key)
AUID->AU_NAME,AU_DOB (partial dependency AUID is part of key)
BK_ID,AUID->POSITION
(BKID)+=(BK_TITLE,BK_LIST_PRICE,PBID)
(AUID)+=(AU_NAME,AU_DOB)
There fore
BOOK_AUTHORS(BKID,BK_TITLE,BK_LIST_PRICE,PBID,AUID,AU_NAME,AU_DOB,POSITION) decomose into
R1:(BK_ID,BK_TITLE,BK_LIST_PRICE,PBID)
FD:
BKID->BK_TITLE,BK_LIST_PRICE,PBID
R2:(AUID,AU_NAME,AU_DOB)
FD:
AUID->AU_NAME,AU_DOB
R3:(BK_ID,AUID,POSITION)
FD:
BK_ID,AUID->POSITION
3)R1,R2,R3 all are loselless deomposition of BOOK_AUTHORS
BKID is in common for R1,R3 and key for R1
AUID is in common for R2,R3 and key for R2
4)
R1,R2,R3 all are in 3NF also therfore we dont need further decoposition to achieve 3NF