Database Tutorial

Course Tutorial Site

Site Admin

Lab #4 : Insert Patterns

with 44 comments

Lab #3: Oracle Assignment Underdevelopment

Objectives

The lab teaches you how to work with sequential and non-sequential INSERT statements across a dependent series of tables. Also, the lab teaches you how to call an API, use subqueries and literal values in the VALUES clause of the INSERT statement; and how to use a derived table instead of a table or view in a query and how to perform a correlated UPDATE statement.

The detail learning objectives are:

  • Learn how to write a set of sequential INSERT statements across a set of dependent tables.
  • Learn how to write a set of non-sequential INSERT statement by leveraging queries to discover appropriate primary key values in an independent table that you can use as foreign key values in a related dependent table.
  • Learn how to use the .NEXTVAL pseudo column in one table as a primary key value; and the .CURRVAL pseudo column in another table with a dependency on the former table as a foreign key value.
  • Learn how to call a store procedure that acts like an API (Application Programming Interface) to a set of tables.
  • Learn how to write a subquery as a derived table (or the result of a table subquery) inside the FROM clause.
  • Learn how to correlate (or join) the results of a subquery (a correlated subquery) with an external UPDATE statement.

Business Scenario

Building data-centric application software often requires that you pre-seed data in certain database tables. The process of pre-seeding involves inserting rows of data. Sometimes the Entity Relationship Diagram (ERD) can create conflicts, which require you to temporarily disable constraints to insert data.

The lab is designed to teach you how to write INSERT statements. The INSERT statements use:

  • Literal values
  • Pseudo columns for sequence values
  • Subqueries that retrieve surrogate primary key values from tables by using the natural keys of the tables

You will disable foreign key constraints and remove not null constraints to pre-seed some of the tables with data. After inserting the new data, you will add re-enable foreign key constraints and re-add not null constraints. You should use the preseed_oracle_store.sql script as a reference model for SQL syntax.

Help Section

The following is an set of sample programs and steps that address concepts in the lab. The instructor should review these in classroom or in a Google Hangout with students. Click the Instructional Material link to see the preparation lesson material.

The lab has ten parts. Each of the parts links to a SQL script file, and you should treat each file as a distinct use case that requires unit testing. The last part actually calls the nine SQL scripts in sequence and becomes an integration test of your code.

Lab Description

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

The lab requires that you run two preparation scripts, which in their respective sequence of operation call other scripts. You will call the seeding.sql script from the /home/student/Data/cit225/oraclelib2/seed directory.

Its recommended that you create your apply_oracle_lab4.sql script in the lab4 directory first. While you’ll add to this file later, the initial file should have the following functional lines:

-- Run the prior lab script.
@/home/student/Data/cit225/oracle/lab3/apply_oracle_lab3.sql
@/home/student/Data/cit225/oracle/lib2/seed/seeding.sql
 
-- insert calls to other SQL script files here, in the order that they appear in the seeding.sql script ...
 
SPOOL apply_oracle_lab4.txt
 
-- insert your SQL statements here ... 
-- start with the validation scripts you find in the seeding.sql script.
-- copying the seeding.sql file and editing it to conform to this layout is the simplest approach to the lab. 
 
SPOOL OFF
 
-- ------------------------------------------------------------------
--  This is necessary to avoid a resource busy error. You can
--  inadvertently create a resource busy error when testing in two
--  concurrent SQL*Plus sessions unless you provide an explicit
--  COMMIT; statement. 
-- ------------------------------------------------------------------
COMMIT;

Click the Lab Instructions link to display the lab steps:

Test Case

Click the Test Case Instructions link to open the test case instructions inside the current webpage.

Written by michaelmclaughlin

January 26th, 2010 at 2:34 am

Posted in

44 Responses to 'Lab #4 : Insert Patterns'

