Consider a simplified relational schema for a Library (consisting of three relat
ID: 3605729 • Letter: C
Question
Consider a simplified relational schema for a Library (consisting of three relations): .Shelf (shelfld, shelfName) .Book (bookld, bookName, shelfld) Staff (staffld, staffName, shelfld) The following conditions are satisfied: .Every shelf has exactly one unique id and one unique name. .Every book has a unique id and one name, but different books may have the same name. .Every staff has an id and a name, and no staffs in the library have the same id or name. A shelf can store many books, but each book is only placed on one shelf. staff is responsible to one shelf, and one shelf is maintained by one or more staffs. A Assignments: 1. (18pts) What are the super keys for each of the relations Shelf, Book, and Staff? 2. (10pts) What are the candidate keys for each of the relations Shelf, Book, and Staff? 3. (8pts) What is the primary key for each of the relations Shelf, Book, and Staff? 4. (4pts) What are the foreign keys for the relations Shelf, Book, and Staff?Explanation / Answer
SHELF
SUPER KEYS- CANDID KEYS PRIMARY KEY FOREIGN KEY
1.SHELF ID, 1.SHELF ID ONE OF CANDID NO FOREIGN KEY
2.SHELF NAME 2.SHELF NAME KEYS
3.SHELF ID,SHELF NAME
BOOK
1.BOOKID 1.BOOKID SHELF ID
BOOKID,BOOKNAME 2.SHELF ID
BOOKID,SHLEF ID
BOOKID,BOOKNAME,SHELF ID
STAFF
STAFFID STAFF ID SHELF ID
STAFFNAME STAFF NAME
STAFFIF,STAFFNAME
SHELF ID,STAFFID
SHELF ID,STAFFNAME
SHELF ID,STAFFID,STAFFNAME