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

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


create table address (
    addressId NUMERIC(30) NOT NULL,
    street VARCHAR(255),
    code NUMERIC(30),
    city VARCHAR(255),
    state VARCHAR(255),
    constraint addr_ct primary key (addressId)
);

create table account (
    accountId VARCHAR(255) NOT NULL,
    feesPaid NUMERIC(30,2),
    feesDue NUMERIC(30,2),
    dueDate DATE,
    constraint acc_ct primary key (accountId)
);

create table dept (
    deptId NUMERIC(30) NOT NULL,
    deptName VARCHAR(255),
    constraint dept_ct primary key (deptId)
);

create table student (
    studentId NUMERIC(30) NOT NULL,
    studentName VARCHAR(255),
    deptId NUMERIC(30),
    addressId NUMERIC(30),
    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(30) NOT NULL,
    deptId NUMERIC(30),
    courseName VARCHAR(255),
    sylabus BLOB,
    constraint course_ct primary key (courseId),
    FOREIGN KEY (deptId) REFERENCES dept (deptId)
);

create table stud_course (
    courseId NUMERIC(30) NOT NULL,
    studentId NUMERIC(30) NOT NULL,
    constraint stco_ct primary key (courseId, studentId),
    FOREIGN KEY (courseId) REFERENCES course (courseId),
    FOREIGN KEY (studentId) REFERENCES student (studentId)
);