INFORMATION MANAGEMENT

MODULE 3: Enhanced ER Model

 MODULE 3 SUBTOPIC 3

THE ENHANCED E-R MODEL

 MODULE 3

OBJECTIVES

■At the end of the chapter, the learner should be able to:
• Define terms
• Understand use of supertype/subtype relationships
• Understand use of specialization and generalization techniques
• Specify completeness and disjointness constraints
• Develop supertype/subtype hierarchies for realistic business
situations
• Develop entity clusters
• Enhanced ER model: extends original ER model with new
modeling constructs
• Subtype: A subgrouping of the entities in an entity type that has
attributes distinct from those in other subgroupings
• Supertype: A generic entity type that has a relationship with one
or more subtypes
• Attribute Inheritance:
• Subtype entities inherit values of all attributes of the supertype
• An instance of a subtype is also an instance of the supertype
a) EER
notation
 b) Microsoft
Visio Notation

Different modeling tools may have different notation for the same modeling
constructs.
• Relationships at the supertype level indicate that all subtypes will
participate in the relationship
• The instances of a subtype may participate in a relationship unique
to that subtype. In this situation, the relationship is shown at the
subtype level
All employee subtypes will
have employee number,
name, address, and date
hired

Each employee subtype will

also have its own attributes
• Generalization: The process of defining a more general entity
type from a set of more specialized entity types. BOTTOM-UP
• Specialization: The process of defining one or more subtypes of
the supertype and forming supertype/subtype relationships. TOP-
DOWN
 Figure 3-4 Example of generalization

a) Three entity types: CAR, TRUCK, and MOTORCYCLE

All these types of vehicles have common attributes

 Figure 3-4 Example of generalization (cont.)
b) Generalization to VEHICLE supertype

So we put the
shared
attributes in a
supertype

Note: no subtype for motorcycle, since it has no unique attributes

Figure 3-5 Example of specialization

a) Entity type PART

Only applies to manufactured parts

Applies only to purchased parts

 Figure 3-5 Example of specialization (cont.)

b) Specialization to MANUFACTURED PART and PURCHASED PART

Created 2
subtypes

Note: multivalued composite attribute was replaced by an

associative entity relationship to another entity
Completeness Constraints: Whether an instance of a
supertype must also be a member of at least one subtype
• Total Specialization Rule: Yes (double line) - A rule that specifies that
each entity instance of a supertype must be a member of some subtype in
the relationship.
• Partial Specialization Rule: No (single line) - A rule that specifies that
an entity instance of a supertype is allowed not to belong to any
subtype.
Figure 3-6 Examples of completeness constraints
a) Total specialization rule
Figure 3-6 Examples of completeness constraints (cont.)
b) Partial specialization rule
•Disjointness Constraints: Whether an instance of a
supertype may simultaneously be a member of two (or more)
subtypes
– Disjoint Rule: An instance of the supertype can be only ONE of the subtypes
– Overlap Rule: An instance of the supertype could be more than one of the
subtypes
Figure 3-7 Examples of disjointness constraints

a) Disjoint rule
Figure 3-7 Examples of disjointness constraints (cont.)
b) Overlap rule
Subtype Discriminator: An attribute of the supertype
whose values determine the target subtype(s)
• Disjoint – a simple attribute with alternative values to indicate the
possible subtypes
• Overlapping – a composite attribute whose subparts pertain to
different subtypes. Each subpart contains a Boolean value to
indicate whether or not the instance belongs to the associated
subtype
Figure 3-8 Introducing a subtype discriminator (disjoint rule)
Figure 3-9 Subtype discriminator (overlap rule)
When a new instance is added to PART, these
components are coded as follows:
Figure 3-10 Example of supertype/subtype hierarchy
• EER diagrams are difficult to read when there are too many entities
and relationships.
• Solution: Group entities and relationships into entity clusters.
• Entity cluster: Set of one or more entity types and associated
relationships grouped into a single abstract entity type
Figure 3-13a Possible
entity clusters for Pine
Valley Furniture in
Microsoft Visio

Related groups of
entities could
become clusters
Figure 3-13b EER diagram of PVF entity clusters

More readable, isn’t it?

Figure 3-14 Manufacturing entity cluster

Detail for a single cluster

