Sql statements, both ddl , dml

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.

 

 

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more