Chapter 4

Database Design I:
The Entity-Relationship
Model

Database Design
Goal: specification of database schema
Methodology:
Use E-R model to get a high-level graphical view of
essential components of enterprise and how they are
related
Convert E-R diagram to DDL

E-R Model:
Model enterprise is viewed as a set of
Entities
Relationships among entities
2

Entities
Entity:
Entity an object that is involved in the
enterprise
Ex: John, CSE305

Entity Type:
Type set of similar objects
Ex: students,
students courses

Attribute:
Attribute describes one aspect of an entity type
Ex: name, maximum enrollment
3

Entity Type
Entity type described by set of attributes
Person:
Person Id, Name, Address, Hobbies

Domain:
Domain possible values of an attribute
Value can be a set (in contrast to relational model)
(111111, John, 123 Main St, {stamps, coins})

Key:
Key minimum set of attributes that uniquely
identifies an entity (candidate key)
Entity Schema:
Schema entity type name, attributes (and
associated domain), key constraints
4

Entity Type (cont)

Graphical Representation in E-R diagram:

Set valued
5

Relationships
Relationship:
Relationship relates two or more entities
John majors in Computer Science

Relationship Type:
Type set of similar relationships
Student (entity type) related to Department (entity type)
by MajorsIn (relationship type).

Distinction:
relation (relational model) - set of tuples
relationship (E-R Model) describes relationship
between entities of an enterprise
Both entity types and relationship types (E-R model)
may be represented as relations (in the relational model)
6

Attributes and Roles

Attribute of a relationship type describes the
relationship
e.g., John majors in CS since 2000
John and CS are related
2000 describes relationship - value of SINCE attribute
of MajorsIn relationship type

Role of a relationship type names one of the

related entities
e.g., John is value of Student role, CS value of
Department role of MajorsIn relationship type
(John, CS; 2000) describes a relationship

Relationship Type
Described by set of attributes and roles
e.g., MajorsIn:
MajorsIn Student, Department, Since
Here we have used as the role name (Student)
the name of the entity type (Student)
Student of the
participant in the relationship, but ...

Roles
Problem: relationship can relate elements of
same entity type
e.g., ReportsTo relationship type relates two
elements of Employee entity type:
Bob reports to Mary since 2000

We do not have distinct names for the roles

It is not clear who reports to whom

Roles (cont)
Solution: role name of relationship type
need not be same as name of entity type
from which participants are drawn
ReportsTo has roles Subordinate and
Supervisor and attribute Since
Values of Subordinate and Supervisor both
drawn from entity type Employee

10

Schema of a Relationship Type

Role names,
names Ri, and their corresponding entity
sets. Roles must be single valued (number of
roles = degree of relationship)
Attribute names,
names Aj, and their corresponding
domains. Attributes may be set valued
Key:
Key Minimum set of roles and attributes that
uniquely identify a relationship
Relationship: <e1, en; a1, ak>
ei is an entity, a value from Ris entity set
aj is a set of attribute values with elements from
domain of Aj

11

Graphical Representation
Roles are edges labeled with role names (omitted if role name
= name of entity set). Most attributes have been omitted.

12

Entity Type Hierarchies

One entity type might be subtype of another
Freshman is a subtype of Student

A relationship exists between a Freshman entity

and the corresponding Student entity
e.g., Freshman John is related to Student John

This relationship is called IsA

Freshman IsA Student
The two entities related by IsA are always descriptions
of the same real-world object
13

IsA
Student
Represents 4
relationship types

IsA

Freshman

Sophmore

Junior

Senior

14

Properties of IsA
Inheritance - Attributes of supertype apply
to subtype.
E.g., GPA attribute of Student applies to
Freshman
Subtype inherits all attributes of supertype.
Key of supertype is key of subtype

Transitivity - Hierarchy of IsA

Student is subtype of Person,
Person Freshman is
subtype of Student, so Freshman is also a
subtype of Student
15

Advantages of IsA
Can create a more concise and readable E-R
diagram
Attributes common to different entity sets need
not be repeated
They can be grouped in one place as attributes
of supertype
Attributes of (sibling) subtypes can be different

16

IsA Hierarchy - Example

17

Constraints on Type Hierarchies

