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.