Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Chapter Two – The Relational Model
Page 20 of 38
D. Based on your answer to part C, show the data changes necessary to add a
second major for the second student.
StudentNumber
StudentName
SiblingName
Major
100
Mary Jones
Victoria
Accounting
100
Mary Jones
Slim
Accounting
100
Mary Jones
Reginald
Accounting
200
Fred Willows
Rex
Finance
100
Mary Jones
Victoria
InfoSystems
100
Mary Jones
Slim
InfoSystems
100
Mary Jones
Reginald
InfoSystems
200
Fred Willows
Rex
Accounting
E. Explain the differences in your answers to parts C and D. Comment on the
desirability of this situation.
F. Define and discuss 4NF and how 4NF can be used to allow a set of well-formed
relations.
G. Divide this relation into a set of well-formed relations.
If we split STUDENT into two relations, STUDENT and STUDENT-SIBLING, then we get:
FOR STUDENT:
Chapter Two – The Relational Model
Page 21 of 38
(StudentNumber, Major) → StudentName
STUDENT Candidate Keys: (StudentNumber, Major)
Is every determinant a candidate key?
FOR STUDENT-2:
Functional Dependencies:
Is every determinant a candidate key?
YES—StudentNumber is a candidate key—Normalization complete!
FOR STUDENT-MAJOR:
Functional Dependencies:
STUDENT_2 Candidate Keys: (StudentNumber, Major)
Is every determinant a candidate key?
YES—(StudentNumber, Major) is a candidate key—Normalization complete!
FINAL NORMALIZED REALTIONs:
2.42 The text states that you can argue that “the only reason for having relations is to store
instances of functional dependencies.” Explain, in your own words, what this means.
Chapter Two – The Relational Model
Page 22 of 38
2.43 Consider a table named ORDER_ITEM, with data as shown in Figure 2-26. The schema
ORDER_ITEM (OrderNumber, SKU, Quantity, Price)
Where SKU is a “Stock Keeping Unit” number, which is similar to a part number. Here it
indicates which product was sold on each line of the table. Note that one OrderNumber
must have at least one SKU associated with it, and may have several. Use this table and
the detailed discussion of normal forms on pages 99-100 to answer the following
questions.
A. Define 1NF. Is ORDER_ITEM in 1NF? If not, why not, and what would have to be
done to put it into 1NF? Make any changes necessary to put ORDER_ITEM into
1NF. If this step requires you to create an additional table, make sure that the
new table is also in 1NF.
First Normal Form is any table that meets the definition of a relation (Figure 2.1 below).
ORDER_ITEM is in 1NF.
B. Define 2NF. Now that ORDER_ITEM is in 1NF, is it also in 2NF? If not, why not,
and what would have to be done to put it into 2NF? Make any changes
necessary to put ORDER_ITEM into 2NF. If this step requires you to create an
additional table, make sure that the new table is also in 2NF.
Chapter Two – The Relational Model
Page 23 of 38
C. Define 3NF. Now that ORDER_ITEM is in 2NF, is it also in 3NF? If not, why not,
and what would have to be done to put it into 3NF? Make any changes
necessary to put ORDER_ITEM into 3NF. If this step requires you to create an
additional table, make sure that the new table and any other tables created in
previous steps are also in 3NF.
D. Define BCNF. Now that ORDER_ITEM is in 3NF, is it also in BCNF? If not, why
not, and what would have to be done to put it into BCNF? Make any changes
necessary to put ORDER_ITEM into BCNF. If this step requires you to create an
additional table, make sure that the new table and any other tables created in
previous steps are also in BCNF.
ANSWERS TO REGIONAL LABS CASE QUESTIONS
Regional Labs is a company that conducts research and development work on a contract basis
for other companies and organizations. Figure 2-33 shows data that Regional Labs collects
about projects and the employees assigned to them.
This data is stored in a relation (table) named PROJECT:
PROJECT (ProjectID, EmployeeName, EmployeeSalary)
Chapter Two – The Relational Model
Page 24 of 38
A. Assuming that all functional dependencies are apparent in this data, which of the
following are true?
B. What is the primary key of PROJECT?
C. Are all the nonkey attributes (if any) dependent on the primary key?
D. In what normal form is PROJECT?
E. Describe two modification anomalies that affect PROJECT.
The two modification anomalies that affect PROJECT are:
F. Is ProjectID a determinant? If so, based on which functional dependencies in part A?
G. Is EmployeeName a determinant? If so, based on which functional dependencies
in part A?
H. Is (ProjectID, EmployeeName) a determinant? If so, based on which functional
dependencies in part A?
Chapter Two – The Relational Model
Page 25 of 38
I. Is EmployeeSalary a determinant? If so, based on which functional dependencies in part
A?
J. Does this relation contain a transitive dependency? If so, what is it?
K. Redesign the relation to eliminate modification anomalies.
The following seems workable:
ANSWERS TO GARDEN GLORY PROJECT QUESTIONS
Garden Glory is a partnership that provides gardening and yard maintenance services to
individuals and organizations. Garden Glory is owned by two partners. They employ two
office administrators and a number of full- and part-time gardeners. Garden Glory will
provide one-time garden services, but it specializes in ongoing service and maintenance.
Many of its customers have multiple buildings, apartments, and rental houses that
require gardening and lawn maintenance services.
Figure 2-34 shows data that Garden Glory collects about properties and services.
A. Using these data, state assumptions about functional dependencies among the columns
of data. Justify your assumptions on the basis of these sample data and also on the
basis of what you know about service businesses.
From the data it appears that there are many functional dependencies that could be defined.
Some examples are:
Chapter Two – The Relational Model
Page 26 of 38
PropertyName → PropertyType
(PropertyName, Street) → (PropertyType, City, Zip)
B. Given your assumptions in part A, comment on the appropriateness of the following designs:
1. PROPERTY (PropertyName, PropertyType, Street, City, Zip, ServiceDate,
Description, Amount)
2. PROPERTY (PropertyName, PropertyType, Street, City, Zip, ServiceDate,
Description, Amount)
3. PROPERTY (PropertyName, PropertyType, Street, City, Zip, ServiceDate,
Description, Amount)
4. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip,
5. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip,
ServiceDate, Description, Amount)
Chapter Two – The Relational Model
Page 27 of 38
6. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip,
ServiceDate)
and
7. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip,
ServiceDate)
and:
8. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip,
ServiceID)
and:
SERVICE (ServiceID, ServiceDate, Description, Amount, PropertyID)
9. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip)
and:
SERVICE (ServiceID, ServiceDate, Description, Amount, PropertyID)
C. Suppose Garden Glory decides to add the following table:
SERVICE-FEE (PropertyID, ServiceID, Description, Amount)
Chapter Two – The Relational Model
Page 28 of 38
Here’s the best design from part B:
PROPERTY(PropertyID, PropertyName, PropertyType, Street, City, Zip)
SERVICE (ServiceID, Description)
ANSWERS TO JAMES RIVER JEWELRY PROJECT QUESTIONS
[NOTE: The James River Jewelry Project Questions are available online for Appendix D,
which can be downloaded from the textbook’s Web site:
www.pearsonhighered.com/kroenke. The solutions for these questions will be included
in the Instructor’s Manual for each chapter]
Chapter Two – The Relational Model
Page 29 of 38
A. Using these data, state assumptions about functional dependencies among the columns
of data. Justify your assumptions on the basis of these sample data and also on the
basis of what you know about retail sales.
From the data it would appear:
B. Given your assumptions in part A, comment on the appropriateness of the following
designs:
1. CUSTOMER (Name, Phone, EmailAddress, InvoiceNumber, InvoiceDate,
PreTaxAmount)
2. CUSTOMER (Name, Phone, EmailAddress, InvoiceNumber, InvoiceDate,
PreTaxAmount)
Chapter Two – The Relational Model
Page 30 of 38
3. CUSTOMER (Name, Phone, EmailAddress, InvoiceNumber, InvoiceDate,
PreTaxAmount)
4. CUSTOMER (CustomerID, Name, Phone, EmailAddress, InvoiceNumber,
InvoiceDate, PreTaxAmount)
5. CUSTOMER (Name, Phone, EmailAddress)
and
6. CUSTOMER (Name, Phone, EmailAddress)
and
PURCHASE (InvoiceNumber, InvoiceDate, PreTaxAmount, EmailAddress)
7. CUSTOMER (Name, EmailAddress)
and
PURCHASE (InvoiceNumber, Phone, InvoiceDate,
PreTaxAmount, EmailAddress)
Chapter Two – The Relational Model
Page 31 of 38
C. Modify what you consider to be the best design in part B to include a column called
AwardPurchaseAmount. The purpose of this column is to keep a balance of the
customers’ purchases for award purposes. Assume that returns will be recorded with
invoices having a negative PreTaxAmount.
D. Add a new AWARD table to your answer to part C. Assume that the new table will hold
data concerning the date and amount of an award that is given after a customer has
purchased 10 items. Ensure that your new table has appropriate primary and foreign
keys.
The new table is:
ANSWERS TO THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS
The Queen Anne Curiosity Shop sells both antiques and current-production household
items that complement or are useful with the antiques. For example, the store sells
antique dining room tables and new tablecloths. The antiques are purchased from both
individuals and wholesalers, and the new items are purchased from distributors. The
store’s customers include individuals, owners of bed-and-breakfast operations, and local
interior designers who work with both individuals and small businesses. The antiques
are unique, although some multiple items, such as dining room chairs, may be available
as a set (sets are never broken). The new items are not unique, and an item may be
reordered if it is out of stock. New items are also available in various sizes and colors
(for example, a particular style of tablecloth may be available in several sizes and in a
variety of colors).
Chapter Two – The Relational Model
Page 32 of 38
Figure 2-35 shows typical sales data for the Queen Anne Curiosity Shop, and Figure 2-36
shows typical purchase data.
Chapter Two – The Relational Model
Page 33 of 38
A. Using these data, state assumptions about functional dependencies among the columns
of data. Justify your assumptions on the basis of these sample data and also on the
basis of what you know about retail sales.
From the sample sales data it would appear:
B. Given your assumptions in part A, comment on the appropriateness of the following
designs:
Chapter Two – The Relational Model
Page 34 of 38
2. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, InvoiceItem, Price,
Tax, Total)
NOT GOOD. There may be many customers with the same last name and first name.
4. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, InvoiceItem, Price,
Tax, Total)
5. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, InvoiceItem, Price,
Tax, Total)
6. CUSTOMER (LastName, FirstName, Phone)
and:
7. CUSTOMER (LastName, FirstName, Phone, InvoiceDate)
and:
Chapter Two – The Relational Model
Page 35 of 38
8. CUSTOMER (LastName, FirstName, Phone)
and:
C. Modify what you consider to be the best design in part B to include surrogate ID columns
called CustomerID and SaleID. How does this improve the design?
The best design in part B was number 8, so we’ll put in the ID columns. These columns
will become the new primary keys, and we’ll need to adjust the foreign key so that it is
in SALE. The result is:
D. Modify the design in part C by breaking SALE into two relations named SALE and
SALE_ITEM. Modify columns and add additional columns as you think necessary. How
does this improve the design?
Chapter Two – The Relational Model
Page 36 of 38
E. Given your assumptions, comment on the appropriateness of the following designs:
1. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone)
2. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone)
3. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone)
4. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone)
5. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate)
and:
VENDOR (Vendor, Phone)
6. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor)
and:
VENDOR (Vendor, Phone)
Chapter Two – The Relational Model
Page 37 of 38
7. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor)
and:
VENDOR (Vendor, Phone)
F. Modify what you consider to be the best design in part E to include surrogate ID columns
called PurchaseID and VendorID. How does this improve the design?
The best design in part E was number 7, so we’ll put in the ID columns. These columns
will become the new primary keys, and we’ll need to adjust the foreign key in
PURCHASE. The result is:
Chapter Two – The Relational Model
Page 38 of 38
G. The relations in your design from part D and part F are not connected. Modify the
database design so that sales data and purchase data are related.
The connection between the two parts of the database design is the item being first purchased
and then sold. Thus, we can create an integrated design by replacing InvoiceItem in SALE_ITEM
with PurchaseID as a foreign key. We will rename Price in SALE_ITEM as SalePrice. Our final
design will be:
