--DROP TABLE Available_Jobs
--DROP TABLE Description
--DROP TABLE Personal_Details
--DROP TABLE Agency
--DROP TABLE Job_Consultant

CREATE TABLE    Description (
DescriptionID	INTEGER		IDENTITY (1,1) NOT NULL,
Startdate		DATETIME	NOT NULL,
Duration		INTEGER		NOT NULL,
Location		VARCHAR(20)	NOT NULL,
Salary			DECIMAL(6,2),
PRIMARY KEY     (DescriptionID),
);

CREATE TABLE    Available_Jobs (
JobID			INTEGER		IDENTITY (1,1) NOT NULL,
DescriptionID	INTEGER		NOT NULL,
Employer		VARCHAR(50)	NOT NULL,
PRIMARY KEY     (JobID),
FOREIGN KEY		(DescriptionID) REFERENCES Description
);


CREATE TABLE	Personal_Details (
PersonID		INTEGER		IDENTITY (1,1) NOT NULL,
Name			VARCHAR(30)	 NOT NULL,
Adress			VARCHAR(60)	 NOT NULL,
Date_of_birth	DATETIME	 NOT NULL,
Sex				VARCHAR(6)		NOT NULL,
Email			VARCHAR(30)		NOT NULL,
Phone_number	VARCHAR(15)		NOT NULL,
PRIMARY KEY		(PersonID),
);

CREATE TABLE	Job_Consultant (
ConsultantID	INTEGER		IDENTITY (1,1) NOT NULL,
AgencyID		INTEGER	NOT NULL,
PersonID		INTEGER	NOT NULL,
PRIMARY KEY		(ConsultantID),
FOREIGN KEY		(AgencyID) 	REFERENCES Agency,
FOREIGN KEY		(PersonID) 	REFERENCES Person_Details
);

CREATE TABLE	Agency (
AgencyID		INTEGER		IDENTITY (1,1) NOT NULL,
ConsultantID	INTEGER		 NOT NULL,
JobID			INTEGER		 NOT NULL,
Location		VARCHAR(30)	NOT NULL,
Phone			VARCHAR(15) NOT NULL,
Email			VARCHAR(30) NOT NULL,
PRIMARY KEY		(AgencyID),
FOREIGN KEY		(ConsultantID) 	REFERENCES Job_Consultant,
FOREIGN KEY		(JobID)			REFERENCES Available_Jobs
);