CS 4250: Project Part 4


Hardcopy/email due at midnight (email only) or 3 pm (paper or email) on November 8, 2019. (formerly on November 1, 2019) (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.

Also, 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) simultaneously, easily scanning down both lists side-by-side. (Ordering your FDs this way prevents errors, by making it easier to double-check that you have not forgotten to consider one of your relations.)

If you use the single letter per field convention, 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: What is the most common cause of errors / points lost on this assignment?

Answer: Hands down, no competition, neglecting to double-check that all relations in the database have been considered, and appropriate FDs listed for each and every relation. (If a relation has no FDs that hold over it, I recommend writing "relation_name has no FDs" in the list of FDs, just to make it easier to triple-check that every single relation has been thoughtfully considered.)

Similarly, students lose points every semester by forgetting to list the normal form of every relation in their database. (No exceptions permitted. Every single relation has a normal form and that normal form must be identified in the documentation your group turns in.)

Question: Can we remove data from the database to make what remains normalized?

Answer: No. Absolutely not. If the data is in and approved by your customer (the professor) as part of Parts 2 and 3 of your project, it must stay. Removing data from the database means your group will lose a minimum of five points from the assignment grade.

Question: The third 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 at least 3NF, without exception.

There are some exceptional cases where normalization to BCNF can be skipped and you can 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. 97635 → Oregon and 97635 → California are both true (Lake, OR and Modoc, CA).

Last modified: 10/14/2019