Translation of ER-diagram into

Relational Schema

Dr. Sunnie S. Chung

CIS430/530
 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
relations
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
9.6 the translated E-R model and produce one final logical
database model for the application
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
9.8
sequence
 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
 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
 Refining the ER Design for the
COMPANY Database

 Change attributes that represent

relationships into relationship types
 Determine cardinality ratio and
participation constraint of each
relationship type
 ER Diagrams, Naming
Conventions, and Design Issues
 Design Choices for ER
Conceptual Design
 Model concept first as an attribute
 Refined into a relationship if attribute is a
reference to another entity type

 Attribute that exists in several entity

types may be elevated to an independent
entity type
 Can also be applied in the inverse
 Attributes

• An entity is represented by a set of

attributes, that is descriptive properties
possessed by all members of an entity set.
Example:
Customer = (customer-id, customer-name,
customer-street, customer-city)

Loan = (loan-number, amount)

• Domain – the set of permitted values for

each attribute
 Attributes

• Composite identifier: Primary key composed of

more than one attribute
• Composite attribute: Attribute that can be
subdivided to yield additional attributes
• Simple attribute: Attribute that cannot be
subdivided
• Single-valued attribute: Attribute that has only a
single value
• Multivalued attributes: Attributes that have
many values

16
 Attributes

• Multivalued (Set Valued) attributes: Attributes

that have many values and require creating:
– Several new attributes, one for each component of
the original multivalued attribute
– A new entity composed of the original multivalued
attribute’s components
• Derived attribute: Attribute whose value is
calculated from other attributes
– Derived using an algorithm

17
Figure 4.6 - Depiction of a
Derived Attribute

18
 Attribute types:

– Simple and Composite attributes.

– Single-valued and Multi-valued attributes

• E.g. multivalued attribute: phone-numbers

– Derived attributes
• Can be computed from other attributes
• E.g. age, given date of birth
 Multivalued Attributes
• Normalization Required to
First Nomal Form
• A multivalued attribute M of an entity E
is represented by a separate table EM
– Table EM has attributes corresponding to
the primary key of E and an attribute
corresponding to multivalued attribute M

– Eg: Create Dept_Location table from

Department table
Dept_Location( Dnumber, Dlocation)
 Multivalued Attributes
– E.g. Multivalued attribute dependent-names
of employee is represented by a table
employee-dependent-names( employee-id,
dname)

– Each value of the multivalued attribute maps

to a separate row of the table EM
• E.g., an employee entity with primary key John
and dependents Johnson and Johndotir maps to
two rows:
(John, Johnson) and (John, Johndotir)
Composite Attributes
 Transforming
Composite Attribute
• Flatten the Structure to One Attribute
eg: Address of Employee Table
• Flatten the Structure to Multiple
Attributes
eg: Name  Lname, MI, Fname of
Employee Table
• Making the Composite attribute into
Separate Table
 Transforming
Composite Attribute 3
• Making a Composite Attribute into Separate
Table Customer_Address
Customer_Address
(Customer_Id, Street_Number, Street_Name,
Apt_Num, City, State, Zip, Country)
• More Query Power for Data Analytic on Address
• No Need to Access Big Customer Table for Address
Analytics
• Required Join with Address Table and Customer Table for
other Customer Information
 Composite Attributes
• Composite attributes are flattened out by
creating a separate attribute for each
component attribute

– E.g. given entity set customer with composite

attribute name with component attributes
first-name and last-name the table
corresponding to the entity set has two
attributes
name.first-name and name.last-name
 Alternative Notations
for ER Diagrams
 Specify structural constraints on
Relationships
 Replaces Cardinality ratio (1:1, 1:N, M:N) and
single/double line notation for Participation
constraints
 Associate a pair of integer numbers (min, max)
with each participation of an entity type E in a
relationship type R, where 0 ≤ min ≤ max and
max ≥ 1
 Cardinality Ratio
(1:1, 1:N, M:N)
since
• 1:N :Each dept has at name dname

most one manager on ssn lot did budget

Manages.
Employees Manages Departments

Translation to
relational model?

1-to-1 1-to Many Many-to-1 Many-to-Many

 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.29 b. The key should be nonredundant
9.30
 Transforming E-R Diagrams into
Relations

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 other (N) side
• The one side migrates to the many side

9.31
 Constraints on Binary
Relationship Types
 Cardinality ratio for a binary relationship
 Specifies maximum number of relationship
instances that entity can participate in

 Participation Constraint
 Specifies whether existence of entity depends
on its being related to another entity
 Types: total and partial
 Attributes of Relationship
Types
 Attributes of 1:1 or 1:N relationship types
can be migrated to one entity type
 For a 1:N relationship type
 Relationship attribute can be migrated only to
entity type on N-side of relationship

 For M:N relationship types

 Some attributes may be determined by
