Database Storage & Design Chapter 2 Two The Relational Model Based Your Answer Part Show The Data

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: