Objectives

This lab will put into practice the table mapping process.

Relational Database Concepts

Examine the sample data for these tables in the DJs on Demand database. Check for entity, referential, and column integrity. Identify any data-integrity violations. Assume that all date columns should have date format and all amount (cost) columns should have a number format.

Basic Mapping

Transform the following entities into table definitions, using suitable naming conventions:

Remember we are creating definitions of tables, the physical implementation will be done later. What foreign key relationship do you think exists?

Match the ERD elements to their corresponding database elements.

Identify what is wrong with the following table names:

• 2016class

• ictskills_class_2016_relational_databases

• ictskills class list

Relationship mapping

Transform relationships as foreign key columns

For the following ERD create table mappings for shift, shift assignment, order, order line, frequent diner card, and food item. Ignore all other entities for the moment.

In your table mapping identify the table name, column name, data type, primary key, foreign key, and any other constraints you think are appropriate.

Super and Subtype mapping

Transform the STAFF supertype in the following example, using the subtype or two table implementation.

Make sure you have a foreign key in each staff type to represent the recursive relationship between manager and staff.