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

drop table STUD_COURSE
drop table COURSE
drop table STUDENT
drop table DEPT
drop table ACCOUNT
drop table ADDRESS
go

create table ADDRESS (
    ADDRESSID NUMERIC(38),
    STREET    VARCHAR(255),
    CODE      NUMERIC(38),
    CITY      VARCHAR(255),
    STATE     VARCHAR(255),
    CONSTRAINT ADDR_CT PRIMARY KEY (ADDRESSID)
)

create table ACCOUNT (
    ACCOUNTID VARCHAR(255),
    FEESPAID  NUMERIC(38,2),
    FEESDUE   NUMERIC(38,2),
    DUEDATE   DATETIME,
    CONSTRAINT ACC_CT PRIMARY KEY (ACCOUNTID)
)

create table DEPT (
    DEPTID   NUMERIC(38),
    DEPTNAME VARCHAR(255),
    CONSTRAINT DEPT_CT PRIMARY KEY (DEPTID)
)

create table STUDENT (
    STUDENTID   NUMERIC(38),
    STUDENTNAME VARCHAR(255),
    DEPTID      NUMERIC(38),
    ADDRESSID   NUMERIC(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   NUMERIC(38),
    DEPTID     NUMERIC(38),
    COURSENAME VARCHAR(255),
    SYLABUS    IMAGE,
    CONSTRAINT COURSE_CT PRIMARY KEY (COURSEID),
    FOREIGN KEY (DEPTID) REFERENCES DEPT (DEPTID)
)

create table STUD_COURSE (
    COURSEID  NUMERIC(38),
    STUDENTID NUMERIC(38),
    CONSTRAINT STCO_CT PRIMARY KEY (COURSEID, STUDENTID),
    FOREIGN KEY (COURSEID) REFERENCES COURSE (COURSEID),
    FOREIGN KEY (STUDENTID) REFERENCES STUDENT (STUDENTID)
)

go