Database Tutorial

Course Tutorial Site

Site Admin

Old Lab 6

without comments

Lab #6: Oracle Assignment

You begin these steps after running the create_oracle_store.sql, the seed_oracle_store.sql, and apply_oracle_lab5.sql scripts. 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/lab5/apply_oracle_lab5.sql
 
SPOOL apply_oracle_lab6.txt
 
... insert code here ...
 
SPOOL OFF

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

  1. [2 points] Add the RENTAL_ITEM_PRICE and RENTAL_ITEM_TYPE columns to the RENTAL_ITEM table. Both columns should use a NUMBER data type in Oracle, and an int unsigned data type for MySQL. While you will place a NOT NULL constraint on the RENTAL_ITEM_PRICE and RENTAL_ITEM_TYPE columns of the RENTAL_ITEM table, you won’t do it until all rows hold data.
Table Name: RENTAL_ITEM
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
RENTAL_ITEM_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
RENTAL_ITEM_PRICE NOT NULL Integer Maximum

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

SET NULL ''
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 = 'RENTAL_ITEM'
ORDER BY 2;

It should display the following results:

TABLE_NAME     COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
RENTAL_ITEM            1 RENTAL_ITEM_ID         NOT NULL NUMBER(22)
RENTAL_ITEM            2 RENTAL_ID              NOT NULL NUMBER(22)
RENTAL_ITEM            3 ITEM_ID                NOT NULL NUMBER(22)
RENTAL_ITEM            4 CREATED_BY             NOT NULL NUMBER(22)
RENTAL_ITEM            5 CREATION_DATE          NOT NULL DATE
RENTAL_ITEM            6 LAST_UPDATED_BY        NOT NULL NUMBER(22)
RENTAL_ITEM            7 LAST_UPDATE_DATE       NOT NULL DATE
RENTAL_ITEM            8 RENTAL_ITEM_TYPE                NUMBER(22)
RENTAL_ITEM            9 RENTAL_ITEM_PRICE               NUMBER(22)
 
9 rows selected.

  1. [3 points] Create the PRICE table as per the specification qualified below with one qualification. You need to name the CHECK constraint YN_PRICE.

Table Name: PRICE
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
PRICE_ID PRIMARY KEY Integer Maximum
ITEM_ID FOREIGN KEY ITEM ITEM_ID Integer Maximum
NOT NULL
PRICE_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
ACTIVE_FLAG NOT NULL Text 1
CHECK(‘Y’,’N’)
START_DATE NOT NULL Date Date
END_DATE Date Date
AMOUNT NOT NULL 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
21

SET NULL ''
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 = 'PRICE'
ORDER BY 2;

It should display the following results:

TABLE_NAME     COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
PRICE                  1 PRICE_ID               NOT NULL NUMBER(22)
PRICE                  2 ITEM_ID                NOT NULL NUMBER(22)
PRICE                  3 PRICE_TYPE                      NUMBER(22)
PRICE                  4 ACTIVE_FLAG            NOT NULL VARCHAR2(1)
PRICE                  5 START_DATE             NOT NULL DATE
PRICE                  6 END_DATE                        DATE
PRICE                  7 AMOUNT                 NOT NULL NUMBER(22)
PRICE                  8 CREATED_BY             NOT NULL NUMBER(22)
PRICE                  9 CREATION_DATE          NOT NULL DATE
PRICE                 10 LAST_UPDATED_BY        NOT NULL NUMBER(22)
PRICE                 11 LAST_UPDATE_DATE       NOT NULL DATE
 
11 rows selected.

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

1
2
3
4
5
6
7
8
9
10
11

COLUMN constraint_name   FORMAT A16
COLUMN search_condition  FORMAT A30
SELECT   uc.constraint_name
,        uc.search_condition
FROM     user_constraints uc INNER JOIN user_cons_columns ucc
ON       uc.table_name = ucc.table_name
AND      uc.constraint_name = ucc.constraint_name
WHERE    uc.table_name = UPPER('price')
AND      ucc.column_name = UPPER('active_flag')
AND      uc.constraint_name = UPPER('yn_price')
AND      uc.constraint_type = 'C';