In this lesson, you should have learned the following:
• Use of supertype/subtype relationships
• Use of specialization and generalization techniques
• Specify completeness and disjointness constraints
• Develop supertype/subtype hierarchies for realistic business
situations
• Develop entity clusters
• Taylor, A. G. (2019). SQL for dummies (9th ed.). Hoboken, NJ: For
Dummies.
• Harrington, J. (2016). Relational Database Design and
Implementation (4th Edition). Morgan Kaufmann
• Juric, N., Vrbsky, S., Nestorov, S. (2016). Database Systems:
Introduction to Databases and Data Warehouses. Prospect Press
• Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson.
• Sullivan, D. (2015). NoSQL for Mere Mortals (1st ed.). Boston:
Addison-Wesley.
• Hoffer, J., Ramesh, V., Topi, H. (2013). Modern Database Management
11th Edition, Prentice Hall.
ASK ANY QUESTION RELATED
TO OUR TOPIC FOR TODAY.
INFORMATION MANAGEMENT
MODULE 4: Relational Database Design and The
Relational Model
 MODULE 4 SUBTOPIC 1

LOGICAL DATABASE DESIGN

 MODULE 4

OBJECTIVES

■At the end of the chapter, the learner should be able to:
• Define terms
• List five properties of relations
• State two properties of candidate keys
• Define first, second, and third normal form
• Transform E-R and EER diagrams to relations
• Create tables with entity and relational integrity constraints
• Use normalization to convert anomalous tables to well-structured
relations
The relational data model represents data in the
form of tables.
Data structure
• Tables (relations), rows, columns
Data manipulation
• Powerful SQL operations for retrieving and modifying data
Data integrity
• Mechanisms for implementing business rules that maintain integrity
of manipulated data
• A relation is a named, two-dimensional table of data.
• A table consists of rows (records) and columns (attribute or field).

Figure 4-1
• We must be able to store and retrieve a row of data in a relation, based on the
data values stored in that row.
• Goal: every relation must have primary keys
• A primary key is an attribute or a combination of attributes that uniquely
identifies each row in a relation
• We designate a primary key by underlining the attribute name(s).
• For example, the primary key for the relation EMPLOYEE1 is EmpID. Notice
that this attribute is underlined in Figure 4-1. In shorthand notation, we express
this relation as follows:
• A foreign key is an attribute (possibly composite) in a relation that serves as
the primary key of another relation. For example, consider the relations
EMPLOYEE1 and DEPARTMENT:
• Requirements for a table to qualify as a relation:
• It must have a unique name.
• Every attribute value must be atomic (not multivalued, not composite).
• Every row must be unique (can’t have two rows with exactly the same values for all their fields).
• Attributes (columns) in tables must have unique names.
• The order of the columns must be irrelevant.
• The order of the rows must be irrelevant.
• NOTE: All relations are in 1st Normal form.
• Relations (tables) correspond with entity types and with
many-to-many relationship types.
• Rows correspond with entity instances and with many-to-
many relationship instances.
• Columns correspond with attributes.

• NOTE: The word relation (in relational database) is NOT

the same as the word relationship (in E-R model).
• The second property of relations listed in the preceding section
states that no multivalued attributes are allowed in a relation.
Thus, a table that contains one or more multivalued attributes
is not a relation.
Figure 4-3 Schema for four relations (Pine Valley Furniture Company)

Primary Key
Foreign Key (implements 1:N
relationship between customer and order)

Combined, these are a composite primary key

(uniquely identifies the order line)…individually they
are foreign keys (implement M:N relationship
between order and product)
1. Domain Constraints
• Allowable values for an attribute (See Table 4-1). A domain
definition usually consists of the following components: domain
name, meaning, data type, size (or length), and allowable values or
allowable range (if applicable).
2. Entity Integrity
• The relational data model allows us to assign a null value to an
attribute in the just described situations. A null is a value that may
be assigned to an attribute when no other value applies or when
the applicable value is unknown.
• No primary key attribute may be null. All primary key fields MUST
have data.
3. Referential Integrity
3. Referential Integrity–rule states that any foreign key value
(on the relation of the many side) MUST match a primary key
value in the relation of the one side. (Or the foreign key can
Figure 4-5
be null) Referential integrity constraints (Pine Valley Furniture)

Referential integrity
constraints are
drawn via arrows
from dependent to
parent table
3. Referential Integrity–
For example: Delete Rules
• Restrict–don’t allow delete of “parent”
side if related rows exist in “dependent”
side
• Cascade–automatically delete
“dependent” side rows that correspond
with the “parent” side row to be deleted
• Set-to-Null–set the foreign key in the
dependent side to null if deleting from
the parent side → not allowed for weak
entities
Figure 4-6 SQL table definitions