combination of participating entities
 Must be specified as relationship attributes
 Weak Entity Types

 Do not have key attributes of their own

 Identified by being related to specific entities
from another entity type

 Identifying relationship
 Relates a weak entity type to its owner

 Always has a total participation constraint

9.35
 Transforming Binary 1:N
Relationships into Relations
• Relationship: CREATE TABLE ORDER(
CUSTOMER Places ORDER(s) Order_Number CHAR(1),
• ORDER Table BEFORE Order_Date DATE,
Relationship: Promised_Date DATE,
(Order_Number, Customer_ID CHAR(1),
Order_Date, Promised_Date) PRIMARY KEY
• ORDER Table AFTER (Order_Number),
Relationship: FOREIGN KEY (Customer_ID)
(Order_Number, REFERENCES
Order_Date, Promised_Date, CUSTOMER(Customer_ID));
Customer_ID)
 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.37
 Binary 1 To 1 Relationship
Employee(Manager) MANAGES Department
EMPLOYEE
FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO

John B Smith 123456789 09-Jan-55 731 Fondren, Houston, TX M 30000 987654321 5

Franklin T Wong 333445555 08-Dec-45 638 Voss, Houston, TX M 40000 888665555 5

Joyce A English 453453453 31-Jul-62 5631 Rice, Houston, TX F 25000 333445555 5

Ramesh K Narayan 666884444 15-Sep-52 975 Fire Oak, Humble, TX M 38000 333445555 5

James E Borg 888665555 10-Nov-27 450 Stone, Houston, TX M 55000 1

Jennifer S Wallace 987654321 20-Jun-31 291 Berry, Bellaire, TX F 43000 888665555 4

Ahmad V Jabbar 987987987 29-Mar-59 980 Dallas, Houston, TX M 25000 987654321 4

Alicia J Zelaya 999887777 19-Jul-58 3321 Castle, SPring, TX F 25000 987654321 4

DEPARTMENT
DNAME DNUMBER MGRSSN MGRSTARTDATE
Headquarters 1 888665555 19-Jun-71
Administration 4 987654321 01-Jan-85
Research 5 333445555 22-May-78
Automation 7 123456789 06-Oct-05
 Transforming E-R Diagrams into
Relations

Represent Relationships
– Binary and higher M:N relationships
• Create another relation and include
primary keys of all relations as primary
key of new relation

9.39
9.40
Association Class to Table
ORDER LINE

Order_ Product_ Quantity_

Number ID Ordered

61384 M128 2
61384 A261 1
62807 A261 2
62807 R149 1
62009 R149 2
 Transforming Binary M:N
Relationships into Relations
• Relationship Requests: CREATE TABLE ORDERLINE (
Order Requests Products Order_Number CHAR(10),
1. Create Table ORDERLINE Product_ID CHAR(10),
for Relationship Requests Quantity_Ordered Integer,
2. Add PK of each side of PRIMARY KEY (Order_Number,
Tables ( Order_Number, Product_ID),
Product_ID) as Foreign Keys FOREIGN KEY (Order_Numer)
3. Make composite of both REFERENCES
attributes as Primary Key of ORDER(Order_Number),
the Table ORDERLINE : FOREIGN KEY (Product_ID)
REFERENCES
ORDERLINE(Order_Number,
PRODUCT(Product_ID));
Product_ID,
Quantity_Ordered)
 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.43
9.44
Unary 1 to M to Table:
MANAGES

Emp_ID Name Mgr_ID Birthdate

11111 John Smith 33333 12/30

22222 Jane Doe 33333 03/23

33333 Anne Hana 99999 05/05
44444 Nick Shaw 99999 11/23
99999 Ron 07/20
Birkman
 Transforming Unary 1:N
Relationships into Relations
• Relationship: CREATE TABLE
EMPLOYEE(
EMPLOYEE (as Manager)
Manages EMPLOYEE Emp_ID CHAR(1),
• EMPLOYEE Table Name Varchar(30),
BEFORE Relationship: Birthday DATE,
(Emp_ID, Name, Mgr_ID CHAR(1),
Birthday) PRIMARY KEY (Emp_ID),
• EMPLOYEE Table AFTER FOREIGN KEY (Mgr_ID)
Relationship: REFERENCES
(Emp_ID, Name, EMPLOYEE (Emp_ID));
Birthday, Mgr_ID)
Unary Association Class to Table:
CONTAINS
Item_ Name Cost
Number
A261 Nail 0.99
61384 AAA 21.11
M128 Screw 2.99
62807 BBB 200.11
62009 CCC 99.00
Containig Contained Quantity
Item_ Item_
Number Number
61384 M128 2
61384 A261 1
62807 A261 2
62807 R149 1
62009 R149 2
 Transforming Unary M:N
