95–703 D: Database Management
DB Implementation Project
This project is to be completed in Oracle Database 18c Express by groups of two students.
Pairing will be provided by the instructor. This document is broken down into the following
parts: description of the model (Part I), the specific tasks you need to do (Part II), and what is
required for submission (Part III). Submit your project (hardcopy) on December 4th, by 6 p.m.
Part I. Description of the model
In Homer Consulting each employee is assigned to (employed by) a specific department. One of
the employees from a department manages the department. Some employees (especially in
larger departments) supervise other employees in their department. Thus, one employee may
have a supervisor and may also supervise other employees as well. The CEO supervises the toplevel
employee of each department.
The company runs projects for external clients. Information on both current clients (those that
have had projects done in the past) as well as potential clients (those who have not requested
projects yet) is kept. For billing purposes, each project is contained within one department.
Therefore, most (but not necessarily all) of employees assigned to a project come from one
department. Administration department does not run any projects. For each project start date
and the total cost is recorded. The total cost of a project can only be estimated (and recorded)
after the project is completed. For an ongoing project, the total cost will have a NULL value.
Each project has several employees working on (assigned to) the project. Newly hired
employees are not assigned to any project for the first few months and may be trained internally
or by attending a formal workshop. Each assignment is recorded on month-by-month base.
Within each month the date employee was assigned to that project, and the end date of the
assignment in that month (not necessarily the last day of the month) is recorded. In addition, the
total number of hours spent by the employee on the project in that month is kept. If an employee
worked on a project for two months, then two records would be created, one record for each
month. Number of hours used within each month is known and recorded after the monthly
assignment is completed. For an assignment in a current month, both the end date and hours
spent are NULL.
Homer Consulting maintains a list of desired skills. For each desired skill, code and description
is kept. Not all of these skills might be represented among the employees. Whenever an
employee acquires a skill, through some form of training (i.e. completing a course at a college,
completing a technical training program, or a workshop), approved by the company, the date
when it was acquired and a comment regarding the training is recorded. Each employee may
take the same training more that once since some of the skills might need to be updated or
renewed.
ER Diagram:
Schema:
SKILL (Code, Description)
TRAINING (Train_Num, Code@, Emp_Num@, Date_Acquired, Name, Comments)
DEPARTMENT (Dept_Code, Name, Location, Phone, Manager_ID@)
EMPLOYEE (Emp_Num, Emp_Last, Emp_First, DOB, Hire_Date, Super_ID@, Dept_Code@)
CLIENT (Client_ID, Name, Street, City, State, Zip_Code, Industry, Web_Address, Phone,
Contact_Name)
PROJECT (Proj_Number, Name, Start_Date, Total_Cost, Dept_Code@, Client_ID@)
ASSIGNMENT (Assign_Num, Proj_Number@, Emp_Num@, Date_Assigned, Date_Ended,
Hours_Used)
A. Create two separate SQL scripts for creating all necessary tables, data integrity
constraints, and for inserting data into the tables to implement the database as defined
above.
1. Script #1: Write all CREATE TABLE statements that have the necessary integrity
constraints including primary keys, foreign keys, check constrains, etc. Start the script
with a set of DROP TABLE statements that will allow you to clean up the database
before creating it (useful when you recreate the database using the script). Use the names
of tables as well as the names of attributes exactly as provided in the model above.
All constraints, except one, must be created and enforced within the “Create Table”
statements and must be named using the convention discussed in class (lecture 12). Run
the script to create the DB objects and to create the missing constraint (as a last statement
in your script). Your CREATE TABLE statements must also include the constraints
defined below:
a. The employee’s Hire_Date, the project Start_Date, and the Date_Assigned
should have a default value that is current date,
b. The Date_Ended cannot be earlier than the Date_Assigned, and
c. Three more appropriate and useful check constraints (but not the Not Null
constraints) of your choice. Make sure that you clearly identify them.
2. Script #2: After the DB tables and all data integrity constraints are created, insert about
ten rows of data into each table. Provide data that would allow you to test all queries as
defined in section B below (each query must give you results). It may be required to add
more than 10 rows in some tables in order to show that your queries work as intended.
The Department and Skill tables could have fewer rows. Run the script to perform the
task of populating the tables in your database. (Note: All constraints must be enabled and
enforced – not deferred – before you populate your tables)
3. After all tables are populated, list the complete content of each table. Format content of
each table to make it readable and understandable.
B. Create SQL queries to answer the following questions:
1. The company’s human resources department needs information on the current training
levels of the employees. They need information that shows, for each employee, and
his/her acquired skills, the number of times he or she received training for that skill, the
most recent date of the training, and the number of months (full months only) that have
passed since the most recent training for the skill. Include all employees in the output,
even if they have not received any training yet.
2. Due to a recent re-organization, the company needs a list that shows who supervises who.
List all the employees in the company clearly indicating the organizational hierarchy.
Include the “level” of the hierarchy each employee is at and the department name of the
employee.
3. For every ongoing project (i.e., a project with total cost being NULL) provide the project
name, project start date. In addition, for each month of the project list the number of
employees assigned that month and hours spent on the project that month. At the end
provide an overall total of the number of employees assigned to the project and the total
hours spent on the project.
4. The company has decided to adopt a bonus policy for their employees. Each employee
will receive a bonus of $200 for every project started last calendar year, on which they
have charged at least 40 hours. As a result, management has asked you to update the
employee records to include their current bonus information. In order to accomplish this,
you will have to add another column to the EMPLOYEE table (i.e., column
BONUS_AMT), before you update the data. After the single Update statement is
performed, list the contents of the Employee table.
5. For each employee hired last calendar year, list the name and the hire date of the
employee, name of training received (if any), date of the training, and the number of days
between the hire date and the training. Also include number of projects that employee
worked on so far.
6. Some projects are discontinued for certain period of time and resumed later. Find out
project name and start date of those projects that have discontinuous activity. Indicate
whether the “discontinued” projects have been completed (i.e., your output should clearly
indicate this by “completed” or “on-going” value in a column).
7. We need a summary of project information for each quarter of this year. For each quarter
include: number of projects started in the quarter, number of employees working on those
projects in the quarter, number of hours spent on average per project.
8. For each employee and each skill, list the number of times the employee had training and
the latest date (most recent) when the employee acquired that particular skill. Also,
provide the number of trainings provided/attended for each skill, and the number of skills
acquired by each employee. The output should look like this:
ID Employee Name ETL
Skills:
100 Mary Smith 1 10/25/19 0 ------- 0 ------- 0 ------- 1
110 Alice Rodriguez 0 ------- 1 11/02/19 2 6/24/19 2 9/07/19 3
120 Kunal Shah 1 9/25/19 2 6/02/19 2 11/23/18 1 4/07/19 4
130 Tao Li 1 6/23/19 1 12/21/18 1 11/23/18 1 10/17/18 4
140 Pat Gomez 0 ------- 1 11/02/19 1 6/24/19 1 10/17/18 3
Number of Trainings: 3 5 6 5
(Note: the values in the table are made up; use the names/values from your own database):
9. For each department and each skill combination, list the number of trainings completed
within the department that was associated with the particular skill. Also provide a rank of
each skill within each department. The rank should be based on the number of trainings
completed for that skill. Same rank should be given when the number of trainings is the
same.
10. For each employee, list ID and name, his/her supervisor’s ID and name (concatenate ID and
name for both employees and supervisors), any training each employee has had, and the
skill(s) associated with the training. For the employee’s training, list the name of training,
training date, skill(s) obtained through the training, and the number of months since the
training date. In addition, provide the number of trainings each employee has had.
11. The firm defines utilization as the number of hours worked since employment divided by the
number of hours available. It also awards the best utilized employee with $10,000, second
best with $9000 and so on till the fifth best with $6000. Given that each employee can work
only 40 hours-per-week, list the top five employees with highest utilization. Also list the
utilization rate and the assigned award/bonus.
12. Create a single query that accesses the system catalog to retrieve information about your
database tables and their columns, as well as any constraints on those columns. Include the
following:
a. Table name
b. Column name
c. Constraint name
d. Constraint type. Instead of the Oracle’s one letter codes, use the two letter indicators as
defined in lecture #12
e. Search condition (for any check constraints), and
f. Table and column that each FK references
Sort the result by table name. List column in the same order as they were created in their
tables. Include commands used for formatting the results (e.g., use BREAK command to
suppress repeating names of tables and columns and COLUMN command on individual
columns).
Part III.
Create a title page that includes your names, email addresses, and the group number as it
was assigned to your group. Clearly identify each part of your submitted project and each
answer. Submit the following:
1. Printout of the two separate SQL script files (Script#1 & Script#2) as you created in Part II.A.
as well as the content of the tables. (Note: Do not print the “spool file” of executing the
scripts)
2. The queries created in Part II.B. Include the SQL statements as you execute them as well as
the results. Make sure you appropriately format both the statements and results (refer to the
“Formatting SQL Output” document provided for your SQL Assignments).
Grading
Projects will be graded based on the following criteria:
Correctness of SQL based on requirements given in the assignment. Although there may be
more than one way to approach each question, the SQL query/results must match the
requirements as defined in each question.
Readability of your SQL statements as they are executed and well formatted results.
Punctuality. The deadline for the submission is 6 p.m. on December 4th.
o Projects submitted on December 4th but later than 6 p.m. will receive a 25% penalty
o Projects will not be accepted after December 4th.