Lab #9: Oracle at Database Tutorial

Lab #9: Oracle

Lab #9: Oracle Assignment

You begin these steps after running the create_oracle_store.sql, the seed_oracle_store.sql, apply_oracle_lab5.sql, apply_oracle_lab6.sql, apply_oracle_lab7.sql, and apply_oracle_lab8.sql scripts. You should create the apply_oracle_lab9.sql script as follows:

-- This calls Lab #8, Lab #8 calls Lab #7, Lab #7 calls Lab #6, Lab #6 calls Lab #5, and Lab #5 calls both the Creation and Seed script files.
@../lab8/apply_oracle_lab8.sql
 
SPOOL apply_oracle_lab9.txt
 
... insert code here ...
 
SPOOL OFF

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

[4 points] Create the following TRANSACTION table as per the specification, but do so understanding the business logic of the model. After creating the TRANSACTION table, create a unique index on the columns that make up the natural key and call it the NATURAL_KEY index.

You create the TRANSACTION table and a TRANSACTION_S1 sequence as qualified below.

Implementation Note: The TRANSACTION table has a range relationship to a CALENDAR table that will be defined later.

Business Rule: You may enter an account number into the TRANSACTION_ACCOUNT column of 111-111-111-111 for a debit, and 222-222-222-222 for a credit. A debit recognizes payment for a rental, and is a value that adds to the company’s assets. A credit recognizes a refund for a rental, and is a value that decreases the company’s assets. Debits should have a TRANSACTION_TYPE that maps to the corresponding debit row in the COMMON_LOOKUP table, and credits should have a TRANSACTION_TYPE that maps to the corresponding credit row in the COMMON_LOOKUP table.

System Logic: In a future refinement of our pilot design, the TRANSACTION_ACCOUNT column will be replaced by a foreign key that points to the CHART_OF_ACCOUNT table. A CHART_OF_ACCOUNT table holds rows that describe every account, like it’s account number (e.g., 111-111-111-111), description, start and end date.

Table Name: TRANSACTION
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
TRANSACTION_ID	PRIMARY KEY			Integer	Maximum
TRANSACTION_ACCOUNT	NOT NULL			String	15
TRANSACTION_TYPE	FOREIGN KEY	COMMON_LOOKUP	COMMON_LOOKUP_ID	Integer	Maximum
TRANSACTION_TYPE	NOT NULL	COMMON_LOOKUP	COMMON_LOOKUP_ID	Integer	Maximum
TRANSACTION_DATE	NOT NULL			Date	Date
TRANSACTION_AMOUNT	NOT NULL			Number	Maximum
RENTAL_ID	FOREIGN KEY	RENTAL	RENTAL_ID	Integer	Maximum
RENTAL_ID	NOT NULL	RENTAL	RENTAL_ID	Integer	Maximum
PAYMENT_METHOD_TYPE	FOREIGN KEY	COMMON_LOOKUP	COMMON_LOOKUP_ID	Integer	Maximum
PAYMENT_METHOD_TYPE	NOT NULL	COMMON_LOOKUP	COMMON_LOOKUP_ID	Integer	Maximum
PAYMENT_ACCOUNT_NUMBER	NOT NULL			String	19
CREATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATED_BY	NOT NULL	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATION_DATE	NOT NULL			Date	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATED_BY	NOT NULL	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			Date	Date

* The TRANSACTION_AMOUNT column uses a NUMBER data type but the column may contain an integer or decimal value.

Don’t forget that on the Oracle Database, that you’ll need to provide an explicit TRANSACTION_S1 sequence. The sequence should start with the default value of a 1.

You should use the following formatting and query to verify completion of this step:

COLUMN table_name   FORMAT A14  HEADING "Table Name"
COLUMN column_id    FORMAT 9999 HEADING "Column ID"
COLUMN column_name  FORMAT A22  HEADING "Column Name"
COLUMN nullable     FORMAT A8   HEADING "Nullable"
COLUMN data_type    FORMAT A12  HEADING "Data Type"
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'TRANSACTION'
ORDER BY 2;

It should display the following results:

