CPSC 343 Database Theory and Practice Fall 2017

Homework 4: Access and Integrity

due Wed Nov 1 in class

For this homework you will be working with the following database schema:

  DEPARTMENT(deptname,deptnum,mgrssn,mgrstartdate)
  DEPENDENT(empssn,fname,sex,bdate,relationship)
  DEPT_LOCATIONS(deptnum,deptloc)
  EMPLOYEE(fname,minit,lname,ssn,bdate,address,sex,salary,superssn,deptnum)
  PROJECT(projname,projnum,projloc,deptnum)
  WORKS_ON(empssn,projnum,hours)
  HISTORY(ssn,salary,timestamp)

Key attributes are underlined. The foreign key constraints are the following:

If you want to try out your solutions on an actual database, you can use user_company (where user is your username).

Views

For each of the following, give the CREATE VIEW statement to create the view described. It is OK to define the view using MySQL Workbench and then copy the CREATE VIEW statement that it generates when you click "Apply".

  1. The employee address book: first name, middle initial, last name, address, and the name of the department they work for.

  2. Employees with children: first name, middle initial, last name, SSN for those employees with at least one dependent whose relationship is "Son" or "Daughter".

  3. Managers: first name, middle initial, last name, SSN, birthdate, address, sex, salary, department name, and department number for those employees who manage a department.

  4. Department statistics: for each department, the number of employees, the highest and lowest salaries of the people who work in that department, the number of different projects associated with that department, and the total number of hours employees work on projects associated with that department.

Stored Routines

For each of the following, write a stored procedure or function as appropriate. Give the CREATE PROCEDURE or CREATE FUNCTION statement to create the routine described. It is OK to define the routine using MySQL Workbench and then copy the CREATE PROCEDURE/FUNCTION statement that it generates when you click "Apply".

Assume that the views you defined above are available - make use of them as appropriate.

  1. Given a project number, get the total number of hours employees work on that project.

  2. Given a salary, find the lowest-paid manager (last name, SSN, salary) whose salary exceeds that threshold. Report NULL if there is no such manager, and any one if there's more than one such manager.

  3. Given an employee's name (first and last), look up their SSN. Signal an error if there is either no such employee or more than one.

  4. Given an employee's SSN, the project number, and a number of hours, update the time the employee spends working on the project. Add a new entry to WORKS_ON if needed.

  5. Given a department name and number, the manager's SSN and start date, and the department's location, add that department.

  6. Given a department number and location, remove that location and set the location for any projects associated with that department and location to NULL. Signal an error if the specified department does not exist in that location.

Queries and Statements

Write queries/statements for each of the following. Assume that the views and stored routines you defined above are available - make use of them as appropriate. (Note that you will not get full credit if you do not use a view or stored routine that would simplify the query.)

  1. Find the total number of hours employees work on project 2.

  2. Find the projects (project number and project name) where the total number of hours employees work on the project exceeds 40.

  3. Find the last name, SSN, and salary of the lowest-paid manager whose salary is above $50,000.

  4. Find the pay gap between the highest-paid and the lowest-paid employee in each department.

  5. Find the number of female managers.

  6. Find the number of dependents that each manager has.

  7. Set the number of hours James Borg spends working on project 20 to 30 hours.

Triggers

For each of the following, identify how to implement the specified constraint in the database: via column-based settings (by choosing the domain or, default value, or by setting primary key, NOT NULL, or UNIQUE tags), foreign keys, or triggers. Be specific; don't just say "domain", for example, but indicate which column and what particular domain. For triggers, give the full CREATE TRIGGER statement to accomplish the task. (It is OK to define the trigger using MySQL Workbench and the copy the CREATE TRIGGER statement that is generates when you click "Apply".)

Modifications that violate the constraint should be disallowed unless otherwise specified. If there is more than one way to enforce a constraint, choose the best or most appropriate solution. (Only use triggers if there isn't another alternative.)

  1. A department must have a manager.

  2. There cannot be two projects with the same name in one department.

  3. The location for a project must be one of the locations associated with the project's department.

  4. Employees can only work on projects belonging to the department they work for.

  5. Hours worked on a project can only be reported to the tenth of an hour.

  6. Employees cannot work more than 40 hours total on their projects.

Write triggers for the following:

  1. Managers do not have supervisors and must also work for the department they manage. If a person becomes a manager, their supervisor should be set to NULL and their deptnum set to the department they manage.

  2. When an employee's salary is changed (or the employee leaves the company and is deleted from EMPLOYEE), the previous salary and a timestamp indicating when the change was made should be recorded in the HISTORY table. (You may find it useful to look up date and time functions in MySQL.)


Valid HTML 4.01!