Topic 2

Data Modelling and

Relational
Database
 Data Models
Entity Relationship (E-R) Model
► 1. Introduction to various data
models
► 2. Record based & Object based
► 3.Cardinality Ratio & Relationships
► 4.Representation of entities,
attributes, relationship attributes,
relationship set
► 5.Generalization, aggregation
► 6.Structure of relational 2
1. Introduction to various data
models
►A database model is a collection
of conceptual tools for describing
data.

► Data modeling is used for

representing entities and their
relationship in a database.
 Types of Data
Models
► 1. Hierarchical Model
► 2. Network Model
► 3. Entity-Relationship
model
► 4. Relational Model
► 5. Object Oriented
Model
► 6. Distributed Model
 1. Hierarchical
Modeldata model was
► Hierarchical
developed by IBM in 1968.
► This model is like a structure of a
tree.
► This model uses parent
child relationship.
► In this model parentchild
relationship is
one to many.
► This restricts a child segment to
have only one parent segment.
Grand
Parent

Parent

Child
 Hierarchical
e.g. In this model each entity has only one parent but can have
Model
several children . At the top of hierarchy there is only one entity
which is called Root.
 Cont
d.
►A parent element have many child
elements but a child element
cannot have many parent
elements.
► That is this model cannot
represent many to many
relationship.
 Advantages of Hierarchical
Model
► 1. It is simple & natural method
of implementing record
relationship.
► 2. It is useful when there is some
hierarchical character in
the database.
 Disadvantag
► 1 This es cannot
model all
. relationship
represent that occur in world
the
► 2.Itreal
cannot represent many .to
relationshi many
► 3 p.Insert Anomaly : It is not possible
. insert
to data about new
► dependent.
4.Delete Deletio in this
Anomaly
leads to: nof manymodel
loss
info rmation. necessary
► 5. Update Anomaly : Updation
operation leads several inconsistencies.
 Network
Model
► In network model data is
represented by records using
links among them, which can
be viewed as pointers.
► Network model is many to
many (M : M) , Many to One
(M : 1), One to one (1 : 1)
relationship model.
e.g
.

Custome Product
r Table
Table

Order Order
Table
Table
 Network
e.g. In the network model, entities are organized in a graph, in
Model which some
entities can be accessed through several path
 Advantages of Network
Model
► 1.This model is useful representing
for such records which many
have many
► relationship.
2. Problem of Inconsistency todoes
not exist in this model.
► 3. Searching of records is easy
because there are multiple
access paths.
 Disadvantages of Network
Model
► All the records are maintained using
pointers and hence the whole
database structure becomes very
complex.
► Insertion, deletion and updation of any
record would require pointer
adjustments.
2.2 Record Based(Relational
Model )
► In a Relational Database Model, data is
organized in the form of rows and
columns similar to table.
► Tables are referred as relations in
this model.
► Rows are referred as tuples.
► Columns are referred as Attributes.
 Cont
d.
► A relational is defined as
model
database that allowsa you to group
its data items into one or more
independent tables that can be
related to one another by using
fields common to each related
table.
 Relational
e.g. In this model, data is organized in two- dimensional tables called
Model relations.
The tables or relation are related to each other.
 Advantages of Relational
Model
► This model can be efficiently used
with limited main memory &
Processing capability.
► Relational database model is only
concerned with data & not with
structures, which improves the
performance.
► Instead of pointers Record
relationship
is implemented, hence it is very
simple.
 2.2. Object Based Database
►Model
Object Oriented database are dependent
on the
object oriented programming
paradigm.
► This model creates classes, objects,
structures, call methods.
► Designed to store, retrieve & Manage
objects created by programs written in
language like C++ or Java.
► Designed to provide object oriented
facilities to users of languages like C or
Pascal.
Object Based Database
Model
 Advantages of
► This
OOD
model work on Nested
Structure applications which is
complex in the form of objects
instead of tables and records.
► i.e. : CAD/CAM, aerospace science.
► Relational database have only limited
data types like integer, char, text,
but OOD allow arbitrary data
types and sub classing.
 5. Distributed Data
► In
Model
a distributed system communication
& exchange of data among one
another is a main facility.
► Data Sharing : User operating on one
computer can work on any another
computer.
► Distributed Control: Responsibility of
control is divided among local
administrator.
► Reliability & Availability : Even if one site
fail, the remaining sites can continue
working.
 2.4 Representation of Entities, attributes,
relationship attributes, relationship set

Entity
Attributes
Relationship
Key attributes

24
 ENTITY

Entity is a thing or an object in the real

world that is distinguishable from all
other objects.
An Entity is a class of Person, Places
Objects, events or concepts about
which we need to collect and store
data.
For Example : Students is an entity
25
 Contd.

