4.3 DDL


The following DDL is used to create the tables from the data model. The constraint names and types are seen in the data dictionary queries of Chapter 5.

Business rules not enforced with constraints are enforced with triggers and stored procedures in Chapters 6, 7, and 8.

 
 DROP TABLE students_courses; DROP TABLE professors; DROP TABLE courses; DROP TABLE parking_tickets; DROP TABLE student_vehicles; DROP TABLE students; DROP TABLE state_lookup; DROP TABLE major_lookup; -- SEQUENCE DROP SEQUENCE students_pk_seq; CREATE SEQUENCE students_pk_seq START WITH 201; -- -- STATE_LOOKUP -- CREATE TABLE state_lookup  (state      VARCHAR2(2)  NOT NULL,   state_desc VARCHAR2(30) NOT NULL) TABLESPACE student_data; -- -- MAJOR_LOOKUP -- CREATE TABLE major_lookup  (major      VARCHAR2(2)  NOT NULL,   major_desc VARCHAR2(15) NOT NULL) TABLESPACE student_data; -- -- STUDENTS -- CREATE TABLE students  (student_id      VARCHAR2(10) NOT NULL,   student_name    VARCHAR2(30) NOT NULL,   college_major   VARCHAR2(2)  NOT NULL,   status          VARCHAR2(15) NOT NULL,   state           VARCHAR2(2),   license_no      VARCHAR2(30)) TABLESPACE student_data; -- -- STUDENT_VEHICLES -- CREATE TABLE student_vehicles  (state           VARCHAR2(2)  NOT NULL,   tag_no          VARCHAR2(10) NOT NULL,   vehicle_desc    VARCHAR2(20) NOT NULL,   student_id      VARCHAR2(10) NOT NULL,   parking_sticker VARCHAR2(10) NOT NULL) TABLESPACE student_data; -- -- PARKING_TICKETS -- CREATE TABLE parking_tickets (ticket_no        VARCHAR2(10) NOT NULL,  amount           NUMBER(5,2)  NOT NULL,  state            VARCHAR2(2)  NOT NULL,  tag_no           VARCHAR2(10) NOT NULL) TABLESPACE student_data; -- -- COURSES -- CREATE TABLE courses  (course_name   VARCHAR2(10) NOT NULL,   course_desc   VARCHAR2(20) NOT NULL,   no_of_credits NUMBER(2,1)  NOT NULL) TABLESPACE student_data; -- -- PROFESSORS -- CREATE TABLE professors  (prof_name     VARCHAR2(10) NOT NULL,   specialty     VARCHAR2(20) NOT NULL,   hire_date     DATE         NOT NULL,   salary        NUMBER(7,2)  NOT NULL,   tenure        VARCHAR2(3)  NOT NULL,   department    VARCHAR2(10) NOT NULL) TABLESPACE student_data; -- -- STUDENTS_COURSES -- CREATE TABLE students_courses  (student_id    VARCHAR2(10) NOT NULL,   course_name   VARCHAR2(10) NOT NULL,   prof_name     VARCHAR2(10) NOT NULL) TABLESPACE student_data; ---------------------------------------- -- PRIMARY KEY CONSTRAINTS ---------------------------------------- ALTER TABLE state_lookup   ADD CONSTRAINT pk_state_lookup PRIMARY KEY (state)   USING INDEX TABLESPACE student_index; ALTER TABLE major_lookup   ADD CONSTRAINT pk_major_lookup PRIMARY KEY (major)   USING INDEX TABLESPACE student_index; ALTER TABLE students    ADD CONSTRAINT pk_students PRIMARY KEY (student_id)    USING INDEX TABLESPACE student_index; ALTER TABLE student_vehicles   ADD CONSTRAINT pk_student_vehicles   PRIMARY KEY (state, tag_no)   USING INDEX TABLESPACE student_index; ALTER TABLE parking_tickets   ADD CONSTRAINT pk_parking_tickets   PRIMARY KEY (ticket_no)   USING INDEX TABLESPACE student_index; ALTER TABLE courses   ADD CONSTRAINT pk_courses   PRIMARY KEY (course_name)   USING INDEX TABLESPACE student_index; ALTER TABLE professors   ADD CONSTRAINT pk_professors   PRIMARY KEY (prof_name)   USING INDEX TABLESPACE student_index; ALTER TABLE students_courses   ADD CONSTRAINT pk_students_courses   PRIMARY KEY (student_id, course_name)   USING INDEX TABLESPACE student_index; ---------------------------------------- -- UNIQUE and CHECK CONSTRAINTS ---------------------------------------- ALTER TABLE students    ADD CONSTRAINT uk_students_license    UNIQUE (state, license_no)    USING INDEX TABLESPACE student_index; ALTER TABLE students    ADD CONSTRAINT ck_students_st_lic    CHECK ((state IS NULL AND license_no IS NULL) OR           (state IS NOT NULL AND license_no is NOT NULL)); ALTER TABLE students     ADD CONSTRAINT ck_students_status     CHECK (status IN ('Degree','Certificate')); ALTER TABLE professors     ADD CONSTRAINT ck_professors_department     CHECK (department IN ('MATH','HIST','ENGL','SCIE')); ALTER TABLE professors     ADD CONSTRAINT ck_professors_tenure     CHECK (tenure IN ('YES','NO')); ALTER TABLE professors     ADD CONSTRAINT ck_professors_salary     CHECK (salary < 30000); ---------------------------------------- -- FOREIGN KEY CONSTRAINTS ---------------------------------------- -- students references state_lookup -- ALTER TABLE students   ADD CONSTRAINT fk_students_state   FOREIGN KEY (state) REFERENCES state_lookup; -- -- students references major_lookup -- ALTER TABLE students   ADD CONSTRAINT fk_students_college_major   FOREIGN KEY (college_major) REFERENCES major_lookup; -- -- student_vehicles references state_lookup -- ALTER TABLE student_vehicles   ADD CONSTRAINT fk_student_vehicles_state   FOREIGN KEY (state) REFERENCES state_lookup; -- -- student_vehicles references students -- ALTER TABLE student_vehicles   ADD CONSTRAINT fk_student_vehicles_stud   FOREIGN KEY (student_id) REFERENCES students; -- -- parking_tickets references students_vehicles -- ALTER TABLE parking_tickets   ADD CONSTRAINT fk_parking_tickets_state_tag   FOREIGN KEY (state, tag_no) REFERENCES student_vehicles; -- -- students_courses references students -- ALTER TABLE students_courses   ADD CONSTRAINT fk_students_courses_st_id   FOREIGN KEY (student_id)   REFERENCES students (student_id); -- -- students_courses references courses -- ALTER TABLE students_courses   ADD CONSTRAINT fk_students_courses_course   FOREIGN KEY (course_name)   REFERENCES courses (course_name); -- -- students_courses references professors -- ALTER TABLE students_courses   ADD CONSTRAINT fk_students_courses_prof   FOREIGN KEY (prof_name)   REFERENCES professors (prof_name); 


Programming Oracle Triggers and Stored Procedures
Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)
ISBN: 0130850330
EAN: 2147483647
Year: 2003
Pages: 111
Authors: Kevin Owens

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