school Bookstore

Using MySQLWorkbench, create a relational database data model for a school Bookstore project.

Create the necessary entities, attributes, and relationships.

Keep in mind the attributes on the following page may or may not include PKs and do not include FK attributes which will be needed to support relationships (referential integrity.)

Submit your completed .mwb file and a .jpg to the dropbox for the assignment.

school Bookstore Entities

The Book table records information about the books on sale in the bookstore. Books are uniquely identified by a 13-digit ISBN. Other information stored in the BOOK entity includes the author(s), title, genre, price, and description of the book. Each book is classified under a genre to enable topical searches.

A Genre is a category or distinctive style, such as Adventure, Music, Nature, etc. The Genre table stores the different types of genres and each is identified by a unique numerical ID and has a name and a description.

A Customer is a person that has access to the bookstores ordering system and purchases books from the online store. Customers are assigned unique identification numbers by the system. Other customer information includes: first and last name, address, city, state, zip code, phone number, email, login password, and their credit card type and number. The Customer table records information about customers of the application. Members will use their email address as their login to the system.

The Order table records information about orders placed by Customers. An order is identified by a unique order number and also has an order date and the customers ID. A unique order number is generated by the system for each order. Members may place many orders (we hope they do!). Several pieces of information are stored about each order. Since orders may contain one or more books and the details of the order are kept in the OrderDetails table.

The OrderDetail table holds one or more books that make up an order. Each OrderDetail instance is identified by the books ISBN and the order number with which it is associated. Additionally, each instance has the number of books ordered and the total price of those books. Each instance is uniquely identified by its order number and the books ISBN.

The Cart table contains ISBN and quantity of each book placed in the shopping cart of a member. Once a member checks out, the shopping cart is emptied, and an order is created. Each instance of a Cart is identified by a customers ID and the ISBN of the book

The CreditCardType is a lookup table to validate credit card types, i.e., AmericanExpress, VISA, MasterCard, etc. CreditCardType has two attributes, a credit card code (AMEX, DISC, VISA, MCRD) and a credit card description. The credit card code is stored for each customer in the Customer table.

school Bookstore Entities (Some only have Non-FK Attributes)

Book

ISBN

Author

Title

Price

Description

Order

OrderNbr

OrderDate

Cart

BookQty

CreditCardType

CreditCardCode

CreditCardDesc

Genre

GenreID

GenreName

GenreDesc

OrderDetail

BookQty

TotalPrice

Customer

UserID

FirstName

LastName

Address

City

State

ZipCode

PhoneNbr

Email

Password

CreditCardNbr

(show ERD data model and relationships: eg Non identifying, Identifying)