Table Name     Column ID Column Name            Nullable Data Type
-------------- --------- ---------------------- -------- ------------
TRANSACTION            1 TRANSACTION_ID         NOT NULL NUMBER(22)
TRANSACTION            2 TRANSACTION_ACCOUNT    NOT NULL VARCHAR2(15)
TRANSACTION            3 TRANSACTION_TYPE       NOT NULL NUMBER(22)
TRANSACTION            4 TRANSACTION_DATE       NOT NULL DATE
TRANSACTION            5 TRANSACTION_AMOUNT     NOT NULL NUMBER(22)
TRANSACTION            6 RENTAL_ID              NOT NULL NUMBER(22)
TRANSACTION            7 PAYMENT_METHOD_TYPE    NOT NULL NUMBER(22)
TRANSACTION            8 PAYMENT_ACCOUNT_NUMBER NOT NULL VARCHAR2(19)
TRANSACTION            9 CREATED_BY             NOT NULL NUMBER(22)
TRANSACTION           10 CREATION_DATE          NOT NULL DATE
TRANSACTION           11 LAST_UPDATED_BY        NOT NULL NUMBER(22)
TRANSACTION           12 LAST_UPDATE_DATE       NOT NULL DATE
 
12 rows selected.

You create the NATURAL_KEY unique index as qualified below.

After you create the table, you need to add a UNIQUE INDEX on the following columns to the TRANSACTION table. This is necessary to improve the run-time performance of the merge operations later in this lab.

RENTAL_ID
TRANSACTION_TYPE
TRANSACTION_DATE
PAYMENT_METHOD_TYPE
PAYMENT_ACCOUNT_NUMBER
TRANSACTION_ACCOUNT

You should use the following formatting and query to verify completion of this step:

COLUMN table_name       FORMAT A12  HEADING "Table Name"
COLUMN index_name       FORMAT A16  HEADING "Index Name"
COLUMN uniqueness       FORMAT A8   HEADING "Unique"
COLUMN column_position  FORMAT 9999 HEADING "Column Position"
COLUMN column_name      FORMAT A24  HEADING "Column Name"
SELECT   i.table_name
,        i.index_name
,        i.uniqueness
,        ic.column_position
,        ic.column_name
FROM     user_indexes i INNER JOIN user_ind_columns ic
ON       i.index_name = ic.index_name
WHERE    i.table_name = 'TRANSACTION'
AND      i.uniqueness = 'UNIQUE'
AND      i.index_name = 'NATURAL_KEY';

It should display the following results:

Table Name   Index Name       Unique   Column Position Column Name
------------ ---------------- -------- --------------- ------------------------
TRANSACTION  NATURAL_KEY      UNIQUE                 1 RENTAL_ID
TRANSACTION  NATURAL_KEY      UNIQUE                 2 TRANSACTION_TYPE
TRANSACTION  NATURAL_KEY      UNIQUE                 3 TRANSACTION_DATE
TRANSACTION  NATURAL_KEY      UNIQUE                 4 PAYMENT_METHOD_TYPE
TRANSACTION  NATURAL_KEY      UNIQUE                 5 PAYMENT_ACCOUNT_NUMBER
TRANSACTION  NATURAL_KEY      UNIQUE                 6 TRANSACTION_ACCOUNT
 
6 rows selected.

[2 points] Insert the following two TRANSACTION_TYPE rows and four PAYMENT_METHOD_TYPE rows into the COMMON_LOOKUP table. They should have valid who-audit column data.

Table Name: COMMON_LOOKUP
Lookup Table	Lookup Column	Lookup Type	Lookup Meaning	Lookup Code
TRANSACTION	TRANSACTION_TYPE	CREDIT	Credit	CR
TRANSACTION	TRANSACTION_TYPE	DEBIT	Debit	DR
TRANSACTION	PAYMENT_METHOD_TYPE	DISCOVER_CARD	Discover Card
TRANSACTION	PAYMENT_METHOD_TYPE	VISA_CARD	Visa Card
TRANSACTION	PAYMENT_METHOD_TYPE	MASTER_CARD	Master Card
TRANSACTION	PAYMENT_METHOD_TYPE	CASH	Cash

You should use the following formatting and query to verify completion of this step:

COLUMN common_lookup_table  FORMAT A20 HEADING "COMMON_LOOKUP_TABLE"
COLUMN common_lookup_column FORMAT A20 HEADING "COMMON_LOOKUP_COLUMN"
COLUMN common_lookup_type   FORMAT A20 HEADING "COMMON_LOOKUP_TYPE"
SELECT   common_lookup_table
,        common_lookup_column
,        common_lookup_type
FROM     common_lookup
WHERE    common_lookup_table = 'TRANSACTION'
AND      common_lookup_column IN ('TRANSACTION_TYPE','PAYMENT_METHOD_TYPE')
ORDER BY 1, 2, 3 DESC;

It should display the following results:

