INFORMATION MANAGEMENT

Module 1: Database
Environment and Development
Process
 MODULE 1 SUBTOPIC 1

THE DATABASE ENVIRONMENT

At the end of the chapter, the learner should be able to:

➢ Define Terms.
➢ Name limitations of conventional file processing
➢ Explain advantages of databases
➢ Identify costs and risks of databases
➢ List components of database environment
➢ Identify categories of database applications
• Database: organized collection of logically related data
• Data: stored representations of meaningful objects and
events
• Structured: numbers, text, dates
• Unstructured: images, video, documents
• Information: data processed to increase knowledge in the
person using the data
• Metadata: data that describes the properties and context
of user data
Context helps users understand data
Graphical displays turn data into useful
information that managers can use for
decision making and interpretation
 Descriptions of the properties or characteristics of the
data, including data types, field sizes, allowable
values, and data context

Database Systems 1
• Program-Data Dependence
• All programs maintain metadata for each file they use

Database Systems 1
• Duplication of Data
• Different systems/programs have separate copies of the same
data

Database Systems 1
• Limited Data Sharing
• No centralized control of data

Database Systems 1
• Lengthy Development Times
• Programmers must design their own file formats

Database Systems 1
• Excessive Program Maintenance
• 80% of information systems budget

Database Systems 1
• Central repository of shared data
• Data is managed by a controlling
agent
• Stored in a standardized,
convenient form
Requires a Database Management System (DBMS)

Database Systems 1
• A software system that is used to create, maintain,
and provide controlled access to user databases

Order Filing
System

Invoicing Central database

DBMS
System
Contains employee,
order, inventory,
Payroll pricing, and
System customer data

Database Systems 1
1. Program-data independence
2. Planned data redundancy
3. Improved data consistency
4. Improved data sharing
5. Increased application development productivity

Database Systems 1
6. Enforcement of standards
7. Improved data quality
8. Improved data accessibility and responsiveness
9. Reduced program maintenance
10.Improved decision support

Database Systems 1
1. New, specialized personnel
2. Installation and management cost and complexity
3. Conversion costs
4. Need for explicit backup and recovery
5. Organizational conflict
Data models
– Graphical system capturing nature and relationship of data
– Enterprise Data Model–high-level entities and relationships for the organization
– Project Data Model–more detailed view, matching data structure in database or data warehouse
Entities
– Noun form describing a person, place, object, event, or concept
– Composed of attributes
Relationships
– Between entities
– Usually one-to-many (1:M) or many-to-many (M:N)
Relational Databases
– Database technology involving tables (relations) representing entities and primary/foreign keys representing relationships
• CASE Tools–computer-aided software engineering
• Repository–centralized storehouse of metadata
• Database Management System (DBMS) –software for managing the
database
• Database–storehouse of the data
• Application Programs–software using the data
• User Interface–text and graphical displays to users
• Data/Database Administrators–personnel responsible for maintaining the
database
• System Developers–personnel responsible for designing databases and
software
• End Users–people who use the applications and databases
• Application Programs–software using the data
• User Interface–text and graphical displays to users
• Data/Database Administrators–personnel responsible for maintaining the
database
• System Developers–personnel responsible for designing databases and
software
• End Users–people who use the applications and databases
END OF MODULE 1
SUBTOPIC 1
 MODULE 1 SUBTOPIC 2

DATABASE DEVELOPMENT
PROCESS
At the end of the chapter, the learner should be able to:
• Describe database system development life cycle
• Explain prototyping and agile development approaches
• Explain roles of individuals
• Explain the three-schema architecture for databases
SDLC
–System Development Life Cycle
–Detailed, well-planned development process
–Time-consuming, but comprehensive
–Long development cycle
Prototyping
–Rapid application development (RAD)
–Cursory attempt at conceptual data modeling
–Define database during development of initial prototype
–Repeat implementation and maintenance activities with new prototype versions

Database Systems 1
 Planning

Analysis

Logical Design

Physical Design

Implementation

Maintenance

Database Systems 1
 Planning
Planning Purpose–preliminary understanding
Deliverable–request for study
Analysis

Logical Design

Physical Design

Database activity– Implementation

enterprise modeling
and early conceptual Maintenance
data modeling

Database Systems 1
 Purpose–thorough requirements analysis
Planning and structuring
Deliverable–functional system specifications
Analysis
Analysis

Logical Design

Physical Design

Database activity–thorough Implementation

and integrated conceptual
data modeling Maintenance

Database Systems 1
 Purpose–information requirements elicitation
Planning
and structure
Deliverable–detailed design specifications
Analysis

