Lab #2 : Oracle Tables at Database Tutorial

Lab #2 : Oracle Tables

Lab #2: Create Tables

Objectives

The lab teaches you how to work SQL commands to create tables. It requires that you have completed Lab 1 because you will need to edit and run both unit and integration test scripts.

You are provided with scripts that create ten tables. All the tables have dependencies on one another. You generally create tables in a specific order. The order starts with the table that has the least number of dependencies. The second table has the next least number of dependencies and the third through the last follows the same pattern until you get to the table with the most dependencies.

You will work with these ten tables in this and subsequent labs. A single script file should create each table. The SQL script files are unit test programs. Each of the unit test programs writes its own log file.

An eleventh SQL script is an integration script, and it calls all the unit tests. You will download eleven SQL script files for this lab from github.com and put them into a new /lib2 directory in your Linux instance.

After you download and position the script files, you will test them by calling the integration script file. Then, you will copy these unit test programs into the lab2 directory where you will edit each of the unit test scripts to create ten nearly duplicate tables. The last step requires you to copy the base integration program into the lab2 directory where you will edit it to run your modified copies in the lab2 directory.

The lab teaches you how to edit and re-inforces Lab 1 where you learned how to run unit and integration test scripts in SQL*Plus (sqlplus). Working with these scripts should help you learn the following:

How to edit a unit test script
How to set a local bind variable
How to conditionally drop a table
How to create a table with database constraints
How to create a sequence for a table’s surrogate primary key
How to create an index for a table
How to change the definition of an existing table
How to query the data dictionary to discover table definitions

Business Scenario

Building data-centric application software requires a place to put the data. You need to create tables to hold the data whether you choose to use a relational, document, or columnar database.

The lab is designed to teach you to create tables in a relational database. Tables should hold a single subject. Well designed tables in domain modeling should include two unique keys, where a unique key is a one or more columns that uniquely define each row in the table. One unique key is a natural key made up of columns that define each instance of data stored in a table. The other unique key is a surrogate key, which is a unique ID. The unique ID is typically populated by a number generated by a sequence that increments by one. The surrogate key acts like an indirect reference to the natural key of the table.

Domain modeling adds the overhead of a surrogate (second unique) key because that key lets you evolve the natural key when you discover that table doesn’t uniquely describe a subject. The natural key provides a set of meaningful information that would let a business user find data about a customer, order, or purchase.

For example, you may want to query a customer by their membership number, credit card number and type, or full name. The following form would let you enter the data to search for the customer.

A form like the preceding forms the basis for a query that uses natural key components from more than one table to identify a correct customer record. The customer service team member may query a record like this and then change the customer’s credit card number or type of card fter querying the results. In today’s world, it’s much more likely the customer would query their own data and change their own credit card information.

The customer would never use the surrogate key value because it’s designed to organize table relationships and only meaningful toward resolving queries with natural key data values.

Table design and creation also requires you to identify whether columns are mandatory or optional. Mandatory tables are constrained by a NOT NULL constraint. Optional tables are not constrained, which lets you write a row without a middle name because not everyone has a middle name. Likewise, tables require UNIQUE constraints for the set of columns that defines a table’s natural key; and PRIMARY KEY constraints for surrogate key columns so that you can create FOREIGN KEY constraints to map relationships between rows of data.

Help Section

There are four key elements to creating tables in a relational database. Table names, column names, constraint names, and types are the four key elements of tables.

The name of any table must be unique within a database (a database management system may and typically does hold more than one database). Likewise, column names must be unique within any table. Constraint names are like table names because they must be unique within a database. Lastly, data types must be defined before you use them to create or modify a table definition.

You are asked to review the following preparation material:

Read the Database Constraints post

Read Data Definition Language (DDL) posts

The reading material gives you a foundation of knowledge about creating tables, constraints, indexes, and sequences.

Instructional Material →

The supplemental materials depend on your reading and understanding the preparation material qualified earlier in this lab. The coding segments below show you:

How to create the table_name bind variable
How to use the :table_name bind variable in a conditional DROP statement
How to create a chicken table without any constraints
How to query the structure of the chicken table with a :table_name bind variable
How to query the constraints of the chicken table with a :table_name bind variable
How to create a chicken_s sequence

The instructions walk you through the steps above.

Create & assign a bind variable

Define a bind variable and assign a value to the bind variable. You define the variable by using a VARIABLE keyword, a table_name identifier, and a VARCHAR2(30) data type. The data type requires that you qualify the length of the string.

You must assign values to bind variables inside PL/SQL anonymous blocks. You must precede a table_name bind variable name with a colon when you want to reference it inside a PL/SQL block.

-- Define a bind variable.
VARIABLE table_name VARCHAR2(30)
 
-- Assign an uppercase string to a session bind variable.
BEGIN
  :table_name := UPPER('chicken');
END;
/

You query the value assigned to the table_name bind variable with the following SELECT statement:

SELECT :table_name FROM dual;

The query returns:

:TABLE_NAME
----------------------
CHICKEN

Conditionally drop a chicken table

You can drop a chicken table with the following anonymous PL/SQL program unit:

DECLARE
  /* Dynamic cursor. */
  CURSOR c (cv_object_name VARCHAR2) IS
    SELECT o.object_type
    ,      o.object_name
    FROM   user_objects o
    WHERE  o.object_name LIKE UPPER(cv_object_name||'%');
BEGIN
  FOR i IN c(:table_name) LOOP
    IF i.object_type = 'SEQUENCE' THEN
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
    ELSIF i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS';
    END IF;
  END LOOP;
END;
/

Create a chicken table

You can create a chicken table without any conditions. The following CREATE statement creates the chicken table:

CREATE TABLE chicken
( chicken_id    NUMBER
, chicken_name  VARCHAR2(20));

Query a chicken table definition

You use the following SELECT statement to view the chicken table’s structure:

SET NULL ''
COLUMN table_name   FORMAT A16
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 = 'CHICKEN'
ORDER BY 2;

The query returns:

TABLE_NAME	 COLUMN_ID COLUMN_NAME		  NULLABLE DATA_TYPE
---------------- --------- ---------------------- -------- ------------
CHICKEN 		 1 CHICKEN_ID			   NUMBER(22)
CHICKEN 		 2 CHICKEN_NAME 		   VARCHAR2(20)

You have created the chicken table and validated the chicken table’s definition. The instructional material only demonstrates how you create an unconstrained chicken table.

The lab has ten unit testing SQL script files, and one integration SQL script file. Each unit SQL script file contains the steps to manage table creation in a re-runnable script file. The integration SQL script file

Lab Description

[20 points] Click the Lab Instructions link to open the instructions inside the current webpage.

Lab Instructions →

Please perform the following tasks:

You begin these steps by running the cleanup_oracle.sql and create_oracle_store2.sql scripts. A great starting point for this lab is to review the create_oracle_store2.sql script. The create_oracle_store2.sql script creates 10 tables. Your Lab #2 script creates 10 tables with some changes and alterations.

You should use the script provided in the downloaded instance or create a new integration script, like the following:

-- Run the prior lab script.
@/home/student/Data/cit225/oracle/lib/cleanup_oracle.sql
@/home/student/Data/cit225/oracle/lib2/create/create_oracle_store2.sql
 
-- ... insert calls to other files with code here, like ...
 
@@system_user_lab.sql
 
SPOOL apply_oracle_lab2.txt
 
-- ... insert your code here ...
 
SPOOL OFF

You should embed the verification queries inside your unit test scripts. The unit test scripts are provided in the lab instructions but they’ve already been placed in the base scripts. That means when you copy the base files, you will not need to copy the verification queries from the web page into your script files.

[0 points] Tutorial on creating the SYSTEM_USER_LAB table and the SYSTEM_USER_LAB_S1 sequence, which is Step #1. The reasons for making the changes are:
- Table, sequence, and constraint names are unique inside a schema or database.
- All the tables, sequences, and constraints names must have counterparts that use an _LAB suffix or an _LAB_ between the base table constraint name and its number.

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

Tutorial Details for Command-line →

This section provides supporting instructions to complete the assignment from the Linux command-line.

The lab requires that you do the following:

Create a lib2 subdirectory in the /home/student/Data/cit225/oracle directory, which you find in maclochlainn/lib2 GitHub repository.
mkdir /home/student/Data/cit225/oracle/lib2
mkdir /home/student/Data/cit225/oracle/lib2
Create a create subdirectory in the lib2 subdirectory
mkdir /home/student/Data/cit225/oracle/lib2/create mkdir /home/student/Data/cit225/oracle/lib2/preseed mkdir /home/student/Data/cit225/oracle/lib2/seed
mkdir /home/student/Data/cit225/oracle/lib2/create mkdir /home/student/Data/cit225/oracle/lib2/preseed mkdir /home/student/Data/cit225/oracle/lib2/seed
Copy all the files from the create, preseed, and seed GitHub directories to the subdirectories you created in the lib2 subdirectory

Copy each unit test file from the lib2 directory to the lab2 directory by adding _LAB suffix to the file name

cp /home/student/Data/cit225/oracle/lib2/create/system_user.sql /home/student/Data/cit225/oracle/lib2/create/system_user_lab.sql
cp /home/student/Data/cit225/oracle/lib2/create/common_lookup.sql /home/student/Data/cit225/oracle/lib2/create/common_lookup_lab.sql
cp /home/student/Data/cit225/oracle/lib2/create/member.sql /home/student/Data/cit225/oracle/lib2/create/member_lab.sql
cp /home/student/Data/cit225/oracle/lib2/create/contact.sql /home/student/Data/cit225/oracle/lib2/create/contact_lab.sql
cp /home/student/Data/cit225/oracle/lib2/create/address.sql /home/student/Data/cit225/oracle/lib2/create/address_lab.sql
cp /home/student/Data/cit225/oracle/lib2/create/street_address.sql /home/student/Data/cit225/oracle/lib2/create/street_address_lab.sql
cp /home/student/Data/cit225/oracle/lib2/create/telephone.sql /home/student/Data/cit225/oracle/lib2/create/telephone_lab.sql
cp /home/student/Data/cit225/oracle/lib2/create/item.sql /home/student/Data/cit225/oracle/lib2/create/item_lab.sql
cp /home/student/Data/cit225/oracle/lib2/create/rental.sql /home/student/Data/cit225/oracle/lib2/create/rental_lab.sql
cp /home/student/Data/cit225/oracle/lib2/create/rental_item.sql /home/student/Data/cit225/oracle/lib2/create/rental_item_lab.sql

Edit each unit test file to create mirrored tables (tables with mirrored structures, like columns and constraints) with an _lab suffix by doing the following:
- Change the program name value in the comment section.
- Change the bind variable from system_user to system_user_lab.
  
  It should look like this:
- Change the CREATE TABLE statement from system_user to system_user_lab.
- Change the system_user_id column name to system_user_lab_id.
- Change the not null constraint names from nn_system_user_x to nn_system_user_lab_x.
- Change all foreign key lowercase references from fk_system_user_x to fk_system_user_lab_x.
- Change all table references in foreign key constraints to point toward the system_user_lab table and any foreign key column references to use the system_user_lab_id column name.
- Change all uppercase references to SYSTEM_USER to SYSTEM_USER_LAB in any diagnostic queries.
- Change all lowercase references to system_user to system_user_lab in any diagnostic queries.
Copy the create_oracle_store2.sql integration file from the lib2 directory to the lab2 directory as the apply_oracle_lab2.sql file. The syntax is:
cp /home/student/Data/cit225/oracle/lib2/create/create_oracle_store2.sql /home/student/Data/cit225/oracle/lab2/create/apply_oracle_lab2.sql
cp /home/student/Data/cit225/oracle/lib2/create/create_oracle_store2.sql /home/student/Data/cit225/oracle/lab2/create/apply_oracle_lab2.sql
Edit the integration file by changing all the script references from file names like system_user.sql to the new files from your lab2 folder like system_user_lab.sql. After you’ve made all those changes your are ready to perform an integration test.

Tutorial Details for GUI Menus →

This section provides supporting instructions to complete the assignment from the Linux GUI applications.

You connect to the VMware instance as the student user. Then, click on Activities to launch the menu. Enter gedit in the search box and click the Return key to launch the gedit program.

Having launched the gedit application, you should see the following image. Click the Open button to find and open the create_oracle_store.sql file.

Having opened the File Manager dialog, you can now click on the Home option in the Places list on the left.

Double click on the Data folder in the Name list.

After double clicking on the Data folder, the File Manager opens the Data folder and puts the Data folder in the list of folders. Next, click on the cit225 folder the Name list.

After double clicking on the cit225 folder, the File Manager opens the cit225 folder and puts the cit225 folder in the list of folders. Next, click on the oracle folder the Name list.

After double clicking on the oracle folder, the File Manager opens the oracle folder and puts the oracle folder in the list of folders. Next, click on the lib folder the Name list.

After double clicking on the lib2 folder, the File Manager opens the lib2 folder and puts the lib2 folder contents in the list of folders. These are folders (or, subdirectories) that you will use in Lab 2, 3, and 4. Next, click on the create folder in the Name list.

After double clicking on the create folder, the File Manager opens the create folder and puts the create folder contents in the Name list. These are files that you will use in Lab 2. Next, click on the Open button to open the system_user.sql file in the gedit application.

At this point, you can see the files that you moved with the command-line cp command. If you already copied these files with the command-line cp command, you don’t have to worry about launching the file in edit mode and saving the modified file in your lab2 folder. Instead, you should open the lab2 folder and edit the system_user.sql file.

Those not confortable with the command-line Linux commands should open the files in the lib2/create folder and save them in the lab2 folder through the Gedit interface. You open the system_user.sql file by navigating to the Gedit menu File tab, and then choose the Open selection. Gedit launches a file chooser dialog box and you would double click on the system_user.sql file name in the Name list.

You need to make the following changes in the system_user.sql script:
- Change the program name value in the comment section.
- Change the bind variable from system_user to system_user_lab.
  
  It should look like this:
- Change the CREATE TABLE statement from system_user to system_user_lab.
- Change the system_user_id column name to system_user_lab_id.
- Change the not null constraint names from nn_system_user_x to nn_system_user_lab_x.
- Change all foreign key lowercase references from fk_system_user_x to fk_system_user_lab_x.
- Change all table references in foreign key constraints to point toward the system_user_lab table and any foreign key column references to use the system_user_lab_id column name.
- Change all uppercase references to SYSTEM_USER to SYSTEM_USER_LAB in any diagnostic queries.
- Change all lowercase references to system_user to system_user_lab in any diagnostic queries.

After you make the changes to the system_user.sql you are ready to save it as the system_user_lab.sql script in the lab2 folder. You navigating to the Gedit menu File tab, and then choose the Save As selection. Gedit launches a file chooser dialog box and you will save it in the lab2 folder.

Now you repeat these steps for the remaining unit test files, which you will copy and rename with an _lab suffix in their file names. The list of base files is:
- common_lookup
- member
- contact
- address
- street_address
- telephone
- item
- rental
- rental_item
After fixing all the unit test files, copy the create_oracle_store2.sql integration file from the lib2 directory to the lab2 directory as the apply_oracle_lab2.sql file. The syntax is:
cp /home/student/Data/cit225/oracle/lib2/create/create_oracle_store2.sql /home/student/Data/cit225/oracle/lab2/create/apply_oracle_lab2.sql
cp /home/student/Data/cit225/oracle/lib2/create/create_oracle_store2.sql /home/student/Data/cit225/oracle/lab2/create/apply_oracle_lab2.sql
Edit the integration file by changing all the script references from file names like system_user.sql to the new files from your lab2 folder like system_user_lab.sql. After you’ve made all those changes your are ready to perform an integration test.

[2 points] Create the SYSTEM_USER_LAB table described by the following and the SYSTEM_USER_LAB_S1 sequence starting with a value of 1001:

Instruction Details →

Use the pattern of change explained in the command-line or GUI menu tutorial above to change base unit test file, system_user.sql, into a new unit test file, system_user_lab.sql. After making the necessary changes in the file, run the script by itself (a unit test) and verify the contents of the text-based log file.

Table Name: SYSTEM_USER_LAB
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
SYSTEM_USER_LAB_ID	PRIMARY KEY			Integer	Maximum
SYSTEM_USER_NAME	NOT NULL			String	20
SYSTEM_USER_GROUP_ID	FOREIGN KEY	COMMON_LOOKUP_LAB	COMMON_LOOKUP_LAB_ID	Integer	Maximum
SYSTEM_USER_GROUP_ID	NOT NULL	COMMON_LOOKUP_LAB	COMMON_LOOKUP_LAB_ID	Integer	Maximum
SYSTEM_USER_TYPE	FOREIGN KEY	COMMON_LOOKUP_LAB	COMMON_LOOKUP_LAB_ID	Integer	Maximum
SYSTEM_USER_TYPE	NOT NULL	COMMON_LOOKUP_LAB	COMMON_LOOKUP_LAB_ID	Integer	Maximum
FIRST_NAME				String	20
MIDDLE_NAME				String	20
LAST_NAME				String	20
CREATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATION_DATE	NOT NULL			DATE	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			DATE	Date

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

SET NULL ''
COLUMN table_name   FORMAT A16
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 = 'SYSTEM_USER_LAB'
ORDER BY 2;

It should display the following results:

TABLE_NAME       COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
---------------- --------- ---------------------- -------- ------------
SYSTEM_USER_LAB          1 SYSTEM_USER_LAB_ID     NOT NULL NUMBER(22)
SYSTEM_USER_LAB          2 SYSTEM_USER_NAME       NOT NULL VARCHAR2(20)
SYSTEM_USER_LAB          3 SYSTEM_USER_GROUP_ID   NOT NULL NUMBER(22)
SYSTEM_USER_LAB          4 SYSTEM_USER_TYPE       NOT NULL NUMBER(22)
SYSTEM_USER_LAB          5 FIRST_NAME                      VARCHAR2(20)
SYSTEM_USER_LAB          6 MIDDLE_NAME                     VARCHAR2(20)
SYSTEM_USER_LAB          7 LAST_NAME                       VARCHAR2(20)
SYSTEM_USER_LAB          8 CREATED_BY             NOT NULL NUMBER(22)
SYSTEM_USER_LAB          9 CREATION_DATE          NOT NULL DATE
SYSTEM_USER_LAB         10 LAST_UPDATED_BY        NOT NULL NUMBER(22)
SYSTEM_USER_LAB         11 LAST_UPDATE_DATE       NOT NULL DATE
 
11 rows selected.

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

COLUMN constraint_name   FORMAT A22
COLUMN search_condition  FORMAT A36
COLUMN constraint_type   FORMAT A1
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('system_user_lab')
AND      uc.constraint_type = UPPER('c')
ORDER BY uc.constraint_name;

It should display the following results:

CONSTRAINT_NAME        SEARCH_CONDITION                     C
---------------------- ------------------------------------ -
NN_SYSTEM_USER_LAB_1   "SYSTEM_USER_NAME" IS NOT NULL       C
NN_SYSTEM_USER_LAB_2   "SYSTEM_USER_GROUP_ID" IS NOT NULL   C
NN_SYSTEM_USER_LAB_3   "SYSTEM_USER_TYPE" IS NOT NULL       C
NN_SYSTEM_USER_LAB_4   "CREATED_BY" IS NOT NULL             C
NN_SYSTEM_USER_LAB_5   "CREATION_DATE" IS NOT NULL          C
NN_SYSTEM_USER_LAB_6   "LAST_UPDATED_BY" IS NOT NULL        C
NN_SYSTEM_USER_LAB_7   "LAST_UPDATE_DATE" IS NOT NULL       C
 
7 rows selected.

You can validate that you created the two self-referencing foreign key constraints with the following query:

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A40 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = 'SYSTEM_USER_LAB'
ORDER BY ucc1.table_name
,        uc.constraint_name;

It should display only the following:

Constraint Name:                       References:
 Table.Column                           Table.Column
-------------------------------------- ------------------------------------------
FK_SYSTEM_USER_LAB_1                   REFERENCES
(SYSTEM_USER_LAB.CREATED_BY)           (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)
 
FK_SYSTEM_USER_LAB_2                   REFERENCES
(SYSTEM_USER_LAB.LAST_UPDATED_BY)      (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)

Please note that the foreign keys on the SYSTEM_USER_GROUP_ID and SYSTEM_USER_TYPE columns haven’t been created at this point. That’s because they can’t exists until you create the COMMON_LOOKUP_LAB table.

You should then create a UQ_SYSTEM_USER_LAB_1 unique index on the SYSTEM_USER_NAME column of the SYSTEM_USER_LAB table. You can then use this query to display the unique constants on the table:

COLUMN index_name FORMAT A20 HEADING "Index Name"
SELECT   index_name
FROM     user_indexes
WHERE    table_name = UPPER('system_user_lab');

It should display:

Index Name
--------------------
PK_SYSTEM_USER_LAB_1
UQ_SYSTEM_USER_LAB_1

You can verify the existence of the SYSTEM_USER_LAB_S1 sequence with the following query:

COLUMN sequence_name FORMAT A20 HEADING "Sequence Name"
SELECT   sequence_name
FROM     user_sequences
WHERE    sequence_name = UPPER('system_user_lab_s1');

It should display the following:

Sequence Name
--------------------
SYSTEM_USER_LAB_S1
 
1 row selected.

[2 points] Create the COMMON_LOOKUP_LAB table described by the following and the COMMON_LOOKUP_LAB_S1 sequence starting with a value of 1001:

Instruction Details →

Use the pattern of change explained in the command-line or GUI menu tutorial above to change base unit test file, common_lookup.sql, into a new unit test file, common_lookup_lab.sql. After making the necessary changes in the file, run the script by itself (a unit test) and verify the contents of the text-based log file.

Table Name: COMMON_LOOKUP_LAB
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
COMMON_LOOKUP_LAB_ID	PRIMARY KEY			Integer	Maximum
COMMON_LOOKUP_CONTEXT	NOT NULL			String	30
COMMON_LOOKUP_TYPE	NOT NULL			String	30
COMMON_LOOKUP_MEANING	NOT NULL			String	30
CREATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATION_DATE	NOT NULL			DATE	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			DATE	Date

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

SET NULL ''
COLUMN table_name   FORMAT A18
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_LAB'
ORDER BY 2;

It should display the following results:

TABLE_NAME         COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
------------------ --------- ---------------------- -------- ------------
COMMON_LOOKUP_LAB          1 COMMON_LOOKUP_LAB_ID   NOT NULL NUMBER(22)
COMMON_LOOKUP_LAB          2 COMMON_LOOKUP_CONTEXT  NOT NULL VARCHAR2(30)
COMMON_LOOKUP_LAB          3 COMMON_LOOKUP_TYPE     NOT NULL VARCHAR2(30)
COMMON_LOOKUP_LAB          4 COMMON_LOOKUP_MEANING  NOT NULL VARCHAR2(30)
COMMON_LOOKUP_LAB          5 CREATED_BY             NOT NULL NUMBER(22)
COMMON_LOOKUP_LAB          6 CREATION_DATE          NOT NULL DATE
COMMON_LOOKUP_LAB          7 LAST_UPDATED_BY        NOT NULL NUMBER(22)
COMMON_LOOKUP_LAB          8 LAST_UPDATE_DATE       NOT NULL DATE
 
8 rows selected.

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

COLUMN constraint_name   FORMAT A22
COLUMN search_condition  FORMAT A36
COLUMN constraint_type   FORMAT A1
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_lab')
AND      uc.constraint_type IN (UPPER('c'),UPPER('p'))
ORDER BY uc.constraint_type DESC
,        uc.constraint_name;

It should display the following results:

CONSTRAINT_NAME        SEARCH_CONDITION                     C
---------------------- ------------------------------------ -
PK_CLOOKUP_LAB_1                                            P
NN_CLOOKUP_LAB_1       "COMMON_LOOKUP_CONTEXT" IS NOT NULL  C
NN_CLOOKUP_LAB_2       "COMMON_LOOKUP_TYPE" IS NOT NULL     C
NN_CLOOKUP_LAB_3       "COMMON_LOOKUP_MEANING" IS NOT NULL  C
NN_CLOOKUP_LAB_4       "CREATED_BY" IS NOT NULL             C
NN_CLOOKUP_LAB_5       "CREATION_DATE" IS NOT NULL          C
NN_CLOOKUP_LAB_6       "LAST_UPDATED_BY" IS NOT NULL        C
NN_CLOOKUP_LAB_7       "LAST_UPDATE_DATE" IS NOT NULL       C
 
8 rows selected.

You can validate that you created the two foreign key constraints with the following query:

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A40 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = 'COMMON_LOOKUP_LAB'
ORDER BY ucc1.table_name
,        uc.constraint_name;

It should display only the following:

Constraint Name:                       References:
 Table.Column                           Table.Column
-------------------------------------- -----------------------------------------
FK_CLOOKUP_LAB_1                       REFERENCES
(COMMON_LOOKUP_LAB.CREATED_BY)         (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)
 
FK_CLOOKUP_LAB_2                       REFERENCES
(COMMON_LOOKUP_LAB.LAST_UPDATED_BY)    (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)

You should then create a unique COMMON_LOOKUP_LAB_U1 unique index on the COMMON_LOOKUP_CONTEXT and COMMON_LOOKUP_TYPE columns of the COMMON_LOOKUP table; and a non-unique COMMON_LOOKUP_LAB_N1 index on the COMMON_LOOKUP_CONTEXT column of the COMMON_LOOKUP table:

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_lab')
ORDER BY UI.index_name
,        uic.column_position;

It should display:

                       Column Column
Index Name           Position Name
-------------------- -------- ----------------------
COMMON_LOOKUP_LAB_1         1 COMMON_LOOKUP_LAB_ID
COMMON_LOOKUP_LAB_N1        1 COMMON_LOOKUP_CONTEXT
COMMON_LOOKUP_LAB_U1        1 COMMON_LOOKUP_CONTEXT
COMMON_LOOKUP_LAB_U1        2 COMMON_LOOKUP_TYPE

You add two new foreign keys to the SYSTEM_USER_LAB table. They should:

Add a FK_SYSTEM_USER_3 foreign key to the SYSTEM_USER_LAB table. It should be placed on on the SYSTEM_USER_GROUP_ID column of the SYSTEM_USER_LAB table; and it should reference the COMMON_LOOKUP_LAB_ID column of the COMMON_LOOKUP_LAB table.
Add a FK_SYSTEM_USER_4 foreign key to the SYSTEM_USER_LAB table. It should be placed on on the SYSTEM_USER_TYPE column of the SYSTEM_USER_LAB table; and it should reference the COMMON_LOOKUP_LAB_ID column of the COMMON_LOOKUP_LAB table.

Run the following query after you add the new foreign key constraints.

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A40 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.position = ucc2.position -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = 'SYSTEM_USER_LAB'
ORDER BY ucc1.table_name
,        uc.constraint_name;

It should show you the following:

Constraint Name:                       References:                              
 Table.Column                           Table.Column                            
-------------------------------------- ---------------------------------------- 
FK_SYSTEM_USER_1                       REFERENCES                               
(SYSTEM_USER.CREATED_BY)               (SYSTEM_USER.SYSTEM_USER_ID)             
 
FK_SYSTEM_USER_2                       REFERENCES                               
(SYSTEM_USER.LAST_UPDATED_BY)          (SYSTEM_USER.SYSTEM_USER_ID)             
 
FK_SYSTEM_USER_3                       REFERENCES                               
(SYSTEM_USER.SYSTEM_USER_GROUP_ID)     (COMMON_LOOKUP.COMMON_LOOKUP_ID)         
 
FK_SYSTEM_USER_4                       REFERENCES                               
(SYSTEM_USER.SYSTEM_USER_TYPE)         (COMMON_LOOKUP.COMMON_LOOKUP_ID)

You can verify the existence of the COMMON_LOOKUP_LAB_S1 sequence with the following query:

COLUMN sequence_name FORMAT A20 HEADING "Sequence Name"
SELECT   sequence_name
FROM     user_sequences
WHERE    sequence_name = UPPER('common_lookup_lab_s1');

It should display the following:

Sequence Name
--------------------
COMMON_LOOKUP_LAB_S1
 
1 row selected.

[2 points] Create the MEMBER_LAB table described by the following and the MEMBER_LAB_S1 sequence starting with a value of 1001:

Instruction Details →

Use the pattern of change explained in the command-line or GUI menu tutorial above to change base unit test file, member.sql, into a new unit test file, member_lab.sql. After making the necessary changes in the file, run the script by itself (a unit test) and verify the contents of the text-based log file.

Table Name: MEMBER_LAB
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
MEMBER_LAB_ID	PRIMARY KEY			Integer	Maximum
MEMBER_TYPE	FOREIGN KEY	COMMON_LOOKUP_LAB	COMMON_LOOKUP_LAB_ID	Integer	Maximum
MEMBER_TYPE	NOT NULL	COMMON_LOOKUP_LAB	COMMON_LOOKUP_LAB_ID	Integer	Maximum
ACCOUNT_NUMBER	NOT NULL			String	10
CREDIT_CARD_NUMBER	NOT NULL			String	20
CREDIT_CARD_TYPE	FOREIGN KEY	COMMON_LOOKUP_LAB	COMMON_LOOKUP_ID	Integer	Maximum
CREDIT_CARD_TYPE	NOT NULL	COMMON_LOOKUP_LAB	COMMON_LOOKUP_ID	Integer	Maximum
CREATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATION_DATE	NOT NULL			DATE	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			DATE	Date

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

SET NULL ''
COLUMN table_name   FORMAT A18
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 = 'MEMBER_LAB'
ORDER BY 2;

It should display the following results:

TABLE_NAME         COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
------------------ --------- ---------------------- -------- ------------
MEMBER_LAB                 1 MEMBER_LAB_ID          NOT NULL NUMBER(22)
MEMBER_LAB                 2 MEMBER_TYPE            NOT NULL NUMBER(22)
MEMBER_LAB                 3 ACCOUNT_NUMBER         NOT NULL VARCHAR2(10)
MEMBER_LAB                 4 CREDIT_CARD_NUMBER     NOT NULL VARCHAR2(19)
MEMBER_LAB                 5 CREDIT_CARD_TYPE       NOT NULL NUMBER(22)
MEMBER_LAB                 6 CREATED_BY             NOT NULL NUMBER(22)
MEMBER_LAB                 7 CREATION_DATE          NOT NULL DATE
MEMBER_LAB                 8 LAST_UPDATED_BY        NOT NULL NUMBER(22)
MEMBER_LAB                 9 LAST_UPDATE_DATE       NOT NULL DATE
 
9 rows selected.

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

COLUMN constraint_name   FORMAT A22
COLUMN search_condition  FORMAT A36
COLUMN constraint_type   FORMAT A1
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('member_lab')
AND      uc.constraint_type IN (UPPER('c'),UPPER('p'))
ORDER BY uc.constraint_type DESC
,        uc.constraint_name;

It should display the following results:

CONSTRAINT_NAME        SEARCH_CONDITION                     C
---------------------- ------------------------------------ -
PK_MEMBER_LAB_1                                             P
NN_MEMBER_LAB_1        "MEMBER_TYPE" IS NOT NULL            C
NN_MEMBER_LAB_2        "ACCOUNT_NUMBER" IS NOT NULL         C
NN_MEMBER_LAB_3        "CREDIT_CARD_NUMBER" IS NOT NULL     C
NN_MEMBER_LAB_4        "CREDIT_CARD_TYPE" IS NOT NULL       C
NN_MEMBER_LAB_5        "CREATED_BY" IS NOT NULL             C
NN_MEMBER_LAB_6        "CREATION_DATE" IS NOT NULL          C
NN_MEMBER_LAB_7        "LAST_UPDATED_BY" IS NOT NULL        C
NN_MEMBER_LAB_8        "LAST_UPDATE_DATE" IS NOT NULL       C
 
9 rows selected.

You can validate that you created the four foreign key constraints with the following query:

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A40 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = 'MEMBER_LAB'
ORDER BY ucc1.table_name
,        uc.constraint_name;

It should display only the following:

Constraint Name:                       References:
 Table.Column                           Table.Column
-------------------------------------- -----------------------------------------
FK_MEMBER_LAB_1                        REFERENCES
(MEMBER_LAB.MEMBER_TYPE)               (COMMON_LOOKUP_LAB.COMMON_LOOKUP_LAB_ID)
 
FK_MEMBER_LAB_2                        REFERENCES
(MEMBER_LAB.CREDIT_CARD_TYPE)          (COMMON_LOOKUP_LAB.COMMON_LOOKUP_LAB_ID)
 
FK_MEMBER_LAB_3                        REFERENCES
(MEMBER_LAB.CREATED_BY)                (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)
 
FK_MEMBER_LAB_4                        REFERENCES
(MEMBER_LAB.LAST_UPDATED_BY)           (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)

You should then create a MEMBER_N1 non-unique index on the CREDIT_CARD_TYPE column of the MEMBER table:

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('member_lab')
AND NOT  ui.index_name IN (SELECT constraint_name
                           FROM   user_constraints)
ORDER BY ui.index_name
,        uic.column_position;

It should display:

                       COLUMN COLUMN
INDEX Name           POSITION Name
-------------------- -------- ----------------------
MEMBER_LAB_N1               1 CREDIT_CARD_TYPE

You can verify the existence of the MEMBER_LAB_S1 sequence with the following query:

