Database Tutorial

Course Tutorial Site

Site Admin

Lab #9: Oracle

with 49 comments

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.

  1. [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.

  1. 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
Type Reference Table Reference Column
TRANSACTION_ID PRIMARY KEY Integer Maximum
TRANSACTION_ACCOUNT NOT NULL String 15
TRANSACTION_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
NOT NULL
TRANSACTION_DATE NOT NULL Date Date
TRANSACTION_AMOUNT NOT NULL Number Maximum
RENTAL_ID FOREIGN KEY RENTAL RENTAL_ID Integer Maximum
NOT NULL
PAYMENT_METHOD_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
NOT NULL
PAYMENT_ACCOUNT_NUMBER NOT NULL String 19
CREATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
NOT NULL
CREATION_DATE NOT NULL Date Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
NOT NULL
LAST_UPDATE_DATE NOT NULL Date Date

* The TRANSACTION_AMOUNT column uses a float data type to designate that 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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.
  1. 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.

  1. [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:

1
2
3
4
5
6
7
8
9
10
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.

  1. [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.

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

Table Name: AIRPORT
Column Name Constraint Data
Type
Physical
Size
Type Reference Table Reference Column
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
NOT NULL
CREATION_DATE NOT NULL Date Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
NOT NULL
LAST_UPDATE_DATE NOT NULL Date Date

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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.

  1. 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.

  1. 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:

1
2
3
4
5
6
7
8
9
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.

  1. You need to create the ACCOUNT_LIST table and ACCOUNT_LIST_S1 sequence.

Table Name: ACCOUNT_LIST
Column Name Constraint Data
Type
Physical
Size
Type Reference Table Reference Column
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
NOT NULL
CREATION_DATE NOT NULL Date Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
NOT NULL
LAST_UPDATE_DATE NOT NULL Date Date

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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.

  1. 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.

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.
  1. 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';
  1. 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.

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 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 apcode         FORMAT A8     HEADING "Airport|State or|Province"
COLUMN alcode         FORMAT A8     HEADING "Airport|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    Airport  Account
 Member Last	Account 		State or   State or   State or State or
   ID # Name	Number	   Address City Province   Province   Province Province
------- ------- ---------- ------------ ---------- ---------- -------- --------
   1001 Winn	SJC-000001 San Jose	California California SJC      SJC
   1002 Vizquel SJC-000002 San Jose	California California SJC      SJC
   1003 Sweeney SJC-000003 San Jose	California California SJC      SJC
   1004 Clinton SLC-000001 Provo	Utah	   Utah       SLC      SLC
   1005 Moss	SLC-000002 Provo	Utah	   Utah       SLC      SLC
   1006 Gretelz SLC-000003 Provo	Utah	   Utah       SLC      SLC
   1007 Royal	SLC-000004 Provo	Utah	   Utah       SLC      SLC
   1008 Smith	SLC-000005 Spanish Fork Utah	   Utah       SLC      SLC
   1009 Potter	SLC-000006 Provo	Utah	   Utah       SLC      SLC

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

  1. [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
Type Reference Table Reference Column
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

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

49 Responses to 'Lab #9: Oracle'

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

  1. In your step G 'Potter' is not put on the table. My code is outputting 'Potter' in the table.

    Sam

    18 Jun 14 at 1:26 pm

  2. Problem One when you create the transaction table , the table diagram you have given had transaction_type as a INT data type but the verify table provided shows it as a VARCHAR2 data type

    Lyle Palagar

    18 Jun 14 at 1:45 pm

  3. Problem One in the table diagram it shows the payment_account_number as a varchar2(19) however on the verify diagram shown the data type as a varchar2(20)

    Lyle Palagar

    18 Jun 14 at 1:48 pm

  4. With the transaction table we should add a comment explaining what a float is and why you put it in there, this was unclear.

    also, the table display for TRANSACTION_TYPE shows VARCHAR2(30) and it should be a NUMBER(22).

    Matthew Stout

    18 Jun 14 at 3:35 pm

  5. The Transaction Table’s foreign key columns may be missing NOT NULL constraints in the diagram.

    Abel Goodwin

    18 Jun 14 at 3:51 pm

  6. The output for Transaction table for the TRANSACTION_TYPE displays the type as being VARCHAR2(30), when it is a foreign key to the common_lookup_id, which is a number.

    I Suggest a change needs to be make to your referenced CREATE TABLE TRANSACTION statement.

    Abel Goodwin

    18 Jun 14 at 3:59 pm

  7. Table Name: TRANSACTION
    Column Name Constraint Data
    Type
    Physical
    Size
    Type Reference Table Reference Column
    TRANSACTION_AMOUNT NOT NULL Float Maximum

    It says the output is:

    TRANSACTION            3 TRANSACTION_TYPE       NOT NULL VARCHAR2(30)

    Reconcile with the data type shown in the diagram.

    Abel Goodwin

    18 Jun 14 at 4:01 pm

  8. The about has been fixed.

    michaelmclaughlin

    18 Jun 14 at 7:06 pm

  9. Abel, the NOT NULL columns have been fixed.

    michaelmclaughlin

    18 Jun 14 at 7:30 pm

  10. “You should see the following rows in the MEMBER table.”
    You may want to include the row with the Potter’s, whom we have entered in a previous lab.

       1009 Potter     SLC-000006 Provo            Utah

    Abel Goodwin

    19 Jun 14 at 10:00 am

  11. The instructions in step #4 are, to me, a little ambiguous.

    I’m unfamiliar with folder paths, where is u01 placed? in the home folder or in lab9?
    Should these instructions come before the table diagram?

    Would you include that to create the virtual directory you log in doing the following:
    sqlplus system/student
    (I doubt the online students would know otherwise).

    At which point do we actually create the transaction_upload table?

    Should we after creating the table be able to have all the check-code functioning or before?

    Abel Goodwin

    19 Jun 14 at 10:21 am

  12. Do you connect as the SYSTEM user, like this:

    system/cangetin

    Abel Goodwin

    19 Jun 14 at 10:34 am

  13. Abel, The data set for the output is fixed.

    michaelmclaughlin

    19 Jun 14 at 3:01 pm

  14. Abel, I’ve provided some basic step-by-step instructions on Linux.

    michaelmclaughlin

    19 Jun 14 at 3:18 pm

  15. Brother McLaughlin,
    “copy all *.csv ”
    I found that we could not simply “copy” the *.csv files by simply copy and pasting as most GUI familiar users would. We needed to use the terminal command to copy from a directory to u01/app/oracle/upload.

    pwd: home/student
    command (from terminal as root): cp transaction_upload.csv /u01/app/oracle/upload

    Abel Goodwin

    19 Jun 14 at 4:08 pm

  16. In 3c the validation code needs headings.

    brett Moan

    6 Nov 14 at 10:01 pm

  17. Where does it show us how to connect to the .csv file? This step seems very vague to me. The following code needs to be placed in our script:

    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;

    Bryce

    6 Nov 14 at 10:48 pm

  18. Brett, I’ve added the headings.

    michaelmclaughlin

    6 Nov 14 at 10:55 pm

  19. Bryce, Please read Chapter 12 in the textbook or the Oracle External Tables web page.

    michaelmclaughlin

    6 Nov 14 at 11:01 pm

  20. “You should create the apply_lab8_oracle.sql script as follows:” at the top of the page within the introductory instructions should be changed to reference apply_lab9_oracle.sql rather than apply_lab8_oracle.sql”.

    Gabe Ventilla

    8 Nov 14 at 12:11 am

  21. Gabe, I opted for apply_oracle_lab8.sql as a script name, so I’ve updated labs 6 through 11.

    michaelmclaughlin

    8 Nov 14 at 10:19 pm

  22. The last sentence in the instructions for Step# 2 is incomplete. It says the following: “After you insert the TRANSACTION_TYPE and PAYMENT_METHOD_TYPE rows in the COMMON_LOOKUP table.”

    Gabe Ventilla

    11 Nov 14 at 8:15 pm

  23. Gabe, It was unnecessary and duplicated below.

    michaelmclaughlin

    12 Nov 14 at 12:16 am

  24. In Step# 1.b the following statement is made in reference to the necessity of a unique index on the natural-key columns: “This is necessary to minimize the run-time performance of the merge operations later in this lab.”

    I believe the sentence should either refer to minimizing the run-time or maximizing the performance but NOT to minimizing the run-time performance.

    Since this is English, I hope I am correct in my assumption / interpretation on this one… 🙂

    Gabe Ventilla

    12 Nov 14 at 3:08 am

  25. Step #3.e has the following typo: “This contains the necessary seeding code that lets you SPECIFIC rules and populate the ACCOUNT_LIST table.”

    Gabe Ventilla

    12 Nov 14 at 6:06 pm

  26. In Step #3.e the following sentence precedes the last validation query: “After you create the ACCOUNT_LIST table and the SEED_ACCOUNT_LIST procedure. Then, call the SEED_ACCOUNT_LIST procedure to populate the table.”

    The sentence should be reworked a bit so it makes more sense.

    Also, it would fit much better (contextually) above the following line of code:

    EXECUTE seed_account_list();

    Gabe Ventilla

    12 Nov 14 at 6:23 pm

  27. In Step #3.g, I can see the following line as the first line of instruction and wonder if it is there by mistake: “http://michaelmclaughlin.info/db1/lesson-7-aggregation/lab-9-oracle-group/”

    Gabe Ventilla

    12 Nov 14 at 6:31 pm

  28. In Step #3g, I think it would be helpful to clarify that the PL*SQL code will update the ACCOUNT_NUMBER column values of the MEMBER table to match the ACCOUNT_NUMBER column values in the ACCOUNT_LIST table. Right now the explanation seems a bit ambiguous as both tables have an ACCOUNT_NUMBER column and the explanation refers to the two columns without specifically mentioning the MEMBER table, as follows:

    “This contains the necessary code to consume values from the ACCOUNT_LIST table and update incorrect ACCOUNT_NUMBER column values with correct values from the ACCOUNT_LIST table”

    Gabe Ventilla

    12 Nov 14 at 6:43 pm

  29. In Step #3.g the following sentence precedes the last validation query: “After you create the ACCOUNT_LIST table, you update the MEMBER table by calling the UPDATE_MEMBER_ACCOUNT procedure. to populate the table.”

    The sentence is cut in half accidentally.

    Also, it would fit much better (contextually) above the following line of code: EXECUTE update_member_account();

    Gabe Ventilla

    12 Nov 14 at 7:04 pm

  30. In Step #3.g, the last line of instruction says “After updating the MEMBER table, you are ready to move forward to the next lab.”

    Instead of “next lab”, it should say “next step”…

    Gabe Ventilla

    12 Nov 14 at 7:08 pm

  31. Gabe, I’ve fixed it. Thanks.

    michaelmclaughlin

    13 Nov 14 at 1:00 am

  32. Gabe, There are two contexts because of the expand and contract segments, which is why I simply fixed the sentence. I also added a sentence for clarity on the confirmation query.

    michaelmclaughlin

    13 Nov 14 at 1:37 am

  33. Gabe, Great catch, I’ve applied it to the Oracle and MySQL labs.

    michaelmclaughlin

    13 Nov 14 at 1:43 am

  34. Gabe, Step 3(e) is fixed.

    michaelmclaughlin

    13 Nov 14 at 1:48 am

  35. Gabe, I must have introduced the URL while correcting something else in 3(g). I’ve replace the paragraph from an earlier copy.

    michaelmclaughlin

    13 Nov 14 at 1:50 am

  36. Gabe, I believe that step 3(e) is fixed.

    michaelmclaughlin

    13 Nov 14 at 1:51 am

  37. Gabe, I concur and have fixed it.

    michaelmclaughlin

    13 Nov 14 at 2:03 am

  38. In step #4, now that the .csv files are preloaded with the image, the following sentences have become outdated:

    “Unzip the transaction_upload.csv file, and put the file in the /u01/app/oracle/upload directory.”

    “Assuming you’ve created the upload directory as the root user, copy all *.csv files to this directory.”

    Gabe Ventilla

    13 Nov 14 at 2:32 am

  39. Gabe, While the Fedora image is the preferred approach, some students choose to install their own instance. I’ve modified the sentences to accommodate both approaches.

    michaelmclaughlin

    13 Nov 14 at 2:01 pm

  40. In the table description of transaction_upload, I noticed that “TRANSACTION_TYPEVARCHAR2(30) should be changed to “…VARCHAR2(14)” in order to match the size specification of this column in Step #4.

    Gabe Ventilla

    4 Dec 14 at 1:11 am

  41. Gabe, thanks. I believe it’s fixed.

    michaelmclaughlin

    21 Dec 14 at 5:16 pm

  42. step 4

    the second to last example output table (external import table) it shows this on the first line

    CREATE TABLE “VIDEO”.”TRANSACTION_UPLOAD”

    “VIDEO” should be “STUDENT”.

    Or at least I hope so because that is what I’m getting.

    Joseph Tracy

    5 Mar 15 at 8:42 pm

  43. Joseph, nice catch. It’s fixed.

    michaelmclaughlin

    6 Mar 15 at 11:09 pm

  44. You’re missing the 1, 3, 5 day rental for common_lookup in step 2. This is MySQL:

    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	
    RENTAL_ITEM	RENTAL_ITEM_TYPE	1-DAY RENTAL	1-Day Rental	1
    RENTAL_ITEM	RENTAL_ITEM_TYPE	3-DAY RENTAL	3-Day Rental	3
    RENTAL_ITEM	RENTAL_ITEM_TYPE	5-DAY RENTAL	5-Day Rental	6

    Jeremy

    13 Mar 15 at 11:21 am

  45. Jeremy, I fixed that earlier in Lab #9.

    michaelmclaughlin

    24 Mar 15 at 9:58 pm

  46. Bro Mclaughlin, the image verification query in Step 1a calls the transaction_amount as a NUMBER when it should be a FLOAT. Could this be fixed to avoid confusion with the instructions?

    Robert Collins

    4 Nov 15 at 4:30 pm

  47. Solution for step one shows transaction_amount as a number rather than a float.

    Anon

    10 Nov 15 at 5:30 pm

  48. Robert, The FLOAT just means a DECIMAL with a varying scale. You can simply use a NUMBER data type in Oracle.

    michaelmclaughlin

    17 Nov 15 at 2:55 am

  49. Anon, Yes, you should use the NUMBER data type in Oracle or DECIMAL data type in MySQL.

    michaelmclaughlin

    17 Nov 15 at 2:57 am

Leave a Reply