It should display the following results:

CONSTRAINT_NAME      SEARCH_CONDITION_VC
-------------------- ------------------------------
YN_PRICE             active_flag IN ('Y','N')
 
1 row selected.

  1. [10 points] Insert new data as follows:

  1. Rename the ITEM_RELEASE_DATE column of the ITEM table to RELEASE_DATE.

The following query checks whether you’ve renamed the column correctly:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SET NULL ''
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 = 'ITEM'
ORDER BY 2;

It should return the following:

TABLE_NAME     COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
ITEM                   1 ITEM_ID                NOT NULL NUMBER(22)
ITEM                   2 ITEM_BARCODE           NOT NULL VARCHAR2(14)
ITEM                   3 ITEM_TYPE              NOT NULL NUMBER(22)
ITEM                   4 ITEM_TITLE             NOT NULL VARCHAR2(60)
ITEM                   5 ITEM_SUBTITLE                   VARCHAR2(60)
ITEM                   6 ITEM_RATING            NOT NULL VARCHAR2(8)
ITEM                   7 RELEASE_DATE           NOT NULL DATE
ITEM                   8 CREATED_BY             NOT NULL NUMBER(22)
ITEM                   9 CREATION_DATE          NOT NULL DATE
ITEM                  10 LAST_UPDATED_BY        NOT NULL NUMBER(22)
ITEM                  11 LAST_UPDATE_DATE       NOT NULL DATE
 
11 rows selected.

  1. Insert three new DVD releases into the ITEM table. The RELEASE_DATE column’s value for new rows in the ITEM table should be less than 31 days at all times. The easiest way to achieve this requirement uses:

    • A (TRUNC(SYSDATE) - 1) value for all RELEASE_DATE column values in the ITEM table.
    • A TRUNC(SYSDATE) value for all rentals in the CHECK_OUT_DATE column of the RENTAL table.
    • A null value in the RETURN_DATE column of the RENTAL table for all new rentals.

The following query checks whether you’ve entered three compliant rentals and rental items:

1
2
3
4
5

SELECT   i.item_title
,        SYSDATE AS today
,        i.release_date
FROM     item i
WHERE   (SYSDATE - i.release_date) < 31;

It should return the following (though the titles may differ):

                          RELEASE
ITEM_TITLE     TODAY      DATE
-------------- ---------- ----------
Tron           27-MAY-14  12-MAY-14
Ender's Game   27-MAY-14  12-MAY-14
Elysium        27-MAY-14  12-MAY-14
 
3 rows selected.

  1. Insert a new row in the MEMBER table, and three new rows in the CONTACT, ADDRESS, STREET_ADDRESS, and TELEPHONE tables. The new contacts should be Harry, Ginny, and Lily Luna Potter. Please remember to check the seed_oracle_store.sql script’s logic for inserting to these tables because the right way is straightforward and the many wrong ways can make the lab extremely difficult.

    The insert statements follow a pattern. You start with the least dependent and work toward the most dependent. Typically, these would be built into a stored procedure and you would simply send all the values to it, and it would sequence the inserts into the different tables.

    You should use .NEXTVAL pseudo column calls for the primary key values and .CURRVAL pseudo column calls for the foreign key values. You should avoid entering literal values for primary and foreign key values unless you like troubleshooting your code because you will do a lot more of it with literal numeric values. The order of the INSERT statements should be like this:

    1. Insert a new MEMBER row for the Potter family.
    2. Insert a new CONTACT row for Harry Potter.
    3. Insert a new ADDRESS row for Harry Potter.
    4. Insert a new STREET_ADDRESS row for Harry Potter.
    5. Insert a new TELEPHONE row for Harry Potter.
    6. Repeat steps 2 through 5 for Ginny Potter.
    7. Repeat steps 2 through 5 for Lily Potter.