Subscribe to comments with RSS or TrackBack to 'Lab #4 : Insert Patterns'.

  1. CREDIT_CARD_TYPE in member ADDRESS_ID in telephone are missing the foreign key information on this page.

    William Sawyer

    27 Jan 10 at 2:51 pm

  2. Another thing we found: The table definition of MEMBER of this page shows MEMBER with the column MEMBER_TYPE. In the create_store.sql script MEMBER does not have that column.

    William Sawyer

    27 Jan 10 at 5:09 pm

  3. The create_store.sql script doesn’t contain the column, and they should add the column with an ALTER TABLE statement.

    michaelmclaughlin

    28 Jan 10 at 12:42 am

  4. I’ve fixed the table definition for CREDIT_CARD_TYPE, and it now contains a foreign key reference. The other isn’t an error but it was missing a buisness rule. I’ve noted it in the page above. Thanks for the great catch.

    michaelmclaughlin

    28 Jan 10 at 12:47 am

  5. In the CONTACT table MEMBER_ID is referenced incorrectly to common_lookup, It should be referenced to the MEMBER table.

    William Sawyer

    28 Jan 10 at 1:49 pm

  6. Suggestion: middle_name column be renamed to middle_initial in the contact table for clarity.

    Blaine Forbush

    29 Jan 10 at 12:46 pm

  7. Actually in the provided script on I-Learn middle_name is of CHAR(20) just like first_name and last_name so this is appropriate. He just needs to update the table on this website to reflect that. Also the order in the CONTACT table goes first_name, middle_name, then last_name. He has last_name, first_name, middle_name. So for this lab it is better to actually use the script he provides as reference.

    Trent Davis

    29 Jan 10 at 7:03 pm

  8. Great catch. The Oracle script differs from the MySQL script. That wasn’t the intention. I’m fixing the scripts so they’re mirrors. In the meantime, I’ve updated this page to reflect the correct model for the future.

    michaelmclaughlin

    29 Jan 10 at 11:01 pm

  9. “The TELEPHONE table contains an ADDRESS_ID column without a foreign key constraint”

    Should read “without a NOT NULL constraint”

    Andrew Thimmig

    21 Jan 11 at 2:25 pm

  10. In the deliverables section, in the ADDRESS table the POSTAL_CODE column does not specify a constraint, but the create_oracle_store.sql and create_mysql_store.sql put a NOT NULL constraint on POSTAL_CODE.

    Eli

    12 May 11 at 1:37 pm

  11. Eli, Thanks, I’ve fixed it.

    michaelmclaughlin

    25 Oct 14 at 6:19 pm

  12. in step 2 dealing with the common_lookup_lab table. output should have member_lab instead of member.

    Tyler Nelson

    27 Jan 15 at 8:59 pm

  13. On step one the system_user_name for Ray Owens is OWENSR but in the output it is OWENS.

    Danielle

    27 Jan 15 at 9:02 pm

  14. On the rental_lab_id and the item_lab_id I am producing different values from the lab. The lab returns the values 1005 and 1006 for the rental_lab_id, and the item_lab_id returns 1003 and 1004. However, I am both returning 1001 and 1002 for both ID’s because the sequence I thought was supposed to start at 1001 on lab #2. If someone could explain that would be great!

    Carissa L

    29 Jan 15 at 7:45 pm

  15. With the addresses themselves being Identical in step 5 how would you use a Scalar Subquery to tie the Street addresses in step 6 to the correct address in step 5. The only way I can think is with a join or a nested subquery which has not been covered yet and I feel is beyond current scope of the class.

    Matt Mason

    29 Jan 15 at 7:48 pm

  16. Also, on the last query in step 11, the table says to input the return date for the second column to be January 9, 2015; however, the validation code only looks for January 8, 2015.

    Carissa L

    29 Jan 15 at 10:52 pm

  17. All throughout you need to change the last name of Jones to Smith because that is the last name you had us use when inserting John and Jane.

    Tyler Nelson

    30 Jan 15 at 1:23 am

  18. Tyler, It should be fixed now.

    michaelmclaughlin

    30 Jan 15 at 5:28 pm

  19. step 11

    the credit card number for ‘john’ and ‘youndo’ are the same

    Joseph Tracy

    30 Jan 15 at 5:28 pm

  20. Step 11

    SELECT   r.check_out_date
    ,        r.return_date
    ,        ri.rental_item_lab_id
    ,        i.item_title
    FROM     rental_lab r INNER JOIN rental_item_lab ri
    ON       r.rental_lab_id = ri.rental_lab_id INNER JOIN item_lab i
    ON       ri.item_lab_id = i.item_lab_id
    WHERE    r.check_out_date = '08-JAN-2015';

    need to search for January 8, 2015 and January 9, 2015

    matthew fox

    31 Jan 15 at 11:26 am

  21. you are missing CONTACT_LAB_ID in the data table telling the students the data fro the inserts into address step 5.

    (AS PER TA WITH ROUND GLASSES)

    Tyler Wilhelm

    31 Jan 15 at 2:48 pm

  22. Step 3 instructions say 'MEMBER_LAB' should be inserted into column COMMON_LOOKUP_CONTEXT, but the expected results list 'MEMBER'.

    Dave Stevenson

    4 Feb 15 at 3:43 pm

  23. In step 4, the common lookup entry for 'CUSTOMER' has context 'CONTACT'. However, the verification query uses context 'CONTACT_LAB', so it fails to find the inserted rows.

    Dave Stevenson

    4 Feb 15 at 3:57 pm

  24. In step 5, the common lookup entry for 'CUSTOMER' has context 'CONTACT'. However, the verification query uses context 'CONTACT_LAB', so it fails to find the inserted rows.

    Dave Stevenson

    4 Feb 15 at 3:59 pm

  25. In step 6, the common lookup entry for 'CUSTOMER' has context 'CONTACT'. However, the verification query uses context 'CONTACT_LAB', so it fails to find the inserted rows.

    Dave Stevenson

    4 Feb 15 at 4:00 pm

  26. Step 10 expects results where foreign keys seem more spread out, perhaps because several records are created in the database used by the course creator. However, our instructions are to only create two records, so I believe we should expect foreign keys no greater than 1002:

    RENTAL_ITEM_LAB_ID RENTAL_LAB_ID ITEM_LAB_ID
    ------------------ ------------- -----------
                  1001          1001        1001
                  1002          1002        1002

    Dave Stevenson

    4 Feb 15 at 4:32 pm

  27. In step 5, you forgot to include contact_lab_id in the diagram for the address_lab insert.

    Jeremy

    9 Feb 15 at 1:15 pm

  28. Step 7’s chart should be telephone_number and not telephone_code.

    Jeremy

    9 Feb 15 at 2:59 pm

  29. Joseph, They should be the same because they belong to the same record (row) in the MEMBER table. Much as a dependent would use their parents’ credit card.

    michaelmclaughlin

    6 Mar 15 at 9:33 pm

  30. Jeremy, I believe that telephone_code is already telephone_number fixed in Step #7.

    michaelmclaughlin

    7 Mar 15 at 2:41 am

  31. Jeremy, The corrections has already been made.

    michaelmclaughlin

    7 Mar 15 at 2:46 am

  32. Matthew, I fixed the date to TRUNC(SYSDATE).

    michaelmclaughlin

    7 Mar 15 at 3:14 am

  33. Dave, That’s a great catch. I’ve updated the results from steps 8 through 10. Here are the results from step 10:

    RENTAL_ITEM_LAB_ID RENTAL_LAB_ID ITEM_LAB_ID
    ------------------ ------------- -----------
                  1001          1001        1001
                  1002          1002        1002

    michaelmclaughlin

    8 Mar 15 at 1:31 am

  34. Dave, Those are incorrect assessments of Steps #3, #4, #5, and #6. Step #2 in the Oracle Lab #2 web page instructs you to enter the following values into the COMMON_LOOKUP_LAB table:

    Table Name: COMMON_LOOKUP_LAB
    Context Type Meaning
    SYSTEM_USER_LAB SYSTEM_ADMIN System Administrator
    SYSTEM_USER_LAB DBA Database Administrator
    CONTACT_LAB EMPLOYEE Employee
    CONTACT_LAB CUSTOMER Customer
    MEMBER_LAB INDIVIDUAL Individual Membership
    MEMBER_LAB GROUP Group Membership
    MEMBER_LAB DISCOVER_CARD Discover Card
    MEMBER_LAB MASTER_CARD Master Card
    MEMBER_LAB VISA_CARD VISA Card
    MULTIPLE HOME Home
    MULTIPLE WORK Work
    ITEM_LAB DVD_FULL_SCREEN DVD: Full Screen
    ITEM_LAB DVD_WIDE_SCREEN DVD: Wide Screen
    ITEM_LAB NINTENDO_GAMECUBE Nintendo Gamecube
    ITEM_LAB PLAYSTATION2 PlayStation2
    ITEM_LAB XBOX XBox
    ITEM_LAB BLU-RAY Blu-ray

    michaelmclaughlin

    8 Mar 15 at 1:40 am

  35. Tyler, Brett (the TA with the round glasses) is right. I’ve fixed the table, and it’s now correct. Thanks!

    michaelmclaughlin

    8 Mar 15 at 2:00 am

  36. Carissa, I’ve fixed that earlier by making the insert date into a TRUNC(SYSDATE) value, and providing a remark that if the current date is January 8, 2015, the value would return 08-JAN-15. Does that work for you?

    michaelmclaughlin

    8 Mar 15 at 2:04 am

  37. Matt, It’s never a good sign when the tutor asks how. 🙂 The subquery needs to tie the address and contact table together to resolve the address_id, like this subquery in a VALUES clause of an INSERT statement:

    (SELECT   address_lab_id
     FROM     address_lab a INNER JOIN contact_lab c
     ON       a.contact_lab_id = c.contact_lab_id
     WHERE    c.first_name = 'John'
     AND      c.last_name = 'Jones'
     AND      a.city = 'Draper'
     AND      a.state_province = 'Utah')

    michaelmclaughlin

    8 Mar 15 at 2:14 am

  38. Carissa, You’re correct and the wrong output values where copied due to an error in my test script. I’ve fixed the solution script and the displayed values. Thanks!

    michaelmclaughlin

    8 Mar 15 at 2:15 am

  39. Tyler, Great catch. Yes, you’re right. It’s fixed now, and the output now displays MEMBER_LAB.

    michaelmclaughlin

    8 Mar 15 at 2:20 am

  40. Danielle, You’re right, and OWENS is now OWENSR. Thanks!

    michaelmclaughlin

    8 Mar 15 at 2:23 am

  41. All of the test queries for common_lookup_lab don’t have the right names (like rather than it saying common_lookup_lab_context or common_lookup_lab_type it says common_lookup_context or common_lookup_type). We were instructed to change everything to common_lookup_lab, so now we have to go and modify all of the test queries to put lab on the end. Would it be possible to get that changed in the future?

    Joshua

    6 Oct 15 at 7:16 pm

  42. So, we noticed that there is a error in the last line of the validation code of Step 11 part 2. It asks for a checkout date validating SYSDATE and not the dates listed in the lab.

    Robert Collins

    8 Oct 15 at 7:54 pm

  43. Joshua, Only the table name, common_lookup becomes common_lookup_lab and common_lookup_id becomes common_lookup_lab_id in Lab #2. You should not add _lab to the other columns of the common_lookup_lab table.

    michaelmclaughlin

    8 Oct 15 at 10:49 pm

  44. Robert, Thank you! Excellent catch, which I’ve fixed as noted below:

    WHERE    r.check_out_date IN ('08-JAN-2015','09-JAN-2015');

    michaelmclaughlin

    8 Oct 15 at 10:58 pm

Leave a Reply