Instructions
Lab #6: SQL UPDATE
Statement
Learn how to use ALTER TABLE
and UPDATE
statements to modify an existing table with data already inserted into the table.
Lab Description
[88 points] Click the Lab Instructions link to open the instructions inside the current webpage.
Lab Instructions →
You begin the lab by calling your prior lab file. You should write the solution for this lab inside the apply_oracle_lab6.sql
script file in the /home/student/Data/cit225/lab6
directory, as shown:
-- Run the prior lab script.
@/home/student/Data/cit225/oracle/lab5/apply_oracle_lab5.sql
SPOOL apply_oracle_lab5.txt
... insert code here ...
SPOOL OFF |
- [4 points] Add the
RENTAL_ITEM_PRICE
andRENTAL_ITEM_TYPE
columns to theRENTAL_ITEM
table. Both columns should use aNUMBER
data type.While you will place a
NOT NULL
constraint on the two columns, you can’t do that immediately because there are already rows in the table. TheALTER
statement adds the two new columns to the table’s structure. The contents of the new columns will be empty or null for all existing rows.You can add the data later and then you can use the
ALTER
statement to addNOT NULL
constraints to the following columns of theRENTAL_ITEM
table:- The
RENTAL_ITEM_PRICE
column. - The
RENTAL_ITEM_TYPE
columns.
- The
Instruction Details →
There are several queries to write in this lab. They’re organized by the type of join and syntax pattern.
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. |
- [4 points] Create the
PRICE
table as per the specification qualified below. The specification requires that you add a'Y'
or'N'
against theACTIVE_FLAG
column. You should useYN_PRICE
for the name of theCHECK
constraint.
Instruction Details →
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. |
- [48 points] You have four parts to complete for this step. They are:
- [2 points] Rename the
ITEM_RELEASE_DATE
column of theITEM
table toRELEASE_DATE
. - [6 points] Insert three new DVD releases into the
ITEM
table. - [26 points] Insert a new member account with three contacts.
- [14 points] Insert two rows into the
RENTAL
table with a dependent row for each in theRENTAL_ITEM
table; and one row into theRENTAL
table with two dependent rows in theRENTAL_ITEM
table.
- [2 points] Rename the
Instruction Details →
- Rename the
ITEM_RELEASE_DATE
column of theITEM
table toRELEASE_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. |
- Insert three new DVD releases into the
ITEM
table. You should insert a value in theRELEASE_DATE
column that should always be less than 31 days during the course. You may want to check the syntax for inserting into theITEM
table, which is found in thelib2/seed/item_inserts.sql
script.The easiest way to insert a value that is less than 31 days from today for your new rentals is:
- A
(TRUNC(SYSDATE) - 1)
value for allRELEASE_DATE
column values in theITEM
table.
- A
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. |
- Insert a new row in the
MEMBER
table, and three new rows in theCONTACT
,ADDRESS
,STREET_ADDRESS
, andTELEPHONE
tables. The new contacts should be Harry, Ginny, and Lily Luna Potter. Please remember to check theseed_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, which follows the pattern introduced bylib2/seed/group_account3.sql
script from Lab #4:- Insert a new
MEMBER
row for the Potter family. - Insert a new
CONTACT
row for Harry Potter. - Insert a new
ADDRESS
row for Harry Potter. - Insert a new
STREET_ADDRESS
row for Harry Potter. - Insert a new
TELEPHONE
row for Harry Potter. - Repeat steps 2 through 5 for Ginny Potter.
- Repeat steps 2 through 5 for Lily Potter.
- Insert a new
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.
You should insert the following information:
New Group Membership | |||||
---|---|---|---|---|---|
Description | Column Value #1 | Column Value #2 | Column Value #3 | ||
Account # | US00011 | ||||
Account Type | Group | ||||
Credit Card Type | Discover Card | ||||
Credit Card # | 6011 0000 0000 0078 | ||||
Last Name | Potter | Potter | Potter | ||
First Name | Harry | Ginny | Lily | ||
Middle Name | Luna | ||||
Street Address | 900 E 300 N | 900 E 300 N | 900 E 300 N | ||
City | Provo | Provo | Provo | ||
Utah | Utah | Utah | Utah | ||
Area Code | 801 | 801 | 801 | ||
Telephone Number | 333-3333 | 333-3333 | 333-3333 |
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 20 21 22 | COLUMN account_number FORMAT A10 HEADING "Account|Number" COLUMN full_name FORMAT A16 HEADING "Name|(Last, First MI)" COLUMN street_address FORMAT A14 HEADING "Street Address" COLUMN city FORMAT A10 HEADING "City" COLUMN state FORMAT A10 HEADING "State" COLUMN postal_code FORMAT A6 HEADING "Postal|Code" SELECT m.account_number , c.last_name || ', ' || c.first_name || CASE WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name || ' ' END AS full_name , sa.street_address , a.city , a.state_province AS state , a.postal_code 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:
Account Name Postal Number (Last, First MI) Street Address City State Code ---------- -------------------- -------------- ---------- ---------- ------ US00011 Potter, Harry 900 E, 300 N Provo Utah 84604 US00011 Potter, Ginny 900 E, 300 N Provo Utah 84604 US00011 Potter, Lily Luna 900 E, 300 N Provo Utah 84604 3 rows selected. |
- Insert three new rows in the
RENTAL
and four new rows in theRENTAL_ITEM
tables. These rentals should be new releases, and it’s recommended that you useSYSDATE
for their release date so that they work consistently through the course. Two of the rows in theRENTAL_ITEM
table should link to the same row in theRENTAL
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 intoRENTAL
andRENTAL_ITEM
tables respectively. Like entering the new customers, you should avoid entering literal values for primary and foreign key values.You should ensure the following for the insert into the
RENTAL
table:- A
TRUNC(SYSDATE)
value for all rentals in theCHECK_OUT_DATE
column of theRENTAL
table. - A null value in the
RETURN_DATE
column of theRENTAL
table.
You may want to check the syntax for inserting into the
RENTAL
andRENTAL_ITEM
table, which is found in thelib2/seed/rental_inserts.sql
script.The order of the
INSERT
statements should be like this:- Insert a new
RENTAL
row for a new rental. - Insert a one or more
RENTAL_ITEM
rows for any rental row.
- A
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. |
- [32 points] Modify the design of the
COMMON_LOOKUP
table and make necessary data changes to support the new design. You should make the following four changes:- [4 points] Drop the
COMMON_LOOKUP_N1
andCOMMON_LOOKUP_U2
indexes. - [6 points] Add three new columns to the
COMMON_LOOKUP
table. - Add the
COMMON_LOOKUP_TABLE
column using aVARCHAR2(30)
. - Add the
COMMON_LOOKUP_COLUMN
column using aVARCHAR2(30)
. - Add the
COOMMON_LOOKUP_CODE
column using aVARCHAR2(1)
. - [8 points] Migrate the
COMMON_LOOKUP_CONTEXT
column values:- Use an
UPDATE
statement to copyCOMMON_LOOKUP_CONTEXT
column values that match table names to theCOMMON_LOOKUP_TABLE
column. - Use an
UPDATE
statement to copy an'ADDRESS'
string literal value to theCOMMON_LOOKUP_TABLE
column where theCOMMON_LOOKUP_CONTEXT
column contains a'MULTIPLE'
string literal value. - Use an
UPDATE
statement to copyCOMMON_LOOKUP_CONTEXT
column values that match table names concatenated with an ‘_TYPE
‘ string literal to theCOMMON_LOOKUP_COLUMN
column. - Use an
UPDATE
statement to copy an'ADDRESS_TYPE'
string literal value to theCOMMON_LOOKUP_COLUMN
column where theCOMMON_LOOKUP_CONTEXT
column contains a'MULTIPLE'
string literal value.
- Use an
- [4 points] Use an
INSERT
statement to add two rows for theTELEPHONE
table’s lookup values to theCOMMON_LOOKUP
table. - [8 points] Use the
ALTER TABLE
orCREATE INDEX
statements against theCOMMON_LOOKUP
table to accomplish:- Remove the
COMMON_LOOKUP_CONTEXT
column from theCOMMON_LOOKUP
table. - Add a
NOT NULL
constraint namedNN_CLOOKUP_8
to theCOMMON_LOOKUP_TABLE
column. - Add a
NOT NULL
constraint namedNN_CLOOKUP_9
to theCOMMON_LOOKUP_COLUMN
column. - Use the
CREATE
statement to add aUNIQUE INDEX
on theCOMMON_LOOKUP
table that uses theCOMMON_LOOKUP_TABLE
,COMMON_LOOKUP_COLUMN
, andCOMMON_LOOKUP_TYPE
columns.
- Remove the
- [2 points] Use an
UPDATE
statement to change theTELEPHONE_TYPE
column values, such that they hold a copy to one of the two new rows you previously added to theCOMMON_LOOKUP
table.
- [4 points] Drop the
Instruction Details →
These business logic notes qualify the reasons why you’re changing the COMMON_LOOKUP
table design and migrating existing foreign key values to new foreign key values.
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.
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 values in the TELEPHONE_TYPE
and ADDRESS_TYPE
columns. You make these changes with UPDATE
statements.
You should UPDATE
existing rows in the ADDRESS
and TELEPHONE
tables where the TELEPHONE_TYPE
or ADDRESS_TYPE
column values holds a foreign key value that matches the COMMON_LOOKUP_ID
where the COMMON_LOOKUP_CONTEXT
held a MULTIPLE
column value. The simplest approach to updating these rows uses two UPDATE
statements.
Implementation Steps:
- Drop the
COMMON_LOOKUP_N1
andCOMMON_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. |
- 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. |
- Migrate data and populate (or seed) new columns with existing data.
- Migrate
COMMON_LOOKUP_CONTEXT
column values. It is possible to make these changes one at a time, as the instructions explain. It is also possible to make multiple changes in a single query. The detail verification scripts verify the detailed steps. You may ignore them if you write a more complexUPDATE
statement. - The state of the
COMMON_LOOKUP
table before any data changes: - Update the
COMMON_LOOKUP_TABLE
column with the value of theCOMMON_LOOKUP_CONTEXT
column when theCOMMON_LOOKUP_CONTEXT
column’s value isn’t equal to a value of'MULTIPLE'
. You can click on the Detail Verification Scripts link to see the verification query and its results.Detail Verification Scripts →
COLUMN common_lookup_context FORMAT A14 HEADING "Common|Lookup Context" COLUMN common_lookup_table FORMAT A12 HEADING "Common|Lookup Table" COLUMN common_lookup_column FORMAT A18 HEADING "Common|Lookup Column" COLUMN common_lookup_type FORMAT A18 HEADING "Common|Lookup Type" SELECT common_lookup_context , common_lookup_table , common_lookup_column , common_lookup_type FROM common_lookup ORDER BY 1, 2, 3;
The results should be the following:
Common Common Common Common Lookup Context Lookup Table Lookup Column Lookup Type -------------- ------------ ------------------ ------------------ CONTACT CONTACT EMPLOYEE CONTACT CONTACT CUSTOMER ITEM ITEM DVD_WIDE_SCREEN ITEM ITEM NINTENDO_GAMECUBE ITEM ITEM BLU-RAY ITEM ITEM PLAYSTATION2 ITEM ITEM DVD_FULL_SCREEN ITEM ITEM XBOX MEMBER MEMBER VISA_CARD MEMBER MEMBER MASTER_CARD MEMBER MEMBER GROUP MEMBER MEMBER INDIVIDUAL MEMBER MEMBER DISCOVER_CARD MULTIPLE WORK MULTIPLE HOME SYSTEM_USER SYSTEM_USER INDIVIDUAL SYSTEM_USER SYSTEM_USER COST_CENTER SYSTEM_USER SYSTEM_USER SYSTEM_GROUP SYSTEM_USER SYSTEM_USER DBA SYSTEM_USER SYSTEM_USER SYSTEM_ADMIN 20 rows selected.
- Update the
COMMON_LOOKUP_TABLE
column with the value of of'ADDRESS'
when theCOMMON_LOOKUP_CONTEXT
column value is equal to'MULTIPLE'
. You can click on the Detail Verification Scripts link to see the verification query and its results.Detail Verification Scripts →
COLUMN common_lookup_context FORMAT A14 HEADING "Common|Lookup Context" COLUMN common_lookup_table FORMAT A12 HEADING "Common|Lookup Table" COLUMN common_lookup_column FORMAT A18 HEADING "Common|Lookup Column" COLUMN common_lookup_type FORMAT A18 HEADING "Common|Lookup Type" SELECT common_lookup_context , common_lookup_table , common_lookup_column , common_lookup_type FROM common_lookup ORDER BY 1, 2, 3;
The results should be the following:
Common Common Common Common Lookup Context Lookup Table Lookup Column Lookup Type -------------- ------------ ------------------ ------------------ CONTACT CONTACT EMPLOYEE CONTACT CONTACT CUSTOMER ITEM ITEM DVD_WIDE_SCREEN ITEM ITEM NINTENDO_GAMECUBE ITEM ITEM BLU-RAY ITEM ITEM PLAYSTATION2 ITEM ITEM DVD_FULL_SCREEN ITEM ITEM XBOX MEMBER MEMBER VISA_CARD MEMBER MEMBER MASTER_CARD MEMBER MEMBER GROUP MEMBER MEMBER INDIVIDUAL MEMBER MEMBER DISCOVER_CARD MULTIPLE ADDRESS WORK MULTIPLE ADDRESS HOME SYSTEM_USER SYSTEM_USER INDIVIDUAL SYSTEM_USER SYSTEM_USER COST_CENTER SYSTEM_USER SYSTEM_USER SYSTEM_GROUP SYSTEM_USER SYSTEM_USER DBA SYSTEM_USER SYSTEM_USER SYSTEM_ADMIN 20 rows selected.
- Create
COMMON_LOOKUP_COLUMN
column values based on references to existing data in theCOMMON_LOOKUP
table by following these rules: - Update the
COMMON_LOOKUP_COLUMN
column with the value of theCOMMON_LOOKUP_CONTEXT
column and a'_TYPE'
string where theCOMMON_LOOKUP_CONTEXT
column contains a valid table name. You can click on the Detail Verification Scripts link to see the verification query and its results.Detail Verification Scripts →
COLUMN common_lookup_context FORMAT A14 HEADING "Common|Lookup Context" COLUMN common_lookup_table FORMAT A12 HEADING "Common|Lookup Table" COLUMN common_lookup_column FORMAT A18 HEADING "Common|Lookup Column" COLUMN common_lookup_type FORMAT A18 HEADING "Common|Lookup Type" SELECT common_lookup_context , common_lookup_table , common_lookup_column , common_lookup_type FROM common_lookup WHERE common_lookup_table IN (SELECT table_name FROM user_tables) ORDER BY 1, 2, 3;
The results should be the following:
Common Common Common Common Lookup Context Lookup Table Lookup Column Lookup Type -------------- ------------ ------------------ ------------------ CONTACT CONTACT CONTACT_TYPE EMPLOYEE CONTACT CONTACT CONTACT_TYPE CUSTOMER ITEM ITEM ITEM_TYPE DVD_WIDE_SCREEN ITEM ITEM ITEM_TYPE NINTENDO_GAMECUBE ITEM ITEM ITEM_TYPE BLU-RAY ITEM ITEM ITEM_TYPE PLAYSTATION2 ITEM ITEM ITEM_TYPE DVD_FULL_SCREEN ITEM ITEM ITEM_TYPE XBOX MEMBER MEMBER MEMBER_TYPE VISA_CARD MEMBER MEMBER MEMBER_TYPE MASTER_CARD MEMBER MEMBER MEMBER_TYPE GROUP MEMBER MEMBER MEMBER_TYPE INDIVIDUAL MEMBER MEMBER MEMBER_TYPE DISCOVER_CARD MULTIPLE ADDRESS WORK MULTIPLE ADDRESS HOME SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE INDIVIDUAL SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE COST_CENTER SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE SYSTEM_GROUP SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE DBA SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE SYSTEM_ADMIN 20 rows selected.
- Update the
COMMON_LOOKUP_COLUMN
column with the value of'ADDRESS_TYPE'
string where theCOMMON_LOOKUP_CONTEXT
column contains aMULTIPLE
string. You can click on the Detail Verification Scripts link to see the verification query and its results. - The table will look like the following after migrating
COMMON_LOOKUP_CONTEXT
values to theCOMMON_LOOKUP_TABLE
andCOMMON_LOOKUP_COLUMN
column values:You can click on the Detail Verification Scripts link to see the verification query and its results.
Detail Verification Scripts →
COLUMN common_lookup_context FORMAT A14 HEADING "Common|Lookup Context" COLUMN common_lookup_table FORMAT A12 HEADING "Common|Lookup Table" COLUMN common_lookup_column FORMAT A18 HEADING "Common|Lookup Column" COLUMN common_lookup_type FORMAT A18 HEADING "Common|Lookup Type" SELECT common_lookup_context , common_lookup_table , common_lookup_column , common_lookup_type FROM common_lookup WHERE common_lookup_table IN (SELECT table_name FROM user_tables) ORDER BY 1, 2, 3;
The results should be the following:
Common Common Common Common Lookup Context Lookup Table Lookup Column Lookup Type -------------- ------------ ------------------ ------------------ CONTACT CONTACT CONTACT_TYPE EMPLOYEE CONTACT CONTACT CONTACT_TYPE CUSTOMER ITEM ITEM ITEM_TYPE DVD_WIDE_SCREEN ITEM ITEM ITEM_TYPE NINTENDO_GAMECUBE ITEM ITEM ITEM_TYPE BLU-RAY ITEM ITEM ITEM_TYPE PLAYSTATION2 ITEM ITEM ITEM_TYPE DVD_FULL_SCREEN ITEM ITEM ITEM_TYPE XBOX MEMBER MEMBER MEMBER_TYPE VISA_CARD MEMBER MEMBER MEMBER_TYPE MASTER_CARD MEMBER MEMBER MEMBER_TYPE GROUP MEMBER MEMBER MEMBER_TYPE INDIVIDUAL MEMBER MEMBER MEMBER_TYPE DISCOVER_CARD MULTIPLE ADDRESS ADDRESS_TYPE WORK MULTIPLE ADDRESS ADDRESS_TYPE HOME SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE INDIVIDUAL SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE COST_CENTER SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE SYSTEM_GROUP SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE DBA SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE SYSTEM_ADMIN 20 rows selected.
You can click on the Detail Verification Scripts link to see the verification query and its full pre-change results.
Detail Verification Scripts →
COLUMN common_lookup_context FORMAT A14 HEADING "Common|Lookup Context" COLUMN common_lookup_table FORMAT A12 HEADING "Common|Lookup Table" COLUMN common_lookup_column FORMAT A18 HEADING "Common|Lookup Column" COLUMN common_lookup_type FORMAT A18 HEADING "Common|Lookup Type" SELECT common_lookup_context , common_lookup_table , common_lookup_column , common_lookup_type FROM common_lookup ORDER BY 1, 2, 3; |
The results should be the following:
Common Common Common Common Lookup Context Lookup Table Lookup Column Lookup Type -------------- ------------ ------------------ ------------------ CONTACT EMPLOYEE CONTACT CUSTOMER ITEM DVD_WIDE_SCREEN ITEM NINTENDO_GAMECUBE ITEM BLU-RAY ITEM PLAYSTATION2 ITEM DVD_FULL_SCREEN ITEM XBOX MEMBER VISA_CARD MEMBER MASTER_CARD MEMBER GROUP MEMBER INDIVIDUAL MEMBER DISCOVER_CARD MULTIPLE WORK MULTIPLE HOME SYSTEM_USER INDIVIDUAL SYSTEM_USER COST_CENTER SYSTEM_USER SYSTEM_GROUP SYSTEM_USER DBA SYSTEM_USER SYSTEM_ADMIN 20 rows selected. |
- Add two new rows to the
COMMON_LOOKUP
table to support the'HOME'
and'WORK'
possibilities for theTELEPHONE_TYPE
column. The following shows you what to insert into theCOMMON_LOOKUP
table:Table Name: COMMON_LOOKUP Table Column Code Type Meaning TEELEPHONE TELEPHONE_TYPE HOME Home TELEPHONE TELEPHONE_TYPE WORK Work; You should insert the required two rows by using the following named notation signature for the
INSERT
statement:INSERT INTO common_lookup ( common_lookup_id , common_lookup_table , common_lookup_column , common_lookup_type , common_lookup_meaning , created_by , creation_date , last_updated_by , last_update_date ) VALUES ( ... );
The
COMMON_LOOKUP
table should look like the following after you insert two new rows in the table. You can click on the Detail Verification Scripts link to see the verification query and its results. You can click on the Detail Verification Scripts link to see the verification query and its results.Detail Verification Scripts →
You can use the following query to verify the contents of the
COMMON_LOOKUP
table.COLUMN common_lookup_context FORMAT A14 HEADING "Common|Lookup Context" COLUMN common_lookup_table FORMAT A12 HEADING "Common|Lookup Table" COLUMN common_lookup_column FORMAT A18 HEADING "Common|Lookup Column" COLUMN common_lookup_type FORMAT A18 HEADING "Common|Lookup Type" SELECT common_lookup_context , common_lookup_table , common_lookup_column , common_lookup_type FROM common_lookup WHERE common_lookup_table IN (SELECT table_name FROM user_tables) ORDER BY 1, 2, 3;
The query should return the following:
Common Common Common Common Lookup Context Lookup Table Lookup Column Lookup Type -------------- ------------ ------------------ ------------------ CONTACT CONTACT CONTACT_TYPE EMPLOYEE CONTACT CONTACT CONTACT_TYPE CUSTOMER ITEM ITEM ITEM_TYPE DVD_WIDE_SCREEN ITEM ITEM ITEM_TYPE NINTENDO_GAMECUBE ITEM ITEM ITEM_TYPE PLAYSTATION2 ITEM ITEM ITEM_TYPE XBOX ITEM ITEM ITEM_TYPE BLU-RAY ITEM ITEM ITEM_TYPE DVD_FULL_SCREEN MEMBER MEMBER MEMBER_TYPE VISA_CARD MEMBER MEMBER MEMBER_TYPE MASTER_CARD MEMBER MEMBER MEMBER_TYPE DISCOVER_CARD MEMBER MEMBER MEMBER_TYPE GROUP MEMBER MEMBER MEMBER_TYPE INDIVIDUAL MULTIPLE ADDRESS ADDRESS_TYPE WORK MULTIPLE ADDRESS ADDRESS_TYPE HOME SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE INDIVIDUAL SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE COST_CENTER SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE SYSTEM_GROUP SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE SYSTEM_ADMIN SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE DBA TELEPHONE TELEPHONE_TYPE HOME TELEPHONE TELEPHONE_TYPE WORK
- At this point, you need to fix the
COMMON_LOOKUP
table’s structure by doing the following four things to the table. - You must get rid of old and now obsolete
COMMON_LOOKUP_CONTEXT
column. - Add a
NOT NULL
constraint to theCOMMON_LOOKUP_TABLE
column. - Add a
NOT NULL
constraint to theCOMMON_LOOKUP_COLUMN
column. - Add a unique index using the following three columns:
COMMON_LOOKUP_TABLE
COMMON_LOOKUP_COLUMN
COMMON_LOOKUP_TYPE
- You can verify the new structure of the
COMMON_LOOKUP
table with the following query: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;
The query should return the following description of the
COMMON_LOOKUP
table:Column Table Name COLUMN_ID 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.
- You can verify the properly named
NOT NULL
constraints with the following query:COLUMN constraint_name FORMAT A22 HEADING "Constraint Name" COLUMN search_condition FORMAT A36 HEADING "Search Condition" COLUMN constraint_type FORMAT A10 HEADING "Constraint|Type" SELECT uc.constraint_name , uc.search_condition , uc.constraint_type 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('common_lookup') AND uc.constraint_type IN (UPPER('c'),UPPER('p')) ORDER BY uc.constraint_type DESC , uc.constraint_name;
The query should return the following description of the
COMMON_LOOKUP
table:Constraint Constraint Name Search Condition Type ---------------------- ------------------------------------ ---------- PK_CLOOKUP_1 P NN_CLOOKUP_2 "COMMON_LOOKUP_TYPE" IS NOT NULL C NN_CLOOKUP_3 "COMMON_LOOKUP_MEANING" IS NOT NULL C NN_CLOOKUP_4 "CREATED_BY" IS NOT NULL C NN_CLOOKUP_5 "CREATION_DATE" IS NOT NULL C NN_CLOOKUP_6 "LAST_UPDATED_BY" IS NOT NULL C NN_CLOOKUP_7 "LAST_UPDATE_DATE" IS NOT NULL C NN_CLOOKUP_8 "COMMON_LOOKUP_TABLE" IS NOT NULL C NN_CLOOKUP_9 "COMMON_LOOKUP_COLUMN" IS NOT NULL C 9 rows selected.
- You can verify the
COMMON_LOOKUP
table’s new unique natural key index with the following query:COLUMN sequence_name FORMAT A22 HEADING "Sequence Name" COLUMN column_position FORMAT 999 HEADING "Column|Position" COLUMN column_name FORMAT A22 HEADING "Column|Name" SELECT UI.index_name , uic.column_position , uic.column_name FROM user_indexes UI INNER JOIN user_ind_columns uic ON UI.index_name = uic.index_name AND UI.table_name = uic.table_name WHERE UI.table_name = UPPER('common_lookup') ORDER BY UI.index_name , uic.column_position;
The query should return the following description for the
COMMON_LOOKUP
table’s natural key index:Column Column Index Name Position Name -------------------- -------- ---------------------- CLOOKUP_U1 1 COMMON_LOOKUP_TABLE CLOOKUP_U1 2 COMMON_LOOKUP_COLUMN CLOOKUP_U1 3 COMMON_LOOKUP_TYPE PK_CLOOKUP_1 1 COMMON_LOOKUP_ID
- The last step requires that you update incorrect foreign key values in the telephone table. They are invalid because the rows in the
COMMON_LOOKUP
table that once supported both theADDRESS
andTELEPHONE
tables now only supports theADDRESS
table. You need to copy the values of the correctCOMMON_LOOKUP_ID
column values (the surrogate primary key column) into theTELEPHONE_TYPE
column of theTELEPHONE
table.You can find the obsolete values with a query like this:
SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'ADDRESS' AND common_lookup_type = 'HOME';
You can find the correct values with a query like this:
SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'TELEPHONE' AND common_lookup_type = 'HOME';
You should structure and
UPDATE
statement that sets theTELEPHONE_TYPE
column values equal to the correct values where they are currently the obsolete values.Detail Verification Scripts →
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;
The results should be the following:
Common Count of Count of Common Common Lookup Foreign Primary Lookup Table Lookup Column 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.
Detail Verification Scripts →
Test Case
Click the Test Case Instructions link to open the test case instructions inside the current webpage.
Test Case Instructions →
After you write the apply_oracle_lab6.sql
script, you should navigate to the /home/student/Data/cit225/oracle/lab6
directory. You call the sqlplus
utility from the same directory and then you run the apply_oracle_lab6.sql
script with the following syntax:
@apply_oracle_lab6.sql |
You should submit your apply_oracle_lab6.sql
script file and apply_oracle_lab6.txt
log file for a grade.