Database Tutorial

Course Tutorial Site

Site Admin

Lab #2 : Oracle Tables

with 46 comments

Lab #2: Oracle Assignment

You begin these steps by running the cleanup_oracle.sql and create_oracle_store.sql scripts. A great starting point for this lab is to review the create_oracle_store.sql script. The create_oracle_store.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 script like:

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

You should embed the verification queries inside your apply_lab2_oracle.sql script.

  1. [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.

  1. [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:
  1. [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:
  1. [2 points] Create the MEMBER_LAB table described by the following and the MEMBER_LAB_S1 sequence starting with a value of 1001:

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

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

  1. [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:

  1. [2 points] Create the TELEPHONE_LAB table described by the following and the TELEPHONE_LAB_S1 sequence starting with a value of 1001:
  1. [2 points] Create the RENTAL_LAB table described by the following and the RENTAL_LAB_S1 sequence starting with a value of 1001:
  1. [2 points] Create the ITEM_LAB table described by the following and the ITEM_LAB_S1 sequence starting with a value of 1001:

  1. [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:

  1. [0 points] You can confirm the creation of the ten tables with the following query:

  1. [0 points] You can confirm the creation of the ten sequences with the following query:

Written by michaelmclaughlin

December 29th, 2009 at 4:40 pm

Posted in

46 Responses to 'Lab #2 : Oracle Tables'

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

  1. 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

  2. (spelling error extra ‘ s ‘) It should displays only the following:

    Cory Allred

    19 Nov 14 at 3:45 pm

  3. 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

  4. ‘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

  5. 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

  6. 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

  7. Christian, Thanks. That’s fixed throughout.

    michaelmclaughlin

    20 Nov 14 at 2:38 pm

  8. Christian, Good catch. It’s fixed.

    michaelmclaughlin

    21 Nov 14 at 12:19 am

  9. Christian, Yes, it’s fixed.

    michaelmclaughlin

    21 Nov 14 at 12:21 am

  10. Cory, Good catch, they’re fixed.

    michaelmclaughlin

    21 Nov 14 at 12:33 am

  11. 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

  12. 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

  13. 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

  14. 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

  15. There is duplicated data in the Common lookup table

    Casey

    15 Jan 15 at 7:52 pm

  16. 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

  17. 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

  18. 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

  19. Madeleine, Great choice. It was an error and has been removed.

    michaelmclaughlin

    16 Jan 15 at 1:37 am

  20. Adam, It was an error and has been removed.

    michaelmclaughlin

    16 Jan 15 at 1:39 am

  21. Joseph, Great catch. It was an error and has been fixed.

    michaelmclaughlin

    16 Jan 15 at 1:43 am

  22. Arden, Got it, and fixed it.

    michaelmclaughlin

    16 Jan 15 at 2:43 am

  23. Casey, I believe these are fixed. Thanks.

    michaelmclaughlin

    16 Jan 15 at 2:54 am

  24. Sam, Is this already fixed?

    michaelmclaughlin

    16 Jan 15 at 3:01 am

  25. Ethan, I believe it’s fixed now, thanks! Great catch.

    michaelmclaughlin

    16 Jan 15 at 3:03 am

  26. 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

  27. Tyler, Great catch, they’re fixed now.

    michaelmclaughlin

    21 Jan 15 at 2:53 am

  28. 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

  29. In step 9’s definition of item, it’s missing release_date.

    Jeremy

    9 Feb 15 at 3:14 pm

  30. Jeremy, It was ITEM_RATING_DATE and is now correctly ITEM_RELEASE_DATE. Thanks.

    michaelmclaughlin

    7 Mar 15 at 2:42 am

  31. 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

  32. 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

  33. 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

  34. 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

  35. Trek, Thanks. I’ve changed contact_lab to item_lab in the query.

    michaelmclaughlin

    25 Sep 15 at 12:00 am

  36. 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

  37. Kami, Already changed.

    michaelmclaughlin

    27 Sep 15 at 5:41 pm

  38. 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

  39. 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

  40. 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

  41. 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

  42. 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

  43. I’ve updated the page to use different constraint name.

    michaelmclaughlin

    29 Apr 16 at 1:57 am

  44. There should only be one row in the SYSTEM_USER and SYSTEM_USER_LAB tables.

    michaelmclaughlin

    29 Apr 16 at 1:59 am

  45. 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

  46. 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

Leave a Reply