An entity has a set of properties may

uniquely identify an entity.
For example : Students is an entity
with properties – roll-no, class
roll-no class

Students

26
 Weak & Strong Entity

Weak Entity : is the one whose existence

depends on another entity.
i.e. Consider an entity marks which
represents the marks of students. Now the
existence of marks depends upon the entity
type student. Marks

Students

27
 Strong Entity

An entity set which has a primary key is

termed as a strong entity set.

roll-no class

Students

28
 ENTITY SET

Entity set is collection of entities of the

same type.
e.g. The set of all students
Ashwin 1122 MCA-I
Ashish 2211 MCA-II
Ashwini 1123 MMM-I
Archana 2212 MBA-II

29
 Attributes

The characteristics of entity are

called attributes.
For example :
an entity say client can have characteristics
like name, address, phone number
etc. Attributes

name address Phone no.

Entity
30
 Different types of attributes

1. Simple and Composite Attribute

2. Single-Valued and Multi-valued
Attributes
3. Derived attribute
4. Null attribute

31
 Simple and Composite Attribute

Simple Attributes can not be divided into subparts.

i.e. Consider customer entity with following
attributes customer
Customer Customer Customer Customer
ID Name Street City

1001 Ram Sinhgad Pune

32
 Composite Attributes

Composite attributes can be divided into

subparts.
e.g. Customer entity with Customer-address
attribute
Customer-address

Street City State

Street No Street Name Apartment No.

33
Single-Valued and Multi-valued Attributes

Single Valued attributes has single value for

particular entity.
e.g. loan number is attribute for
loan entity refers to only one loan
number.

Multi-valued attributes has a set of values.

e.g. Consider an employee entityset
the attribute Phone-number.
An employee may have 0,1 or several
phone numbers. 34
 Derived attribute
The value of this type of attribute can be derived
from the values of other related attributes or
entities.
i.e. suppose customer entity set has an attribute
age, which indicates the customer’s age.
If the customer entity has also an attribute date-of-
birth, we can calculate age from date-of-birth
and the current date. Thus age is
attribute. Dt_of_Birth derived Current_Dt.
Age

Customer 35
 Null attribute

Null value is used when an entity does

not have a value for an attribute
Null can also designate that an attribute
is
unknown. (missing or not known)
E.g. one may have no middle name,
or A null value for apartment-no

36
 Key Attribute

The key attribute is an attribute that

uniquely identifies an entity in the
entity set.
i.e. Employee_code can be the key
attribute for entity set
Employee, because it uniquely
identifies each
employee entity.

37
 KEYS

The concept of keys helps us

to distinguish the entities and
relationship.
A Key is data item that
exclusively identifies a record.
i.e. Account_number, Product_code,
Employee_number and
Customer_number are used as key
fields because they specifically identify
a record stored in a database.
38
 Super Key
A super key is set of one or more attributes
whose combined value uniquely identifies
the entity.
i.e. For an entity set Employees the set of
attributes (emp_id, emp_name) can be
considered as a super key.
( emp_id, emp_name )  Super key
(Emp_name can be same )

39
 Candidate Key
A Candidate key is an attribute that uniquely identifies a
record.
candidate key is a column in a table which has the
ability to become a primary key.
e.g. For account entity acc_no is candidate key. If
combination of cust_name and branch_name is sufficient
to distinguish among members of the account then
(cust_name, account_number, branch-name) is also a
candidate key.
acc_no cust_name branch_name

Candidate Key 40
 Primary Key
The primary key identifies every record in
table uniquely and must never be the same
for two records.
The primary key should be chosen such that its
attributes are never, or very rarely changed.
Address field of a person should not be a
part of the primary key. Since it is likely to
change.
Emp_code are Primary key which
guaranteed to never change.
41
 Composite Key
A key formed by the combination of 2 or
more columns is the composite key.
A composite Key can be either Primary or
Unique Key. More then One Key columns
are said to be composite keys
When a record cannot be uniquely
identified by a single field, in such case a
composite key is used. e.g.
Supplier_id Part_id

Composite Key 42
 Foreign Key
An attribute or combination of attribute in a
table whose value match a primary key in
another table.
The purpose of the foreign key is to
ensure referential integrity of the data. e
e.g.
Prod_code Prod_name Supp_code

Foreign Key

Supp_code Supp_name
43
 Notation for E-R Diagram
Symbol Meaning

Rectangles Which represents entity

set.

Double Which represent weak

Rectangles entity set.

Diamonds Represents Relationship

sets.

44
 Notation for E-R Diagram
