Table and Column Descriptions

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Appendix C.  Student Database Schema


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 columnindicates user who inserted data

CREATED_DATE

NOT NULL

DATE

Audit columnindicates date of insert

MODIFIED_BY

NOT NULL

VARCHAR2(30)

Audit columnindicates who made last update

MODIFIED_DATE

NOT NULL

DATE

Audit columndate 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 columnindicates user who inserted data

CREATED_DATE

NOT NULL

DATE

Audit columnindicates date of insert

MODIFIED_BY

NOT NULL

VARCHAR2(30)

Audit columnindicates who made last update

MODIFIED_DATE

NOT NULL

DATE

Audit columndate 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 columnindicates user who inserted data

CREATED_DATE

NOT NULL

DATE

Audit columnindicates date of insert

MODIFIED_BY

NOT NULL

VARCHAR2(30)

Audit columnindicates who made last update

MODIFIED_DATE

NOT NULL

DATE

Audit columndate 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 columnindicates user who inserted data

CREATED_DATE

NOT NULL

DATE

Audit columnindicates date of insert

MODIFIED_BY

NOT NULL

VARCHAR2(30)

Audit columnindicates who made last update

MODIFIED_DATE

NOT NULL

DATE

Audit columndate 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 columnindicates user who inserted data

CREATED_DATE

NOT NULL

DATE

Audit columnindicates date of insert

MODIFIED_BY

NOT NULL

VARCHAR2(30)

Audit columnindicates who made last update

MODIFIED_DATE

NOT NULL

DATE

Audit columndate 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 columnindicates user who inserted data

CREATED_DATE

NOT NULL

DATE

Audit columnindicates date of insert

MODIFIED_BY

NOT NULL

VARCHAR2(30)

Audit columnindicates who made last update

MODIFIED_DATE

NOT NULL

DATE

Audit columndate 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 columnindicates user who inserted data

CREATED_DATE

NOT NULL

DATE

Audit columnindicates date of insert

MODIFIED_BY

NOT NULL

VARCHAR2(30)

Audit columnindicates who made last update

MODIFIED_DATE

NOT NULL

DATE

Audit columndate 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 columnindicates user who inserted data

CREATED_DATE

NOT NULL

DATE

Audit columnindicates date of insert

MODIFIED_BY

NOT NULL

VARCHAR2(30)

Audit columnindicates who made last update

MODIFIED_DATE

NOT NULL

DATE

Audit columndate 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 columnindicates user who inserted data

CREATED_DATE

NOT NULL

DATE

Audit columnindicates date of insert

MODIFIED_BY

NOT NULL

VARCHAR2(30)

Audit columnindicates who made last update

MODIFIED_DATE

NOT NULL

DATE

Audit columndate 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 columnindicates user who inserted data

CREATED_DATE

NOT NULL

DATE

Audit columnindicates date of insert

MODIFIED_BY

NOT NULL

VARCHAR2(30)

Audit columnindicates who last made update

MODIFIED_DATE

NOT NULL

DATE

Audit columndate of last update

graphics/apcfig01.jpg


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net