TestBed.sql

drop table Employee;

drop table Department;

drop table Dept$\_$Locations;

drop table Works$\_$on;

drop table Comp$\_$Project;

drop table Dependent;

CREATE TABLE Employee $($

SSN CHAR$(9),$

Fname CHAR$(10),$

Minit CHAR$(1),$

Lname CHAR$(10),$

Address CHAR$(30),$

Sex CHAR$(1),$

Salary REAL,

SuperSSN CHAR$(9),$

Dno CHAR$(1),$

PRIMARY KEY $($SSN$))$;

INSERT INTO Employee VALUES $(\text{'}123456789\text{'},$ 'John', $\text{'}$B$\text{'},$ 'Smith', $\text{'}731$ Fondren, Houston TX$\text{'},\text{'}$M$\text{'},30000,\text{'}333445555\text{'},\text{'}5\text{'})$;

INSERT INTO Employee VALUES $(\text{'}333445555\text{'},$ 'Franklin', $\text{'}$T$\text{'},$ 'Wong', $\text{'}638$ Voss, Houston TX$\text{'},\text{'}$M$\text{'},40000,\text{'}888665555\text{'},\text{'}5\text{'})$;

INSERT INTO Employee VALUES $(\text{'}999887777\text{'},$ 'Alicia', $\text{'}$J$\text{'},$ 'Zelaya', $\text{'}3321$ Castle, Spring TX$\text{'},\text{'}$F$\text{'},25000,\text{'}987654321\text{'},\text{'}4\text{'})$;

INSERT INTO Employee VALUES $(\text{'}987654321\text{'},$ 'Jennifer', $\text{'}$S$\text{'},$ 'Wallace', $\text{'}291$ Berry, Bellaire TX$\text{'},\text{'}$F$\text{'},43000,\text{'}888665555\text{'},\text{'}4\text{'})$;

INSERT INTO Employee VALUES $(\text{'}666884444\text{'},$ 'Ramesh', $\text{'}$K$\text{'},$ 'Narayan', $\text{'}975$ FireOak, Humble TX$\text{'},\text{'}$M$\text{'},38000,\text{'}333445555\text{'},\text{'}5\text{'})$;

INSERT INTO Employee VALUES $(\text{'}453453453\text{'},$ 'Joyce', $\text{'}$A$\text{'},$ 'English', $\text{'}5631$ Rice, Houston TX$\text{'},\text{'}$F$\text{'},25000,\text{'}333445555\text{'},\text{'}5\text{'})$;

INSERT INTO Employee VALUES $(\text{'}987987987\text{'},$ 'Ahmad', $\text{'}$V$\text{'},$ 'Jabbar', $\text{'}980$ Dallas, Houston TX$\text{'},\text{'}$M$\text{'},25000,\text{'}987654321\text{'},\text{'}4\text{'})$;

INSERT INTO Employee VALUES $(\text{'}888665555\text{'},$ 'James', $\text{'}$E$\text{'},$ 'Borg', $\text{'}450$ Stone, Houston TX$\text{'},\text{'}$M$\text{'},55000,$ null, $\text{'}1\text{'})$;

CREATE TABLE Department $($

Dnumber CHAR$(1),$

Dname CHAR$(20),$

MgrSSN CHAR$(9),$

PRIMARY KEY $($Dnumber$))$;

INSERT INTO Department VALUES $(\text{'}5\text{'},$ 'Research', $\text{'}333445555\text{'})$;

INSERT INTO Department VALUES $(\text{'}4\text{'},$ 'Administration', $\text{'}987654321\text{'})$;

INSERT INTO Department VALUES $(\text{'}3\text{'},$ 'Research', $\text{'}333445555\text{'})$;

INSERT INTO Department VALUES $(\text{'}1\text{'},$ 'Headquarters', $\text{'}888665555\text{'})$;

CREATE TABLE Dept$\_$Locations $($

Dnumber CHAR$(1),$

Dlocation CHAR$(20),$

PRIMARY KEY $($Dnumber$,$ Dlocation$))$;

INSERT INTO Dept$\_$Locations VALUES $(\text{'}1\text{'},$ 'Houston'$)$;

INSERT INTO Dept$\_$Locations VALUES $(\text{'}3\text{'},$ 'Bellaire'$)$;

INSERT INTO Dept$\_$Locations VALUES $(\text{'}4\text{'},$ 'Stafford'$)$;

INSERT INTO Dept$\_$Locations VALUES $(\text{'}5\text{'},$ 'Bellaire'$)$;

INSERT INTO Dept$\_$Locations VALUES $(\text{'}5\text{'},$ 'Sugarland'$)$;

INSERT INTO Dept$\_$Locations VALUES $(\text{'}5\text{'},$ 'Houston'$)$;

CREATE TABLE Works$\_$on $($

ESSN CHAR$(9),$

Pno CHAR$(2),$

Hours REAL,

PRIMARY KEY $($ESSN$,$ Pno$))$;

INSERT INTO Works$\_$on VALUES $(\text{'}123456789\text{'},\text{'}1\text{'},32\mathrm{.}5)$;

INSERT INTO Works$\_$on VALUES $(\text{'}123456789\text{'},\text{'}2\text{'},7\mathrm{.}5)$;

