Module Title?
Data Base Design
30/6/2007
WHY
Entity Relationship Diagrams are data-modeling tool.
organizes the data into entities.
defines the relationships between the entities.
shows how information is, or should be, stored and used within a business
system.
Helpful to the analyst to produce a good database structure so that the data can
be stored and retrieved in a most efficient manner.
The success of any organization relies on the efficient flow and processing of
information.
Module Title?
Data Base Design
30/6/2007
The Four Major Components of an Entity
Relationship (E-R) Model
Entities (really entity sets)
Attributes
Relationships
Cardinality
Module Title?
Data Base Design
30/6/2007
Entity
represented by
A data entity is anything real or abstract about which we want
to store data.
Entity types fall into five classes:
roles, events, locations, tangible things or concepts.
Banking system: Customer, Account, Loan.
Airline system: Aircraft, Passenger, Flight, Airport.
Registration system: student, module, classroom, instructor.
Module Title?
Data Base Design
30/6/2007
Naming of Entities
Create a name that is unambiguous
Use the minimum number of words
Do not use abbreviations
Use singular form of the name.
Module Title?
Data Base Design
30/6/2007
Attribute
It is a characteristic common to all or most instances of a
particular entity.
It is also called as property, data element, and field.
E.g. Name, address, Employee Number, pay rate are all attributes
of the entity employee.
Module Title?
Data Base Design
30/6/2007
Attribute types:
Simple and composite attributes.
E.g. composite attribute: address
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
Module Title?
Data Base Design
30/6/2007
Single-valued attributes
Attributes having a single value for a particular entity are
known as Single valued attributes.
For example, the loan-number attribute for a specific loan entity
refers to only one loan number.
Module Title?
Data Base Design
30/6/2007
multi-valued attributes
Attributes that have a set of values for a specific entity are
known as multivalued attributes.
Eg: Consider and employee entity set with the attribute phone-
number.
An employee may have zero, one, or several phone numbers,
and different employees may have different numbers of phones.
Module Title?
Data Base Design
30/6/2007
Simple attributes
Attributes that can not be divided into subparts are known as
Simple attributes.
For example student_id, position etc
Module Title?
Data Base Design
30/6/2007
composite attributes
Composite attributes can be divided into subparts.
For example, an attribute name could be structured as a composite
attribute consisting of first-name, middle-initial, and last name.
Composite attributes help us to group together related
attributes making the modeling cleaner.
Suppose we were to substitute for the customer entity-set
attributes customer-street and customer-city the composite
attribute address with the attributes street, city, state, and zip-
code.
Module Title?
Data Base Design
30/6/2007
Derived Attributes
A Derived Attribute does not physically exist within the
database, but is derived (computed) by an algorithm or
computation.
Example: A person's AGE attribute can be derived by subtracting
the date of birth (DOB) from the current date.
Example: Total cost can be derived by multiplying quantity
ordered by unit price.
Module Title?
Data Base Design
30/6/2007
Primary Key
An attribute or combination of attributes that uniquely
identifies one and only one instance of an entity is called a
primary key or identifier.
E.g. Employee Number is a primary key for Employee.
Module Title?
Data Base Design
30/6/2007
Attributes & Domains
Attributes will also have a domain.
The domain is the attribute's set of possible values.
The domain of the attribute "Grade Point Average" is a real number
between 0 and 4.
The domain of the attribute "Gender" consists of only two possibilities, M
or F (or some other equivalent code).
Attributes may share a domain.
The attribute PROFESSOR_AGE and STUDENT_AGE share the domain
of all possible ages.
Module Title?
Data Base Design
30/6/2007
Relationship
represented by
It is a association between one or more entities.
E.g. Employee worksfor department.
Module Title?
Data Base Design
30/6/2007
Degree of Relationship
Degree of a Relationship is the number of entity types that
participate in it
Unary Relationship or Recursive Binary
Binary Relationship
Ternary Relationship
Module Title?
Data Base Design
30/6/2007
One entity related to another of the same entity type
Entities of two different types related to each other
Entities of three different types related to each other
Module Title?
Data Base Design
30/6/2007
Cardinality
Cardinality is the numerical mapping between entities.
This describes “how many” of one entity are related to “how
many” of another entity.
For example, we can say that a Student entity is related to many
Course entities and a Course entity is related to many Student
entities.
Module Title?
Data Base Design
30/6/2007
One-to-One (1:1)
An example of a one-to-one relationship might be the relationship
between a Company Car entity and an Employee entity.
An employee is assigned one company car and a company car is
assigned to one employee.
Employee Car
Module Title?
Data Base Design
30/6/2007
One-to-Many (1:N)
For example, we might have a relationship between an Instructor
entity and a Course entity.
We might find that an instructor is related to (teach) several
courses while each course has a single instructor.
Instructor Courses
Module Title?
Data Base Design
30/6/2007
Many-to-Many (N:M)
For example, we might have a relationship between an Instructor
entity and a Student entity.
We might find that an instructor is related to (teach) several
Students while each student has a many instructors.
Instructor Student
Module Title?
Data Base Design
30/6/2007
Given a description of the real world enterprise, the following
guidelines help in drawing an ER diagram to represent the data model.
Identify all the entities and their corresponding attributes.
Note that an attribute cannot exist by itself. Eg. address is an attribute, not
an entity, because it doesn’t exist without a customer entity or a student
entity.
Identify all relationships between these entities.
All entities and relationships should be uniquely identifiable usually by
some key attribute(s).
Determine degree of relationships by looking at relationship from both
sides.
Drawing E/R Diagrams
Module Title?
Data Base Design
30/6/2007
A SIMPLE EXAMPLE
A company has several departments. Each department has a
supervisor and at least one employee.
Employees must be assigned to at least one, but possibly more
departments. At least one employee is assigned to a project, but an
employee may be on vacation and not assigned to any projects.
The important data fields are the names of the departments, projects,
supervisors and employees, as well as the supervisor and employee
number and a unique project number.
Module Title?
Data Base Design
30/6/2007
Identify Entities
The entities in this system are Department, Employee,
Supervisor and Project.
One is tempted to make Company an entity, but it is a false
entity because it has only one instance in this problem.
True entities must have more than one instance.
Module Title?
Data Base Design
30/6/2007
Entity Relationship Matrix
Department Employee Supervisor Project
Department is assigned run by
Employee belongs to works on
Supervisor runs
Project uses
Draw Rough ERD
Module Title?
Data Base Design
30/6/2007
Fill in Cardinality
Each department has exactly one supervisor.
A supervisor is in charge of one and only one department.
Each department is assigned at least one employee.
Each employee works for at least one department.
Each project has at least one employee working on it.
An employee is assigned to 0 or more projects.
Module Title?
Data Base Design
30/6/2007
Define Primary Keys
Primary Keys are:
Department Name,
Supervisor Number,
Employee Number,
Project Number.
Module Title?
Data Base Design
30/6/2007
Draw Key Based ERD
There are two many-to-many relationships in the rough ERD above,
between Department and Employee and between Employee and
Project.
Thus we need the associative entities Department-Employee and
Employee-Project.
The primary key for Department-Employee is the concatenated key
Department Name and Employee Number.
The primary key for Employee-Project is the concatenated key
Employee Number and Project Number.
Module Title?
Data Base Design
30/6/2007
Identify Attributes
The only attributes indicated are the names of the departments, projects, supervisors and employees, as well as the supervisor and employee NUMBER and a unique project number.
Map Attributes
Attribute Entity Attribute Entity
Department
Name
Department Supervisor
Number
Supervisor
Employee
Number
Employee Supervisor Name Supervisor
Employee Name Employee Project Name Project
Project Number Project