Relational Database
Design by ER - to-
Relational Mapping
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 1
FIGURE
The ER
conceptual
schema
diagram for
the
COMPANY
database.
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 2
SOME DEFINITIONS
FOREIGN KEY
An attribute in the present table which occurs
as a primary key in another table is known
as FOREIGN KEY in the present table
PARTIAL KEY
A part of a key is called partial key. i.e it alone
cannot act as a key and therefore needs
some another attribute/s to form a key.
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 3
FOREIGN KEY
FOREIGN KEYS
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 4
Lecture Outline
➢ ER-to-Relational Mapping Algorithm
Step 1: Mapping of Regular Entity Types
Step 2: Mapping of Weak Entity Types
Step 3: Mapping of Binary(or Unary) 1:1 Relationship
Types
Step 4: Mapping of Binary (or Unary) 1:N Relationship
Types.
Step 5: Mapping of Binary M:N (or Unary) Relationship
Types.
Step 6: Mapping of Multivalued attributes.
Step 7: Mapping of N-ary Relationship Types.
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 5
ER-to-Relational Mapping
Algorithm
➢ Step 1: Mapping of Regular Entity Types.
⚫ For each regular (strong) entity type E in the ER schema, create
a relation R that includes all the simple attributes of E.
⚫ Choose one of the key attributes of E as the primary key for R. If
the chosen key of E is composite, the set of simple attributes that
form it will together form the primary key of R.
Example: We create the relations EMPLOYEE, DEPARTMENT,
and PROJECT in the relational schema corresponding to the
regular entities in the ER diagram. SSN, DNUMBER, and
PNUMBER are the primary keys for the relations EMPLOYEE,
DEPARTMENT, and PROJECT as shown.
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 6
FIGURE 6.1:
The ER
conceptual
schema
diagram for
the
COMPANY
database.
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 7
FIGURE 6.2
Result of
mapping the
COMPANY
ER schema
into a
relational
schema.
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 8
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 9
ER-to-Relational Mapping
➢
Algorithm (cont)
Step 2: Mapping of Weak Entity Types
⚫ For each weak entity type W in the ER schema with owner entity
type E, create a relation R and include all simple attributes (or
simple components of composite attributes) of W as attributes of
R.
⚫ In addition, include as foreign key attributes of R the primary key
attribute(s) of the relation(s) that correspond to the owner entity
type(s).
⚫ The primary key of R is the combination of the primary key(s) of
the owner(s) and the partial key of the weak entity type W, if any.
Example: Create the relation DEPENDENT in this step to
correspond to the weak entity type DEPENDENT. Include the
primary key SSN of the EMPLOYEE relation as a foreign key
attribute of DEPENDENT (renamed to ESSN).
The primary key of the DEPENDENT relation is the combination
{ESSN, DEPENDENT_NAME} because DEPENDENT_NAME is
the partial key of DEPENDENT.
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 10
ER-to-Relational Mapping
Algorithm (cont)
➢ Step 3: Mapping of Binary 1:1 Relationship Types
For each binary 1:1 relationship type R in the ER schema, identify the
relations S and T that correspond to the entity types participating in R.
There are three possible approaches:
(1) Foreign Key approach: Choose one of the relations-S, say-and include a foreign key in
S the primary key of T. It is better to choose an entity type with total participation in R in
the role of S.
Example: 1:1 relation MANAGES is mapped by choosing the participating entity type
DEPARTMENT to serve in the role of S, because its participation in the MANAGES
relationship type is total.
(2) Merged relation option: An alternate mapping of a 1:1 relationship type is possible by
merging the two entity types and the relationship into a single relation. This may be
appropriate when both participations are total.
(3) Cross-reference or relationship relation option: The third alternative is to set up a third
relation R for the purpose of cross-referencing the primary keys of the two relations S and
T representing the entity types.
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 11
ER-to-Relational Mapping
Algorithm (cont)
➢ Step 4: Mapping of Binary(or Unary) 1:N Relationship Types.
⚫ For each regular binary 1:N relationship type R, identify the
relation S that represent the participating entity type at the N-side
of the relationship type.
⚫ Include as foreign key in S the primary key of the relation T that
represents the other entity type participating in R.
⚫ Include any simple attributes of the 1:N relation type as attributes
of S.
Example: 1:N relationship types WORKS_FOR, CONTROLS,
and SUPERVISION in the figure. For WORKS_FOR we include
the primary key DNUMBER of the DEPARTMENT relation as
foreign key in the EMPLOYEE relation and call it DNO.
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 12
ER-to-Relational Mapping
Algorithm (cont)
➢ Step 5: Mapping of Binary M:N Relationship Types.
⚫ For each regular binary M:N relationship type R, create a new
relation S to represent R.
⚫ Include as foreign key attributes in S the primary keys of the
relations that represent the participating entity types; their
combination will form the primary key of S.
⚫ Also include any simple attributes of the M:N relationship type (or
simple components of composite attributes) as attributes of S.
Example: The M:N relationship type WORKS_ON from the ER
diagram is mapped by creating a relation WORKS_ON in the
relational database schema. The primary keys of the PROJECT and
EMPLOYEE relations are included as foreign keys in WORKS_ON
and renamed PNO and ESSN, respectively.
Attribute HOURS in WORKS_ON represents the HOURS attribute
of the relation type. The primary key of the WORKS_ON relation is
the combination of the foreign key attributes {ESSN, PNO}.
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 13
ER-to-Relational Mapping
Algorithm (cont)
➢ Step 6: Mapping of Multivalued attributes.
⚫ For each multivalued attribute A, create a new relation R. This
relation R will include an attribute corresponding to A, plus the
primary key attribute K-as a foreign key in R-of the relation that
represents the entity type of relationship type that has A as an
attribute.
⚫ The primary key of R is the combination of A and K. If the
multivalued attribute is composite, we include its simple
components.
Example: The relation DEPT_LOCATIONS is created. The
attribute DLOCATION represents the multivalued attribute
LOCATIONS of DEPARTMENT, while DNUMBER-as foreign key-
represents the primary key of the DEPARTMENT relation. The
primary key of R is the combination of {DNUMBER, DLOCATION}.
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 14
ER-to-Relational Mapping
Algorithm (cont)
➢ Step 7: Mapping of N-ary Relationship Types.
⚫ For each n-ary relationship type R, where n>2, create
a new relationship S to represent R.
⚫ Include as foreign key attributes in S the primary keys
of the relations that represent the participating entity
types.
⚫ Also include any simple attributes of the n-ary
relationship type (or simple components of composite
attributes) as attributes of S.
Example: The relationship type SUPPY in the ER below. This
can be mapped to the relation SUPPLY shown in the relational
schema, whose primary key is the combination of the three
foreign keys {SNAME, PARTNO, PROJNAME}
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 15
FIGURE 6.3
Ternary relationship types. (a) The SUPPLY relationship.
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 16
FIGURE 6.4
Mapping the n-ary relationship type SUPPLY from
Figure 6.3
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 17
Summary of Mapping constructs
and constraints
Table 6.1 Correspondence between ER and Relational Models
ER Model Relational Model
Entity type “Entity” relation
1:1 or 1:N relationship type Foreign key (or “relationship” relation)
M:N relationship type “Relationship” relation and two foreign keys
n-ary relationship type “Relationship” relation and n foreign keys
Simple attribute Attribute
Composite attribute Set of simple component attributes
Multivalued attribute Relation and foreign key
Value set Domain
Key attribute Primary (or secondary) key
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 18
CONSTRUCT AN ER DIAGRAM FOR THE FOLLOWING
REQUIREMENTS
Each BANK has a unique Code, as well as a Name and
Address. Each BANK is related to one or more BANK-
BRANCHes, and the BranhNo is unique among each set
of BANK-BRANCHes that are related to the same BANK.
Each BANK-BRANCH has an Address. Each BANK-
BRANCH has zero or more LOANS and zero or more
ACCTS. Each ACCOUNT has an AcctNo (unique),
Balance, and Type and is related to exactly one BANK-
BRANCH and to at least one CUSTOMER. Each LOAN
has a LoanNo (unique), Amount, and Type and is related
to exactly one BANK-BRANCH and to at least one
CUSTOMER. Each CUSTOMER has an SSN (unique),
Name, Phone, and Address, and is related to zero or
more ACCOUNTs and to zero or more LOANs.
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 19
ER DIAGRAM FOR A BANK
DATABASE
© The Benjamin/Cummings Publishing Company, Inc. 1994, Elmasri/Navathe, Fundamentals of Database Systems, Second Edition
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 20
Enhanced Entity-
Relationship and UML
Modeling
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 21
Enhanced-ER (EER) Model
Concepts
➢ Includes all modeling concepts of basic ER
➢ Additional concepts: subclasses/superclasses,
specialization/generalization, categories,
attribute inheritance
➢ The resulting model is called the enhanced-ER
or Extended ER (E2R or EER) model
➢ It is used to model applications more completely
and accurately if needed
➢ It includes some object-oriented concepts, such
as inheritance
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 22
Subclasses and Superclasses (1)
➢ An entity type may have additional meaningful
subgroupings of its entities
➢ Example: EMPLOYEE may be further grouped into
SECRETARY, ENGINEER, MANAGER, TECHNICIAN,
SALARIED_EMPLOYEE, HOURLY_EMPLOYEE,…
⚫ Each of these groupings 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.
➢ Example: EMPLOYEE/SECRETARY,
EMPLOYEE/TECHNICIAN
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 23
Subclasses and Superclasses (2)
➢ These are also called IS-A relationships (SECRETARY IS-A
EMPLOYEE, TECHNICIAN IS-A EMPLOYEE, …).
➢ Note: An entity that is member of a subclass represents the same
real-world entity as some member of the superclass
⚫ The Subclass member is the same entity in a distinct specific
role
⚫ An entity cannot exist in the database merely by being a member
of a subclass; it must also be a member of the superclass
⚫ A member of the superclass can be optionally included as a
member of any number of its subclasses
➢ Example: A salaried employee who is also an engineer belongs to
the two subclasses ENGINEER and SALARIED_EMPLOYEE
⚫ It is not necessary that every entity in a superclass be a member
of some subclass
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 24
Attribute Inheritance in Superclass /
Subclass Relationships
➢ An entity that is member of a subclass inherits
all attributes of the entity as a member of the
superclass
➢ It also inherits all relationships
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 25
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 in method of
pay is {SALARIED_EMPLOYEE, HOURLY_EMPLOYEE}.
⚫ Superclass/subclass relationships and specialization can be
diagrammatically represented in EER diagrams
⚫ Attributes of a subclass are called specific attributes. For
example, TypingSpeed of SECRETARY
⚫ The subclass can participate in specific relationship types. For
example, BELONGS_TO of HOURLY_EMPLOYEE
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 26
Example of a Specialization
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 27
Generalization
➢ 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
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 28
Generalization and Specialization
➢ Diagrammatic notation sometimes used to distinguish between
generalization and specialization
⚫ Arrow pointing to the generalized superclass represents a
generalization
⚫ Arrows pointing to the specialized subclasses represent a
specialization
⚫ We do not use this notation because it is often subjective as to
which process is more appropriate for a particular situation
⚫ We advocate not drawing any arrows in these situations
➢ Data Modeling with Specialization and Generalization
⚫ A superclass or subclass represents a set of entities
⚫ Shown in rectangles in EER diagrams (as are entity types)
⚫ Sometimes, all entity sets are simply called classes, whether
they are entity types, superclasses, or subclasses
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 29
Constraints on Specialization and
Generalization (1)
➢ If we can determine exactly those entities that will become members of
each subclass by a condition, the subclasses are called predicate-defined
(or condition-defined) subclasses
⚫ Condition is a constraint that determines subclass members
⚫ Display a predicate-defined subclass by writing the predicate condition
next to the line attaching the subclass to its superclass
➢ If all subclasses in a specialization have membership condition on same
attribute of the superclass, specialization is called an attribute defined-
specialization
⚫ Attribute is called the defining attribute of the specialization
⚫ Example: JobType is the defining attribute of the specialization
{SECRETARY, TECHNICIAN, ENGINEER} of EMPLOYEE
➢ If no condition determines membership, the subclass is called user-defined
⚫ Membership in a subclass is determined by the database users by
applying an operation to add an entity to the subclass
⚫ Membership in the subclass is specified individually for each entity in
the superclass by the user
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 30
Constraints on Specialization and
Generalization (2)
➢ Two other conditions apply to a specialization/generalization:
➢ Disjointness Constraint:
⚫ Specifies that the subclasses of the specialization must be disjointed
(an entity can be a member of at most one of the subclasses of the
specialization)
⚫ Specified by d in EER diagram
⚫ If not disjointed, overlap; that is the same entity may be a member of
more than one subclass of the specialization
⚫ Specified by o in EER diagram
➢ Completeness Constraint:
⚫ Total specifies that every entity in the superclass must be a member of
some subclass in the specialization/ generalization
⚫ Shown in EER diagrams by a double line
⚫ Partial allows an entity not to belong to any of the subclasses
⚫ Shown in EER diagrams by a single line
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 31
Constraints on Specialization and
Generalization (3)
➢ Hence, we have four types of specialization/generalization:
⚫ Disjoint, total
⚫ Disjoint, partial
⚫ Overlapping, total
⚫ Overlapping, partial
➢ Note: Generalization usually is total because the superclass is
derived from the subclasses.
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 32
Example of disjoint partial
Specialization
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 33
Specialization / Generalization
Hierarchies, Lattices and Shared
Subclasses
➢ A subclass may itself have further subclasses specified on it
➢ Forms a hierarchy or a lattice
➢ Hierarchy has a constraint that every subclass has only one superclass
(called single inheritance)
➢ In a lattice, a subclass can be subclass of more than one superclass (called
multiple inheritance)
➢ In a lattice or hierarchy, a subclass inherits attributes not only of its direct
superclass, but also of all its predecessor superclasses
➢ A subclass with more than one superclass is called a shared subclass
➢ Can have specialization hierarchies or lattices, or generalization hierarchies
or lattices
➢ In specialization, start with an entity type and then define subclasses of the
entity type by successive specialization (top down conceptual refinement
process)
➢ In generalization, start with many entity types and generalize those that
have common properties (bottom up conceptual synthesis process)
➢ In practice, the combination of two processes is employed
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 34
Specialization / Generalization
Lattice Example (UNIVERSITY)
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 35
Categories (UNION TYPES)
➢ All of the superclass/subclass relationships we have seen thus far have a
single superclass
➢ A shared subclass is subclass in more than one distinct superclass/subclass
relationships, where each relationships has a single superclass (multiple
inheritance)
➢ In some cases, need to model a single superclass/subclass relationship
with more than one superclass
➢ Superclasses represent different entity types
➢ Such a subclass is called a category or UNION TYPE
➢ Example: Database for vehicle registration, vehicle owner can be a person,
a bank (holding a lien on a vehicle) or a company.
⚫ Category (subclass) OWNER is a subset of the union of the three superclasses
COMPANY, BANK, and PERSON
⚫ A category member must exist in at least one of its superclasses
➢ Note: The difference from shared subclass, which is subset of the
intersection of its superclasses (shared subclass member must exist in all of
its superclasses).
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 36
Example of categories
(UNION TYPES)
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 37
UML Example for Displaying
Specialization / Generalization
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 38
Alternative Diagrammatic Notations
Symbols for entity type / class, Displaying attributes
attribute and relationship
Notations for displaying Various (min, Displaying
specialization / generalization max) notations cardinality ratios
DON'T DEPEND ON SLIDES --
ALWAYS REFER BOOK 39