Database Tutorial

Course Tutorial Site

Site Admin

Instructions

without comments

Lab #3: Modeling Data

Learn data modeling definitions, theory, and normalization process; learn how to use common lookup tables; who-audit columns to manage row-level security; and the INSERT statement.

Lab Instructions

This lab has twelve unit testing steps placed into one test script.

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

Lab Instructions →

You begin these steps by running two scripts:

  • The lab2/apply_oracle_lab2.sql script.
  • The lib1/preseed/preseed_oracle_store.sql script.

You put these two scripts into your apply_oracle_lab3.sql script, which should be in the following directory:

/home/student/Data/cit225/lab3

The lib1/preseed/preseed_oracle_store.sql script manages all constraint changes and inserts to the SYSTEM_USER and COMMON_LOOKUP tables. You should use it as the basis for creating your apply_oracle_lab3.sql that will do the same things for the SYSTEM_USER_LAB and COMMON_LOOKUP_LAB tables.

As noted, you call the apply_oracle_lab2.sql script file as the first step in your apply_oracle_lab3.sql script file. You won’t write the apply_oracle_lab3.sql script file from scratch.

You copy the pressed_oracle_store.sql script from the lib1/preseed subdirectory to the lab3 subdirectory. Many inexperienced users would copy the pressed_oracle_store.sql script to the lab3 subdirectory and then rename it as the apply_oracle_lab3.sql script but you can do both with a single command:

cp /home/student/Data/cit225/oracle/lib1/preseed/preseed_oracle_store.sql /home/student/Data/cit225/oracle/lab3/apply_oracle_lab3.sql

Call the lib1/utility/cleanup_oracle.sql script, which gives you a fresh database instance; and call the lib1/create/create_oracle_store2.sql script, which creates your core ten tables for the video store data model at the top of the file, like:

-- ------------------------------------------------------------------
-- This creates tables, sequences, indexes, and constraints necessary
-- to begin lesson #3. Demonstrates proper process and syntax.
-- ------------------------------------------------------------------
 
@@/home/student/Data/cit225/oracle/lab2/apply_oracle_lab2.sql
@@/home/student/Data/cit225/oracle/lib1/preseed/preseed_oracle_store.sql
 
-- --------------------------------------------------------
--  Step #1
--  -------
--  Disable foreign key constraints dependencies.
-- --------------------------------------------------------

You should complete the following twelve steps:

  1. You need to disable the following foreign key constraints from the SYSTEM_USER_LAB table:

    • Disable the fk_system_user_lab_3 foreign key constraint.
    • Disable the fk_system_user_lab_4 foreign key constraint.

    After you run the two UPDATE statements, you should query the results from the SYSTEM_USER_LAB table, like this:

    COL table_name       FORMAT A18  HEADING "Table Name"
COL constraint_name  FORMAT A28  HEADING "Constraint Name"
COL constraint_type  FORMAT A12  HEADING "Constraint Type"
COL status           FORMAT A8   HEADING "Status"
SELECT   table_name
,        constraint_name
,        CASE
           WHEN constraint_type = 'R'        THEN 'FOREIGN KEY'
           WHEN constraint_type = 'P'        THEN 'PRIMARY KEY'
           WHEN constraint_type = 'U'        THEN 'UNIQUE'
           WHEN constraint_type = 'C' AND  REGEXP_LIKE(constraint_name,'^.*nn.*$','i')
           THEN 'NOT NULL'
           ELSE 'CHECK'
         END constraint_type
,        status
FROM     user_constraints
WHERE    table_name = 'SYSTEM_USER_LAB'
ORDER BY CASE
           WHEN constraint_type = 'PRIMARY KEY' THEN 1
           WHEN constraint_type = 'NOT NULL'    THEN 2
           WHEN constraint_type = 'CHECK'       THEN 3
           WHEN constraint_type = 'UNIQUE'      THEN 4
           WHEN constraint_type = 'FOREIGN KEY' THEN 5
         END
,        constraint_name;

    It should show:

    Table Name     Constraint Name                  Constraint Type Status
-------------- -------------------------------- --------------- --------
SYSTEM_USER_LAB    PK_SYSTEM_USER_LAB_1         PRIMARY KEY     ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_1         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_2         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_3         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_4         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_5         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_6         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_7         NOT NULL        ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_1         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_2         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_3         FOREIGN KEY     DISABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_4         FOREIGN KEY     DISABLED

  2. You need to remove the not null constraints from the following columns of the SYSTEM_USER_LAB table, which you can do with the MODIFY subclause:

    • Remove not null constraint from the system_user_group_id column.
    • Remove not null constraint from the system_user_type column.

    COL table_name       FORMAT A18  HEADING "Table Name"
COL constraint_name  FORMAT A28  HEADING "Constraint Name"
COL constraint_type  FORMAT A15  HEADING "Constraint Type"
COL status           FORMAT A8   HEADING "Status"
SELECT   table_name
,        constraint_name
,        CASE
           WHEN constraint_type = 'R'        THEN 'FOREIGN KEY'
           WHEN constraint_type = 'P'        THEN 'PRIMARY KEY'
           WHEN constraint_type = 'U'        THEN 'UNIQUE'
           WHEN constraint_type = 'C' AND  REGEXP_LIKE(constraint_name,'^.*nn.*$','i')
           THEN 'NOT NULL'
           ELSE 'CHECK'
         END constraint_type
,        status
FROM     user_constraints
WHERE    table_name = 'SYSTEM_USER_LAB'
ORDER BY CASE
           WHEN constraint_type = 'PRIMARY KEY' THEN 1
           WHEN constraint_type = 'NOT NULL'    THEN 2
           WHEN constraint_type = 'CHECK'       THEN 3
           WHEN constraint_type = 'UNIQUE'      THEN 4
           WHEN constraint_type = 'FOREIGN KEY' THEN 5
         END
,        constraint_name;

    It should show a gap of not null constraints between nn_system_user_lab_1 and nn_system_user_lab4 constraint names, like:

    Table Name         Constraint Name              Constraint Type Status
------------------ ---------------------------- --------------- --------
SYSTEM_USER_LAB    PK_SYSTEM_USER_LAB_1         PRIMARY KEY     ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_1         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_4         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_5         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_6         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_7         NOT NULL        ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_1         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_2         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_3         FOREIGN KEY     DISABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_4         FOREIGN KEY     DISABLED

  3. After running your apply_oracle_lab2.sql script, you will insert one row into the SYSTEM_USER_LAB table. This single row will use a literal values as qualified below.

    Table Name: SYSTEM_USER_LAB
    Column Value #1
    SYSTEM_USER_LAB_ID 1
    SYSTEM_USER_NAME SYSADMIN
    SYSTEM_USER_GROUP_ID null
    SYSTEM_USER_TYPE null
    FIRST_NAME  
    MIDDLE_NAME  
    LAST_NAME  
    CREATED_BY 1
    CREATION_DATE SYSDATE
    LAST_UPDATED_BY 1
    LAST_UPDATE_DATE SYSDATE

  4. You need to disable the following foreign key constraints from the COMMON_LOOKUP_LAB table:

    • Disable the fk_clookup_lab_1 foreign key constraint.
    • Disable the fk_clookup_lab_2 foreign key constraint.

    COL table_name       FORMAT A14  HEADING "Table Name"
COL constraint_name  FORMAT A24  HEADING "Constraint Name"
COL constraint_type  FORMAT A15  HEADING "Constraint Type"
COL status           FORMAT A8   HEADING "Status"
SELECT   table_name
,        constraint_name
,        CASE
           WHEN constraint_type = 'R'        THEN 'FOREIGN KEY'
           WHEN constraint_type = 'P'        THEN 'PRIMARY KEY'
           WHEN constraint_type = 'U'        THEN 'UNIQUE'
           WHEN constraint_type = 'C' AND  REGEXP_LIKE(constraint_name,'^.*nn.*$','i')
           THEN 'NOT NULL'
           ELSE 'CHECK'
         END constraint_type
,        status
FROM     user_constraints
WHERE    table_name = 'COMMON_LOOKUP_LAB'
ORDER BY CASE
           WHEN constraint_type = 'PRIMARY KEY' THEN 1
           WHEN constraint_type = 'NOT NULL'    THEN 2
           WHEN constraint_type = 'CHECK'       THEN 3
           WHEN constraint_type = 'UNIQUE'      THEN 4
           WHEN constraint_type = 'FOREIGN KEY' THEN 5
         END