Might have associated constraints:
Covering constraint:
constraint Union of subtype entities is
equal to set of supertype entities
Employee is either a secretary or a technician (or both)

Disjointness constraint:
constraint Sets of subtype entities are
disjoint from one another
Freshman,
Freshman Sophomore,
Sophomore Junior,
Junior Senior are disjoint set

18

Single-role Key Constraint

If, for a particular participant entity type,
each entity participates in at most one
relationship, corresponding role is a key of
relationship type
E.g., Professor role is unique in WorksIn

Representation in E-R diagram: arrow

Professor

WorksIn

Department
19

Participation Constraint
If every entity participates in at least one
relationship, a participation constraint
holds:
A participation constraint of entity type E
having role in relationship type R states that
for e in E there is an r in R such that (r) = e.
e.g., every professor works in at least one
department
Reprsentation in ER

Professor

WorksIn

Department

20

Participation and Key Constraint

If every entity participates in exactly one
relationship, both a participation and a key
constraint hold:
e.g., every professor works in exactly one
department
E-R representation: thick line

Professor

WorksIn

Department
21

Representation of Entity Types in the

Relational Model
An entity type corresponds to a relation
Relations attributes = entity types attributes
Problem: entity type can have set valued attributes, e.g.,
Person:
Person Id, Name, Address, Hobbies
Solution: Use several rows to represent a single entity
(111111, John, 123 Main St, stamps)
(111111, John, 123 Main St, coins)

Problems with this solution:

Redundancy
Key of entity type (Id) not key of relation
Hence, the resulting relation must be further transformed (Chapter
6)
22

Representation of Relationship Types in the

Relational Model
Typically, a relationship becomes a relation in the relational model
Attributes of the corresponding relation are
Attributes of relationship type
For each role, the primary key of the entity type associated with that role
Example:
SectNo

CrsCode

Enroll

S2000Courses

RoomNo

DeptId

Professor

Teaching
TAs

Name

S2000Courses (CrsCode, SectNo, Enroll)

Professor (Id, DeptId, Name)
Teaching (CrsCode, SecNo, Id, RoomNo, TAs)

Id

23

Representation of Relationship Types in

the Relational Model
Candidate key of corresponding table = candidate key
of relation
Except when there are set valued attributes
Example: Teaching (CrsCode, SectNo, Id, RoomNo, TAs)
Key of relationship type = (CrsCode, SectNo)
Key of relation = (CrsCode, SectNo, TAs)
CrsCode

SectNo

CSE305
CSE305

1
1

Id

RoomNo

1234 Hum 22
1234 Hum 22

TAs

Set
valued

Joe
Mary
24

Representation in SQL
Each role of relationship type produces a
foreign key in corresponding relation
Foreign key references table corresponding to
entity type from which role values are drawn

25

Example 1
Since
Professor

Status
WorksIn

Department

CREATE TABLE WorksIn (

Since DATE,
-- attribute
Status CHAR (10),
-- attribute
ProfId INTEGER,
-- role (key of Professor)
Professor
DeptId CHAR (4),
-- role (key of Department)
Department
PRIMARY KEY (ProfId), -- since a professor works in at most one department
FOREIGN KEY (ProfId) REFERENCES Professor (Id),
FOREIGN KEY (DeptId) REFERENCES Department )
26

Example 2
Date
Project

Price
Sold

Part

Supplier
CREATE TABLE Sold (
Price INTEGER,
-- attribute
Date DATE,
-- attribute
ProjId INTEGER,
-- role
SupplierId INTEGER,
-- role
PartNumber INTEGER,
-- role
PRIMARY KEY (ProjId, SupplierId, PartNumber, Date),
FOREIGN KEY (ProjId) REFERENCES Project,
Project
FOREIGN KEY (SupplierId) REFERENCES Supplier (Id),
FOREIGN KEY (PartNumber) REFERENCES Part (Number) )
27

Representation of Single Role Key Constraints

in the Relational Model
Relational model representation: key of the relation
corresponding to the entity type is key of the relation
corresponding to the relationship type
Id is primary key of Professor;
Professor ProfId is key of WorksIn.
WorksIn
Professor 4100 does not participate in the relationship.
Cannot use foreign key in Professor to refer to WorksIn since
some professors may not work in any dept. (But ProfId is a
foreign key in WorksIn that refers to Professor.)
Professor

