sql assignment 4 online

Individual Assignment 4

Part A:  Answer the following questions based on the textbook – 5 points each.

1.       What are the four categories of update anomalies and why could these problems occur when a relation is in first normal form but not in second normal form?   

2.       Give an example of a column A and a column B such that B is functionally dependent on A.  Give an example of a column C and a column D such that D is not functionally dependent on C.

Part B:  Answer Question #2 from the Premiere Products Exercises on pg. 180 in the textbook.  (Note: two-part answer – 5 points each)

Part C:  Answer Question #1 from the Henry Books Case on pg. 181 in the textbook.  (Note: four-part answer – 10 points total)

Part D: Normalization – 45 points

Data File needed for this Problem:

·         Student.xlsx

Data Files to Upload to Moodle:

·         Students.accdb

Your client wants to build a database for a school that contains the contact information for students as well as the courses they have taken and the grades they have received. They provided the attached Excel spreadsheet to show you how they are currently storing this information.

Upon studying the file, you see that it is not properly normalized. For this assignment, you will need to create a database with normalized tables, similar to the classroom discussion.

Your new database should include the following elements:

a) Main table for student information. The data provided in the Excel spreadsheet should be entered into the tables in proper format. Remember that it may be necessary to breakdown the Excel name or address fields into multiple columns in your new Access table.

b) Phone Type Table. It should contain, at minimum, the types of phones listed in the Excel spreadsheet.

c) Student Phone Table. It should contain the student ID, phone type ID and the student’s phone number.

d) Course Table. This table should contain the Course # and Description for each course offered at the school.

e) Student Transcript. This table should contain information linking the student with a course and the grade received.

For each table, ensure you have the following in place to receive full credit:

·         Correct primary keys. Some tables will require 2 fields for a primary key.

·         Appropriate and meaningful names for the table and the fields.

·         Database relationships with enforced referential integrity and Cascade Updates turned on

Be sure to enter all data into the database before submitting your assignment!

Part E:  Normalization Modifications – 25 points

Data File needed for this Problem:

·         Contacts.accdb

·         ContactAddress.xlsx

Data Files to Upload to Moodle:

·         Contacts.accdb

We are using the database created in example 4 during class. I have provided a copy of this database for you, Contacts.accdb. In this case, the client has decided to add address tracking for each client, so you will need to modify the database to capture this data.

Upon studying the PDF file, you see that each contact can have multiple addresses. For this assignment, you will need to add two tables to the database.

Your new tables should include the following elements:

a) Address Type Table. It should contain, at minimum, the types of Addresses listed in the PDF file.

b) Table that relates the Contact with a given address. The data provided in the PDF should be entered into the tables in proper format. Remember that it may be necessary to breakdown the name or fields into multiple columns in your new Access tables. Also, you should track whether this is a mailing address or not.

For each table, ensure you have the following in place to receive full credit:

·         Correct primary keys. Some tables will require 2 fields for a primary key.

·         Appropriate and meaningful names for the table and the fields.

·         Database relationships with enforced referential integrity and Cascade Updates turned on

Be sure to enter all data into the database before submitting your assignment!