Questions 1 and 3 require you to develop a crow's foot ERD. Develop these ERDs using either Oracle Data Modeler or ER assistant. Submit the competed ERDs as part of your exam solution. Submit a separate file containing each ERD. Question 2 requires you to convert the ERD developed for problem number one into the DDL required to create the Oracle database from the ERD. Submit the DDL as a SQL script to be graded as a separate file. This SQL script MUST be a text file that is error free when executed against the Oracle database. That being said you should test the script by running it in SQL Developer making sure that it is error free. The remainder of the exam questions should be submitted as a word document.

Exam Deliverables:

1. ERD for question 1.

2. SQL script for question 2.

3. ERD for question 3

4. Word document containing answers for questions 4 thru 12.

All exam deliverables must be submitted to the midterm assignments folder on or before the due date.

1. You are the lead data architect for DataSoft Inc. You have been assigned to a project where you are to design a database to assist physical plant personnel in managing assignments of keys to employees. The primary purpose of the database is to ensure proper accounting for all keys. Develop a crow's foot ERD using Oracle Data Modeler or ER assistant that captures the following requirements:

· An employee has a unique employee number, a name, a position, and an optional office number.

· A building has a unique building number, a name, and a location within the campus.

· A room has a room number, a size (physical dimension), a capacity, a number of entrances and a description of equipment in the room. Because each room is located in exactly one building, the identification of a room depends on the identification of a building.

· Key types (also known as master keys) are designed to open one or more rooms. A room may have one or more key types that open it. A key type has a unique key number, a date designed, and the employee authorizing the key type. A key type must be authorized before it is created.

· A copy of a key type is known as a key. Keys are assigned to employees. Each key is assigned to exactly one employee, but an employee can hold multiple keys. The key type number plus a copy number uniquely identify a key. The date the copy was made should be recorded in the database.

· The physical plant needs to know not only the current holder of the key but the past holders of a key. For past key holders, the date range that a key was held should be recorded.

· The physical plant needs to know the current status of each key: in use by an employee, in storage, or reported lost. If lost, the date reported lost should be stored.

2. Write the DDL for the ERD you developed in problem 1 above.

3. Develop a crow's foot ERD that captures the following business rules.

All persons have an SSN, a name, and phone number. A person can be either a buyer or an owner. An owner has a spouse name, profession and spouse profession as attributes. A buyer has an address as attributes.