CS 4250: Project Part 4


Hardcopy/email due at midnight (email only) or 2pm (paper or email) on Thursday, April 7, 2016. (Email: subject line of "cs4250, project part 4". PDF, plain text or MS Word only.)

Section 1: Initial Relations
(0 pts) List all the relations in this schema. (An up-to-date copy of your "create table" statements would be fine. These tables should have been corrected based on feedback from Part 3, if necessary.)

This list is of the tables as they exist after problems with Part 3 have been fixed, and before the rest of Part 4 begins. If you alter your tables while working on Section 3 of this assignment, the alterations will be listed as part of Section 3 of Project Part 4; those changes will NOT affect this initial list.

Section 2: Functional Dependencies
List ALL reasonable, non-trivial functional dependencies (FDs) that apply to your design.

For readability: List all FDs with only one attribute on the right hand side. List all the FDs vertically, one FD per line. To the right of each FD, list the relations/tables it involves, in alphabetical order.

Also, if particular sets of FDs apply over particular relations in your schema, list the FDs that apply to relations in the same order you listed your 'create table' statements for the first part of this assignment. That way, a reader can look at the list of relations in your schema (#1), and the list of FDs (#2), scanning down both lists simultaneously.

If you use the single letter per field convention in our textbook, be clear about which letter corresponds to which field in which relation at all times.

Section 3: Normalization
Are all the relations in your chosen schema in 3NF? Are they in BCNF? Explain both answers. Do not make a blanket statement about all relations; make a neat list of every single one of your relation names and, for each one, which normal form it is (or is not) in.

If any of your relations are not in BCNF, normalise them to BCNF. If you choose to normalise your relations only until they are in 3NF, explain your reasons (e.g., the amount of redundancy introduced is limited or some other valid reason). Notice that if you decide to go ahead with normalization, you will have to list the violating FDs for each of your relations and explain why you think they violate 3NF and/or BCNF.

List any changed / new relations in Section 3 of this assignment. Do not edit the initial list of relations in Section 1. (The grader needs to be able to do "before and after" comparisons.)

Most of the assignment points will be given for your clear, well-reasoned explanations, demonstrating clear understanding of what FDs and normal forms are and how your FDs and relations interact with the normal forms.

Required but not graded: Include one sentence per group member summarizing each group member's contribution to Project Part 4. These sentences are not for part of any student grades. They will be used to monitor group dynamics, and to try to intercede in troubled groups (if any) before troubles get out of hand.


Question: The second part seems to imply that we can "get away without doing normalization". Is that true? Why do you have the clause "if you decide to go ahead with normalization"?

Answer: There is no escaping from normalization! We haven't spent so much time on it without requiring you to do it for your project. First realize that you have to bring your relations into 3NF, come what may.

There are some exceptional cases where normalization to BCNF can be skipped and you can actually leave the relation in 3NF. However, such cases are far and few in between. You must have a truly sound reason for not doing BCNF normalization.

Question: Is zip_code → state a valid functional dependency?

Answer: For zip code to imply state, it must be the case that every zip code's geographic area fits in one and only one state. This is not the case. Nationwide, there are a few zip codes that span two states, so that functional dependency is not valid. (Look here for more trivia about zip codes.) 97635 → Oregon and 97635 → California are both true (Lake, OR and Modoc, CA).

Last modified: 3/17/2016