TOPIC 2:
DATA MODELLING
LESSON LEARNING OUTCOME
At the end of the class, students should be able to :
Determine entity
relationship diagram
Entity Relationship Diagram
An entity relationship diagram (ERD) shows the
relationships of entity sets stored in a database.
In 1967 Peter Chen developed the ER Model.
It is a high level data model used for developing the
conceptual design of the database.
ER diagram helps designers understand and specify the
desired components of database and the relationship among
them.
ENTITY
An entity is an object or concept about which you want
to store information
t is a real world item / concept that can exist on it’s own.
It may be an object with physical existence ( person, house)
or it may be an object with conceptual existence (company
,job, university course)
STUDENT
WEAK ENTITY AND STRONG
ENTITY
Entity type that doesn’t have a key attribute on it’s own is called
weak entity and the Regular entity types that have key value is
called strong entities.
Entity belonging to weak entity type is identified by being related
to specific entities from another entity type in combination with
one of their attribute value.
We call this entity type as identifying or owner entity type
(Parent/Dominanat Entity type)
The relationship that connects owner entity type to weak entity is
called Identifying relationship.
The weak entities are also called as child entity type or
subordinate entity type
Weak entities have always a total participating constraint because
they cannot be identified without an owner entity.
Relationship
1 N
EMPLOYEE HAS DEPENDENTS
Dependent-
name
Employee
ATTRIBUTE
Attributes are the properties that describe the entities.
Attribute names are enclosed by ovals and connected to their
entities by single line.
Set of attribute values of a given attribute is the value set or
domain
Ssn
EMPLOYEE Salary
Bdate
SIMPLE ATTRIBUTE COMPOSITE ATTRIBUTE
• Cannot be split in to further • Can be divided in to smaller
attributes(indivisible) subparts which represent more
• Also known as Atomic attribute basic attributes with
• Ex: Ssn(Social Security independent meaning
Number) • Even form hierarchy
• Value of the composite attribute
is the composition of the
constituent simple attributes
• Ex: Address
SIMPLE KEY ATTRIBUTE
Rollno Name
STUDENT
COMPOSITE KEY ATTRIBUTE
Date
No-of-
Flightid
passengers
Flightno
FLIGHT
SINGLE VALUED
MULTI VALUED ATTRIBUTE
ATTRIBUTE
• Attributes having single • Attribute having set of values
value for particular entity. • Denoted by double circled
• Ex - Age oval
• Ex: Phone-number, College-
degree
DERIVED ATTRIBUTE STORED ATTRIBUTE
• Attribute values are derived • Attributes from which the
from another attribute. values of other attributes are
• Denoted by dotted oval derived
• Ex - Age • Ex: Bdate
Composite-
hierarchy
City
Street State Address
Primary-key
Street-add
Apartment-no Ssn
EMPLOYEE Salary
Bdate
Phone- Age
no stored
derived
multivalued
IDENTIFIER ATTRIBUTE OR KEY
ATTRIBUTE
Key • Data item that allows us to uniquely identify individual occurrences or an
entity type.
Alternate key • A candidate key not used for primary key.
Candidate key • Minimal super key with the property of irredusability and uniqueness
Primary key • An entity type may have one or more possible candidate keys, the one
which is selected as primary key.
Composite key • candidate key that consisting of two or more attributes
Foreign key • An attribute or set of attribute that matches the candidate key or other or
same relation
The name of each primary key attribute is underlined.
COMPLEX ATTRIBUTE
Composite and mutivalued attribute can also be nested
arbitrarily to form complex key.
Areacode
Phoneno
Street
Phone
Apartment
Street-add
no
Address-Phone State
Address
City
Zip
RELATIONSHIP TYPE
When attribute of an entity refers to another entity type there
exists relationship
Ex: attribute ‘Manager’ of DEPARTMENT refers to another
employee who manages the department.
Attribute ‘Controlling-dep’t refers to another department that
controls the project.
In ER diagram these references must be represented as
relationship and not attributes.
Ex: Relationship ‘WORKS-FOR’ between EMPLOYEE and
DEPARTMENT associates each employee with the
department for he works.
The relationship is often denoted by diamond symbol and are
usually verbs.
Each relationship instance in relationship set WORKS_FOR
associates one EMPLOYEE entity and one DEPARTMENT
entity.
WORKS
EMPLOYEE DEPARTMENT
-FOR
ATTRIBUTE OF RELATIONSHIP
TYPE
Relationship can also have attributes
Ex: Hours for WORKS-ON relationship between
EMPLOYEE and PROJECT
WORKS-
EMPLOYEE PROJECT
ON
Hours
Attributes of 1:1 or 1:N relationship can be migrated to one of
the participating entity types.
Ex: Start-date attributes of MANAGES can be attribute of
either DEPARTMENT or EMPLOYEE though conceptually it
belongs to manages.
1 MANAGE 1
EMPLOYEE DEPARTMENT
S
Start-date
CARDINALITY CONSTRAINTS
The number of instances of one entity that can or must be associated
with each instance of another entity.
If we have two entity types A and B, the cardinality
constraint specifies the number of instances of entity B that
can (or must) be associated with entity A.
Four possible categories are
One to one (1:1) relationship
One to many (1:m) relationship
Many to one (m:1) relationship
Many to many (m:n) relationship
Cardinality Constraints
We express cardinality constraints by drawing either a directed line (),
signifying “one,” or an undirected line (—), signifying “many,” between the
relationship set and the entity set.
E.g.: One-to-one relationship:
A customer is associated with at most one loan via the relationship borrower
A loan is associated with at most one customer via borrower
One-To-Many Relationship
In the one-to-many relationship a loan is associated with at most one customer
via borrower, a customer is associated with several (including 0) loans via
borrower
Many-To-One Relationships
In a many-to-one relationship a loan is associated with several (including 0)
customers via borrower, a customer is associated with at most one loan via
borrower
Many-To-Many Relationship
A customer is associated with several (possibly 0) loans via borrower
A loan is associated with several (possibly 0) customers via borrower
one-to-one
MANAGE
EMPLOYEE DEPARTMENT
1 1 S 1
one to many
WORKS-
EMPLOYEE DEPARTMENT
FOR
N 1
Many
WORKS-
EMPLOYEE PROJECT
ON
M N
ROLE NAME
Some entities participate more than once in a
relationship type in different roles.
Role name represents role that a participating entity from the entity type
plays in the relationship.
Ex: Employee plays the role of supervisor as well as
supervisee.
If the participating entity types are distinct then there
is no need for role name else role name is a must.
PARITICIPATION
CONSTRAINTS(OPTIONALITY)
Specifies if existence of an entity depends on it being related to another
entity via relationship.
Specifies minimum number of relationship instances each
entity can participate in .
This is called minimum cardinality constraint.
Two type of the participation are : Total And Partial
TOTAL
PARTICIPATION
PARTIAL
Ex: if company policy says that every employee must work for the
department then participation of employee in work-for is total.
WORKS-
EMPLOYEE DEPARTMENT
FOR 1
N
Total participation is also called existence dependencies.
Every entity in total set of employee must be related to a
department via WORKS-FOR
But we can’t say that every employee must MANAGE a department
.
Hence relationship is partial.
Total participation is indicated by double line and partial
participation by single line.
DEGREE OF A
RELATIONSHIP
It is the number of entity types that participate in a relationship
If there are one entity type involved it is a unary
relationship type
If there are two entity types involved it is a binary
relationship type
If there are three entity types involved it is a ternary
relationship type
It is possible to have a n-array relationship (quaternary)
SALESASSIST SELLS PRODUCT
CUSTOMER ternary-relationship
Unary relationships are also known as a recursive
relationship.
It is a relationship where the same entity participates more
than once in different roles.
In the example above we are saying that employees are
supervised by employees.
p er v isor
su
SUPERVI
EMPLOYEE
SION
superv
isee
DEALING WITH TERNARY RELATIONSHIPS
Consider the below relationship. ]It is no longer clear which sales
assistant sold a customer a particular product.
So try replacing the ternary relations hip with an entity type and a
set of binary relationships.
SALES-
SELLS PRODUCT
ASSISTANT
SELLS SELLS
CUSTOMER
The relationship sells can become the entity type
sale .
So a sales assistant can be linked to a specific
customer and both of them to the sale of a particular
product.
MA INV
SALES-
KE SALE OLV PRODUCT
ASSISSTANT ES
S
REQ
UEST
CUSTOMER
CONSTRUCTING AN ER MODEL
Before beginning to draw the ER model, read the
requirements specification carefully.
Document any assumptions you need to make.
[Link] entities
• list all potential entity types. These are the object of interest
in the system. It is better to put too many entities in at this
stage and them discard them later if necessary.
[Link] duplicate entities
• Ensure that they really separate entity types or just two
names for the same thing
• Also do not include the system as an entity type
• e.g. if modelling a library, the entity types might be
books, borrowers, etc.
• The library is the system, thus should not be an entity
type.
[Link] the attributes of each entity
• Ensure that the entity types are really needed.
• Are any of them just attributes of another entity
type?
• If so keep them as attributes a nd cross them off
the entity list.
• Do not have attributes of one entity as attributes of
another entity!
[Link] the primary keys
• Which attributes uniquely identify instances of
that entity type?
• This may not be possible for some weak entities.
[Link] the relationships
• Examine each entity type to see its relationship to
the others.
[Link] the cardinality and optionality of the
relationships
• Examine the constraints betwee n participating
entities.
[Link] redundant relationships
• Examine the ER model for redundant
relationships.
ER modelling is an iterative process, so draw several versions, refining
each one until you are happy with it. Note that there is no one right
answer to the problem, but some solutions are better than others!
EXAMPLE
“A Country Bus Company owns a number of busses. Each bus is
allocated to a particular route, although some routes may have several
busses. Each route passes through a number of towns. One or more drivers
are allocated to each stage of a route, which corresponds to a journey
through some or all of the towns on a route. Some of the towns have a
garage where busses are kept and each of the busses are identified by the
registration number and can carry different numbers of passengers, since
the vehicles vary in size and can be single or double-decked. Each route is
identified by a route number and information is available on the average
number of passengers carried per day for each route. Drivers have an
employee number, name , address, and sometimes a telephone number.”
ENTITIES
Bus - Company owns busses and will hold information
about them.
Route - Buses travel on routes and will need described.
Town - Buses pass through towns and need to know about
them
Driver - Company employs drivers, personnel will hold
their data.
Stage - Routes are made up of stages
Garage - Garage houses buses, and need to know where
they are.
RELATIONSHIPS
A bus is allocated to a route and a route may have several
buses.
Bus-route (m:1) - is serviced by
A route comprises of one or more stages.
route-stage (1:m) comprises
One or more drivers are allocated to each stage.
driver-stage (m:1) is allocated
A stage passes through some or all of the towns on a route.
stage-town (m:n) passes-through
A route passes through some or all of the towns
route-town (m:n) passes-through
Some of the towns have a garage
garage-town (1:1) is situated
A garage keeps buses and each bus has one `home' garage
garage-bus (m:1) is garaged
ER DIAGRAM
ATTRIBUTES
Bus (reg- no,make,size,deck,no-pass)
Route (route-no,avg-pass)
Driver (emp - no,name,address,tel-no)
Town (name)
Stage (stage - no)
Garage (name,address)
ALTERNATIVE DIAGRAMATIC NOTATIONS
FOR ER MODELS
Symbols for entity type attribute and relationship
Displaying attributes
Various (min, max) notations
CASE IN POINT: E-R MODEL FOR
ONLINE SALES FOR BIGHIT VIDEO
“ BigHit Video Inc. wants to create an information system for online
sales of movies in both DVD and videotape format. People will be
allowed to register as customers of the online site and to update their
stored information. Informa-tion must be maintained about customers ’
shipping addresses, e-mail ad-dresses and credit cards. In a single sale,
customers will be allowed to purchase any quantity of videos. The items
in a single sale will be shipped to a single address and will have a single
credit card charge A customer will be provided with a virtual shopping
cart to store items to be purchased. As each item is selected, it is added
to the shopping cart. When the customer finishes shopping, he will be
directed to a checkout area where he can purchase all of the items in the
shopping cart. At this time, payment and shipping information is entered.
Once the sale is complete, the shopping cart will be deleted and the
customer will be sent a receipt by e-mail.”
ENTITY ATTRIBUTE CONSTRAINTS
[Link] accountId Key
lastName Not null
firstName
shippingAddresses Multivalued
IDENTIFY ENTITY AND
composite with
components name,
street, city,
state, zipcode
emailAddress
creditCards Multivalued
composite with
components type,
TRIBUTES
accountNumber ,
expiration
password Not null at least 6
characters
ENTITY ATTRIBUTE CONSTRAINTS
2. Movie Movieid Key
Title
Genre
Media Either “dvd” or
“videotape”
determines subclass
3. DVD Languages Subclass of Movie
Videoformat
Captioning
4. Videotape Format Subclass of Movie
Soundtrack
ENTITY ATTRIBUTE CONSTRAINTS
5. Sale SaleId Key
Totalcost
DateSold
CreditCard Composite with
components type,
accountNumber ,
and expiration
6. ShoppingCart CartId key
DateCreated
Relationshi Entity Entity Cardinali Attributes
RELATIONSHIPS
p Type Class Class ty Ratio
Purchases Customer Sale one-to-
many
Includes Sale Movie many-to- quantity
many
Selects Customer Shopping one-to-
Cart many
Includes Movie Shopping many-to- quantity
Cart many
THANK
YOU