SOLUTIONS TO CSPP - 606

	Q1. Create tables with relevant foreign keys.
	Q2. Populate the tables with data.
	Q3. Perform the following queries on the database:

ANS 1.
create table EMPLOYEE
(
Fname	varchar(15)	not null,
Minit	char,
Lname	varchar(15)	not null,
Ssn	char(9),
Bdate	date,
Address	varchar(30),
Sex	char,
Salary	float(10,2),
Superssn	char(9),
Dno	int,
Primary key(Ssn),
Foreign key(Superssn) references EMPLOYEE(Ssn)
)

create table DEPARTMENT
(
Dname		varchar(16)	not null,
Dnumber		int 		Primary key,
Mgrssn		char(9)		not null,
MgrStartDate	date,
unique(dname),
Foreign key(Mgrssn) references EMPLOYEE(Ssn)
)

alter table EMPLOYEE
add constraint Emo_PK
foreign key(dno) references department(dnumber)

create table DEPT_LOCATIONS
(
Dnumber		int		not null,
Dlocation		varchar(15),
Primary key (Dnumber,Dlocation),
Foreign key(Dnumber) references DEPARTMENT(Dnumber)
)

create table PROJECT
(
Pname	varchar(15)	not null,
Pnumber	int		Primary Key,
Plocation	varchar(15),
Dnum	int		Foreign key references DEPARTMENT(Dnumber)
unique(Pname)
)

create table WORKS_ON
(
Essn	char(9)	foreign key references Employee(Ssn),
Pno	int	foreign key references PROJECT(Pnumber),
Hours	float(3,1),
primary key(Essn,pno)
)
create table DEPENDENT
(
Essn		char(9)		foreign key references Employee(Ssn),
Dependent_name	varchar(15)	not null,
Sex		char(1),
Bdate		date,
Relationship	varchar(10),
primary key(Essn,Dependent_name)
)

ANS 2.


ANS 3.