Logical Design
Logical Design

Physical Design

Database activity– Implementation

logical database design
(transactions, forms, Maintenance
displays, views, data
integrity and security)

Database Systems 1
 Purpose–develop technology and
Planning organizational specifications

Analysis Deliverable–program/data
structures, technology purchases,
organization redesigns
Logical Design

Physical Design
Physical Design

Database activity– Implementation

physical database design
(define database to DBMS, Maintenance
physical data organization,
database processing programs)

Database Systems 1
 Purpose–programming, testing,
Planning
training, installation, documenting

Analysis Deliverable–operational programs,

documentation, training materials
Logical Design

Physical Design

Database activity–
Implementation
Implementation
database implementation,
including coded
programs, documentation, Maintenance
installation and
conversion

Database Systems 1
Planning Purpose–monitor, repair, enhance

Analysis
Deliverable–periodic audits

Logical Design

Physical Design

Database activity–
Implementation
database maintenance,
performance analysis
and tuning, error Maintenance
Maintenance
corrections
Database Systems 1
External Schema
• User Views
• Subsets of Conceptual Schema
• Can be determined from business-function/data entity matrices
• DBA determines schema for different users
Conceptual Schema
• E-R models
Internal Schema
• Logical structures
• Physical structures

Database Systems 1
Different
people have
different views
of the
database…these
are the external
schema

The internal
schema is the
underlying
design and
implementation

Database Systems 1
• Project–a planned undertaking of related activities to reach an objective
that has a beginning and an end
• Initiated and planned in planning stage of SDLC
• Executed during analysis, design, and implementation
• Closed at the end of implementation

Database Systems 1
• Business analysts
• Systems analysts
• Database analysts and data modelers
• Data/Database administrators
• Project managers
• Users
• Programmers
• Database architects
• Other technical experts

Database Systems 1
Driven by four main objectives:
• Need for program-data independence ➔ reduced maintenance
• Desire to manage more complex data types and structures
• Ease of data access for less technical personnel
• Need for more powerful decision support platforms
Database Systems 1
Database Systems 1
Database Systems 1
Database Systems 1
Database Systems 1
Database Systems 1
Database Systems 1
Database Systems 1
Database Systems 1
• Personal databases
• Multitier client/server databases
• Enterprise applications
• Enterprise resource planning (ERP) systems
• Data warehousing implementations

Database Systems 1
Database Systems 1
Database Systems 1
• 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. (2016). Modern Database Management 12th
Edition, Prentice Hall.
END OF SUBTOPIC 2

END OF MODULE 1
INFORMATION MANAGEMENT
Module 1: Database
Environment and Development
Process
 MODULE 1 SUBTOPIC 1

THE DATABASE ENVIRONMENT

At the end of the chapter, the learner should be able to:

➢ Define Terms.
➢ Name limitations of conventional file processing
➢ Explain advantages of databases
➢ Identify costs and risks of databases
➢ List components of database environment
➢ Identify categories of database applications
• Database: organized collection of logically related data
• Data: stored representations of meaningful objects and
events
• Structured: numbers, text, dates
• Unstructured: images, video, documents
• Information: data processed to increase knowledge in the
person using the data
• Metadata: data that describes the properties and context
of user data
Context helps users understand data
Graphical displays turn data into useful
information that managers can use for
decision making and interpretation
 Descriptions of the properties or characteristics of the
data, including data types, field sizes, allowable
values, and data context

Database Systems 1
• Program-Data Dependence
• All programs maintain metadata for each file they use

Database Systems 1
• Duplication of Data
• Different systems/programs have separate copies of the same
data

Database Systems 1
• Limited Data Sharing
• No centralized control of data

Database Systems 1
• Lengthy Development Times
• Programmers must design their own file formats

Database Systems 1
• Excessive Program Maintenance
• 80% of information systems budget

Database Systems 1
• Central repository of shared data
• Data is managed by a controlling
agent
• Stored in a standardized,
convenient form
Requires a Database Management System (DBMS)

Database Systems 1
• A software system that is used to create, maintain,
and provide controlled access to user databases

Order Filing
System

Invoicing Central database

DBMS
System
Contains employee,
order, inventory,
Payroll pricing, and
System customer data

Database Systems 1
1. Program-data independence
2. Planned data redundancy
3. Improved data consistency
4. Improved data sharing
5. Increased application development productivity

Database Systems 1
6. Enforcement of standards
7. Improved data quality
8. Improved data accessibility and responsiveness
9. Reduced program maintenance
10.Improved decision support

