Database Normalization - DePaul University



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.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download