Relational Constructs   «Prev  Next»

Translate Entities and Attributes into Tables - Exercise

Translate entities and attributes

Objective: Translate entities and attributes into tables with column headings.

Exercise scoring

This exercise is worth a total of two points and is auto-scored. After you finish your work, click Submit to receive credit and open the results page.

Background and overview

In this exercise, you will practice a core database-design skill: converting an ER diagram into a set of relational tables. You will work from the course case study, Stories on CD, which tracks customers, orders, products, distributors, and the line items that connect orders to products.

Instructions

Review the ER diagram below. Then, create a table design for each of the following entities: Customer, Order, CD, Distributor, and Line Item.

For each table, write:
  1. Table name
  2. Column headings (one column per attribute)
  3. Primary key column(s)
  4. Foreign key column(s) needed to represent the relationships shown in the diagram

Keep your answer at the “design level.” You are not writing SQL in this exercise—just clear table definitions with columns.
Stories on CD ER diagram
CUSTOMER 
* CustID (PK)
* CustLast
* CustFirst
* CustCity
* CustZip
* CustPhone

ORDER 
* OrderNo (PK)
* CustID (FK)
* OrderDate

LINE ITEM 
* OrderNo (CPK, FK)
* CDNo (CPK, FK)
* Quantity
* SellingPrice
* LineCost
* Shipped

CD 
* CDNo (PK)
* CDTitle
* DistID (FK)
* RetailPrice
* AgeGroup
* Description

DISTRIBUTOR 
* DistID (PK)
* DistName
* DistStreet
* DistCity
* DistState
* DistZip
* DistPhone

Stories on CD ER diagram showing five entities: CUSTOMER, ORDER, LINE ITEM, CD, and DISTRIBUTOR. Each entity lists its attributes, and PK/FK labels indicate primary and foreign keys. Relationship lines show how CUSTOMER connects to ORDER, and how LINE ITEM bridges ORDER and CD.

Hints

  • A primary key (PK) uniquely identifies each row in a table.
  • A foreign key (FK) is a column that stores a primary key value from a related table.
  • In many designs, Line Item is used to model an order containing multiple products.

Submitting your exercise

This exercise is auto-scored. Your submission is used to confirm completion. When you are ready, click Submit to open the results page.