You should set the addresses for the Potter family members in the city of Provo, Utah. The addresses are critical and have an impact on Lab #7. If you choose an address other than Provo, Utah, you’ll need to add it to the AIRPORT table in Lab #8 because it is beyond the scope of subsequent lab steps.

The following query checks whether you’ve entered three compliant rentals and rental items:

1
2
3
4
5
6
7
8
9
10
11
12

COLUMN full_name FORMAT A20
COLUMN city      FORMAT A10
COLUMN state     FORMAT A10
SELECT   c.last_name || ', ' || c.first_name AS full_name
,        a.city
,        a.state_province AS state
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 street_address sa
ON       a.address_id = sa.address_id INNER JOIN telephone t
ON       c.contact_id = t.contact_id
WHERE    c.last_name = 'Potter';

It should return the following:

FULL_NAME        CITY       STATE
---------------- ---------- ----------
Potter, Harry    Provo      Utah
Potter, Ginny    Provo      Utah
Potter, Lily     Provo      Utah
 
3 rows selected.

  1. Insert three new rows in the RENTAL and four new rows in the RENTAL_ITEM tables. These rentals should be new releases, and it’s recommended that you use SYSDATE for their release date so that they work consistently through the course. Two of the rows in the RENTAL_ITEM table should link to the same row in the RENTAL table for the rental of Harry Potter.

    In other words, there you should insert data for: (a) Harry Potter renting two rental items for a 1-Day Rental period (one new release and one old release), (b) Ginny Potter renting one new release rental item for a 3-Day Rental period, and (c) Lily Luna Potter renting one new release rental item for a 5-Day Rental period.

    You should use .NEXTVAL pseudo column calls for the primary key values and .CURRVAL pseudo column calls for the foreign key values into RENTAL and RENTAL_ITEM tables respectively. Like entering the new customers, you should avoid entering literal values for primary and foreign key values. The order of the INSERT statements should be like this:

    1. Insert a new RENTAL row for a new rental.
    2. Insert a one or more RENTAL_ITEM rows for any rental row.

The following query checks whether you’ve entered three compliant rentals and rental items:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

COLUMN full_name   FORMAT A18
COLUMN rental_id   FORMAT 9999
COLUMN rental_days FORMAT A14
COLUMN rentals     FORMAT 9999
COLUMN items       FORMAT 9999
SELECT   c.last_name||', '||c.first_name||' '||c.middle_name AS full_name
,        r.rental_id
,       (r.return_date - r.check_out_date) || '-DAY RENTAL' AS rental_days
,        COUNT(DISTINCT r.rental_id) AS rentals
,        COUNT(ri.rental_item_id) AS items
FROM     rental r INNER JOIN rental_item ri
ON       r.rental_id = ri.rental_id INNER JOIN contact c
ON       r.customer_id = c.contact_id
WHERE   (SYSDATE - r.check_out_date) < 15
AND      c.last_name = 'Potter'
GROUP BY c.last_name||', '||c.first_name||' '||c.middle_name
,        r.rental_id
,       (r.return_date - r.check_out_date) || '-DAY RENTAL'
ORDER BY 2;

It should return the following:

FULL_NAME          RENTAL_ID RENTAL_DAYS    RENTALS ITEMS
------------------ --------- -------------- ------- -----
Potter, Harry           1006 1-DAY RENTAL         1     2
Potter, Ginny           1007 3-DAY RENTAL         1     1
Potter, Lily Luna       1008 5-DAY RENTAL         1     1
 
3 rows selected.

  1. [20 points] Modify the design of the COMMON_LOOKUP table, insert new data into the model, and update old non-compliant design data in the model.

Hint for Step #4

Before you shift contents, you should drop the COMMON_LOOKUP_N1 and COMMON_LOOKUP_U2 indexes. Then, you can shift the contents of one column to another column in the same row. An UPDATE statement that moves the content from one column’s value to another for all rows in a table is:

