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);
3.142.166.55