Logical Design

After you have successfully diagrammed your designs via the Entity-Relationship Model, the next step is to take your models and convert them to SQL CREATE TABLE statements.

Transform entities in tables

The first step is to create a table for each entity within the model.

1. Use the name of the entity as the basis for the table name
2. For each attribute within the entity:
• Use the attribute name as the basis for the column name
• Determine the most specific data type available for the attribute
• Specify whether the attribute is required (NULL Status)
• Specify whether the attribute has a default value
• Specify if the attribute has any data constraints (must it be in a range, something like an email address, etc)
3. Determine the Primary Key (usually the Entity Identifier)
4. Determine if there are any alternate keys (UNIQUE constraints)

Transform relationships using foreign keys

The second step is to establish the relationships between the entities (now tables) with FOREIGN KEY constraints. Reminder, the FOREIGN KEY is the PRIMARY KEY of another relation (parent) that is placed in another (child), and forms a link between the two. The values of the FOREIGN KEY must already exist as PRIMARY KEY values in the corresponding relation before they can be added to the child table (no "dangling references").

1:1 - Strong Entity Relationships

1. For 1:1 - Strong Entity Relationships we will place the key of one entity, the parent, in the other entity (child) as a foreign key.
   • Either design will work - both could be parent or child
   • Minimum cardinality considerations: If only one entity is mandatory, that one should be the parent.
2. Choose NULL status for the FOREIGN KEY column(s) based on the minimum cardinality, (mandatory / optional)
3. Declare UNIQUE constraints for the FOREIGN KEY column(s). Knowledge Check: What is this enforcing?
   

1:1 - Example - Member as Parent

CREATE TABLE CLUB_MEMBER (
  MemberNumber INT PRIMARY KEY,
  MemberName VARCHAR(50),
  Phone CHAR(15),
  Email VARCHAR(50));

CREATE TABLE LOCKER (
  LockerNumber INT PRIMARY KEY,
  LockerRoom INT,
  LockerSize INT,
  MemberNumber INT NULL,
  CONSTRAINT FK_LOCKER_MEMBER FOREIGN KEY (MemberNumber)
    REFERENCES CLUB_MEMBER(MemberNumber),
  CONSTRAINT Unique_Member UNIQUE(MemberNumber));

1:1 - Example - Locker as Parent

DROP TABLE IF EXISTS LOCKER;
CREATE TABLE LOCKER (
  LockerNumber INT PRIMARY KEY,
  LockerRoom INT,
  LockerSize INT);

DROP TABLE IF EXISTS CLUB_MEMBER;
CREATE TABLE CLUB_MEMBER (
  MemberNumber INT PRIMARY KEY,
  MemberName VARCHAR(50),
  Phone CHAR(15),
  Email VARCHAR(50),
  LockerNumber INT NULL,
  CONSTRAINT FK_CLUBMEMBER_LOCKER FOREIGN KEY (LockerNumber)
    REFERENCES LOCKER(LockerNumber),
  CONSTRAINT Unique_Locker UNIQUE(LockerNumber));

We must enforce Referential Integrity, if a new Member row is added that references a non-existent locker - reject it! What if a locker row is deleted? What should we do?

• Also delete all member rows that refer to it?
• ON DELETE CASCADE... probably not a good idea!
• Disallow deletion of a Locker Row if it is referred to in CLUB_MEMBER?
• ON DELETE NO ACTION ... This is the MySQL default setting when none specified, it would work here but require someone to manually remove the locker entry in CLUB_MEMBER before deleting it in LOCKER.
• Set LockerNumber in Member to NULL when a Locker is deleted in LOCKER?
• ON DELETE SET NULL... This is probably my favorite answer, if we are renovating the locker room and develop a new locker scheme, we just delete the entries in the locker table and all members get a NULL value for their locker. We are going to have to re-assign them anyways!
• Set LockerNumber in Member to a default value when a Locker is deleted in LOCKER?
• ON DELETE SET DEFAULT... This works if we want to track members without a locker by a default value like 99999. Similar behavior as described with SET NULL.

