appserver/tests/v2-tests/appserv-tests/devtests/cmp/collegeapp/sql/CollegeApp_psql.sql

DROP TABLE STUD_COURSE cascade;
DROP TABLE COURSE cascade;
DROP TABLE STUDENT cascade;
DROP TABLE DEPT cascade;
DROP TABLE ACCOUNT cascade;
DROP TABLE ADDRESS cascade;


CREATE TABLE ADDRESS (
    ADDRESSID DECIMAL(38),
    STREET VARCHAR(255),
    CODE DECIMAL(38),
    CITY VARCHAR(255),
    STATE VARCHAR(255),
    CONSTRAINT ADDR_CT PRIMARY KEY (ADDRESSID)
);

CREATE TABLE ACCOUNT (
    ACCOUNTID VARCHAR(255),
    FEESPAID DECIMAL(38,2),
    FEESDUE DECIMAL(38,2),
    DUEDATE TIMESTAMP,
    CONSTRAINT ACC_CT PRIMARY KEY (ACCOUNTID)
);

CREATE TABLE DEPT (
    DEPTID DECIMAL(38),
    DEPTNAME VARCHAR(255),
    CONSTRAINT DEPT_CT PRIMARY KEY (DEPTID)
);

CREATE TABLE STUDENT (
    STUDENTID DECIMAL(38),
    STUDENTNAME VARCHAR(255),
    DEPTID DECIMAL(38),
    ADDRESSID DECIMAL(38),
    ACCOUNTID VARCHAR(255),
    CONSTRAINT st_CT PRIMARY KEY (STUDENTID),
    FOREIGN KEY (DEPTID) REFERENCES DEPT (DEPTID),
    FOREIGN KEY (ADDRESSID) REFERENCES ADDRESS (ADDRESSID),
    FOREIGN KEY (ACCOUNTID) REFERENCES ACCOUNT (ACCOUNTID)
);

CREATE TABLE COURSE (
    COURSEID DECIMAL(38),
    DEPTID DECIMAL(38),
    COURSENAME VARCHAR(255),
    SYLABUS BYTEA,
    CONSTRAINT COURSE_CT PRIMARY KEY (COURSEID),
    FOREIGN KEY (DEPTID) REFERENCES DEPT (DEPTID)
);

CREATE TABLE STUD_COURSE (
    COURSEID DECIMAL(38),
    STUDENTID DECIMAL(38),
    CONSTRAINT STCO_CT PRIMARY KEY (COURSEID, STUDENTID),
    FOREIGN KEY (COURSEID) REFERENCES COURSE (COURSEID),
    FOREIGN KEY (STUDENTID) REFERENCES STUDENT (STUDENTID)
);

exit;