Question

Bundoora College has a small book store that opens for students, staff and general public. Currently, they use a manual order system. The manager asked you to build a database for them. You had an interview session with him and you get a general overview about the database requirements. The database should store:

• Orders information made by students, staff or general public. The order should have order number, the date and the time it is made.
• Customers details, so that the manager can notify the customers with the order status as well as send promotional material. The detail includes customer code, the full name, home address, contact phone number, and email address.
• List of the items in each order. The items can be books and/or stationeries. For each book, you should be able to include the details such as the title, the authors, the ISBN, and the price. For stationery, you should have the code, description, and price.
• If a book is required for a particular subject offered in Bundoora College, the database should store this data as well. The information includes the subject code, the subject name, the coordinator name, and the semester it is offered.

Create an Entity-Relationship Diagram (ERD) for the bookstore database. Make sure you include all required entities, relationships and cardinalities information.

Answer 1

Please find below the steps to create Entity Relationship Diagram for Bundoora College

Step 1: List entities and attributes:

The system will have below entities in it:

1. Order: This entity stores the details of the orders made by a customer

It has below attributes:

(Order_number, order_date, order_time)

2. Customer: This is the entity that stores the customer details who orders the items:

It has below attributes:

(Customer_code, full_name, home_address, contact_phone, email_address)

3. Item: This is the entity which stores the item details. And it has two types – Book and Stationary

It has below attributes:

(item_code, price, type)

4. Book: This is a subtype of Item with all attributes of Item entity along with below additional attributes:

(title, authors, ISBN)

5. Stationary: This is a subtype of Item with all attributes of Item entity along with below additional attributes:

(description)

6. Subject: This entity is used to store the subject that requires a book for it.

It has below attributes:

(subject_code, subject_name, coordinator_name, semester)

Step 2: List relationship among entities:

1. Order has one Customer and a Customer may have made none or many Orders. It is M:1 relationship.

2. Order has many Items and an Item can be order in none or many Orders. It is M:N relationship.

3. Book may be offered in none or one semester offered by college and a Subject can have none or many books to be used in it. It is M:1 relationship.

Step 3: Draw ERD:

Step 4: ERD Notations used: