METCS779 Assignment 6 Solution

Overview of the Assignment:
Oracle, IBM DB2 and other ORDBMS that support standard object-relational features.

Please submit screenshots demonstrating the execution of your SQL statements, both DDL , DML and the results of the SQL statements.

Object-relational DBMS
In this exercise, we will construct and query an AUTOMOBILE table by using abstract data types (ADTs), VARRAYs, and nested tables. The syntax is for Oracle.
1. Create a PERSON_TY abstract data type which has the following fields -- first_name, last_name, date_of_birth. Assign each field an appropriate data type.
3. Create a OWNER_TY abstract data type which has the following fields -- PERSON_TY, Date_Purchased, Date_Sold. Assign each field an appropriate data type.
4. Create a OWNERS_VA VARRAY of the OWNER_TY abstract data type you created in step 2. Define the VARRAY to allow for 3 OWNER_TY elements.
5. Create an AUTOMOBILE table which is composed of the following elements:
• A vehicle_identification_number field which is the primary key.
• An OWNER_VA VARRAY.
• A nested table named DRIVERS_NT which contains the following fields – DRIVER_TY. Assign each field an appropriate data type.
6. Insert the following rows into the AUTOMOBILE table: ROW 1
• The Vehicle Identification Number is 101.
ROW 2
• The Vehicle Identification Number is 102.



create type PERSON_TY as object( first_name varchar2(20), last_name varchar2(20),
);
/

create or replace type DRIVER_TY as object(
Person PERSON_TY,
Drivers_License_ID varchar(30),
);
/

create or replace type OWNER_TY as object(
Person PERSON_TY,
);
/
create or replace type OWNERS_VA as varray(3) of OWNER_TY;
/


create or replace type DRIVERS_NT as table of DRIVER_TY;
/

create table AUTOMOBILE(
vehicle_identification_number varchar2(30) primary key,
Owners OWNERS_VA,
Drivers DRIVERS_NT
)
nested table Drivers store as DRIVERS_NT_TAB;
/




insert into AUTOMOBILE values (101,
OWNERS_VA(
DRIVER_TY(PERSON_TY('Joe','Smalltalk',to_date('10/7/1982','MM/DD/YYYY
);
/


insert into AUTOMOBILE values
(102,OWNERS_VA(OWNER_TY(PERSON_TY('George','Stephanopolis',null),to
/


select d.Person.first_name, d.Person.last_name, a.vehicle_identification_number, d.Date_Driven from AUTOMOBILE a, table(a.Drivers) d
/


select o.Person.first_name, o.Person.last_name,
a.vehicle_identification_number,
o.Date_Purchased,o.Date_Sold from AUTOMOBILE a, table(a.Owners) o
/



Use the Ask the Teaching Team Discussion Forum if you have any questions regarding the how to approach this assignment.

Save your assignment as lastnameFirstname_assign6_0.docx and submit it in the Assignments section of the course.

For help uploading files please refer to the Technical Support page in the syllabus.