CREATE TABLE Employee
             (empNo  	VARCHAR(4)    NOT NULL,
              fName	VARCHAR(25),
              lName	VARCHAR(20),
		    address VARCHAR(45),
			DOB smallint,
               Sex char NOT NULL
                   CHECK(sex IN('M','F')),
               position VARCHAR NOT NULL
CHECK(position IN('MAanger', 'Team Leader', 'Analyst', 'Software Developer')),
               deptNo VARCHAR(10),
            
	     
PRIMARY KEY (EmpNo));

CREATE TABLE Department

             (deptNo   VARCHAR(10)	NOT NULL,
	      deptName	VARCHAR(50),
	      mgrEmpNo VARCHAR(35),
              
             
PRIMARY KEY (deptNo));


CREATE TABLE Project

             (projNo  VARCHAR(24)	NOT NULL,
	      projName	VARCHAR(40),
	      deptNo	VARCHAR(10),
                        
PRIMARY KEY (projNo));


CREATE TABLE WorksOn

             (empNo  		VARCHAR(4)	NOT NULL,
	      projNo	VARCHAR(10),
	      dateWorked	dateworked,
           hoursWorked VARCHAR(10),
                       
PRIMARY KEY (empNo, projNo, dateWorked));

Commit;

CREATE TABLE Sexes
( Sex CHAR(1) NOT NULL PRIMARY KEY
);
INSERT INTO Sexes VALUES ('M'),('F')
;

CREATE TABLE Positions
( Position VARCHAR(37) NOT NULL PRIMARY KEY
);
INSERT INTO Positions VALUES 
 ('Manager')
,('Team Leader')
,('Analyst')
,('Software Developer')
;

CREATE TABLE Departments
( deptNo  VARCHAR(10) NOT NULL PRIMARY KEY
, deptName VARCHAR(50)
, mgrEmpNo VARCHAR(35)
);

CREATE TABLE Employee 
( empNo VARCHAR(4) NOT NULL PRIMARY KEY
, fName VARCHAR(25)
, lName VARCHAR(20)
, address VARCHAR(45)
, DOB DATE
, Sex CHAR(1) NOT NULL 
      REFERENCES Sexes ( Sex )
, position VARCHAR(37) NOT NULL
      REFERENCES Positions ( position )
, deptNo VARCHAR(10)
      REFERENCES Departments ( deptNo )
);