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