LAB7.SQL

/*
SECTION D
CLIENT
COLUMN          	TYPE    	     NN / U   PK   REF-TAB      PK-REF	  CHECK

CLIENT_ID		NUMBER(5)			Y
CLIENT_NAME		VARCHAR2(20)    NN, U
CLIENT_ADDRESS	VARCHAR2(30)      
CLIENT_CITY		VARCHAR2(10)    NN
CLIENT_PROV		CHAR(2)             NN
CLIENT_POSTAL	CHAR(6)             NN
*/

CREATE TABLE CLIENT (
    CLIENT_ID NUMBER(5) PRIMARY KEY,
    CLIENT_NAME VARCHAR2(20) NOT NULL,
    CLIENT_ADDRESS VARCHAR2(30),
    CLIENT_CITY VARCHAR2(10) NOT NULL,
    CLIENT_PROV VARCHAR2(2) NOT NULL,
    CLIENT_POSTAL VARCHAR2(6) NOT NULL,
    CONSTRAINT UN_CLIENT UNIQUE(CLIENT_NAME)  
);

/*		
PROG
COLUMN          	TYPE    	     NN / U   PK   REF-TAB     PK-REF     CHECK
PROG_ID		NUMBER(5)			Y
PROG_NAME		VARCHAR2(30)    NN
PROG_OFFICE		CHAR(4)             NN
PROG_PHONE		CHAR(10) 
*/          

CREATE TABLE PROG (
    PROG_ID NUMBER(5) PRIMARY KEY,
    PROG_NAME VARCHAR2(30) NOT NULL,
    PROG_OFFICE VARCHAR2(4) NOT NULL,
    PROG_PHONE VARCHAR2(10)
);





/*
PROJECT
COLUMN          	TYPE    	    NN / U   PK   REF-TAB     PK-REF   CHECK

PROJECT_ID		NUMBER(6)		        Y
PROJ_NAME		VARCHAR2(40)    NN, U
COMPLET_DATE	DATE
TOTAL_COST		NUMBER(8,2)						  > 0
CLIENT_ID		NUMBER(5)	     NN	     CLIENT     CLIENT_ID
*/

    CREATE TABLE PROJECT(
    PROJECT_ID NUMBER(6),
    PROJ_NAME VARCHAR2(40) NOT NULL,
    COMPLETE_DATE DATE,
    TOTAL_COST NUMBER(8,2),
    CLIENT_ID NUMBER(5) NOT NULL,
    CONSTRAINT PK_PROJECT PRIMARY KEY(PROJECT_ID),
    CONSTRAINT UN_PROJECT UNIQUE(PROJ_NAME),
    CONSTRAINT FK_CLIENTPROJECT FOREIGN KEY(CLIENT_ID)
    REFERENCES CLIENT(CLIENT_ID)
);

/*
PROJECT_STAFF
COLUMN          	TYPE    	    NN / U   PK   REF-TAB     PK-REF	CHECK

PROG_ID		NUMBER(5)		        Y    PROG         PROG_ID
PROJECT_ID		NUMBER(6)		        Y    PROJECT   PROJECT_ID
WEEK_YEAR		CHAR(5)		        Y	
HOURS_WORKED	NUMBER(4,1)    NN						  > 0
*/

    CREATE TABLE PROJECT_STAFF(
    PROJ_ID NUMBER(5),
    PROJECT_ID NUMBER(6),
    WEEK_YEAR CHAR(5),
    HOURS_WORKED NUMBER(4,1) NOT NULL,
    CONSTRAINT PK_PROJECT_STAFF PRIMARY KEY(WEEK_YEAR, PROJ_ID),
    CONSTRAINT CK_HOURS_WORKED CHECK(HOURS_WORKED > 0),
    CONSTRAINT FK_PROJECT_STAFF_PROJECT FOREIGN KEY(PROJECT_ID)
    REFERENCES PROJECT(PROJECT_ID));