COMMON_LOOKUP_TABLE  COMMON_LOOKUP_COLUMN COMMON_LOOKUP_TYPE
-------------------- -------------------- --------------------
TRANSACTION          PAYMENT_METHOD_TYPE  VISA_CARD
TRANSACTION          PAYMENT_METHOD_TYPE  MASTER_CARD
TRANSACTION          PAYMENT_METHOD_TYPE  DISCOVER_CARD
TRANSACTION          PAYMENT_METHOD_TYPE  CASH
TRANSACTION          TRANSACTION_TYPE     DEBIT
TRANSACTION          TRANSACTION_TYPE     CREDIT
 
6 rows selected.

[14 points] Create the following AIRPORT and ACCOUNT_LIST tables as per the specification, but do so understanding the business logic of the model.

Implementation Note: The AIRPORT table has a filtered substring relationship to ACCOUNT numbers because the business model is evolving toward airport kiosks rather than stores. The ACCOUNT_LIST contains a set of pre-seeded values that may be consumed as new customers are entered into the Video Store application.

Business Rule: You must seed the AIRPORT with any additional information that may be added to the model to support new customers. The city where the customer lives needs to be mapped to the nearest regional airport. All ACCOUNT_NUMBER column values must contain a valid account number found in the ACCOUNT_LIST table. When an account number is assigned to a customer, you must indicate the SYSTEM_USER_ID linked to assigning the account number in the CONSUMED_BY column of the ACCOUNT_LIST table, and provide the date of that activity in the CONSUMED_BY column.

System Logic: Seeding procedures are provided in the lab to update all existing data in the model, provided you add any required new cities in the AIRPORT table. The import of external records is now dependent on accurate ACCOUNT_NUMBER values in the MEMBER table. You must update all pre-existing records.

You need to create the AIRPORT table and the AIRPORT_S1 sequences.

Table Name: AIRPORT
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
AIRPORT_ID	PRIMARY KEY			Integer	Maximum
AIRPORT_CODE	NOT NULL			String	3
AIRPORT_CITY	NOT NULL			String	30
CITY	NOT NULL			String	30
STATE_PROVINCE	NOT NULL			String	30
CREATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATED_BY	NOT NULL	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATION_DATE	NOT NULL			Date	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATED_BY	NOT NULL	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			Date	Date

You should use the following formatting and query to verify completion of this step:

COLUMN table_name   FORMAT A14  HEADING "Table Name"
COLUMN column_id    FORMAT 9999 HEADING "Column ID"
COLUMN column_name  FORMAT A22  HEADING "Column Name"
COLUMN nullable     FORMAT A8   HEADING "Nullable"
COLUMN data_type    FORMAT A12  HEADING "Data Type"
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'AIRPORT'
ORDER BY 2;

It should display the following results:

Table Name     Column ID Column Name            Nullable Data Type
-------------- --------- ---------------------- -------- ------------
AIRPORT                1 AIRPORT_ID             NOT NULL NUMBER(22)
AIRPORT                2 AIRPORT_CODE           NOT NULL VARCHAR2(3)
AIRPORT                3 AIRPORT_CITY           NOT NULL VARCHAR2(30)
AIRPORT                4 CITY                   NOT NULL VARCHAR2(30)
AIRPORT                5 STATE_PROVINCE         NOT NULL VARCHAR2(30)
AIRPORT                6 CREATED_BY             NOT NULL NUMBER(22)
AIRPORT                7 CREATION_DATE          NOT NULL DATE
AIRPORT                8 LAST_UPDATED_BY        NOT NULL NUMBER(22)
AIRPORT                9 LAST_UPDATE_DATE       NOT NULL DATE
 
9 rows selected.

You need to create a unique natural key (named NK_AIRPORT) index for the AIRPORT table. You should create it with the following four columns.

AIRPORT_CODE
AIRPORT_CITY
CITY
STATE_PROVINCE

You should use the following formatting and query to verify completion of this step:

COLUMN table_name       FORMAT A12  HEADING "Table Name"
COLUMN index_name       FORMAT A16  HEADING "Index Name"
COLUMN uniqueness       FORMAT A8   HEADING "Unique"
COLUMN column_position  FORMAT 9999 HEADING "Column Position"
COLUMN column_name      FORMAT A24  HEADING "Column Name"
SELECT   i.table_name
,        i.index_name
,        i.uniqueness
,        ic.column_position
,        ic.column_name
FROM     user_indexes i INNER JOIN user_ind_columns ic
ON       i.index_name = ic.index_name
WHERE    i.table_name = 'AIRPORT'
AND      i.uniqueness = 'UNIQUE'
AND      i.index_name = 'NK_AIRPORT';