1
2

UPDATE   common_lookup
SET      common_lookup_table = common_lookup_context;

The following shows you how UPDATE an existing row before you ALTER TABLE to DROP the column:

1
2
3
4

UPDATE   common_lookup
SET      common_lookup_table = 'ITEM'
,        common_lookup_column = 'ITEM_TYPE'
WHERE    common_lookup_context = 'ITEM';

The two rows containing the MULTIPLE context values, should migrate to four rows, as shown in the following table.

Table Name: COMMON_LOOKUP
Code Lookup Table Lookup Column Lookup Context Lookup Type
Old MULTIPLE HOME
Old MULTIPLE WORK
New ADDRESS ADDRESS_TYPE HOME
New ADDRESS ADDRESS_TYPE WORK
New TELEPHONE TELEPHONE_TYPE HOME
New TELEPHONE TELEPHONE_TYPE WORK

After you’ve made these data changes in the COMMON_LOOKUP table, you need to update the old TELEPHONE_TYPE or ADDRESS_TYPE column values with the correct new values. It depends on how you do it. If you UPDATE the existing rows from MULTIPLE to TELEPHONE_TYPE, then you’ll need to update the rows in the ADDRESS table to point to the correct new rows in the COMMON_LOOKUP table.

The best practice in this case is to write two UPDATE statements. One updates the HOME values in the ADDRESS table. The other updates the WORK values in the ADDRESS table.

Implementation Steps:

  1. Drop the COMMON_LOOKUP_N1 and COMMON_LOOKUP_U2 indexes.

The following query verifies that you’ve dropped the indexes:

1
2
3
4
5
6

COLUMN table_name FORMAT A14
COLUMN index_name FORMAT A20
SELECT   table_name
,        index_name
FROM     user_indexes
WHERE    table_name = 'COMMON_LOOKUP';

It should return when you’ve removed the unique and non-unique indexes from the COMMON_LOOKUP table:

TABLE_NAME     INDEX_NAME
-------------- --------------------
COMMON_LOOKUP  PK_C_LOOKUP_1
 
1 row selected.
  1. Add three new columns to the COMMON_LOOKUP table.
  • The COMMON_LOOKUP_TABLE column.
  • The COMMON_LOOKUP_COLUMN column.
  • The COMMON_LOOKUP_CODE column.

The following query verifies that addition of the columns:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SET NULL ''
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 = 'COMMON_LOOKUP'
ORDER BY 2;

It should return:

TABLE_NAME     COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
COMMON_LOOKUP          1 COMMON_LOOKUP_ID       NOT NULL NUMBER(22)
COMMON_LOOKUP          2 COMMON_LOOKUP_CONTEXT  NOT NULL VARCHAR2(30)
COMMON_LOOKUP          3 COMMON_LOOKUP_TYPE     NOT NULL VARCHAR2(30)
COMMON_LOOKUP          4 COMMON_LOOKUP_MEANING  NOT NULL VARCHAR2(30)
COMMON_LOOKUP          5 CREATED_BY             NOT NULL NUMBER(22)
COMMON_LOOKUP          6 CREATION_DATE          NOT NULL DATE
COMMON_LOOKUP          7 LAST_UPDATED_BY        NOT NULL NUMBER(22)
COMMON_LOOKUP          8 LAST_UPDATE_DATE       NOT NULL DATE
COMMON_LOOKUP          9 COMMON_LOOKUP_TABLE             VARCHAR2(30)
COMMON_LOOKUP         10 COMMON_LOOKUP_COLUMN            VARCHAR2(30)
COMMON_LOOKUP         11 COMMON_LOOKUP_CODE              VARCHAR2(30)
 