Referential integrity
constraints are
implemented with
foreign key to primary
key references.
Mapping Regular Entities to Relations
• Simple attributes: E-R attributes map directly onto the relation
• Composite attributes: Use only their simple, component attributes
• Multivalued Attribute: Becomes a separate relation with a foreign
key taken from the superior entity
 Figure 4-8 Mapping a regular entity

(a) CUSTOMER
entity type with
simple attributes

(b) CUSTOMER relation

 Figure 4-9 Mapping a composite attribute

(a) CUSTOMER
entity type with
composite attribute

(b) CUSTOMER relation with address detail

 Figure 4-10 Mapping an entity with a multivalued attribute

(a)

Multivalued attribute becomes a separate relation with foreign key

(b)

One–to–many relationship between original entity and new relation

Mapping Weak Entities
• Becomes a separate relation with a foreign key taken from the superior entity
• Primary key composed of:
• Partial identifier of weak entity
• Primary key of identifying relation (strong entity)
Figure 4-11 Example of mapping a weak entity
Figure 4-11 Example of mapping a weak entity (cont.)
a) Weak entity DEPENDENT
b) Relations resulting from weak entity

NOTE: the domain

constraint for the foreign
key should NOT allow
null value if
DEPENDENT is a weak
Foreign key
entity

Composite primary key

Relation Dependent
Mapping Binary Relationships

• One-to-Many–Primary key on the one side becomes a foreign key on the many
side

• Many-to-Many–Create a new relation with the primary keys of the two entities
as its primary key

• One-to-One–Primary key on mandatory side becomes a foreign key on optional

side
 Figure 4-12 Example of mapping a 1:M relationship

a) Relationship between customers and orders

Note the mandatory one

b) Mapping the relationship

Again, no null value in the foreign

key…this is because of the
mandatory minimum cardinality.

Foreign key
 Figure 4-13 Example of mapping an M:N relationship

a) Completes relationship (M:N)

The Completes relationship will need to become a separate relation.

 Figure 4-13 Example of mapping an M:N relationship (cont.)

b) Three resulting relations

Composite primary key

Foreign key
new
Foreign key intersection
relation
 Figure 4-14 Example of mapping a binary 1:1 relationship

a) In charge relationship (1:1)

Often in 1:1 relationships, one direction is optional

Figure 4-14 Example of mapping a binary 1:1 relationship (cont.)

b) Resulting relations

Foreign key goes in the relation on the optional side,

matching the primary key on the mandatory side
Mapping Associative Entities
• Identifier Not Assigned
• Default primary key for the association relation is composed of the primary keys of the two
entities (as in M:N relationship)
• Identifier Assigned
• It is natural and familiar to end-users
• Default identifier may not be unique
 Figure 4-15 Example of mapping an associative entity

a) An associative entity
Figure 4-15 Example of mapping an associative entity (cont.)

b) Three resulting relations

Composite primary key formed from the two foreign keys

Figure 4-16 Example of mapping an associative entity with
an identifier

a) SHIPMENT associative entity

 Figure 4-16 Example of mapping an associative entity with
an identifier (cont.)

b) Three resulting relations

Primary key differs from foreign keys

Mapping Unary Relationships
• One-to-Many–Recursive foreign key in the same relation
• Many-to-Many–Two relations:
• One for the entity type
• One for an associative relation in which the primary key has two attributes, both taken from the
primary key of the entity
 Figure 4-17 Mapping a unary 1:N relationship

(a) EMPLOYEE entity with

unary relationship

(b) EMPLOYEE
relation with
recursive foreign
key
Figure 4-18 Mapping a unary M:N relationship

(a) Bill-of-materials
relationships (M:N)

(b) ITEM and

COMPONENT
relations
Mapping Ternary (and n-ary) Relationships
•One relation for each entity and one for the associative
entity
•Associative entity has foreign keys to each entity in the
relationship
 Figure 4-19 Mapping a ternary relationship

a) PATIENT TREATMENT Ternary relationship with associative entity

 Figure 4-19 Mapping a ternary relationship (cont.)

b) Mapping the ternary relationship PATIENT TREATMENT

Remember that This is why treatment But this makes a very It would be better to
the primary key date and time are cumbersome key… create a surrogate
MUST be included in the key like Treatment#.
unique. composite primary
key.
Mapping Supertype/Subtype Relationships

• One relation for supertype and for each subtype

• Supertype attributes (including identifier and subtype discriminator) go into supertype relation
• Subtype attributes go into each subtype; primary key of supertype relation also becomes
primary key of subtype relation
• 1:1 relationship established between supertype and each subtype, with supertype as primary
table
Figure 4-20 Supertype/subtype relationships
Figure 4-21
Mapping supertype/subtype relationships to relations

These are implemented as one-to-one relationships.

 MODULE 4 SUBTOPIC 1

NORMALIZATION
 MODULE 4

OBJECTIVES

■At the end of the chapter, the learner should be able to:
• Define terms
• List five properties of relations
• State two properties of candidate keys
• Define first, second, and third normal form
• Transform E-R and EER diagrams to relations
• Create tables with entity and relational integrity constraints
• Use normalization to convert anomalous tables to well-structured
relations
• Primarily a tool to validate and improve a logical design so
that it satisfies certain constraints that avoid unnecessary
duplication of data
• The process of decomposing relations with anomalies to
produce smaller, well-structured relations
 • A relation that contains minimal data redundancy and allows users
to insert, delete, and update rows without causing data
inconsistencies
• Goal is to avoid anomalies

Figure 4-1
 Example–Figure 4-2b

Question–Is this a relation? Answer–Yes: Unique rows and no multivalued

attributes

Question–What’s the primary key? Answer–Composite: EmpID, CourseTitle

• Types of Anomalies:

• Insertion Anomaly–adding new rows

forces user to create duplicate data
• Deletion Anomaly–deleting rows may
cause a loss of data that would be
needed for other future rows
• Modification Anomaly–changing data in a
row forces changes to other rows
because of duplication

General rule of thumb: A

table should not pertain to
more than one entity type.

Why do these anomalies exist?

Because there are two themes (entity types) in this one relation. This results in data
duplication and an unnecessary dependency between the entities.
Figure 4.22 Steps in normalization

3rd normal form is generally

considered sufficient
• For example, consider the relation EMP COURSE (EmpID, CourseTitle,
DateCompleted) shown in Figure 4-7. We represent the functional dependency
in this relation as follows:

• The comma between EmpID and CourseTitle stands for the logical AND
operator, because DateCompleted is functionally dependent on EmpID and
CourseTitle in combination.
• The functional dependency in this statement implies that the date when a
course is completed is determined by the identity of the employee and the title
of the course.
Typical examples of functional dependencies are the following:
1. SSN → Name, Address, Birthdate A person’s name, address, and birth date
are functionally dependent on that person’s Social Security number (in other
words, there can be only one Name, one Address, and one Birthdate for each
SSN).
2. VIN → Make, Model, Color The make, model, and the original color of a
vehicle are functionally dependent on the vehicle identification number (as above,
there can be only one value of Make, Model, and Color associated with each
VIN).
3. ISBN → Title, FirstAuthorName, Publisher The title of a book, the name of
the first author, and the publisher are functionally dependent on the book’s
international standard book number (ISBN).
• The attribute on the left side of the arrow in a functional dependencyis called a
determinant.
• SSN, VIN, and ISBN are determinants in the preceding three examples. In the
EMP COURSE relation (Figure 4-7), the combination of EmpID and
CourseTitle is a determinant.
• Candidate key is an attribute, or combination of
attributes, that uniquely identifies a row in a relation. A
candidate key must satisfy the following properties
,which are a subset of the six properties of a relation
previously listed:
• 1. Unique identification For every row, the value of the key must
uniquely identify that row. This property implies that each nonkey
attribute is functionally dependent on that key.
• 2. Nonredundancy No attribute in the key can be deleted without
destroying the property of unique identification.
STEPS IN NORMALIZATION
• No multivalued attributes
• Every attribute value is atomic
• Fig. 4-25 is not in 1st Normal Form (multivalued
attributes) ➔ it is not a relation.
• Fig. 4-26 is in 1st Normal form.
• All relations are in 1st Normal Form.
Table with multivalued attributes, not in 1st normal form

Note: This is NOT a relation.

Table with multivalued attributes, not in 1st normal form

Note: This is NOT a relation.

