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. Assignment: Your task is to create the following tables.

# Records

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 (See Figure 1-1) 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)

Explanation / Answer

The below are the create table statements which create the tables in the database.

1. Student Table:-

CREATE TABLE STUDENT ( STUDENTID NUMBER(10) PRIMARY KEY,FIRSTNAME VARCHAR2(10),LASTNAME VARCHAR2(10),GENDER CHAR(2),ADDRESS VARCHAR2(20),STATE VARCHAR2(10),ZIPCODE NUMBER(5),HOMEPHONE NUMBER(10),EMAIL VARCHAR2(10));

2. Faculty Table:-

CREATE TABLE FACULTY(FACULTYID NUMBER(10) PRIMARY KEY,FIRSTNAME VARCHAR2(10),LASTNAME VARCHAR2(10),GENDER CHAR(2),WORKPHONE NUMBER(10),HOMEPHONE NUMBER(10),EMAIL VARCHAR2(10),ADDRESS VARCHAR2(20),STATE VARCHAR2(10),ZIPCODE NUMBER(5));

3. Course Table

CREATE TABLE COURSE(COURSEID NUMBER(5) PRIMARY KEY,COURSENAME VARCHAR2(10),COURSE_DESCRIPTION VARCHAR2(20),COURSEHOURS NUMBER(3),FID NUMBER(10) FOREIGN KEY REFERENCES FACULTY(FACULTYID));

4. Section Table

CREATE TABLE SECTION (SECTIONNUMBER NUMBER(3) PRIMARY KEY,NO_OF_STUDENTS NUMBER(5),DEPARTMENT VARCHAR2(5),INCHARGE NUMBER(10) FOREIGN KEY REFERENCES FACULTY(FACULTYID));

5. Location Table

CREATE TABLE LOCATION(LOCATIONID NUMBER(5) PRIMARY KEY, LOCATIONNAME VARCHAR2(10),DESCRIPTION VARCHAR2(20),ADDRESS VARCHAR2(20),ZIPCODE NUMBER(5));

Inserting Data to the Tables:-

1. INSERT INTO STUDENT VALUES ( STUDENTIDSEQ.NEXTVAL,'JOHN','JAMES','M','123 STREET','MEXICO',99891,89787676,'JAMES@JOHN.LIVE.COM');

INSERT INTO STUDENT VALUES ( STUDENTIDSEQ.NEXTVAL,'DEBRA','JOHN','FM','1 AVENUE','DALLAS',98901,78767687,'DEBRA@JOHN.LIVE.COM');

2. INSERT INTO FACULTY VALUES ( 987654,'SAI','KUMAR','M',12345678,2345678,'SAI@KUMAR.LIVE.COM','GRAN AVENUE','TORONTO',99890);
  
INSERT INTO FACULTY VALUES (456789,'SATYA','NATH','M',34567812,456789,'STYA@NATH.LIVE.COM','15TH AVENUE','MEXICO',899090);

3. INSERT INTO COURSE VALUES (1001,'DBMS','DATABASE MANAGMENT SYSTES',10,987654);

INSERT INTO COURSE VALUES (1002,'ADBMS','ADVANCED DATABASE MANAGMENT SYSTES',12,987654);

4. INSERT INTO SECTION VALUES (001,20,'CSE',45789);

INSERT INTO SECTION VALUES (010,30,'IT',987654);

5. INSERT INTO LOCATION VALUES (210,'GRAND AVENUE',' MAIN OFFICE LOCATION','15TH AVENUE',98901);

INSERT INTO LOCATION VALUES (102,'123 STREET',' 2ND OFFICE LOCATION','MEXICO',99891);

Above are the insert statements which inserts data into the database tables. I have entered 2 records in each table. Use the same insert statements with the new data to insert more data into tables.

Sequence Creation:-

CREATE SEQUENCE STUDENTIDSEQ START WITH 12001 INCREMENT BY 1 NOCYCLE;

This will create the sequence on studentid field of student table which starts from the number 12001 and then it will be gradually increment by 1 for every student who joins.

Index Creation :-

Indexes will contain an entry for the given columns so that the data retrieval is direct and fast of that rows. By default indexes will be created in the database. Also we can explicitly create the index for the faster access of data.

1. CREATE INDEX STUDENTINDEX ON STUDENT(STUDENTID,STUDENTNAME);

This will create INDEX on studentid and name columns of student table.

2. CREATE INDEX COURSEINDEX ON COURSE (COURSENAME);

This will create index on coursename of course table.

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

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