Database Systems 1
1. New, specialized personnel
2. Installation and management cost and complexity
3. Conversion costs
4. Need for explicit backup and recovery
5. Organizational conflict
Data models
– Graphical system capturing nature and relationship of data
– Enterprise Data Model–high-level entities and relationships for the organization
– Project Data Model–more detailed view, matching data structure in database or data warehouse
Entities
– Noun form describing a person, place, object, event, or concept
– Composed of attributes
Relationships
– Between entities
– Usually one-to-many (1:M) or many-to-many (M:N)
Relational Databases
– Database technology involving tables (relations) representing entities and primary/foreign keys representing relationships
• CASE Tools–computer-aided software engineering
• Repository–centralized storehouse of metadata
• Database Management System (DBMS) –software for managing the
database
• Database–storehouse of the data
• Application Programs–software using the data
• User Interface–text and graphical displays to users
• Data/Database Administrators–personnel responsible for maintaining the
database
• System Developers–personnel responsible for designing databases and
software
• End Users–people who use the applications and databases
• Application Programs–software using the data
• User Interface–text and graphical displays to users
• Data/Database Administrators–personnel responsible for maintaining the
database
• System Developers–personnel responsible for designing databases and
software
• End Users–people who use the applications and databases
END OF MODULE 1
SUBTOPIC 1
 MODULE 1 SUBTOPIC 2

DATABASE DEVELOPMENT
PROCESS
At the end of the chapter, the learner should be able to:
• Describe database system development life cycle
• Explain prototyping and agile development approaches
• Explain roles of individuals
• Explain the three-schema architecture for databases
SDLC
–System Development Life Cycle
–Detailed, well-planned development process
–Time-consuming, but comprehensive
–Long development cycle
Prototyping
–Rapid application development (RAD)
–Cursory attempt at conceptual data modeling
–Define database during development of initial prototype
–Repeat implementation and maintenance activities with new prototype versions

Database Systems 1
 Planning

Analysis

Logical Design

Physical Design

Implementation

Maintenance

Database Systems 1
 Planning
Planning Purpose–preliminary understanding
Deliverable–request for study
Analysis

Logical Design

Physical Design

Database activity– Implementation

enterprise modeling
and early conceptual Maintenance
data modeling

Database Systems 1
 Purpose–thorough requirements analysis
Planning and structuring
Deliverable–functional system specifications
Analysis
Analysis

Logical Design

Physical Design

Database activity–thorough Implementation

and integrated conceptual
data modeling Maintenance

Database Systems 1
 Purpose–information requirements elicitation
Planning
and structure
Deliverable–detailed design specifications
Analysis

Logical Design
Logical Design

Physical Design

Database activity– Implementation

logical database design
(transactions, forms, Maintenance
displays, views, data
integrity and security)

Database Systems 1
 Purpose–develop technology and
Planning organizational specifications

Analysis Deliverable–program/data
structures, technology purchases,
organization redesigns
Logical Design

Physical Design
Physical Design

Database activity– Implementation

physical database design
(define database to DBMS, Maintenance
physical data organization,
database processing programs)

Database Systems 1
 Purpose–programming, testing,
Planning
training, installation, documenting

Analysis Deliverable–operational programs,

documentation, training materials
Logical Design

Physical Design

Database activity–
Implementation
Implementation
database implementation,
including coded
programs, documentation, Maintenance
installation and
conversion

Database Systems 1
Planning Purpose–monitor, repair, enhance

Analysis
Deliverable–periodic audits

Logical Design

Physical Design

Database activity–
Implementation
database maintenance,
performance analysis
and tuning, error Maintenance
Maintenance
corrections
Database Systems 1
External Schema
• User Views
• Subsets of Conceptual Schema
• Can be determined from business-function/data entity matrices
• DBA determines schema for different users
Conceptual Schema
• E-R models
Internal Schema
• Logical structures
• Physical structures

Database Systems 1
Different
people have
different views
of the
database…these
are the external
schema

The internal
schema is the
underlying
design and
implementation

Database Systems 1
• Project–a planned undertaking of related activities to reach an objective
that has a beginning and an end
• Initiated and planned in planning stage of SDLC
• Executed during analysis, design, and implementation
• Closed at the end of implementation

Database Systems 1
• Business analysts
• Systems analysts
• Database analysts and data modelers
• Data/Database administrators
• Project managers
• Users
• Programmers
• Database architects
• Other technical experts

