The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Schema Design Exercises

Next ►
►

In this section, we will provide practical exercises to help you apply the principles of schema design. These exercises will reinforce your understanding of creating efficient and effective database schemas. Each exercise will be followed by a detailed solution and explanation.

Exercise 1: Designing a Library Database Schema

Problem Statement

Design a database schema for a library system. The system should be able to store information about books, authors, and borrowers. The requirements are as follows:

  • Each book has a unique ISBN, title, publication year, and belongs to one or more genres.
  • Each author has a unique ID, name, and birthdate.
  • Each borrower has a unique ID, name, and contact information.
  • A book can have multiple authors, and an author can write multiple books.
  • A borrower can borrow multiple books, and each book can be borrowed by multiple borrowers over time.

Solution

Step 1: Identify Entities and Attributes

  • Books: ISBN, Title, Publication Year, Genre
  • Authors: Author ID, Name, Birthdate
  • Borrowers: Borrower ID, Name, Contact Information

Step 2: Define Relationships

  • Books-Authors: Many-to-Many
  • Books-Borrowers: Many-to-Many

Step 3: Create ER Diagram

+----------------+      +----------------+      +----------------+
|    Authors     |      |     Books      |      |   Borrowers    |
+----------------+      +----------------+      +----------------+
| AuthorID (PK)  |<---->| ISBN (PK)      |<---->| BorrowerID (PK)|
| Name           |      | Title          |      | Name           |
| Birthdate      |      | PublicationYear|      | ContactInfo    |
+----------------+      | Genre          |      +----------------+
                        +----------------+

Step 4: Create Junction Tables for Many-to-Many Relationships

  • BooksAuthors: ISBN, AuthorID
  • BooksBorrowers: ISBN, BorrowerID, BorrowDate, ReturnDate

Step 5: Define the Relational Schema

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    Name VARCHAR(100),
    Birthdate DATE
);

CREATE TABLE Books (
    ISBN VARCHAR(13) PRIMARY KEY,
    Title VARCHAR(255),
    PublicationYear INT,
    Genre VARCHAR(50)
);

CREATE TABLE Borrowers (
    BorrowerID INT PRIMARY KEY,
    Name VARCHAR(100),
    ContactInfo VARCHAR(255)
);

CREATE TABLE BooksAuthors (
    ISBN VARCHAR(13),
    AuthorID INT,
    PRIMARY KEY (ISBN, AuthorID),
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN),
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

CREATE TABLE BooksBorrowers (
    ISBN VARCHAR(13),
    BorrowerID INT,
    BorrowDate DATE,
    ReturnDate DATE,
    PRIMARY KEY (ISBN, BorrowerID, BorrowDate),
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN),
    FOREIGN KEY (BorrowerID) REFERENCES Borrowers(BorrowerID)
);

Explanation

  • Authors table stores information about authors.
  • Books table stores information about books.
  • Borrowers table stores information about borrowers.
  • BooksAuthors table handles the many-to-many relationship between books and authors.
  • BooksBorrowers table handles the many-to-many relationship between books and borrowers, including the borrowing and returning dates.

Exercise 2: Designing an E-commerce Database Schema

Problem Statement

Design a database schema for an e-commerce platform. The system should store information about products, customers, and orders. The requirements are as follows:

  • Each product has a unique ProductID, name, description, price, and stock quantity.
  • Each customer has a unique CustomerID, name, email, and address.
  • Each order has a unique OrderID, order date, and is associated with one customer.
  • Each order can contain multiple products, and each product can appear in multiple orders.

Solution

Step 1: Identify Entities and Attributes

  • Products: ProductID, Name, Description, Price, StockQuantity
  • Customers: CustomerID, Name, Email, Address
  • Orders: OrderID, OrderDate, CustomerID

Step 2: Define Relationships

  • Orders-Customers: Many-to-One
  • Orders-Products: Many-to-Many

Step 3: Create ER Diagram

+----------------+      +----------------+      +----------------+
|   Customers    |      |     Orders     |      |    Products    |
+----------------+      +----------------+      +----------------+
| CustomerID (PK)|<---->| OrderID (PK)   |<---->| ProductID (PK) |
| Name           |      | OrderDate      |      | Name           |
| Email          |      | CustomerID (FK)|      | Description    |
| Address        |      +----------------+      | Price          |
+----------------+                           | StockQuantity |
                                              +----------------+

Step 4: Create Junction Table for Many-to-Many Relationship

  • OrderDetails: OrderID, ProductID, Quantity

Step 5: Define the Relational Schema

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100),
    Address VARCHAR(255)
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100),
    Description TEXT,
    Price DECIMAL(10, 2),
    StockQuantity INT
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Explanation

  • Customers table stores information about customers.
  • Products table stores information about products.
  • Orders table stores information about orders and links to customers.
  • OrderDetails table handles the many-to-many relationship between orders and products, including the quantity of each product in an order.

Conclusion

In this section, we have covered two practical exercises on schema design. These exercises help you understand how to identify entities, define relationships, and create relational schemas for different scenarios. By practicing these exercises, you will gain a better understanding of how to design efficient and effective database schemas. In the next section, we will focus on normalization exercises to further enhance your database design skills.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved