Computer Science 455

Exercise Set #4

Due: Thursday, March 14, in class

YoyoDyne, Inc. maintains information about employees (eno, ename, salary).  Each employee belongs to one department (dno, dname, supervisor) and works on a variety of projects, devoting a percentage of the employee's time on each.
  1. From this description, construct an entity-relationship diagram. You may need to alter this diagram by the time the other parts of the problem are completed. What you submit should be the final copy.
  2. From the E-R diagram construct a relational schema. Identify keys and foreign keys. Describe which fields are appropriate for indexes. For those fields appropriate for indexes you choose not to index, say why (assume a reasonably large catalog and customer base).  Turn in a typed schema with the above identified for each table.
  3. From the relational schema, construct CREATE TABLE commands with only primary  key constraints.  Turn in the SQL file with the CREATE TABLE commands.
  4. The file universal.dat in the handouts folder on Plato contains  a "universal relation" with all the fields (you can use this to determine data types and lengths for the previous step). Write a CREATE TABLE statement for this table and use the database loader to load it with data from universal.dat.  You may want to create a second table using a CREATE TABLE AS SELECT to remove a number of duplicate records which have appeared in universal.dat.  Turn in the results of a SELECT statement for this universal table.
  5. Using INSERT ... AS SELECT, copy information from the universal table into the tables you created in step 3.  Turn in the INSERT ... AS SELECT statements.
  6. Create a view that recaptures part of the universal relation containing department information (but include the name of the employee supervising the department in addition to the employee number of the departmental supervisor) and the names, employee numbers, and salaries of the employees in the department.  Turn in the view definition and a select * query for the view.
  7. Create a report from the view defined above.  For each department, include the number of employees in that department with company-wide totals (it is, currently, a very small department).  Turn in the report.
  8. Add yourself as an employee and verify that the insertion worked.
  9. Give everyone in your department a 10% raise.
  10. Delete yourself from the database.  Verify that each of these steps worked.  The easiest way to do this for steps 8 - 10 is to create a SQL file with these commands (prefaced with a 'set echo on' and capture the output to a file.  Turn in the captured file.

Any questions? Please let me know. Many thanks!