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. Primary key fields must be underlined.

Example:

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. 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.

(1) 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.