Database Design  «Prev  Next»

Lesson 4 Relational Database Course Project
Objective Elements of relational database course project

Relational Database Course Project

For the remainder of the course, I want you to imagine that you have been hired by a company called Stories on CD, Inc. to consult on the design of a relational database that will enable the company to
  1. store and
  2. retrieve
information about its business transactions.
To fulfill your obligation to the company, you need to accomplish a number of steps involving
  1. analyzing their business structure,
  2. their needs,
  3. and the best way to organize and store the data to ensure its most efficient retrieval.

This course includes a number of hands-on exercises that enable you to complete these steps incrementally, applying what you learn in the lessons to a real-world situation.
These exercises culminate in the completion of an entity-relationship (ER) diagram and completing the conceptual phase of database design. The image below to shows the diagram that will be created.

Relational Database Design

Stories on CD

ER diagram consisting of CUSTOMER, ORDER, LINEITEM, DISTRIBUTOR, and CD tables.
The database schema in the image titled "Stories on CD, Inc." contains five tables. Below is a detailed description of each table:
  1. CUSTOMER Table
    • Primary Key (PK): CustID
    • Columns:
      • CustID (Unique customer identifier)
      • CustLast (Customer's last name)
      • CustFirst (Customer's first name)
      • CustCity (Customer's city)
      • CustState (Customer's state)
      • CustZip (Customer's ZIP code)
      • CustPhone (Customer's phone number)
    • Purpose: This table stores customer-related details.
  2. ORDER Table
    • Primary Key (PK): OrderNo
    • Foreign Key (FK): CustID (References CUSTOMER table)
    • Columns:
      • OrderNo (Unique order number)
      • CustID (Link to the CUSTOMER table)
      • OrderDate (Date the order was placed)
    • Purpose: This table stores details of orders placed by customers.
  3. DISTRIBUTOR Table
    • Primary Key (PK): DistID
    • Columns:
      • DistID (Unique distributor identifier)
      • DistName (Distributor's name)
      • DistStreet (Distributor's street address)
      • DistCity (Distributor's city)
      • DistState (Distributor's state)
      • DistZip (Distributor's ZIP code)
      • DistPhone (Distributor's phone number)
    • Purpose: This table contains information about distributors.
  4. CD Table
    • Primary Key (PK): CDNo
    • Foreign Key (FK): DistID (References DISTRIBUTOR table)
    • Columns:
      • CDNo (Unique CD identifier)
      • CDTitle (Title of the CD)
      • DistID (Link to the DISTRIBUTOR table)
      • RetailPrice (Retail price of the CD)
      • AgeGroup (Target age group for the CD)
      • Description (Description of the CD)
    • Purpose: This table stores information about CDs, including their distributor.
  5. LINE ITEM Table
    • Composite Primary Key (CPK): OrderNo, CDNo
    • Foreign Keys (FK):
      • OrderNo (References ORDER table)
      • CDNo (References CD table)
    • Columns:
      • OrderNo (Link to the ORDER table)
      • CDNo (Link to the CD table)
      • Quantity (Number of CDs ordered)
      • SellingPrice (Selling price for the order)
      • LineCost (Total cost for the line item)
      • Shipped (Status of shipment)
    • Purpose: This table is a junction table that stores the details of CDs in each order.

Relationships Between Tables:
  1. CUSTOMER ↔ ORDER
    • CustID links CUSTOMER and ORDER tables (one-to-many relationship).
  2. ORDER ↔ LINE ITEM
    • OrderNo links ORDER and LINE ITEM tables (one-to-many relationship).
  3. CD ↔ LINE ITEM
    • CDNo links CD and LINE ITEM tables (one-to-many relationship).
  4. CD ↔ DISTRIBUTOR
    • DistID links CD and DISTRIBUTOR tables (many-to-one relationship).

Summary: This database system is designed to manage a CD sales business. It tracks:
  • Customers and their orders,
  • CDs and their distributors,
  • Specific line items within each order, including quantity and cost.

The LINE ITEM table serves as the bridge between ORDERS and CDs, enabling detailed order management.

This concludes the first module of the course. In the next module I will discuss the relational database model and how to describe the structure of a relational database. This material may be a review for many of you and is necessary to establish common terminology.

RelationalDB Course Project

Before moving on to the next module, click the RelationalDB Course Project link below to download the case study on which the course project is based.
RelationalDB Course Project

SEMrush Software