Solution Manual + Answer Key

Solution Manual for A Guide to SQL 10th Edition by Philip J. Pratt

View Full Product:

https://selldocx.com/products/solution-manual-a-guide-to-sql-10e-pratt

Book Title: A Guide to SQL

Edition: 10th Edition

Author: Philip J. Pratt

Click above to view a sample

A Guide to SQL, Tenth Edition Instructor’s Manual Page 1-1

Chapter 1

Introduction to Kimtay Pet Supplies and StayWell Student

Accommodation Databases

At a Glance

Instructor’s Manual Table of Contents

 Overview

 Chapter Objectives

 Teaching Tips

 Quick Quizzes

 Class Discussion Topics

 Additional Projects

 Additional Resources

 Key Terms

©2021 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.
A Guide to SQL, Tenth Edition Instructor’s Manual Page 1-2

Lecture Notes

Overview

In this chapter, students examine the database requirements of KimTay Pet Supplies—a
company that is used in the examples throughout the text. Students also examine the
database requirements for StayWell, whose database is featured in the exercises that appear
at the end of each module.

Chapter Objectives

After completing this chapter, students will be able to accomplish the following:
 Introduce KimTay Pet Supplies, a company whose database is used to manage a pet
supplies business. The KimTay database is used as the basis for many of the examples
throughout the text.
 Introduce StayWell, a Seattle-based company whose database is used to manage
accommodation for university students on behalf of property owners. The StayWell
database is used as an additional case that runs throughout the text.

Teaching Tips

What Is a Database?

1. Define database. A database is a structure that contains different categories of

information and the relationships between these categories.
2. The KimTay Pet Supplies database contains information about categories such as sales
representatives (sales reps), customers, invoices, and items.
3. The StayWell database contains information about the offices that manage the
accommodation, the owners of the accommodation, the residents, and the services (such
as cleaning and maintenance) offered for the properties.
4. Each database also contains relationships between categories. The following are some
examples:
a. The KimTay Pet Supplies database contains information that relates sales reps to
the customers they represent and customers to the invoices they have placed.
b. The StayWell database contains information that relates the two main company
offices to the properties that they manage, the owners, the different services to
the service request, and to the resident renting a property.

©2021 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.
A Guide to SQL, Tenth Edition Instructor’s Manual Page 1-3

Make sure that students understand the types of businesses and categories that
are used to describe the databases. Ask them for corresponding real-life
Teaching
examples of the sample businesses. For example, what pet supplies stores are in
Tip
your town? Have they ever been contacted by agencies that manage student
accommodations?

The Kimtay Pet Supplies Database

1. KimTay Pet Supplies, a supplier of pet supplies, food, and accessories located in Cody,
Wyoming, is a fictitious company that will be used as an example throughout the text.
The company must maintain customer, invoice, and inventory data using its manual
systems.
2. Figure 1-1 shows a sample invoice for KimTay Pet Supplies. Use the figure to point out
that an invoice includes data from all of the tables in the KimTay Pet Supplies database.
The data in the Total column is not stored in the database but calculated whenever an
invoice is printed or displayed on the screen.
3. Figure 1-2 lists the five tables that make up the KimTay Pet Supplies database. Each
table represents a category. The data in the tables is related through common fields.
These relationships allow the user to access data from more than one table and produce
reports, queries, and forms.
4. Use Figure 1-2 to explain the relationships in the KimTay Pet Supplies database. There
is a one-to-many relationship between the SALES_REP table and the CUSTOMER
table; that is, one sales rep can represent many customers. There is a one-to-many
relationship between the CUSTOMER table and the INVOICES table because one
customer can place many orders. Because the same item can be found on many orders
and one order can include many items, there is a many-to-many relationship between
the ITEM and the INVOICES table. The INVOICE_LINE table relates the ITEM and
INVOICES table by including both the INVOICE_NUM field and the ITEM_ID field.
The INVOICE_NUM field is the common field between the INVOICES and the
INVOICES_LINE tables. The ITEM_ID field is the common field between the ITEM
and INVOICE_LINE tables.
5. Point out the problems with storing data in the table structure shown in Figure 1-3. This
alternative table structure violates the definition of a relation. (There are multi-valued
entries; the rows are not distinct.)
6. Review the “Q&A” section on page 6 of this chapter to make sure that students
understand the KimTay Pet Supplies database.

©2021 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.
A Guide to SQL, Tenth Edition Instructor’s Manual Page 1-4

Use Figure 1-1 and ask students to analyze the invoice. From which table does
each piece of data come? (All tables in the KimTay Pet Supplies database are
used.) Which entries are calculated from the data in the database? (The entries in
Teaching
the Total column are calculated.)
Tip
Use one (or more) of the KimTay Pet Supplies exercises on page 12 as an in-
class exercise.

Quick Quiz 1
1. A(n) ____ is a structure that contains different categories of information and the
relationships between these categories.
Answer: database

StayWell Student Accommodation Database

1. Describe StayWell Student Accommodation. StayWell finds and manages

accommodation for owners of student accommodation in the Seattle area.
2. StayWell has decided that the best way to increase efficiency and move toward an e-
commerce-based business model is to store all the data about the properties, owners,
tenants, and services in databases.
3. Use Figures 1-4 through 1-9 to describe the type of data that StayWell Student
Accommodation needs to maintain. There are six tables in the database: OFFICE,
OWNER, PROPERTY, SERVICE_CATEGORY, SERVICE_REQUEST, and
RESIDENTS.
4. Use Figures 1-4 through 1-9 to explain the relationships. There is a one-to-many
relationship (one customer can make many reservations) between the
SERVICE_CATEGORY table and the SERVICE_REQUEST table. The common field
between the two tables is CATEGORY_NUM. Because an owner could have more than
one property, there is a one-to-many relationship between the OWNER table and the
PROPERTY table with OWNER_NUM being the common field. There is a many-to-
many relationship between the PROPERTY table and the SERVICE_REQUEST table.

Teaching Use one (or more) of the StayWell Student Accommodation exercises on page 13
Tip as an in-class exercise.

Quick Quiz 2

©2021 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.
A Guide to SQL, Tenth Edition Instructor’s Manual Page 1-5

1. In the StayWell Student Accommodation database, owner number _____ owns the
property with the ID 6.
Answer: MO100
2. In the StayWell Student Accommodation database, the corresponding description for
the CATEGORY_NUM associated with SERVICE_ID 2 is _____.
Answer: electrical systems
3. In the StayWell Student Accommodation database, the OFFICE_NAME associated
with PROPERTY_ID 7 is _____.
Answer: StayWell-Georgetown

Class Discussion Topics

1. Students interact with databases daily but often do not recognize that fact. Ask students
whether they have used Google or another search engine recently. Have students
purchased anything online? Did they use the online library catalog? How have they
interacted with any databases in the previous 24 hours? Write the list on the board to
emphasize the important role databases play in our daily lives.
2. Choose one of the two databases presented in this chapter. Ask students to provide other
data items that could be included. For example, the OWNER table in the StayWell
Student Accommodation database could include telephone numbers. Then, ask the
students how the data items should be stored; for example, should phone numbers
include area code? Why, or why not?

Additional Projects

1. Have students bring in an invoice for something that they have purchased recently. Can
they identify which items would be calculated and which items would be stored in the
database?
2. Have students create an invoice for StayWell Student Accommodation that would use
the OWNER, PROPERTY, and SERVICE_CATEGORY tables in the database.
3. Have students visit a local business to find out how the business uses a database.

Additional Resource

1. Concepts of Database Management, Eighth Edition by Philip Pratt and Mary Last
Cengage Learning, 2015.

©2021 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.
A Guide to SQL, Tenth Edition Instructor’s Manual Page 1-6

Key Term

 Database: A structure that contains different categories of information and the

relationships between these categories

©2021 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.