Question

design a relational database to cater for the needs of the new bookshop owner as described in the first question giving examples of tables, attributes, primary and foreign keys. you may assume that suppliers' orders contain more than one line

Answer 1

A relational database for catering the needs of a bookshop owner contains the following tables:

1. Book (for storage of list of books)

2. Publisher (for list of publishers)

3. Book_price (to store price of books)

4. Supplier (To store supplier information)

5. Supplier_order (To store supplier's order information)

6. Supplier_order_details (To store order details. Since one supplier can have >1 orders)

Now, let us look at what all attributes are associated with each entity:

1. Book:

- Book_ID [Primary Key]

- Publisher_ID [Foreign Key]

- Code

- Name

- Author

- Year

- Stock

2. Publisher:

- Publisher_ID [Primary key]

- Name

- Date

3. Book_price:

- ID [Primary Key]

- Book_ID [Foreign Key]

- Price

- Discount

4. Supplier:

- Supplier_ID [Primary Key]

- First_Name

- Last_Name

- Email

- Address

- Phone

5. Supplier_order:

- ID [Primary Key]

- Supplier_ID [Foreign Key]

- Price

- Date

6. Supplier_order_details:

- ID [Foreign Key]

- Supplier_order_ID [Primary Key]

- Book_ID [Foreign Key]

- Quantity

This is the basic and required information. You can add more attributes if required. Please feel free to reach out in the comment box in case you have any doubts. Hope it helps. Happy learning!!