CS360: Lab 6

---

Due: See Moodle for the due date.

Goals:

• Practice creating tables from an Entity Relationship Diagram
• Learn another dialect of ER diagram notation.

Requirements:

1. Read the excerpt from C. J. Date’s database book stored near the top of the CS360 moodle home page.
2. Transform the ER Diagram shown as figure 13.2 in this reading into tables.
3. Create a MYSQL script in a text file named lab6.sql that will execute and create these tables with the necessary keys and constraints.

NOTES:

· You may NOT disable foreign key constraints in your script

· Your script must first drop tables if they exist then create the necessary tables.

· You must include engine=InnoDB at the end of each Create table statement.

4. Submit your lab6.sql file on moodle by clicking on the Lab 6 assignment. 

Hints:

· The reading shows definitions for a few of the required tables.

· The opening paragraphs make reference to several things that are not included in this reading.  You do not need them.

· Think of a relvar as the definition of a relation and a relvalue as a particular set of data that might be in this relation at any given time.

· Here is a mapping between “Pete’s foot” notation and the Date notation:

Shape in Date	Meaning in “Pete’s foot”
Rectangle	strong entity
Double rectangle	weak entity
Ellipse	Attribute of an entity or relationship
Diamond	relationship – see example below
Double diamond	No useful difference between double and single diamonds
Line	Two meanings: 1. Connects attribute to an entity or relationship 2. partial participation in a relationship – see example below
Double Line	Total participation in a relationship – see example below.

· Notice ENAME is a virtual attribute, i.e., ENAME is composed of the three sub-attributes FIRST, MI, LAST.  You need only list FIRST, MI, LAST in your tables.

· Relationship example: employee and department relationship.

Date defines the words total and partial, which mean required vs optional participation in a relationship, which in Pete’s foot, we represent as a minimum cardinality of 1 (total) or 0 (partial).  A double line means total participation, and a single line means partial participation.

Here is Dates’ description of the employee (E) -- department (D) relationship (drawn horizontally here – vertically in Date Fig 13.2):

The single line between DEPARTMENT and the diamond means DEPARTMENT optionally participates in the relationship, or any given department need not have any employees.

The double line between EMPLOYEE and the diamond means EMPLOYEE is required to participate in the relationship, or an employee must belong to a department.

The ‘1’ means an employee belongs to a maximum of 1 department, and the ‘M’ means a department may have many employees.

In Pete’s foot, this relationship would be:

DEPARTMENT 1 1 ------  has / belongs to ----------- 0 = EMPLOYEE

Read a DEPARTMENT has 0 to many EMPLOYEES and an EMPLOYEE belongs to one and only one DEPARTMENT.