Saturday, August 7, 2010

Table creation scripts

create table course(
COURSE_NO  NUMBER(8, 0) NOT NULL ,
DESCRIPTION  VARCHAR2(50), --The full name of this course
COST  NUMBER(9,2), -- The dollar amount charged for
PREREQUISITE  NUMBER(8, 0), -- The ID number of the course
CREATED_BY  VARCHAR2(30) NOT NULL , --Audit column—indicates who inserted data
CREATED_DATE   DATE , --Audit column—indicates the
MODIFIED_BY   VARCHAR2(30) , ---Audit column—indicates who
MODIFIED_DATE  DATE -- Audit column—date of the
);

Create table SECTION --: Information for an individual section (class) of a particular course
(
SECTION_ID  NUMBER(8,0) NOT NULL , --The unique ID for a section
COURSE_NO   NUMBER(8,0) NOT NULL,  The course number for which his is a section
SECTION_NONUMBER(3)  NOT NULL , The individual section numb
START_DATE_TIME  DATE,  The date and time when thi
LOCATION   VARCHAR2(50),  The meeting room for t
INSTRUCTOR_ID  NUMBER(8,0)  NOT NULL,  --The ID number of the instructor
CAPACITY   NUMBER(3,0) , --The maximum number of students allowed in
CREATED_BY  VARCHAR2(30) NOT NULL , --Audit column—indicates who inserted data
CREATED_DATE   DATE , --Audit column—indicates the
MODIFIED_BY   VARCHAR2(30) , ---Audit column—indicates who
MODIFIED_DATE  DATE);



create table student(
STUDENT_ID  NUMBER(8,0) NOT NULL , -- A unique ID for the student
SALUTATION  VARCHAR2(5), -- This student’s title (Ms., Mr., Dr.)
FIRST_NAME  VARCHAR2(25) , --This student’s first name
LAST_NAME     VARCHAR2(25), -- This student’s last name
STREET_ADDRESS  VARCHAR2(50), -- This student’s street address
ZIP    VARCHAR2(5), -- This student’s zip code
PHONE  VARCHAR2(15), -- This student’s phone number, including
EMPLOYER   VARCHAR2(50), -- The name of the company where this
REGISTRATION_DATE  DATE  NOT NULL, --The date this student registered in the
CREATED_BY  VARCHAR2(30) NOT NULL , --Audit column—indicates who inserted data
CREATED_DATE   DATE , --Audit column—indicates the
MODIFIED_BY   VARCHAR2(30) , ---Audit column—indicates who
MODIFIED_DATE  DATE );



create table enrollment(
STUDENT_ID  NUMBER(8,0) NOT NULL, -- The ID for a student
SECTION_ID  NUMBER(8,0)  NOT NULL, --The ID for a section
ENROLL_DATE   DATE , The date this student registered for this section
FINAL_GRADE NUMBER(3,0) , --The final grade given to this student for all the
CREATED_BY  VARCHAR2(30) NOT NULL , --Audit column—indicates who inserted data
CREATED_DATE   DATE , --Audit column—indicates the
MODIFIED_BY   VARCHAR2(30) , ---Audit column—indicates who
MODIFIED_DATE  DATE );

create table instructor(
INSTRUCTOR_ID   NUMBER(8) NOT NULL, -- The unique ID for an instructor
SALUTATION  VARCHAR2(5), -- This instructor’s title (Mr., Ms., Dr., Rev.)
FIRST_NAME   VARCHAR2(25) , --This instructor’s first name
LAST_NAME   VARCHAR2(25), -- This instructor’s last name
STREET_ADDRESS  VARCHAR2(50), -- This instructor’s street address
ZIP   VARCHAR2(5), -- This instructor’s zip code
PHONE   VARCHAR2(15), -- This instructor’s phone number, including
CREATED_BY  VARCHAR2(30) NOT NULL , --Audit column—indicates who inserted data
CREATED_DATE   DATE , --Audit column—indicates the
MODIFIED_BY   VARCHAR2(30) , ---Audit column—indicates who
MODIFIED_DATE  DATE );


create table GRADE_TYPE --- : Lookup table of a grade type (code) and its description
( GRADE_TYPE_CODE  CHAR(2) not null, -- The unique code that identifies a
  DESCRIPTION   VARCHAR2(50) not null, --The description for this code (such as
   CREATED_BY  VARCHAR2(30) NOT NULL , --Audit column—indicates who inserted data
CREATED_DATE   DATE , --Audit column—indicates the
MODIFIED_BY   VARCHAR2(30) , ---Audit column—indicates who
MODIFIED_DATE  DATE );




create table 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
(
SECTION_Id nUMBER(8)  not null, --The ID for a section
GRADE_TYPE_CODE  CHAR(2) NOT NULL , --The code that identifies a category
NUMBER_PER_ SECTION  NUMBER(3)  NOT NULL, --How many of these grade types can
PERCENT_OF_FINAL_ GRADE NUMBER(3) NOT NULL, --The percentage that this category of
DROP_LOWEST  CHAR(1) NOT NULL, -- Is the lowest grade in this type
 CREATED_BY  VARCHAR2(30) NOT NULL , --Audit column—indicates who inserted data
CREATED_DATE   DATE , --Audit column—indicates the
MODIFIED_BY   VARCHAR2(30) , ---Audit column—indicates who
MODIFIED_DATE  DATE );




create table GRADE ---: The individual grades a student received for a particular section (class)
(
STUDENT_ID   NUMBER(8)  not null, --The ID for a student
SECTION_ID  NUMBER(8) NOT NULL, --The ID for a section
GRADE_TYPE_CODE  CHAR(2), NOT NULL , -- The code that identifies a category
GRADE_CODE_ OCCURRENCE   NUMBER(38) NOT NULL, --The sequence number of one
NUMERIC_GRADE  NUMBER(3) not null, --Numeric grade value (such a
COMMENTS  VARCHAR2(2000) , Instructor’s comments on this grade
 CREATED_BY  VARCHAR2(30) NOT NULL , --Audit column—indicates who inserted data
CREATED_DATE   DATE , --Audit column—indicates the
MODIFIED_BY   VARCHAR2(30) , ---Audit column—indicates who
MODIFIED_DATE  DATE );


create table GRADE_CONVERSION--: Converts a number grade to a letter grade
( LETTER_GRADE  VARCHAR(2) not null, --The unique grade as a letter (A, A–, B+, B, and so on)
GRADE_POINT    NUMBER(3,2) not null, --The number grade on a scale from 0 (F) to 4 (A)
MAX_GRADE   NUMBER(3) not null, --The highest grade number that corresponds to
MIN_GRADE NOT NULL NUMBER(3) not null, --The lowest grade number that corresponds to
 CREATED_BY  VARCHAR2(30) NOT NULL , --Audit column—indicates who inserted data
CREATED_DATE   DATE , --Audit column—indicates the
MODIFIED_BY   VARCHAR2(30) , ---Audit column—indicates who
MODIFIED_DATE  DATE );

No comments:

Post a Comment