CIS431-DL                                   Dr. J. Scher                                        Home Mini Exam # 7

   Functional Dependencies & Database Normalization (to 3NF and BCNF)

READ:  In the Powerpoint Slides, view the modules on Functional Dependencies and Normalization of Relations, In D. Kroenke's DATABASE PROCESSING, read pp.121-126, 126-131, 131-133 (Functional Dependencies and Normalization: 1NF, 2NF, 3NF, BCNF) 

(RE)VIEW: Videotapes 8 and 9

DUE: your submission to DrScher431@lycos.com no later than 3:30 PM, Monday,  May 6, 2002. (Send as a regular email - do not send .doc attachments.) 

    Are you aware of the fact that Newark, New Jersey has a professional wrestling stadium, called the WrestlingMania Dome, with a seating capacity of  18,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). 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. 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. Is the above relation in 1NF? Why?

b. What are the candidate keys of the above relation (i.e., what minimal set of attributes functionally determine every attribute in the relation)?  [GIANT HINT: the candidate keys will be composite, consisting of two attributes.] Explain, using functional dependencies, the reason why these are candidate keys.

c. From your candidate keys, is there any candidate key containing the attribute "Winner?" If yes, use that as your primary key. (If not, check your work.)

d. In addition to your candidate keys, provide two other superkeys of the above relation.

e. Can (Winner, WinManagerSSNUM) be a candidate key? Explain precisely why, using functional dependencies. 
f. Determine all (non-trivial) functional dependencies which apply to the above relation. In creating your functional dependencies, do NOT use Winner and Looser, or Winmanager or Loosermanager as determinants in any FD; instead, use Wrestler and Manager as determinants. (So, one FD might be Wrestler --->Weight)

g. Explain why the above table does NOT satisfy the requirements for Second Normal Form, and give  specific instances why.
h. Decompose the above relation into a set of relations (using our symbolic notation for representing a relation - you do not need to deal with the actual data), each of which satisfies 2NF. For each such relation, identify the primary key and any foreign keys. For each foreign key, include a statement of the form: Foreign Key X, in relation R, points to relation S. (HINT: Note that the domains of winners and loosers are identical, namely wrestlernames, so you might wish to have a relation called wrestlers, as opposed to 'winners' (and/or 'losers')).
i. Do each of the relations in part (e) satisfy Third Normal Form? If any do not, convert them into 3NF, re-identifying primary and foreign keys as per part (e).
j. Explain why your 'decomposed' relation is a 'better' database design than the original relation.

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 T I J N 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), 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.

a) Use Armstrong's axioms to prove the soundness of the Pseudotransitivity Rule.

b) Which of the following five functional dependencies ( (a), (b), (c), (d) or (e) ) are in the closure F+ of relational scheme R = (A, B, C, D, E), if the three FD's A ---> BC, B ---> E and D ---> A hold on the relation R? Explain/justify your answers. {HINT: See which can be derived using Armstrong's axioms and the Union, Decomposition and Pseudotransitivity Rules.}

a)  D ---> BC
b)  A ---> E
c)  BC ---> EC
d)  A ---> D
e)  C ---> A

c) Using the 3 functional dependencies in problem b above, compute D+, the set of all attributes functionally determined by attribute D in the above set of functional dependencies.

i) Is attribute D a superkey? Explain.)

ii) Are there any other superkeys? (If yes, provide a few of them; if no, explain)

iii) Is attribute D  a candidate key? Explain.

iv) Are there other candidate keys? (If yes, provide some; if no, then explain)

v) Is attribute D a primary key? Explain.