Henry's Bookstore Database

Henry's Bookstore is a small company with four locations in the city. Henry has struggled with MS Excel mainly because he was trying to use spreadsheet software as a database, when in fact he should have been using a database.

Requirements

• Create all of the entities needed for the Physical ERD
• Specify the columns/attributes needed in each entity along with the data type and size of each attribute.
• Specify the Primary Key(s) and Foreign Key(s) for the attributes within the entities.
• Draw the relationships among the entities. Specify the cardinality of the relationship. (One to One, One to Many, etc.)

1. Using the online Lucidchart software, design the database based on the entities that Henry has provided you. You should create a Physical Entity Relationship Diagram (ERD) that will capture how these entities will be related to each other. This means that your Entity box will have three columns, one for the Key, the Attribute Name, and the Datatype/Size. The entities are shown below.

Author

• Author_Num decimal(5,0)
• Author_Last char(30)
• Author_First char(40)

Book

• Book_Code char(5)
• Title char(75)
• Publisher_Code char(40)
• Type char(20)
• Price decimal(6,0)
• Paperback char(1)

Branch

• Branch_Num decimal(4,0)
• Branch_Name char(20)
• Branch_Location char(30)
• Num_Employees decimal(4,0)

Inventory

• Book_Code char(5)
• Branch_Num decimal(4,0)
• On_Hand decimal(5,0)

Publisher

• Publisher_Code char(40)
• Publisher_Name char(40)
• City char(40)

Wrote

• Book_Code char(5)
• Author_num decimal(5,0)
• Sequence decimal(2,0)

2. Authors write books and many authors can write one book. (this is why we have the Wrote Entity)

3. A specific book title is provided by only one publisher.

4. Copies of the same book can exist at more than one location for Henry's Bookstore.