Overview of the Assignment:

There are two versions of this exercise. The first version is for Oracle, IBM DB2 and other ORDBMS that support standard object-relational features. The second version is for Microsoft SQL Server, Sybase, MySQL and other RDBMS that do not support standard object-relational features.

You are required to do only one of these Options, though completing both will help you appreciate the tradeoffs between purely relational and object-relational implementations. If you choose to complete both versions your score will reflect the best aspects of both of your solutions, and any analysis that you provide of the tradeoffs between the implementations. Both versions can be completed in Oracle or other ORDBMS with the requested features.

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

First Option: for object-relational DBMS

In this version of the 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 DRIVER_TY abstract data type which has the following fields — first_name, last_name, date_of_birth. Assign each field an appropriate data type.
2. Create a DRIVERS_VA VARRAY of the DRIVER_TY abstract data type you created in step 1. Define the VARRAY to allow for 5 DRIVER_TY elements.
3.  Create an AUTOMOBILE table which is composed of the following elements:

·         A vehicle_identification_number field which is the primary key.

·         A DRIVERS_VA VARRAY.

·         A nested table named OWNERS_NT which contains the following fields — first_name, last_name, date_purchased. Assign each field an appropriate data type.

1. Insert the following rows into the AUTOMOBILE table:
   ROW 1

·         The Vehicle Identification Number is 101.

·         The first driver is named “Erin Smalltalk”, who was born on May 23rd, 1957.

·         The second driver is named “Joe Smalltalk”, who was born on October 9th, 1983.

·         The only owner is named “Lance Smalltalk”, who purchased the automobile January 19th, 2003.

ROW 2

·         The Vehicle Identification Number is 102.

·         The first driver is named “Julie Goldstein”, who was born on June 19th, 1977.

·         The second driver is named “Max Lucids”, who was born on February 12th, 1989.

·         The first owner is named “George Stephanopolis”, who purchased the automobile July 3rd, 1999.

·         The second owner is named “Max Lucids”, who purchased the automobile on August 23rd, 2009.

5.      Write a query which lists the first and last names of all drivers, along with the vehicle identification number of the car they drive.

Second Option:  for RDBMS without object-relational extensions

In this version we construct and query a purely relational schema which is representationally equivalent to the object-relational schema defined in Exercise 1.

1. Steps 1 – 3 in Version One above define an object-relational schema.  Convert that object-relational schema into an equivalent relational schema. Your relational schema will have tables, primary and foreign keys, and attributes. The purely relational schema that you create should provide for storing the same data along with equivalent relationships. Your schema should include an AUTOMOBILE table.   Submit a diagram of the schema using Visio by pasting it here.
2. Using SQL DDL, create the schema you defined in Step 1 in the relational database you are using for this course, such as Microsoft SQL Server or Oracle.
3. Insert the data values defined in Step #4 in Version One into your purely relational schema.
4. Write a query which lists the first and last name of all drivers, along with the vehicle identification number of the car they drive.