Assignment Gain understanding of execution plans and index c

Assignment

Gain understanding of execution plans and index creation. Learn how to use the Autotrace statement, create index statement and how to read the autotrace results.

Tasks 1. Using the student, advisor and faculty tables create a select query that outputs all students for a specific advisor. Generate the execution plan by first setting Autotrace on. Create an index that allows for a better join between the two tables. Again generate and display the revised execution plan using Autotrace.

The commands for using Autotrace are as follows: SET AUTOTRACE ON; SET AUTOTRACE OFF;

Format: Submit the code to create the tables, insert data, run the select, show the execution plan, create an index and then generate and show the execution plan again. You must submit the code as scripts that the instructor can run from an SQL prompt on an Oracle DBMS. Your submission must include a description of the scripts, their actions, and the order in which they should be run. Your code must not have any \"side effects\" (that is, it must not do anything not listed in the assignment description, and all of its actions must be described in the program header).

These are my tables:

CREATE TABLE faculty ( facultyid NUMBER(10) NOT NULL PRIMARY KEY, fname varchar2(20) NOT NULL , lname varchar2(20) NOT NULL );

CREATE TABLE advisor – Associative table between faculty and student ( Studentid NUMBER(10), facultyid NUMBER(10) , Advisor_Type varchar2(20) );

CREATE TABLE student ( studentid NUMBER(10) NOT NULL PRIMARY KEY , firstname varchar2(15) NOT NULL , lastname varchar2(30) NOT NULL , streetaddress varchar2(30) , state char(2) , zip varchar2(9) , enrollid NUMBER(10) );

Solution

--DROP TABLES
DROP TABLE ADVISOR;
DROP TABLE STUDENT;
DROP TABLE FACULTY;

--CREATE TABLES

CREATE TABLE STUDENT
(   STUDENTID NUMBER(10) NOT NULL ENABLE,
   FIRSTNAME VARCHAR2(15) NOT NULL ENABLE,
   LASTNAME VARCHAR2(30) NOT NULL ENABLE,
   STREETADDRESS VARCHAR2(30),
   STATE CHAR(2),
   ZIP VARCHAR2(9),
   ENROLLID NUMBER(10),
   PRIMARY KEY (STUDENTID) ENABLE
)
;
CREATE TABLE FACULTY
(   FACULTYID NUMBER(10) NOT NULL ENABLE,
   FNAME VARCHAR2(20) NOT NULL ENABLE,
   LNAME VARCHAR2(20) NOT NULL ENABLE,
   PRIMARY KEY (FACULTYID) ENABLE
)
;

CREATE TABLE ADVISOR
(   STUDENTID NUMBER(10),
   FACULTYID NUMBER(10),
   ADVISOR_TYPE VARCHAR2(20)
)
;

--INSERT VALUES INTO STUDENT

INSERT INTO STUDENT VALUES( 1, \'Lousia\', \'Alcott\', \'Wind Ave\', \'NY\' , \'13202\', 4444) ;
INSERT INTO STUDENT VALUES( 2, \'Isaac\', \'Asimov\', \'Rose Lane\', \'NY\', \'13213\' ,4445) ;
INSERT INTO STUDENT VALUES( 3, \'Emily\' ,\'Bronte\', \'River Street\', \'NY\', \'13211\', 4446);
INSERT INTO STUDENT VALUES( 4, \'Thomas\', \'Eliot\', \'Brook Street\', \'NY\', \'13312\', 4447);
INSERT INTO STUDENT VALUES( 5, \'Kurt\', \'Vonnegut\',\'Stream Street\', \'NY\', \'13222\', 4448);

--INSERT VALUES INTO FACULTY

INSERT INTO FACULTY VALUES(500,\'Marth\',\'Graham\');
INSERT INTO FACULTY VALUES(501,\'Mikhail\',\'Baryshinokov\');
INSERT INTO FACULTY VALUES(502,\'Jerome\', \'Robbins\');
INSERT INTO FACULTY VALUES(503,\'Arthur\', \'Mitchell\');
INSERT INTO FACULTY VALUES(504,\'Kir\',\'Jooss\');

--INSERT VALUES INTO ADVISOR

INSERT INTO ADVISOR VALUES( 1, 500, \'Academic Advisor\');
INSERT INTO ADVISOR VALUES( 2, 501, \'Academic Advisor\');
INSERT INTO ADVISOR VALUES( 3, 502, \'Academic Advisor\');
INSERT INTO ADVISOR VALUES( 4, 503, \'Academic Advisor\');
INSERT INTO ADVISOR VALUES( 5, 504, \'Academic Advisor\');
INSERT INTO ADVISOR VALUES( 1, 502, \'Thesis Advisor\');
INSERT INTO ADVISOR VALUES( 2, 504, \'Thesis Advisor\');
INSERT INTO ADVISOR VALUES( 3, 503, \'Thesis Advisor\');
INSERT INTO ADVISOR VALUES( 4, 501, \'Thesis Advisor\');
INSERT INTO ADVISOR VALUES( 5, 500, \'Thesis Advisor\');

COMMIT;

SET AUTOTRACE ON;

--QUERY : All students under a specific advisor
SELECT FACULTY.FACULTYID,FACULTY.FNAME,FACULTY.LNAME,STUDENT.STUDENTID,FIRSTNAME,LASTNAME
FROM STUDENT,FACULTY,ADVISOR
WHERE (STUDENT.STUDENTID = ADVISOR.STUDENTID) AND
(FACULTY.FACULTYID = ADVISOR.FACULTYID)
ORDER BY FACULTY.FACULTYID
;

CREATE UNIQUE INDEX index_name
ON ADVISOR(STUDENTID,FACULTYID);

SET AUTOTRACE ON;

--QUERY : All students under a specific advisor
SELECT FACULTY.FACULTYID,FACULTY.FNAME,FACULTY.LNAME,STUDENT.STUDENTID,FIRSTNAME,LASTNAME
FROM STUDENT,FACULTY,ADVISOR
WHERE (STUDENT.STUDENTID = ADVISOR.STUDENTID) AND
(FACULTY.FACULTYID = ADVISOR.FACULTYID)
ORDER BY FACULTY.FACULTYID
;