It should display the following results:

Table Name   Index Name       Unique   Column Position Column Name
------------ ---------------- -------- --------------- ------------------------
AIRPORT      NK_AIRPORT       UNIQUE                 1 AIRPORT_CODE
AIRPORT      NK_AIRPORT       UNIQUE                 2 AIRPORT_CITY
AIRPORT      NK_AIRPORT       UNIQUE                 3 CITY
AIRPORT      NK_AIRPORT       UNIQUE                 4 STATE_PROVINCE
 
4 rows selected.

You need to seed the AIRPORT table with at least these cities, and any others that you’ve used for inserted values in the CONTACT table.

Table Name: AIRPORT
Airport Code	Airport City	City	State Province
LAX	Los Angeles	Los Angeles	California
SLC	Salt Lake City	Provo	Utah
SLC	Salt Lake City	Spanish Fork	Utah
SFO	San Francisco	San Francsico	California
SJC	San Jose	San Jose	California
SJC	San Jose	San Carlos	California

You should use the following query to verify completion of this step:

COLUMN code           FORMAT A4  HEADING "Code"
COLUMN airport_city   FORMAT A14 HEADING "Airport City"
COLUMN city           FORMAT A14 HEADING "City"
COLUMN state_province FORMAT A10 HEADING "State or|Province"
SELECT   airport_code AS code
,        airport_city
,        city
,        state_province
FROM     airport;

It should display the following results:

                                         State or
Code   Airport City     City             Province
------ ---------------- ---------------- ------------
LAX    Los Angeles      Los Angeles      California
SFO    San Francisco    San Francisco    California
SJC    San Jose         San Carlos       California
SJC    San Jose         San Jose         California
SLC    Salt Lake City   Provo            Utah
SLC    Salt Lake City   Spanish Fork     Utah
 
6 rows selected.

You need to create the ACCOUNT_LIST table and ACCOUNT_LIST_S1 sequence.

Table Name: ACCOUNT_LIST
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
ACCOUNT_LIST_ID	PRIMARY KEY			Integer	Maximum
ACCOUNT_NUMBER	NOT NULL			String	10
CONSUMED_DATE				Date	Date
CONSUMED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATED_BY	NOT NULL	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATION_DATE	NOT NULL			Date	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATED_BY	NOT NULL	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			Date	Date

You should use the following formatting and query to verify completion of this step:

COLUMN table_name   FORMAT A14
COLUMN column_id    FORMAT 9999
COLUMN column_name  FORMAT A22
COLUMN data_type    FORMAT A12
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'ACCOUNT_LIST'
ORDER BY 2;

It should display the following results (please note that the CONSUMED_DATE and CONSUMED_BY columns are nullable until you use the account number):

TABLE NAME     COLUMN_ID COLUMN NAME            NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
ACCOUNT_LIST           1 ACCOUNT_LIST_ID        NOT NULL NUMBER(22)
ACCOUNT_LIST           2 ACCOUNT_NUMBER         NOT NULL VARCHAR2(10)
ACCOUNT_LIST           3 CONSUMED_DATE                   DATE
ACCOUNT_LIST           4 CONSUMED_BY                     NUMBER(22)
ACCOUNT_LIST           5 CREATED_BY             NOT NULL NUMBER(22)
ACCOUNT_LIST           6 CREATION_DATE          NOT NULL DATE
ACCOUNT_LIST           7 LAST_UPDATED_BY        NOT NULL NUMBER(22)
ACCOUNT_LIST           8 LAST_UPDATE_DATE       NOT NULL DATE
 
8 rows selected.

Don’t forget that you need to create a ACCOUNT_LIST_S1 sequence to enable the procedure to work.

You need to seed the ACCOUNT_LIST table. This would take more time than leveraging a stored procedure but writing stored procedures from class is a beyond scope. Therefore, you’ve been provided with stored procedures for the databases. Click on the Setup Script to see how the procedure works, how to run the procedure, and how to check how many rows are inserted into the ACCOUNT_LIST table.

Setup Script →

Click on this to see a necessary PL/SQL procedure for this lab.

This contains the necessary seeding code that lets you apply specific rules to populate the ACCOUNT_LIST table. It is dependent upon you’re seeding of the AIRPORT table. It won’t work correctly if you have incorrect values in that table.

You should just copy and past this code into a text editor of your choice. Then, you can run it as the student user against the student database. After you have it in a file, you should run it to place the procedure in your database.

