TablesCreation.sql

CREATE TABLE CUSTOMER(
	CUSTNO varchar(8) not null,
	CUSTNAME varchar(40) not null,
	ADDRESS varchar(40) not null,
	INTERNAL char(1) not null,
	CONTACT varchar(40) not null,
	PHONE VARCHAR(11) not null,
	CITY varchar(20) not null,
	STATE char(2) not null,
	ZIP INTEGER not null,
	CONSTRAINT CASTNOPK PRIMARY KEY (CUSTNO));
CREATE TABLE FACILITY(
	FACNO varchar(8) not null,
	FACNAME char(40) not null,
	CONSTRAINT FACNOPK PRIMARY KEY (FACNO),
	CONSTRAINT FACNAME UNIQUE (FACNAME)
);

CREATE TABLE LOCATION (
	LOCNO varchar(8) not null,
	FACNO varchar(8) not null,
	LOCNAME varchar(40) not null,
	CONSTRAINT LOCNOPK PRIMARY KEY (LOCNO),
	CONSTRAINT FACNOPK FOREIGN KEY (FACNO) REFERENCES FACILITY
);

CREATE TABLE EVENTREQUEST(
	EVENTNO varchar(8) not null,
	DATEHELD date not null,
	DATEREQ date not null,
	FACNO varchar(8) not null,
	CUSTNO varchar(8) not null,
	DATEAUTH date,
	STATUS varchar(8) not null,
	ESTCOST decimal(7,2) not null,
	ESTAUDIENCE integer not null,
	BUDNO varchar(5),
	CONSTRAINT EVENTNOPK PRIMARY KEY (EVENTNO),
	CONSTRAINT CUSTNOFK FOREIGN KEY (CUSTNO) REFERENCES CUSTOMER,
	CONSTRAINT FACNOFK FOREIGN KEY (FACNO) REFERENCES FACILITY,
	CONSTRAINT STATUSCHK CHECK (STATUS IN ('Pending', 'Denied', 'Approved')),
	CONSTRAINT ESTAUDIENCECH CHECK (ESTAUDIENCE >0)
);

CREATE TABLE EMPLOYEE(
	EMPNO varchar(8) not null,
	EMPNAME varchar(40) not null,
	DEPARTMENT varchar(25) not null,
	EMAIL varchar(30) not null,
	PHONE char(6) not null,
	CONSTRAINT EMPNOPK PRIMARY KEY (EMPNO)	
);

CREATE TABLE EVENTPLAN(
	PLANNO varchar(8) not null,
	EVENTNO varchar(8) not null,
	WORKDATE date not null,
	NOTES varchar(25),
	ACTIVITY varchar(9) not null,
	EMPNO varchar(8),
	CONSTRAINT PLANNOPK PRIMARY KEY (PLANNO),
	CONSTRAINT EVENTNOFK FOREIGN KEY (EVENTNO) REFERENCES EVENTREQUEST,
	CONSTRAINT EMPNOFK FOREIGN KEY (EMPNO) REFERENCES EMPLOYEE
);

CREATE TABLE RESOURCETBL(
	RESNO varchar(8) not null,
	RESNAME varchar(20) not null,
	RATE decimal(4,2) not null,
	CONSTRAINT RESNOPK PRIMARY KEY (RESNO),
	CONSTRAINT RATECHECK CHECK (RATE>0)
);

CREATE TABLE EVENTPLANLINE(
	PLANNO varchar(8) not null,
	LINENO integer not null,
	TIMESTART timestamp not null,
	TIMEEND timestamp not null,
	NUMBERFLD integer not null,
	LOCNO varchar(8) not null,
	RESNO varchar(8) not null,
	CONSTRAINT LINENOPLANNOPK PRIMARY KEY (LINENO, PLANNO),
	CONSTRAINT LOCNOFK FOREIGN KEY (LOCNO) REFERENCES LOCATION,
	CONSTRAINT RESNOFK FOREIGN KEY (RESNO) REFERENCES RESOURCETBL,
	CONSTRAINT PLANNOFK FOREIGN KEY (PLANNO) REFERENCES EVENTPLAN,
	CONSTRAINT TIMESTARTENDCHECK CHECK (TIMESTART<TIMEEND)
);