INSERT INTO Works$\_$on VALUES $(\text{'}666884444\text{'},\text{'}3\text{'},40\mathrm{.}0)$;

INSERT INTO Works$\_$on VALUES $(\text{'}453453453\text{'},\text{'}1\text{'},20\mathrm{.}0)$;

INSERT INTO Works$\_$on VALUES $(\text{'}453453453\text{'},\text{'}2\text{'},20\mathrm{.}0)$;

INSERT INTO Works$\_$on VALUES $(\text{'}333445555\text{'},\text{'}2\text{'},10\mathrm{.}0)$;

INSERT INTO Works$\_$on VALUES $(\text{'}333445555\text{'},\text{'}3\text{'},10\mathrm{.}0)$;

INSERT INTO Works$\_$on VALUES $(\text{'}333445555\text{'},\text{'}10\text{'},10\mathrm{.}0)$;

INSERT INTO Works$\_$on VALUES $(\text{'}333445555\text{'},\text{'}20\text{'},10\mathrm{.}0)$;

INSERT INTO Works$\_$on VALUES $(\text{'}999887777\text{'},\text{'}30\text{'},30\mathrm{.}0)$;

INSERT INTO Works$\_$on VALUES $(\text{'}999887777\text{'},\text{'}10\text{'},10\mathrm{.}0)$;

INSERT INTO Works$\_$on VALUES $(\text{'}987987987\text{'},\text{'}10\text{'},35\mathrm{.}0)$;

INSERT INTO Works$\_$on VALUES $(\text{'}987987987\text{'},\text{'}30\text{'},5\mathrm{.}0)$;

INSERT INTO Works$\_$on VALUES $(\text{'}987654321\text{'},\text{'}30\text{'},20\mathrm{.}0)$;

INSERT INTO Works$\_$on VALUES $(\text{'}987654321\text{'},\text{'}20\text{'},15\mathrm{.}0)$;

INSERT INTO Works$\_$on VALUES $(\text{'}888665555\text{'},\text{'}20\text{'},$ null$)$;

CREATE TABLE Comp$\_$Project $($

Pnumber CHAR$(2),$

Pname CHAR$(20),$

Plocation CHAR$(20),$

Dnum CHAR$(1),$

PRIMARY KEY $($Pnumber$))$;

INSERT INTO Comp$\_$Project VALUES $(\text{'}1\text{'},$ 'ProductX', 'Bellaire', $\text{'}5\text{'})$;

INSERT INTO Comp$\_$Project VALUES $(\text{'}2\text{'},$ 'ProductY', 'Sugarland', $\text{'}5\text{'})$;

INSERT INTO Comp$\_$Project VALUES $(\text{'}3\text{'},$ 'ProductZ', 'Houston', $\text{'}5\text{'})$;

INSERT INTO Comp$\_$Project VALUES $(\text{'}10\text{'},$ 'Computerization', 'Stafford', $\text{'}4\text{'})$;

INSERT INTO Comp$\_$Project VALUES $(\text{'}20\text{'},$ 'Reorganization', 'Houston', $\text{'}1\text{'})$;

INSERT INTO Comp$\_$Project VALUES $(\text{'}30\text{'},$ 'ProductX', 'Stafford', $\text{'}4\text{'})$;

CREATE TABLE Dependent $($

ESSN CHAR$(9),$

Dependent$\_$name CHAR$(20),$

Sex CHAR$(1),$

Relationship CHAR$(10),$

PRIMARY KEY $($ESSN$,$ Dependent$\_$name$))$;

INSERT INTO Dependent VALUES $(\text{'}333445555\text{'},$ 'Alice', $\text{'}$F$\text{'},$ 'Daughter'$)$;

INSERT INTO Dependent VALUES $(\text{'}333445555\text{'},$ 'Theodore', $\text{'}$M$\text{'},$ 'Son'$)$;

INSERT INTO Dependent VALUES $(\text{'}333445555\text{'},$ 'Joy', $\text{'}$F$\text{'},$ 'Spouse'$)$;

INSERT INTO Dependent VALUES $(\text{'}987654321\text{'},$ 'Abner', $\text{'}$M$\text{'},$ 'Spouse'$)$;

INSERT INTO Dependent VALUES $(\text{'}123456789\text{'},$ 'Michael', $\text{'}$M$\text{'},$ 'Son'$)$;

INSERT INTO Dependent VALUES $(\text{'}123456789\text{'},$ 'Alice', $\text{'}$F$\text{'},$ 'Daughter'$)$;

INSERT INTO Dependent VALUES $(\text{'}123456789\text{'},$ 'Elizabeth', $\text{'}$F$\text{'},$ 'Spouse'$)$;

Use Oracle WPI DB to run the given TestBed.sql script to deploy the database and construct the corresponding

SQL queries for the above Problem $2$ questions. Your solutions of this problem should include:

a$)$ Attach all SQL queries and the corresponding query results in the pdf document.

b$)$ Submit a script file that includes all the SQL queries in $($a$).$ Your script file will be graded only if it executes as one script file without syntax errors or another similar problem.

Note: TestBed.sql script is NOT fully completed and implemented. It is only for you to test your SQL$.$ You should base upon the logic of Relation Algebra in Problem $2$ to help you complete this task.