Professor

WorksIn
Id

1123
4100
3216
Professor

Department
ProfId

1123
3216
WorksIn

Key

CSE
AMS
28

Representing Type Hierarchies in the

Relational Model
Supertypes and subtypes can be realized as
separate relations
Need a way of identifying subtype entity with
its (unique) related supertype entity
Choose a candidate key and make it an attribute of
all entity types in hierarchy

29

Type Hierarchies and the Relational Model

Translated by adding the primary key of supertype to all
subtypes. Plus foreign key from subtypes to the supertype.
Id attribs0

Student

Id attribs1

Id

attribs2

Freshman

Sophmore

Id

attribs3

Junior

Id

attribs4

Senior

FOREIGN KEY Id REFERENCES Student

inFreshman,Sophomore,Sunior,Senior

30

Type Hierarchies and the Relational Model

Redundancy eliminated if IsA is not disjoint
For individuals who are both employees and
students, Name and DOB are stored only once
Person
SSN
1234

Name DOB
Mary 1950

Employee
SSN Department
1234 Accounting

Student
Salary
35000

SSN GPA StartDate

1234 3.5
1997

31

Type Hierarchies and the Relational Model

Other representations are possible in special cases,
such as when all subtypes are disjoint
See in the book

32

Representing Participation Constraints

in the Relational Model
Professor

WorksIn

Department

Inclusion dependency:
dependency Every professor works in at least one dept.
in the relational model: (easy)
Professor (Id) references WorksIn (ProfId)
in SQL:
Simple case: If ProfId is a key in WorksIn (i.e., every professor works
in exactly one department) then it is easy:
FOREIGN KEY Id REFERENCES WorksIn (ProfId)

General case ProfId is not a key in WorksIn, so cant use foreign key
constraint (not so easy):
CREATE ASSERTION ProfsInDepts
CHECK ( NOT EXISTS (
SELECT * FROM Professor P
WHERE NOT EXISTS (
SELECT * FROM WorksIn W
WHERE P.Id = W.ProfId ) ) )

Select those professors

that do not work
33

Representing Participation Constraint in the

Relational Model
Example (cant use foreign key in Professor if ProfId
is not a candidate key in WorksIn)
WorksIn
ProfId

Id

1123
1123
4100
3216

1123
4100
3216
Professor

CSE
AMS
ECO
AMS

WorksIn
not a
candidate key
ProfId

34

Representing Participation and Key Constraint

in SQL
If both participation and key constraints apply, use
foreign key constraint in entity table (but beware: if
candidate key in entity table is not primary, presence
of nulls violates participation constraint).
CREATE TABLE Professor (
Id INTEGER,

PRIMARY KEY (Id),

-- Id cant be null
FOREIGN KEY (Id) REFERENCES WorksIn (ProfId)
--all professors participate
)

Professor

WorksIn

Department
35

Participation and Key Constraint in the

Relational Model
Example:

Id

xxxxxx 1123
yyyyyy 4100
zzzzzzz 3216
Professor

ProfId

1123
4100
3216

CSE
ECO
AMS

WorksIn

36

Participation and Key Constraint in

Relational Model (again)
Alternative solution if both key and
participation constraints apply: merge the
tables representing the entity and
relationship sets
Since there is a 1-1 and onto relationship
between the rows of the entity set and the
relationship sets, might as well put all the
attributes in one table
37

Participation and Key Constraint in

Relational Model
Example
Name

Id

xxxxxxx
yyyyyyy
zzzzzzzz

1123
4100
3216

DeptId

CSE
ECO
AMS

Prof_WorksIn

38

Entity or Attribute?
Sometimes information can be represented
as either an entity or an attribute.
Student

Semester

Transcript
Course

Grade
Semester

Student

Appropriate if Semester
has attributes
(next slide)

Transcript
Grade
Course

39

Entity or Relationship?

40

(Non-) Equivalence of Diagrams

Transformations between binary and ternary relationships.
Part

Date

Project

Sold
Price

Supplier

41