COLUMN sequence_name FORMAT A20 HEADING "Sequence Name"
SELECT   sequence_name
FROM     user_sequences
WHERE    sequence_name = UPPER('member_lab_s1');

It should display the following:

Sequence Name
--------------------
MEMBER_LAB_S1

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

Instruction Details →

Use the pattern of change explained in the command-line or GUI menu tutorial above to change base unit test file, contact.sql, into a new unit test file, contact_lab.sql. After making the necessary changes in the file, run the script by itself (a unit test) and verify the contents of the text-based log file.

Table Name: CONTACT_LAB
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
CONTACT_LAB_ID	PRIMARY KEY			Integer	Maximum
MEMBER_LAB_ID	FOREIGN KEY	MEMBER_LAB	MEMBER_LAB_ID	Integer	Maximum
MEMBER_LAB_ID	NOT NULL	MEMBER_LAB	MEMBER_LAB_ID	Integer	Maximum
CONTACT_TYPE	FOREIGN KEY	COMMON_LOOKUP_LAB	COMMON_LOOKUP_LAB_ID	Integer	Maximum
CONTACT_TYPE	NOT NULL	COMMON_LOOKUP_LAB	COMMON_LOOKUP_LAB_ID	Integer	Maximum
LAST_NAME	NOT NULL			String	20
FIRST_NAME	NOT NULL			String	20
MIDDLE_NAME				String	20
CREATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATION_DATE	NOT NULL			DATE	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			DATE	Date

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

SET NULL ''
COLUMN table_name   FORMAT A18
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 = 'CONTACT_LAB'
ORDER BY 2;

It should display the following results:

TABLE_NAME         COLUMN_ID Name                   NULLABLE DATA_TYPE
------------------ --------- ---------------------- -------- ------------
CONTACT_LAB                1 CONTACT_LAB_ID         NOT NULL NUMBER(22)
CONTACT_LAB                2 MEMBER_LAB_ID          NOT NULL NUMBER(22)
CONTACT_LAB                3 CONTACT_TYPE           NOT NULL NUMBER(22)
CONTACT_LAB                4 FIRST_NAME             NOT NULL VARCHAR2(20)
CONTACT_LAB                5 MIDDLE_NAME                     VARCHAR2(20)
CONTACT_LAB                6 LAST_NAME              NOT NULL VARCHAR2(20)
CONTACT_LAB                7 CREATED_BY             NOT NULL NUMBER(22)
CONTACT_LAB                8 CREATION_DATE          NOT NULL DATE
CONTACT_LAB                9 LAST_UPDATED_BY        NOT NULL NUMBER(22)
CONTACT_LAB               10 LAST_UPDATE_DATE       NOT NULL DATE
 
10 rows selected.

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

COLUMN constraint_name   FORMAT A22
COLUMN search_condition  FORMAT A36
COLUMN constraint_type   FORMAT A1
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('contact_lab')
AND      uc.constraint_type IN (UPPER('c'),UPPER('p'))
ORDER BY uc.constraint_type DESC
,        uc.constraint_name;

It should display the following results:

CONSTRAINT_NAME        SEARCH_CONDITION                     C
---------------------- ------------------------------------ -
PK_CONTACT_LAB_1                                            P
NN_CONTACT_LAB_1       "MEMBER_LAB_ID" IS NOT NULL          C
NN_CONTACT_LAB_2       "CONTACT_TYPE" IS NOT NULL           C
NN_CONTACT_LAB_3       "FIRST_NAME" IS NOT NULL             C
NN_CONTACT_LAB_4       "LAST_NAME" IS NOT NULL              C
NN_CONTACT_LAB_5       "CREATED_BY" IS NOT NULL             C
NN_CONTACT_LAB_6       "CREATION_DATE" IS NOT NULL          C
NN_CONTACT_LAB_7       "LAST_UPDATED_BY" IS NOT NULL        C
NN_CONTACT_LAB_8       "LAST_UPDATE_DATE" IS NOT NULL       C
 
9 rows selected.

You can validate that you created the two foreign key constraints with the following query:

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A40 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = 'CONTACT_LAB'
ORDER BY ucc1.table_name
,        uc.constraint_name;

It should display only the following:

Constraint Name:                       References:
 Table.Column                           Table.Column
-------------------------------------- -----------------------------------------
FK_CONTACT_LAB_1                       REFERENCES
(CONTACT_LAB.MEMBER_ID)                (MEMBER_LAB.MEMBER_LAB_ID)
 
FK_CONTACT_LAB_2                       REFERENCES
(CONTACT_LAB.CONTACT_TYPE)             (COMMON_LOOKUP_LAB.COMMON_LOOKUP_LAB_ID)
 
FK_CONTACT_LAB_3                       REFERENCES
(CONTACT_LAB.CREATED_BY)               (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)
 
FK_CONTACT_LAB_4                       REFERENCES
(CONTACT_LAB.LAST_UPDATED_BY)          (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)

You should then create a CONTACT_N1 non-unique index on the MEMBER_ID column of the CONTACT table, and a CONTACT_N2 non-unique index on the CONTACT_TYPE
column of the CONTACT table:

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('contact_lab')
AND NOT  ui.index_name IN (SELECT constraint_name
                           FROM   user_constraints)
ORDER BY ui.index_name
,        uic.column_position;

It should display:

                       COLUMN COLUMN
INDEX Name           POSITION Name
-------------------- -------- ----------------------
CONTACT_LAB_N1              1 MEMBER_LAB_ID
CONTACT_LAB_N2              1 CONTACT_TYPE

You can verify the existence of the CONTACT_LAB_S1 sequence with the following query:

COLUMN sequence_name FORMAT A20 HEADING "Sequence Name"
SELECT   sequence_name
FROM     user_sequences
WHERE    sequence_name = UPPER('contact_lab_s1');

It should display the following:

Sequence Name
--------------------
CONTACT_LAB_S1

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

Instruction Details →

Use the pattern of change explained in the command-line or GUI menu tutorial above to change base unit test file, address.sql, into a new unit test file, address_lab.sql. After making the necessary changes in the file, run the script by itself (a unit test) and verify the contents of the text-based log file.

Table Name: ADDRESS_LAB
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
ADDRESS_LAB_ID	PRIMARY KEY			Integer	Maximum
CONTACT_LAB_ID	FOREIGN KEY	CONTACT_LAB	CONTACT_LAB_ID	Integer	Maximum
CONTACT_LAB_ID	NOT NULL	CONTACT_LAB	CONTACT_LAB_ID	Integer	Maximum
ADDRESS_TYPE	FOREIGN KEY	COMMON_LOOKUP_LAB	COMMON_LOOKUP_LAB_ID	Integer	Maximum
ADDRESS_TYPE	NOT NULL	COMMON_LOOKUP_LAB	COMMON_LOOKUP_LAB_ID	Integer	Maximum
CITY	NOT NULL			String	30
STATE_PROVINCE	NOT NULL			String	30
POSTAL_CODE	NOT NULL			String	20
CREATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATION_DATE	NOT NULL			DATE	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			DATE	Date

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

SET NULL ''
COLUMN table_name   FORMAT A18
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 = 'ADDRESS_LAB'
ORDER BY 2;

It should display the following results:

                             Column
TABLE_NAME         COLUMN_ID Name                   NULLABLE DATA_TYPE
------------------ --------- ---------------------- -------- ------------
ADDRESS_LAB                1 ADDRESS_LAB_ID         NOT NULL NUMBER(22)
ADDRESS_LAB                2 CONTACT_LAB_ID         NOT NULL NUMBER(22)
ADDRESS_LAB                3 ADDRESS_TYPE           NOT NULL NUMBER(22)
ADDRESS_LAB                4 CITY                   NOT NULL VARCHAR2(30)
ADDRESS_LAB                5 STATE_PROVINCE         NOT NULL VARCHAR2(30)
ADDRESS_LAB                6 POSTAL_CODE            NOT NULL VARCHAR2(20)
ADDRESS_LAB                7 CREATED_BY             NOT NULL NUMBER(22)
ADDRESS_LAB                8 CREATION_DATE          NOT NULL DATE
ADDRESS_LAB                9 LAST_UPDATED_BY        NOT NULL NUMBER(22)
ADDRESS_LAB               10 LAST_UPDATE_DATE       NOT NULL DATE
 
10 rows selected.

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

COLUMN constraint_name   FORMAT A22
COLUMN search_condition  FORMAT A36
COLUMN constraint_type   FORMAT A1
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('address_lab')
AND      uc.constraint_type IN (UPPER('c'),UPPER('p'))
ORDER BY uc.constraint_type DESC
,        uc.constraint_name;

It should display the following results:

CONSTRAINT_NAME        SEARCH_CONDITION                     C
---------------------- ------------------------------------ -
PK_ADDRESS_LAB_1                                            P
NN_ADDRESS_LAB_1       "CONTACT_LAB_ID" IS NOT NULL         C
NN_ADDRESS_LAB_2       "ADDRESS_TYPE" IS NOT NULL           C
NN_ADDRESS_LAB_3       "CITY" IS NOT NULL                   C
NN_ADDRESS_LAB_4       "STATE_PROVINCE" IS NOT NULL         C
NN_ADDRESS_LAB_5       "POSTAL_CODE" IS NOT NULL            C
NN_ADDRESS_LAB_6       "CREATED_BY" IS NOT NULL             C
NN_ADDRESS_LAB_7       "CREATION_DATE" IS NOT NULL          C
NN_ADDRESS_LAB_8       "LAST_UPDATED_BY" IS NOT NULL        C
NN_ADDRESS_LAB_9       "LAST_UPDATE_DATE" IS NOT NULL       C
 
10 rows selected.

You can validate that you created the two foreign key constraints with the following query:

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A40 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = 'ADDRESS_LAB'
ORDER BY ucc1.table_name
,        uc.constraint_name;

It should display only the following:

Constraint Name:                       References:
 Table.Column                           Table.Column
-------------------------------------- --------------------------------------
FK_ADDRESS_LAB_1                       REFERENCES
(ADDRESS_LAB.CONTACT_ID)               (CONTACT_LAB.CONTACT_LAB_ID)
 
FK_ADDRESS_LAB_2                       REFERENCES
(ADDRESS_LAB.ADDRESS_TYPE)             (COMMON_LOOKUP_LAB.COMMON_LOOKUP_LAB_ID)
 
FK_ADDRESS_LAB_3                       REFERENCES
(ADDRESS_LAB.CREATED_BY)               (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)
 
FK_ADDRESS_LAB_4                       REFERENCES
(ADDRESS_LAB.LAST_UPDATED_BY)          (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)

You should then create a ADDRESS_LAB_N1 non-unique index on the CONTACT_ID column of the ADDRESS_LAB table, and a CONTACT_N2 non-unique index on the ADDRESS_TYPE
column of the ADDRESS_LAB table:

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('address_lab')
AND NOT  ui.index_name IN (SELECT constraint_name
                           FROM   user_constraints)
ORDER BY ui.index_name
,        uic.column_position;

It should display:

                       COLUMN COLUMN
INDEX Name           POSITION Name
-------------------- -------- ----------------------
ADDRESS_LAB_N1              1 CONTACT_LAB_ID
ADDRESS_LAB_N2              1 ADDRESS_TYPE