,        constraint_name;

    It should show a gap of not null constraints between nn_system_user_lab_1 and nn_system_user_lab4 constraint names, like:

    Table Name         Constraint Name          Constraint Type Status
------------------ ------------------------ --------------- --------
COMMON_LOOKUP_LAB  PK_CLOOKUP_LAB_1         PRIMARY KEY     ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_1         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_2         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_3         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_4         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_5         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_6         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_7         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  FK_CLOOKUP_LAB_1         FOREIGN KEY     DISABLED
COMMON_LOOKUP_LAB  FK_CLOOKUP_LAB_2         FOREIGN KEY     DISABLED

  5. Insert 20 rows into the COMMON_LOOKUP_LAB table. They should contain the following data. Rows 6 through 20 will use the COMMON_LOOKUP_LAB_S1 sequence value.

    Table Name: COMMON_LOOKUP_LAB
    ID # Context Type Meaning
    1 SYSTEM_USER_LAB SYSTEM_ADMIN System Administrator
    2 SYSTEM_USER_LAB DBA Database Administrator
    3 SYSTEM_USER_LAB SYSTEM_GROUP Database Administrator
    4 SYSTEM_USER_LAB COST_CENTER Database Administrator
    5 SYSTEM_USER_LAB INDIVIDUAL Database Administrator
    1001 CONTACT_LAB EMPLOYEE Employee
    1002 CONTACT_LAB CUSTOMER Customer
    1003 MEMBER_LAB INDIVIDUAL Individual Membership
    1004 MEMBER_LAB GROUP Group Membership
    1005 MEMBER_LAB DISCOVER_CARD Discover Card
    1006 MEMBER_LAB MASTER_CARD Master Card
    1007 MEMBER_LAB VISA_CARD VISA Card
    1008 MULTIPLE HOME Home
    1009 MULTIPLE WORK Work
    1010 ITEM_LAB DVD_FULL_SCREEN DVD: Full Screen
    1011 ITEM_LAB DVD_WIDE_SCREEN DVD: Wide Screen
    1012 ITEM_LAB NINTENDO_GAMECUBE Nintendo Gamecube
    1013 ITEM_LAB PLAYSTATION2 PlayStation2
    1014 ITEM_LAB XBOX XBox
    1015 ITEM_LAB BLU-RAY Blu-ray

  6. Query the result of inserting 20 rows in the COMMON_LOOKUP_LAB table with the following query: 

    1
2
3
4
5
6
7

    COL common_lookup_id       FORMAT  9999
COL common_lookup_context  FORMAT A22
COL common_lookup_type     FORMAT A18
SELECT   common_lookup_lab_id
,        common_lookup_context
,        common_lookup_type
FROM     common_lookup_lab;

    It should print:

    COMMON_LOOKUP_ID COMMON_LOOKUP_CONTEXT  COMMON_LOOKUP_TYPE
---------------- ---------------------- ------------------
               1 SYSTEM_USER_LAB        SYSTEM_ADMIN
               2 SYSTEM_USER_LAB        DBA
               3 SYSTEM_USER_LAB        SYSTEM_GROUP
               4 SYSTEM_USER_LAB        COST_CENTER
               5 SYSTEM_USER_LAB        INDIVIDUAL
            1001 CONTACT_LAB            EMPLOYEE
            1002 CONTACT_LAB            CUSTOMER
            1003 MEMBER_LAB             INDIVIDUAL
            1004 MEMBER_LAB             GROUP
            1005 MEMBER_LAB             DISCOVER_CARD
            1006 MEMBER_LAB             MASTER_CARD
            1007 MEMBER_LAB             VISA_CARD
            1008 MULTIPLE               HOME
            1009 MULTIPLE               WORK
            1010 ITEM_LAB               DVD_FULL_SCREEN
            1011 ITEM_LAB               DVD_WIDE_SCREEN
            1012 ITEM_LAB               NINTENDO_GAMECUBE
            1013 ITEM_LAB               PLAYSTATION2
            1014 ITEM_LAB               XBOX
            1015 ITEM_LAB               BLU-RAY

  7. Write two queries against the COMMON_LOOKUP_LAB table:

    • One query should return the common_lookup_lab_id column value from the COMMON_LOOKUP_LAB table where the common_lookup_context column value equals an uppercase string literal of 'SYSTEM_USER_LAB' and common_lookup_type column value equals an uppercase string literal of 'SYSTEM_GROUP'. This query should return 3. 

      COMMON_LOOKUP_LAB_ID