ER exercise 1
Consider the design of the following database system for
managing a conference X: a collection of papers are submitted to
X, each of which has a unique paper IDs, a list of authors (names,
affiliations, emails) in the order of contribution significance, title,
abstract, and a PDF file for its content. The conference has a list
of program committee (PC) members to review the papers. To
ensure review quality, each paper is assigned to 3 PC members for
review. To avoid overloading, each PC member is assigned with at
most 5 papers, assuming that there are enough PC members. Each
review report consists of a report ID, a description of review
comment, a final recommendation (accept, reject), and the date the
review report is submitted. A PC member can submit at most one
review report for the paper that is assigned to him/her.
42

ER exercise 1 (cont)
Draw an E-R diagram for the above system. Use underlines,
thick lines, and arrows to represent constraints. State your
assumptions if necessary.
Translate the previous E-R diagram for exercise1 into a
relational model, i.e., a set of CREAT TABLE statements
enforcing all stated constraints. In addition, write a CREATE
ASSERTION statement to enforce that no PC member will be
assigned to a paper of which she/he is a coauthor.

43

ER Diagram

44

SQL exercise
Create table paper (
paperid integer,
title VARCHAR(50),
abstract VARCHAR(250),
pdf VARCHAR(100),
primary key (paperid)
)

45

SQL exercise
Create table author(
email VARCHAR(100),
name VARCHAR(50),
affiliation VARCHAR(100),
primary key(email)
)

46

CREATE table write(

paperid integer,
email varchar(50),
order integer,
Primary key(paperid, email),
foreign key paperid references paper,
foreign key email references autor)
47

create table pcmember(

email VARCHAR(50),
name VARCHAR(20),
primary key (email)
)

48

create table review(

reportid integer,
sdate DATE,
comment VARCHAR(250),
recommendation CHAR(1),
paperid integer,
email VARCHAR(100),
unique(paperid, email),
foreign key paperid references paper,
foreign key email references pcmember)

49

CREATE Assertion 3pc

CHECK NOT EXISTS(
SELECT * FROM Papers P
WHERE 3 <> (
SELECT COUNT(*)
FROM Review R
WHERE R.paperid = P.paperid
)
)

50

CREATE ASSERTION atmostfivepapers

CHECK NOT NOT EXISTS
(
SELECT * FROM pcmember P
WHERE 5 < (
SELECT *
FROM review R
WHERE R.email = P.email
)
)

51

ER exercise 2
Suppose you are asked to design a club database system based on
the following information. Each student has a unique student id, a
name, and an email; each club has a unique club id, a name, a
contact telephone number, and has exactly one student as its
president. Each student can serve as a president in at most one of the
clubs, although he/she can be the members of several clubs. Clubs
organize activities and students can participate in any of them. Each
activity is described by a unique activity id, a place, a date, a time
and those clubs that organize it. If an activity is organized by more
than one club, different clubs might contribute different activity
fees.

52

Exercise 2 (cont)
Draw an E-R diagram for the system, in particular, use
arrows or thick lines to represent constraints appropriately.
Write down your assumptions if necessary.
Translate the above E-R diagram to a relational model, in
particular, specify your primary key and foreign key
constraints clearly.

53

Reference solution

54

CREATE TABLE Student (

studid CHAR(9),
email VARCHAR(50),
name VARCHAR(50) NOT NULL,
PRIMARY KEY ( studid ),
)

55

CREATE TABLE Club (

clubid INTEGER,
telephone VARCHAR(15),
name VARCHAR(50),
president CHAR(9) NOT NULL,
unique(president),
PRIMARY KEY (clubid),
FOREIGN KEY (president) REFERENCES Student(studid )
)
56

CREATE TABLE MemberOf (

clubid INTEGER,
studid VARCHAR(50),
PRIMARY KEY (clubid, studid ),
FOREIGN KEY (clubid ) REFERENCES Club( clubid ),
FOREIGN KEY (studid ) REFERENCES Student( studid )
)

57

CREATE TABLE Activities (

actid INTEGER,
actdt DATETIME,
place VARCHAR(50),
PRIMARY KEY (actid )
)

58

CREATE TABLE Organize (

actid INTEGER,
clubid INTEGER,
fee VARCHAR(50),
PRIMARY KEY (actid, clubid ),
FOREIGN KEY (actid ) REFERENCES Activities(actid ),
FOREIGN KEY (clubid ) REFERENCES Club( clubid )
)

59

CREATE ASSERTION AtLeastOneOrganizer