You can verify the existence of the ADDRESS_LAB_S1 sequence with the following query:

COLUMN sequence_name FORMAT A20 HEADING "Sequence Name"
SELECT   sequence_name
FROM     user_sequences
WHERE    sequence_name = UPPER('address_lab_s1');

It should display the following:

Sequence Name
--------------------
ADDRESS_LAB_S1

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

Instruction Details →

Use the pattern of change explained in the command-line or GUI menu tutorial above to change base unit test file, street_address.sql, into a new unit test file, street_address_lab.sql. After making the necessary changes in the file, run the script by itself (a unit test) and verify the contents of the text-based log file.

Table Name: STREET_ADDRESS_LAB
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
STREET_ADDRESS_LAB_ID	PRIMARY KEY			Integer	Maximum
ADDRESS_LAB_ID	FOREIGN KEY	ADDRESS_LAB	ADDRESS_LAB_ID	Integer	Maximum
ADDRESS_LAB_ID	NOT NULL	ADDRESS_LAB	ADDRESS_LAB_ID	Integer	Maximum
STREET_ADDRESS	NOT NULL			String	30
CREATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATION_DATE	NOT NULL			DATE	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			DATE	Date

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

SET NULL ''
COLUMN table_name   FORMAT A18
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 = 'STREET_ADDRESS_LAB'
ORDER BY 2;

It should display the following results:

TABLE_NAME         COLUMN_ID Name                   NULLABLE DATA_TYPE
------------------ --------- ---------------------- -------- ------------
STREET_ADDRESS_LAB         1 STREET_ADDRESS_LAB_ID  NOT NULL NUMBER(22)
STREET_ADDRESS_LAB         2 STREET_ADDRESS         NOT NULL VARCHAR2(30)
STREET_ADDRESS_LAB         3 CREATED_BY             NOT NULL NUMBER(22)
STREET_ADDRESS_LAB         4 CREATION_DATE          NOT NULL DATE
STREET_ADDRESS_LAB         5 LAST_UPDATED_BY        NOT NULL NUMBER(22)
STREET_ADDRESS_LAB         6 LAST_UPDATE_DATE       NOT NULL DATE
 
6 rows selected.

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

COLUMN constraint_name   FORMAT A22
COLUMN search_condition  FORMAT A36
COLUMN constraint_type   FORMAT A1
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('street_address_lab')
AND      uc.constraint_type IN (UPPER('c'),UPPER('p'))
ORDER BY uc.constraint_type DESC
,        uc.constraint_name;

It should display the following results:

CONSTRAINT_NAME        SEARCH_CONDITION                     C
---------------------- ------------------------------------ -
PK_SADDRESS_LAB_1                                           P
NN_SADDRESS_LAB_1      "ADDRESS_ID" IS NOT NULL             C
NN_SADDRESS_LAB_2      "STREET_ADDRESS" IS NOT NULL         C
NN_SADDRESS_LAB_3      "CREATED_BY" IS NOT NULL             C
NN_SADDRESS_LAB_4      "CREATION_DATE" IS NOT NULL          C
NN_SADDRESS_LAB_5      "LAST_UPDATED_BY" IS NOT NULL        C
NN_SADDRESS_LAB_6      "LAST_UPDATE_DATE" IS NOT NULL       C
 
7 rows selected.

You can validate that you created the two foreign key constraints with the following query:

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A40 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = UPPER('street_address_lab')
ORDER BY ucc1.table_name
,        uc.constraint_name;

It should display only the following:

Constraint Name:                       References:
 Table.Column                           Table.Column
-------------------------------------- ----------------------------------------
FK_SADDRESS_LAB_1                      REFERENCES
(STREET_ADDRESS_LAB.ADDRESS_LAB_ID)    (ADDRESS_LAB.ADDRESS_LAB_ID)
 
FK_SADDRESS_LAB_2                      REFERENCES
(STREET_ADDRESS_LAB.CREATED_BY)        (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)
 
FK_SADDRESS_LAB_3                      REFERENCES
(STREET_ADDRESS_LAB.LAST_UPDATED_BY)   (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)

You can verify the existence of the STREET_ADDRESS_LAB_S1 sequence with the following query:

COLUMN sequence_name FORMAT A22 HEADING "Sequence Name"
SELECT   sequence_name
FROM     user_sequences
WHERE    sequence_name = UPPER('street_address_lab_s1');

It should display the following:

Sequence Name
----------------------
STREET_ADDRESS_LAB_S1

[2 points] Create the TELEPHONE_LAB table described by the following and the TELEPHONE_LAB_S1 sequence starting with a value of 1001:

Instruction Details →

Use the pattern of change explained in the command-line or GUI menu tutorial above to change base unit test file, telephone.sql, into a new unit test file, telephone_lab.sql. After making the necessary changes in the file, run the script by itself (a unit test) and verify the contents of the text-based log file.

Table Name: TELEPHONE_LAB
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
TELEPHONE_LAB_ID	PRIMARY KEY			Integer	Maximum
CONTACT_LAB_ID	FOREIGN KEY	CONTACT_LAB	CONTACT_LAB_ID	Integer	Maximum
CONTACT_LAB_ID	NOT NULL	CONTACT_LAB	CONTACT_LAB_ID	Integer	Maximum
ADDRESS_LAB_ID	FOREIGN KEY	ADDRESS_LAB	ADDRESS_LAB_ID	Integer	Maximum
TELEPHONE_TYPE	FOREIGN KEY	COMMON_LOOKUP_LAB	COMMON_LOOKUP_LAB_ID	Integer	Maximum
TELEPHONE_TYPE	NOT NULL	COMMON_LOOKUP_LAB	COMMON_LOOKUP_LAB_ID	Integer	Maximum
COUNTRY_CODE	NOT NULL			String	3
AREA_CODE	NOT NULL			String	6
TELEPHONE_NUMBER	NOT NULL			String	10
CREATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATION_DATE	NOT NULL			DATE	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			DATE	Date

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

SET NULL ''
COLUMN table_name   FORMAT A18
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 = 'TELEPHONE_LAB'
ORDER BY 2;

It should display the following results:

                             Column
TABLE_NAME         COLUMN_ID Name                   NULLABLE DATA_TYPE
------------------ --------- ---------------------- -------- ------------
TELEPHONE_LAB              1 TELEPHONE_LAB_ID       NOT NULL NUMBER(22)
TELEPHONE_LAB              2 CONTACT_LAB_ID         NOT NULL NUMBER(22)
TELEPHONE_LAB              3 ADDRESS_LAB_ID                  NUMBER(22)
TELEPHONE_LAB              4 TELEPHONE_TYPE         NOT NULL NUMBER(22)
TELEPHONE_LAB              5 COUNTRY_CODE           NOT NULL VARCHAR2(3)
TELEPHONE_LAB              6 AREA_CODE              NOT NULL VARCHAR2(6)
TELEPHONE_LAB              7 TELEPHONE_NUMBER       NOT NULL VARCHAR2(10)
TELEPHONE_LAB              8 CREATED_BY             NOT NULL NUMBER(22)
TELEPHONE_LAB              9 CREATION_DATE          NOT NULL DATE
TELEPHONE_LAB             10 LAST_UPDATED_BY        NOT NULL NUMBER(22)
TELEPHONE_LAB             11 LAST_UPDATE_DATE       NOT NULL DATE
 
11 rows selected.

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

COLUMN constraint_name   FORMAT A22
COLUMN search_condition  FORMAT A36
COLUMN constraint_type   FORMAT A1
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('telephone_lab')
AND      uc.constraint_type IN (UPPER('c'),UPPER('p'))
ORDER BY uc.constraint_type DESC
,        uc.constraint_name;

It should display the following results:

CONSTRAINT_NAME        SEARCH_CONDITION                     C
---------------------- ------------------------------------ -
PK_TELEPHONE_LAB_1                                          P
NN_TELEPHONE_LAB_1     "CONTACT_ID" IS NOT NULL             C
NN_TELEPHONE_LAB_2     "TELEPHONE_TYPE" IS NOT NULL         C
NN_TELEPHONE_LAB_3     "COUNTRY_CODE" IS NOT NULL           C
NN_TELEPHONE_LAB_4     "AREA_CODE" IS NOT NULL              C
NN_TELEPHONE_LAB_5     "TELEPHONE_NUMBER" IS NOT NULL       C
NN_TELEPHONE_LAB_6     "CREATED_BY" IS NOT NULL             C
NN_TELEPHONE_LAB_7     "CREATION_DATE" IS NOT NULL          C
NN_TELEPHONE_LAB_8     "LAST_UPDATED_BY" IS NOT NULL        C
NN_TELEPHONE_LAB_9     "LAST_UPDATE_DATE" IS NOT NULL       C
 
10 rows selected.

You can validate that you created the two foreign key constraints with the following query:

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A40 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = UPPER('telephone_lab')
ORDER BY ucc1.table_name
,        uc.constraint_name;

It should display only the following:

Constraint Name:                       References:
 Table.Column                           Table.Column
-------------------------------------- --------------------------------------
FK_TELEPHONE_LAB_1                     REFERENCES
(TELEPHONE_LAB.CONTACT_LAB_ID)         (CONTACT_LAB.CONTACT_LAB_ID)
 
FK_TELEPHONE_LAB_2                     REFERENCES
(TELEPHONE_LAB.TELEPHONE_TYPE)         (COMMON_LOOKUP_LAB.COMMON_LOOKUP_LAB_ID)
 
FK_TELEPHONE_LAB_3                     REFERENCES
(TELEPHONE_LAB.CREATED_BY)             (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)
 
FK_TELEPHONE_LAB_4                     REFERENCES
(TELEPHONE_LAB.LAST_UPDATED_BY)        (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)

You should then create a TELEPHONE_LAB_N1 non-unique index on the CONTACT_ID and ADDRESS_ID columns of the TELEPHONE_LAB table, a TELEPHONE_N2 non-unique index on the ADDRESS_ID column of the TELEPHONE_LAB table, and a TELEPHONE_N3 non-unique index on the TELEPHONE_TYPE column of the TELEPHONE_LAB table:

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('telephone_lab')
AND NOT  ui.index_name IN (SELECT constraint_name
                           FROM   user_constraints)
ORDER BY ui.index_name
,        uic.column_position;

It should display:

                       COLUMN COLUMN
INDEX Name           POSITION Name
-------------------- -------- ----------------------
TELEPHONE_LAB_N1            1 CONTACT_LAB_ID
TELEPHONE_LAB_N1            2 ADDRESS_LAB_ID
TELEPHONE_LAB_N2            1 ADDRESS_LAB_ID
TELEPHONE_LAB_N3            1 TELEPHONE_TYPE

You can verify the existence of the TELEPHONE_LAB_S1 sequence with the following query:

COLUMN sequence_name FORMAT A20 HEADING "Sequence Name"
SELECT   sequence_name
FROM     user_sequences
WHERE    sequence_name = UPPER('telephone_lab_s1');

It should display the following:

Sequence Name
--------------------
TELEPHONE_LAB_S1

[2 points] Create the RENTAL_LAB table described by the following and the RENTAL_LAB_S1 sequence starting with a value of 1001:

Instruction Details →

Use the pattern of change explained in the command-line or GUI menu tutorial above to change base unit test file, rental.sql, into a new unit test file, rental_lab.sql. After making the necessary changes in the file, run the script by itself (a unit test) and verify the contents of the text-based log file.

Table Name: RENTAL_LAB
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
RENTAL_LAB_ID	PRIMARY KEY			Integer	Maximum
CUSTOMER_ID	FOREIGN KEY	CONTACT_LAB	CONTACT_LAB_ID	Integer	Maximum
CUSTOMER_ID	NOT NULL	CONTACT_LAB	CONTACT_LAB_ID	Integer	Maximum
CHECK_OUT_DATE	NOT NULL			DATE	Date
RETURN_DATE	NOT NULL			DATE	Date
CREATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATION_DATE	NOT NULL			DATE	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			DATE	Date

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

SET NULL ''
COLUMN table_name   FORMAT A18
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 = UPPER('rental_lab')
ORDER BY 2;

It should display the following results:

                             Column
TABLE_NAME         COLUMN_ID Name                   NULLABLE DATA_TYPE
------------------ --------- ---------------------- -------- ------------
RENTAL_LAB                 1 RENTAL_LAB_ID          NOT NULL NUMBER(22)
RENTAL_LAB                 2 CUSTOMER_ID            NOT NULL NUMBER(22)
RENTAL_LAB                 3 CHECK_OUT_DATE         NOT NULL DATE
RENTAL_LAB                 4 RETURN_DATE            NOT NULL DATE
RENTAL_LAB                 5 CREATED_BY             NOT NULL NUMBER(22)
RENTAL_LAB                 6 CREATION_DATE          NOT NULL DATE
RENTAL_LAB                 7 LAST_UPDATED_BY        NOT NULL NUMBER(22)
RENTAL_LAB                 8 LAST_UPDATE_DATE       NOT NULL DATE
 
8 rows selected.

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

COLUMN constraint_name   FORMAT A22
COLUMN search_condition  FORMAT A36
COLUMN constraint_type   FORMAT A1
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('rental_lab')
AND      uc.constraint_type IN (UPPER('c'),UPPER('p'))
ORDER BY uc.constraint_type DESC
,        uc.constraint_name;

It should display the following results:

CONSTRAINT_NAME        SEARCH_CONDITION                     C
---------------------- ------------------------------------ -
PK_RENTAL_LAB_1                                             P
NN_RENTAL_LAB_1        "CUSTOMER_ID" IS NOT NULL            C
NN_RENTAL_LAB_2        "CHECK_OUT_DATE" IS NOT NULL         C
NN_RENTAL_LAB_3        "RETURN_DATE" IS NOT NULL            C
NN_RENTAL_LAB_4        "CREATED_BY" IS NOT NULL             C
NN_RENTAL_LAB_5        "CREATION_DATE" IS NOT NULL          C
NN_RENTAL_LAB_6        "LAST_UPDATED_BY" IS NOT NULL        C
NN_RENTAL_LAB_7        "LAST_UPDATE_DATE" IS NOT NULL       C
 
8 rows selected.

You can validate that you created the two foreign key constraints with the following query:

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A40 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = UPPER('rental_lab')
ORDER BY ucc1.table_name
,        uc.constraint_name;

It should display only the following:

Constraint Name:                       References:
 Table.Column                           Table.Column
-------------------------------------- ----------------------------------------
FK_RENTAL_LAB_1                        REFERENCES
(RENTAL_LAB.CUSTOMER_ID)               (CONTACT_LAB.CONTACT_LAB_ID)
 
FK_RENTAL_LAB_2                        REFERENCES
(RENTAL_LAB.CREATED_BY)                (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)
 
FK_RENTAL_LAB_3                        REFERENCES
(RENTAL_LAB.LAST_UPDATED_BY)           (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)

You can verify the existence of the RENTAL_LAB_S1 sequence with the following query:

COLUMN sequence_name FORMAT A20 HEADING "Sequence Name"
SELECT   sequence_name
FROM     user_sequences
WHERE    sequence_name = UPPER('rental_lab_s1');

It should display the following:

Sequence Name
--------------------
RENTAL_LAB_S1

[2 points] Create the ITEM_LAB table described by the following and the ITEM_LAB_S1 sequence starting with a value of 1001:

Instruction Details →

Use the pattern of change explained in the command-line or GUI menu tutorial above to change base unit test file, item.sql, into a new unit test file, item_lab.sql. After making the necessary changes in the file, run the script by itself (a unit test) and verify the contents of the text-based log file.

Table Name: ITEM_LAB
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
ITEM_LAB_ID	PRIMARY KEY			Integer	Maximum
ITEM_BARCODE	NOT NULL			String	14
ITEM_TYPE	FOREIGN KEY	COMMON_LOOKUP_LAB	COMMON_LOOKUP_ID	Integer	Maximum
ITEM_TYPE	NOT NULL	COMMON_LOOKUP_LAB	COMMON_LOOKUP_ID	Integer	Maximum
ITEM_TITLE	NOT NULL			String	60
ITEM_SUBTITLE				String	60
ITEM_RATING	NOT NULL			String	8
ITEM_RELEASE_DATE	NOT NULL			DATE	Date
CREATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATION_DATE	NOT NULL			DATE	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			DATE	Date

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

SET NULL ''
COLUMN table_name   FORMAT A18
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_LAB'
ORDER BY 2;

It should display the following results:

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

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

COLUMN constraint_name   FORMAT A22
COLUMN search_condition  FORMAT A36
COLUMN constraint_type   FORMAT A1
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('item_lab')
AND      uc.constraint_type IN (UPPER('c'),UPPER('p'))
ORDER BY uc.constraint_type DESC
,        uc.constraint_name;

It should display the following results:

CONSTRAINT_NAME        SEARCH_CONDITION                     C
---------------------- ------------------------------------ -
PK_ITEM_LAB_1                                               P
NN_ITEM_LAB_1          "ITEM_BARCODE" IS NOT NULL           C
NN_ITEM_LAB_2          "ITEM_TYPE" IS NOT NULL              C
NN_ITEM_LAB_3          "ITEM_TITLE" IS NOT NULL             C
NN_ITEM_LAB_4          "ITEM_RATING" IS NOT NULL            C
NN_ITEM_LAB_5          "ITEM_RELEASE_DATE" IS NOT NULL      C
NN_ITEM_LAB_6          "CREATED_BY" IS NOT NULL             C
NN_ITEM_LAB_7          "CREATION_DATE" IS NOT NULL          C
NN_ITEM_LAB_8          "LAST_UPDATED_BY" IS NOT NULL        C
NN_ITEM_LAB_9          "LAST_UPDATE_DATE" IS NOT NULL       C
 
10 rows selected.

You can validate that you created the two foreign key constraints with the following query:

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A38 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = UPPER('item_lab')
ORDER BY ucc1.table_name
,        uc.constraint_name;

It should display only the following:

Constraint Name:                       References:
 Table.Column                           Table.Column
-------------------------------------- ----------------------------------------
FK_ITEM_LAB_1                          REFERENCES
(ITEM_LAB.ITEM_TYPE)                   (COMMON_LOOKUP_LAB.COMMON_LOOKUP_LAB_ID)
 
FK_ITEM_LAB_2                          REFERENCES
(ITEM_LAB.CREATED_BY)                  (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)
 
FK_ITEM_LAB_3                          REFERENCES
(ITEM_LAB.LAST_UPDATED_BY)             (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)

You can verify the existence of the ITEM_LAB_S1 sequence with the following query:

COLUMN sequence_name FORMAT A20 HEADING "Sequence Name"
SELECT   sequence_name
FROM     user_sequences
WHERE    sequence_name = UPPER('item_lab_s1');

It should display the following:

Sequence Name
--------------------
ITEM_LAB_S1

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

Instruction Details →

Use the pattern of change explained in the command-line or GUI menu tutorial above to change base unit test file, rental_item.sql, into a new unit test file, rental_item_lab.sql. After making the necessary changes in the file, run the script by itself (a unit test) and verify the contents of the text-based log file.

Table Name: RENTAL_ITEM_LAB
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
RENTAL_ITEM_LAB_ID	PRIMARY KEY			Integer	Maximum
RENTAL_LAB_ID	FOREIGN KEY	RENTAL_LAB	RENTAL_LAB_ID	Integer	Maximum
RENTAL_LAB_ID	NOT NULL	RENTAL_LAB	RENTAL_LAB_ID	Integer	Maximum
ITEM_LAB_ID	FOREIGN KEY	ITEM_LAB	ITEM_LAB_ID	Integer	Maximum
ITEM_LAB_ID	NOT NULL	ITEM_LAB	ITEM_LAB_ID	Integer	Maximum
CREATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
CREATION_DATE	NOT NULL			DATE	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATED_BY	NOT NULL	SYSTEM_USER_LAB	SYSTEM_USER_LAB_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			DATE	Date

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

SET NULL ''
COLUMN table_name   FORMAT A18
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 = UPPER('rental_item_lab')
ORDER BY 2;

It should display the following results:

                             Column
TABLE_NAME         COLUMN_ID Name                   NULLABLE DATA_TYPE
------------------ --------- ---------------------- -------- ------------
RENTAL_ITEM_LAB            1 RENTAL_ITEM_LAB_ID     NOT NULL NUMBER(22)
RENTAL_ITEM_LAB            2 RENTAL_LAB_ID          NOT NULL NUMBER(22)
RENTAL_ITEM_LAB            3 ITEM_LAB_ID            NOT NULL NUMBER(22)
RENTAL_ITEM_LAB            4 CREATED_BY             NOT NULL NUMBER(22)
RENTAL_ITEM_LAB            5 CREATION_DATE          NOT NULL DATE
RENTAL_ITEM_LAB            6 LAST_UPDATED_BY        NOT NULL NUMBER(22)
RENTAL_ITEM_LAB            7 LAST_UPDATE_DATE       NOT NULL DATE
 
7 rows selected.

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

COLUMN constraint_name   FORMAT A22
COLUMN search_condition  FORMAT A36
COLUMN constraint_type   FORMAT A1
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('rental_item_lab')
AND      uc.constraint_type IN (UPPER('c'),UPPER('p'))
ORDER BY uc.constraint_type DESC
,        uc.constraint_name;

It should display the following results:

CONSTRAINT_NAME        SEARCH_CONDITION                     C
---------------------- ------------------------------------ -
PK_RENTAL_ITEM_LAB_1                                        P
NN_RENTAL_ITEM_LAB_1   "RENTAL_LAB_ID" IS NOT NULL          C
NN_RENTAL_ITEM_LAB_2   "ITEM_LAB_ID" IS NOT NULL            C
NN_RENTAL_ITEM_LAB_3   "CREATED_BY" IS NOT NULL             C
NN_RENTAL_ITEM_LAB_4   "CREATION_DATE" IS NOT NULL          C
NN_RENTAL_ITEM_LAB_5   "LAST_UPDATED_BY" IS NOT NULL        C
NN_RENTAL_ITEM_LAB_6   "LAST_UPDATE_DATE" IS NOT NULL       C
 
7 rows selected.

You can validate that you created the two foreign key constraints with the following query:

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A40 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = UPPER('rental_item_lab')
ORDER BY ucc1.table_name
,        uc.constraint_name;

It should display only the following:

