Question

Draw an ER diagram with these attributes ( ER diagram for SQL for a library database)

DRAW ER DIAGRAM AS ONE FULL LIBRARY SYSTEM, NOT DIFFERENT FOR EACH ATTRIBUTE.

Attributes :

          Customer

• Cust_ID: key identifier, required, simple, single valued

• Cust_Name{ first name, last name}: Key Identifier, simple;composite, multivaried

•   Address{street, city,zip,state}: Customer address, required, composite, single can be derived from zip

• (placeholder, there should be another attribute here to represent the books taken out by the customer. Not sure.)

          Inventory

• Book_ID: Key identifier, required, simple single valued

• Book_Name: Key identifier, required, simple, single-valued

• Genre: required: simple; single valued

• Publication_Date: required; simple; single valued

         

         Transaction

•    Book_ID: Key identifier, required, simple, single valued

• Rental_Date: required, simple, single valued

• Rental cost: required, simple, single valued

• Rental_Date: required, simple, single valued

Answer 1

ER diagram for the given table attributes:

Notes: We have three tables Customer, Inventory and Transaction - We converted them to Entities in ER diagram. The attributes of table become attributes of Entities. Note that while a table may have multiple key Attributes, each entity will have a single primary key which is represented as underlined. As the placeholder attribute for Customer table was supposed to represent Books rented, we assume this was Book_ID as foreign key (FK). This way, we create a 1:1 Relationship between Customer and Inventory. Note that Transaction has just Book_ID (which is FK from Inventory) as the only key attribute - This means Transaction is a weak entity dependent on Inventory in a dependent Relationship. Note that this Relationship is assumed to be 1:N - A book may have multiple transaction (at different dates) but a transaction belong to a single book. Note that Transaction also has two redundent attributes of same name Rental Date - We changed one to Rental Date 2