Student Record Club Honor Society Memberships You were just hired to create a database to record student club and honor society memberships at a university

Student Record Club Honor Society Memberships You were just hired to create a database to record student club and honor society memberships at a university.

The following key points have been agreed:
1 Each student is given a unique StudentID
2 Each Assoc-Name is unique
3 DOB is date of birth
4 MaxMem is the maximum approved membership for an association
5 The DataJoined field records the date a student became a member of the club or honor society
6 Each student may belong to zero, one, or many associations, and each association may have zero, one, or many student members.
7 No fields beyond those in the spreadsheet are needed.

The objective of this exercise is to demonstrate an understanding of some basic concepts covered in the course. The exercise is a straight forward application of those concepts – there are no “hidden” complexities – should you identify something in the key points or data that adds complexity, contact the instructor before submission – you may be over thinking the exercise.

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 with a relational database. Your submission will consist of a word compatible document, and an Access DB.

Here is the un-normalized table notation for the above report:
StudentAssoc (StudentID, LastName, FirstName, Major, (Assoc-Name, AssocContact#, MaxMem))

The specific tasks are listed below, the percentage in parenthesis corresponds to the grade weight for each task: Organize your document to match the tasks below, label each section to match the task.
Name your document Last Name_CaseStudy (i.e. Smith_CaseStudy). When you are asked to provide an explanation or description, you must include sufficient content to demonstrate that you understand the definition, term, concept, etc. and how it applies to this exercise.

SUGGESTION: Review the Terms and Concepts Forum, especially the Many-to-Many and One-To-Many examples. There is also a normalization MP4 file that can be downloaded and viewed in the Resources section.

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). (10%)

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. (10%)

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). (10%)

3) Explain the relationship between the given entities (tables) in plain English – (either one-to-many, or many-to-many). (10%)4)
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) (10%)
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) (10%)
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) (10%)

5) Create an MS Access database, including the data provided, use only the 3NF tables submitted above. Name your database Last Name_CaseStudy (i.e. Smith_CaseStudy). Upload the database to the assignment area as one of the deliverables (5%).
6) Create a Query (In MS Access) that may include one or more tables, as needed, that provides the results for the following request: List each Student_ID, FirstName, LastName, and Assoc-Name for all students. (5%)
7) Create one Form: New Student Input – this form should include all relevant fields needed (5%)
8) Create One Report: Association List – list all Associations – this report should include all relevant fields (5%)
9) Submission content organization, clarity, spelling and grammar (10%)