Database Systems 1
Driven by four main objectives:
• Need for program-data independence ➔ reduced maintenance
• Desire to manage more complex data types and structures
• Ease of data access for less technical personnel
• Need for more powerful decision support platforms
Database Systems 1
Database Systems 1
Database Systems 1
Database Systems 1
Database Systems 1
Database Systems 1
Database Systems 1
Database Systems 1
Database Systems 1
• Personal databases
• Multitier client/server databases
• Enterprise applications
• Enterprise resource planning (ERP) systems
• Data warehousing implementations

Database Systems 1
Database Systems 1
Database Systems 1
• 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. (2016). Modern Database Management 12th
Edition, Prentice Hall.
END OF SUBTOPIC 2

END OF MODULE 1
INFORMATION MANAGEMENT
MODULE 2: Modeling Data in the
Organization
 MODULE 2 SUBTOPIC 1
ENTITY RELATIONSHIP
DIAGRAM(ERD)
 OBJECTIVES

■At the end of the chapter, the learner should be able to:
• Define terms
• Understand importance of data modeling
• Write good names and definitions for entities, relationships, and attributes
• Distinguish unary, binary, and ternary relationships
• Model different types of attributes, entities, relationships, and cardinalities
• Draw E-R diagrams for common business situations
• Convert many-to-many relationships to associative entities
Explanation of a term or fact
• Term–word or phrase with specific meaning
• Fact–association between two or more terms
Guidelines for good data definition
• A concise description of essential data meaning
• Gathered in conjunction with systems requirements
• Accompanied by diagrams
• Achieved by consensus, and iteratively refined
• Related to business, not technical, characteristics
• Meaningful and self-documenting
• Unique
• Readable
• Composed of words from an approved list
• Repeatable
• Written in standard syntax
Entities:
• Entity instance–person, place, object, event, concept (often corresponds to a
row in a table)
• Entity Type–collection of entities (often corresponds to a table)
Relationships:
• Relationship instance–link between entities (corresponds to primary key-
foreign key equivalencies in related tables)
• Relationship type–category of relationship…link between entity types
Attributes:
• Properties or characteristics of an entity or relationship type (often
corresponds to a field in a table)
 Entity Attribute
symbols symbols

A special
entity that is Relationship
also a symbols
relationship
Relationship
degrees
specify Relationship
number of cardinalities
entity types specify how many
involved of each entity type
is allowed
• Entities are represented by
softboxes
• Entity names go in the
softboxes
• Entity names are always
singular and written in capital
letters
• Attributes are listed under
entity names
• Mandatory attributes are
marked with an asterisk: “*”
• Optional attributes are
marked with a circle: “o”
• Unique identifiers are marked
with a hash sign: “#”
• Are statements that define or constrain some
aspect of the business
• Are derived from policies, procedures, events,
functions
• Assert business structure
• Control/influence business behavior
• Are expressed in terms familiar to end users
• Are automated through DBMS software
•Declarative–what, not how
•Precise–clear, agreed-upon meaning
•Atomic–one statement
•Consistent–internally and externally
•Expressible–structured, natural language
•Distinct–non-redundant
•Business-oriented–understood by business people
 • Entity – a person, a place, an object, an event, or a concept in the user environment
about which the organization wishes to maintain data
• Entity type – a collection of entities that share common properties or characteristics
• Entity instance – A single occurrence of an entity type
• Attribute–property or characteristic of an entity or relationship type

Entity instance

EMPLOYEE

ID First Name Last Name Hire Date Salary Commission

100 Santos Albert Jul-20-1990 45000 0.8

attributes 101 Rivera Sally Aug-23- 46000 0.5

1996

Entity Type
• Entity – a person, a place, an object, an event, or
a concept in the user environment about which
the organization wishes to maintain data
• Entity type – a collection of entities that share
common properties or characteristics
• Entity instance – A single occurrence of an entity
type
System System
user Inappropriate output
entities

Appropriate
entities
Strong entity
• exists independently of other types of entities
• has its own unique identifier
• identifier underlined with single line
Weak entity
• dependent on a strong entity (identifying owner)…cannot exist on its own
• does not have a unique identifier (only a partial identifier)
• entity box and partial identifier have double lines
Identifying relationship
• links strong entities to weak entities
Strong entity Weak entity
Attribute–property or characteristic of an entity or relationship type
• Name should be a singular noun or noun phrase
• Name should be unique
• Name should follow a standard format
• e.g. [Entity type name { [ Qualifier ] } ] Class
• Similar attributes of different entity types should use the
same qualifiers and classes
• State what the attribute is and possibly why it is important
• Make it clear what is and is not included in the attribute’s
value
• Include aliases in documentation
• State source of values
• Specify required vs. optional
• State min and max number of occurrences allowed
• Indicate relationships with other attributes
• Required versus Optional Attributes
• Simple versus Composite Attribute
• Single-Valued versus Multivalued Attribute
• Stored versus Derived Attributes
• Identifier Attributes
Required – must have a value for every entity (or Optional – may not have a value for every entity (or
relationship) instance with which it is associated relationship) instance with which it is associated
Composite attribute – An attribute that has
meaningful component parts (attributes)

