120 lines
2.5 KiB
SQL
120 lines
2.5 KiB
SQL
CREATE TABLE INSTRUCTOR
|
|
(INSTRUCTOR_ID NUMBER(8,0)
|
|
,SALUTATION VARCHAR2(5)
|
|
,FIRST_NAME VARCHAR2(25)
|
|
,LAST_NAME VARCHAR2(25)
|
|
,STREET_ADDRESS VARCHAR2(50)
|
|
,ZIP VARCHAR2(5)
|
|
,PHONE VARCHAR2(15)
|
|
,CREATED_BY VARCHAR2(30)
|
|
,CREATED_DATE DATE
|
|
,MODIFIED_BY VARCHAR2(30)
|
|
,MODIFIED_DATE DATE
|
|
);
|
|
|
|
CREATE TABLE GRADE
|
|
(STUDENT_ID NUMBER(8,0)
|
|
,SECTION_ID NUMBER(8,0)
|
|
,GRADE_TYPE_CODE CHAR(2)
|
|
,GRADE_CODE_OCCURRENCE NUMBER(38,0)
|
|
,NUMERIC_GRADE NUMBER(3,0) DEFAULT 0
|
|
,COMMENTS VARCHAR2(2000)
|
|
,CREATED_BY VARCHAR2(30)
|
|
,CREATED_DATE DATE
|
|
,MODIFIED_BY VARCHAR2(30)
|
|
,MODIFIED_DATE DATE
|
|
);
|
|
|
|
CREATE TABLE GRADE_TYPE
|
|
(GRADE_TYPE_CODE CHAR(2)
|
|
,DESCRIPTION VARCHAR2(50)
|
|
,CREATED_BY VARCHAR2(30)
|
|
,CREATED_DATE DATE
|
|
,MODIFIED_BY VARCHAR2(30)
|
|
,MODIFIED_DATE DATE
|
|
);
|
|
|
|
CREATE TABLE GRADE_CONVERSION
|
|
(LETTER_GRADE VARCHAR2(2)
|
|
,GRADE_POINT NUMBER(3,2) DEFAULT 0
|
|
,MAX_GRADE NUMBER(3,0)
|
|
,MIN_GRADE NUMBER(3,0)
|
|
,CREATED_BY VARCHAR2(30)
|
|
,CREATED_DATE DATE
|
|
,MODIFIED_BY VARCHAR2(30)
|
|
,MODIFIED_DATE DATE
|
|
);
|
|
|
|
CREATE TABLE GRADE_TYPE_WEIGHT
|
|
(SECTION_ID NUMBER(8,0)
|
|
,GRADE_TYPE_CODE CHAR(2)
|
|
,NUMBER_PER_SECTION NUMBER(3,0)
|
|
,PERCENT_OF_FINAL_GRADE NUMBER(3,0)
|
|
,DROP_LOWEST CHAR(1)
|
|
,CREATED_BY VARCHAR2(30)
|
|
,CREATED_DATE DATE
|
|
,MODIFIED_BY VARCHAR2(30)
|
|
,MODIFIED_DATE DATE
|
|
);
|
|
|
|
CREATE TABLE SECTION
|
|
(SECTION_ID NUMBER(8,0)
|
|
,COURSE_NO NUMBER(8,0)
|
|
,SECTION_NO NUMBER(3,0)
|
|
,START_DATE_TIME DATE
|
|
,LOCATION VARCHAR2(50)
|
|
,INSTRUCTOR_ID NUMBER(8,0)
|
|
,CAPACITY NUMBER(3,0)
|
|
,CREATED_BY VARCHAR2(30)
|
|
,CREATED_DATE DATE
|
|
,MODIFIED_BY VARCHAR2(30)
|
|
,MODIFIED_DATE DATE
|
|
);
|
|
|
|
CREATE TABLE COURSE
|
|
(COURSE_NO NUMBER(8,0)
|
|
,DESCRIPTION VARCHAR2(50)
|
|
,COST NUMBER(9,2)
|
|
,PREREQUISITE NUMBER(8,0)
|
|
,CREATED_BY VARCHAR2(30)
|
|
,CREATED_DATE DATE
|
|
,MODIFIED_BY VARCHAR2(30)
|
|
,MODIFIED_DATE DATE
|
|
);
|
|
|
|
CREATE TABLE ENROLLMENT
|
|
(STUDENT_ID NUMBER(8,0)
|
|
,SECTION_ID NUMBER(8,0)
|
|
,ENROLL_DATE DATE
|
|
,FINAL_GRADE NUMBER(3,0)
|
|
,CREATED_BY VARCHAR2(30)
|
|
,CREATED_DATE DATE
|
|
,MODIFIED_BY VARCHAR2(30)
|
|
,MODIFIED_DATE DATE
|
|
);
|
|
|
|
CREATE TABLE STUDENT
|
|
(STUDENT_ID NUMBER(8,0)
|
|
,SALUTATION VARCHAR2(5)
|
|
,FIRST_NAME VARCHAR2(25)
|
|
,LAST_NAME VARCHAR2(25)
|
|
,STREET_ADDRESS VARCHAR2(50)
|
|
,ZIP VARCHAR2(5)
|
|
,PHONE VARCHAR2(15)
|
|
,EMPLOYER VARCHAR2(50)
|
|
,REGISTRATION_DATE DATE
|
|
,CREATED_BY VARCHAR2(30)
|
|
,CREATED_DATE DATE
|
|
,MODIFIED_BY VARCHAR2(30)
|
|
,MODIFIED_DATE DATE
|
|
);
|
|
|
|
CREATE TABLE ZIPCODE
|
|
(ZIP VARCHAR2(5)
|
|
,CITY VARCHAR2(25)
|
|
,STATE VARCHAR2(2)
|
|
,CREATED_BY VARCHAR2(30)
|
|
,CREATED_DATE DATE
|
|
,MODIFIED_BY VARCHAR2(30)
|
|
,MODIFIED_DATE DATE
|
|
); |