A Guide to SQL
10th Edition Mark Shellman, Hassan Afyouni, Philip Pratt, Mary Last
Table of Contents:-
Module 1. Introduction to KimTay Pet Supplies and StayWell Student Accommodation Databases
Module 2. Database Design Fundamentals
Module 3. Creating Tables
Module 4. Single-Table Queries
Module 5. Multiple-Table Queries
Module 6. Updating Data
Module 7. Database Administration
Module 8. Functions, Procedures and Triggers
,A Guide to SQL 10th Edition Shellman
Complete Pack Solutions Manual
Modules and Lab Assessment Solution
Answers to End of Module Questions
Quiz File Solutions
SQL Lab Solutions
Chapter No. 01 : Introduction to Kimtay Pet Supplies and
StayWell Student Accommodation Databases
At a Glance
IM Table of Contents:-
• Overview
• Chapter Objectives
• Teaching Tips
• Quick Quizzes
• Class Discussion Topics
• Additional Projects
• Additional Resources
• Key Terms
,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.
, 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.
10th Edition Mark Shellman, Hassan Afyouni, Philip Pratt, Mary Last
Table of Contents:-
Module 1. Introduction to KimTay Pet Supplies and StayWell Student Accommodation Databases
Module 2. Database Design Fundamentals
Module 3. Creating Tables
Module 4. Single-Table Queries
Module 5. Multiple-Table Queries
Module 6. Updating Data
Module 7. Database Administration
Module 8. Functions, Procedures and Triggers
,A Guide to SQL 10th Edition Shellman
Complete Pack Solutions Manual
Modules and Lab Assessment Solution
Answers to End of Module Questions
Quiz File Solutions
SQL Lab Solutions
Chapter No. 01 : Introduction to Kimtay Pet Supplies and
StayWell Student Accommodation Databases
At a Glance
IM Table of Contents:-
• Overview
• Chapter Objectives
• Teaching Tips
• Quick Quizzes
• Class Discussion Topics
• Additional Projects
• Additional Resources
• Key Terms
,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.
, 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.
