You were just hired to create a database to record student club and honor society memberships at a university. Discussions with the university representatives focused on two initial entities, students and associations; the following key points were agreed:
The sample data may not represent all possible values of each field - consider the domain of values that may be used in the future as well as the below data.
Your objective is to replace the following report. Your submission will consist of a word compatible document.
| StudentID | LastName | FirstName | DOB | Assoc-Name | MaxMem | DateJoined |
| Stu001 | Smith | James | 2/23/1998 | Epsilon Pi Tau Global Connections | 50 120 | 1/15/2017 2/15/2017 |
| Stu002 | Jones | John | 5/14/1999 | Delta Mu Delta | 50 | 2/18/2017 |
| Stu003 | Rosenberg | Samuel | 3/23/1998 | Global Connections | 120 | 4/14/2018 |
| Stu004 | Jones | John | 1/25/1997 | Global Connections | 120 | 5/1/62017 |
| Stu005 | Hancock | James | 10/10/1999 | Episilon Pi Tau | 50 | 6/6/2017 |
| Stu006 | Smith | Sallry | 9/4/1997 | Entrepreneurship Club Criminology Club | 55 100 | 3/20/2017 4/29/2017 |
| Stu007 | Jones | Nancy | 2/23/1998 | Criminology Club | 100 | 6/6/2017 |
| Stu008 | Jones | Nancy | 4/15/1997 | Criminilogy Club | 100 | 4/16/2016 |
Here is the un-normalized table notation for the above report:
StudentAssoc (StudentID, LastName, FirstName, DOB, (Assoc-Name, MaxMem, DateJoined))
You must use the following outline in your submission - include the section labels.
1) Review the existing report, with the sample data (as well as field domains and common knowledge) and document any assumptions you feel are appropriate (beyond those in the key points).
2 a) Submit "one" complete functional dependency analysis (all fields must be included at least once), use the functional notation shown on pages 410-413 Examples 14.2, 14.3, 14.4 and 14.5 to document the functional dependencies. Do not submit functional dependencies for each normal form - only one set of functional dependencies is needed. There may be one or more rows of functional dependencies.
Functional dependency notation example: Field A -> Field B, Field C, means that Field A is a determinant for Field B and Field C.
Similarly if a field is dependent on the combination of values of two fields then:
Field X, Field Y -> Field Z
2 b) Define functional dependency, and explain one row of the submitted functional dependencies in plain English (use field names and values in this exercise).
3) Explain the relationship between the initial entities in plain English - (either one-to-many, or many-to-many).
4) All fields should be included in at least once in a table at each normal form level
a) Submit a complete set of 1NF table(s), you must use table notation (see the above UNF example - also refer to the relational schema under Figure 4.2.6 on page 111 of the Connolly text), include the first normal form definition, and a plain English explanation of why the table(s) is (are) in 1NF - copy the definition in the Connolly text (or the Terms and Concepts forum)
b) Submit a complete set of 2NF table(s), you must use table notation, include the second normal form definition, and a plain English explanation of why the table(s) is (are) in 2NF - copy the definition in the Connolly text (or the Terms and Concepts forum)
c) Submit a complete set of 3NF table(s), you must use table notation, include the third normal form definition, and a plain English explanation of why the table(s) is (are) in 3NF - copy the definition in the Connolly text (or the Terms and Concepts forum)