CIS431  Dr. J. Scher Home Mini Exam #9   Due: November 12, 2003 6:00PM

Normalization & Database Design

READ::  In D. Kroenke's DATABASE PROCESSING, read  pp.125-131 (Normalization: 1NF, 2NF, 3NF, BCNF,4NF). View Powerpoint presentations (in week 9) on Normalization of Relations (1NF, 2NF, 3NF, BCNF,4NF).  Also review the prior week's material on Functional Dependencies.

    Are you aware of the fact that Newark, New Jersey has a professional wrestling stadium, called the WrestlingMania Dome, with a seating capacity of  36,000?  (If you are not aware of this, then please just pretend!)

    Approximately every week, there is an event with several matches, featuring some of the leading wrestlers in the country. The management of WrestlingMania Dome is required by the NWF (National Wrestling Federation) to keep a record of the winner of each match, and other pertinent information about the wrestlers involved, and submit this to the NWF at the end of the wrestling season. Each wrestler has exactly one manager/trainer, and this manager trainer will be with the wrestler for the entire wrestling season, and collects the entire Purse for the match (which, of course, he shares with the wrestler). Furthermore, each wrestler is weighed at the beginning of the wrestling season, and this wrestler weight is specified in the table for each match. The loser (and the losermanager) do not receive any part of the purse.

    The National Wrestling Federation requires that each wrestler (and manager) have a unique name, so we have some fairly unique and descriptive names for our wrestlers and managers. (No two wrestlers or managers will ever have the identical name). It is also a known fact that, by NWF rules, a given wrestler is not allowed to wrestle twice on the same night. (However, since a given manage/trainer is allowed to manage and train 'many' wrestlers, a manager may be associated with several matches on the same night.) Furthermore, the NWF allows a 're-match' involving two wrestlers who were opponents in a prior match.

    The table below provides a sample of  what the recent data collected by the WrestlingMania Dome looks like:
  

a) Identify all functional dependencies which have a determinant consisting of one or two attributes.

b) Determine all candidate keys of the above relation which contain the attribute "Winner."

c) Can (Winner, Purse) be a candidate key of the above relation? EXPLAIN CLEARLY USING FUNCTIONAL DEPENDENCIES.

d) Determine a composite primary key of the above relation which contains the attribute "Winner."

e) Give one superkey of the above relation which contains the attribute "Winner."

f)  Does the above table satisfy the requirements for Second Normal Form? If yes, explain clearly why, using functional dependencies. If no, explain clearly what it is which violates 2NF.

g) Since the domains of winners and losers are identical, namely wrestlernames, let us consider an attribute called wrestler, and also an attribute called Managerssnum. Suppose we begin to create the following two relations, which may have additional attributes with them, with a goal of finding a decomposition which will satisfy Third Normal Form.

 R1 (Wrestler, Managerssnum, ...) 

 R2 (Date, Winner, Loser, ...) 

      In R2, Winner and Loser are foreign keys pointing to a particular row of R1. 

1. What will be the candidate key(s) of R1? What will be the primary key of R1 ?

2. What will be the candidate key(s) of R2? What will be the primary key of R2 ?

3. Should any additional attributes appear in R1? (If yes, specify them).

4. Should any additional attributes appear in R2? (If yes, specify them)

5. Specify clearly the additional relations (besides R1 and R2), and the attributes within these relations, which will form a decomposition and satisfy 3NF.

6. Will the decomposition you just specified in (5) also satisfy BCNF? Explain clearly (a plain "yes" or "no" will not be sufficient).

h. Explain why the 'decomposed' relation above is a 'better' database design than the original relation. (Explain specifically in terms of anomalies.)

i) Suppose that NWF changed their rules, and required that each wrestler be weighed on each night they wrestled, and that this current weight be used in the records describing the wrestling matches each night. (Thus, for example, though "Hollywood Hogan" weighed 294 pounds on the night of 10/09/03 when he was defeated by the heavier "Andre the Giant," he decided to gain some weight for his next match, and, on 10/27/03, his actual weight was 327 pounds, which value would have been recorded in the database under the changed rules.)

Describe what changes you would need to make in R1 and R2 (and any relations added in part 5 of question g above) to insure that the decomposed relations, using the new NWF rules, also satisfy 3NF.

In order to further the education of professional wrestlers, who often travel around the country and are thus unable to attend a traditional college, the TIJN Office of Distance Learning has entered into an arrangement with the National Wrestling Federation, whereby all NWF certified wrestlers (there are several thousand such NWF wrestlers) will enroll for a baccalaureate degree at TIJN, and earn a degree in any traditional major (or even a double degree, where a student may earn two degrees in two different majors), and be advised by a regular professor at TIJN (where each professor is associated with only one degree program).

Below is a 'glimpse' of the relation that TIJN has created to store this data:
 
 

a. What is the primary key of this relation?
b. Identify all functional dependencies present in this relation, from the rules governing the participation of professors and wrestlers in this program (you may assume that each professor's name is unique, i.e., no two professors have the same name).
c. Is this relation in 3NF? Explain clearly. (If it is not in 3NF, then convert to 3NF)
d. Is the relation (or relations from part (c)) in Boyce Codd Normal Form (BCNF)? If not, convert to BCNF, and show the resulting instances in the form of the redefined relations.

On page 147-148,  problem 5.25, look at the PROJECT-HOURS relation.

1. Answer questions A, B, C and D

2. What is the primary key of the PROJECT-HOURS relation?

3. Decompose this relation so that it will satisfy 4NF.