Database case study examples with solutions

You were just hired to create a database to track student club and honor society membership at a university.

The following key points have been agreed:
1 Each StudentID will be unique
2 Each Assoc-ID will be unique
3 Each student may belong to zero, one, or many associations, and each association may have zero, one, or many student members.
4 No fields beyond those in the spreadsheet are needed (including additional fields will result in point deductions)

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.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 spreadsheet, and the sample data and document any assumptions or constraints you feel are appropriate (beyond those in the key points). (10%)

2) 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 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