Scenario

You are designing a database for an online bookstore. The system should handle customers, their orders, and the books being sold. A customer can place multiple orders, and each order can contain multiple books.

Tasks:

1. Identify the main entities in this system.

2. Describe the possible relationships between these entities (e.g., one-to-many, many-to-many).

3. For each entity, design a simple data structure with at least 3 attributes and their respective type and constraints.

Deliverables:

• A list of entities and their relationships.
• A table structure for each entities.

Solution

1. The information system has 3 entities: Customer, Order and Book.

2. A customer can place multiple orders but each orders are associated with one and only one customer (one-to-many). An order can contain multiple books and a book can appear in different orders (many-to-many).

3. Thereafter is a list of at least 3 attributes for each entities with their type and constraints (physical data model):

Customer:

attribute	data_type	constraints
customer_id	INT	PRIMARY_KEY, AUTO_INCR
name	VARCHAR(100)	NOT NULL, UNIQUE
email	VARCHAR(255)	NOT NULL, UNIQUE
phone_number	VARCHAR(15)	NULLABLE, UNIQUE
address	TEXT	NULLABLE
last_order_date	DATETIME	NULLABLE

Order:

attribute	data_type	constraints
order_id	INT	PRIMARY_KEY, NOT NULL, AUTO_INCR
customer_id	INT	FOREIGN_KEY
order_date	DATETIME	NOT NULL
total_amount	DECIMAL(10, 2)	NOT NULL

Book:

attribute	data_type	constraints
book_id	INT	PRIMARY_KEY, NOT NULL, AUTO_INCR
title	VARCHAR(100)	NOT NULL
price	DECIMAL(10, 2)	NOT NULL
description	TEXT	NULLABLE
publishing_date	DATETIME	NULLABLE
stock_quantity	INT	NOT NULL

Having created the table for those 3 entities, it appears that a fourth entity is required to make the connection between an order and the book(s) it contains:

OrderDetails:

attribute	data_type	constraints
order_id	INT	FOREIGN_KEY, NOT NULL
book_id	INT	FOREIGN_KEY, NOT NULL
quantity	INT	NOT NULL
unit_price	DECIMAL(10, 2)	NOT NULL

Acknowledgement

This scenario was designed using the following CHatGPT prompts (GPT-4o model):

1. what is data modelling?

2. can you recap all this information into tables?

3. can you imagine a small exercise for me to check that I master the presented information?