You are doing a consulting project for an educational institution and want to co

Question

You are doing a consulting project for an educational institution and want to convince them to utilize your software. They currently have limitations using their current software and are in search of a new course management system.

Your task is to create the following tables.

Recodrds#

1. Student 20

2. Faculty 15

3. Course 15

4. Section 25

5. Location 20

Populate each table with the records above making sure each field is unique. (Hint: Use the insert statement). TURN IN: 1. A single script file that contains all the commands used in this lab. The script file should also include a NICELY FORMATTED report for each file. The script file will be used for each subsequent. 2. In addition create at least 1 sequence and apply it to one of your tables. Feel free to use any number of sequences for your other tables as well. Also, create at least 2 indexes for your tables; choose the fields you feel will benefit most from having an index. Feel free to add more. Use the information below for the structure of your tables. Note: “N” is short for NUMBER, “V” is short for VARCHAR2, and “C” is short for CHAR. STUDENT StudentID N(10) FirstName VC(20) LastName VC(20) MI C(1) Address VC(20) City VC(20) State C(2) Zip VC(10) HomePhone VC(10) WorkPhone VC(10) Email VC(20) DOB DATE PIN VC(10) Status C(1) FACULTY FacultyID N(10) FirstName VC(20) LastName VC(20) MI C(1) WorkPhone VC(10) CSCI 2370 - Page 2 CellPhone VC(10) Rank VC(20) Experience VC(20) Status C(1) COURSE CourseID N(10) CourseNumber VC(20) CourseName VC(20) Description VC(20) CreditHours N(4) Status C(1) SECTION SectionID N(10) CourseID N(10) SectionNumber VC(10) Days VC(10) StartTime DATE EndTime DATE LocationID N(10) SeatsAvailable N(3) Status C(1) LOCATION LocationID N(10) Building VC(20) Room VC(5) Capacity N(5) Status C(1)

Explanation / Answer

I've done the exercise for one of the tables, for the rest of them it is the same exercise and you must give it a try on your own.

--table creation script

CREATE TABLE STUDENT(
StudentID NUMBER(10) NOT NULL,
FirstName VARCHAR2(20) NOT NULL,
LastName VARCHAR2(20) NOT NULL,
MI CHAR(1) NOT NULL,
Address VARCHAR2(20) NOT NULL,
City VARCHAR2(20) NOT NULL,
State CHAR(2) NOT NULL,
Zip VARCHAR2(10) NOT NULL,
HomePhone VARCHAR2(10) NOT NULL,
WorkPhone VARCHAR2(10)NOT NULL,
Email VARCHAR2(20) NOT NULL,
DOB DATE NOT NULL,
PIN VARCHAR2(10) NOT NULL,
Status CHAR(1) NOT NULL
)

ALTER TABLE "STUDENT" ADD PRIMARY KEY ("STUDENTID");

--sequence for generating student id

CREATE SEQUENCE S_ID
MINVALUE 1
MAXVALUE 99999
START WITH 1
INCREMENT BY 1
CACHE 20;
  
--INSERT STATEMENT WITH USE OF SEQUENCE
INSERT INTO STUDENT ("STUDENTID", "FIRSTNAME", "LASTNAME", "MI", "ADDRESS", "CITY", "STATE", "ZIP", "HOMEPHONE", "WORKPHONE", "EMAIL", "DOB", "PIN", "STATUS")
VALUES (S_ID.nextval, 'Johnny', 'Bonny', 'z', 'abc Apartments', 'abc', 'AJ', '101010', '654321', '7654', 'a@abc', '13-MAY-88', '123456', 'A');

--INDEXES ON STATE & FISTNAME,LASTNAME
CREATE INDEX "state_indx"
ON "STUDENT" ("STATE" ASC)
LOGGING
VISIBLE;

CREATE INDEX "name_indx"
ON "NIC"."STUDENT" ("FIRSTNAME" ASC, "LASTNAME" ASC);

--An index will be automatically created on studentid as it is the primary key

---for your help---

FACULTY
FacultyID NUMBER(10)
FirstName VARCHAR2(20)
LastName VARCHAR2(20)
MI CHAR(1)
WorkPhone VARCHAR2(10)
CellPhone VARCHAR2(10)
Rank VARCHAR2(20)
Experience VARCHAR2(20)
Status CHAR(1)

COURSE
CourseID NUMBER(10)
CourseNumber VARCHAR2(20)
CourseName VARCHAR2(20)
Description VARCHAR2(20)
CreditHours NUMBER(4)
Status CHAR(1)

SECTION
SectionID NUMBER(10)
CourseID NUMBER(10)
SectionNumber VARCHAR2(10)
Days VARCHAR2(10)
StartTime DATE
EndTime DATE
LocationID NUMBER(10)
SeatsAvailable NUMBER(3)
Status CHAR(1)

LOCATION
LocationID NUMBER(10)
Building VARCHAR2(20)
Room VARCHAR2(5)
Capacity NUMBER(5)
Status CHAR(1)

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.