• Insertion –if new product is ordered for order 1007 of existing customer,
customer data must be re-entered, causing duplication
• Deletion –if we delete the Dining Table from Order 1006, we lose
information concerning this item’s finish and price
• Update –changing the price of product ID 4 requires update in multiple
records

Why do these anomalies exist?

Because there are multiple themes
(entity types) in one relation. This
results in duplication and an
unnecessary dependency between
the entities.
four determinants in INVOICE
1NF PLUS every non-key attribute is fully functionally
dependent on the ENTIRE primary key
• Every non-key attribute must be defined by the entire key, not by only part of
the key
• No partial functional dependencies
 Figure 4-27 Functional dependency diagram for INVOICE

OrderID ➔ OrderDate, CustomerID, CustomerName, CustomerAddress

CustomerID ➔ CustomerName, CustomerAddress
ProductID ➔ ProductDescription, ProductFinish, ProductStandardPrice
OrderID, ProductID ➔ OrderQuantity

Therefore, NOT in 2nd Normal Form

Figure 4-28 Removing partial dependencies

Getting it into Second Normal

Form

Partial dependencies are removed, but there are still

transitive dependencies
• 2NF PLUS no transitive dependencies (functional dependencies on non-
primary-key attributes)
• Note: This is called transitive, because the primary key is a determinant for
another attribute, which in turn is a determinant for a third
• Solution: Non-key determinant with transitive dependencies go into a new
table; non-key determinant becomes primary key in the new table and stays as
foreign key in the old table
Figure 4-29 Removing partial dependencies

Getting it into Third Normal

Form

Transitive dependencies are removed.

In this lesson, you should have learned the following:
• Five properties of relations
• Two properties of candidate keys
• First, second, and third normal form
• Transform E-R and EER diagrams to relations
• Create tables with entity and relational integrity constraints
• Use normalization to convert anomalous tables to well-structured
relations
ASK ANY QUESTION RELATED TO OUR
TOPIC FOR TODAY.
• Taylor, A. G. (2019). SQL for dummies (9th ed.). Hoboken, NJ: For
Dummies.
• Harrington, J. (2016). Relational Database Design and Implementation
(4th Edition). Morgan Kaufmann
• Juric, N., Vrbsky, S., Nestorov, S. (2016). Database Systems: Introduction
to Databases and Data Warehouses. Prospect Press
• Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson.
• Sullivan, D. (2015). NoSQL for Mere Mortals (1st ed.). Boston: Addison-
Wesley.
• Hoffer, J., Ramesh, V., Topi, H. (2013). Modern Database Management 11th
Edition, Prentice Hall.
INFORMATION MANAGEMENT
MODULE 4: Relational Database Design and The
Relational Model
 MODULE 4 SUBTOPIC 1

LOGICAL DATABASE DESIGN

 MODULE 4

OBJECTIVES

■At the end of the chapter, the learner should be able to:
• Define terms
• List five properties of relations
• State two properties of candidate keys
• Define first, second, and third normal form
• Transform E-R and EER diagrams to relations
• Create tables with entity and relational integrity constraints
• Use normalization to convert anomalous tables to well-structured
relations
The relational data model represents data in the
form of tables.
Data structure
• Tables (relations), rows, columns
Data manipulation
• Powerful SQL operations for retrieving and modifying data
Data integrity
• Mechanisms for implementing business rules that maintain integrity
of manipulated data
• A relation is a named, two-dimensional table of data.
• A table consists of rows (records) and columns (attribute or field).

Figure 4-1
• We must be able to store and retrieve a row of data in a relation, based on the
data values stored in that row.
• Goal: every relation must have primary keys
• A primary key is an attribute or a combination of attributes that uniquely
identifies each row in a relation
• We designate a primary key by underlining the attribute name(s).
• For example, the primary key for the relation EMPLOYEE1 is EmpID. Notice
that this attribute is underlined in Figure 4-1. In shorthand notation, we express
this relation as follows:
• A foreign key is an attribute (possibly composite) in a relation that serves as
the primary key of another relation. For example, consider the relations
EMPLOYEE1 and DEPARTMENT:
• Requirements for a table to qualify as a relation:
• It must have a unique name.
• Every attribute value must be atomic (not multivalued, not composite).
• Every row must be unique (can’t have two rows with exactly the same values for all their fields).
• Attributes (columns) in tables must have unique names.
• The order of the columns must be irrelevant.
• The order of the rows must be irrelevant.
• NOTE: All relations are in 1st Normal form.
• Relations (tables) correspond with entity types and with
many-to-many relationship types.
• Rows correspond with entity instances and with many-to-
many relationship instances.
• Columns correspond with attributes.

