95–703 D代做、代写Database、SQL程序语言调试、SQL代写代写

日期：2019-11-07 09:37

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.