Project Overview

Updated 2020.03.09

1. Design tables (25% of project)

You should derive the necessary functional dependencies and the tables for all of the attributes in the tables. Perform any necessary normalization.

Provide a report that includes the following (5% each). Copy the five questions below to a Word document and write your answers below each question. Write the way a college student should write – in complete sentences, with proper spelling, grammar, and word usage. Suggestion: download Grammarly.

1.    A list of all functional dependencies before doing any normalization. Do not do redundant dependencies (e.g. ID ŕ name and name ŕ ID). Take each multivalued dependency and convert it into a single-valued dependency.

2.    A discussion of all decisions regarding keys: Why is a given field a key field? Are there any alternate key fields (another field that will uniquely identify a row)? If there was more than one choice for a key, why did you choose the one you chose? If you used a surrogate key, why did you do so?

3.    A discussion of all normalization activities: What did you do to normalize, and why did you do it? Include any intersection tables that you had to create as a result of N:M relationships. Note that your original design may already have been normalized. Note that if there is a field in a table that is determined by something other than the key (or a candidate key), then your table is NOT normalized.

4.    A justification for any violations of the normalization rules that you decided on. A common example of this is ZIP code (include this if you have a ZIP code field).

5.    A listing of all the final (normalized) table definitions. Use the format:
      TABLE (key, field2, field3, ..., foreign_key).
Underline primary keys and use italics to indicate foreign keys. Note that a field can be both a foreign key and a primary key (e.g. in an intersection table), and would be written in italics and underlined.

2. Conceptual Design: ER Models (25% of project)

Create a conceptual design for your database using ER diagrams. Be sure to include both maximum and minimum cardinalities. Explain why a relationship is 1:1, 1:N, or N:M. Explain why you chose the cardinalities that you chose. Many times this term we have said that the answer to a design decision depends on what the user says and needs. Any place where there is doubt about how to interpret something, you are the user, and must provide an answer. Your report should answer all such questions. You will be severely docked for leaving any such question unanswered in your report. ER diagrams must be drawn with MySQL Workbench.

Create and hand in hard copies of the following:

·         ER diagram with relationship lines and minimum and maximum cardinalities for all entities. Use MySQL Workbench. Correctly include the data type for each field in each entity. Correctly fill in the check box indicating if the field is a primary key (PK), and the check box indicating if the field is required (not null—NN). Don't worry about the other check boxes. Do not allow the same field to occur twice in the same table. Make sure your foreign keys are correctly identified. (15%)

·         Word document: This is where you explain why you chose the cardinalities that you chose (1:1, 1:N, N:M). List any constraints on fields. Use a word processor and double-space. Use correct spelling and grammar. Write in complete sentences. Some examples of constraints might be:

1.    ZIP code field: "Must be a 5 or 9-character string of digits".

2.    Pay rate field: "Must be >= 0" (a constraint on a pay rate field).

3.    State field: "Must be a 2-letter abbreviation". (10%)

3. Build a test database and queries (24-26% of project)

Build a test database in Access for the relational design created in the parts 1 & 2 of the project.

Ensure that there is sufficient data in the database to provide reasonable answers to test queries.

Design in SQL, and implement using Access, test queries specified by your instructor. All queries must use parameters entered by the user.

4. Build forms and reports (24-26% of project)

Design, and implement using Access, the forms specified by your instructor.

Design, and implement using Access, the reports specified by your instructor.

Hand in a report which includes the following:

1.    An example of each form and description of how the form is to be used.

2.    A printout of each report.