Posted: June 16th, 2022
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.
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.
· 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.
· 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.
· 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.
In this version we construct and query a purely relational schema which is representationally equivalent to the object-relational schema defined in Exercise 1.
Place an order in 3 easy steps. Takes less than 5 mins.