Database work
22-IS3030 Database Design & Tools 1
Lab Assignment
Part 1A (10 points):
To begin, you’ll need to drop all tables that are currently in your Oracle account besides the tables from Lab Assignment 4.
· View all tables currently in your account using the statement SELECT TABLE_NAME FROM USER_TABLES; Then, delete each table using a drop statement: DROP TABLE <table name> CASCADE CONSTRAINTS;
· Use the start script posted on Canvas to create, populate, and add foreign key constraints the tables provided below.
Employee |
|||||||||
|
Ssno |
Last_name |
Mi |
First_name |
Gender |
Bdate |
Address |
Salary |
Supssno |
Dno |
|
123456789 |
MCCARTNEY |
B |
paul |
M |
12/25/1978 |
731, Fondren, WACO, TX |
30000 |
666884444 |
5 |
|
222556666 |
Wong |
T |
Frank |
M |
11/25/1978 |
638, Voss, CLEVELAND, OH |
55000 |
987987987 |
5 |
|
453453453 |
English |
A |
Joyce |
F |
10/25/1978 |
5631, Rice, ORLANDO, FL |
50000 |
222556666 |
5 |
|
666884444 |
GOMEZ |
B |
Jane |
F |
9/25/1978 |
975, FireOak, Humble, TX |
45000 |
222556666 |
5 |
|
777211234 |
RUTHERFORD |
E |
James |
M |
8/25/1978 |
3321, Castle, Spring, TX |
64000 |
|
1 |
|
987654321 |
SMITH |
S |
Jeniffer |
F |
7/25/1978 |
2912, BERRY, CINCINNATI, OH |
60000 |
777211234 |
4 |
|
987987987 |
Jabbar |
V |
Ahmad |
M |
6/25/1978 |
980, CALHOUN, CINCINNATI, OH |
35000 |
|
4 |
|
123123123 |
KEYS |
J |
Alicia |
F |
5/25/1978 |
3321, Castle, CINCINNATI, OH |
64000 |
|
4 |
|
Department |
|||
|
Dept_name |
Dept_no |
Mgr_start_date |
Mgrssno |
|
Headquarters |
1 |
6/19/91 |
777211234 |
|
Administration |
4 |
1/1/89 |
987654321 |
|
Production |
5 |
5/22/93 |
222556666 |
|
Location |
||
|
LoCATION |
LOC_SIZE |
STATE |
|
WACO |
200 |
tx |
|
CINCINNATI |
200 |
OH |
|
ORLANDO |
300 |
fl |
|
CLEVELAND |
400 |
OH |
|
Works_on |
||
|
Essno |
Pno |
Work_hrs |
|
123456789 |
1 |
32.5 |
|
123456789 |
2 |
7.5 |
|
222556666 |
2 |
10 |
|
222556666 |
3 |
10 |
|
222556666 |
10 |
10 |
|
222556666 |
30 |
10 |
|
453453453 |
1 |
20 |
|
453453453 |
2 |
20 |
|
666884444 |
3 |
40 |
|
777211234 |
20 |
0 |
|
987654321 |
20 |
15 |
|
987654321 |
30 |
20 |
|
987987987 |
10 |
35 |
|
987987987 |
30 |
5 |
|
123123123 |
10 |
10 |
|
123123123 |
30 |
25.5 |
|
DependEnt |
||||
|
Essno |
DependEnt_nm |
Dep_gender |
Dep_bdate |
Related_how |
|
123456789 |
Alice |
F |
6/7/92 |
Daughter |
|
123456789 |
Elizabeth |
F |
5/5/67 |
Spouse |
|
123456789 |
Michael |
M |
1/10/89 |
Son |
|
222556666 |
sARAH |
F |
4/5/86 |
Daughter |
|
222556666 |
Joy |
M |
5/3/58 |
Spouse |
|
123123123 |
Theodore |
M |
11/12/96 |
Son |
|
987654321 |
BILL |
M |
2/29/60 |
Spouse |
|
department_Location |
|
|
Dnum |
Dept_location |
|
1 |
WACO |
|
4 |
CINCINNATI |
|
5 |
CLEVELAND |
|
5 |
Detroit |
|
5 |
ORLANDO |
|
Project |
||
|
Proj_name |
Proj_no |
Dnumb |
|
Product a |
1 |
5 |
|
Product b |
2 |
5 |
|
Product c |
3 |
5 |
|
Computerization |
10 |
4 |
|
Reorganization |
20 |
1 |
|
BenefitS PLAN |
30 |
4 |
Part 1B (10 points):
You are tasked with updating the extensive company database in Oracle to incorporate the following changes. It is industry standard to save all your work in notepad or by equivalent means in order to reference later. You will need to make changes to the database before it will allow you to enter data, so decide what needs to be done and in what order. Changes made directly to the original code provided (database structure start script) will NOT receive credit.
1. On January 23 of this year, the company hired a new employee: Samuel G. Patel with a salary of $120,000 and an SSNO 234-01-5678. He was born April 25, 1986 and his manager is Joyce English. Samuel is the Manager of the newly created department, Operations (#18), which runs out the company’s office just opened in Miami, Florida. With this location still in its infancy stage, it only staffs 100 employees.
2. Currently, Samuel is working on a brand-new project called Productivity (#41) which the Operations Department is responsible for. He has already dedicated 89.0 hours of his time to this project and is hoping senior management will realize its potential and provide him with more employees.
3. Even though you work in database management, you have strong ties to HR and so you ask for more details regarding Samuel. Besides enjoying long walks on the beach and murder mystery novels, he has two children, Marisa and Bensen. Marisa was born May 29, 2014 while Ben turned 3 years old the day Samuel was hired. He has a caring wife, Micah, born January 14, 1981. The Patel family lives at 1270, Creekside, Miami, FL.
Part 2 (20 points):
Using the “Company database” that you have created in Oracle, construct queries in SQL (ANSI 1991) to answer the following questions and execute the queries on the Oracle platform (4 points each).
DML:
1. Retrieve the First name, Middle initial, Last name, Birthdate, Gender, and Salary of the employees along with their dependent names. The list should include ALL employees. (Use Outer join)
2. List the SSNO of employees who don’t have dependents.
a. Demonstrate the use of the MINUS operator in this query.
b. Write the query without using the MINUS operator.
3. List the SSNO of employees who are department managers.
a. Demonstrate the use of INTERSECT operator in this query.
b. Write the query without using the INTERSECT operator.
4. Retrieve the name and birthday of all employees; if the employee is a manager, include the name of the department managed. If the employee has a dependent(s) include the name of the dependents(s)
a. Demonstrate the use of outer join operation in this query.
-if an employee has multiple dependents it is okay for their name to be in multiple records
5. List the names of the dependents whose related employee works on the “PRODUCT B” Project.
Deliverables:
· Your SQL work for Part 1 in a notepad text file showing the changes made to the database and the proper data entries made.
· Either using the spool function, spool each query for Part 2 and its results immediately following the query, or copy your query results in a notepad text file after each query.