Home‎ > ‎CIS 54‎ > ‎

Lab 3 - Keys

I this lab you'll build a schema that uses constraints to guarantee referential integrity. The lab begins on page 110 of the book. 

Introduction

Referential integrity constraints are essential in a high quality database. Referential integrity constraints apply to a table. They tell the database that attributes in the table they apply to must match data that is in another table. The attributes must be keys in the foreign table, but not in the local table. Without constraints errors will creep in over time, eventually making the database useless. In this lab you will create the database for the Wedgewood Pacific Corporation from the book. 

Part 1: Create a Schema

Begin by creating a new schema:

    youname_lab3

You will create tables in your schema with referential integrity constraints and enter data into those tables. When you're done you will export your schema and submit it on Canvas.

A Note on Table Notation

The labs show you how to create a table using a common notation. For example when you see a table written like this:

    DEPARTMENT ( DepartmentName, BudgetCode, OfficeNumber, Phone ) 

That tells you the table looks like this:

 DepartmentName BudgetCode OfficeNumber  Phone
    
    

The underlined text tells you what attribute or attributes form the key. This is important because when you setup a foreign key constraint you must have attributes in both tables that match the key.

Before You Begin

The exercise in chapter 2 assumes that you have already created the EMPLOYEE table. Since you did not do that in the last assignment you must create the table before you begin. Create the EMPLOYEE as described in figure 1-32 on page 58. Then insert the data in figure 1-33 also on page 58. 

Exercise

The lab in the book assumes you're using Microsoft Access, which is very similar to MySQL Workbench, but not the same. Ignore any parts that ask you to create a form. The forms are used to enter data, instead use MySQL Workbench's table editor to enter data.

Do parts "A" through "I" of the "Access Workbench Exercise" starting on page 110.

When you're done export your database to a file called lab3.sql

Adding Data

You can add the data by typing the values into MySQL Workbench. But, that's a lot of typing. The following file contains INSERT statements that add the data for you. You can load it into MySQL Workbench and execute it using the "lightning bolt" icon. 

 
Turn In
  1. Your lab3.sql file. 
  2. The answers to the questions in G.
Submit your homework on canvas.

Grading

  • 15 points for a correctly formed database
  • 5 points for the answers to the questions
ċ
DBC-e07-MySQL-WPC-Insert-Data.sql
(4k)
Michael Matera,
Feb 9, 2017, 8:13 AM
Comments