COURSE: Information for a course | Column Name | Null | Type | Comments | | COURSE_NO | NOT NULL | NUMBER(8, 0) | The unique course number | | DESCRIPTION | NULL | VARCHAR2(50) | The full name for this course | | COST | NULL | NUMBER(9,2) | The dollar amount charged for enrollment in this course | | PREREQUISITE | NULL | NUMBER(8, 0) | The ID number of the course that must be taken as a prerequisite to this course | | CREATED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates user who inserted data | | CREATED_DATE | NOT NULL | DATE | Audit column ” indicates date of insert | | MODIFIED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates who made last update | | MODIFIED_DATE | NOT NULL | DATE | Audit column ” date of last update | SECTION: Information for an individual section (class) of a particular course | Column Name | Null | Type | Comments | | SECTION_ID | NOT NULL | NUMBER(8,0) | The unique ID for a section | | COURSE_NO | NOT NULL | NUMBER(8,0) | The course number for which this is a section | | SECTION_NO | NOT NULL | NUMBER(3) | The individual section number within this course | | START_DATE_TIME | NULL | DATE | The date and time on which this section meets | | LOCATION | NULL | VARCHAR2(50) | The meeting room for the section | | INSTRUCTOR_ID | NOT NULL | NUMBER(8,0) | The ID number of the instructor who teaches this section | | CAPACITY | NULL | NUMBER(3,0) | The maximum number of students allowed in this section | | CREATED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates user who inserted data | | CREATED_DATE | NOT NULL | DATE | Audit column ” indicates date of insert | | MODIFIED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates who made last update | | MODIFIED_DATE | NOT NULL | DATE | Audit column ” date of last update | STUDENT: Profile information for a student | Column Name | Null | Type | Comments | | STUDENT_ID | NOT NULL | NUMBER(8,0) | The unique ID for a student | | SALUTATION | NULL | VARCHAR2(5) | This student's title (Ms., Mr., Dr., etc.) | | FIRST_NAME | NULL | VARCHAR2(25) | This student's first name | | LAST_NAME | NOT NULL | VARCHAR2(25) | This student's last name | | STREET_ADDRESS | NULL | VARCHAR2(50) | This student's street address | | ZIP | NOT NULL | VARCHAR2(5) | The postal zipcode for this student | | PHONE | NULL | VARCHAR2(15) | The phone number for this student, including area code | | EMPLOYER | NULL | VARCHAR2(50) | The name of the company where this student is employed | | REGISTRATION_DATE | NOT NULL | DATE | The date this student registered in the program | | CREATED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates user who inserted data | | CREATED_DATE | NOT NULL | DATE | Audit column ” indicates date of insert | | MODIFIED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates who made last update | | MODIFIED_DATE | NOT NULL | DATE | Audit column ” date of last update | ENROLLMENT: Information for a student registered for a particular section of a particular course (class) | Column Name | Null | Type | Comments | | STUDENT_ID | NOT NULL | NUMBER(8,0) | The ID for a student | | SECTION_ID | NOT NULL | NUMBER(8,0) | The ID for a section | | ENROLL_DATE | NOT NULL | DATE | The date this student registered for this section | | FINAL_GRADE | NULL | NUMBER(3,0) | The final grade given to this student for all work in this section (class) | | CREATED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates user who inserted data | | CREATED_DATE | NOT NULL | DATE | Audit column ” indicates date of insert | | MODIFIED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates who made last update | | MODIFIED_DATE | NOT NULL | DATE | Audit column ” date of last update | INSTRUCTOR: Profile information for an instructor | Column Name | Null | Type | Comments | | INSTRUCTOR_ID | NOT NULL | NUMBER(8) | The unique ID for an instructor | | SALUTATION | NULL | VARCHAR2(5) | This instructor's title (Mr., Ms., Dr., Rev., etc.) | | FIRST_NAME | NULL | VARCHAR2(25) | This instructor's first name | | LAST_NAME | NULL | VARCHAR2(25) | This instructor's last name | | STREET_ADDRESS | NULL | VARCHAR2(50) | This instructor's street address | | ZIP | NULL | VARCHAR2(5) | The postal zipcode for this instructor | | PHONE | NULL | VARCHAR2(15) | The phone number for this instructor, including area code | | CREATED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates user who inserted data | | CREATED_DATE | NOT NULL | DATE | Audit column ” indicates date of insert | | MODIFIED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates who made last update | | MODIFIED_DATE | NOT NULL | DATE | Audit column ” date of last update | ZIPCODE: City, state, and zipcode information | Column Name | Null | Type | Comments | | ZIP | NOT NULL | VARCHAR2(5) | The zipcode number, unique for a city and state | | CITY | NULL | VARCHAR2(25) | The city name for this zipcode | | STATE | NULL | VARCHAR2(2) | The postal abbreviation for the U.S. state | | CREATED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates user who inserted data | | CREATED_DATE | NOT NULL | DATE | Audit column ” indicates date of insert | | MODIFIED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates who made last update | | MODIFIED_DATE | NOT NULL | DATE | Audit column ” date of last update | GRADE_TYPE: Lookup table of a grade type (code) and its description | Column Name | Null | Type | Comments | | GRADE_TYPE_CODE | NOT NULL | CHAR(2) | The unique code that identifies a category of grade (e.g., MT, HW) | | DESCRIPTION | NOT NULL | VARCHAR2(50) | The description for this code (e.g., Midterm, Homework) | | CREATED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates user who inserted data | | CREATED_DATE | NOT NULL | DATE | Audit column ” indicates date of insert | | MODIFIED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates who made last update | | MODIFIED_DATE | NOT NULL | DATE | Audit column ” date of last update | GRADE_TYPE_WEIGHT: Information on how the final grade for a particular section is computed; for example, the midterm constitutes 50%, the quiz 10%, and the final examination 40% of the final grade | Column Name | Null | Type | Comments | | SECTION_ID | NOT NULL | NUMBER(8) | The ID for a section | | GRADE_TYPE_CODE | NOT NULL | CHAR(2) | The code which identifies a category of grade | | NUMBER_PER_SECTION | NOT NULL | NUMBER(3) | How many of these grade types can be used in this section (i.e., there may be three quizzes) | | PERCENT_OF_FINAL_GRADE | NOT NULL | NUMBER(3) | The percentage this category of grade contributes to the final grade | | DROP_LOWEST | NOT NULL | CHAR(1) | Is the lowest grade in this type removed when determining the final grade? (Y/N) | | CREATED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates user who inserted data | | CREATED_DATE | NOT NULL | DATE | Audit column ” indicates date of insert | | MODIFIED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates who made last update | | MODIFIED_DATE | NOT NULL | DATE | Audit column ” date of last update | GRADE: The individual grades a student received for a particular section (class) | Column Name | Null | Type | Comments | | STUDENT_ID | NOT NULL | NUMBER(8) | The ID for a student | | SECTION_ID | NOT NULL | NUMBER(8) | The ID for a section | | GRADE_TYPE_CODE | NOT NULL | CHAR(2) | The code that identifies a category of grade | | GRADE_CODE_OCCURRENCE | NOT NULL | NUMBER(38) | The sequence number of one grade type for one section. For example, there could be multiple assignments numbered 1, 2, 3, etc. | | NUMERIC_GRADE | NOT NULL | NUMBER(3) | Numeric grade value (e.g., 70, 75) | | COMMENTS | NULL | VARCHAR2(2000) | Instructor's comments on this grade | | CREATED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates user who inserted data | | CREATED_DATE | NOT NULL | DATE | Audit column ” indicates date of insert | | MODIFIED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates who made last update | | MODIFIED_DATE | NOT NULL | DATE | Audit column ” date of last update | GRADE_CONVERSION: Converts a number grade to a letter grade | Column Name | Null | Type | Comments | | LETTER_GRADE | NOT NULL | VARCHAR(2) | The unique grade as a letter (A, A-, B, B+, etc.) | | GRADE_POINT | NOT NULL | NUMBER(3,2) | The number grade on a scale from 0 (F) to 4 (A) | | MAX_GRADE | NOT NULL | NUMBER(3) | The highest grade number that corresponds to this letter grade | | MIN_GRADE | NOT NULL | NUMBER(3) | The lowest grade number that corresponds to this letter grade | | CREATED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates user who inserted data | | CREATED_DATE | NOT NULL | DATE | Audit column ” indicates date of insert | | MODIFIED_BY | NOT NULL | VARCHAR2(30) | Audit column ” indicates who last made update | | MODIFIED_DATE | NOT NULL | DATE | Audit column ” date of last update |   |
![Oracle PL[s]SQL by Example](/icons/blank_book.jpg "Oracle PL[s]SQL by Example"){kind=icon}