--------------------
	           3

    • One query should return the common_lookup_id column value from the COMMON_LOOKUP_LAB table where the common_lookup_context column value equals an uppercase string literal of 'SYSTEM_USER_LAB' and common_lookup_type column value equals an uppercase string literal of 'SYSTEM_ADMIN'. This query should return 1. 

      COMMON_LOOKUP_LAB_ID
--------------------
	           1

  8. Use the two queries from the previous step in UPDATE statements as subqueries:

    • One query should assign the return value of a subquery to the system_user_type column. The subquery return common_lookup_id column value from the COMMON_LOOKUP_LAB table where the common_lookup_context column value equals an uppercase string literal of 'SYSTEM_USER' and common_lookup_type column value equals an uppercase string literal of 'SYSTEM_GROUP'.

      After you run the first UPDATE statement, you can query the result of the UPDATE statement with the following query:

      SET NULL '<Null>'
COL system_user_lab_id    FORMAT  9999
COL system_user_group_id  FORMAT  9999
COL system_user_type      FORMAT  9999
COL system_user_name      FORMAT  9999
SELECT   system_user_lab_id
,        system_user_name
,        system_user_group_id
,        system_user_type
FROM     system_user_lab
WHERE    system_user_id = 1;

      It should show:

                          System
 System System        User  System
   User User         Group    User
   ID # Name          ID #    Type
------- ---------- ------- -------
      1 SYSADMIN         1  <Null>
    • One query should assign the return value of a subquery to the system_user_type column. The subquery should return the common_lookup_id column value from the COMMON_LOOKUP_LAB table where the common_lookup_context column value equals an uppercase string literal of 'SYSTEM_USER' and common_lookup_type column value equals an uppercase string literal of 'SYSTEM_ADMIN'.

      After you run the second UPDATE statement, you can query the result of the UPDATE statement with the following query:

      SET NULL '<Null>'
COL system_user_lab_id    FORMAT  9999
COL system_user_group_id  FORMAT  9999
COL system_user_type      FORMAT  9999
COL system_user_name      FORMAT  9999
SELECT   system_user_lab_id
,        system_user_name
,        system_user_group_id
,        system_user_type
FROM     system_user_lab
WHERE    system_user_id = 1;

      It should show:

                          System
 System System        User  System
   User User         Group    User
   ID # Name          ID #    Type
------- ---------- ------- -------
      1 SYSADMIN	 1	 3

  9. You need to enable the fk_system_user_lab_1 and fk_system_user_lab_2 foreign key constraints for the SYSTEM_USER_LAB table; and enable the fk_clookup_lab_1 and fk_clookup_lab_2 foreign key constraints for the COMMON_LOOKUP_LAB table.

    1. You need to enable the fk_system_user_lab_1 and fk_system_user_lab_2 foreign key foreign key constraints for the SYSTEM_USER_LAB table:

      • Enable the fk_system_user_lab_1 foreign key constraint.
      • Enable the fk_system_user_lab_2 foreign key constraint.

      COL table_name       FORMAT A14  HEADING "Table Name"
COL constraint_name  FORMAT A24  HEADING "Constraint Name"
COL constraint_type  FORMAT A15  HEADING "Constraint Type"
COL status           FORMAT A8   HEADING "Status"
SELECT   table_name
,        constraint_name
,        CASE
           WHEN constraint_type = 'R'        THEN 'FOREIGN KEY'
           WHEN constraint_type = 'P'        THEN 'PRIMARY KEY'
           WHEN constraint_type = 'U'        THEN 'UNIQUE'
           WHEN constraint_type = 'C' AND  REGEXP_LIKE(constraint_name,'^.*nn.*$','i')
           THEN 'NOT NULL'
           ELSE 'CHECK'
         END constraint_type
