Database Tutorial

Course Tutorial Site

Site Admin

Lab #4 : MySQL Inserts

with 28 comments

Lab #2: Oracle Assignment

You begin these steps by running the apply_mysql_lab2.sql and seed_mysql_store_ri.sql script. A great starting point for this lab is to review the seed_mysql_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/mysql/lab2/apply_mysql_lab2.sql
\. /home/student/Data/cit225/mysql/lib/seed_mysql_store_ri.sql
 
TEE apply_mysql_lab4.txt
 
... insert code here ...
 
NOTEE

You should embed the verification queries inside your apply_mysql_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 them using the auto incrementing sequence and the last_insert_id() function. The last_insert_id() function returns the last auto incrementing sequence value from any table. The value written to the last_insert_id() function may come from any table. This step requires you to capture the last auto incrementing value and assign it to a local variable, which can be done with the following syntax:
SET @lv_table_name_id := last_insert_id();

Written by michaelmclaughlin

December 2nd, 2014 at 2:39 am

Posted in

28 Responses to 'Lab #4 : MySQL Inserts'

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

  1. in step 8 the query says customer_id but I was told it should say customer_lab_id, and the output has more fields than the query asks for.

    Samuel Heindel

    29 Jan 15 at 9:12 pm

  2. step 9 mysql store didnt contain BLU-Ray so I couldnt use it in the Item Lab table

    Samuel Heindel

    29 Jan 15 at 10:12 pm

  3. SYSTEM_USER_LAB table (step 1) the first column should be SYSTEM_USER_LAB_ID instead of SYSTEM_USER_ID.

    ADDRESS_LAB table (step 5) check output is incorrect it is just a copy from step 4.

    TELEPHONE_CODE column name in the TELEPHONE_LAB (STEP 7) should be TELEPHONE_NUMBER.

    All throughout you need to change the last name of Jones to Smith.

    Tyler Nelson

    30 Jan 15 at 12:48 am

  4. RENTAL_LAB table (step 8) check output is incorrect it is just a copy from step 7.

    Tyler Nelson

    30 Jan 15 at 1:05 am

  5. RENTAL_ITEM_LAB table (step 10) needs to have the column RENTAL_ID changed to RENTAL_LAB_ID.

    Tyler Nelson

    30 Jan 15 at 1:19 am

  6. RENTAL_ITEM_LAB table (step 10) needs to have the column ITEM_ID changed to ITEM_LAB_ID.

    Tyler Nelson

    30 Jan 15 at 1:20 am

  7. Step 8 Validation is wrong.

    SELECT   r.rental_lab_id
    ,        r.customer_id
    ,        r.check_out_date
    ,        r.return_date
    FROM     rental_lab r 
    WHERE    r.check_out_date IN ('2015-01-02','2015-01-03');

    Output should be different

    Jeremy

    30 Jan 15 at 5:10 pm

  8. John and Jane Smith in step 4 needs to be changed to John and Jane Jones.

    Jeremy

    30 Jan 15 at 5:11 pm

  9. Jeremy, It’s fixed. It should’ve been Jones.

    michaelmclaughlin

    30 Jan 15 at 5:18 pm

  10. In step 5 the display of the verification query has items in it like credit card type that is not in the verification query.

    James Stakebake

    31 Jan 15 at 1:05 pm

  11. In step 8 the display of the verification query has items in it that is not in the verification query.

    James Stakebake

    31 Jan 15 at 1:22 pm

  12. in step 8 verification query the r.customer_id needs to be changed to r.customer_lab_id

    James Stakebake

    31 Jan 15 at 1:32 pm

  13. verification output display for step 5 is a repeat from step 4. Should display address results instead of credit card.

    Benjamin

    31 Jan 15 at 5:06 pm

  14. Concerning step 1: In Lab 2 you have us change the column system_user_id in the system_user table to system_user_lab_id. Here in Lab 4 your having us insert into the system_user_lab table but the column is still named system_user_id. It should reflect the changes we made in lab 2 and be called system_user_lab_id here as well.

    Jonathan

    5 Feb 15 at 1:37 am

  15. Step 4 should have them be putting in CUSTOMER not group for the contact type.

    Brett

    14 Feb 15 at 4:36 pm

  16. Brett, Great catch. It’s fixed. Thanks.

    michaelmclaughlin

    7 Mar 15 at 2:13 am

  17. Jeremy, I fixed it with the UTC_DATE() function call.

    michaelmclaughlin

    7 Mar 15 at 3:18 am

  18. James, The r.customer_id is correct because it an example of a foreign key column name that doesn’t conform or mirror the primary key column name. Hence, the change to include _lab is unnecessary and incorrect.

    michaelmclaughlin

    8 Mar 15 at 2:39 am

  19. Samuel, The r.customer_id is correct because it an example of a foreign key column name that doesn’t conform or mirror the primary key column name. Hence, the change to include _lab is unnecessary and incorrect.

    michaelmclaughlin

    8 Mar 15 at 2:42 am

  20. Samuel, The Lab #2 Instructions say you should insert it. Does your Lab #2 script insert it?

    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 2:57 am

  21. Jonathan, Great catch! It wasn’t updated to system_user_lab_id and now it is.

    michaelmclaughlin

    8 Mar 15 at 3:04 am

  22. James, You are correct. I’ve fixed the verification output for Step #8. Thanks.

    michaelmclaughlin

    8 Mar 15 at 9:56 pm

  23. James, You are correct. I’ve fixed the verification output for Step #5. Thanks.

    michaelmclaughlin

    8 Mar 15 at 10:00 pm

  24. Tyler, Good catch on Step #10. I’ve renamed the ITEM_ID column to ITEM_LAB_ID.

    michaelmclaughlin

    8 Mar 15 at 10:19 pm

  25. Tyler, Good catch on Step #10. I’ve renamed the RENTAL_ID column to RENTAL_LAB_ID.

    michaelmclaughlin

    8 Mar 15 at 10:26 pm

  26. Benjamin, The output display for Step #5 has been fixed.

    michaelmclaughlin

    8 Mar 15 at 10:34 pm

  27. Tyler, Your observation on Step #8 output appears to have been fixed due to an earlier comment. Thanks though for finding the error.

    michaelmclaughlin

    8 Mar 15 at 10:36 pm

  28. Tyler, Great catches! The observations for Step #1 and Step #5 have been fixed earlier. However, the TELEPHONE_CODE did need to change to TELEPHONE_NUMBER; it’s now fixed. Everything has been changed from Smith to Jones.

    michaelmclaughlin

    8 Mar 15 at 11:17 pm

Leave a Reply