Relationships into Relations
• Relationship CONTAINS:
ITEM Contains ITEM CREATE TABLE CONTAINS (
Containing_Item_Number CHAR(10),
1. Create Table for
Contained_Item_Number CHAR(10),
Relationship
Quantity Integer,
CONTAINS PRIMARY KEY
2. Add PK of each side of (Containing_Item_Numer,
Contained_Item_Numer),
Tables (
FOREIGN KEY
Containing_Item_Numer, (Containing_Item_Number)
Contained_Item_Numer) as REFERENCES
Foreign Keys ITEM(Item_Number),
3. Make composite of both FOREIGN KEY
(Contained_Item_Number)
attributes as Primary Key of
REFERENCES
the Table CONTAINS: ITEM(Item_Number));
CONTAINS
(Containing_Item_Number,
Contained_Item_Number,
Quantity)
9.49
 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 dname 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 (

dname CHAR(20),
age INTEGER,
cost REAL,
ssn CHAR(11) NOT NULL,
PRIMARY KEY (pname, ssn),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE CASCADE)
 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. – Key is minimal.
– However, 2 does not hold (so false) for superkey – which is not
minimal.
– If there’s more than one keys for a relation, one of the keys is
chosen (by DBA) to be the primary key.
• E.g., customer_id is a key for Customer. (What about
name?) The set {customer_id, name} could be a
superkey.

Primary key can not have null value

 Domain Constraint

• The value of each Attribute A with

Domain Type D(Ai) must be a atomic value
from the domain type D(Ai).
 Definitions of Keys and Attributes
Participating in Keys
• A superkey of a relation schema R = {A1, A2, ...., An} is a
set of attributes S, subset-of R, with the property that
No two tuples t1 and t2 in any legal relation state r
of R will have t1[S] = t2[S].
That is, for any given two tuples t1, t2 in data
(extensions) of Relation schema R, t1[S] is not identical
to t2[S].
• A key K is a superkey with the additional property that
removal of any attribute from K will cause K not to be a
superkey any more; Key is minimal.
 Definitions of Keys and Attributes
Participating in Keys
• If a relation schema has more than one key, each is
called a candidate key.
• One of the candidate keys is arbitrarily designated to
be the primary key, and the others are called secondary
keys.
• A Prime attribute must be a member of any (candidate)
key
• A Nonprime attribute is not a prime attribute—that is,
it is not a member of any (candidate) key.
 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. customer_id in Order is a foreign key
referring to Customer:
Order (order_number, order_date, promised_date,
customer_id)
 Foreign Keys, Referential Integrity

• If all foreign key constraints are

enforced, referential integrity is
achieved; all foreign key values should
refer to existing values, i.e., no dangling
references.
• Can you name a data model w/o referential
integrity?
– Links in HTML!
 Enforcing Referential Integrity
• Consider Students(sid, name, gpa) and Enrolled (rid, semester, sid);
• 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))
 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

 Transforming 1:N, M:N Relationships
with Key Constraints
ER Diagram:

since
name dname
ssn lot did budget

Employees Manages Departments

Works_In

since
 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 here! 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.
 Transforming Realtionship to
Tables
Example E-R diagram:

since
name dname
ssn lot did budget

Employees Manages Departments

Works_In

since
 Relationship Sets to Tables
• In translating a relationship CREATE TABLE Works_In(
Works_In (M-N) to a ssn CHAR(1),
did INTEGER,
relation, attributes of the since DATE,
relation must include: PRIMARY KEY (ssn, did),
FOREIGN KEY (ssn)
– Keys for each REFERENCES Employees,
participating entity set FOREIGN KEY (did)
REFERENCES Departments)
(as foreign keys).
– This set of attributes
forms a superkey for the
relation.
– All descriptive attributes.
 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: Binary vs. Ternary
Relationships
name
ssn lot dname age
• If each policy is Employees Covers Dependents
owned by just 1
employee:
Bad design Policies

– Key constraint policyid cost

on Policies would name dname 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
• The key constraints (Contd.)
allow us to combine
CREATE TABLE Policies (
Purchaser with Policies
and Beneficiary with
policyid INTEGER,
Dependents. cost REAL,
• Participation
ssn CHAR(11) NOT NULL,
constraints lead to PRIMARY KEY (policyid).
NOT NULL constraints. FOREIGN KEY (ssn) REFERENCES Employees,
• What if Policies is a ON DELETE CASCADE);
weak entity set? CREATE TABLE Dependents (
PK of Policies: dname CHAR(20),
(policyid, ssn) age INTEGER,
PK of Dependents: policyid INTEGER,
(dname, policyid, ssn) PRIMARY KEY (dname, policyid).
FOREIGN KEY (policyid) REFERENCES Policies
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 attributes (fields)
 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 Students table