Question

1. Transform this ERD into relation. Explain each time what rule you use. This ERD shows the organization of data in a library system.

2. Transform the following ERDs into relations using the algorithm provided in the notes. Explain each time what rule you use.

Setution Pasiword Name Address uld erlyn Fine User O-n O-n Date Date Withdraw Hold N PubYear Tile Book -n -n 1-e Rfere CopyNumber Status I- BookCopy 0-1 bid 0-1 WritenBy M abbooks UName N 1-n 0-n Shelf PlacedOn Author FNomma ONE-TO-MANY RELATIONSHIP one-to-many relationship between an instructor and a student an instructor is associated with several (including 0) students via advisor a student is associated with at most one instructor via advisor, 1N student instructor 0-1 ID 1n ID advisor name name tot_cred salary MANY-TO-MANY RELATIONSHIP An instructor is associated with several (possibly 0) students via advisor o A student is associated with several (possibly 0) instructors via advisor NM student instructor ID advisor name name salary tot_cred

Answer 1

1. Relational Model

User(uId, Name, Password, Address, userType,fine) --- convert each entity to relation and attribute to columns, primary key is underlined .

Withdraw(uId, bId, Date) ----- convert Withdraw relationship to relation as it has one attribute Date and the composite primary key is primary keys of the two relations which are relation by this relationship which is also foreign key to both relations

Hold(uId, bId, Date) ---- convert Hold relationship to relation as it has one attribute Date and the composite primary key is primary keys of the two relations which are relation by this relationship which is also foreign key to both relations

BookCopy(bId, CopyNumber, Status,uId,Isbn) ---- usId and Isbn are foreign keys as each bId is related to 1 uId and 1 Isbn

Book(Isbn, Title, PubYear)

Author(aid,LName,FName)

WrittenBy(Isbn,aid) --- M to N relationship

Shelf(sid, nbbooks,bId)

underlined are primary keys and italicised are foreign keys.

2.

One -to-Many

instructor(ID, name, salary)

student(ID, name, tot_cred, instructor_ID)

Many-to-Many

instructor(ID, name, salary)

student(ID, name, tot_cred)

instructor_student( instructor_ID,student_ID)

underlined are primary keys and italicised are foreign keys.

Do ask if any doubt. Please upvote.