ER Relational Data Model

ER to Relational Data Model

9.1 An Entity-Relationship (ER) Diagram

9.2 A Relational Data Schema

EMPLOYEE	Fname Lname $\underset{¯}{SSN}$ SupervisorSSN
DEPARTMENT	Name $\underset{¯}{NUMBER}$ MANAGER-SSN StartDate
DEPENDENT	Relationship $\underset{¯}{EMPL-SSN}$ $\underset{¯}{Name}$
DEP-LOCATION	Location DEP-NUMBER
WORKS-FOR	EmployeeSSN DeptNumber

Comments:

EMPLOYEE (SupervisorSSN)	$\to$	EMPLOYEE (SSN)
DEPARTMENT (MANAGER-SSN)	$\to$	EMPLOYEE (SSN)
DEPENDENT (EMPL-SSN)	$\to$	EMPLOYEE (SSN)
DEP-LOCATION (DEP-NUMBER)	$\to$	DEPARTMENT (NUMBER)
WORKS-FOR (EmployeeSSN)	$\to$	EMPLOYEE (SSN)
WORKS-FOR (DeptNumber)	$\to$	DEPARTMENT (NUMBER)
unique: DEPARTMENT.NAME

create table EMPLOYEE{ 
  Fname... 
  Lname... 
  SSN... primary key 
  SupervisorSSN... reference EMPLOYEE(SSN) 
} 
create table DEPARTMENT{ 
  NAME... 
  NUMBER... primary key 
  MANAGER-SSN... references EMPLOYEE(SSN) 
  StartDate 
} 
create table DEPENDENT{ 
  Relationship... 
  EMPL-SSN... references EMPLOYEE(SSN) 
  Name ... 
  primary key(EMPL-SSN,Name) 
} 
create table DEP-LOCATION{ 
  Location... primary key 
  DEPNUMBER... references DEPARTMENT(Number) 
} 
create table WORKS-FOR{ 
  EmployeeSSN... references EMPLOYEE(SSN) 
 DeptNumber... references DEPARTMENT(Number) 
}  

9.3 The Mapping

(Strong) Entity Type into Relation

• Include the simple attributes
• Include the simple components of the composite attributes
• Identify the primary keys
• Don’t include: non-simple components of composite attributes, foreign keys, derived attributes, relational attributes

EMPLOYEE Fname Lname $\underset{¯}{SSN}$

DEPARTMENT Name $\underset{¯}{Number}$

Binary 1:1 Relationship Types into Foreign Keys

• Include as foreign keys, in the relation of one entity type, the primary keys of the other entity type
• Include also the simple attributes of the relationship type
• If possible, the first entity type should have total participation in the relationship (to save memory!)

DEPARTMENT

MANAGER-SSN StartDate

Binary 1:N Relationship Types into Foreign Keys

• Add as foreign keys, to the relation of the entity type at the N side, the primary keys of the entity type at the 1 side (don’t duplicate records!)
• Include also the simple attributes of the relationship type

EMPLOYEE

SupervisorSSN

Binary M:N Relationship Type into Relation

• We don’t want to duplicate records!
• Set as foreign keys the primary keys of the participating entity types
• Include the simple attributes of the relationship type

WORKS-FOR

EmployeeSSN DeptNumber

N-Ary Relationship Type

Similar to binary M:N relationship type

Multivalued Attribute into Relation

• Include the given attribute
• Include as foreign keys the primary attributes of the entity/relationship type owning the multivalued attribute
• Keys not designated within primary keys are to be mentioned as such in side comments

DEP-LOCATION

Location $\underset{¯}{DEP-NUMBER}$

Resembles the treatment of a relationship type.

Weak Entity+Relationship Types into Relation

• Include simple attributes
• Add the owner’s primary key attributes, as foreign key attributes
• Declare into a primary key the partial keys of the weak entity type combined with those imported from the owner

DEPENDENT Relationship $\underset{¯}{EMPL-SSN}$ $\underset{¯}{NAME}$

Reference: Ch. 7.1 in textbook.