Chapter Four
Database Design
Levels of Database Design
Conceptual Database Design
- Conceptual design is the process of constructing a model of the
information used in an enterprise, independent of any physical
considerations.
- Identify what are the entities/entity types
- Identify what are the attributes
- Identify relationship types
- Identify what are the constraints/business rules that hold?
- Draw entity-relationship diagram
- Review the conceptual data model with user
ENTITY - RELATIONSHIP DIAGRAMS (E-RD)
• An entity-relationship model (ERM) is a model that
provides a high-level description of a conceptual
data model.
• Data modeling that provides a graphical notation
for representing such data models in the form of
entity-relationship diagrams (ERD).
Graphical Representations in ER Diagramming
• Entity is represented by a rectangle
containing the name of the entity
Strong Entity Weak Entity
Attributes are represented by ovals and are connected to the
entity by a line.
Cont…
• A derived attribute is indicated by a
dotted line. (……..)
Key
• Primary Keys are underlined.
• Relationships are represented by Diamond shaped symbols
• Weak Relationship - is a relationship between Weak and
Strong Entities.
• Strong Relationship - is a relationship between two
strong Entities.
Strong relationship Weak relationship
Developing an E-R
Diagram(1)
- First we should gather all the relevant information’s.
- Information gathered by
Interviewing end users individually and in a group
Questionnaire survey
Direct observation
Examining different documents
Developing an E-R Diagram(2)
A student record management system will have the
following two basic data object categories with their
own features or properties: Students will have an Id,
Name, Dept, Age, GPA and Course will have an Id,
Name, Credit Hours. Whenever a student enroll in a
course in a specific Academic Year and Semester, the
Student will have a grade for the course.
Developing an E-R Diagram(3)
Developing
Cont.… an E-R Diagram(4)
• A Personnel record management system will have the
following two basic data object categories with their own
features or properties: Employee will have an Id, Name,
Age, salary and Department will have an Id, Name, budget.
Whenever an Employee works in one Department, the
duration of his stay in the respective department should be
registered.
DID
name budget
ID
Works- department
employee
in
sa dn
age lar du am
y rati e
o n
Relationship cardinality
The multiplicity of the relationship: is the number or
range of possible occurrence of tuples of an entity type.
One branch can only have one manager.
One employee could manage either one or no branches
1..1 0..1
Employee Manages Branch
One staff may Lead one or more project(s)
One project is Lead by one staff
1..1 1.*
Employee Leads Project
an Instructor Teaches one or more Course(s)
One Course Thought by Zero or more Instructor(s)
Participation constraints(1)
- Total participation
every tuple in the entity or relation participates in at least one relationship by
taking a role.
This means, every tuple in a relation will be attached with at least one other
tuple.
The minimum cardinality of the entity set is 1 or more.
The entity with total participation in a relationship will be connected to the
relationship using a double line.
- Partial participation
some tuple in the entity or relation may not participate in the relationship.
This means, there is at least one tuple from that relation not taking any role in
that specific relationship.
The minimum cardinality of the entity set is zero.
The entity with partial participation in a relationship will be connected to the
relationship using a single line.
Participation constraints(2)
- Participation of EMPLOYEE in “belongs to” relationship with DEPARTMENT is
total since every employee should belong to a department.
- Participation of DEPARTMENT in “belongs to” relationship with EMPLOYEE is
total since every department should have more than one employee
- Participation of employee in “manages” relationship with Department, is
partial participation since not all employees are managers.
- Participation of department in “Manages” relationship with employee is total
since every department should have a manager.
Problem in ER Modeling
Connection traps are problems arising from
misinterpreting certain relationships.
1. Fan trap
• Occurs where a model represents a relationship
between entity types, but the pathway between
certain entity occurrences is ambiguous.
• Solution: restructuring
Example:
Cont.…
Problem: Which car (Car1 or Car3 or Car5) is used by
Employee 6. Emp6 working in Branch 1 (Bra1). Thus from
this ER Model one can not tell which car is used by which
staff since a branch can have more than one car and also a
branch is populated by more than one employee.
[Link] Trap:
Chasm Trap:
• Occurs where a model suggests the existence of
a relationship between entity types, but the
path way does not exist between certain entity
occurrences.
• Solution: add another relationship between the
extreme entities.
Cont.…
If we have a set of projects that are not active currently then we can
not assign a project manager for these projects. So there are project
with no project manager making the participation to have a minimum
value of zero.
• Problem: How can we identify which BRANCH is responsible for
which PROJECT? We know that whether the PROJECT is active or
not there is a responsible BRANCH. But which branch is a question
to be answered, and since we have a minimum participation of zero
between employee and PROJECT we can’t identify the BRANCH
responsible for each PROJECT.
Chasm Trap
BRANCH 1..1 Has 1..* EMPLOYEE 0..1 Manages PROJECT
0..*
Enhanced E-R (E-ER) Model
EER is used to reduce the complexity in participation and
relationship complexity.
In this part we will discuss the following basic
EER concepts.
Generalization
Specialization
Sub classes
Super classes
Attribute Inheritance
Constraints on specialization and
generalization
Subclass and superclass(1)
An entity type may have additional meaningful
subgroupings of its entities
Example: EMPLOYEE may be further grouped into:
SECRETARY, ENGINEER, TECHNICIAN, …Based on the EMPLOYEE’s Job
SALARIED_EMPLOYEE, HOURLY_EMPLOYEE -Based on the EMPLOYEE’s
method of pay
EER diagrams extend ER diagrams to represent these additional
subgroupings, called subclasses or subtypes
Each of these subgroupings is a subset of EMPLOYEE entities
Each is called a subclass of EMPLOYEE
EMPLOYEE is the superclass for each of these subclasses
These are called superclass/subclass relationships: A superclass-subclass
relationship is also known as an "IS-A" relationship. For instance, Salaried_Employee IS-An
Employee.
EMPLOYEE/SECRETARY
EMPLOYEE/TECHNICIAN
EMPLOYEE/MANAGER
Subclass entity inherits all attributes and relationships of superclass
Subclass and superclass(2)
Specialization(1)
Specialization is the process of defining a set of subclasses of a
superclass
The set of subclasses is based upon some distinguishing
characteristics of the entities in the superclass
Example: {SECRETARY, ENGINEER, TECHNICIAN} is a specialization
of EMPLOYEE based upon job type.
May have several specializations of the same superclass
Example: Another specialization of EMPLOYEE based
on method of pay is {SALARIED_EMPLOYEE, HOURLY_EMPLOYEE}
Attributes of a subclass are called specific or local attributes. For
example, the attribute TypingSpeed of SECRETARY
The subclass can also participate in specific relationship types. For
example, a relationship BELONGS_TO of HOURLY_EMPLOYEE
Specialization(2)
Generalization(1)
Generalization is the reverse of the specialization
process
Several classes with common features are
generalized into a superclass;
original classes become its subclasses
Example: CAR, TRUCK generalized into VEHICLE;
both CAR, TRUCK become subclasses of the
superclass VEHICLE.
We can view {CAR, TRUCK} as a specialization of
VEHICLE
Alternatively, we can view VEHICLE as a
generalization of CAR and TRUCK
Generalization (2)
Constraints (1)
Constraints on Specialization and Generalization
Disjointness Constraint:
Specifies that the subclasses of the specialization must be disjoint
An entity belong to no more than one lower level entity set/An
entity can only be a member of one subclass.
an entity can be a member of at most one of the subclasses of the
specialization Specified by d in EER diagram
Generalization/ specialization relationships can be disjointed or
overlapped
If not disjoint, specialization is overlapping:
Cont.…
Example employee account
d d
Salaried Hourly current
Saving
employee employee account
account
Overlapping constraint
Sometimes, an entity can belong to more than one subclass of
the specialization, this means that subclasses overlap with
each other.
That is the same entity may be a member of more than one
subclass of the specialization: Specified by o in EER diagram
Ex. Employee working at the university can be both a Student
and an employee at the same time.
University member
student employee
Constraints (2)
Completeness Constraint: Completeness constraint states that a
specialization can be:
total or partial specialization
Total specifies that every entity in the superclass must be a
member of some subclass in the specialization/generalization
Every entity that belongs to a superclass must belong to at least
one subclass of the specialization .Shown in EER diagrams by a
double line
Cont.….
• Example: A student is specialized into: – Graduate
Student: Master or PhD student – Undergraduate
Student: [Link]. Student
Cont.…
Partial allows an entity not to belong to any of the
subclasses. Shown in EER diagrams by a single line
Example: – If an employee is not a secretary, technician, or
engineer, then he does not belong to any of these
subclasses of this specialization. So, the specialization
{secretary, technician, engineer} is a partial specialization.
Or an employee may not belongs to secretary, technician,
or engineer (some employees may not fit into the subtypes)
Hence we have four possible constraints
Disjoint AND Total
Disjoint AND Partial
Overlapping AND Total
Overlapping AND Partial
Note: Generalization is usually total because the superclass is derived
from the subclasses
Thanks !!!