| 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 DATE, |
| 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, |
| 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; |