Project 1. CompanyX
CS422, Summer 2004

Thursday, August 26

Please upload your files using the online turnin server. Note that file uploading will be disabled automatically after 11:59PM of the due date, so please make sure to turn in your solutions in time.

---

[Problem Description] In this project we will implement a database for a hypothetical CompanyX. In particular, the database will keep track the following information:

For each employee, their name, SSN, birthday, gender, salary,  address,  supervisor, dependent(s). We assume that

• SSN is unique.
• A name consists of first name, last name, and middle name initial.
• An address consists of street number, apartment number, city, state, and zip code.
• An employee has at most one supervisor, e.g. some employees may be supervised by no one; and a supervisor is an employee.
• An employee may have multiple dependents. The employee themself does not count as a dependent.
  

For each dependent, their name, SSN, birthday, gender, and their relationship with the employee.

For each department, its name, city, manager, employee(s), and project(s). We assume that

• Department name and city together is unique.
• Each department has exactly one manager.
• Each employee works for only one department.
• Each department may have multiple projects.

For each project, their name, id, the department to which it belongs,  the employees working on the projects, and the number of hours per week each employee spends on this project. We assume

• The id of each project is unique.
• A project belongs to exactly one department.

[Relational Design] Apply the relational design principles and come up with a set of relations in 3NF, BCNF, or 4NF as appropriate. Write SQL statements to implement your design in a DBMS, and populate the tables with some synthetic data such that each table has at least two tuples. Evaluation for this part of the project will be based on the database design, and proper use of schema elements such as data types, constraints, indexes, and views.

[Inheritance] Modify your database implementation to support different types of employees, namely, salaried employees, commissioned employees, and hourly employees.

• A salaried employee is paid an annual salaray, spreaded over 12 months. So for example, if the annual salary is $50,000, the monthly salary is $4166.67.
• A commissioned employee is paid a base monthly salary, plus commissions calculated as the commission rate times the total sales made during that month.
• A hourly employee is paid by a hourly rate, and the monthly salary is calculated as 180 * hourly_rate + 1.5 * hourly_rate * overtime_hours.

[Triggers and Stored Procedures] Add the following triggers and stored procedures to your database:

• salaries() - a function returns a list of employee names and their salaries for the previous month. Note that to calculate the monthly salary of a commissioned employee, you need an addtional relation to keep track the sales; and to calculate the monthly salary of a hourly employee, you need to know the number of overtime hours. For simplicity, we will calculate the overtime hours per month as (hours_per_week_working_on_projects - 40) * 4.5.
• commuting_employees() - a function returns the percentage of the employees who commute, e.g. the percentage of the employees who live in a different city from the city where their depepartment is located.
  
• num_dependents( employee ) - a function returns the number of dependents of the employee.
  
• no_slave_labor - a trigger to ensure that no employee work more than 60 hours a week.
• abandon_project - a trigger to delete a project if all employees previouly working on it are removed from the project.
  

[Java]  Write a Java GUI client to the database such that a person who does not know SQL could perform the following tasks:

• Add / remove an employee and the dependents of the employee
  
• Add / remove an project and the employees working on the project
  

(a) Use JDBC for the connection between the the GUI client and the database.
(b) Use hibernate for the database connection and design.

[Grading and Extra Credit Work]

• Relational design (50pt)
  
• Inheritance (20pt)
• Functions and triggers (50pt)
  
• Java (extra credit)
  
• GUI and JDBC (+40pt)
  
• Hibernate (+40pt)