Introduction

In this case study, we will explore the design and implementation of a relational database for a fictional company, "TechStore," which sells electronic gadgets. This case study will cover the following aspects:

Requirements Gathering
Entity-Relationship (ER) Diagram
Transformation to Relational Schema
SQL Implementation
Normalization

Requirements Gathering

Before designing the database, we need to gather the requirements. Here are the key requirements for TechStore:

Products: The company sells various products, each with a unique ID, name, description, price, and stock quantity.
Customers: Customers have a unique ID, name, email, and phone number.
Orders: Each order has a unique ID, date, customer ID, and a list of products with quantities.
Order Details: Each order can contain multiple products, and we need to track the quantity of each product in the order.

Entity-Relationship (ER) Diagram

Based on the requirements, we can create an ER diagram to visualize the relationships between different entities.

ER Diagram

+-----------------+          +-----------------+          +-----------------+
|    Customer     |          |     Product     |          |      Order      |
|-----------------|          |-----------------|          |-----------------|
| CustomerID (PK) |<-------->| ProductID (PK)  |<-------->| OrderID (PK)    |
| Name            |          | Name            |          | Date            |
| Email           |          | Description     |          | CustomerID (FK) |
| Phone           |          | Price           |          +-----------------+
+-----------------+          | StockQuantity   |
                             +-----------------+
                                   ^
                                   |
                                   |
                             +-----------------+
                             |  OrderDetails   |
                             |-----------------|
                             | OrderID (FK)    |
                             | ProductID (FK)  |
                             | Quantity        |
                             +-----------------+

Transformation to Relational Schema

Next, we transform the ER diagram into a relational schema. Here are the tables and their attributes:

Tables

Customer
- CustomerID (Primary Key)
- Name
- Email
- Phone
Product
- ProductID (Primary Key)
- Name
- Description
- Price
- StockQuantity
Order
- OrderID (Primary Key)
- Date
- CustomerID (Foreign Key)
OrderDetails
- OrderID (Composite Key, Foreign Key)
- ProductID (Composite Key, Foreign Key)
- Quantity

SQL Implementation

Let's implement the relational schema using SQL.

SQL Code

-- Create Customer table
CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100),
    Phone VARCHAR(15)
);

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

-- Create Order table
CREATE TABLE "Order" (
    OrderID INT PRIMARY KEY,
    Date DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

-- Create OrderDetails table
CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES "Order"(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

Explanation

Customer Table: Stores customer information.
Product Table: Stores product information.
Order Table: Stores order information, including the customer who placed the order.
OrderDetails Table: Stores details of each order, including the products and their quantities.

Normalization

Normalization ensures that the database is free from redundancy and dependency anomalies. Let's ensure our tables are normalized.

Normal Forms

First Normal Form (1NF): Each table has a primary key, and each column contains atomic values.
Second Normal Form (2NF): All non-key attributes are fully functional dependent on the primary key.
Third Normal Form (3NF): There are no transitive dependencies.

Normalization Process

Our tables are already in 1NF, 2NF, and 3NF because:

Each table has a primary key.
Non-key attributes depend only on the primary key.
There are no transitive dependencies.

Conclusion

In this case study, we covered the design and implementation of a relational database for TechStore. We started with requirements gathering, created an ER diagram, transformed it into a relational schema, implemented it using SQL, and ensured normalization. This comprehensive approach ensures that the database is well-structured, efficient, and scalable.

This case study prepares you for real-world scenarios where you need to design and implement relational databases, ensuring data integrity and efficiency.

Case Study: Relational Database

Introduction

Requirements Gathering

Entity-Relationship (ER) Diagram

ER Diagram

Transformation to Relational Schema

Tables

SQL Implementation

SQL Code

Explanation

Normalization

Normal Forms

Normalization Process

Conclusion

Fundamentals of Databases

Module 1: Introduction to Databases

Module 2: Relational Databases

Module 3: Non-Relational Databases

Module 4: Schema Design

Module 5: Normalization

Module 6: Practical Exercises

Module 7: Case Studies

Module 8: Additional Resources