Consider the following write-up:

At YoyoDyne, we have employees, each of whom is assigned to a single department.  Each department has a supervisor, also an employee.  We store the following information about employees:  A (unique) employee number, employee name, annual salary and the department to which the employee is assigned.  For departments, we store the name of the department and the employee number of the department’s supervisor.

Each employee is assigned to work on a number of projects, spending a certain percentage of the employee’s time on each project (to a maximum of 100%, though we don’t need to model this just yet), and each project has several employees working on it.  Projects are identified by a project number, and each project has a name.  For each employee working on a project we record the percentage of that employee’s time spent on the project.

From this write-up produce an entity-relationship diagram and an associated relational schema.

The file universal.dat on Hedwig contains further information for this exercise.

For this file, create a table and load the information from the file into the table (all into one table). Turn in the .ctl file and the SQL statement used to create the table.

Write create table statements for the entities you created in the classroom exercise. Do not use create as select, and include constraints to enforce entity and referential integrity. Turn in the SQL statements creating the tables.

Insert values from the universal table into these tables. You will need to be careful about the order in which you create the tables. Turn in the SQL statements to do this.

Finally, verify all this by selecting and printing all of the information in each table (SQL: select * from table_name). Turn in the SQL statements and the results (as with the first SQL exercise)

This will be due Wednesday, February 25. If I don’t make it to the History of Science conference on that Friday, it will be due Friday, February 27.

Working on this in the groups assigned for the first part of this exercise is preferred. In that case, each group should turn in one copy with the names of the individuals in the group. No email attachments (sorry).