Relational Data Model

The relational model represents the database

as a collection of relations. Each relation
resembles a table.

In a relational model a row of a table is called

a tuple, a column header is called an attribute
and table is called a relation.
 Attributes

Students Student Sname Department Year

No
S1 Mala Physics 2
Tuple S2 Nimal Chemistry 1
S3 Sunil Maths 3
Each value in a tuple is an atomic value. Ie. It
is not divisible into components. Therefore,
composite and multi-valued attributes are not
allowed. Multi valued attributes must be
represented by separate relations and
composite attributes are represented by their
simple component attributes.
 Key Attributes

Super key
A super key is a set of attributes which uniquely
identifies a tuple in a relation.

Example:
If Sno, sname, dept, degree, years are the
attributes of the entity type Student, the super
keys are:
• {Sno}
• {Sno, Sname}
• {Sno, Sname,dept}
• {Sno, Sname,dept, degree}
• {Sno, Sname,dept, degree,years}
Candidate Key
A candidate key is an irreducible set of attributes
which uniquely identifies a tuple in a relation.

Note:

A candidate key is a super key, but a super key is

not necessarily a candidate key. A super key may
be reducible, meaning that even if one or more
attributes are excluded, the remaining attributes
still uniquely identifies a tuple.
Primary Key
One of the candidate keys, is assigned as the
primary key. The primary key is used to
identify tuples in a relation.

Foreign Key
A field in a table which links another related
table Ie. if field X is the primary key of table A
and also appears in table B, it is a foreign key
in table B.
 Table A

X
PK
Foreign Key
Table B

Y X
 Relational Model Constraints

• The various types of constraint that can be

specified on a relational database schema
includes:
• Domain constraints
• Key constraints
• Entity integrity constraints
• Referential integrity constraints
• Domain constraint
Domain constraints specify that the value
of each attribute must be atomic.

• Key constraint
No two tuples can have the same
combination of values for all their
attributes.
• Entity Integrity Constraint
This constraint states that no primary key
value can be null. This is because the
primary key value is used to identify
individual tuples in a relation.
• Referential Integrity Constraints
A tuple in one relation that refers to another
relation must refer to an existing tuple in that
relation.
For example:
The attribute Dnum of employee gives the
department for which each employee work.
Therefore, its value in every employee tuple
must match Dno value in some tuple in the
department relation.
Department Employee FK
Dno Dname Eno Ename Dnum
1 Admin 100 Perera 2

2 Accounts 200 Silva 1

3 Marketing 300 Yapa 4

Violating the referential

integrity constraint
 Schema

The description of the database is called the

database schema. A database schema is
specified during database design and is not
expected to change frequently. A displayed
schema is called a schema diagram.
A schema diagram displays only some aspects of
a schema, such as the name of the record
types, data items and some types of constraints.
A sample schema diagram of a customer
database us given below.
Customer

CustID Name Address Phone

Product

ProdID Description Price

Orders

OrderID CustID Orderdate Shipdate

Conversion of ER model to
Relational Schema
• Step 1:
For each strong entity type in the ER diagram
create a relation that includes all the simple
attributes of the entity.
• Any composite attributes must be
represented as basic attributes
• Omit multi-valued /derived attributes
• Step 2
For each weak entity type, create a new
relation
• Include all the attributes of the weak entity
• Add the primary key of the strong entity
(owner entity) as a foreign key.
• The primary key of the weak entity will be a
combination of the owner entity and the
partial key of the weak entity.
 fname
sname
Eno Ename dname

1 n
Employee has Dependent

telephone Salary relationship

Employee
Eno fname sname telephone salary

Dependent
Enum dname relationship
FK
• Step 3
For 1:1 relationships, select any side of the
relation (but it is better to choose an entity type
with a total participation)

• Indicate the primary key on one relation as

the foreign key in the other relation (selected
entity type).

• Include all simple attributes of the 1:1

