CS275 - Wk 3 - Combined Lab 3

gistfile1.txt

DROP TABLE STUDENT CASCADE CONSTRAINTS;
DROP TABLE DORM_ROOM CASCADE CONSTRAINTS;

CREATE TABLE STUDENT
(STUDENT_ID     	number(8) PRIMARY KEY,
STUDENT_FIRST_NAME	varchar(25) NOT NULL,
STUDENT_LAST_NAME	varchar(50) NOT NULL,
STUDENT_TYPE	varchar(1) NOT NULL,
FK_DORM_ROOM_ID	varchar(8));

CREATE TABLE DORM_ROOM
(DORM_ROOM_ID	varchar(8) PRIMARY KEY,
BUILDING_ID	varchar(8) NOT NULL,
COST	decimal(8,2));

ALTER TABLE STUDENT
ADD CONSTRAINT FK_DORM_ROOM
FOREIGN KEY(FK_DORM_ROOM_ID)
REFERENCES DORM_ROOM(DORM_ROOM_ID);

INSERT INTO DORM_ROOM
(DORM_ROOM_ID, BUILDING_ID, COST)
VALUES
('ABC123','Searcy',125.25);

INSERT INTO DORM_ROOM
(DORM_ROOM_ID, BUILDING_ID, COST)
VALUES
('DEF234','Wood',75.89);

INSERT INTO DORM_ROOM
(DORM_ROOM_ID, BUILDING_ID, COST)
VALUES
('GHI567','Columbia',145.15);

INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, STUDENT_TYPE, FK_DORM_ROOM_ID)
VALUES
(12345678,'James','Joyce','F','ABC123');

INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, STUDENT_TYPE, FK_DORM_ROOM_ID)
VALUES
(23456789,'Ernest','Hemingway','S','DEF234');

INSERT INTO STUDENT
(STUDENT_ID, STUDENT_FIRST_NAME, STUDENT_LAST_NAME, STUDENT_TYPE, FK_DORM_ROOM_ID)
VALUES
(34567890,'Zelda','Fitzgerald','J',NULL);

DROP TABLE TEXTBOOK CASCADE CONSTRAINTS;


CREATE TABLE TEXTBOOK
(TEXTBOOK_ID	varchar(18) PRIMARY KEY,
TEXTBOOK_TITLE	varchar(50) NOT NULL,
FK_STUDENT_ID	number(8));

ALTER TABLE TEXTBOOK
ADD CONSTRAINT FK_STUDENT
FOREIGN KEY(FK_STUDENT_ID)
REFERENCES STUDENT(STUDENT_ID);

INSERT INTO TEXTBOOK
(TEXTBOOK_ID, TEXTBOOK_TITLE, FK_STUDENT_ID)
VALUES
('123-45-6789','Database Design and Implementation',12345678);

INSERT INTO TEXTBOOK
(TEXTBOOK_ID, TEXTBOOK_TITLE, FK_STUDENT_ID)
VALUES
('345-67-8901','Systems Analysis and Design',23456789);

INSERT INTO TEXTBOOK
(TEXTBOOK_ID, TEXTBOOK_TITLE, FK_STUDENT_ID)
VALUES
('678-90-1234','Refreshing Calculus',NULL);