,        status
FROM     user_constraints
WHERE    table_name = 'SYSTEM_USER_LAB'
ORDER BY CASE
           WHEN constraint_type = 'PRIMARY KEY' THEN 1
           WHEN constraint_type = 'NOT NULL'    THEN 2
           WHEN constraint_type = 'CHECK'       THEN 3
           WHEN constraint_type = 'UNIQUE'      THEN 4
           WHEN constraint_type = 'FOREIGN KEY' THEN 5
         END
,        constraint_name;

      It still should show a gap of not null constraints between nn_system_user_lab_1 and nn_system_user_lab4 constraint names. However, you also should show that you enabled the fk_system_user_lab_3 and fk_system_user_lab_4 foreign keys.

      You should get results like these:

      Table Name         Constraint Name              Constraint Type Status
------------------ ---------------------------- --------------- --------
SYSTEM_USER_LAB    PK_SYSTEM_USER_LAB_1         PRIMARY KEY     ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_1         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_4         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_5         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_6         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_7         NOT NULL        ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_1         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_2         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_3         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_4         FOREIGN KEY     ENABLED

    2. You need to enable the fk_clookup_lab_1 and fk_clookup_lab_2 foreign key foreign key constraints for the COMMON_LOOKUP_LAB table:

      • Enable the fk_clookup_lab_1 foreign key constraint.
      • Enable the fk_clookup_lab_2 foreign key constraint.

      COL table_name       FORMAT A14  HEADING "Table Name"
COL constraint_name  FORMAT A24  HEADING "Constraint Name"
COL constraint_type  FORMAT A15  HEADING "Constraint Type"
COL status           FORMAT A8   HEADING "Status"
SELECT   table_name
,        constraint_name
,        CASE
           WHEN constraint_type = 'R'        THEN 'FOREIGN KEY'
           WHEN constraint_type = 'P'        THEN 'PRIMARY KEY'
           WHEN constraint_type = 'U'        THEN 'UNIQUE'
           WHEN constraint_type = 'C' AND  REGEXP_LIKE(constraint_name,'^.*nn.*$','i')
           THEN 'NOT NULL'
           ELSE 'CHECK'
         END constraint_type
,        status
FROM     user_constraints
WHERE    table_name = 'COMMON_LOOKUP_LAB'
ORDER BY CASE
           WHEN constraint_type = 'PRIMARY KEY' THEN 1
           WHEN constraint_type = 'NOT NULL'    THEN 2
           WHEN constraint_type = 'CHECK'       THEN 3
           WHEN constraint_type = 'UNIQUE'      THEN 4
           WHEN constraint_type = 'FOREIGN KEY' THEN 5
         END
,        constraint_name;

      There should no longer be a gap of not null constraint names. You should see nn_clookup_lab_2 and nn_clookup_lab_3 not null constraints.

      You should get results like these:

      Table Name         Constraint Name          Constraint Type Status
------------------ ------------------------ --------------- --------
COMMON_LOOKUP_LAB  PK_CLOOKUP_LAB_1         PRIMARY KEY     ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_1         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_2         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_3         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_4         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_5         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_6         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_7         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  FK_CLOOKUP_LAB_1         FOREIGN KEY     ENABLED
COMMON_LOOKUP_LAB  FK_CLOOKUP_LAB_2         FOREIGN KEY     ENABLED

  10. You need to enable the fk_clookup_lab_1 and fk_clookup_lab_2 foreign key foreign key constraints for the COMMON_LOOKUP_LAB table:

    • Enable the fk_clookup_lab_1 foreign key constraint.
    • Enable the fk_clookup_lab_2 foreign key constraint.

    COL table_name       FORMAT A14  HEADING "Table Name"
COL constraint_name  FORMAT A24  HEADING "Constraint Name"
COL constraint_type  FORMAT A15  HEADING "Constraint Type"
COL status           FORMAT A8   HEADING "Status"
SELECT   table_name
,        constraint_name
,        CASE
           WHEN constraint_type = 'R'        THEN 'FOREIGN KEY'
           WHEN constraint_type = 'P'        THEN 'PRIMARY KEY'
           WHEN constraint_type = 'U'        THEN 'UNIQUE'
           WHEN constraint_type = 'C' AND  REGEXP_LIKE(constraint_name,'^.*nn.*$','i')
           THEN 'NOT NULL'
           ELSE 'CHECK'
         END constraint_type