Constraint Name:                       References:
 Table.Column                           Table.Column
-------------------------------------- ----------------------------------------
FK_RENTAL_ITEM_LAB_1                   REFERENCES
(RENTAL_ITEM_LAB.RENTAL_LAB_ID)        (RENTAL_LAB.RENTAL_LAB_ID)
 
FK_RENTAL_ITEM_LAB_2                   REFERENCES
(RENTAL_ITEM_LAB.ITEM_LAB_ID)          (ITEM_LAB.ITEM_LAB_ID)
 
FK_RENTAL_ITEM_LAB_3                   REFERENCES
(RENTAL_ITEM_LAB.CREATED_BY)           (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)
 
FK_RENTAL_ITEM_LAB_4                   REFERENCES
(RENTAL_ITEM_LAB.LAST_UPDATED_BY)      (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)

You can verify the existence of the ITEM_LAB_S1 sequence with the following query:

COLUMN sequence_name FORMAT A20 HEADING "Sequence Name"
SELECT   sequence_name
FROM     user_sequences
WHERE    sequence_name = UPPER('rental_item_lab_s1');

It should display the following:

Sequence Name
--------------------
RENTAL_ITEM_LAB_S1

[0 points] You can confirm the creation of the ten tables with the following query, which you should add to your apply_oracle_lab2.sql file:

Confirmation Details for Tables →

COLUMN table_name_base FORMAT A30 HEADING "Base Tables"
COLUMN table_name_lab  FORMAT A30 HEADING "Lab Tables"
SELECT   a.table_name_base
,        b.table_name_lab
FROM    (SELECT   table_name AS table_name_base
         FROM     user_tables
         WHERE    table_name IN ('SYSTEM_USER'
                                ,'COMMON_LOOKUP'
                                ,'MEMBER'
                                ,'CONTACT'
                                ,'ADDRESS'
                                ,'STREET_ADDRESS'
                                ,'TELEPHONE'
                                ,'ITEM'
                                ,'RENTAL'
                                ,'RENTAL_ITEM')) a INNER JOIN
        (SELECT   table_name AS table_name_lab
         FROM     user_tables
         WHERE    table_name IN ('SYSTEM_USER_LAB'
                                ,'COMMON_LOOKUP_LAB'
                                ,'MEMBER_LAB'
                                ,'CONTACT_LAB'
                                ,'ADDRESS_LAB'
                                ,'STREET_ADDRESS_LAB'
                                ,'TELEPHONE_LAB'
                                ,'ITEM_LAB'
                                ,'RENTAL_LAB'
                                ,'RENTAL_ITEM_LAB')) b
ON       a.table_name_base = SUBSTR( b.table_name_lab, 1, REGEXP_INSTR(table_name_lab,'_LAB') - 1)
ORDER BY CASE
           WHEN table_name_base LIKE 'SYSTEM_USER%' THEN 0
           WHEN table_name_base LIKE 'COMMON_LOOKUP%' THEN 1
           WHEN table_name_base LIKE 'MEMBER%' THEN 2
           WHEN table_name_base LIKE 'CONTACT%' THEN 3
           WHEN table_name_base LIKE 'ADDRESS%' THEN 4
           WHEN table_name_base LIKE 'STREET_ADDRESS%' THEN 5
           WHEN table_name_base LIKE 'TELEPHONE%' THEN 6
           WHEN table_name_base LIKE 'ITEM%' THEN 7
           WHEN table_name_base LIKE 'RENTAL%' AND NOT table_name_base LIKE 'RENTAL_ITEM%' THEN 8
           WHEN table_name_base LIKE 'RENTAL_ITEM%' THEN 9
         END;

COLUMN table_name_base FORMAT A30 HEADING "Base Tables" COLUMN table_name_lab FORMAT A30 HEADING "Lab Tables" SELECT a.table_name_base , b.table_name_lab FROM (SELECT table_name AS table_name_base FROM user_tables WHERE table_name IN ('SYSTEM_USER' ,'COMMON_LOOKUP' ,'MEMBER' ,'CONTACT' ,'ADDRESS' ,'STREET_ADDRESS' ,'TELEPHONE' ,'ITEM' ,'RENTAL' ,'RENTAL_ITEM')) a INNER JOIN (SELECT table_name AS table_name_lab FROM user_tables WHERE table_name IN ('SYSTEM_USER_LAB' ,'COMMON_LOOKUP_LAB' ,'MEMBER_LAB' ,'CONTACT_LAB' ,'ADDRESS_LAB' ,'STREET_ADDRESS_LAB' ,'TELEPHONE_LAB' ,'ITEM_LAB' ,'RENTAL_LAB' ,'RENTAL_ITEM_LAB')) b ON a.table_name_base = SUBSTR( b.table_name_lab, 1, REGEXP_INSTR(table_name_lab,'_LAB') - 1) ORDER BY CASE WHEN table_name_base LIKE 'SYSTEM_USER%' THEN 0 WHEN table_name_base LIKE 'COMMON_LOOKUP%' THEN 1 WHEN table_name_base LIKE 'MEMBER%' THEN 2 WHEN table_name_base LIKE 'CONTACT%' THEN 3 WHEN table_name_base LIKE 'ADDRESS%' THEN 4 WHEN table_name_base LIKE 'STREET_ADDRESS%' THEN 5 WHEN table_name_base LIKE 'TELEPHONE%' THEN 6 WHEN table_name_base LIKE 'ITEM%' THEN 7 WHEN table_name_base LIKE 'RENTAL%' AND NOT table_name_base LIKE 'RENTAL_ITEM%' THEN 8 WHEN table_name_base LIKE 'RENTAL_ITEM%' THEN 9 END;

It returns:

Base Tables                    Lab Tables
------------------------------ ------------------------------
SYSTEM_USER                    SYSTEM_USER_LAB
COMMON_LOOKUP                  COMMON_LOOKUP_LAB
MEMBER                         MEMBER_LAB
CONTACT                        CONTACT_LAB
ADDRESS                        ADDRESS_LAB
STREET_ADDRESS                 STREET_ADDRESS_LAB
TELEPHONE                      TELEPHONE_LAB
ITEM                           ITEM_LAB
RENTAL                         RENTAL_LAB
RENTAL_ITEM                    RENTAL_ITEM_LAB
 
10 rows selected.

[0 points] You can confirm the creation of the ten sequences with the following query, which you should add to your apply_oracle_lab2.sql file:

Confirmation Details for Sequences →

COLUMN sequence_name_base FORMAT A30 HEADING "Base Sequences"
COLUMN sequence_name_lab  FORMAT A30 HEADING "Lab Sequences"
SELECT   a.sequence_name_base
,        b.sequence_name_lab
FROM    (SELECT   sequence_name AS sequence_name_base
         FROM     user_sequences
         WHERE    sequence_name IN ('SYSTEM_USER_S1'
                                   ,'COMMON_LOOKUP_S1'
                                   ,'MEMBER_S1'
                                   ,'CONTACT_S1'
                                   ,'ADDRESS_S1'
                                   ,'STREET_ADDRESS_S1'
                                   ,'TELEPHONE_S1'
                                   ,'ITEM_S1'
                                   ,'RENTAL_S1'
                                   ,'RENTAL_ITEM_S1')) a INNER JOIN
        (SELECT   sequence_name AS sequence_name_lab
         FROM     user_sequences
         WHERE    sequence_name IN ('SYSTEM_USER_LAB_S1'
                                   ,'COMMON_LOOKUP_LAB_S1'
                                   ,'MEMBER_LAB_S1'
                                   ,'CONTACT_LAB_S1'
                                   ,'ADDRESS_LAB_S1'
                                   ,'STREET_ADDRESS_LAB_S1'
                                   ,'TELEPHONE_LAB_S1'
                                   ,'ITEM_LAB_S1'
                                   ,'RENTAL_LAB_S1'
                                   ,'RENTAL_ITEM_LAB_S1')) b
ON       SUBSTR(a.sequence_name_base, 1, REGEXP_INSTR(a.sequence_name_base,'_S1') - 1) =
           SUBSTR( b.sequence_name_lab, 1, REGEXP_INSTR(b.sequence_name_lab,'_LAB_S1') - 1)
ORDER BY CASE
           WHEN sequence_name_base LIKE 'SYSTEM_USER%' THEN 0
           WHEN sequence_name_base LIKE 'COMMON_LOOKUP%' THEN 1
           WHEN sequence_name_base LIKE 'MEMBER%' THEN 2
           WHEN sequence_name_base LIKE 'CONTACT%' THEN 3
           WHEN sequence_name_base LIKE 'ADDRESS%' THEN 4
           WHEN sequence_name_base LIKE 'STREET_ADDRESS%' THEN 5
           WHEN sequence_name_base LIKE 'TELEPHONE%' THEN 6
           WHEN sequence_name_base LIKE 'ITEM%' THEN 7
           WHEN sequence_name_base LIKE 'RENTAL%' AND NOT sequence_name_base LIKE 'RENTAL_ITEM%' THEN 8
           WHEN sequence_name_base LIKE 'RENTAL_ITEM%' THEN 9
         END;

COLUMN sequence_name_base FORMAT A30 HEADING "Base Sequences" COLUMN sequence_name_lab FORMAT A30 HEADING "Lab Sequences" SELECT a.sequence_name_base , b.sequence_name_lab FROM (SELECT sequence_name AS sequence_name_base FROM user_sequences WHERE sequence_name IN ('SYSTEM_USER_S1' ,'COMMON_LOOKUP_S1' ,'MEMBER_S1' ,'CONTACT_S1' ,'ADDRESS_S1' ,'STREET_ADDRESS_S1' ,'TELEPHONE_S1' ,'ITEM_S1' ,'RENTAL_S1' ,'RENTAL_ITEM_S1')) a INNER JOIN (SELECT sequence_name AS sequence_name_lab FROM user_sequences WHERE sequence_name IN ('SYSTEM_USER_LAB_S1' ,'COMMON_LOOKUP_LAB_S1' ,'MEMBER_LAB_S1' ,'CONTACT_LAB_S1' ,'ADDRESS_LAB_S1' ,'STREET_ADDRESS_LAB_S1' ,'TELEPHONE_LAB_S1' ,'ITEM_LAB_S1' ,'RENTAL_LAB_S1' ,'RENTAL_ITEM_LAB_S1')) b ON SUBSTR(a.sequence_name_base, 1, REGEXP_INSTR(a.sequence_name_base,'_S1') - 1) = SUBSTR( b.sequence_name_lab, 1, REGEXP_INSTR(b.sequence_name_lab,'_LAB_S1') - 1) ORDER BY CASE WHEN sequence_name_base LIKE 'SYSTEM_USER%' THEN 0 WHEN sequence_name_base LIKE 'COMMON_LOOKUP%' THEN 1 WHEN sequence_name_base LIKE 'MEMBER%' THEN 2 WHEN sequence_name_base LIKE 'CONTACT%' THEN 3 WHEN sequence_name_base LIKE 'ADDRESS%' THEN 4 WHEN sequence_name_base LIKE 'STREET_ADDRESS%' THEN 5 WHEN sequence_name_base LIKE 'TELEPHONE%' THEN 6 WHEN sequence_name_base LIKE 'ITEM%' THEN 7 WHEN sequence_name_base LIKE 'RENTAL%' AND NOT sequence_name_base LIKE 'RENTAL_ITEM%' THEN 8 WHEN sequence_name_base LIKE 'RENTAL_ITEM%' THEN 9 END;