1:N - Non-Identifying (Strong Entity) Relationships

With one-to-many non-identifying relationships simply place the key of the parent in the child. The parent is always the entity that can have many of the other entity in the relationship. COMPANY is the parent below.

DROP TABLE IF EXISTS COMPANY;
CREATE TABLE COMPANY (
  CompanyName VARCHAR(50) PRIMARY KEY,
  City VARCHAR(50),
  Country VARCHAR(50),
  Volume DEC );

DROP TABLE IF EXISTS DEPARTMENT;
CREATE TABLE DEPARTMENT (
  DepartmentName VARCHAR(50) PRIMARY KEY,
  BudgetCode CHAR(5),
  MailStop INT,
  CompanyName VARCHAR(50) NULL,
  CONSTRAINT FK_DEPARTMENT_COMPANY FOREIGN KEY (CompanyName)
    REFERENCES COMPANY (CompanyName)
    ON DELETE SET NULL ON UPDATE CASCADE );

1:N - Identifying (Weak Entity) Relationships

An identifying relationship occurs where a child instance cannot exist without the parent instance and the id of the parent is necessary in order to identify a child instance. To transform this relationship:

• place the key of the parent as a foreign key in the child table
• make the column from the parent part of the primary key of the child (so the primary key of the child will be a composite key) not just as a simple column.
• The column will be NOT NULL, since the parent is mandatory.
• The referential integrity constraint for the foreign key referencing the parent will likely have either CASCADE or NO ACTION, since the column cannot be NULL and likely does not have a default value

Let's look at an identifying relationship example in detail: the BUILDING and APARTMENT tables from our last class.

DROP TABLE IF EXISTS BUILDING;
CREATE TABLE BUILDING (
  BuildingName VARCHAR(50) PRIMARY KEY,
  Street VARCHAR(50),
  City VARCHAR(50),
  State VARCHAR(30),
  Zip INT);

DROP TABLE IF EXISTS APARTMENT;
CREATE TABLE APARTMENT (
  ApartmentNumber INT NOT NULL,
  BuildingName VARCHAR(50) NOT NULL,
  NumberBedrooms INT,
  NumberBaths INT,
  MonthlyRent DEC,
  CONSTRAINT PK_APARTMENT
    PRIMARY KEY (BuildingName, ApartmentNumber),
  CONSTRAINT FK_APARTMENT_BUILDING FOREIGN KEY (BuildingName)
    REFERENCES BUILDING (BuildingName)
    ON DELETE CASCADE ON UPDATE CASCADE);

N:M Strong Entity Relationships

In a N:M relationship there is no place for a FOREIGN KEY in either table.

• A COMPANY can supply many PARTS
• A PART may be supplied by many COMPANIES

DROP TABLE IF EXISTS COMPANY;
DROP TABLE IF EXISTS COMPANY;
CREATE TABLE COMPANY (
  CompanyName VARCHAR(50) PRIMARY KEY,
  City VARCHAR(50),
  Country VARCHAR(50),
  Volume DEC);

DROP TABLE IF EXISTS PART;
CREATE TABLE PART (
  PartNumber INT PRIMARY KEY,
  PartName VARCHAR(50),
  SalesPrice DEC,
  ReOrderQuantity INT,
  QuantityOnHand INT);

In order to link the two tables, we need to create an intersection table where the primary keys of each of the original tables becomes part of a composite primary key in the intersection table. Each of the original table's primary keys become a foreign key in the new intersection table.

