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

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


create table address (
    addressId DECIMAL(32),
    street VARCHAR(127),
    code DECIMAL(32),
    city VARCHAR(127),
    state VARCHAR(127),
    primary key (addressId)
);

create table account (
    accountId VARCHAR(127),
    feesPaid DECIMAL(32,2),
    feesDue DECIMAL(32,2),
    dueDate DATE,
    primary key (accountId)
);

create table dept (
    deptId DECIMAL(32),
    deptName VARCHAR(127),
    primary key (deptId)
);

create table student (
    studentId DECIMAL(32),
    studentName VARCHAR(127),
    deptId DECIMAL(32),
    addressId DECIMAL(32),
    accountId VARCHAR(127),
    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(32),
    deptId DECIMAL(32),
    courseName VARCHAR(127),
    sylabus BYTE,
    primary key (courseId),
    FOREIGN KEY (deptId) REFERENCES dept (deptId)
);

create table stud_course (
    courseId DECIMAL(32),
    studentId DECIMAL(32),
    primary key (courseId, studentId),
    FOREIGN KEY (courseId) REFERENCES course (courseId),
    FOREIGN KEY (studentId) REFERENCES student (studentId)
);

exit;