Symbol Meaning
Ellipses Which represents
Attributes
Which link attributes
Lines to entity sets &
entity sets to
relationship sets.
Ellipse with Which represent key
Line attribute

45
 Contd.

Symbol Meaning
Double Ellipses Which represent
multi-valued
attributes
Dashed ellipses Which denote
double lines derived
attributes

46
 Relationships

A relationship connects two or more

entity sets.
It is represented by a diamond, with lines
to each of the entity sets involved.
i.e.

Instructor Teaches Student

47
 Contd.

There are 4 types of relationships

1. One-to-one
2. One-to-Many
3. Many to one
4. Many-to-Many

48
 One-to-one Relationship (1 : 1)

A one-to-one relationship is an association

only between two entities.
i.e. In a university each department has
only one HOD, moreover, one faculty
cannot HOD more than one department

1
Department HOD
1

49
 One-to-Many Relationship (1 : M)

A one-to-many relationship exists when one

entity is related to more than one entity.
i.e. Father may have many children but
a child has one father.

1
Father Children
M
50
 Many-to-Many Relationship (M:M)

A many-to-many relationship describes

entities that may have many relationships
among each other.
i.e. One customer may buy many items and
one item may be bought by many
customers.

M
Customer Item
M
51
 One-To-Many Relationship

In the one-to-many relationship

a loan is associated with at most
one customer,
a customer is associated with
several loans

52
 Many-To-One Relationships

In a many-to-one relationship a loan is

associated with several customers,
a customer is associated with at most one
loan.

53
 Many-To-Many Relationship

A customer is associated with several

loans . A loan is associated with several
customers.

54
 E-R Diagram with different Attributes

Middle-name last-name
First-name

name Composite
Attribute
customer-id
Customer
Multivalued attributes Derived
Attribute
phone- age
date-of-birth
Number
55
 Weak Entity Sets

We depict a weak entity set by double rectangles.

We underline the discriminator of a weak entity
set with a dashed line.
Primary key for payment – (loan_number,
payment_number)

56
 Attribut
ERD:- es

Cust_ Cust_ Dat Acc_ Balanc

name No e no e

Custom Cust_ Accoun

er Acct t

Stat Cit
e y Entity sets

Entity- Relationship Diagram for customer account

 E-R diagram:-

Customer Customer
_State Loan_ Amou
_name nt
number
Customer
Customer
_id
_City

Customer borrow Loan

er

E-R diagram corresponding to customer and loan

 Database Design using E-R Model:-
The entityTeacher and student are Mapped on two separate
table say Teacher and Student will map onto fields
in the relevant table. The key attributes becomes

the key field when mapped in the form of a tables

T_N Nam Roll_N Nam

O e o e

TEACHE Teache STUDEN

R s T

Addres Salar Clas Mark

s y s s
Mapped onto Mapped onto
table table
Teacher. Student.
dbf dbf

T.No Name Address Salary Roll_No Name Class Marks

Mapping Entities and Attributes on a

table
E-R DIAGRAM

61
 E-R Diagram for Insurance Company

Construct E-R diagram for a insurance

company that has the set of customers
each of whom owns one or more cars
each car has associated to any number of
accidents regarded.
What are the entity’s ?
1. Insurance company
2. Customer
3. Car
62
 E-R Diagram
Insurance Insurance
Customer_id customer_name
No, name

Insurance Customer
member customer_add

company

company_ad owns
d

Car

No. of
car name Car no. accidents
63
 2. E-R Diagram for Banking Enterprise

What are the entity’s of

Banking enterprise ?
Customer

Employee
Branch
Loan
Payment
Account 64
E-R Diagram for a Banking Enterprise

65
 E-R Diagram for Practice

Construct an E-R Diagram for a

Hospital with set of medical
patient and set of medical Doctor
associated with each patient a
log of various test and
examination conduct.

66
 Cont..

What are the entity’s for

Hospital? Hospital
Patient
Doctors

67
 E-R Diagram

Tes Examination ID
ID Name t

No.of beds Hospital Admit Patient Name

s

Appoint Treatme
nt

Doctors
Name
Specialization
Reg.No.
68
E-R Diagram for Practice

Draw the E-R diagram of the

registration process of the
student in a particular course.
Convert the E-R Diagram into
tables also.

69
E-R Diagram for Practice

What are the entity’s of registration

Process ?
Student
Course

70
 E-R Diagram

Stud_nam stud.add Stud. Roll_no. course_nam Fee

e e

Student Register
s
Course

Student Ph.No

71
 Tables of E-R Diagram
1. Student table
Stud roll Stud name Stud Stud Phone No.
no. Address
integer Character Character Number

2. Course
table Course name Fee

Character Number

72
SPECIALISATION

