Relational Database Course Project

For the remainder of the course, 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 store and retrieve information about its business transactions.

To fulfill that obligation, you will work through a number of steps: analyzing the company's business structure, identifying its needs, and determining the best way to organize and store its data to ensure efficient retrieval.

This course includes a number of hands-on exercises that let you complete these steps incrementally, applying what you learn in each lesson to a real-world situation. These exercises culminate in the entity-relationship (ER) diagram shown below, representing the logical design that falls out of the conceptual analysis you will work through first. The diagram below shows the design you will be building toward.

It is worth looking at this finished diagram now, before you have built any part of it yourself, for the same reason it helps to see a finished puzzle before dumping out the pieces: it gives you a picture of where each lesson's individual concept, an entity here, a foreign key there, a normalization decision somewhere else, is ultimately headed. Nothing about the diagram below should make complete sense yet, and that is expected. By the time you reach the end of this course, every piece of it will.

The database schema shown above contains five tables.

1. CUSTOMER
   • Primary key: CustID
   • Columns: CustID, CustLast, CustFirst, CustCity, CustState, CustZip, CustPhone
   • Purpose: stores customer-related details.
2. ORDER
   • Primary key: OrderNo
   • Foreign key: CustID (references CUSTOMER)
   • Columns: OrderNo, CustID, OrderDate
   • Purpose: stores details of orders placed by customers.
3. DISTRIBUTOR
   • Primary key: DistID
   • Columns: DistID, DistName, DistStreet, DistCity, DistState, DistZip, DistPhone
   • Purpose: stores information about distributors.
4. CD
   • Primary key: CDNo
   • Foreign key: DistID (references DISTRIBUTOR)
   • Columns: CDNo, CDTitle, DistID, RetailPrice, AgeGroup, Description
   • Purpose: stores information about each CD, including its distributor.
5. LINE ITEM
   • Composite primary key: OrderNo + CDNo
   • Foreign keys: OrderNo (references ORDER), CDNo (references CD)
   • Columns: OrderNo, CDNo, Quantity, SellingPrice, LineCost, Shipped
   • Purpose: a junction table storing the details of each CD within each order.

• CUSTOMER to ORDER: one-to-many, linked by CustID. One customer can place many orders.
• ORDER to LINE ITEM: one-to-many, linked by OrderNo. One order can contain many line items.
• DISTRIBUTOR to CD: one-to-many, linked by DistID. One distributor supplies many CDs.
• CD to LINE ITEM: one-to-many, linked by CDNo. One CD can appear as a line item on many different orders.

This database system is designed to manage a CD sales business. It tracks customers and their orders, CDs and their distributors, and the specific line items within each order, including quantity and cost. The LINE ITEM table serves as the bridge between ORDER and CD, enabling detailed order management; it is a classic example of the junction, or bridge, table used to implement a many-to-many relationship, since a single order can include many different CDs and a single CD can appear on many different orders.

A few details in this diagram are worth flagging now, since they connect directly back to the concepts introduced earlier in this module:

• Every table has a primary key, and every relationship is implemented with a foreign key. CUSTOMER's CustID, ORDER's OrderNo, DISTRIBUTOR's DistID, and CD's CDNo each uniquely identify a row in their own table; the foreign keys (CustID on ORDER, DistID on CD) are what actually connect the tables together, exactly the key concepts covered earlier in this module.
• LINE ITEM has no single-column primary key of its own. Instead, it uses a composite primary key made up of OrderNo and CDNo together. Neither column alone uniquely identifies a line item, since a given order can have many line items and a given CD can appear on many orders, but the combination of the two always does.
• Every relationship in this diagram is one-to-many, even the many-to-many one. A single order genuinely can include many different CDs, and a single CD genuinely can appear on many different orders, which on its face sounds like a many-to-many relationship. LINE ITEM is what resolves that: it turns one many-to-many relationship into two one-to-many relationships, ORDER to LINE ITEM and CD to LINE ITEM, which is exactly the kind of junction-table design this course will have you build yourself.

This concludes the first module of the course. In the next module, the relational database model itself is discussed: 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, the same vocabulary you will need to describe the Stories on CD, Inc. design in your own words as you work through the rest of this 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