(1)Suppose that you find a job in the Human Resources Department of The DreamCompany.

You are responsible for building a database to store various information about the employees,
departments, and so on. In this part of the project, you will complete a conceptual design using
ER/EER data model for a much simplifed HR database based on the following set of requirements:

The employees work in departments for some projects.

An employee can work on multiple projects that may be participated by several departments.

Any employee can only be either a salariedEmp or an hourlyEmp but not both. An employee is uniquely identified by a ssn. We should record dob(date of birth), name, and address of the employee. Name is composed of Fname, Minit, and Lname.

A salariedEmp has a monthly salary and an hourlyEmp has a rate of hourPay.

An employee may have dependents (described by name, and relationship). Dependents of different employees may have the same names.

Each department, identified by deptNum, has deptName and one or more locations. The numEmp (number of employees) can be regarded as a calculated attribute of the department.

A department has a manager who is also a salaried employee.

A project is identified by projName and projNum together. It has a description (projDesc).

(2)Relational Schema Design

Continuing at the corrected version of part #1,

Specify the domains of each attribute listed by relations. If you have special considerations such as Nullness and uniqueness, please point out.

Add semantic checks. SSN must be exactly nine digits, and hourPay must be at least $7.50.

Convert your ER/EER diagram into relational tables.

Specify the entity constraints (by underlining) and referential integrity constraints (by FK-PK relationship arrows).

(3)In this part, you will implement your relational schema designed in part #2 using a commercial database, insert some tuples into the tables and query the database. You can use WAMPS/MAMP/LAMP or SQL server (or Oracle or any other databases).

1. Create the tables. Submit your SQL scripts of CREATE TABLE commands. Enforce key and referential integrity constraints and any other constraints you have (e. g. unique, not null, check etc.).

2. Insert into each table 4 records. Submit your INSERT commands and the final result of running "select * from YOUR_TABLE" for each of your tables (hint: you can cut and paste to a WORD document named as YOUR_LOGIN_prj3.doc as a submission for example). You can insert 10 records for one or two tables to look at more interesting query results.

3. Update a record in one table. Update potentially several records at once using subqueries. Show the records after updating.

4. Query your database. Submit the SQL scripts of five queries into the tables together with their running results. At least one query includes join operation; one has subquery in it; one has group by and having in it; one has set operation (union , intersect or except) in it. Submit the hardcopy.