73
 Specialization (Top Down Process)
Specialization is the result of taking
subsets of a higher level entity set to
form lower level entity sets.
Specialization is the process of
defining a set of subclasses of an
entity type.
Specialization is the top down
process.

74
 Contd.
For example, Specialization of Employee
entity.
Attributes of Employee Entity.
emp_id, emp_name, emp_dob,

emp_name
emp_id emp_dob

employee

75
 Contd.
A employee may be further classified
as one of the following
Teaching
Non-teaching
Each of these employee types is
described by a set of attributes that
includes all the attributes of entity
set employee plus possibly additional
attributes.
76
 Contd.
i.e. Teaching entity be described
may
further by the attribute emp_vacation,
where as No teaching entity may be
described further by the attribute emp_EL.
The Process of designating sub
groupings within an entity set is called
Specialization.
The specialization of employee allows us
to distinguish among employee according
to whether they are Teaching or Non-
Teaching. 77
 Contd.

emp_id emp_name emp_dob

employee

ISA
emp_EL
emp_vacation

Teaching Non-Teaching

Employee “is a” having Teaching & non-teaching entity.

Teaching & Non-Teaching entity is a SUBCLASS of Employee.
78
 Flo Locatio Radiatio
w n n

Owne Pressur
r Plan e
t
Specialized
as

Owne Locatio
r n
Plan
t

ISA

Stea Nucle Hydr

m ar o
Base Based Base
d d

Pressure Radiatio Flo 7

9
GENERALIZATION

80
 Generalization
Generalization is the result of taking the
union of several lower level entity sets to
produce higher-level entity set.
(The UNION operator returns results from both queries after
eliminating duplication)
Generalization is the exact
reverse process of Specialization.
Generalization is Bottom-Up Process.
Generalization is used to emphasize
the similarities among lower-level
and to hide the
entity differences.
sets 81
 Contd.
For example : There is similarities between
saving account entity set and current account
entity set in bank account.
They have several attributes in common.
This commonality can be expressed by
Generalization.
Interest_rate Name
Name Acc_no overdraft_amt
Acc_no

Saving Account Current

Account
82
 Contd.

acc_no name

Account

ISA
overdraft_amt
Interest_rate

saving account current account

The Account entity set is the superclass of saving & current

account Subclass.
83
 Contd.

Numb nam
er e

Vehicle

IS
A

Bus Car Bike

The Vehicle entity set is the super class of Bus &

Car, Bike
84
Subclass.
Aggregation
Aggregation is the process of compiling
information on an object
The relationship set works on relation the
entity sets employees branch and job is a
higher level entity set relationship manages
between works-on and manager to
represents who manages what task.

85
 Contd..
Job

Employee Works Branch

on

Manage
s

Manager

ER-Diagram with 8
Aggregation 6
 Database normalization

employee_id name job_code job state_code home_state

E001 Alice J01 Chef 26 Michigan

E001 Alice J02 Waiter 26 Michigan

E002 Bob J02 Waiter 56 Wyoming

E002 Bob J03 Bartender 56 Wyoming

E003 Alice J01 Chef 56 Wyoming

All the entries are atomic and there is a composite primary key (employee_id,
job_code) so the table is in the first normal form (1NF).
But even if you only know someone's employee_id, then you can determine
their name, home_state, and state_code (because they should be the same
person). This means name, home_state, and state_code are dependent
on employee_id (a part of primary composite key). So, the table is not in 2NF.
We should separate them to a different table to make it 2NF.
Example of Second Normal Form (2NF)

employee_id job_code
E001 J01
E001 J02
E002 J02
E002 J03
E003 J01

employee_id name state_code home_state

E001 Alice 26 Michigan

E002 Bob 56 Wyoming

E003 Alice 56 Wyoming

job_code job
J01 Chef
J02 Waiter
J03 Bartender
To take this a step further, we should separate them again
to a different table to make it 3NF.
employee_roles Table
employee_id job_code
employees Table
E001 J01
E001 J02 employee_id name state_code

E002 J02
E001 Alice 26
E002 J03
E003 J01 E002 Bob 56

E003 Alice 56

job_code job
J01 Chef state_code home_state

J02 Waiter
26 Michigan
J03 Bartender

56 Wyoming
 SUMMARY
Specialization is the result of
taking a subset of higher level
entity set to form a lower level
entity set.
Generalization is the result of
taking the union of two or more
lower level entity sets to produce
higher level entity set.
90
 SPPU-Questions
1) Explain generalization and aggregation-Oct-2012- 10
2) Explain Various key in relational data models –Ap-
2012-10
3) Explain Various constraints in relational models-Oct-
2012-10.

91