-- Create or replace seeding procedure.
CREATE OR REPLACE PROCEDURE seed_account_list IS
  /* Declare variable to capture table, and column. */
  lv_table_name   VARCHAR2(90);
  lv_column_name  VARCHAR2(30);
 
  /* Declare an exception variable and PRAGMA map. */
  not_null_column  EXCEPTION;
  PRAGMA EXCEPTION_INIT(not_null_column,-1400);
 
BEGIN
  /* Set savepoint. */
  SAVEPOINT all_or_none;
 
  FOR i IN (SELECT DISTINCT airport_code FROM airport) LOOP
    FOR j IN 1..50 LOOP
 
      INSERT INTO account_list
      VALUES
      ( account_list_s1.NEXTVAL
      , i.airport_code||'-'||LPAD(j,6,'0')
      , NULL
      , NULL
      , 1002
      , SYSDATE
      , 1002
      , SYSDATE);
    END LOOP;
  END LOOP;
 
  /* Commit the writes as a group. */
  COMMIT;
 
EXCEPTION
  WHEN not_null_column THEN
    /* Capture the table and column name that triggered the error. */
    lv_table_name := (TRIM(BOTH '"' FROM RTRIM(REGEXP_SUBSTR(SQLERRM,'".*\."',REGEXP_INSTR(SQLERRM,'\.',1,1)),'."')));
    lv_column_name := (TRIM(BOTH '"' FROM REGEXP_SUBSTR(SQLERRM,'".*"',REGEXP_INSTR(SQLERRM,'\.',1,2))));
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
    RAISE_APPLICATION_ERROR(
       -20001
      ,'Remove the NOT NULL contraint from the '||lv_column_name||' column in'||CHR(10)||' the '||lv_table_name||' table.');
  WHEN OTHERS THEN
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
END;
/

After you create the procedure in your database, you call it like so:

EXECUTE seed_account_list();

You should use the following query to verify completion of this step:

COLUMN object_name FORMAT A18
COLUMN object_type FORMAT A12
SELECT   object_name
,        object_type
FROM     user_objects
WHERE    object_name = 'SEED_ACCOUNT_LIST';

It should display the following results:

OBJECT_NAME        OBJECT_TYPE
------------------ ------------
SEED_ACCOUNT_LIST  PROCEDURE
 
1 row selected.

The procedure has an embedded COMMIT instruction. You can query the ACCOUNT_LIST table to make sure it inserted 50 rows for each airport.

After you’ve created the ACCOUNT_LIST table and the SEED_ACCOUNT_LIST procedure, you call the SEED_ACCOUNT_LIST procedure to populate the table. The following query verifies that you’ve successfully run the SEED_ACCOUNT_LIST procedure:

COLUMN airport FORMAT A7
SELECT   SUBSTR(account_number,1,3) AS "Airport"
,        COUNT(*) AS "# Accounts"
FROM     account_list
WHERE    consumed_date IS NULL
GROUP BY SUBSTR(account_number,1,3)
ORDER BY 1;

It should display the following:

Airport # Accounts
------- ----------
LAX             50
SFO             50
SJC             50
SLC             50
 
4 rows selected.

In a prior lab and in both the create and seed scripts, the STATE_PROVINCE column values uses a mixture of US Postal Services state abbreviations and full state names. You need to update all STATE_PROVINCE values with their full state names because a subsequent seeding and the import program rely on full STATE_PROVINCE names. You need to update any pre-seeded US Postal Service state abbreviations with the full state names.

The following UPDATE statement changes US Postal Services abbreviations to the full state name:

1
2
3

UPDATE address
SET    state_province = 'California'
WHERE  state_province = 'CA';

You need to run the script that creates the UPDATE_MEMBER_ACCOUNT procedure, and then you need to call it to update values in the MEMBER and ACCOUNT_LIST tables. It relies on you having run the SEED_ACCOUNT_LIST procedure successfully.

Setup Script →

Click on this to see a necessary PL/SQL procedure for this lab.

This contains the necessary code to consume values from the ACCOUNT_LIST table and update the ACCOUNT_NUMBER column of the MEMBER table with correct values from the ACCOUNT_LIST table.

You should just copy and past this code into your apply_lab9_oracle.sql script. Then, you can run it as the student user.

