Database Systems: Design, Implementation, and Management, 8th Edition 3-1
The Relational Database Model
At a Glance
Instructor’s Manual Table of Contents
• Teaching Tips
• Quick Quizzes
• Class Discussion Topics
• Additional Projects
• Additional Resources
• Key Terms
Database Systems: Design, Implementation, and Management, 8th Edition 3-2
Chapter 3 presents characteristics of tables and explains what keys are. It
discusses relational operators in some depth with examples. Relationships in a
relational database are revisited, and the implementation of M:N relationships
using a composite entity is presented. Data redundancy is reviewed and discussed
in more depth. A discussion of indexes follows. The chapter ends with Dr. Codd’s
relational database rules.
In this chapter, students will learn:
• That the relational database model offers a logical view of data
• About the relational model’s basic component: relations
• That relations are logical constructs composed of rows (tuples) and columns
• That relations are implemented as tables in a relational DBMS
• About relational database operators, the data dictionary, and the system catalog
• How data redundancy is handled in the relational database model
• Why indexing is important
1. Review Chapter 2, specifically the relational data model, structural and data
independence, and entity relationship diagrams (ERDs).
2. Introduce topics covered by this chapter, such as the table as a logical construct,
and note that individual tables within the database can be related to one another.
3. Note that the chapter will introduce basic concepts in the design of tables and
basic concepts that become important in the coming chapters.
3.1 A Logical View of Data
1. Remind students that placing the DBMS between the application and the database
eliminates most of the file system’s limitations.
2. Emphasize that the increased flexibility comes at the expense of complexity.
Database Systems: Design, Implementation, and Management, 8th Edition 3-3
3. Explain that the relational data model allows the designer to focus on the logical
representation of the data and its relationships
3.1.1 Tables and Their Characteristics
1. Introduce the relation, and explain that it is a mathematical construct.
2. Explain that end users often find it easier to think of a relation as a table, and that
a table is thought of as a two-dimensional structure of rows and columns.
It may not be obvious to students why a relation should be thought of as a table.
Spend some time in class discussing this. Note that Dr. Codd used the term
relation as a synonym for table. Explain why.
3. Explain that a table can be thought of as a persistent relation, and explain why,
emphasizing that from the perspective of the end user, a table contains a group of
related entity occurrences.
4. Note that the terms entity set and table are often used interchangeably.
5. Walk through Table 3.1 to discuss characteristics of a relational table.
6. Walk through the example presented in Figure 3.1 and the itemized list on pages
64 – 66, and introduce the concept of a primary key.
1. Explain what a key is and why it is important.
2. Explain that in addition to uniquely identifying each row in the table, they
establish relationships among tables and ensure the integrity of the data.
3. Discuss the concept of determination.
4. Remind students what a primary key is, and explain that a key’s role is based on
the concept of determination.
5. Explain what functional dependence is.
Database Systems: Design, Implementation, and Management, 8th Edition 3-4
Teaching Emphasize that students should be careful when defining a dependency’s
6. Explain that it may take more than a single attribute to define functional
dependence, and explain that a multi-attribute key is a composite key.
7. Explain that any attribute that is part of a key is a key attribute, and provide an
8. Explain what full functional dependence is, and walk through the example on
Teaching Take the time to discuss the difference between functional dependence and full
Tip functional dependence.
9. Explain that within the broad key classification, several specialized keys can be
defined, and a superkey is any key that uniquely identifies each row.
10. Explain what entity integrity is, and that a null value is not permitted in the
11. Emphasize that nulls can never be part of a primary key, and explain why they
should be avoided in general.
12. Walk through the list on page 69 of possible meanings of nulls, and discuss why
the multiple definitions create problems.
13. Explain that controlled redundancy makes a relational database work, and discuss
how tables are linked by common attributes.
14. Walk through the example presented in Figure 3.2.
15. Explain that a relational database can be represented by a relational schema, and
explain what a relational schema is.
Teaching It may be helpful to students to briefly review what an entity relationship
Tip diagram (ERD) is.
Database Systems: Design, Implementation, and Management, 8th Edition 3-5
16. Explain what a foreign key (FK) is, and discuss how foreign keys exhibit
referential integrity. Explain the concept of referential integrity.
17. Explain that a secondary key is strictly used for data retrieval purposes.
Teaching Take the time to walk through an example of how a secondary key might be used
Tip for data retrieval purposes, and discuss when this might be useful.
18. Walk through Table 3.3 to summarize different types of relational database keys.
Quick Quiz 1
1. Data relationships in a relational database are based on a logical construct known
as a(n) ____.
2. A(n) ____ consists of one or more attributes that determine other attributes.
3. True or False: The statement “A determines B” indicates that if you know the
value of attribute B, you can look up the value of attribute A.
4. True or False: The attribute B is functionally dependent on A if A determines B.
3.3 Integrity Rules
1. Emphasize that relational database integrity rules are important to good database
design, and walk through Table 3.4, which summarizes the rules.
2. Walk through Figure 3.4, which provides an illustration of integrity rules.
3. Explain the use of flags to avoid nulls, and use Table 3.5 to illustrate.
Take the time to discuss why using flags is preferable to using nulls.
Database Systems: Design, Implementation, and Management, 8th Edition 3-6
3.4 Relational Set Operators
1. Explain what relational algebra is, and discuss each of the eight relational
2. Explain the closure property.
3. Walk through the itemized list, which runs from page 73 to page 78, to discuss
each operator in more detail. Use Figures 3.5 through 3.17 to illustrate.
4. Explain that union-compatible means that two or more tables have the same
number of columns, and the columns have the same names and domains.
Emphasize that the JOIN operator is the real power behind a relational database.
5. Explain that a natural join links tables by selecting only rows with common
values in their common attributes.
Take time to walk through the three-step process resulting in a natural join.
6. Explain the different types of joins, including equijoins, theta joins, and left and
right outer joins
3.5 The Data Dictionary and the System Catalog
1. Explain that a data dictionary provides a detailed description of all tables found
within the database.
2. Explain that a data dictionary includes attribute names and characteristics for each
table in the system, and use Figure 3.4 and Table 3.6 to illustrate.
3. Explain that a system catalog contains metadata, and describes all objects within
Teaching It may be helpful to students to compare and contrast a data dictionary with a
Tip system catalog.
Database Systems: Design, Implementation, and Management, 8th Edition 3-7
4. Explain what homonyms and synonyms are in the context of a database.
Quick Quiz 2
1. A requirement of _____ integrity is that all primary key entries are unique and no
part of a primary key may be null.
2. A requirement of ____ integrity is that every non-null foreign key value must
reference an exiting primary key value.
3. When two or more tables share the same number of columns, when the columns
have the same names, and when they share the same domains, they are said to be
4. In a(n) ____ join, the matched pairs are retained and any unmatched values in the
other table would be left null.
3.6 Relationships within the Relational Database
1. Remind students what one-to-one, one-to-many, and many-to-many relationships
2. Emphasize that 1:M relationships are the norm in relational database design.
3. Emphasize that 1:1 relationships are rare in a relational database.
4. Remind students that many-to-many relationships cannot be implemented in a
3.6.1 The 1:M Relationship
1. Use Figures 3.18 through 3.21 to illustrate 1:M relationships and their
implementation in a relational database.
3.6.2 The 1:1 Relationship
1. Use Figures 3.22 and 3.23 to illustrate 1:1 relationships, reminding students they
are rare in a relational database.
2. Discuss conditions that require a 1:1 relationship.
Database Systems: Design, Implementation, and Management, 8th Edition 3-8
3.6.3 The M:N Relationship
1. Explain that the M:N relationship is not directly supported in a relational
2. Explain that M:N relationships are implemented by creating a new entity, which
has 1:M relationships with the original entities.
3. Use Figures 3.24 and 3.25 with Table 3.7 to illustrate M:N relationships.
4. Point out the problems in implementation discussed in the bulleted list on page
5. Explain what a composite entity is, and how its use avoids many of the problems
in M:N relationships.
6. Walk through Figures 3.26 through 3.29 to illustrate how M:N relationships are
3.7 Data Redundancy Revisited
1. Remind students what data anomalies are, and how they can be avoided using
Teaching Emphasize that using foreign keys will not eliminate data anomalies, but will
Tip minimize them.
2. Introduce situations when data redundancy must increase to make the database
serve information purposes, and walk through the example presented in Figures
3.30 and 3.31.
1. Explain that an index is an orderly arrangement to access rows in a table.
2. Explain that an index key is the index reference point, and each key points to the
location of the data identified by the key.
3. Use Figures 3.19 and 3.32 to illustrate the use of an index.
Database Systems: Design, Implementation, and Management, 8th Edition 3-9
4. Explain that a unique index is an index in which the key can have only one
pointer value associated with it.
5. Emphasize that although a table can have many indexes, each index is associated
with only one table.
3.8 Codd’s Relational Database Rules
1. Explain that Dr. E. F. Codd published a list of 12 rules to define a relational
database system, and discuss his purpose in doing so.
2. Walk through Table 3.8, which lists Dr. Codd's rules.
Teaching Remind students that most database vendors do not support all 12 of Dr. Codd’s
Tip rules, and take time to discuss why.
Quick Quiz 3
1. True or False: There are no situations that require the use of 1:1 relationships.
2. A(n) ____ table is the implementation of a composite entry.
3. A(n) ____ is an orderly arrangement used to logically access rows in a table.
4. True or False: According to Dr. Codd’s relational database rules, any view that is
theoretically updatable must be updatable through the system.
Class Discussion Topics
1. What types of joins are most appropriate for which types of problems? For
example, what types of problems would be best addressed with an equijoin or a
theta join compared to other types of joins?
2. Why is it not always appropriate to use the terminology “record” and “field” in
place of “row” and “column”?
Database Systems: Design, Implementation, and Management, 8th Edition 3-10
1. Compare and contrast a data dictionary and a system catalog.
2. Explain why M:N relationships cannot be directly implemented in a relational
1. An online article about relational databases:
2. A blog discussion of the limitations of the relational model:
3. A Wikipedia article listing Dr. Codd’s rules:
4. An Oracle page on data integrity:
associative entity—See composite entity.
attribute domain—See domain.
bridge entity—See composite entity.
candidate key—See key.
closure—A property of relational operators that permits the use of relational
algebra operators on existing tables (relations) to produce new relations.
composite entity—An entity designed to transform an M:N relationship into two
1:M relationships. The composite entity’s primary key comprises at least the
primary keys of the entities that it connects. Also known as a bridge entity. See
also linking table.
composite key—A multiple-attribute key.
data dictionary—A DBMS component that stores metadata—data about data.
Thus, the data dictionary contains the data definition as well as its characteristics
and relationships. A data dictionary may also include data that are external to the
DBMS. Also known as an information resource dictionary. See also active data
dictionary, metadata, and passive data dictionary.
determination—The role of a key. In the context of a database table, the
statement “A determines B” indicates that knowing the value of attribute A means
that the value of attribute B can be looked up (determined).
Database Systems: Design, Implementation, and Management, 8th Edition 3-11
domain—In data modeling, refers to the construct used to organize and describe
an attribute’s set of possible values.
entity integrity—The property of a relational table that guarantees that each
entity has a unique value in a primary key and that there are no null values in the
equijoin—A join operator that links tables based on an equality condition that
compares specified columns of the tables.
flags—Special codes implemented by designers to trigger a required response, to
alert end users to specified conditions, or to encode values. Flags may be used to
prevent nulls by bringing attention to the absence of a value in a table.
foreign key (FK)—See key.
full functional dependence—A condition in which an attribute is functionally
dependent on a composite key but not on any subset of that composite key.
functional dependence—Within a relation R, an attribute B is functionally
dependent on an attribute A if and only if a given value of the attribute A
determines exactly one value of the attribute B. The relationship “B is dependent
on A” is equivalent to “A determines B” and is written as A B.
homonyms—Indicates the use of the same name to label different attributes;
generally should be avoided. Some relational software automatically checks for
homonyms and either alerts the user to their existence or automatically makes the
appropriate adjustments. See also synonym.
index—An ordered array composed of index key values and row ID values
(pointers). Indexes are generally used to speed up and facilitate data retrieval.
Also known as an index key.
index key—See index.
join column(s)—A term used to refer to the columns that join two tables. The
join columns generally share similar values.
key—An entity identifier based on the concept of functional dependence; may be
classified as follows: Superkey: An attribute (or combination of attributes) that
uniquely identifies each entity in a table. Candidate key: A minimal attribute that
is itself a superkey. Primary key (PK): A candidate key selected as a unique entity
identifier. Secondary key: A key that is used strictly for data retrieval purposes.
For example, a customer is not likely to know his or her customer number
(primary key), but the combination of last name, first name, middle initial, and
telephone number is likely to make a match to the appropriate table row. Foreign
key: An attribute (or combination of attributes) in one table whose values must
match the primary key in another table or whose values must be null.
key attribute—The attribute(s) that form(s) a primary key. See also prime
left outer join—In a pair of tables to be joined, a left outer join yields all of the
rows in the left table, including those that have no matching values in the other
table. For example, a left outer join of Customer with Agent will yield all of the
Customer rows, including the ones that do not have a matching Agent row. See
also outer join and right outer join.
linking table—In the relational model, a table that implements a M:M
relationship. See also composite entity.
Database Systems: Design, Implementation, and Management, 8th Edition 3-12
natural join—A relational operation that links tables by selecting only the rows
with common values in their common attribute(s).
null—In SQL, refers to the absence of an attribute value. Note: A null is not a
outer join—A relational-algebra JOIN operation that produces a table in which
all unmatched pairs are retained; unmatched values in the related table are left
null. Contrast with inner join. See also left outer join and right outer join.
predicate logic—Used extensively in mathematics, provides a framework in
which an assertion (statement of fact) can be verified as either true or false. For
example, suppose that a student with a student ID of 12345678 is named Melissa
Sanduski. That assertion can easily be demonstrated to be true or false.
primary key (PK)—In the relational model, an identifier composed of one or
more attributes that uniquely identifies a row. See also key.
referential integrity—A condition by which a dependent table’s foreign key
must have either a null entry or a matching entry in the related table. Even though
an attribute may not have a corresponding attribute, it is impossible to have an
relational algebra—A set of mathematical principles that form the basis of the
manipulation of relational table contents; composed of eight main functions:
SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT,
relational schema—The description of the organization of a relational database
as seen by the database administrator.
right outer join—In a pair of tables to be joined, a right outer join yields all of
the rows in the right table, including the ones with no matching values in the other
table. For example, a right outer join of CUSTOMER with AGENT will yield all
of the agent rows, including the ones that do not have a matching CUSTOMER
row. See also left outer join and outer join.
secondary key—A key that is used strictly for data retrieval purposes. For
example, a customer is not likely to know his or her customer number (primary
key), but the combination of last name, first name, middle initial, and telephone
number is likely to make a match to the appropriate table row. See also key.
set theory—A mathematical science component that deals with sets, or groups of
things, and is used as the basis for data manipulation in the relational model.
synonym—The use of different names to identify the same object, such as an
entity, an attribute, or a relationship; should generally be avoided. See also
system catalog—A detailed system data dictionary that describes all objects in a
theta join—A join operator that links tables, using an inequality comparison
operator (<, >, <=, >=) in the join condition.
tuple—In the relational model, a table row.
union-compatible—Two or more tables are union-compatible when they share
the same column names and the columns have compatible data types or domains.
Database Systems: Design, Implementation, and Management, 8th Edition 3-13
unique index—An index in which the index key can have only one pointer value
(row) associated with it.