A Guide to SQL, Ninth Edition Page 2–1
Chapter 2
Database Design Fundamentals
At a Glance
Instructor’s Manual Table of Contents
• Overview
• Objectives
• Teaching Tips
• Quick Quizzes
• Class Discussion Topics
• Additional Projects
• Additional Resources
• Key Terms
A Guide to SQL, Ninth Edition Page 2–2
Lecture Notes
Overview
In this chapter, students learn about database design. Students examine the important
concepts related to databases. They learn how to identify tables and columns and how to
identify the relationships between the tables. Students learn how to produce an appropriate
database design for a given set of requirements. They examine the process of
normalization, a process that identifies and fixes potential problems in a database design.
Finally, students learn how to visually represent a database design.
Chapter Objectives
In this chapter, students learn about:
• What the terms entity, attribute, and relationship mean
• What the terms relation and relational database mean
• What functional dependencies are and how to identify when one column is functionally
dependent of another
• What the term primary key means and how to identify primary keys in tables
• How to design a database to satisfy a set of requirements
• How to convert an unnormalized relation to first normal form
• How to convert tables from first normal form to second normal form
• How to convert tables from second normal form to third normal form
• How to create an entity-relationship diagram to represent the design of a database
Teaching Tips
Introduction
1. Define database design. Database design is the process of determining the particular
tables and columns that will comprise a database.
A Guide to SQL, Ninth Edition Page 2–3
Teaching
Tip
This chapter does not need to be covered in sequence. It can be covered later in
the course. If you are using a textbook such as Pratt and Last’s Concepts of
Database Management, Eighth Edition, you may want to skip this chapter
entirely.
Be prepared to spend considerable class time on this chapter. The material is
complex, and it is important that students understand all of the concepts
presented. The best way for students to learn the material is to work through lots
of examples. Use the embedded questions that are included throughout the
chapter to test students’ understanding.
Encourage students to bring their texts with them to class so that they can review
the examples.
Database Concepts
1. An understanding of fundamental database concepts is essential to good database
design.
Relational Databases
1. Define relational database. A relational database is a collection of tables. Formally,
tables are called relations.
3. Review the Note on page 23.
Entities, Attributes, and Relationships
1. Define entity. An entity is a person, place, object, event, or idea for which you want to
2. Define attribute. An attribute is a characteristic or property of an entity. The terms
3. Define relationship and one-to-many relationship. A relationship is an association
4. In a relational database, each entity has its own tables, and the attributes of the entity are
5. Use Figure 2-1 to illustrate the one-to-many relationship between sales reps and
©2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part,
6. Use Figure 2-2 to illustrate repeating groups (multiple entries in an individual location
in a table).
7. Define relation. A relation is a two-dimensional table with specific properties. These
properties include:
8. Use Figure 2-3 to discuss the six properties of a relation.
9. See the Note on page 26. Mention that the formal term for a table is relation, and the
10. DBDL (Database Definition Language) is a commonly accepted shorthand notation for
showing the structure of a table. After the name of the table, all of the columns in the
11. When two or more tables in a database use the same column name, qualify the column
name; that is, combine the table name and the column name
Teaching
Tip
Reinforce the material in this section by using either the Colonial Adventure
Tours database or the Solmaris Condominium Group database and asking
students to identify the entities, attributes, and relationships.
Use review question 11 on page 55 as an in-class exercise to test students’
understanding of entities attributes and relationships.
Quick Quiz 1
1. A(n) _____ is a person, place, object, event, or idea for which you want to store and
process data.
2. A(n) _____ is a characteristic or property of an entity.
3. A(n) _____ is the association between entities.
A Guide to SQL, Ninth Edition Page 2–5
Functional Dependence
1. Functional dependence is a formal name for what is basically a simple idea. In a
relational database, column B is functionally dependent on another column A (or
2. Use Figure 2-4 to explain functional dependence. Make sure that students understand
4. Use Figures 2-5 and 2-6 to point out that you cannot determine functional dependencies
by looking at sample data. You must understand the users’ policies
Teaching
Tip
Use review question 11 as an in-class exercise to test students’ understanding of
functional dependencies.
Primary Keys
1. To make each row distinct, one or more columns must uniquely identify a given row in
a table. This column or collection of columns is called the primary key.
2. A more precise definition for a primary key is the following:
Column (attribute) A (or a collection of columns) is the primary key for a table
4. Explain that, when using the shorthand representation of a database, the primary key is
underlined.
6. Point out that a candidate key is a column or collection of columns on which all
columns in the table are functionally dependent. The definition for a primary key really
defines a candidate key as well. If two or more columns in a table are identified as
candidate keys, choose one to be the primary key. The decision is usually based on the
specific application for which the database will be used.
Teaching
Tip
Use Review Question 11 as an in-class exercise to test students’ understanding of
primary keys.
A Guide to SQL, Ninth Edition Page 2–6
Quick Quiz 2
1. The _____ is the unique identifier for a table.
2. If a table includes one or more columns that can be used as a primary key, both columns
are referred to as _____.
3. To indicate a table’s primary key with a shorthand representation of a database, _____
the column or collection of columns that comprise the primary key.
Database Design
1. Point out that the determination of the database requirements is part of the process
known as systems analysis.
Design Method
1. Review the design steps given in this section.
(1) Read the requirements, identify the entities (objects) involved, and name the
entities.
(3) Identify the attributes for all of the entities.
(5) Use the functional dependencies to identify the tables by placing each attribute
(6) Identify any relationships between tables.
Teaching
Tip
Use Figure 2-1 as a visual aid as you explain each of the steps above and ask the
students to identify the items listed in the steps.
Database Design Requirements
1. Review the requirements that the database for TAL Distributors must support. The
2. Mention that there are certain constraints, such as, “there is only one customer per
order” that the database must enforce.
Teaching
Use Figure 2-1 to illustrate the requirements.
A Guide to SQL, Ninth Edition Page 2–7
©2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part,
except for use as permitted in a license distributed with a certain product or service or otherwise on a password–
2. Point out the redundancy; that is, duplication of data in Figure 2-9. This duplication
can cause update anomalies.
3. Update anomalies occur when a column is dependent on only a portion of the primary
key and fall into four categories:
Update
Instead of changing one row, it is necessary to update multiple rows.
Inconsistent
data
If the same value appears in more than one row, for example, part
description, an update could change one row without changing the other
rows.
Additions
Cannot add a record correctly.
Deletions
Cannot delete a record correctly.
4. Emphasize the fact that much real-world data (including relational data) are not well
structured and have update anomalies.
5. Define second normal form (2NF). Second normal form eliminates update anomalies
7. Mention the Note on page 44. If a relation has a single-column primary key , it
automatically is in 2NF.
Third Normal Form
1. Use Figure 2-11 to illustrate update anomalies with a table in 2NF.
2. Define determinant. Any column or collection of columns that determines another
4. Mention the Note on page 48. The definition used in this text for 3NF is really the
definition for Boyce-Codd normal form (BCNF).
6. Review the embedded Question and Answer on pages 50 and 51.
Teaching
Tip
Point out that normalization is a technique that allows us to analyze the design of
a relational database to see whether it is bad. It alerts us to update anomalies and
provides a method for correcting those problems. The goal of normalization is to
start with a table or collection of tables and produce a new collection of tables
that is equivalent (represents the same information) but is free of problems.
Emphasize to students that normalization does not add additional attributes or
A Guide to SQL, Ninth Edition Page 2–9
remove attributes from tables; it merely rearranges the attributes into an
equivalent collection of tables. Many students seem to think that they need to add
more attributes to normalize relations in a database. Normalization alerts us to
problems with the database design and provides a method to correct those
problems. Edgar Codd developed the process and gave it the name
normalization.
Quick Quiz 3
1. A relation is in _____ normal form if no repeating groups exist.
2. If the primary key of a relation contains only a single column, then the relation is
automatically in _____ normal form.
3. Any column (or collection of columns) that determines another column is called a(n)
_____.
Diagrams for Database Design
1. Remind students of the old adage that “a picture is worth a thousand words.” For many
people, a database design is easier to understand if it is depicted in graphical form.
2. Explain an entity-relationship (E-R) diagram. In an E-R diagram, a rectangle represents
4. Use Figures 2-13 through 2-15 to illustrate the different styles of E-R diagrams.
Class Discussion Topics
1. Ask students for other examples of relations (tables) that could have more than one
2. Have students read the Note on page 31. Ask them how they feel about using Social
3. Ask students for examples of unnormalized relations in a student environment. One
example is students and courses. Courses will be the repeating group. How would they
convert to 1NF?
Additional Projects
1. Place students in teams. Have them design a database to meet the requirements for a
student activity database. The database must keep track of information about the student
2. Divide the class into small groups and assign Review Questions 11 and 15 as a group
A Guide to SQL, Ninth Edition Page 2–11
Additional Resources
2. Database Concepts: www.service-architecture.com/database/articles/
Key Terms
➢ attribute: A characteristic or property of an entity
➢ Boyce-Codd normal form (BCNF): A relation is in Boyce-Codd normal form if it is in
second normal form and the only determinants it contains are candidate keys; also called
third normal form
➢ candidate key: A minimal collection of columns in a table
➢ concatenation: A combination of columns
➢ field : An attribute
➢ first normal form (1NF): A table that does not contain any repeating groups
➢ functionally dependent: Column B is functionally dependent on column A (or on a
collection of columns) if a value for A determines a single value for B at any one time
➢ functionally determine: Column A functionally determines column B if B is functionally
dependent on A
➢ nonkey column: A column that is not part of the primary key
➢ qualify: To combine a column name with a table name
➢ record: A row in a table
➢ redundancy: Duplication of data
➢ relation: A two-dimensional table in which the entries are single valued; each column has a
distinct name (or attribute name); all values in a column are values of the same attribute; the
order of the rows and columns is immaterial; and each row contains unique values
©2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part,
except for use as permitted in a license distributed with a certain product or service or otherwise on a password–
protected website for classroom use.