CREATE OR REPLACE PROCEDURE update_member_account IS
 
  /* Declare a local variable. */
  lv_account_number VARCHAR2(10);
 
  /* Declare a SQL cursor fabricated from local variables. */  
  CURSOR member_cursor IS
    SELECT   DISTINCT
             m.member_id
    ,        a.city
    ,        a.state_province
    FROM     member m INNER JOIN contact c
    ON       m.member_id = c.member_id INNER JOIN address a
    ON       c.contact_id = a.contact_id
    ORDER BY m.member_id;
 
BEGIN
 
  /* Set savepoint. */  
  SAVEPOINT all_or_none;
 
  /* Open a local cursor. */  
  FOR i IN member_cursor LOOP
 
      /* Secure a unique account number as they're consumed from the list. */
      SELECT al.account_number
      INTO   lv_account_number
      FROM   account_list al INNER JOIN airport ap
      ON     SUBSTR(al.account_number,1,3) = ap.airport_code
      WHERE  ap.city = i.city
      AND    ap.state_province = i.state_province
      AND    consumed_by IS NULL
      AND    consumed_date IS NULL
      AND    ROWNUM < 2;
 
      /* Update a member with a unique account number linked to their nearest airport. */
      UPDATE member
      SET    account_number = lv_account_number
      WHERE  member_id = i.member_id;
 
      /* Mark consumed the last used account number. */      
      UPDATE account_list
      SET    consumed_by = 1002
      ,      consumed_date = SYSDATE
      WHERE  account_number = lv_account_number;
 
  END LOOP;
 
  /* Commit the writes as a group. */
  COMMIT;
 
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('You have an error in your AIRPORT table inserts.');
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
  WHEN OTHERS THEN
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
END;
/

You should use the following query to verify completion of this step:

COLUMN object_name FORMAT A22
COLUMN object_type FORMAT A12
SELECT   object_name
,        object_type
FROM     user_objects
WHERE    object_name = 'UPDATE_MEMBER_ACCOUNT';

It should display the following results:

OBJECT_NAME            OBJECT_TYPE
---------------------- ------------
UPDATE_MEMBER_ACCOUNT  PROCEDURE
 
1 row selected.

After you create the procedure in your database, you call it like so:

EXECUTE update_member_account();

The procedure has an embedded COMMIT instruction. You can query the updated ACCOUNT_NUMBER values from the MEMBER table. It inserts one row for each row in the MEMBER table.

After you create the ACCOUNT_LIST table, you update the MEMBER table by calling the UPDATE_MEMBER_ACCOUNT procedure to populate the table. The following query verifies that change:

-- Format the SQL statement display.
COLUMN member_id      FORMAT 999999 HEADING "Member|ID #"
COLUMN last_name      FORMAT A7     HEADING "Last|Name"
COLUMN account_number FORMAT A10    HEADING "Account|Number"
COLUMN acity          FORMAT A12    HEADING "Address City"
COLUMN apstate        FORMAT A10    HEADING "Airport|State or|Province"
COLUMN alcode         FORMAT A5     HEADING "Airport|Account|Code"
 
-- Query distinct members and addresses.
SELECT   DISTINCT
         m.member_id
,        c.last_name
,        m.account_number
,        a.city AS acity
,        ap.state_province AS apstate
,        SUBSTR(al.account_number,1,3) AS alcode
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN address a
ON       c.contact_id = a.contact_id INNER JOIN airport ap
ON       a.city = ap.city
AND      a.state_province = ap.state_province INNER JOIN account_list al
ON       ap.airport_code = SUBSTR(al.account_number,1,3)
ORDER BY 1;

You should see the following rows in the MEMBER table.

					Address    Airport  
 Member Last	Account 		State or   Account
   ID # Name	Number	   Address City Province   Code
------- ------- ---------- ------------ ---------- --------
   1001 Winn	SJC-000001 San Jose	California SJC
   1002 Vizquel SJC-000002 San Jose	California SJC
   1003 Sweeney SJC-000003 San Jose	California SJC
   1004 Clinton SLC-000001 Provo	Utah	   SLC
   1005 Moss	SLC-000002 Provo	Utah	   SLC
   1006 Gretelz SLC-000003 Provo	Utah	   SLC
   1007 Royal	SLC-000004 Provo	Utah	   SLC
   1008 Smith	SLC-000005 Spanish Fork Utah	   SLC
   1009 Potter	SLC-000006 Provo	Utah	   SLC

Troubleshooting Script ↓

You should open the nested element to find and run a diagnostic script if the previous query failed to return the correct results.

If you didn’t get the result above, it’s likely that a step was missed in this or an earlier lab in the sequence of labs. The missing step most likely caused the update_member_account procedure to fail because it couldn’t resolve a join between tables. The following diagnostic query should be used when the previous query failed.

