FINAL
CS422
Summer 2005
Please
upload the following files using the online
turnin server at the end of the exam:
- final.sql, which
includes all the queries, stored procedures, and triggers.
- Java source code for the Client Application problem.
- Hibernate mapping (.hbm.xml) files for the extra credit
problem.
Note that file
uploading will be disabled
automatically after 9:35PM, and late turnin will not
be
accepted.
[Schema] Download the following scripts:
Run the hr8_main.sql
script,
which will create and populate a number of tables as shown in Figure 1.
Figure
1. Human Resource (HR) Tables
You
will use these tables to solve the problems in the exam. If
you
want to remove the tables from your schema, simply run the script hr8_drop.sql.
[SQL] Complete the following SQL queries:
Q1. (5pt) List the name(s) of the employee(s) who do not have a manager.
Q2. (5pt) Find the name of the department that has the most employees.
Q3. (10pt) Find the number of employees managed directly or indirectly
by Neena Kochhar.
Q4. (10pt) List the id('s) of the employee(s) who have worked in more
than one country.
[Stored Procedures] (20pt) Implement a stored procedure which takes an
employee id
as parameter and removes the employee from the database. In particular,
the procedure first checks whether the employee has a manager, and if
not, the procedure outputs a message "Cannot remove the president." and
terminates; otherwise, the procedure performs the following operations
(and for clarity, we will refer to the employee to be removed as E, and
his or her manager as M):
- assign M to be the manager
of the employee(s) who are currently managed by E.
- assign M to be the manager
of the department(s) who are currently managed by E.
- remove E's information from
the JOB_HISTORY table.
- remove E's information from
the EMPLOYEES table.
[Triggers] Use triggers to enforces the following
constraints:
- (15pt) A manager must have a
higher salary than any employee managed by him or her.
- (15pt) The phone number of
an employee who works at a location outside the US or Canada must start
with
"011.".
[Client Application] (20pt) Write a Java program which looks up the
database for employee emails and phone numbers. In particular, the
program takes a string from user input, and outputs the name,
email, and phone number of the employee(s) whose first name and/or last
name fully or partially match the input string in a case-insensitive
manner. For example, if the input string is "john", the program
should
output the following:
John Chen, JCHEN, 515.124.4269
John Seo, JSEO, 650.121.2019
John Russell, JRUSSEL, 011.44.1344.429268
Charles Johnson, CJOHNSON, 011.44.1644.429262
[Extra Credit] (30pt) Write the hibernate mapping files for
the following Java classes so that if you run the hbm2ddl (a.k.a.
SchemaExport) utility, the generated schema is exactly
the same as the one shown in employees.ddl.
