Homework 4
CS422, Summer 2005

Due: Thursday, August 25

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

---

[Reading]

• Chapter 4 of the textbook.
• Chapter 2 of Oracle Database Application Developer's Guide - Object-Relational Features
• Section 3.6 of the JDBC Tutorial

[Object-Oriented DB Design and Implementation]

A company wants to store the following information in a database:

• For each employee, their employee id, name, birthday, address, supervisor, and dependent(s). There are two types of employees: salaried employees and hourly employees. A salaried employee is paid a monthly salary, and an hourly employee is paid by an hourly rate.
• For each dependent of an employee, their name, birthday, and relationship with the employee.
• For each department, the department id, department name, and the employees who work for the department. Note that a department may have both salaried employees and hourly employees.

For this assignment, design and implement the database using an object-oriented approach with Oracle object and collection types. Your work will be graded in the following aspects:

• (40pt) Overall design and implementation, which include proper use of object and collection types, references, inheritance, and polymorphism.
• (20pt) Primary key, not null, default, and foreign key constraints in object tables.
• (20pt) Member functions and constructors
• (20pt) Queries
• Given an employee id, find the number of dependents of the employee.
• Given an department id, find the average salary of the employees who work for the department. Note that the salaries of the hourly employees vary from month to month, so the query result should be the average salary of current month.
  

[Client Application Development] (extra credit)

For extra credit, develop a Java application which interacts with the database. Your Java application must access database objects as objects, not as rows and columns, by using

• (40pt) SQLData interface in JDBC, or
• (60pt) hibernate

And  your application should allow a user to perform the following task:

• (15pt) List the id, name, birthday, and address of all the employees in the database.
• (15pt) Select an employee and list the information about dependent(s) of the employee.
• (15pt) Add an employee, including his or her dependent information.
• (15pt) Delete an employee, including his or her dependent information.

The due date for the extra credit work is Saturday, August 27, and you may turn it in as Homework 5.