Refer to the assignment 3 for definitions of what is meant by a database design outline and an entity-relationship diagram.    
 

Q1.  Normalization with a Repeating Group:  suppose that a car wash keeps records in the following form, using only one table.

The fields AddOnCode, AddOnDescrip, and AddOnCharge form a "repeating group" indicating which optional services were added to the basic wash operation. The interpretation of the data above is:

• On March 5, Jane Wright washed her Honda Accord, with no optional services (so AddOnCode, AddOnDescrip, and AddOnCharge are blank).
• On March 8, Janet Wu washed her Toyota Camry, with two optional services: hot wax and undercoat.
• On March 12, Feliz Ortiz washed his Nissan Altima, with no optional services.
• On March 15, Janet Wu returned in a different car, this time a Honda Civic.  She washed it with one optional service: hot wax. 
• On March 15, Felix Ortiz returned with a different car, this time a Pontiac GTO.  This time, he got three optional services: foam polish, hand wax, and tire seal.
• On March 16, Bill Shor washed his Dodge Magnum, with no optional services.
• On March 31, Jane Wright returned in the same Honda Accord.  This time she washed it with one optional service: tire seal.

You may assume that the car wash always charges the same amount for each optional service, no matter the date of service, the kind of car, or the customer involved.  Redesign this database so it is in third normal form.  Draw an entity-relationship diagram and write a database design outline (as defined in assignment 3).  You may add "ID" fields where necessary.

 

Q2.  More Query Exercises:  From the class website, download the file sports-with-games.mdb, similar to the database used in class on March 27.  For each of the following, create a query, hand in a printed screen shot of the query design window, and hand in a printout of the query results (see assignment 7 for screen-shot instructions; when available, refer to the assignment 7 solution for examples of what the output should look like).

1. Produce a table of all coaches, showing (in order) first name, last name, the nickname of their team, status, and phone number.  Sort the table alphabetically by team nickname; within each team, list status "2" coaches first, and then status "1" coaches.
    
2. Produce a table of all teams, showing (in order) their nicknames and the total number of coaches they have.  Sort the table alphabetically by nickname.  Teams with no coaches should still appear in the list, showing zero for the number of coaches (hint: you will have to manipulate join properties).
    
3. Produce a table showing (in order) the first name, last name, and team nickname for all "A"-level players who have a status "2" coach.  Sort alphabetically by player last name.
    
4. Produce a table of all games showing (in order) the date played, home team nickname, home team score, visiting team nickname, and visiting team score.  Sort the table from earliest to latest date played, and within the same date played, sort alphabetically by home team nickname.
    

Q3.  Query Chaining:   For the same database as Q2, create a set of interconnected queries whose final output is a table with a row for each team and four columns showing, respectively:

1. The team name
2. The team colors
3. The number of home games the team won
4. The number of away games the team won.

Sort the results alphabetically by team nickname.  Hand in the final resulting table, and a screen shot of the design window for each of the queries you interconnected.  Hint: one possible approach is to make a query computing the number of home games won for each team, and another query computing the number of away games won for each team; finally, you can make a third query combining information from the first two queries, and possibly the teams table as well.