Database Storage & Design Chapter 3 Homework From The Sample Sales Data Would Appear

Chapter 3 – The Relational Model and Normalization

Page 3-21

c. Transform this table into two or more tables such that each table is in BCNF and in

4NF. State the primary keys, candidate keys, foreign keys, and referential integrity

constraints.

We’ll move the obvious multivalued dependencies into their own tables, and then check for

BCNF. IF we have BCNF and no multivalued dependencies, we also have 4NF:

STEP ONE: MOVE MUTIVALUED DEPENDENCIES INTO SEPARATE TABLES:

STEP TWO: CHECK EACH OF THE RESULTING TABLES FOR BNCF:

STEP TWO (A): CHECK STUDENT

STUDENT_2 FUNCTIONAL DEPENDENCIES:

STUDENT_2 (StudentNumber, StudentName, Dorm, RoomType, DormCost)

Is every determinant a candidate key?

NO, RoomType is NOT a candidate key.

Therefore the relation is NOT in BCNF.

Chapter 3 – The Relational Model and Normalization

Page 3-22

STEP TWO (A) (1): CHECK STUDENT_3:

STUDENT FUNCTIONAL DEPENDENCIES:

STUDENT_3 (StudentNumber, StudentName, Dorm, RoomType)

STUDENT CANDIDATE KEYS:

StudentNumber

Is every determinant a candidate key?

DORM_RATE CANDIDATE KEYS:

RoomType

Is every determinant a candidate key?

YES, Therefore DORM_RATE is in BNCF.

STEP TWO (B): CHECK STUDENT_CLUB_MEMBERSHIP

STUDENT_CLUB_MEMBERSHIP FUNCTIONAL DEPENDENCIES:

Chapter 3 – The Relational Model and Normalization

Page 3-23

Is every determinant a candidate key?

NO, Club is not a candidate key.

STUDENT_CLUB_MEMBERSHIP CANDIDATE KEYS:

(StudentNumber, Club)

Is every determinant a candidate key?

YES, in this case there are NO determinants, but this

STEP TWO (B) (2): CHECK STUDENT_CLUB_COST:

STUDENT_CLUB_COST FUNCTIONAL DEPENDENCIES:

STUDENT_CLUB_COST (Club, ClubCost)

Is every determinant a candidate key?

Chapter 3 – The Relational Model and Normalization

Page 3-24

YES, Therefore STUDENT_CLUB_COST is in BNCF.

STEP TWO (C): CHECK STUDENT_SIBLING

STUDENT_SIBLING FUNCTIONAL DEPENDENCIES:

Is every determinant a candidate key?

YES, in this case there are NO determinants, but this

meets the criteria!

STUDENT_NICKNAME FUNCTIONAL DEPENDENCIES:

STUDENT_NICKNAME (StudentNumber, Nickname)

None StudentNumber does not determine Nickname,

Is every determinant a candidate key?

YES, in this case there are NO determinants, but this

meets the criteria!

Chapter 3 – The Relational Model and Normalization

Page 3-25

ALL TABLES ARE NOW IN BCNF AND 4NF!

STEP THREE: STATE FINAL MODEL SPECIFICATIONS:

Primary Keys are underlined.

Foreign Keys are italicized.

Non-Primary Key Candidate Keys (Alternate Primary Keys) are stated following

each relation as Alternate Keys [NOTE: None exist].

Referential Integrity Constraints are stated following each relation.

❖ 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 3-32 shows data that Regional Labs collects

about projects and the employees assigned to them. This data is stored in a relation (table)

named PROJECT:

Chapter 3 – The Relational Model and Normalization

Page 3-26

A. Assuming that all functional dependencies are apparent in this data, which of the

following are true?

1. ProjectID → EmployeeName FALSE

B. What is the primary key of PROJECT?

(ProjectID, EmployeeName)

C. Are all the non-key attributes (if any) dependent on the primary key?

D. In what normal form is PROJECT?

1NF ONLY

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?

Chapter 3 – The Relational Model and Normalization

Page 3-27

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?

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.

Chapter 3 – The Relational Model and Normalization

Page 3-28

❖ THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS

Figure 3-33 shows typical sales data for the Queen Anne Curiosity Shop, and Figure 3-34

shows typical purchase data.

Chapter 3 – The Relational Model and Normalization

Page 3-29

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:

LastName → (FirstName, Phone)

Chapter 3 – The Relational Model and Normalization

Page 3-30

However, these are based on a very limited dataset and cannot be trusted. For example, name is

not a good determinant in a retail application; there may be many customers with the same name.

It’s also possible that some customers could have the same phone, even though they do not in this

example. The one trustable functional dependency here is:

Price → (Tax, Total)

From the sample purchase data it would appear:

(PurchaseItem, PurchasePrice) → (PurchaseDate, Vendor, Phone)

However, these are based on a very limited dataset and cannot be trusted. For example,

PurchaseItem is not a good determinant in a retail application; there may be many PurchaseItems

with the same designator. It’s also possible that multiple purchases on the same purchase date

will be for the same purchase price and not necessarily for the same purchase item. The most

trustworthy functional dependencies here are:

B. Given your assumptions in part A, comment on the appropriateness of the following

designs:

1. CUSTOMER (LastName, FirstName, Phone, EmailAddress, InvoiceDate,

InvoiceItem, Price, Tax, Total)

2. CUSTOMER (LastName, FirstName, Phone, EmailAddress, InvoiceDate,

InvoiceItem, Price, Tax, Total)

3. CUSTOMER (LastName, FirstName, Phone, EmailAddress, InvoiceDate,

InvoiceItem, Price, Tax, Total)

Chapter 3 – The Relational Model and Normalization

Page 3-31

4. CUSTOMER (LastName, FirstName, Phone, EmailAddress, InvoiceDate,

InvoiceItem, Price, Tax, Total)

5. CUSTOMER (LastName, FirstName, Phone, EmailAddress, InvoiceDate,

InvoiceItem, Price, Tax, Total)

6. CUSTOMER (LastName, FirstName, Phone, EmailAddress)

and:

7. CUSTOMER (LastName, FirstName, Phone, EmailAddress, InvoiceDate)

and:

8. CUSTOMER (LastName, FirstName, Phone, EmailAddress, InvoiceDate,

InvoiceItem)

and:

SALE (InvoiceDate, InvoiceItem, Price, Tax, Total)

Chapter 3 – The Relational Model and Normalization

Page 3-32

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?

The main problem with the design in part C is that only one item can be included in each

sale. Moving items into a SALE_ITEM table linked to SALE will allow multiple items

to be purchased as part of one sale. We’ll need to include SaleID as part of a composite

primary key so that the sale items are grouped according to their corresponding SALE.