Database and ER Diagram:

Directions: For each of the below situations, design a database to hold the necessary information using the principles we learned in class. Specifically, present your solutions by drawing an entity-relationship (ER) diagram and writing a database design outline, as illustrated in class. In your solutions, each table must (of course) have a primary key; you may add a synthetic key (i.e. an “ID” field) if necessary. In each question below, assume that address information is specified in standard US format, with a Street address, city, state and zip code. Also assume that you do not have access to a master zip-code table, so you may treat each of these as independent.

Question: Rutgers parking After getting substantial complaints about its parking procedures (i.e. tickets), Rutgers contracts your firm to manage its parking lots. Each lot its own code (“lot 23A” for example), number of spaces, and description (example: “behind College Ave. gym”). Each student, faculty, and staff has regular permission to park in exactly one lot. For each affiliate, you want to store a first name, middle name/initial, last name, home address information, date enrolled/hired, home phone number, cell phone number, and e-mail address. Each affiliate 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.