LSP 121
Homework – Normalization, Queries
Question #1: Normalize each of the following datasets (remove the repeating fields and place into a separate table with a foreign key). Rewrite the field names under an appropriate name for the table. Circle the primary key, and put the initials FK (for foreign key) next to each foreign key. This question does NOT have to be performed in Access.
A. Personnel Database
Employee ID
First Name
Last Name
Address
City
State
Zip
Annual Income
Insurance Provider Name
Following fields repeat 1-n times
Phone Number
Type of Phone (home, cell, work, etc.)
B. Daycare Database
Parent ID
Parent Last Name
Parent First Name
Parent Address
Parent City
Parent State
Parent Zip
Following fields repeat 1-n times
Phone Number
Type of Phone
Following fields repeat 1-n times
First Name
Age
Allergies
C. CD Database
CD ID
Title of CD
Artist
Year recorded
Genre (rock, pop, hiphop, etc.)
Year purchased
Following fields repeat 1-n times
Track title
Track length
Queries
Open the database student_records.mdb from the class webpage.
Show the ID, last name, first name of students who began receiving financial aid after Jan 1, 2007. Note that you may initially see multiple records for each student. Try using the ‘Group By’ function and choosing Student ID and see what happens. (Remember that you need to click on ‘Totals’ to see ‘Group By’).
Show the average GPA of all students living in Detroit.
Of all of the courses recorded in the Grades table, how many A’s were awarded? Note: You will need to display the ‘Grade’ field twice in your query. Once to search for ‘A’ and the other to display the count.
Display the Student ID, Course Name, Course Number, Course Grade of all students who received an A in Math (MAT). You will need to include a ‘Group By’ under Course Number. Note that this is an “AND” query…
Using the ‘NOT’ operator (you may need to look this up – but it’s not difficult), list the student id, major, and phone numbers for all students that do NOT major in history.
FORMS:
Using the Pets database, do the following to the table ‘Pet’:
· Add a field called ‘Vaccinated’ of data type yes/no.
· Return to the datasheet view and randomly enter either yes or no for the pets currently listed in the table.
· Create a form to allow entry/modification of the table. Most entry controls should be text-fields, with the following exceptions:
o For type of animal, use a combo box
o For Vaccinated, use a checkbox
To display, make the output as large as possible in your window, then do a print-screen and paste it into your Word document. If you have Windows 7, you can use the extremely handy ‘Snipping Tool’ (Start à Programs à Accessories à Snipping Tool) which lets you select a specific part of your window. I’m pretty sure Macs have a similar app built in.
REPORTS:
Experiment! Create two different reports one from the Authors database (see todays activity) and another from any of the databases we’ve used. In each one, demonstrate the use of some final statistic (mean, sum, max, etc) at the bottom. Remember that these summaries must be Report footers (not Page footers).
As above, paste the output (showing your report totals at the bottom) into your Word document.