Question

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. The entities are shown below.

Key	Book Entity	Data Type	Size
	BOOK_CODE	Integer	5
	TITLE	Character	75
	PUBLISHER_CODE	Integer	5
	TYPE	Character	20
	PRICE	Decimal	5.2
	PAPERBACK	Character	1

Key	Author Entity	Data Type	Size
	AUTHOR_NUM	Integer	5
	AUTHOR_LAST	Character	30
	AUTHOR_FIRST	Character	40

Key	Branch Entity	Data Type	Size
	BRANCH_NUM	Integer	4
	BRANCH_NAME	Character	20
	BRANCH_LOCATION	Character	30
	NUM_EMPLOYEES	Integer	4

Key	Inventory Entity	Data Type	Size
	BOOK_CODE	Integer	5
	BRANCH_NUM	Integer	4
	ON_HAND	Integer	5

Key	Publisher Entity	Data Type	Size
	PUBLISHER_CODE	Integer	5
	PUBLISHER_NAME	Character	40
	CITY	Character	40

Key	Wrote Entity	Data Type	Size
	BOOK_CODE	Integer	5
	AUTHOR_NUM	Integer	5
	SEQUENCE	Integer	2

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.

Answer 1

Branch Num

Branch Name

Branch Location

Num of Employees

Book Code

Title

Publisher Code

Author Num

Publisher Name

Author Last

Author First

City

Sequence

paper Back

Price

On hand