DROP TABLE IF EXISTS COMPANY_PART;
CREATE TABLE COMPANY_PART (
  CompanyName VARCHAR(50) NOT NULL,
  PartNumber INT NOT NULL,
  CONSTRAINT PK_COMPANY_PART PRIMARY KEY (CompanyName, PartNumber),
  CONSTRAINT FK_COMPANYPART_COMPANY FOREIGN KEY (CompanyName)
    REFERENCES COMPANY (CompanyName) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_COMPANYPART_PART FOREIGN KEY (PartNumber)
    REFERENCES PART (PartNumber) ON DELETE NO ACTION ON UPDATE CASCADE);

IS-A Relationships with Subtypes

With subtypes, we will want to use the PRIMARY KEY of the supertype as the PRIMARY KEY and FOREIGN KEY within the subtype entities. The tables will be linked either via a condition on a column in the supertype that directs you down a specific path, or via various JOINS on supertype's PRIMARY KEY.

/*
    We don't want to just allow any EmpCodes, so let's create a table to track them!
    We also want to ensure we have a description of the EmpCode and the name of
    the table that stores further information about the employees with the
    given EmpCode!
*/
DROP TABLE IF EXISTS EMPCODES;
CREATE TABLE EMPCODES(
  EmpCode CHAR(10) NOT NULL,
  TableName VARCHAR(50) NOT NULL,
  Description VARCHAR(50) NULL,
  CONSTRAINT PK_EMPCODES PRIMARY KEY (EmpCode)
);

DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE(
  EmployeeNumber INT NOT NULL,
  EmployeeName VARCHAR(50) NOT NULL,
  Phone CHAR(15) NOT NULL,
  Email VARCHAR(50) NOT NULL,
  HireDate DATE NOT NULL,
  ReviewDate TIMESTAMP,
  EmpCode CHAR(10) NULL,
  CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EmployeeNumber),
  CONSTRAINT FK_EMPLOYEE_EMPCODES FOREIGN KEY (EmpCode)
    REFERENCES EMPCODES (EmpCode) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS MANAGER;
CREATE TABLE MANAGER(
  EmployeeNumber INT PRIMARY KEY,
  MgrTrainingDate date,
  ManagerLevel INT,
  CONSTRAINT FK_MANAGER_EMPLOYEE FOREIGN KEY (EmployeeNumber)
    REFERENCES EMPLOYEE (EmployeeNumber) ON DELETE CASCADE );

DROP TABLE IF EXISTS DB_ADMIN;
CREATE TABLE DB_ADMIN(
  EmployeeNumber INT PRIMARY KEY,
  DB_Name VARCHAR(50),
  DBMS VARCHAR(50),
  CONSTRAINT FK_DBADMIN_EMPLOYEE FOREIGN KEY (EmployeeNumber)
    REFERENCES EMPLOYEE (EmployeeNumber) ON DELETE CASCADE );

Specify logic for enforcing minimum cardinality

As a reminder, the cardinality refers to the minimum and maximum number of entity instances that can participate in a relationship.

Enforcing a Mandatory Parent

We will enforce minimum cardinality of one (mandatory) for a parent via FOREIGN KEY constraints and the NOT NULL setting for the FOREIGN KEY column(s).

DEPARTMENT (DepartmentName, BudgetCode, ManagerName)

DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE (
  EmployeeNumber INT PRIMARY KEY,
  EmployeeName VARCHAR(50),
  DepartmentName VARCHAR(50) NOT NULL,
  CONSTRAINT FK_EMPLOYEE_DEPARTMENT FOREIGN KEY(DepartmentName)
    REFERENCES DEPARTMENT(DepartmentName)
    ON DELETE NO ACTION
    ON UPDATE CASCADE );

Enforcing a Mandatory Child

Enforcing a mandatory child is considerably more difficult and requires the use of triggers, code that we write into the database that perform some action during updates. Writing triggers will be explained later in the class. Enforcing a mandatory child constraint is tricky, as a parent relation must have a valid child, and that child has a FOREIGN KEY constraint with the parent.

Practice Problems

1. Convert the ER diagrams you created during the previous class into MySQL CREATE Statements. Ensure you properly enforce the relationships captured in the model!