11 rows selected.
  1. Migrate data and populate (or seed) new columns with existing data.
  • Migrate COMMON_LOOKUP_CONTEXT column values:
    • The state of the COMMON_LOOKUP table before any data changes:
    UpdateDataState01

    • Update the COMMON_LOOKUP_TABLE column with the value of the COMMON_LOOKUP_CONTEXT column when the COMMON_LOOKUP_CONTEXT column’s value isn’t equal to a value of 'MULTIPLE'.
    • Update the COMMON_LOOKUP_TABLE column with the value of of 'ADDRESS' when the COMMON_LOOKUP_CONTEXT column value is equal to 'MULTIPLE'.
  • Create COMMON_LOOKUP_COLUMN column values based on references to existing data in the COMMON_LOOKUP table by following these rules:
    • Update the COMMON_LOOKUP_COLUMN column with the value of the COMMON_LOOKUP_CONTEXT column and a '_TYPE' string when the COMMON_LOOKUP_TABLE value is 'MEMBER' and COMMON_LOOKUP_TYPE column values is 'INDIVIDUAL' or 'GROUP'.
    • Update the COMMON_LOOKUP_COLUMN column with a value of 'CREDIT_CARD_TYPE' when the COMMON_LOOKUP_TYPE column value is 'VISA_CARD', 'MASTER_CARD', or 'DISCOVER_CARD'.
    • Update the COMMON_LOOKUP_COLUMN column with a value of 'ADDRESS_TYPE' when the COMMON_LOOKUP_CONTEXT value is 'MULTIPLE'.
    • Update the COMMON_LOOKUP_COLUMN column with the value of the COMMON_LOOKUP_CONTEXT column and a '_TYPE' string when the COMMON_LOOKUP_CONTEXT value is anything other than 'MEMBER' or 'MULTIPLE'.
    • The table will look like the following after migrating COMMON_LOOKUP_CONTEXT values to the COMMON_LOOKUP_TABLE and COMMON_LOOKUP_COLUMN column values:

      • UpdateDataState02

  • Add two new rows to the COMMON_LOOKUP table to support the 'HOME' and 'WORK' possibilities for the TELEPHONE_TYPE column. (Note: This means you’ll need to change the value of foreign keys in the TELEPHONE_TYPE column of the TELEPHONE table to one of those found in the new COMMON_LOOKUP table rows.)

    • The state of the COMMON_LOOKUP table after the two INSERT statements for the TELEPHONE table.

    UpdateDataState03

  • Update the now obsolete foreign key values in the TELEPHONE_TYPE column of the TELEPHONE table to their correct values, which are found in the two new rows of the COMMON_LOOKUP table.

You should use the following formatting and query to verify completion of the re-structuring of the COMMON_LOOKUP table in this step:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SET NULL ''
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 = 'COMMON_LOOKUP'
ORDER BY 2;

It should return the following:

TABLE_NAME     COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
COMMON_LOOKUP          1 COMMON_LOOKUP_ID       NOT NULL NUMBER(22)
COMMON_LOOKUP          2 COMMON_LOOKUP_CONTEXT  NOT NULL VARCHAR2(30)
COMMON_LOOKUP          3 COMMON_LOOKUP_TYPE     NOT NULL VARCHAR2(30)
COMMON_LOOKUP          4 COMMON_LOOKUP_MEANING  NOT NULL VARCHAR2(30)
COMMON_LOOKUP          5 CREATED_BY             NOT NULL NUMBER(22)
COMMON_LOOKUP          6 CREATION_DATE          NOT NULL DATE
COMMON_LOOKUP          7 LAST_UPDATED_BY        NOT NULL NUMBER(22)
COMMON_LOOKUP          8 LAST_UPDATE_DATE       NOT NULL DATE
COMMON_LOOKUP          9 COMMON_LOOKUP_TABLE             VARCHAR2(30)
COMMON_LOOKUP         10 COMMON_LOOKUP_COLUMN            VARCHAR2(30)
COMMON_LOOKUP         11 COMMON_LOOKUP_CODE              VARCHAR2(30)
 
11 rows selected.

You should use the following formatting and query to verify completion of the data updates to the COMMON_LOOKUP table in this step:

1
2
3
4
5
6
7
8

