Database Tutorial

Course Tutorial Site

Site Admin

Old Lab 2

without comments

Lab #2: Oracle Assignment

You begin these steps by running the cleanup_oracle.sql and create_oracle_store.sql scripts. A great starting point for this lab is to review the create_oracle_store.sql script. The create_oracle_store.sql script creates 10 tables. Your Lab #2 script creates 10 tables with some changes and alterations. You should use the script provided in the downloaded instance or create a script like:

-- Run the prior lab script.
@/home/student/Data/cit225/oracle/lib/cleanup_oracle.sql
@/home/student/Data/cit225/oracle/lib/create_oracle_store.sql
 
SPOOL apply_oracle_lab2.txt
 
... insert code here ...
 
SPOOL OFF

You should embed the verification queries inside your apply_lab2_oracle.sql script.

  1. [0 points] Tutorial on creating the SYSTEM_USER_LAB table and the SYSTEM_USER_LAB_S1 sequence, which is Step #1. The reasons for making the changes are:

    • Table, sequence, and constraint names are unique inside a schema or database.
    • All the tables, sequences, and constraints names must have counterparts that use an _LAB suffix or an _LAB_ between the base table constraint name and its number.

After making these changes for one table, the SYSTEM_USER_LAB table, you repeat the process by copying segments and editing them until you replicate the entire create_oracle_store.sql script for tables with the _LAB suffix or an _LAB_ element between the base table constraint name and its number.

  1. [2 points] Create the SYSTEM_USER_LAB table described by the following and the SYSTEM_USER_LAB_S1 sequence starting with a value of 1001:
  1. [2 points] Create the COMMON_LOOKUP_LAB table described by the following and the COMMON_LOOKUP_LAB_S1 sequence starting with a value of 1001:
  1. [2 points] Create the MEMBER_LAB table described by the following and the MEMBER_LAB_S1 sequence starting with a value of 1001:

  1. [2 points] Create the CONTACT_LAB table described by the following and the CONTACT_LAB_S1 sequence starting with a value of 1001:

  1. [2 points] Create the ADDRESS_LAB table described by the following and the ADDRESS_LAB_S1 sequence starting with a value of 1001:

  1. [2 points] Create the STREET_ADDRESS_LAB table described by the following and the STREET_ADDRESS_LAB_S1 sequence starting with a value of 1001:

  1. [2 points] Create the TELEPHONE_LAB table described by the following and the TELEPHONE_LAB_S1 sequence starting with a value of 1001:
  1. [2 points] Create the RENTAL_LAB table described by the following and the RENTAL_LAB_S1 sequence starting with a value of 1001:
  1. [2 points] Create the ITEM_LAB table described by the following and the ITEM_LAB_S1 sequence starting with a value of 1001:

  1. [2 points] Create the RENTAL_ITEM_LAB table described by the following and the RENTAL_ITEM_LAB_S1 sequence starting with a value of 1001:

  1. [0 points] You can confirm the creation of the ten tables with the following query:

  1. [0 points] You can confirm the creation of the ten sequences with the following query:

Written by michaelmclaughlin

April 30th, 2018 at 3:29 am

Posted in