Database Tutorial

Course Tutorial Site

Site Admin

Lab #4 : Oracle Inserts

with 44 comments

Lab #2: Oracle Assignment

You begin these steps by running the apply_oracle_lab2.sql and seed_oracle_store.sql script. A great starting point for this lab is to review the seed_oracle_store.sql script. You should use the script provided in the downloaded instance or create a script like:

-- Run the prior lab script.
@/home/student/Data/cit225/oracle/lab2/apply_oracle_lab2.sql
@/home/student/Data/cit225/oracle/lib/seed_oracle_store.sql
 
SPOOL apply_oracle_lab4.txt
 
... insert code here ...
 
SPOOL OFF

You should embed the verification queries inside your apply_oracle_lab4.sql script. All foreign key columns in steps 1 through 10 should be populated by subqueries that use the natural key values to discover the surrogate key values. No credit will be given for INSERT statements that use numeric literals for foreign key column values with an exception for both the CREATED_BY and LAST_UPDATED_BY columns.

Step 11 requires you to enter data values into the MEMBER_LAB, CONTACT_LAB, ADDRESS_LAB, STREET_ADDRESS_LAB, TELEPHONE_LAB, RENTAL_LAB, and RENTAL_ITEM_LAB tables. You will insert data into these tables in a specific order. The order depends on the degree that tables are unconstrained or constrained by their foreign key to primary key dependencies. That means you insert into the least dependent table first, which is the MEMBER_LAB table because it only has dependencies on the SYSTEM_USER_LAB and COMMON_LOOKUP_LAB tables. After inserting into the MEMBER_LAB table, you insert customer data into the CONTACT_LAB, ADDRESS_LAB table, STREET_ADDRESS_LAB table, and TELEPHONE_LAB tables. Then, you insert rows in the RENTAL_LAB and RENTAL_ITEM_LAB tables. Step 11 mimics how you would insert values into a series of tables in a dependent relationship from a web application form.

  1. [1 point] Insert two rows into the SYSTEM_USER_LAB table:
  1. [1 point] Insert two rows into the COMMON_LOOKUP_LAB table:
  1. [1 point] Insert two rows into the MEMBER_LAB table:

  1. [1 point] Insert two rows into the CONTACT_LAB table:

  1. [1 point] Insert two rows into the ADDRESS_LAB table::

  1. [1 point] Insert two rows into the STREET_ADDRESS_LAB table:

  1. [1 point] Insert two rows into the TELEPHONE_LAB table:
  1. [1 point] Insert two rows into the RENTAL_LAB table:
  1. [1 points] Insert two rows into the ITEM_LAB table:
  1. [1 point] Insert two rows into the RENTAL_ITEM_LAB table:

  1. [10 points] You insert rows into the a series of tables differently than you insert rows individually. For example, you insert the primary key by using the sequence and .NEXTVAL pseudo column value; and you insert the foreign key by using the sequence and .CURRVAL pseudo column value. You can read more about the .NEXTVAL and .CURRVAL pseudo columns on this web page on automatic numbering.

Written by michaelmclaughlin

January 26th, 2010 at 2:34 am

Posted in

44 Responses to 'Lab #4 : Oracle Inserts'

Subscribe to comments with RSS or TrackBack to 'Lab #4 : Oracle Inserts'.

  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