Refer to the assignment 3 for definitions of what is meant by a database design outline and an entity-relationship diagram.  In all three questions, assume you not have access to a zip code table.  
 

Q1.  Jersey Harbor Patrol:  You have been assigned the task of automating the records of the Jersey Harbor Patrol Agency.  This agency sends patrol boats throughout the Jersey Harbor area, checking for safety and security problems.  The agency owns a fleet of boats; for each boat, you want to store its (unique) marine registration number, name, date built, and date acquired.  The boats are operated by employees, for each of whom you want to store an employee number, first name, last name, date of birth, date hired, cell phone number, home phone number, home street address, city, state, and zip code.  

The employees use the boats to visit various sites around the harbor.  Each site has a "site number", name, and description.  Each time a boat leaves the agency's home dock is called a "patrol".  Each patrol involves two employees, a "captain" and an "assistant".  The captain is responsible for all decisions on the patrol, and the assistant must follow his orders.  However, an employee who is the captain of one patrol might be the assistant on a different patrol.  For each patrol, you want to record which boat was involved, who the captain and assistant were, what date and time the patrol started, and what date and time it ended.  Each patrol involves visits to one or more sites; generally, the agency tries to visit each site at least once every few days.  You want to record the set of sites visited by each patrol, and the exact date and time of each visit.

Design a database to store this information.  Draw an entity-relationship diagram and write a database design outline.  You may add "ID" fields where necessary.
 

Q2.  Query Exercises:  From the class website entries for November 5, download the file conference.mdb, which describes the sessions and speakers at a conference:

1. From the information in the relationships window, draw an entity-relationship diagram for this database, showing a "many-to-many" relationship.
   
   For each of the following, create a query, hand in a printed "screen shot" of the query design window, as well as a printout of the query results.   To obtain a screen shot, push the "Print Screen" key on the keyboard (usually located near the F12 key on desktop computers).  Pressing the "Print Screen" key places a screen shot in the clipboard.  You may then paste it into the "paint" utility under Programs→Accessories or All Programs→Accessories, and crop off areas outside the query design window.
    
2. A "master schedule" table for the conference.  For each talk at the conference, this query should list the date, session start time, session title, room ID, room capacity, speaker first name, and speaker last name.  It should be sorted by date, then by session start time, then by session name, and finally by speaker last name.
    
3. The ID, title, date, and start time of each session, along with the number of speakers scheduled for the session (hint: do a join and use "count" aggregation).  Sort the results from the largest number of speakers to the smallest.
    
4. The same information as part c, but instead sorted by session date and then by session time.  Do not show sessions with only one speaker.
    
5. The same information and sort order as part c, but instead of counting the number of speakers, count the number of speakers whose expertise is not "Student Life".  Sessions with no such speakers need not appear.  There should be only one line of output for each session that does not have such speakers.
    
6. The room ID, room capacity, date, starting time, and session title for each session being held in a room whose capacity is greater than 50 people.  Sort by room number, then by date, and then by starting time.  For each room with capacity exceeding 50 that is not being used for any sessions, include a line showing the room number and capacity, with the other information blank.
    

Q3.  Normalizing Temporary Employment Records:   The following table keeps track of temporary worker assignments at a firm, one row per assignment:

Note that the rate per hour is negotiated separately for each assignment.

Design a third-normal-form database storing the same information  Draw an entity-relationship diagram and write a database design outline.  Note: this problem is not as complicated as the normalization problem in assignment 6.