In this exercise, you will demonstrate your understanding of the object-relational features of Oracle.
Directions: Complete the tasks that follow.
- Take screenshots to show the successful execution of SQL statements for the tasks indicated. Place the screenshots for the tasks indicated after the words, Place screenshot here.
- Save all SQL statements to one text file named Name the file Additional_Lab_Assignment.txt.
Add comments to identify the task. Include a description of the SQL statement.
Deliverables
The deliverables for this assignment are:
- This document that contains the screenshots.
- The file named Additional_Lab_Assignment.txt.
Tasks:
- Read the lecture notes for Week 5.
- Review the on the object-relational features of Oracle
- Make a copy of the STUDENT table and name it STUDENT1.
One way to make a copy of the STUDENT table is to use a construction like:
CREATE TABLE STUDENT1 AS (SELECT * FROM STUDENT);
Place screenshot here:
Define a user-defined object type data type named phone_type with attributes COUNTRY_CODE, AREA_CODE and PHONE_NUMBER. Use datatypes varchar2(3), varchar2(3), and varchar2(12), respectively.
Place screenshot here:
- Define a user-defined VARRAY data type named Phone_List_type as an array of size three of the type phone_type.
Place screenshot here:
- Modify the table STUDENT1 by adding the attribute PHONE2 is of data type Phone_List_type.
Place screenshot here:
- Populate the new PHONE2 column: Use ‘001’ for the value of COUNTRY_CODE. Use the values of AREA_CODE, PHONE_NUMBER from PHONE. The following SQL will populate the PHONE2 with one of phone number.
UPDATE STUDENT1 S SET S.PHONE2 = (phone_list_type(phone_type((‘001’), (substr(S.phone,1,3)), (substr(S.phone,5,8)))));
Place screenshot here:
- Drop the column PHONE and rename PHONE2 as PHONE.
Place screenshot here:
- Write a query to display the values of the attributes student_ID and phone.
select s.student_ID, v.* from student1 s, table (s.phone) v;
Place screenshot here:
- Create a type named course_T that has attributes named COURSE_NO, DESCRIPTION, ENROLL_DATE and FINAL_GRADE.
create or replace type course_T as object(Course_No number(8,0), Description varchar2(50), Enroll_Date date, Final_Grade number(3,0));
Place screenshot here:
- Create an object table named course_Table_T as a table of course_T .
create or replace type course_Table_T is table of course_T;
Place screenshot here:
- Create a table named transcript that contains the student_id and a column whose type is the table course_Table_T.
create table transcript(Student_ID number(8,0), Transcript_attrib course_Table_T) nested table Transcript_attrib store as Transcript_tab;
Place screenshot here:
- Determine what courses have grades and update the transcript table accordingly.
/* Using the following SQL, I determined the students that successfully completed the course. I also obtained the variables needed to populate the nested table and they included student_ID, course_no, Description, Enroll_Date, Final_Grade*/
select e.student_id, c.course_no, c.Description, e.enroll_date, e.final_grade from course c inner join section sc on c.course_NO = sc.course_NO inner join enrollment e on sc.section_ID = e.section_ID inner join student1 s on e.student_id=s.student_id where s.student_id =102 and e.final_grade is not null;
/* The above query yielded only data from only one student who had successfully completed the course. This student’s information was inserted in the nested table as follows */
insert into transcript values(102, course_Table_T(Course_T(25,’Intro to Programming’,’30-JAN-07′,92)));
–The following SQL statement retrieves data from transcript
select t.student_ID, c.* from transcript t, table(t.transcript_attrib) c;
Place screenshots here:
- Create and populate a table named Current_Enrollment that contains the student_id and a column whose type is the table course_Table_T. Include only those courses with no final grade.
DECLARE
course_tbl course_table_t;
counter number;
BEGIN
FOR i IN (SELECT s.student_id FROM student1 s)
LOOP
course_tbl := course_table_t();
counter := 1;
FOR k IN (SELECT course_t(c.course_no, c.Description, e.enroll_date, e.final_grade) AS course_temp
FROM course c
INNER JOIN section sc ON c.course_NO = sc.course_NO
INNER JOIN enrollment e ON sc.section_ID = e.section_ID
INNER JOIN student1 s ON e.student_id = s.student_id
WHERE e.final_grade IS NULL AND e.student_id = i.student_id)
LOOP
course_tbl.extend();
course_tbl(counter) := k.course_temp;
counter := counter + 1;
END LOOP;
INSERT INTO current_enrollment VALUES(i.student_id, course_tbl);
END LOOP;
END;
/
SELECT c.student_ID, t.* FROM current_enrollment c, table(c.current_enrollment_attrib) t;
Place screenshots here:
- The attribute PHONE is of type PHONE_LIST TYPE where PHONE_LIST_TYPE is a VARRAY(3. There is just one phone number in the VARRAY. Modify the database such that the attribute PHONE contains three phone numbers: A primary phone, a home phone number, and a mobile number.
DECLARE
phone_list phone_list_type;
BEGIN
FOR i IN (SELECT s.student_id FROM student1 s)
LOOP
SELECT s.phone INTO phone_list FROM student1 s WHERE s.student_id = i.student_id;
phone_list.extend;
phone_list (2) := phone_type((‘001’), (‘222’), (‘555-1111’));
phone_list.extend;
phone_list (3) := phone_type((‘001’), (‘333’), (‘555-4444’));
UPDATE student1 s SET s.phone = phone_list WHERE s.student_id = i.student_id;
END LOOP;
END;
/
SELECT s.student_id, p.* FROM student1 s, table(s.phone) p;
Place screenshots here:
- Describe the logic of the PL/SQL statements in tasks 13 and 14..
Figure 1 Student Schema
Figure 2http://www.oraclesqlbyexample.com/download-the-sample-database.html
Chapters 14 and 15 cover topics for this assignment.
Code Samples for Chapters 14 and 15 may be found at https://www.mhprofessional.com/9780072230666-usa-oracle-database-10g-plsql-programming-group
A zip file with the code samples for chapters 14 and 15 are contained in the zip file that is embedded in this document:
Other Resources:
A Sample Application using Object-Relational Features
Using PL/SQL With Object Types
Introduction to Object-Relational Mapping
Adding Custom Datatypes to Oracle SQL Developer Data Modeler (SDDM)
Object-Relational Features of Oracle
Viewing Complex Types in Oracle SQL Developer Data Modeler
Object Views and Nested Tables
A pdf of the Student Database Schema is embedded in this document:
