For each of the following situations, design a database to hold the specified information drawing an entity-relationship (ER) diagram and writing a database design outline for the database required to hold the information described in the problem. A database design outline is a description of a database using the following notation (shown here for an example we used in class). Primary key fields must be underlined and foreign keys annotated with "attribute foreign key to TABLE".

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 synthetic-key "ID" fields to tables whenever necessary.

An entity-relationship (ER) diagram means a graphical depiction of the database structure as given in class. For the example above, the diagram would be as follows:

In each question below, assume that any "address information" specified 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 (this issue will be discussed in a later class). Note that any text in the diamonds (such as "takes out" and "made on" above) is essentially a comment relating the database structure to the business situation, so exactly what you write in the diamonds is not critical.

When the problems specify that you should store a "date/time", assume that you will store dates and times together in MS-Access-style "date/time" fields.

Q1. 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 (EDIT: not every parking lot has to be assigned to an employee) . For each employee, you want to store a first name, middle name/initial, last name, home address information, date hired, office phone number, cell phone number, and e-mail address. Each employee may own one or more vehicles, and has permission to park any of these vehicles in his or her designated lot. For each vehicle, you want to store the license plate number, the state in which vehicle is registered (using standard two-letter codes), the make, model, year, and color.