-- Reset the pagesize.
SET PAGESIZE 99
SET LINESIZE 92
 
-- Verify the account changes in the MEMBER table.
COLUMN member_id      FORMAT 999999 HEADING "Member|ID #"
COLUMN last_name      FORMAT A9     HEADING "Last|Name"
COLUMN account_number FORMAT A10    HEADING "Account|Number"
COLUMN acity          FORMAT A14    HEADING "Address City"
COLUMN apcity         FORMAT A14    HEADING "Airport|List City"
COLUMN astate         FORMAT A10    HEADING "Address|State or|Province"
COLUMN apstate        FORMAT A10    HEADING "Airport|State or|Province"
COLUMN apcode         FORMAT A5     HEADING "Airport|Code"
COLUMN alcode         FORMAT A5     HEADING "Account|Airport|Code"
 
-- Run the query.
SELECT   DISTINCT
         m.member_id
,        c.last_name
,        m.account_number
,        a.city AS acity
,        ap.city AS apcity
,        a.state_province AS astate
,        ap.state_province AS apstate
,        ap.airport_code AS apcode
,        SUBSTR(al.account_number,1,3) AS alcode
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN address a
ON       c.contact_id = a.contact_id RIGHT JOIN airport ap
ON       a.city = ap.city
AND      a.state_province = ap.state_province RIGHT JOIN account_list al
ON       ap.airport_code = SUBSTR(al.account_number,1,3)
ORDER BY 1;
 
-- Reset the pagesize.
SET PAGESIZE 80

This should yield the following results:

							 Address    Airport             Account
 Member Last	Account 				 State or   State or   Airport  Airport
   ID # Name	Number	   Address City   Airport City	 Province   Province   Code     Code
------- ------- ---------- -------------- -------------- ---------- ---------- -------- --------
   1001 Winn	SJC-000001 San Jose	  San Jose	 California California SJC	SJC
   1002 Vizquel SJC-000002 San Jose	  San Jose	 California California SJC	SJC
   1003 Sweeney SJC-000003 San Jose	  San Jose	 California California SJC	SJC
   1004 Clinton SLC-000001 Provo	  Provo 	 Utah	    Utah       SLC	SLC
   1005 Moss	SLC-000002 Provo	  Provo 	 Utah	    Utah       SLC	SLC
   1006 Gretelz SLC-000003 Provo	  Provo 	 Utah	    Utah       SLC	SLC
   1007 Royal	SLC-000004 Provo	  Provo 	 Utah	    Utah       SLC	SLC
   1008 Smith	SLC-000005 Spanish Fork   Spanish Fork	 Utah	    Utah       SLC	SLC
   1009 Potter	SLC-000006 Provo	  Provo 	 Utah	    Utah       SLC	SLC
					  Los Angeles		    California LAX	LAX
					  San Carlos		    California SJC	SJC
					  San Francisco 	    California SFO	SFO

After updating the MEMBER table, you are ready to move forward to the next step.

[5 points] Create the following TRANSACTION_UPLOAD table as per the specification, but do so understanding the business logic of the model. As a BIG PICTURE, our video store just got a consolidation of rentals from a store that’s closed for our customers. We need to import the values and ensure that our source data agrees with the other store. For example, do all customer names, addresses, account numbers match our data. If they do, the merge should go well. If they don’t, the figures will ultimately be incorrect.

Implementation Note: The TRANSACTION_UPLOAD table is in unnormalized form, which means it has repeating rows and unique rows.

Business Rule: The data has secured a valid CUSTOMER_ID and ITEM_ID before generating the CSV source file. You need to create an import table to support the upload of remote store data.

System Logic: This will be implemented in an Oracle database as an external table (or, this short essay on external tables), and in MySQL as a memory resident table. Memory resident tables disappear when the database is rebooted. You use the ENGINE=MEMORY as opposed to the ENGINE=INNODB syntax. These rules support the business rules provided you have no FOREIGN KEY references in the TRANSACTION_UPLOAD table.

Table Name: TRANSACTION_UPLOAD
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
ACCOUNT_NUMBER				String	10
FIRST_NAME				String	20
MIDDLE_NAME				String	20
LAST_NAME				String	20
CHECK_OUT_DATE				Date	Date
RETURN_DATE				Date	Date
RENTAL_ITEM_TYPE				String	12
TRANSACTION_TYPE				String	14
TRANSACTION_AMOUNT				Number	Decimal
TRANSACTION_DATE				Date	Date
ITEM_ID				Integer	Maximum
PAYMENT_METHOD_TYPE				String	14
PAYMENT_ACCOUNT_NUMBER				String	19