COLUMN common_lookup_table  FORMAT A20
COLUMN common_lookup_column FORMAT A20
COLUMN common_lookup_type   FORMAT A20
SELECT   common_lookup_table
,        common_lookup_column
,        common_lookup_type
FROM     common_lookup
ORDER BY 1, 2, 3;

It should display the following results:

COMMON_LOOKUP_TABLE  COMMON_LOOKUP_COLUMN COMMON_LOOKUP_TYPE
-------------------- -------------------- --------------------
ADDRESS 	     ADDRESS_TYPE	  HOME
ADDRESS 	     ADDRESS_TYPE	  WORK
CONTACT 	     CONTACT_TYPE	  CUSTOMER
CONTACT 	     CONTACT_TYPE	  EMPLOYEE
ITEM		     ITEM_TYPE		  BLU-RAY
ITEM		     ITEM_TYPE		  DVD_FULL_SCREEN
ITEM		     ITEM_TYPE		  DVD_WIDE_SCREEN
ITEM		     ITEM_TYPE		  NINTENDO_GAMECUBE
ITEM		     ITEM_TYPE		  PLAYSTATION2
ITEM		     ITEM_TYPE		  XBOX
MEMBER		     CREDIT_CARD_TYPE	  DISCOVER_CARD
MEMBER		     CREDIT_CARD_TYPE	  MASTER_CARD
MEMBER		     CREDIT_CARD_TYPE	  VISA_CARD
MEMBER		     MEMBER_TYPE	  GROUP
MEMBER		     MEMBER_TYPE	  INDIVIDUAL
SYSTEM_USER	     SYSTEM_USER_TYPE	  DBA
SYSTEM_USER	     SYSTEM_USER_TYPE	  SYSTEM_ADMIN
TELEPHONE	     TELEPHONE_TYPE	  HOME
TELEPHONE	     TELEPHONE_TYPE	  WORK
 
19 rows selected.

After checking the new structure and data set of the COMMON_LOOKUP table, you should check the migration of data in the ADDRESS and TELEPHONE tables with the following query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

COLUMN common_lookup_table  FORMAT A14 HEADING "Common|Lookup Table"
COLUMN common_lookup_column FORMAT A14 HEADING "Common|Lookup Column"
COLUMN common_lookup_type   FORMAT A8  HEADING "Common|Lookup|Type"
COLUMN count_dependent      FORMAT 999 HEADING "Count of|Foreign|Keys"
COLUMN count_lookup         FORMAT 999 HEADING "Count of|Primary|Keys"
SELECT   cl.common_lookup_table
,        cl.common_lookup_column
,        cl.common_lookup_type
,        COUNT(a.address_id) AS count_dependent
,        COUNT(DISTINCT cl.common_lookup_table) AS count_lookup
FROM     address a RIGHT JOIN common_lookup cl
ON       a.address_type = cl.common_lookup_id
WHERE    cl.common_lookup_table = 'ADDRESS'
AND      cl.common_lookup_column = 'ADDRESS_TYPE'
AND      cl.common_lookup_type IN ('HOME','WORK')
GROUP BY cl.common_lookup_table
,        cl.common_lookup_column
,        cl.common_lookup_type
UNION
SELECT   cl.common_lookup_table
,        cl.common_lookup_column
,        cl.common_lookup_type
,        COUNT(t.telephone_id) AS count_dependent
,        COUNT(DISTINCT cl.common_lookup_table) AS count_lookup
FROM     telephone t RIGHT JOIN common_lookup cl
ON       t.telephone_type = cl.common_lookup_id
WHERE    cl.common_lookup_table = 'TELEPHONE'
AND      cl.common_lookup_column = 'TELEPHONE_TYPE'
AND      cl.common_lookup_type IN ('HOME','WORK')
GROUP BY cl.common_lookup_table
,        cl.common_lookup_column
,        cl.common_lookup_type;

It should display the following results:

                                             Count of Count of
