For each of the situations below, draw an ER diagram and a database design outline. A database design outline is a description of a database using the following notation (shown here for the example we used in class on September 24, and also found in Chapter 5 of the G&B book).  Primary key fields must be underlined.

CUSTOMER(CustomerID, FirstName, LastName, StreetAddress, City, State,
                      Zip, Phone)

LOAN(LoanID, Date, Loan Amount, Rate, Term, Type, CustID)
        CustID foreign key to CUSTOMER

PAYMENT(LoanID, PaymentNumber, Date, PaymentAmount)
        LoanID foreign key to LOAN

In your answers, all tables must have a primary key; you may add "ID" fields to tables whenever you need to. 

An entity-relationship diagram means a graphical description as given in class on February 6 and 9.  For the loans example above, the diagram would be as below. 

In each question below, assume that any address information is in U.S. format, consisting of fields for street address, city, state code, and zip code.  Assume that you do not have the master zip code table in your databases, so that city, state, and zip code may be treated as independent.

 
Q1.    One-person consulting business:  You are a freelance consultant, working alone, and want to keep detailed records of the time you spend working for your clients.  For each client, you want to store an ID number, name, address information, phone number, and e-mail.  Each client may have one or more jobs, each of which has a unique job ticket number, a description, an agreed hourly rate, and a yes/no field indicating whether the job has been completed.  You work on a particular job in one or more "work sessions": for each work session, you want to store the date/time you started working, the date/time you stopped working, and a brief description of what you accomplished during that session.  Some jobs may be completed in a single session, but others may require a large number of sessions spread out over a number of months.  Design a database to hold this information.  Hand in a database design outline and an ER diagram. 
 

Q2.    Employee parking permissions:  Your firm maintains gated parking lots, each with its own code ("lot 23A" for example), number of spaces, and description (example: "behind main manufacturing facility").  Each employee has regular permission to park in exactly one lot.  For each employee, you want to store a first name, middle name/initial, last name, date hired, office phone number, cell phone number, and e-mail address.  Each employee may own more than one car, and has permission to park any of these cars in his or her designated lot.  For each car, you want to store the license plate number, the state in which car is registered (using standard two-letter codes), the make, model, year, and color.  Design a database to hold this information.  Hand in a database design outline and an ER diagram.
 

Q3.    Restaurant review website:  You operate a community website where registered users may post rating and reviews of area restaurants.  Each registered user has a unique screen name, along with a first name, last name, address information, e-mail address, and phone number; however, your public website displays only the screen name.  For each restaurant, you want to store a name, address information, phone number, website URL, category ("steakhouse", "Chinese", etc.).  Each review consists of a single registered user's opinion of a single restaurant, and consists of a food quality rating on a scale of 1 to 5, dinner cost paid per person, and comments (up to 255 characters text, such as "the crab cakes are excellent").  Each registered user may review many restaurants, but is only allowed to have one review per restaurant.  Design a database to hold this information.  Hand in a database design outline and an ER diagram.