relationship type as an attribute of the chosen
entity type.
 fname
sname
Eno Ename Dno Dname
1 1
Employee manages Department

telephone Salary No of emp.

Location
mstartdate

Department

Dno Dname mstartdate mEno

FK
 fname
sname
Pno Pname ACno Model
1 1
Pilot Flies Aircraft

DOB Catagory Type

No_of_seats
No_of_hours

Pilot

Pno Fname Sname DOB Catagory

Aircraft

ACno Model Type No_of_seats Pnum No_of_hours

FK
• Step 4
For 1:m relationships select the many side of
the relation and indicate the primary key of the
one side as the foreign key on the many side.
 CN
INO Titl Name O CName
e

Lecturer 1 1 Teac
n m Course
h
Address
Telephone Duration

Lecturer

Ino Title name address tel

Course
Cno Cname Duration Inum
FK
• Step 5
For m:n relationships create a new relation
• Contain the primary keys of the two relations
and
• Any attributes on the relationship type.

• Step 6
For multi valued attributes create a new relation
containing the:
• Multi valued attribute and
• The Primary key of the entity
 Name hours PN
EN
O PName
O

Employee 1 m Work
nn Project
s on
Address
Telephone location

Works_on Project_loc
Enum Pnum hours Pnumber Ploc

FK FK FK
• Step 7
For n-ary relationship types create a new
relation
• Include attributes of the relationship type
• All primary keys of the entities participating in
the n-ary relationship.
Sname Qty Pno

Supplier supply Project

Part

partno

sname Pno partno Qty

FK FK FK
• Example 1:
Design a database schema that
corresponds to the following Library
database ER diagram.
 Address
Pname
BookID Title

Authors
Publisher

Books Publishes 1
m
m Phone

Editioncopies
Borrows
n
Phone
Date_in
Member
Date_out
Address

MemberID
Name
Books
BookID Title Pnum
Borrow
FK
BID MID Date_in Date_out
Publisher
FK FK
Pname Address Phone
Book Authors

Member BId Author

FK
MemberID Name Address Phone

Book Copies
BId Editioncopies
FK
• Example 2:
Design a database schema that
corresponds to the Hospital ER diagram.
 Pname
address
Wname
Adno
Wno
Pno Tel
m
Ward hosts Patient
1
1 m m
Date_a

assign prescribed

Spec Dosage
m 1 n
Doctor Treatment

Tel
Dno Tno
address
Dnname description
Ward
WNo Wname Phone

Patients
AdNo Pname Address Tel D_Ad Wnum Dnum

FK
FK
Doctor
Dno Dname Spec Addr Tel WN
FK
Prescription
Treatment
Ano Tnum Dosage
Tno Descr
FK FK
• Example 3:
Design a database schema that
corresponds to the Order entry
system ER diagram.
 Cno shipaddr
climit
discount Ino
balance Customer description

1
Item
n
places QH

Date_o
Order items
m
Order m Qty

ONo
sdate
Customer
Cno balance climit discount

Item
Ino description

Order
Cnum ONo Odate sdate
FK

Ship_addr Items_in_order
Cnum shipaddress Ino ONo Qty QH

FK FK FK
• Example 3:
Design a database schema that
corresponds to the Company ER diagram.
Emp No Address First Name
Location
Mid Initials
supervise
Dept No
Last Name
Emp Name Dept Name
1 m
m
Employee works 1
1 Department
Start d 1
1
m 1
Hours manage Phone

Salary
has controls
DOB Works on

n m Proj No
m Depd Name
Project Proj Name
DOB Location
Dependent
Relation
 Employees
Emp_no Fname Mname Sname Address DOB Dno S_emp_no
FK FK
Department
Dept_no Dept_name Phone stdt Mgr_e_no
Works_on
FK
Project E_num Pno Hours

Proj_no Proj_name location Dno

FK
Dept_loc
Dependents
Dno D_loc
E_num Dependent_n DOB Relationship
FK