Common         Common         Common          Foreign  Primary
Lookup Table   Lookup Column  Lookup Type        Keys     Keys
-------------- -------------- -------------- -------- --------
ADDRESS        ADDRESS_TYPE   HOME                 15        1
ADDRESS        ADDRESS_TYPE   WORK                  0        1
TELEPHONE      TELEPHONE_TYPE HOME                 15        1
TELEPHONE      TELEPHONE_TYPE WORK                  0        1
 
4 rows selected.
  1. Remove obsolete columns, apply not null constraints, and create a new unique index for the natural key of the COMMON_LOOKUP table.
  • Drop the now obsolete COMMON_LOOKUP_CONTEXT column from COMMON_LOOKUP table.
  • After seeding the new columns in the COMMON_LOOKUP table with data, ALTER the any new columns of the natural key for COMMON_LOOKUP table with NOT NULL constraints. They are:
    • The COMMON_LOOKUP_TABLE column.
    • The COMMON_LOOKUP_COLUMN column.
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

    SET NULL ''
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 = 'COMMON_LOOKUP'
ORDER BY 2;

    It should return the following:

    TABLE_NAME     COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
COMMON_LOOKUP          1 COMMON_LOOKUP_ID       NOT NULL NUMBER(22)
COMMON_LOOKUP          2 COMMON_LOOKUP_TYPE     NOT NULL VARCHAR2(30)
COMMON_LOOKUP          3 COMMON_LOOKUP_MEANING  NOT NULL VARCHAR2(30)
COMMON_LOOKUP          4 CREATED_BY             NOT NULL NUMBER(22)
COMMON_LOOKUP          5 CREATION_DATE          NOT NULL DATE
COMMON_LOOKUP          6 LAST_UPDATED_BY        NOT NULL NUMBER(22)
COMMON_LOOKUP          7 LAST_UPDATE_DATE       NOT NULL DATE
COMMON_LOOKUP          8 COMMON_LOOKUP_TABLE    NOT NULL VARCHAR2(30)
COMMON_LOOKUP          9 COMMON_LOOKUP_COLUMN   NOT NULL VARCHAR2(30)
COMMON_LOOKUP         10 COMMON_LOOKUP_CODE              VARCHAR2(30)
 
10 rows selected.
  • Create a unique index across the COMMON_LOOKUP_TABLE, COMMON_LOOKUP_COLUMN, and COMMON_LOOKUP_TYPE columns found in the COMMON_LOOKUP table.
1
2
3
4
5
6

COLUMN table_name FORMAT A14
COLUMN index_name FORMAT A20
SELECT   table_name
,        index_name
FROM     user_indexes
WHERE    table_name = 'COMMON_LOOKUP';

It should return the following:

TABLE_NAME     INDEX_NAME
-------------- --------------------
COMMON_LOOKUP  COMMON_LOOKUP_U2
COMMON_LOOKUP  PK_C_LOOKUP_1
 
2 rows selected.

Implementation Note: The COMMON_LOOKUP table uses a single lookup row to solve problems across multiple tables. This makes it non-conforming to our normalized model, where a small set of rows should answer questions about a column in a single table. You need to change this table structure, while preserving the data. This means you can’t drop and recreate it. Please read the business rule and system logic to understand the issues.

Business Rule: You want sets of lookup values that are uniquely identified across the table, column, type values in the COMMON_LOOKUP table.

System Logic: You need to modify the table to align it with the business rule. This means: (a) removing a unique index that constrains the table, (b) adding the appropriate columns, (c) migrating existing data, and (d) constraining the columns by a unique index or table-level constraint key. After you’ve updated the data, you’ll need to drop the COMMON_LOOKUP_CONTEXT column because your changes have effectively deprecated the column. The new UNIQUE constraint will be across the COMMON_LOOKUP_TABLE, COMMON_LOOKUP_COLUMN, and COMMON_LOOKUP_TYPE columns.

Written by michaelmclaughlin

May 4th, 2018 at 4:55 pm

Posted in