,        status
FROM     user_constraints
WHERE    table_name = 'COMMON_LOOKUP_LAB'
ORDER BY CASE
           WHEN constraint_type = 'PRIMARY KEY' THEN 1
           WHEN constraint_type = 'NOT NULL'    THEN 2
           WHEN constraint_type = 'CHECK'       THEN 3
           WHEN constraint_type = 'UNIQUE'      THEN 4
           WHEN constraint_type = 'FOREIGN KEY' THEN 5
         END
,        constraint_name;

    There should no longer be a gap in the not null constraints of the SYSTEM_USER_LAB table. It should display the following results:

    You should get results like these:

     
Table Name         Constraint Name              Constraint Type Status
------------------ ---------------------------- --------------- --------
SYSTEM_USER_LAB    PK_SYSTEM_USER_LAB_1         PRIMARY KEY     ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_1         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_2         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_3         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_4         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_5         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_6         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_7         NOT NULL        ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_1         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_2         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_3         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_4         FOREIGN KEY     ENABLED

  11. You should write two INSERT statements that use the system_user_lab_s1 sequence and four subqueries. The subqueries should do the following:

    1. One subquery should return the common_lookup_lab_id column value from the common_lookup_lab table where the common_lookup_context value equals an uppercase 'SYSTEM_USER' string literal and the common_lookup_type value equals an uppercase 'DBA' string literal.
    2. One subquery should return the common_lookup_lab_id column value from the common_lookup_lab table where the common_lookup_context value equals an uppercase 'SYSTEM_USER' string literal and the common_lookup_type value equals an uppercase 'SYSTEM_GROUP' string literal.
    3. One subquery should return the system_user_lab_id column value from the system_user_lab table where the system_user_name value equals an uppercase 'SYSADMIN' string literal.
    4. One subquery should return the system_user_lab_id column value from the system_user_lab table where the system_user_name value equals an uppercase 'SYSADMIN' string literal.

    Table Name: SYSTEM_USER_LAB
    Column Value #1 Value #1
    SYSTEM_USER_LAB_ID 1001 1002
    SYSTEM_USER_NAME DBA1 DBA2
    COMMON_LOOKUP_CONTEXT SYSTEM_USER_LAB SYSTEM_USER_LAB
    COMMON_LOOKUP_TYPE SYSTEM_GROUP SYSTEM_GROUP
    FIRST_NAME Phineas Phineas
    MIDDLE_NAME Phineas  
    LAST_NAME Phineas Fogg
    CREATED_BY SYSADMIN SYSADMIN
    CREATION_DATE SYSDATE SYSDATE
    LAST_UPDATED_BY SYSADMIN SYSADMIN
    LAST_UPDATE_DATE SYSDATE SYSDATE

  12. You should use the following query to validate the three inserts into the SYSTEM_USER_LAB table: 

    SET NULL '<Null>'
COL system_user_id        FORMAT  9999
COL system_user_group_id  FORMAT  9999
COL system_user_type      FORMAT  9999
COL system_user_name      FORMAT  A10
COL full_user_name        FORMAT  A30
SELECT   system_user_lab_id
,        system_user_group_id
,        system_user_type
,        system_user_name
,        CASE
           WHEN last_name IS NOT NULL THEN
             last_name || ', ' || first_name || ' ' || middle_name
         END AS full_user_name
FROM     system_user_lab;

    It should print the following:

           System
System   User System System
  User  Group   User User
  ID #   ID #   Type Name       FULL_USER_NAME
------ ------ ------ ---------- ------------------------------
     1      1      3 SYSADMIN   <Null>
  1001      2      3 DBA1       Barnum, Phineas Taylor
  1002      2      3 DBA2       Fogg, Phineas

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

@apply_oracle_lab3.sql

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

Written by michaelmclaughlin

August 13th, 2018 at 12:06 am

Posted in