The address is
broken into
component parts

Figure 2-7 A composite attribute

Multivalued – may take on more than Derived – values can be calculated from
one value for a given entity (or related attribute values (not physically
relationship) instance stored in the database)

Figure 2-8 Entity with multivalued attribute (Skill) and derived attribute
(Years Employed)

Multivalued Derived
an employee can Calculated
have more than one from date
skill employed
and current
date
• Identifier (Key)–an attribute (or combination of
attributes) that uniquely identifies individual
instances of an entity type
• Simple versus Composite Identifier
• Candidate Identifier–an attribute that could be a
key…satisfies the requirements for being an
identifier
• Choose Identifiers that
• Will not change in value
• Will not be null
• Avoid intelligent identifiers (e.g., containing locations or
people that might change)
• Substitute new, simple keys for long, composite keys
The identifier
is boldfaced
and underlined
• Relationship Types vs. Relationship Instances
• The relationship type is modeled as lines between entity types…the instance
is between specific entity instances
• Relationships can have attributes
• These describe features pertaining to the association between the entities in the relationship
• Two entities can have more than one type of relationship
between them (multiple relationships)
• Associative Entity–combination of relationship and entity
a) Relationship
type (Completes)

b) Relationship
instances
• Degree of a relationship is the number of entity
types that participate in it
• Unary Relationship
• Binary Relationship
• Ternary Relationship
 Entities of
One entity two
related to different Entities of three
another of different types
types related related to each
the same to each other
entity type other
a) Unary relationships
b) Binary relationships
c) Ternary relationship

Note: a relationship can have attributes of its own

One-to-One
• Each entity in the relationship will have exactly one related entity
One-to-Many
• An entity on one side of the relationship can have many related
entities, but an entity on the other side will have a maximum of
one related entity
Many-to-Many
• Entities on both sides of the relationship can have many related
entities on the other side
• Cardinality Constraints—the number of instances of one
entity that can or must be associated with each instance of
another entity
• Minimum Cardinality
• If zero, then optional
• If one or more, then mandatory
• Maximum Cardinality
• The maximum number
a) Mandatory cardinalities

A patient history is A patient must have

recorded for one and recorded at least one
only one patient history, and can have many
b) One optional, one mandatory

A project must be assigned to at An employee can be assigned to

least one employee, and may be any number of projects, or may not
assigned to many be assigned to any at all
c) Optional cardinalities

A person is
married to at most
one other person,
or may not be
married at all
a) Employees and departments

Entities can be related to one another in more than one way

b) Professors and courses (fixed lower limit constraint)

Here, min cardinality constraint is 2. At least two

professors must be qualified to teach each course. Each
professor must be qualified to teach at least one course.
simple

composite
An entity–has attributes
A relationship–links entities together
When should a relationship with attributes instead be an associative
entity?
• All relationships for the associative entity should be many
• The associative entity could have meaning independent of the other entities
• The associative entity preferably has a unique identifier, and should also have other
attributes
• The associative entity may participate in other relationships other than the entities of the
associated relationship
• Ternary relationships should be converted to associative entities
Associative entity is like a relationship with an attribute, but it is also considered to be an entity
in its own right.

Note that the many-to-many cardinality between entities in Figure 2-11a has been replaced by
two one-to-many relationships with the associative entity.

a) Relationship
type (Completes)
 ERD example
Business Rules:
• A library system contains books, authors and patrons, with attributes book
number, author number and patron number, respectively.
• Books are further described by title and page count
• Authors by author name, and
• Patrons by patron name.
• Books should have at least one author or can have more. An author can
author a book or many books
• Patrons borrow books but at any point in time, may not have anything
checked out. When they do have a book checked out, there is a due date
associated with it.
Figure 2-22
Data model for Pine
Valley Furniture
Company in
Microsoft Visio
notation

Different modeling
software tools may have
different notation for
the same constructs.
In this lesson, you should have learned the following:
• Importance of data modeling
• Unary, binary, and ternary relationships
• Model different types of attributes, entities, relationships, and
cardinalities
• Draw E-R diagrams for common business situations
• Convert many-to-many relationships to associative entities
• 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 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.