It returns:

Base Sequences                 Lab Sequences
------------------------------ ------------------------------
SYSTEM_USER_S1                 SYSTEM_USER_LAB_S1
COMMON_LOOKUP_S1               COMMON_LOOKUP_LAB_S1
MEMBER_S1                      MEMBER_LAB_S1
CONTACT_S1                     CONTACT_LAB_S1
ADDRESS_S1                     ADDRESS_LAB_S1
STREET_ADDRESS_S1              STREET_ADDRESS_LAB_S1
TELEPHONE_S1                   TELEPHONE_LAB_S1
ITEM_S1                        ITEM_LAB_S1
RENTAL_S1                      RENTAL_LAB_S1
RENTAL_ITEM_S1                 RENTAL_ITEM_LAB_S1
 
10 rows selected.

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_lab2.sql script, you should navigate to the /home/student/Data/cit225/oracle/lab2 directory. You call the sqlplus utility from the same directory and then you run the apply_oracle_lab2.sql script with the following syntax:

@apply_oracle_lab2.sql

You should submit your apply_oracle_lab2.sql script file and apply_oracle_lab2.txt log file for a grade.

Written by michaelmclaughlin

December 29th, 2009 at 4:40 pm

Posted in

48 Responses to 'Lab #2 : Oracle Tables'

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

So in step 1 in Lab 2 During this part:

That’s because they can’t exists until you create the COMMON_LOOKUP_LAB table.

You should then create a SYSTEM_USER_LAB_U1 unique index on the SYSTEM_USER_NAME column of the SYSTEM_USER table. You can then use this query to display the unique constants on the table:

There is a space needed between the ‘the’ and the COMMON_LOOKUP_LAB table and the ‘the’ and the SYSTEM_USER_NAME. The NAME in SYSTEM_USER_NAME needs to be corrected in spelling as well.

Noble Hamblin

19 Nov 14 at 2:05 pm
(spelling error extra ‘ s ‘) It should displays only the following:

Cory Allred

19 Nov 14 at 3:45 pm
Again, in step 1, the system_user_name you have the data type requirement as an integer. I believe it should be a string?

Christian

19 Nov 14 at 4:28 pm
‘You should then create a SYSTEM_USER_LAB_U1 unique index on the SYSTEM_USER_NANE‘ – in step 1, says nane instead of NAME

Christian

19 Nov 14 at 4:37 pm
Again on step 1, the foreign key references are labeled as the system_user and common_lookup, where the tables that should be referenced is system_user_lab and common_lookup_lab.

Christian

19 Nov 14 at 4:59 pm
All foreign key references in all steps in the lab directions (the blue tables) have system_user(when referencing that table), not system_user_lab. Is it supposed to be system_user_lab?

Christian

19 Nov 14 at 5:13 pm
Christian, Thanks. That’s fixed throughout.

michaelmclaughlin

20 Nov 14 at 2:38 pm
Christian, Good catch. It’s fixed.

michaelmclaughlin

21 Nov 14 at 12:19 am
Christian, Yes, it’s fixed.

michaelmclaughlin

21 Nov 14 at 12:21 am
Cory, Good catch, they’re fixed.

michaelmclaughlin

21 Nov 14 at 12:33 am
Noble, there are spaces, which browser wasn’t displaying them? However, the SYSTEM_USER should have been SYSTEM_USER_LAB; and that’s fixed.

michaelmclaughlin

21 Nov 14 at 12:54 am
In step 5, the ADDRESS_LAB table has a column named LAST_NAME. Is that supposed to be left out?

Adam Quinton

13 Jan 15 at 10:27 pm
In step 9 it states that we need to make a column named ADDRESS_LAB_ID, but the results shown further down don’t have that as one of the columns.

Sam Barney

14 Jan 15 at 12:08 am
On table 4 the CONTACT_LAB in the MEMBER_LAB_ID column it says COMMON_LOOKUP_ID in the reference column and I believe it should say COMMON_LOOKUP_LAB_ID instead?

Ethan Killian

15 Jan 15 at 7:40 pm
There is duplicated data in the Common lookup table

Casey

15 Jan 15 at 7:52 pm
In the section about the ADDRESS table, there is a line in the blue box describing a LINE_NUMBER column. This has been deleted from everywhere else both in the post and in Bro. McLaughlin’s own code. My solution was just to assume that it’s no longer relevant and ignore it.

Madeleine

15 Jan 15 at 9:27 pm
There is an error in one of the verifications. Step three in the Member Lab forieng key constraint varification. It is checking COMMON_LOOKUP_LAB but it should be “MEMBER_LAB“.

Arden Shackelford

15 Jan 15 at 10:15 pm
Step 10.

RENTAL_ITEM_ID

This should read as RENATL_ITEM_LAB_ID in the table that is provided on the website. Your naming convention so far has followed that.

Unless of course this is an exception.

Joseph Tracy

16 Jan 15 at 12:10 am
Madeleine, Great choice. It was an error and has been removed.

michaelmclaughlin

16 Jan 15 at 1:37 am
Adam, It was an error and has been removed.

michaelmclaughlin

16 Jan 15 at 1:39 am
Joseph, Great catch. It was an error and has been fixed.

michaelmclaughlin

16 Jan 15 at 1:43 am
Arden, Got it, and fixed it.

michaelmclaughlin

16 Jan 15 at 2:43 am
Casey, I believe these are fixed. Thanks.

michaelmclaughlin

16 Jan 15 at 2:54 am
Sam, Is this already fixed?

michaelmclaughlin

16 Jan 15 at 3:01 am
Ethan, I believe it’s fixed now, thanks! Great catch.

michaelmclaughlin

16 Jan 15 at 3:03 am
Under the steps the following tables have their reference columns incorrect because they do not include “_lab” the same thing needs to be reflected in the checks

COMMON_LOOKUP_LAB
MEMBER_LAB
ITEM_LAB

Tyler Nelson

16 Jan 15 at 5:00 pm
Tyler, Great catch, they’re fixed now.

michaelmclaughlin

21 Jan 15 at 2:53 am
In the creation of the contact_lab table, the foreign key on the member_id column should reference the member_lab table, not the common_lookup table. This creates a problem in step 4 of lab 4.

Adam Quinton

27 Jan 15 at 11:06 pm
In step 9’s definition of item, it’s missing release_date.

Jeremy

9 Feb 15 at 3:14 pm
Jeremy, It was ITEM_RATING_DATE and is now correctly ITEM_RELEASE_DATE. Thanks.

michaelmclaughlin

7 Mar 15 at 2:42 am
Adam, You’re totally right that the HTML table had COMMON_LOOKUP_LAB where it should have had MEMBER_LAB for the MEMBER_LAB_ID column. Thanks!

michaelmclaughlin

8 Mar 15 at 2:29 am
What exactly is meant by this code? I don’t think it’s correct on the website page:

Change all constraint names from nn_system_user_x to nn_system_user_x. (Are these not the exact same thing?)
Change all lowercase references to fk_system_user_x to fk_system_user_x.

Brandon

23 Sep 15 at 9:49 am
The Item_lab table creation verification code has an error where it refers to the contact_lab table.

Trek

24 Sep 15 at 9:43 pm
I fixed these on the Lab #2 : Oracle Tables web page:
- Change all uppercase references to SYSTEM_USER to SYSTEM_USER_LAB.
- Change all lowercase references to system_user to system_user_lab.
- Change all constraint names from nn_system_user_x to nn_system_user_lab_x.
- Change all lowercase references to fk_system_user_x to fk_system_user_lab_x.
michaelmclaughlin

24 Sep 15 at 11:21 pm
Trek, Thanks. I’ve changed contact_lab to item_lab in the query.

michaelmclaughlin

25 Sep 15 at 12:00 am
In step 9 in the second query code to “verify completion of the constraints for this step” on line #10 it has the table name as contact_lab. I think it should be item_lab for this step.

Kami

26 Sep 15 at 9:47 am
Kami, Already changed.

michaelmclaughlin

27 Sep 15 at 5:41 pm
In step 1 your instructions at one part say:

You should then create a SYSTEM_USER_LAB_U1 unique index on the SYSTEM_USER_NAME column of the SYSTEM_USER_LAB table. [Which should return this:]

INDEX Name
——————–
SYSTEM_USER_LAB_PK
SYSTEM_USER_LAB_U1

However our lab files are setup so that the names are:
PK_SYSTEM_USER_LAB
SYSTEM_USER_LAB_U1 <— The one we make ourselves

Are we supposed to change the other one to the same formatting? Your instructions only say to add the word LAB not rearrange the name.

Chris

28 Apr 16 at 10:54 am
You also have several other constraint names named differently like:

PK_C_LOOKUP_LAB_1 <– In the lab
PK_LOOKUP_LAB_1 <– What you said we should see returned if we did it right.

Are we supposed to catch these naming errors and change (fix) them? Your instructions did not say to but I do not want errors later on.

Chris

28 Apr 16 at 11:26 am
Step 1: You have “COMMON_LOOKUP_LAB_S1 sequence starting with a value of 1001” but the value should be 1003 because you create two users manually that take 1001 and 1002.

Is this going to effect the rest of the lab’s sequencing?

Chris

28 Apr 16 at 12:48 pm
In step 6, working with the street_address_lab table, the verification looks for an address_type column. The address_type column exists in the address_lab table only and is not needed in the street_address_lab table. It needs to be removed from the table verification.

Tyler Nelson

28 Apr 16 at 9:02 pm
In step 10 working with the RENTAL_ITEM_LAB table, the constraint verification is incorrect. In the SEARCH_CONDITION column, RENTAL_ID should be RENTAL_LAB_ID and ITEM_ID should be ITEM_LAB_ID.

Tyler Nelson

28 Apr 16 at 9:40 pm
I’ve updated the page to use different constraint name.

michaelmclaughlin

29 Apr 16 at 1:57 am
There should only be one row in the SYSTEM_USER and SYSTEM_USER_LAB tables.

michaelmclaughlin

29 Apr 16 at 1:59 am
Tyler, Great catch. The output names of RENTAL_ID and ITEM_ID are fixed, and are now RENTAL_ITEM_LAB_ID and ITEM_LAB_ID.

michaelmclaughlin

29 Apr 16 at 2:14 am
Tyler, Another great catch. The ADDRESS_TYPE column doesn’t belong in the STREET_ADDRESS table. It’s been removed from the displayed output.

michaelmclaughlin

29 Apr 16 at 2:18 am
One thing that was not mentioned in the tutorial: In order to ensure the verification script at the end works, you have to find where he declares the bind variable for each table’s name – which is located in that table’s SQL file – and add “_lab” to the end of it.

Montana Burr

4 May 18 at 4:24 pm
Montana, Wonderful catch! I’ve fixe it and made some significant improvements. If you have a chance check them out and let me know if you like if better. Thanks.

michaelmclaughlin

4 May 18 at 7:15 pm

Database Tutorial

Lab #2 : Oracle Tables

48 Responses to 'Lab #2 : Oracle Tables'

Leave a Reply

My Technical Blog

Tutorial Pages

Blogroll