• NOTE: The word relation (in relational database) is NOT

the same as the word relationship (in E-R model).
• The second property of relations listed in the preceding section
states that no multivalued attributes are allowed in a relation.
Thus, a table that contains one or more multivalued attributes
is not a relation.
Figure 4-3 Schema for four relations (Pine Valley Furniture Company)

Primary Key
Foreign Key (implements 1:N
relationship between customer and order)

Combined, these are a composite primary key

(uniquely identifies the order line)…individually they
are foreign keys (implement M:N relationship
between order and product)
1. Domain Constraints
• Allowable values for an attribute (See Table 4-1). A domain
definition usually consists of the following components: domain
name, meaning, data type, size (or length), and allowable values or
allowable range (if applicable).
2. Entity Integrity
• The relational data model allows us to assign a null value to an
attribute in the just described situations. A null is a value that may
be assigned to an attribute when no other value applies or when
the applicable value is unknown.
• No primary key attribute may be null. All primary key fields MUST
have data.
3. Referential Integrity
3. Referential Integrity–rule states that any foreign key value
(on the relation of the many side) MUST match a primary key
value in the relation of the one side. (Or the foreign key can
Figure 4-5
be null) Referential integrity constraints (Pine Valley Furniture)

Referential integrity
constraints are
drawn via arrows
from dependent to
parent table
3. Referential Integrity–
For example: Delete Rules
• Restrict–don’t allow delete of “parent”
side if related rows exist in “dependent”
side
• Cascade–automatically delete
“dependent” side rows that correspond
with the “parent” side row to be deleted
• Set-to-Null–set the foreign key in the
dependent side to null if deleting from
the parent side → not allowed for weak
entities
Figure 4-6 SQL table definitions

Referential integrity
constraints are
implemented with
foreign key to primary
key references.
Mapping Regular Entities to Relations
• Simple attributes: E-R attributes map directly onto the relation
• Composite attributes: Use only their simple, component attributes
• Multivalued Attribute: Becomes a separate relation with a foreign
key taken from the superior entity
 Figure 4-8 Mapping a regular entity

(a) CUSTOMER
entity type with
simple attributes

(b) CUSTOMER relation

 Figure 4-9 Mapping a composite attribute

(a) CUSTOMER
entity type with
composite attribute

(b) CUSTOMER relation with address detail

 Figure 4-10 Mapping an entity with a multivalued attribute

(a)

Multivalued attribute becomes a separate relation with foreign key

(b)

One–to–many relationship between original entity and new relation

Mapping Weak Entities
• Becomes a separate relation with a foreign key taken from the superior entity
• Primary key composed of:
• Partial identifier of weak entity
• Primary key of identifying relation (strong entity)
Figure 4-11 Example of mapping a weak entity
Figure 4-11 Example of mapping a weak entity (cont.)
a) Weak entity DEPENDENT
b) Relations resulting from weak entity

NOTE: the domain

constraint for the foreign
key should NOT allow
null value if
DEPENDENT is a weak
Foreign key
entity

Composite primary key

Relation Dependent
Mapping Binary Relationships

• One-to-Many–Primary key on the one side becomes a foreign key on the many
side

• Many-to-Many–Create a new relation with the primary keys of the two entities
as its primary key

• One-to-One–Primary key on mandatory side becomes a foreign key on optional

side
 Figure 4-12 Example of mapping a 1:M relationship

a) Relationship between customers and orders

Note the mandatory one

b) Mapping the relationship

Again, no null value in the foreign

key…this is because of the
mandatory minimum cardinality.

Foreign key
 Figure 4-13 Example of mapping an M:N relationship

a) Completes relationship (M:N)

The Completes relationship will need to become a separate relation.

 Figure 4-13 Example of mapping an M:N relationship (cont.)

b) Three resulting relations

Composite primary key

Foreign key
new
Foreign key intersection
relation
 Figure 4-14 Example of mapping a binary 1:1 relationship

a) In charge relationship (1:1)

Often in 1:1 relationships, one direction is optional

Figure 4-14 Example of mapping a binary 1:1 relationship (cont.)

b) Resulting relations

Foreign key goes in the relation on the optional side,