CHECK NOT EXISTS(
SELECT *
FROM Activities
WHERE NOT EXISTS(
SELECT *
FROM Organize
WHERE Organize.actid=Activities.actid
)
)
60

Exercise 3
Consider the design of a database for the management of
grants. Each grant is identified by a unique grant ID, a title,
the funding source of the grant, the period (starting data and
ending date), and the amount of grant. Each grant might be
participated by several professors and each professor might
also participate in several grants. Each professor is identified
by a unique SSN, name, and email address. In addition,
several graduate students might be supported by a grant as
GRAs, although each student can be supported by at most one
grant. Each graduate student has exactly one professor as
his/her advisor.
61

Exercise 3 (cont)
Draw an E-R diagram for the system, in particular,
use arrows or thick lines to represent constraints
appropriately. Write down your assumptions and
justifications briefly and clearly.
Translate the above E-R diagram into a relational
model, i.e., write a set of CREATE TABLE
statements. In particular, specify primary key,
foreign key and other constraints whenever possible.

62

Reference solution

63

create table grant(

grantid integer,
title varchar(50),
source varchar(50),
periodstart DATE,
periodend DATE,
amount integer,
primary key(grantid)
)
64

Create table professor(

ssn char(9),
name VARCHAR(20),
email varchar(20),
primary key(ssn),
unique(email)
)
65

Create table participate

(grantid integer,
professorid char(9),
primary key(grantid, professorid),
foreign key grantid references grant,
foreign key professorid references
professor(ssn))
66

Create student
(studid integer,
name varchar(50),
status varchar(20),
advisor char(9) NOT NULL,
supportgrantid integer,
primary key(studid),
foreign key advisor references professor,
Foreign key supportgrantid references grant(grantid)
)
67

Exercise 4
Consider the design of the following database system: each
PhD student has exactly one a dissertation committee which
consists of 4-5 faculty, and each committee is for exactly one
student. Each student has an ordered list of advisors including
the primary advisor followed by 0 or more secondary
advisors. Each student has a unique studid, a name, and a
major. Each committee has a unique committee id, and the
date the committee is formed. Each faculty has a unique facid
and a name. Each faculty can participate in multiple
committees and be the advisors (either primary or secondary)
of several students.
68

Exercise 4 (cont)
Draw an E-R diagram for the above system. Use
underlines, thick lines, and arrows to represent constraints.
State your assumptions if necessary.
Translate your E-R diagram for problem 1 into a relational
model, i.e., a set of CREAT TABLE/ASSERTION
statements enforcing all stated constraints. In addition,
write a CREATE ASSERTION statement to enforce that
each committee consists of the primary advisor of the
student and all other members of the committee cannot be
the secondary advisors of the student.
69

Reference solution

70

Reference solution
CREATE TABLE Advise (
order VARCHAR(50),
studid VARCHAR(50),
facid VARCHAR(50),
PRIMARY KEY ( studid, facid ),
FOREIGN KEY ( studid ) REFERENCES Students
( studid ),
FOREIGN KEY (facid ) REFERENCES Faculty ( facid )
)
71

CREATE TABLE Student (

studid VARCHAR(50) NOT NULL,
name VARCHAR(50),
major VARCHAR(50),
since DATE,
PRIMARY KEY ( studid )
)

72

CREATE TABLE Participate (

studid VARCHAR(50),
facid VARCHAR(50),
PRIMARY KEY (studid, facid ),
FOREIGN KEY ( studid ) REFERENCES Student
FOREIGN KEY (facid ) REFERENCES Faculty ( facid )
)

73

The primary advisor must be in the committee

CREATE ASSERTION
CHECK NOT EXISTS(
SELECT * from Student S
WHERE (
SELECT facid
// one primary advisor
FROM Advise A
WHERE A.facid = S.facid and A.order = 1
) NOT IN
(
// all my committee members
select facid
FROM Participate P
WHERE P.stuid = S.studid
)
)

74

Other co-advisors must be NOT in the committee

CREATE ASSERTION
CHECK NOT EXISTS(
SELECT * from Student S
WHERE EXISTS( // some committee members
are co-advisors
SELECT A.facid
FROM Advise A, Pariticpate P
WHERE s.studid = A.stuid AND A.stuid =
P.studid AND A.order <> 1 AND A.facid = P.facid
)

75