Database
Systems:
Design,
Implementation,
and
Management,
14e
Module 3: The Relational
Database Model
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 1
whole or in part.
Chapter Objectives (1 of 2)
By the end of this chapter, you should be able to:
1. Describe the relational database model’s logical structure
2. Identify the relational model’s basic components and explain the structure,
contents, and characteristics of a relational table
3. Use relational database operators to manipulate relational table contents
4. Explain the purpose and components of the data dictionary and system catalog
5. Identify appropriate entities and then the relationships among the entities in the
relational database model
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 2
whole or in part.
Chapter Objectives (2 of 2)
By the end of this chapter, you should be able to (continued):
6. Describe how data redundancy is handled in the relational database model
7. Explain the purpose of indexing in a relational database
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 3
whole or in part.
A Logical View of Data
• The relational data model allows the designer to focus on the logical representation
of the data and its relationships, rather than on the physical storage details
• The relational model enables you to view data logically rather than physically
• Logical simplicity tends to yield simple and effective database design methodologies
• This section explores the details of table structure and contents
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 4
whole or in part.
Tables and Their Characteristics (1 of
2)
• A table is perceived as a two-dimensional structure composed of rows and columns
• A table is also called a relation
• You can think of a table as a persistent representation of a logical relation
− A relation whose contents can be permanently saved for future use
• The domain is the set of allowable values for an attribute
• The primary key (PK) is an attribute or combination of attributes that uniquely
identifies any given row
• The characteristics of a relational table are summarized in Table 3.1 on the following
slide
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 5
whole or in part.
Tables and Their Characteristics (2 of
2)
Table 3.1 Characteristics of a Relational Table
1 A table is perceived as a two-dimensional structure composed of rows and columns
2 Each table row (tuple) represents a single entity occurrence within the entity set
3 Each table column represents an attribute, and each column has a distinct name
4 Each intersection of a row and column represents a single data value
5 All values in a column must conform to the same data format
6 Each column has a specific range of values known as the attribute domain
7 The order of the rows and columns is immaterial to the DBMS
8 Each table must have an attribute or combination of attributes that uniquely identifies each row
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 6
whole or in part.
Keys
• A key consists of one or more attributes that determine other attributes
• Keys are important because they are used to ensure that each row in a table is
uniquely identifiable
− They are also used to establish relationships among tables and to ensure the
integrity of the data
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 7
whole or in part.
Dependencies
• The role of a key is based on the concept of determination, which is the state in
which knowing the value of one attribute helps to determine the value of another
− Example: revenue – cost = profit
• Functional dependence means that the value of one or more attributes
determines the value of one or more other attributes
• The attribute whose value determines another is called the determinant or the key
• The attribute whose value is determined by the other is called the dependent
• Full functional dependence is used to refer to functional dependencies in which
the entire collection of attributes in the determinant is necessary for the relationship
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 8
whole or in part.
Types of Keys (1 of 3)
• A composite key is a key that is composed of more than one attribute
• An attribute that is part of a key is called a key attribute
• A superkey is a key that can uniquely identify any row in the table
• A candidate key is a minimal superkey; that is, a key that does not contain a
subset of attributes that is itself a superkey
• Entity integrity is the condition in which each row in the table has its own known,
unique identity
• A null is the absence of any data value, and it is never allowed in any part of a
primary key
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 9
whole or in part.
Types of Keys (2 of 3)
• A null could represent any of the following:
− An unknown attribute value
− A known, but missing, attribute value
− A “not applicable” condition
• A foreign key (FK) is a primary key of one table that has been placed in another
table
• Referential integrity is a condition by which a dependent table’s foreign key entry
must have either a null entry or a matching entry in the primary key of the related
table
• A secondary key is a key that is used strictly for data retrieval purposes and does
not require a functional dependency
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 10
whole or in part.
Types of Keys (3 of 3)
Figure 3.2 An Example of a Simple
Relational Database
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 11
whole or in part.
Knowledge Check Activity 3-1
• What is the difference between a database and a table?
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 12
whole or in part.
Knowledge Check Activity 3-1: Answer
• What is the difference between a database and a table?
• Answer: A table, a logical structure that represents an entity set, is
only one of the components of a database. A table stores the end-user
data.
• The database is a structure that houses one or more tables and
metadata. The metadata are data about data. Metadata include the
data (attribute) characteristics and the relationships between the
entity sets.
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 13
whole or in part.
Integrity Rules (1 of 2)
Table 3.4 Integrity Rules
Entity Integrity Description
Requirement All primary key entries are unique, and no part of a primary key may be null.
Purpose Each row will have a known, unique identity, and foreign key values can properly reference primary key values.
Example No invoice can have a duplicate number, nor can it be null; in short, all invoices are uniquely identified by their invoice
number.
Referential Description
Integrity
Requirement A foreign key may have either a null entry, as long as it’s not part of its table’s primary key, or an entry that matches
the primary key value in a table to which it is related (every non-null foreign key value must reference an existing
primary key value).
Purpose The purpose is to ensure that every reference by a foreign key is a valid reference to the related primary key. It is
possible for an attribute not to have a corresponding value, but it will be impossible to have an invalid entry; the
enforcement of the referential integrity rule makes it impossible to delete a row in one table whose primary key has
mandatory matching foreign key values in another table.
Example A customer might not yet have an assigned sales representative (number), but it will be impossible to have an invalid
sales representative (number).
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 14
whole or in part.
Integrity Rules (2 of 2)
Figure 3.3 An Illustration of Integrity
Rules
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 15
whole or in part.
Relational Algebra
• Relational algebra is a set of mathematical principles that form the basis for
manipulating relational table contents
• The following are the eight main functions:
− SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 16
whole or in part.
Formal Definitions and Terminology
• A relation is the data that you see in your tables
• A relvar is a variable that holds a relation
− It is a container (variable) for holding a relation data, not the relation itself
− The term is short for relation variable
• A relvar has the following two parts:
− The heading contains the names of the attributes
− The body contains the relation
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 17
whole or in part.
Relational Set Operators (1 of 7)
• Figure 3.4 SELECT
• SELECT is an operator used to select a subset of
rows
• Also known as RESTRICT
• Figure 3.5 PROJECT
• PROJECT is an operator used to select a subset
of columns
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 18
whole or in part.
Relational Set Operators (2 of 7)
• Figure 3.6 UNION
• UNION is an operator used to merge two tables
into a new table, dropping duplicate rows
• The tables must be union-compatible
• Figure 3.7 INTERSECT
• INTERSECT is an operator used to yield only the
rows that are common to two union-compatible
tables
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 19
whole or in part.
Relational Set Operators (3 of 7)
• Figure 3.8 DIFFERENCE
• DIFFERENCE is an operator used to yield all rows
from one table that are not found in another
union-compatible table
• Figure 3.9 PRODUCT
• PRODUCT is an operator used to yield all
possible pairs of rows from two tables
• Also known as the Cartesian product
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 20
whole or in part.
Relational Set Operators (4 of 7)
• Figure 3.10 Two Tables that Will be Used in JOIN
Illustrations
• JOIN allows information to be intelligently
combined from two or more tables
• Figure 3.11 Natural JOIN, Step 1: PRODUCT
• A natural join links tables by selecting only the
rows with common values in their common
attribute(s)
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 21
whole or in part.
Relational Set Operators (5 of 7)
• Figure 3.12 Natural JOIN, Step 2: SELECT
• A SELECT is performed on the output of Step 1 to
yield only the rows for which the AGENT_CODE
values are equal
• Figure 3.13 Natural JOIN, Step 3: PROJECT
• A PROJECT is performed on the results of Step 2
to yield a single copy of each attribute
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 22
whole or in part.
Relational Set Operators (6 of 7)
• Other forms of JOIN include the following:
− Equijoin – links tables on the basis of an equality condition that compares specified
columns of each table
− Theta join – links tables using an inequality comparison operator
− Inner join – only returns matched records from the tables that are being joined
− Outer join – matched pairs are retained and unmatched values in the other table
are left null
Left outer join: yields all of the rows in the first table, including those that do
not have a matching value in the second table
Right outer join: yields all of the rows in the second table, including those that
do not have matching values in the first table
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 23
whole or in part.
Relational Set Operators (7 of 7)
• Figure 3.16 DIVIDE
• The DIVIDE operator is used to
answer questions about one set
of data being associated with all
values of data in another set of
data
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 24
whole or in part.
Knowledge Check Activity 3-2
• Which relational algebra operators can be applied to a pair of
tables that are not union-compatible?
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 25
whole or in part.
Knowledge Check Activity 3-2: Answer
• Which relational algebra operators can be applied to a pair of
tables that are not union-compatible?
• Answer: The Product, Join, and Divide operators can be applied to a
pair of tables that are not union-compatible. Divide does place
specific requirements on the tables to be operated on; however, those
requirements do not include union-compatibility. Select (or Restrict)
and Project are performed on individual tables, not pairs of tables.
(Note that if two tables are joined, then the result is a single table and
the Select or Project operator is performed on that single table.)
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 26
whole or in part.
The Data Dictionary and the System
Catalog
• The data dictionary provides a detailed description of all tables in the database
created by the user and designer
− It is sometimes described as “the database designer’s database” because it
records the design decisions about tables and their structures
• The system catalog is a detailed system data dictionary that describes all objects
within the database
• Homonyms and synonyms must be avoided to lessen confusion
Homonym – same name is used to label different attributes
Synonym – different names are used to describe the same attribute
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 27
whole or in part.
Relationships within the Relational
Database (1 of 6)
• The one-to-many (1:M) relationship is the norm for relational databases
• In the one-to-one (1:1) relationship, one entity can be related to only one other
entity and vice versa
• The many-to-many (M:N) relationship can be implemented by creating a new entity
in 1:M relationships with the original entities
− A composite entity (i.e., bridge or associative entity) helps avoid problems
inherent to M:N relationships
It includes the primary keys of tables to be linked
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 28
whole or in part.
Relationships within the Relational
Database (2 of 6)
• Figure 3.19 The 1:M Relationship between
COURSE and CLASS
• Figure 3.20 The Implemented 1:M Relationship
between COURSE and CLASS
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 29
whole or in part.
Relationships within the Relational
Database (3 of 6)
• Figure 3.21 The 1:1 Relationship between
PROFESSOR and DEPARTMENT
• Figure 3.22 The Implemented 1:1 Relationship
between PROFESSOR and DEPARTMENT
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 30
whole or in part.
Relationships within the Relational
Database (4 of 6)
• Figure 3.23 The ERM’s M:N Relationship
between STUDENT and CLASS
• Figure 3.24 The Wrong Implementation of the
M:N Relationship between STUDENT and CLASS
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 31
whole or in part.
Relationships within the Relational
Database (5 of 6)
• Figure 3.25 Converting the M:N Relationship
into Two 1:M Relationships
• Because the ENROLL table links two tables, it is
also called a linking table
• Figure 3.26 Changing the M:N Relationships to
Two 1:M Relationships
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 32
whole or in part.
Relationships within the Relational
Database (6 of 6)
• Figure 3.27 The Expanded ER Model
• Figure 3.28 The Relational Diagram for the
Ch03_TinyCollege Database
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 33
whole or in part.
Data Redundancy Revisited (1 of 2)
• The relational database facilitates control of data redundancies through use of
foreign keys
− Recall that foreign keys are common attributes that are shared by tables
• Data redundancy should be controlled except in the following circumstances:
− Sometimes data redundancy must be increased to make the database serve
crucial information purposes
− Sometimes data redundancy exists to preserve the historical accuracy of data
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 34
whole or in part.
Data Redundancy Revisited (2 of 2)
• Figure 3.29 A Small Invoicing System
• Figure 3.30 The Relational Diagram for the
Invoicing System
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 35
whole or in part.
Indexes (1 of 2)
• An index is an orderly arrangement to logically access rows in a table
• The index key is the index’s reference point that leads to data location identified
by the key
• In a unique index, the index key can have only one pointer value associated with it
• A table can have many indexes, but each index is associated with only one table
• The index key can have multiple attributes
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 36
whole or in part.
Indexes (2 of 2)
Figure 3.31 Components of an
Index
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 37
whole or in part.
Cobb’s Relational Database Rules (1
Table 13.8
of 2)
Dr. Codd’s 12 Relational Database
Rules
Rule Rule Name Description
1 Information All information in a relational database must be logically represented as column values
in rows within tables.
2 Guaranteed access Every value in a table is guaranteed to be accessible through a combination of table
name, primary key value, and column name.
3 Systematic treatment of nulls Nulls must be represented and treated in a systematic way, independent of data type.
4 Dynamic online catalog based The metadata must be stored and managed as ordinary data—that is, in tables within
on the relational model the database; such data must be available to authorized users using the standard
database relational language.
5 Comprehensive data The relational database may support many languages; however, it must
sublanguage support one well-defined, declarative language as well as data definition,
view definition, data manipulation (interactive and by program), integrity
constraints, authorization, and transaction management (begin, commit,
and rollback).
6 View updating Any view that is theoretically updatable must be updatable through the
system.
7 High-level insert, update, and delete The database must support set-level inserts, updates, and deletes.
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 38
whole or in part.
Cobb’s Relational Database Rules (2
Table 13.8
of 2)
Dr. Codd’s 12 Relational Database
Rules
Rule Rule Name Description
8 Physical data independence Application programs and ad hoc facilities are logically unaffected when physical
access methods or storage structures are changed.
9 Logical data independence Application programs and ad hoc facilities are logically unaffected when changes are
made to the table structures that preserve the original table values (changing order of
columns or inserting columns).
10 Integrity independence All relational integrity constraints must be definable in the relational language and
stored in the system catalog, not at the application level.
11 Distribution independence The end users and application programs are unaware of and unaffected by the data
location (distributed vs. local databases).
12 Nonsubversion If the system supports low-level access to the data, users must not be allowed to
bypass the integrity rules of the database.
13 Rule zero All preceding rules are based on the notion that to be considered relational, a database
must use its relational facilities exclusively for management.
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 39
whole or in part.
Knowledge Check Activity 3-3
• Explain why the data dictionary is sometimes called "the
database designer's database."
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 40
whole or in part.
Knowledge Check Activity 3-3: Answer
• Explain why the data dictionary is sometimes called "the
database designer's database."
• Answer: Just as the database stores data that is of interest to the
users regarding the objects in their environment that are important to
them, the data dictionary stores data that is of interest to the
database designer about the important decisions that were made in
regard to the database structure. The data dictionary contains the
number of tables that were created, the names of all of those tables,
the attributes in each table, the relationships between the tables, the
data type of each attribute, the enforced domains of the attributes,
etc.
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 41
whole or in part.
Summary (1 of 2)
Now that the lesson has ended, you should be able to:
1. Describe the relational database model’s logical structure
2. Identify the relational model’s basic components and explain the structure,
contents, and characteristics of a relational table
3. Use relational database operators to manipulate relational table contents
4. Explain the purpose and components of the data dictionary and system catalog
5. Identify appropriate entities and then the relationships among the entities in the
relational database model
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 42
whole or in part.
Summary (2 of 2)
Now that the lesson has ended, you should be able to (continued):
6. Describe how data redundancy is handled in the relational database model
7. Explain the purpose of indexing in a relational database
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 43
whole or in part.