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

drop table stud_course;
drop table course;
drop table student;
drop table dept;
drop table account;
drop table address;


create table address (
    addressId NUMBER(38),
    street VARCHAR2(255),
    code NUMBER(38),
    city VARCHAR2(255),
    state VARCHAR2(255),
    constraint addr_ct primary key (addressId)
);

create table account (
    accountId VARCHAR2(255),
    feesPaid NUMBER(38,2),
    feesDue NUMBER(38,2),
    dueDate TIMESTAMP,
    constraint acc_ct primary key (accountId)
);

create table dept (
    deptId NUMBER(38),
    deptName VARCHAR2(255),
    constraint dept_ct primary key (deptId)
);

create table student (
    studentId NUMBER(38),
    studentName VARCHAR2(255),
    deptId NUMBER(38),
    addressId NUMBER(38),
    accountId VARCHAR2(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 NUMBER(38),
    deptId NUMBER(38),
    courseName VARCHAR2(255),
    sylabus BLOB (10k),
    constraint course_ct primary key (courseId),
    FOREIGN KEY (deptId) REFERENCES dept (deptId)
);

create table stud_course (
    courseId NUMBER(38),
    studentId NUMBER(38),
    constraint stco_ct primary key (courseId, studentId),
    FOREIGN KEY (courseId) REFERENCES course (courseId),
    FOREIGN KEY (studentId) REFERENCES student (studentId)
);

exit;