Linux and Oracle Virtual Directory Setup Steps →

Open these additional instructions if you are not using the instructor provided Linux image. The instructions will enable you to create and grant privileges to an Oracle virtual directory that you need to complete the lab.

Before you create the TRANSACTION_UPLOAD table, you need to create a physical and virtual directory. You should use the following physical path for the upload directory (the Fedora image has these directories already):

/u01/app/oracle/upload

Assuming you’re working in an Oracle Database 11g XE instance (other than the course’s Fedora image), you can create a valid upload directory as the root user. You assume the root user privilege with the following command:

su - root

You should navigate to this directory:

/u01/app/oracle

Then, issue this command as the root user to create a new upload directory:

mkdir upload

Now you have the following directory:

/u01/app/oracle/upload

Assuming you’ve created the upload directory as the root user, copy all *.csv files to this directory. Then, as the root user you should issue the following two commands from the /u01/app/oracle directory:

chown -R oracle:dba upload
chmod -R 755 upload

Having made that change you should now be able to query the external file source, like a *.csv (comma-separated values) file. Hope this helps those trying to use external tables.

Unzip the transaction_upload.csv file if you’re not using the course’s Fedora image, and put the file in the /u01/app/oracle/upload directory.

Then, connect as the system user and create an upload virtual directory and grant privileges to your student user to read and write from the directory:

CREATE DIRECTORY upload AS '/u01/app/oracle/upload';
GRANT READ, WRITE ON DIRECTORY upload TO student;

Please check the Oracle External Tables web page if you need more information about how to setup external tables in an Oracle database.

Unlike ordinary tables, a query against the data dictionary doesn’t yield a full understanding of an external table. To see the storage clause of an external table, you must use the get_ddl function found in the dbms_metadata package.

You can use the following query to see the full structure of an external table:

SET LONG 200000  -- Enables the display of the full statement.
SELECT   dbms_metadata.get_ddl('TABLE','TRANSACTION_UPLOAD') AS "Table Description"
FROM     dual;

This should show you the following:

Table Description
------------------------------------------------------------
  CREATE TABLE "STUDENT"."TRANSACTION_UPLOAD"
   (    "ACCOUNT_NUMBER" VARCHAR2(10),
        "FIRST_NAME" VARCHAR2(20),
        "MIDDLE_NAME" VARCHAR2(20),
        "LAST_NAME" VARCHAR2(20),
        "CHECK_OUT_DATE" DATE,
        "RETURN_DATE" DATE,
        "RENTAL_ITEM_TYPE" VARCHAR2(12),
        "TRANSACTION_TYPE" VARCHAR2(14),
        "TRANSACTION_AMOUNT" NUMBER,
        "TRANSACTION_DATE" DATE,
        "ITEM_ID" NUMBER,
        "PAYMENT_METHOD_TYPE" VARCHAR2(14),
        "PAYMENT_ACCOUNT_NUMBER" VARCHAR2(19)
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "UPLOAD"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BADFILE     'UPLOAD':'transaction_upload.bad'
      DISCARDFILE 'UPLOAD':'transaction_upload.dis'
      LOGFILE     'UPLOAD':'transaction_upload.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL     )
      LOCATION
       ( 'transaction_upload.csv'
       )
    )
   REJECT LIMIT UNLIMITED
 
1 row selected.

You can verify that you have access to the external roles:

SELECT   COUNT(*) AS "External Rows"
FROM     transaction_upload;

External Rows
-------------
        11520

Written by michaelmclaughlin

June 3rd, 2014 at 11:30 pm

Posted in

53 Responses to 'Lab #9: Oracle'

Subscribe to comments with RSS or TrackBack to 'Lab #9: Oracle'.

“you’re seeding” should be spelled as “your seeding”.

Cam

19 Nov 17 at 5:44 pm
Great catch. Changed.

michaelmclaughlin

23 Dec 17 at 2:55 pm
Sebastian, did you install your own instance? Or, did somebody other than me provide you with it. That would indicate that the Oracle instance was installed differently than it is in the standard classroom instance. The generic installation is always oracle:dba because that’s built into the Red Hat Package installation script.

michaelmclaughlin

23 Dec 17 at 3:04 pm

Database Tutorial

Lab #9: Oracle

53 Responses to 'Lab #9: Oracle'

Leave a Reply

My Technical Blog

Tutorial Pages

Blogroll