matching the primary key on the mandatory side
Mapping Associative Entities
• Identifier Not Assigned
• Default primary key for the association relation is composed of the primary keys of the two
entities (as in M:N relationship)
• Identifier Assigned
• It is natural and familiar to end-users
• Default identifier may not be unique
 Figure 4-15 Example of mapping an associative entity

a) An associative entity
Figure 4-15 Example of mapping an associative entity (cont.)

b) Three resulting relations

Composite primary key formed from the two foreign keys

Figure 4-16 Example of mapping an associative entity with
an identifier

a) SHIPMENT associative entity

 Figure 4-16 Example of mapping an associative entity with
an identifier (cont.)

b) Three resulting relations

Primary key differs from foreign keys

Mapping Unary Relationships
• One-to-Many–Recursive foreign key in the same relation
• Many-to-Many–Two relations:
• One for the entity type
• One for an associative relation in which the primary key has two attributes, both taken from the
primary key of the entity
 Figure 4-17 Mapping a unary 1:N relationship

(a) EMPLOYEE entity with

unary relationship

(b) EMPLOYEE
relation with
recursive foreign
key
Figure 4-18 Mapping a unary M:N relationship

(a) Bill-of-materials
relationships (M:N)

(b) ITEM and

COMPONENT
relations
Mapping Ternary (and n-ary) Relationships
•One relation for each entity and one for the associative
entity
•Associative entity has foreign keys to each entity in the
relationship
 Figure 4-19 Mapping a ternary relationship

a) PATIENT TREATMENT Ternary relationship with associative entity

 Figure 4-19 Mapping a ternary relationship (cont.)

b) Mapping the ternary relationship PATIENT TREATMENT

Remember that This is why treatment But this makes a very It would be better to
the primary key date and time are cumbersome key… create a surrogate
MUST be included in the key like Treatment#.
unique. composite primary
key.
Mapping Supertype/Subtype Relationships

• One relation for supertype and for each subtype

• Supertype attributes (including identifier and subtype discriminator) go into supertype relation
• Subtype attributes go into each subtype; primary key of supertype relation also becomes
primary key of subtype relation
• 1:1 relationship established between supertype and each subtype, with supertype as primary
table
Figure 4-20 Supertype/subtype relationships
Figure 4-21
Mapping supertype/subtype relationships to relations

These are implemented as one-to-one relationships.

INFORMATION MANAGEMENT
MODULE 4: Relational Database Design and The
Relational Model
 MODULE 4 SUBTOPIC 1

NORMALIZATION
 MODULE 4

OBJECTIVES

■At the end of the chapter, the learner should be able to:
• Define terms
• List five properties of relations
• State two properties of candidate keys
• Define first, second, and third normal form
• Transform E-R and EER diagrams to relations
• Create tables with entity and relational integrity constraints
• Use normalization to convert anomalous tables to well-structured
relations
• Primarily a tool to validate and improve a logical design so
that it satisfies certain constraints that avoid unnecessary
duplication of data
• The process of decomposing relations with anomalies to
produce smaller, well-structured relations
 • A relation that contains minimal data redundancy and allows users
to insert, delete, and update rows without causing data
inconsistencies
• Goal is to avoid anomalies

Figure 4-1
 Example–Figure 4-2b

Question–Is this a relation? Answer–Yes: Unique rows and no multivalued

attributes

Question–What’s the primary key? Answer–Composite: EmpID, CourseTitle

• Types of Anomalies:

• Insertion Anomaly–adding new rows

forces user to create duplicate data
• Deletion Anomaly–deleting rows may
cause a loss of data that would be
needed for other future rows
• Modification Anomaly–changing data in a
row forces changes to other rows
because of duplication

General rule of thumb: A

table should not pertain to
more than one entity type.

Why do these anomalies exist?

Because there are two themes (entity types) in this one relation. This results in data
duplication and an unnecessary dependency between the entities.
Figure 4.22 Steps in normalization

3rd normal form is generally

considered sufficient
• For example, consider the relation EMP COURSE (EmpID, CourseTitle,
DateCompleted) shown in Figure 4-7. We represent the functional dependency
in this relation as follows:

