Data Modelling Challenge

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?