$/*$ Company database mySQL data definition.

Ordering of tables is important

since a table can reference tables that are defined before.

Since date type is not available in MySQL$,$

create a new user defined data type date as char$(10).$

$*/$

$/*$ Phase $1$: Drop all six tables of Company database if they exist

in the correct sequence without violating foreign key constraints

$*/$

$--$ if dependent table already exists, remove the table

drop table if exists dependent;

$--$ if works$\_$on table already exists, remove the tablema

drop table if exists works$\_$on;

$--$ if project table already exists, remove the table

drop table if exists project;

$--$ if dept$\_$locations table already exists, remove the table

drop table if exists dept$\_$locations;

$--$ if employee table already exists, remove the table

drop table if exists employee;

$--$ if department table already exists,department remove the table

drop table if exists department;

$/*$ Phase $2$: Create all six empty tables of Company database $*/$

$--$ create a new empty table department

create table department

$($ dname varchar$(15)$ not null,

dnumber int not null,

mgrssn char$(9)$ not null,

mgrstartdate char$(10),$

constraint deptpk

primary key $($dnumber$),$

constraint deptsk

unique $($dname$)$

$/*$constraint deptmgrfk

foreign key $($mgrssn$)$ references employee$($ssn$)$

This foreign key has been commented out since the dbms does not

support mutual foreign keys between department and employee tables $*/$

$)$;

$--$ create a new empty table employee

create table employee

$($ fname varchar$(15)$ not null,

minit char,

lname varchar$(15)$ not null,

ssn char$(9)$ not null,

bdate char$(10),$

address varchar$(30),$

sex char,

salary decimal$(10,2),$

superssn char$(9),$

dno int not null,

constraint emppk

primary key $($ssn$),$

$/*$constraint empsuperfk

foreign key $($superssn$)$ references employee$($ssn$),$

This foreign key constraint among columns of the same table

has not been supported by MS$-$SQL $*/$

constraint empdeptfk

foreign key $($dno$)$ references department$($dnumber$)$

$)$;

$--$ create a new empty table dept$\_$locations

create table dept$\_$locations

$($ dnumber int not null,

dlocation varchar$(15)$ not null,

constraint dlocpk

primary key $($dnumber$,$ dlocation$),$

constraint dlocdeptfk

foreign key $($dnumber$)$ references department$($dnumber$)$

$)$;

$--$ create a new empty table project

create table project

$($ pname varchar$(15)$ not null,

pnumber int not null,

plocation varchar$(15),$

dnum int not null,

constraint projpk

primary key $($pnumber$),$

constraint projsk

unique $($pname$),$

constraint projdeptfk

foreign key $($dnum$)$ references department$($dnumber$)$

$)$;

$--$ create a new empty table works$\_$on

create table works$\_$on

$($ essn char$(9)$ not null,

pno int not null,

hours decimal$(3,1)$ not null,

constraint workpk

primary key $($essn$,$ pno$),$

constraint workempfk

foreign key $($essn$)$ references employee$($ssn$),$

constraint workprojfk

foreign key $($pno$)$ references project$($pnumber$)$

$)$;

$--$ create a new empty table dependent

create table dependent

$($ essn char$(9)$ not null,

dependent$\_$name varchar$(15)$ not null,

sex char,

bdate char$(10),$

relationship varchar$(8),$

constraint deppk

primary key $($essn$,$ dependent$\_$name$),$

constraint depempfk

foreign key $($essn$)$ references employee$($ssn$)$

$)$;

$/*$ Phase $3$: Add rows of data to each of these six empty tables of Company DB $*/$

$--$ Insertion of three department rows

INSERT INTO department

VALUES

$(\text{'}$Research$\text{'},\text{'}5\text{'},\text{'}333445555\text{'},\text{'}1988-05-22\text{'})$;

INSERT INTO department

VALUES

$(\text{'}$Administration$\text{'},\text{'}4\text{'},\text{'}987654321\text{'},\text{'}1995-01-01\text{'})$;

INSERT INTO department

VALUES

$(\text{'}$Headquarters$\text{'},\text{'}1\text{'},\text{'}888665555\text{'},\text{'}1981-06-19\text{'})$;

$--$ Insertion of eight employee rows

INSERT INTO employee

VALUES

$(\text{'}$James$\text{'},\text{'}$E$\text{'},$'Borg',$\text{'}888665555\text{'},\text{'}1937-11-10\text{'},\text{'}450$ Stone, Houston, TX$\text{'},\text{'}$M$\text{'},\text{'}55000\text{'},\text{'}\text{'},\text{'}1\text{'})$;

INSERT INTO employee

VALUES

$(\text{'}$Franklin$\text{'},\text{'}$T$\text{'},$'Wong',$\text{'}333445555\text{'},\text{'}1955-12-08\text{'},\text{'}638$ Voss, Houston, TX$\text{'},\text{'}$M$\text{'},\text{'}40000\text{'},\text{'}888665555\text{'},\text{'}5\text{'})$;

INSERT INTO employee

VALUES

$(\text{'}$Jennifer$\text{'},\text{'}$S$\text{'},$'Wallace',$\text{'}987654321\text{'},\text{'}1941-06-20\text{'},\text{'}291$ Berry, Bellaire, TX$\text{'},\text{'}$F$\text{'},\text{'}43000\text{'},\text{'}888665555\text{'},\text{'}4\text{'})$;

INSERT INTO employee

VALUES

$(\text{'}$John$\text{'},\text{'}$B$\text{'},$'Smith',$\text{'}123456789\text{'},\text{'}1965-01-09\text{'},\text{'}731$ Fondren, Houston, TX$\text{'},\text{'}$M$\text{'},\text{'}30000\text{'},\text{'}333445555\text{'},\text{'}5\text{'})$;

INSERT INTO employee

VALUES

$(\text{'}$Ramesh$\text{'},\text{'}$K$\text{'},$'Narayan',$\text{'}666884444\text{'},\text{'}1962-09-15\text{'},\text{'}975$ Fire Oak, Humble, TX$\text{'},\text{'}$M$\text{'},\text{'}38000\text{'},\text{'}333445555\text{'},\text{'}5\text{'})$;

INSERT INTO employee

VALUES

$(\text{'}$Joyce$\text{'},\text{'}$A$\text{'},$'English',$\text{'}453453453\text{'},\text{'}1972-07-31\text{'},\text{'}5631$ Rice, Houston, TX$\text{'},\text{'}$F$\text{'},\text{'}25000\text{'},\text{'}333445555\text{'},\text{'}5\text{'})$;

INSERT INTO employee

VALUES

$(\text{'}$Alicia$\text{'},\text{'}$J$\text{'},$'Zelaya',$\text{'}999887777\text{'},\text{'}1968-01-19\text{'},\text{'}3321$ Castle, Spring, TX$\text{'},\text{'}$F$\text{'},\text{'}25000\text{'},\text{'}987654321\text{'},\text{'}4\text{'})$;

INSERT INTO employee

VALUES

$(\text{'}$Ahmad$\text{'},\text{'}$V$\text{'},$'Jabbar',$\text{'}987987987\text{'},\text{'}1969-03-29\text{'},\text{'}980$ Dallas, Houston, TX$\text{'},\text{'}$M$\text{'},\text{'}25000\text{'},\text{'}987654321\text{'},\text{'}4\text{'})$;

$--$ Insertion of five dept$\_$locations rows

INSERT INTO dept$\_$locations

VALUES

$(\text{'}1\text{'},$'Houston'$)$;

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{'},$'Sugarl