• The comma between EmpID and CourseTitle stands for the logical AND
operator, because DateCompleted is functionally dependent on EmpID and
CourseTitle in combination.
• The functional dependency in this statement implies that the date when a
course is completed is determined by the identity of the employee and the title
of the course.
Typical examples of functional dependencies are the following:
1. SSN → Name, Address, Birthdate A person’s name, address, and birth date
are functionally dependent on that person’s Social Security number (in other
words, there can be only one Name, one Address, and one Birthdate for each
SSN).
2. VIN → Make, Model, Color The make, model, and the original color of a
vehicle are functionally dependent on the vehicle identification number (as above,
there can be only one value of Make, Model, and Color associated with each
VIN).
3. ISBN → Title, FirstAuthorName, Publisher The title of a book, the name of
the first author, and the publisher are functionally dependent on the book’s
international standard book number (ISBN).
• The attribute on the left side of the arrow in a functional dependencyis called a
determinant.
• SSN, VIN, and ISBN are determinants in the preceding three examples. In the
EMP COURSE relation (Figure 4-7), the combination of EmpID and
CourseTitle is a determinant.
• Candidate key is an attribute, or combination of
attributes, that uniquely identifies a row in a relation. A
candidate key must satisfy the following properties
,which are a subset of the six properties of a relation
previously listed:
• 1. Unique identification For every row, the value of the key must
uniquely identify that row. This property implies that each nonkey
attribute is functionally dependent on that key.
• 2. Nonredundancy No attribute in the key can be deleted without
destroying the property of unique identification.
STEPS IN NORMALIZATION
• No multivalued attributes
• Every attribute value is atomic
• Fig. 4-25 is not in 1st Normal Form (multivalued
attributes) ➔ it is not a relation.
• Fig. 4-26 is in 1st Normal form.
• All relations are in 1st Normal Form.
Table with multivalued attributes, not in 1st normal form

Note: This is NOT a relation.

Table with multivalued attributes, not in 1st normal form

Note: This is NOT a relation.

• Insertion –if new product is ordered for order 1007 of existing customer,
customer data must be re-entered, causing duplication
• Deletion –if we delete the Dining Table from Order 1006, we lose
information concerning this item’s finish and price
• Update –changing the price of product ID 4 requires update in multiple
records

Why do these anomalies exist?

Because there are multiple themes
(entity types) in one relation. This
results in duplication and an
unnecessary dependency between
the entities.
four determinants in INVOICE
1NF PLUS every non-key attribute is fully functionally
dependent on the ENTIRE primary key
• Every non-key attribute must be defined by the entire key, not by only part of
the key
• No partial functional dependencies
 Figure 4-27 Functional dependency diagram for INVOICE

OrderID ➔ OrderDate, CustomerID, CustomerName, CustomerAddress

CustomerID ➔ CustomerName, CustomerAddress
ProductID ➔ ProductDescription, ProductFinish, ProductStandardPrice
OrderID, ProductID ➔ OrderQuantity

Therefore, NOT in 2nd Normal Form

Figure 4-28 Removing partial dependencies

Getting it into Second Normal

Form

Partial dependencies are removed, but there are still

transitive dependencies
• 2NF PLUS no transitive dependencies (functional dependencies on non-
primary-key attributes)
• Note: This is called transitive, because the primary key is a determinant for
another attribute, which in turn is a determinant for a third
• Solution: Non-key determinant with transitive dependencies go into a new
table; non-key determinant becomes primary key in the new table and stays as
foreign key in the old table
Figure 4-29 Removing partial dependencies

Getting it into Third Normal

Form

Transitive dependencies are removed.

In this lesson, you should have learned the following:
• Five properties of relations
• Two properties of candidate keys
• First, second, and third normal form
• Transform E-R and EER diagrams to relations
• Create tables with entity and relational integrity constraints
• Use normalization to convert anomalous tables to well-structured
relations
ASK ANY QUESTION RELATED TO OUR
TOPIC FOR TODAY.
• Taylor, A. G. (2019). SQL for dummies (9th ed.). Hoboken, NJ: For
Dummies.
• Harrington, J. (2016). Relational Database Design and Implementation
(4th Edition). Morgan Kaufmann
• Juric, N., Vrbsky, S., Nestorov, S. (2016). Database Systems: Introduction
to Databases and Data Warehouses. Prospect Press
• Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson.
• Sullivan, D. (2015). NoSQL for Mere Mortals (1st ed.). Boston: Addison-
Wesley.
• Hoffer, J., Ramesh, V., Topi, H. (2013). Modern Database Management 11th
Edition, Prentice Hall.