Translation of ER-diagram into
Relational Schema
Prof. Sin-Min Lee
Department of Computer
Science
1
Learning Objectives
Define each of the following database terms
Relation
Primary key
Foreign key
Referential integrity
Field
Data type
Null value
Discuss the role of designing databases in the
analysis and design of an information system
Learn how to transform an entity-relationship (ER)
Diagram into an equivalent set of well-structured
9.2
9.2
relations
9.4
9.4
Process of Database Design
• Logical Design
– Based upon the conceptual data model
– Four key steps
1. Develop a logical data model for each known user interface
for the application using normalization principles.
2. Combine normalized data requirements from all user
interfaces into one consolidated logical database model
3. Translate the conceptual E-R data model for the
application into normalized data requirements
4. Compare the consolidated logical database design with the
translated E-R model and produce one final logical
database model for the application
9.6
9.6
9.7
9.7
Relational Database Model
• Data represented as a set of related tables or relations
• Relation
– A named, two-dimensional table of data. Each relation consists
of a set of named columns and an arbitrary number of unnamed
rows
– Properties
• Entries in cells are simple
• Entries in columns are from the same set of values
• Each row is unique
• The sequence of columns can be interchanged without changing the
meaning or use of the relation
• The rows may be interchanged or stored in any sequence
9.8
9.8
Relational Database Model
• Well-Structured Relation
– A relation that contains a minimum
amount of redundancy and allows users
to insert, modify and delete the rows
without errors or inconsistencies
9.9
9.9
Transforming E-R Diagrams into
Relations
• It is useful to transform the conceptual data
model into a set of normalized relations
• Steps
1. Represent entities
2. Represent relationships
3. Normalize the relations
4. Merge the relations
9.10
9.10
Transforming E-R Diagrams into
Relations
– The primary key must satisfy the following
two conditions
a. The value of the key must uniquely identify every
row in the relation
9.11
9.11 b. The key should be nonredundant
9.12
9.12
Transforming E-R Diagrams into
Relations
2. Represent Relationships
– Binary 1:N Relationships
• Add the primary key attribute (or attributes) of
the entity on the one side of the relationship as
a foreign key in the relation on the right side
• The one side migrates to the many side
9.14
9.14
9.15
9.15
Transforming E-R Diagrams into
Relations
– Binary or Unary 1:1
• Three possible options
a. Add the primary key of A as a foreign key of B
b.Add the primary key of B as a foreign key of A
c. Both
9.16
9.16
Transforming E-R Diagrams into
Relations
2. Represent Relationships (continued)
– Binary and higher M:N relationships
• Create another relation and include primary
keys of all relations as primary key of new
relation
9.17
9.17
9.18
9.18
Transforming E-R Diagrams into
Relations
– Unary 1:N Relationships
• Relationship between instances of a single entity type
• Utilize a recursive foreign key
– A foreign key in a relation that references the primary key
values of that same relation
– Unary M:N Relationships
• Create a separate relation
• Primary key of new relation is a composite of two attributes
that both take their values from the same primary key
9.19
9.19
9.20
9.20
9.21
9.21
Primary Key Constraints
• A set of fields is a key for a relation if :
1. No two distinct tuples can have same values in all key
fields, and
2. This is not true for any subset of the key.
– Part 2 false? A superkey.
– If there’s >1 key for a relation, one of the keys is
chosen (by DBA) to be the primary key.
• E.g., sid is a key for Students. (What about
name?) The set {sid, gpa} is a superkey.
Primary key can not have null value
Foreign Keys, Referential
Integrity
• Foreign key : Set of fields in one relation that is used to
`refer’ to a tuple in another relation. (Must correspond
to primary key of the second relation.) Like a `logical
pointer’.
• E.g. sid is a foreign key referring to Students:
– Enrolled(sid: string, cid: string, grade: string)
– If all foreign key constraints are enforced, referential
integrity is achieved, i.e., no dangling references.
– Can you name a data model w/o referential integrity?
• Links in HTML!
Enforcing Referential Integrity
• Consider Students and Enrolled; sid in Enrolled is a foreign key that
references Students.
• What should be done if an Enrolled tuple with a non-existent student id
is inserted? (Reject it!)
• What should be done if a Students tuple is deleted?
– Also delete all Enrolled tuples that refer to it.
– Disallow deletion of a Students tuple that is referred to.
– Set sid in Enrolled tuples that refer to it to a default sid.
– (In SQL, also: Set sid in Enrolled tuples that refer to it to a special
value null, denoting `unknown’ or `inapplicable’.)
• Similar if primary key of Students tuple is updated.
Logical DB Design: ER to Relational
• Entity sets to tables.
name
ssn lot
Employees
CREATE TABLE Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER,
PRIMARY KEY (ssn))
Relationship Sets to Tables
CREATE TABLE Works_In(
• In translating a relationship
ssn CHAR(1),
did INTEGER,
set to a relation, attributes of
since DATE,
the relation must include:
PRIMARY KEY (ssn, did),
– Keys for each participating FOREIGN KEY (ssn)
entity set (as foreign keys). REFERENCES Employees,
• This set of attributes FOREIGN KEY (did)
forms a superkey for the REFERENCES Departments)
relation.
– All descriptive attributes.
Review: Key Constraints
since
• Each dept has at most name dname
one manager, according ssn lot did budget
to the key constraint
on Manages. Employees Manages Departments
Translation to
relational model?
1-to-1 1-to Many Many-to-1 Many-to-Many
Translating ER Diagrams with Key Constraints
CREATE TABLE Manages(
• Map relationship to a ssn CHAR(11),
table: did INTEGER,
since DATE,
– Note that did is the PRIMARY KEY (did),
key now! FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments)
– Separate tables for
Employees and
Departments. CREATE TABLE Dept_Mgr(
did INTEGER,
• Since each department dname CHAR(20),
has a unique manager, budget REAL,
ssn CHAR(11),
we could instead since DATE,
combine Manages and PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees)
Departments.
Review: Participation Constraints
• Does every department have a manager?
– If so, this is a participation constraint: the participation of Departments in
Manages is said to be total (vs. partial).
• Every did value in Departments table must appear in a row of the Manages table
(with a non-null ssn value!)
since
name dname
ssn lot did budget
Employees Manages Departments
Works_In
since
Participation Constraints in
SQL
• We can capture participation constraints
involving one entity set in a binary
relationship, but little else (without
resorting to CHECK constraints).
CREATE TABLE Dept_Mgr(
did INTEGER,
dname CHAR(20),
budget REAL,
ssn CHAR(11) NOT NULL,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE NO ACTION)
Review: Weak Entities
• A weak entity can be identified uniquely only by considering the primary key of
another (owner) entity.
– Owner entity set and weak entity set must participate in a one-to-many
relationship set (1 owner, many weak entities).
– Weak entity set must have total participation in this identifying
relationship set.
name
cost pname age
ssn lot
Employees Policy Dependents
Translating Weak Entity Sets
• Weak entity set and identifying relationship set are translated into a single
table.
– When the owner entity is deleted, all owned weak entities must also be
deleted.
CREATE TABLE Dep_Policy (
pname CHAR(20),
age INTEGER,
cost REAL,
ssn CHAR(11) NOT NULL,
PRIMARY KEY (pname, ssn),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE CASCADE)
Review: Binary vs. Ternary
Relationships
name
ssn lot pname age
• If each policy is
Employees Covers Dependents
owned by just 1
employee: Bad design Policies
– Key constraint policyid cost
on Policies would name pname age
ssn lot
mean policy can
Dependents
only cover 1 Employees
dependent!
Purchaser
Beneficiary
• What are the
additional constraints Better design Policies
in the 2nd diagram?
policyid cost
Binary vs. Ternary Relationships
(Contd.)
CREATE TABLE Policies (
• The key constraints policyid INTEGER,
allow us to combine cost REAL,
Purchaser with ssn CHAR(11) NOT NULL,
Policies and PRIMARY KEY (policyid).
Beneficiary with FOREIGN KEY (ssn) REFERENCES Employees,
Dependents. ON DELETE CASCADE)
• Participation CREATE TABLE Dependents (
constraints lead to pname CHAR(20),
NOT NULL age INTEGER,
constraints. policyid INTEGER,
PRIMARY KEY (pname, policyid).
• What if Policies is a
FOREIGN KEY (policyid) REFERENCES Policies,
weak entity set?
ON DELETE CASCADE)
An Example
CREATE TABLE Student (
ID NUMBER,
Fname VARCHAR2(20),
Lname VARCHAR2(20),
);
Constraints in Create Table
• Adding constraints to a table enables the
database system to enforce data
integrity.
• Different types of constraints:
* Not Null * Default Values
* Unique * Primary Key
* Foreign Key * Check Condition
Not Null Constraint
CREATE TABLE Student (
ID NUMBER,
Fname VARCHAR2(20) NOT NULL,
Lname VARCHAR2(20) NOT NULL,
);
Primary Key Constraint
CREATE TABLE Student (
ID NUMBER PRIMARY KEY,
Fname VARCHAR2(20) NOT NULL,
Lname VARCHAR2(20) NOT NULL,
);
Primary Key implies: * NOT NULL * UNIQUE.
There can only be one primary key.
Primary Key Constraint
(Syntax 2)
CREATE TABLE Students (
ID NUMBER,
Fname VARCHAR2(20) NOT NULL,
Lname VARCHAR2(20) NOT NULL,
PRIMARY KEY(ID)
);
Needed when the primary key is made
up of two or more fields
Another Table
CREATE TABLE Studies(
Course NUMBER,
Student NUMBER
);
What should be the primary key?
What additional constraint do we want on
Student?
Foreign Key Constraint
CREATE TABLE Studies(
Course NUMBER,
Student NUMBER,
FOREIGN KEY (Student) REFERENCES
Students(ID)
);
NOTE: ID must be unique (or primary key) in
Student
Translating ER-Diagrams to
Table Definitions
45
Relations vs. Tables
• We show how to translate ER-Diagrams to
table definitions
• Sometimes, people translate ER-Diagrams
to relation definitions, which is more
abstract than table definitions.
– e.g., Student(ID, Fname, Lname);
– table definitions contain, in addition,
constraints and datatypes
Translating Entities
birthday
id
Actor
name address
General Rule:
• Create a table with the name of the Entity.
• There is a column for each attribute
• The key in the diagram is the primary key of the table
Translating Entities
birthday
id
Actor
name address
Relation: Actor (id, name, birthday, address)
create table Actor(id varchar2(20) primary key,
name varchar2(40),
birthday date,
address varchar2(100));
Translating Relationships
(without constraints)
birthday title
id
Actor Acted In Film year
name
salary
address type
General Rule:
• Create a table with the name of the relationship
• The table has columns for all of the relationship's attributes and
for the keys of each entity participating in the relationship
• What is the primary key of the table?
• What foreign keys are needed?
Translating relationships
(without constraints)
birthday title
id
Actor Acted In Film year
name
salary
address type
What would be the relation for ActedIn?
How would you define the table for ActedIn?
Translating Recursive Relationships
(without constraints)
manager
id
Employee Manages
worker
name
address
Relation: Actor (worker-id, manager-id)
What would be the table definition?
Translating relationships
(key constraints): Option 1
id
Director Directed Film title
name
salary year
General Rule for Option 1:
• Same as without key constraints, except that
the primary key is defined differently
Translating relationships
(key constraints): Option 1
id
Director Directed Film title
name
salary year
create table Directed(
id varchar2(20),
title varchar2(40),
salary integer,
What primary and foreign keys are missing?
)
Translating relationships
(key constraints): Option 2
id
Director Directed Film title
name
salary year
General Rule for Option 2:
• Do not create a table for the relationship
• Add information columns that would have been in the
relationship's table to the table of the entity with the key
constraint
• What is the disadvantage of this method?
• What is the advantage of this method?
Translating relationships
(key constraints): Option 2
id
Director Directed Film title
name
salary year
create table Film(
title varchar2(40),
year integer,
primary key (title),
What 3 lines are missing?
)
Translating relationships
(key constraints)
A R B
• What are the different options for
translating this diagram?
Translating relationships
(participation constraints)
id
Director Directed Film title
name
salary year
General Rule:
• If has both participation and key constraint, use Option
2 from before.
• Add the not null constraint to ensure that there will
always be values for the key of the other entity
Translating relationships
(participation constraints)
id
Director Directed Film title
name
salary year
create table Film(
title varchar2(40),
Where should we add
year integer,
NOT NULL?
id varchar2(20),
salary integer,
foreign key (id) references Director,
primary key (title))
Translating relationships
(participation constraints)
id
Actor Acted In Film title
name
salary year
• How would we translate this?
Translating Weak Entity Sets
phone
name create table award(
number
name varchar2(40),
Organization year integer,
money number(6,2),
o_name varchar2(40),
Gives
primary key(name, year, o_name),
foreign key (o_name) references
Organization(name)
Award money
on delete cascade
year )
name
Translating ISA: address
Option 1 id
Movie Person
name
ISA
picture Actor Director
create table MoviePerson( ... )
create table Actor(id varchar2(20),
picture bfile,
primary key(id),
foreign key (id) references MoviePerson))
create table Director(...)
Translating ISA: address
Option 2 id
Movie Person
name
ISA
picture Actor Director
No table for MoviePerson!
create table Actor(id varchar2(20),
address varchar2(100),
name varchar2(20),
picture blob,
primary key(id));
create table Director(...)
Which Option To Choose?
• What would you choose if:
– Actor and Director DO NOT COVER
MoviePerson?
– Actor OVERLAPS Director?
Translating
phone
name
number
Aggregation
Organization
picture Actor
Gives salary
Acted In
Won
year Film
Award
year name title type
• Create table for Won using:
– key of ActedIn
– key of Award (careful, award is a weak entity)
