Database Tutorial

Course Tutorial Site

Site Admin

Lab #9: MySQL

with 28 comments

Lab #9: MySQL Assignment

You begin these steps after running the create_oracle_mysql_ri2.sql, the seed_mysql_store_ri2.sql, apply_mysql_lab5.sql, apply_mysql_lab6.sql, apply_mysql_lab7.sql, and apply_mysql_lab8.sql scripts. You should create the apply_mysql_lab9.sql script as follows:

-- This calls Lab #8, Lab #8 calls Lab #7, Lab #7 calls Lab #6, Lab #6 calls Lab #5, and Lab #5 calls both the Creation and Seed script files.
\. ../lab8/apply_mysql_lab8.sql
 
TEE apply_mysql_lab9.txt
 
... insert code here ...
 
NOTEE

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

  1. [4 points] Create the following TRANSACTION table as per the specification, but do so understanding the business logic of the model.

  1. You create the TRANSACTION table as qualified below.

Implementation Note: The TRANSACTION table has a range relationship to a CALENDAR table that will be defined later.

Business Rule: You may enter an account number into the TRANSACTION_ACCOUNT column of 111-111-111-111 for a debit, and 222-222-222-222 for a credit. A debit recognizes payment for a rental, and is a value that adds to the company’s assets. A credit recognizes a refund for a rental, and is a value that decreases the company’s assets. Debits should have a TRANSACTION_TYPE that maps to the corresponding debit row in the COMMON_LOOKUP table, and credits should have a TRANSACTION_TYPE that maps to the corresponding credit row in the COMMON_LOOKUP table.

System Logic: In a future refinement of our pilot design, the TRANSACTION_ACCOUNT column will be replaced by a foreign key that points to the CHART_OF_ACCOUNT table.

Table Name: TRANSACTION
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
TRANSACTION_ID PRIMARY KEY Integer Maximum
TRANSACTION_ACCOUNT NOT NULL String 15
TRANSACTION_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
NOT NULL
TRANSACTION_DATE NOT NULL Date Date
TRANSACTION_AMOUNT NOT NULL Float Maximum
RENTAL_ID FOREIGN KEY RENTAL RENTAL_ID Integer Maximum
NOT NULL
PAYMENT_METHOD_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
NOT NULL
PAYMENT_ACCOUNT_NUMBER NOT NULL String 20
CREATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
NOT NULL
CREATION_DATE NOT NULL DATE Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
NOT NULL
LAST_UPDATE_DATE NOT NULL DATE Date

* The TRANSACTION_AMOUNT column uses a float data type to designate that the column may contain an integer or decimal value.

You should use the following query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10
11

SELECT   table_name
,        ordinal_position
,        column_name
,        CASE
           WHEN is_nullable = 'NO' THEN 'NOT NULL'
           ELSE ''
         END AS constraints
,        column_type
FROM     information_schema.columns
WHERE    table_name = 'transaction'
ORDER BY ordinal_position;

It should display the following results:

+-------------+------------------+------------------------+-------------+------------------+
| table_name  | ordinal_position | column_name            | constraints | column_type      |
+-------------+------------------+------------------------+-------------+------------------+
| transaction |                1 | transaction_id         | NOT NULL    | int(10) unsigned |
| transaction |                2 | transaction_account    | NOT NULL    | varchar(15)      |
| transaction |                3 | transaction_type       | NOT NULL    | int(10) unsigned |
| transaction |                4 | transaction_date       | NOT NULL    | date             |
| transaction |                5 | transaction_amount     | NOT NULL    | double           |
| transaction |                6 | rental_id              | NOT NULL    | int(10) unsigned |
| transaction |                7 | payment_method_type    | NOT NULL    | int(10) unsigned |
| transaction |                8 | payment_account_number | NOT NULL    | varchar(20)      |
| transaction |                9 | created_by             | NOT NULL    | int(10) unsigned |
| transaction |               10 | creation_date          | NOT NULL    | date             |
| transaction |               11 | last_updated_by        | NOT NULL    | int(10) unsigned |
| transaction |               12 | last_update_date       | NOT NULL    | date             |
+-------------+------------------+------------------------+-------------+------------------+
12 rows in set (0.00 sec)

Unfortunately, the previous query doesn’t qualify the starting value of auto incrementing values or the engine used to create the TRANSACTION table. The following SHOW command does show you the details:

SHOW CREATE TABLE transaction\G

It should display:

*************************** 1. row ***************************
       Table: transaction
Create Table: CREATE TABLE `transaction` (
  `transaction_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `transaction_account` varchar(15) NOT NULL,
  `transaction_type` varchar(30) NOT NULL,
  `transaction_date` date NOT NULL,
  `transaction_amount` double NOT NULL,
  `rental_id` int(10) unsigned NOT NULL,
  `payment_method_type` int(10) unsigned NOT NULL,
  `payment_account_number` varchar(20) NOT NULL,
  `created_by` int(10) unsigned NOT NULL,
  `creation_date` date NOT NULL,
  `last_updated_by` int(10) unsigned NOT NULL,
  `last_update_date` date NOT NULL,
  PRIMARY KEY (`transaction_id`),
  KEY `fk_transaction1` (`created_by`),
  KEY `fk_transaction2` (`last_updated_by`)
) ENGINE=MEMORY AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

  1. You create the NATURAL_KEY unique index as qualified below.

After you create the table, you need to add a UNIQUE INDEX on the following columns to the TRANSACTION table. This is necessary to improve the run-time performance of the merge operations later in this lab.

  • RENTAL_ID
  • TRANSACTION_TYPE
  • TRANSACTION_DATE
  • PAYMENT_METHOD_TYPE
  • PAYMENT_ACCOUNT_NUMBER
  • TRANSACTION_ACCOUNT

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10

SELECT   tc.table_name
,        tc.constraint_name
,        kcu.ordinal_position
,        kcu.column_name 
FROM     information_schema.table_constraints tc INNER JOIN
         information_schema.key_column_usage kcu
ON       tc.table_name = kcu.table_name
AND      tc.constraint_name = kcu.constraint_name
AND      tc.constraint_type = 'UNIQUE'
AND      tc.table_name = 'transaction';

It should display the following results:

+-------------+-----------------+------------------+------------------------+
| table_name  | constraint_name | ordinal_position | column_name            |
+-------------+-----------------+------------------+------------------------+
| transaction | natural_key     |                1 | rental_id              |
| transaction | natural_key     |                2 | transaction_type       |
| transaction | natural_key     |                3 | transaction_date       |
| transaction | natural_key     |                4 | payment_method_type    |
| transaction | natural_key     |                5 | payment_account_number |
| transaction | natural_key     |                6 | transaction_amount     |
+-------------+-----------------+------------------+------------------------+
6 rows in set (0.06 sec)
  1. [2 points] Insert the following two TRANSACTION_TYPE rows and four PAYMENT_METHOD_TYPE rows into the COMMON_LOOKUP table. They should have valid who-audit column data. After you insert the TRANSACTION_TYPE and PAYMENT_METHOD_TYPE rows in the COMMON_LOOKUP table.

Table Name: COMMON_LOOKUP
Lookup Table Lookup Column Lookup Type Lookup Meaning Lookup Code
TRANSACTION TRANSACTION_TYPE CREDIT Credit CR
TRANSACTION TRANSACTION_TYPE DEBIT Debit DR
TRANSACTION PAYMENT_METHOD_TYPE DISCOVER_CARD Discover Card
TRANSACTION PAYMENT_METHOD_TYPE VISA_CARD Visa Card
TRANSACTION PAYMENT_METHOD_TYPE MASTER_CARD Master Card
TRANSACTION PAYMENT_METHOD_TYPE CASH Cash
RENTAL_ITEM RENTAL_ITEM_TYPE 1-DAY RENTAL 1-Day Rental 1
RENTAL_ITEM RENTAL_ITEM_TYPE 3-DAY RENTAL 3-Day Rental 3
RENTAL_ITEM RENTAL_ITEM_TYPE 5-DAY RENTAL 5-Day Rental 6

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6
7

SELECT   cl.common_lookup_table
,        cl.common_lookup_column
,        cl.common_lookup_type
FROM     common_lookup cl
WHERE    cl.common_lookup_table = 'TRANSACTION'
AND      cl.common_lookup_column IN ('TRANSACTION_TYPE','PAYMENT_METHOD_TYPE')
ORDER BY 1, 2, 3 DESC;

It should display the following results:

+---------------------+----------------------+--------------------+
| common_lookup_table | common_lookup_column | common_lookup_type |
+---------------------+----------------------+--------------------+
| TRANSACTION         | PAYMENT_METHOD_TYPE  | VISA_CARD          |
| TRANSACTION         | PAYMENT_METHOD_TYPE  | MASTER_CARD        |
| TRANSACTION         | PAYMENT_METHOD_TYPE  | DISCOVER_CARD      |
| TRANSACTION         | PAYMENT_METHOD_TYPE  | CASH               |
| TRANSACTION         | TRANSACTION_TYPE     | DEBIT              |
| TRANSACTION         | TRANSACTION_TYPE     | CREDIT             |
+---------------------+----------------------+--------------------+
6 rows in set (0.01 sec)

  1. [14 points] Create the following AIRPORT and ACCOUNT_LIST tables as per the specification, but do so understanding the business logic of the model.

Implementation Note: The AIRPORT table has a filtered substring relationship to ACCOUNT numbers because the business model is evolving toward airport kiosks rather than stores. The ACCOUNT_LIST contains a set of pre-seeded values that may be consumed as new customers are entered into the Video Store application.

Business Rule: You must seed the AIRPORT with any additional information that may be added to the model to support new customers. The city where the customer lives needs to be mapped to the nearest regional airport. All ACCOUNT_NUMBER column values must contain a valid account number found in the ACCOUNT_LIST table. When an account number is assigned to a customer, you must indicate the SYSTEM_USER_ID linked to assigning the account number in the CONSUMED_BY column of the ACCOUNT_LIST table, and provide the date of that activity in the CONSUMED_BY column.

System Logic: Seeding procedures are provided in the lab to update all existing data in the model, provided you add any required new cities in the AIRPORT table. The import of external records is now dependent on accurate ACCOUNT_NUMBER values in the MEMBER table. You must update all pre-existing records.

  1. You need to create the AIRPORT table.

Table Name: AIRPORT
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
AIRPORT_ID PRIMARY KEY Integer Maximum
AIRPORT_CODE NOT NULL String 3
AIRPORT_CITY NOT NULL String 30
CITY NOT NULL String 30
STATE_PROVINCE NOT NULL String 30
CREATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
NOT NULL
CREATION_DATE NOT NULL DATE Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
NOT NULL
LAST_UPDATE_DATE NOT NULL DATE Date

You should use the following query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10
11

SELECT   table_name
,        ordinal_position
,        column_name
,        CASE
           WHEN is_nullable = 'NO' THEN 'NOT NULL'
           ELSE ''
         END AS constraints
,        column_type
FROM     information_schema.columns
WHERE    table_name = 'airport'
ORDER BY ordinal_position;

It should display the following results:

+------------+------------------+------------------+-------------+------------------+
| table_name | ordinal_position | column_name      | constraints | column_type      |
+------------+------------------+------------------+-------------+------------------+
| airport    |                1 | airport_id       | NOT NULL    | int(10) unsigned |
| airport    |                2 | airport_code     | NOT NULL    | char(3)          |
| airport    |                3 | airport_city     | NOT NULL    | char(30)         |
| airport    |                4 | city             | NOT NULL    | char(30)         |
| airport    |                5 | state_province   | NOT NULL    | char(30)         |
| airport    |                6 | created_by       | NOT NULL    | int(10) unsigned |
| airport    |                7 | creation_date    | NOT NULL    | date             |
| airport    |                8 | last_updated_by  | NOT NULL    | int(10) unsigned |
| airport    |                9 | last_update_date | NOT NULL    | date             |
+------------+------------------+------------------+-------------+------------------+
9 rows in set (0.01 sec)

  1. You need to create a unique index for the AIRPORT table, named nk_airport.

  • AIRPORT_CODE
  • AIRPORT_CITY
  • CITY
  • STATE_PROVINCE

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10

SELECT   tc.table_name
,        tc.constraint_name
,        kcu.ordinal_position
,        kcu.column_name 
FROM     information_schema.table_constraints tc INNER JOIN
         information_schema.key_column_usage kcu
ON       tc.table_name = kcu.table_name
AND      tc.constraint_name = kcu.constraint_name
AND      tc.constraint_type = 'UNIQUE'
AND      tc.table_name = 'airport';

It should display the following results:

+------------+-----------------+------------------+----------------+
| table_name | constraint_name | ordinal_position | column_name    |
+------------+-----------------+------------------+----------------+
| airport    | nk_airport      |                1 | airport_code   |
| airport    | nk_airport      |                2 | airport_city   |
| airport    | nk_airport      |                3 | city           |
| airport    | nk_airport      |                4 | state_province |
+------------+-----------------+------------------+----------------+
4 rows in set (0.04 sec)

  1. You need to seed the AIRPORT table with at least these cities, and any others that you’ve used for inserted values in the CONTACT table.

Table Name: AIRPORT
Airport Code Airport City City State Province
LAX Los Angeles Los Angeles California
SLC Salt Lake City Provo Utah
SLC Salt Lake City Spanish Fork Utah
SFO San Francisco San Francsico California
SJC San Jose San Jose California
SJC San Jose San Carlos California

You should use the following query to verify completion of this step:

1
2
3
4
5

SELECT   airport_code
,        airport_city
,        city
,        state_province
FROM     airport;

It should display the following results:

+--------------+----------------+---------------+----------------+
| airport_code | airport_city   | city          | state_province |
+--------------+----------------+---------------+----------------+
| LAX          | Los Angeles    | Los Angeles   | California     |
| SFO          | San Francisco  | San Francisco | California     |
| SJC          | San Jose       | San Carlos    | California     |
| SJC          | San Jose       | San Jose      | California     |
| SLC          | Salt Lake City | Provo         | Utah           |
| SLC          | Salt Lake City | Spanish Fork  | Utah           |
+--------------+----------------+---------------+----------------+
6 rows in set (0.02 sec)

  1. You need to create the ACCOUNT_LIST table.

Table Name: ACCOUNT_LIST
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
ACCOUNT_LIST_ID PRIMARY KEY Integer Maximum
ACCOUNT_NUMBER NOT NULL String 10
CONSUMED_DATE Date Date
CONSUMED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
CREATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
CREATION_DATE NOT NULL DATE Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
LAST_UPDATE_DATE NOT NULL DATE Date

You should use the following query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10
11

SELECT   table_name
,        ordinal_position
,        column_name
,        CASE
           WHEN is_nullable = 'NO' THEN 'NOT NULL'
           ELSE ''
         END AS constraints
,        column_type
FROM     information_schema.columns
WHERE    table_name = 'account_list'
ORDER BY ordinal_position;

It should display the following results:

+--------------+------------------+------------------+-------------+------------------+
| table_name   | ordinal_position | column_name      | constraints | column_type      |
+--------------+------------------+------------------+-------------+------------------+
| account_list |                1 | account_list_id  | NOT NULL    | int(10) unsigned |
| account_list |                2 | account_number   | NOT NULL    | varchar(10)      |
| account_list |                3 | consumed_date    |             | date             |
| account_list |                4 | consumed_by      |             | int(10) unsigned |
| account_list |                5 | created_by       | NOT NULL    | int(10) unsigned |
| account_list |                6 | creation_date    | NOT NULL    | date             |
| account_list |                7 | last_updated_by  | NOT NULL    | int(10) unsigned |
| account_list |                8 | last_update_date | NOT NULL    | date             |
+--------------+------------------+------------------+-------------+------------------+
8 rows in set (0.01 sec)

  1. You need to seed the ACCOUNT_LIST table. This would take more time than leveraging a stored procedure but writing stored procedures from class is a beyond scope. Therefore, you’ve been provided with stored procedures for the databases.

Setup Script ↓

The drop down items, unfold with source code to seed the examples.

This contains the necessary seeding code that lets you apply specific rules to populate the ACCOUNT_LIST table. It is dependent upon you’re seeding of the AIRPORT table. It won’t work correctly if you have incorrect values in that table.

You should just copy and past this code into a text editor of your choice. Then, you can run it as the student user against the sampledb database. After you have it in a file, you should run it to place the procedure in your database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

-- Conditionally drop the procedure.
SELECT 'DROP PROCEDURE seed_account_list' AS "Statement";
DROP PROCEDURE IF EXISTS seed_account_list;
 
-- Create procedure to insert automatic numbered rows.
SELECT 'CREATE PROCEDURE seed_account_list' AS "Statement";
 
-- Reset delimiter to write a procedure.
DELIMITER $$
 
CREATE PROCEDURE seed_account_list() MODIFIES SQL DATA
BEGIN
 
  /* Declare local variable for call parameters. */
  DECLARE lv_key CHAR(3);
 
  /* Declare local control loop variables. */
  DECLARE lv_key_min  INT DEFAULT 0;
  DECLARE lv_key_max  INT DEFAULT 50;
 
  /* Declare a local variable for a subsequent handler. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE fetched INT DEFAULT 0;
 
  /* Declare a SQL cursor fabricated from local variables. */  
  DECLARE parameter_cursor CURSOR FOR
    SELECT DISTINCT airport_code FROM airport;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT all_or_none;
 
  /* Open a local cursor. */  
  OPEN parameter_cursor;
  cursor_parameter: LOOP
 
    FETCH parameter_cursor
    INTO  lv_key;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_parameter; END IF;
 
    seed: WHILE (lv_key_min < lv_key_max) DO
      SET lv_key_min = lv_key_min + 1;
 
      INSERT INTO account_list
      VALUES
      ( null
      , CONCAT(lv_key,'-',LPAD(lv_key_min,6,'0'))
      , null
      , null
      , 1002
      , UTC_DATE()
      , 1002
      , UTC_DATE());
    END WHILE;
 
    /* Reset nested low range variable. */
    SET lv_key_min = 0;
 
  END LOOP cursor_parameter;
  CLOSE parameter_cursor;
 
    /* This acts as an exception handling block. */  
  IF duplicate_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
 
  END IF;
 
  /* Commit the writes as a group. */
  COMMIT;
 
END;
$$
 
-- Reset delimiter to the default.
DELIMITER ;

After you create the procedure in your database, you call it like so:

CALL seed_account_list();

You should use the following query to verify completion of this step:

1
2
3
4

SELECT   r.routine_schema
,        r.routine_name
FROM     information_schema.routines r
WHERE    r.routine_name = 'seed_account_list';

It should display the following results:

+----------------+-------------------+
| routine_schema | routine_name      |
+----------------+-------------------+
| studentdb      | seed_account_list |
+----------------+-------------------+
1 row in set (0.09 sec)

The procedure has an embedded COMMIT instruction. You can query the ACCOUNT_LIST table to make sure it inserted 50 rows for each airport.

After you create the ACCOUNT_LIST table and the SEED_ACCOUNT_LIST procedure. Then, call the SEED_ACCOUNT_LIST procedure to populate the table.

SELECT   SUBSTR(account_number,1,3) AS "Airport"
,        COUNT(*) AS "# Accounts"
FROM     account_list
WHERE    consumed_date IS NULL
GROUP BY airport
ORDER BY 1;

It should display the following:

+---------+------------+
| Airport | # Accounts |
+---------+------------+
| LAX     |         50 |
| SFO     |         50 |
| SJC     |         50 |
| SLC     |         50 |
+---------+------------+
4 rows in set (0.00 sec)

  1. In a prior lab and in both the create and seed scripts, the STATE_PROVINCE column values uses a mixture of US Postal Services state abbreviations and full state names. You need to update all STATE_PROVINCE values with their full state names because a subsequent seeding and the import program rely on full STATE_PROVINCE names. You need to update any pre-seeded US Postal Service state abbreviations with the full state names.

The following UPDATE statement changes US Postal Services abbreviations to the full state name:

1
2
3

UPDATE address
SET    state_province = 'California'
WHERE  state_province = 'CA';
  1. You need to run the script that creates the UPDATE_MEMBER_ACCOUNT procedure, and then you need to call it to update values in the MEMBER and ACCOUNT_LIST tables. It relies on you having run the SEED_ACCOUNT_LIST procedure successfully.

Setup Script ↓

The drop down items, unfold with source code to seed the examples.

This contains the necessary code to consume values from the ACCOUNT_LIST table and update the ACCOUNT_NUMBER column of the MEMBER table with correct values from the ACCOUNT_LIST table.

You should just copy and past this code into your apply_lab9_oracle.sql script. Then, you can run it as the student user.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

-- Reset delimiter to write a procedure.
DELIMITER $$
 
CREATE PROCEDURE update_member_account() MODIFIES SQL DATA
BEGIN
 
  /* Declare local variable for call parameters. */
  DECLARE lv_member_id      INT UNSIGNED;
  DECLARE lv_city           CHAR(30);
  DECLARE lv_state_province CHAR(30);
  DECLARE lv_account_number CHAR(10);
 
  /* Declare a local variable for a subsequent handler. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE fetched INT DEFAULT 0;
 
  /* Declare a SQL cursor fabricated from local variables. */  
  DECLARE member_cursor CURSOR FOR
    SELECT   DISTINCT
             m.member_id
    ,        a.city
    ,        a.state_province
    FROM     member m INNER JOIN contact c
    ON       m.member_id = c.member_id INNER JOIN address a
    ON       c.contact_id = a.contact_id
    ORDER BY m.member_id;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT all_or_none;
 
  /* Open a local cursor. */  
  OPEN member_cursor;
  cursor_member: LOOP
 
    FETCH member_cursor
    INTO  lv_member_id
    ,     lv_city
    ,     lv_state_province;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_member; END IF;
 
      /* Secure a unique account number as they're consumed from the list. */
      SELECT al.account_number
      INTO   lv_account_number
      FROM   account_list al INNER JOIN airport ap
      ON     SUBSTRING(al.account_number,1,3) = ap.airport_code
      WHERE  ap.city = lv_city
      AND    ap.state_province = lv_state_province
      AND    consumed_by IS NULL
      AND    consumed_date IS NULL LIMIT 1;
 
      /* Update a member with a unique account number linked to their nearest airport. */
      UPDATE member
      SET    account_number = lv_account_number
      WHERE  member_id = lv_member_id;
 
      /* Mark consumed the last used account number. */      
      UPDATE account_list
      SET    consumed_by = 1002
      ,      consumed_date = UTC_DATE()
      WHERE  account_number = lv_account_number;
 
  END LOOP cursor_member;
  CLOSE member_cursor;
 
    /* This acts as an exception handling block. */  
  IF duplicate_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
 
  END IF;
 
  /* Commit the writes as a group. */
  COMMIT;
 
END;
$$
 
-- Reset delimiter to the default.
DELIMITER ;

You should use the following query to verify completion of this step:

1
2
3
4

SELECT   r.routine_schema
,        r.routine_name
FROM     information_schema.routines r
WHERE    r.routine_name = 'update_member_account';

It should display the following results:

+----------------+-----------------------+
| routine_schema | routine_name          |
+----------------+-----------------------+
| studentdb      | update_member_account |
+----------------+-----------------------+
1 row in set (0.00 sec)

After you create the procedure in your database, you call it like so:

CALL update_member_account();

The procedure has an embedded COMMIT instruction. You can query the updated ACCOUNT_NUMBER values from the MEMBER table. It inserts one row for each row in the MEMBER table.

After you create the ACCOUNT_LIST table, you update the MEMBER table by calling the UPDATE_MEMBER_ACCOUNT procedure to populate the table. The following query verifies that change:

1
2
3
4
5
6
7
8
9
10

SELECT   DISTINCT
         m.member_id
,        c.last_name
,        m.account_number
,        a.city
,        a.state_province
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN address a
ON       c.contact_id = a.contact_id
ORDER BY 1;

You should see the following rows in the MEMBER table.

+-----------+-----------+----------------+--------------+----------------+
| member_id | last_name | account_number | city         | state_province |
+-----------+-----------+----------------+--------------+----------------+
|      1001 | Winn      | SJC-000001     | San Jose     | California     |
|      1002 | Vizquel   | SJC-000002     | San Jose     | California     |
|      1003 | Sweeney   | SJC-000003     | San Jose     | California     |
|      1004 | Clinton   | SLC-000001     | Provo        | Utah           |
|      1005 | Moss      | SLC-000002     | Provo        | Utah           |
|      1006 | Gretelz   | SLC-000003     | Provo        | Utah           |
|      1007 | Royal     | SLC-000004     | Provo        | Utah           |
|      1008 | Smith     | SLC-000005     | Spanish Fork | Utah           |
|      1009 | Potter    | SLC-000006     | Provo        | Utah           |
+-----------+-----------+----------------+--------------+----------------+
9 rows in set (0.00 sec)

After updating the MEMBER table, you are ready to move forward to the next lab.

  1. [5 points] Create the following TRANSACTION_UPLOAD table as per the specification, but do so understanding the business logic of the model. As a BIG PICTURE, our video store just got a consolidation of rentals from a store that’s closed for our customers. We need to import the values and ensure that our source data agrees with the other store. For example, do all customer names, addresses, account numbers match our data. If they do, the merge should go well. If they don’t, the figures will ultimately be incorrect.

Implementation Note: The TRANSACTION_UPLOAD table is in unnormalized form, which means it has repeating rows and unique rows.

Business Rule: The data has secured a valid CUSTOMER_ID and ITEM_ID before generating the CSV source file. You need to create an import table to support the upload of remote store data.

System Logic: This will be implemented in an Oracle database as an external table, and in MySQL as a memory resident table. Memory resident tables disappear when the database is rebooted. You use the ENGINE=MEMORY as opposed to the ENGINE=INNODB syntax. These rules support the business rules provided you have no FOREIGN KEY references in the TRANSACTION_UPLOAD table.

Table Name: TRANSACTION_UPLOAD
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
ACCOUNT_NUMBER String 10
FIRST_NAME String 20
MIDDLE_NAME String 20
LAST_NAME String 20
CHECK_OUT_DATE Date Date
RETURN_DATE Date Date
RENTAL_ITEM_TYPE String 12
TRANSACTION_TYPE String 14
TRANSACTION_AMOUNT Number Decimal
TRANSACTION_DATE Date Date
ITEM_ID Integer Maximum
PAYMENT_METHOD_TYPE String 14
PAYMENT_ACCOUNT_NUMBER String 19

You should use the following query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10
11

SELECT   table_name
,        ordinal_position
,        column_name
,        CASE
           WHEN is_nullable = 'NO' THEN 'NOT NULL'
           ELSE ''
         END AS constraints
,        column_type
FROM     information_schema.columns
WHERE    table_name = 'transaction_upload'
ORDER BY ordinal_position;

It should display the following results:

+--------------------+------------------+------------------------+-------------+------------------+
| table_name         | ordinal_position | column_name            | constraints | column_type      |
+--------------------+------------------+------------------------+-------------+------------------+
| transaction_upload |                1 | account_number         |             | varchar(10)      |
| transaction_upload |                2 | first_name             |             | varchar(20)      |
| transaction_upload |                3 | middle_name            |             | varchar(20)      |
| transaction_upload |                4 | last_name              |             | varchar(20)      |
| transaction_upload |                5 | check_out_date         |             | date             |
| transaction_upload |                6 | return_date            |             | date             |
| transaction_upload |                7 | rental_item_type       |             | varchar(12)      |
| transaction_upload |                8 | transaction_type       |             | varchar(30)      |
| transaction_upload |                9 | transaction_amount     |             | double(10,2)     |
| transaction_upload |               10 | transaction_date       |             | date             |
| transaction_upload |               11 | item_id                |             | int(10) unsigned |
| transaction_upload |               12 | payment_method_type    |             | varchar(14)      |
| transaction_upload |               13 | payment_account_number |             | varchar(19)      |
+--------------------+------------------+------------------------+-------------+------------------+
13 rows in set (0.01 sec)

You should use the following physical path for the upload directory:

/u01/app/mysql/upload

Having added the directory, unzip (extract) the transaction_upload_mysql.csv file into the upload directory.

Write a LOAD DATA LOCAL command that loads the file contents into the transaction_upload table. You can verify that it loaded the correct number of records with the following query:

SELECT   COUNT(*) AS "External Rows"
FROM     transaction_upload;

It should return the following number of rows:

+----------+
| COUNT(*) |
+----------+
|    11520 |
+----------+
1 row in set (0.00 sec)

Written by michaelmclaughlin

June 4th, 2014 at 12:05 am

Posted in

28 Responses to 'Lab #9: MySQL'

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

  1. Looks like you may need to reconcile the Transaction account numeric types with the numeric types that are showing in your check results, for example you say float for TRANSACTION_AMOUNT in the diagram, but your result returns a double type.

    Abel Goodwin

    18 Jun 14 at 7:45 pm

  2. “transaction | 3 | transaction_type | varchar”
    from the result to check the Transaction table should show an INT, as the common_lookup_id is an int data type and the transaction_type is a foreign key of common_lookup_id.

    Abel Goodwin

    18 Jun 14 at 7:54 pm

  3. Abel, All of your reported items are fixed.

    michaelmclaughlin

    18 Jun 14 at 8:00 pm

  4. For the data types of the AIRPORT table your desired results for character data type for all three string type columns. Should only the code be CHAR(3) and the rest be VARCHAR2(30)?

    Abel Goodwin

    18 Jun 14 at 8:22 pm

  5. I don’t see the changes from Oracle Lab #9 here. Are we still awaiting these changes?

    Brandon

    18 Jun 14 at 8:38 pm

  6. Brandon, Step #4 is completely new and contains the change.

    michaelmclaughlin

    19 Jun 14 at 2:34 pm

  7. Yes, that’s correct. Only one column is limited to 3 characters.

    michaelmclaughlin

    19 Jun 14 at 3:17 pm

  8. There is no Step #4 as you previously said in a comment.
    As the information is not here is there a possibility of extending the deadline to Monday for full credit?

    Abel Goodwin

    19 Jun 14 at 3:41 pm

  9. We’re missing the update_member_account create script with it’s check statement.

    Abel Goodwin

    19 Jun 14 at 3:57 pm

  10. Just curious, when will the MYSQL part of lab9 be finished? THANKS!

    Bryce Franzen

    19 Jun 14 at 7:20 pm

  11. Abel, It’s there now.

    michaelmclaughlin

    20 Jun 14 at 1:23 am

  12. Bryce, It’s got the basics for grading. There are some indexes that I’ll need to add.

    michaelmclaughlin

    20 Jun 14 at 1:24 am

  13. Abel, It’s a time consuming process with all the changes to have parallel documents with the different dialect. Step #4 is there now.

    michaelmclaughlin

    20 Jun 14 at 1:25 am

  14. 3.b reads, “You need to create the AIRPORT table.”
    it should be, “You need to create the Unique Index for the AIRPORT table, named nk_airport”.

    Abel Goodwin

    20 Jun 14 at 12:58 pm

  15. Abel, great catch. I’ve fixed it.

    michaelmclaughlin

    22 Jun 14 at 12:08 am

  16. There is a mistake in path to the reference to the previous lab8 sql file. Instead it calls the lab9 file in the lab8 folder. This mistake is at the beginning of this post.

    Matthew Ary

    23 Jun 14 at 6:55 pm

  17. Matthew, Good catch, it’s fixed.

    michaelmclaughlin

    24 Jun 14 at 12:03 pm

  18. Step 3g has the initial instructions from step 3e.

    Matthew Mason

    24 Jun 14 at 9:44 pm

  19. Matt, Great catch, it was wrong in both Oracle and MySQL versions. They’re both fixed.

    michaelmclaughlin

    25 Jun 14 at 12:31 am

  20. In step 3G I needed to add this to the top of your setup script for mine to run properly.

    SELECT 'DROP PROCEDURE update_member_account' AS "LINE 509";
DROP PROCEDURE IF EXISTS update_member_account;

    Nathan Birch

    7 Nov 14 at 5:55 pm

  21. Nathan, That’s not where you should have made the change. You should insert those two lines at the beginning of Lab #9, Step 3(g). That means the it should be in the apply_mysql_lab9.sql script.

    SELECT 'DROP PROCEDURE update_member_account' AS "Statement";
DROP PROCEDURE IF EXISTS update_member_account;

    michaelmclaughlin

    8 Nov 14 at 2:25 am

  22. In step 2, the table representing the rows to be inserted includes three rows for the RENTAL_ITEM table. But these rows already exist in the database.

    Justin Jaynes

    14 Nov 14 at 2:01 pm

  23. There’s a discrepancy on the first transaction instruction. we are instructed to create payment_account_number as varchar(19), but the expected result shown is varchar(20).

    Robert Thorne

    7 Mar 15 at 4:21 pm

  24. Robert, Thanks for catching that, it’s now fixed.

    michaelmclaughlin

    7 Mar 15 at 6:15 pm

  25. Step 2 says 9 rows in set, but there are less in the query.

    +---------------------+----------------------+--------------------+
| common_lookup_table | common_lookup_column | common_lookup_type |
+---------------------+----------------------+--------------------+
| TRANSACTION         | PAYMENT_METHOD_TYPE  | VISA_CARD          |
| TRANSACTION         | PAYMENT_METHOD_TYPE  | MASTER_CARD        |
| TRANSACTION         | PAYMENT_METHOD_TYPE  | DISCOVER_CARD      |
| TRANSACTION         | PAYMENT_METHOD_TYPE  | CASH               |
| TRANSACTION         | TRANSACTION_TYPE     | DEBIT              |
| TRANSACTION         | TRANSACTION_TYPE     | CREDIT             |
+---------------------+----------------------+--------------------+
9 rows in set (0.01 sec)

    Jeremy

    13 Mar 15 at 9:40 am

  26. Jeremy, Yes, the row count was inccorect. Now, it’s fixed.

    +---------------------+----------------------+--------------------+
| common_lookup_table | common_lookup_column | common_lookup_type |
+---------------------+----------------------+--------------------+
| TRANSACTION         | PAYMENT_METHOD_TYPE  | VISA_CARD          |
| TRANSACTION         | PAYMENT_METHOD_TYPE  | MASTER_CARD        |
| TRANSACTION         | PAYMENT_METHOD_TYPE  | DISCOVER_CARD      |
| TRANSACTION         | PAYMENT_METHOD_TYPE  | CASH               |
| TRANSACTION         | TRANSACTION_TYPE     | DEBIT              |
| TRANSACTION         | TRANSACTION_TYPE     | CREDIT             |
+---------------------+----------------------+--------------------+
6 rows in set (0.01 sec)

    michaelmclaughlin

    13 Mar 15 at 9:50 am

  27. The seed account list procedure and the update member account procedure need to have the who audit columns changed from 2 to 1002 if the students are working in _ri2 otherwise it violates the foreign key constraints.

    Jeremy

    13 Mar 15 at 10:13 am

  28. Jeremy, Lab #9 is fixed for the created_by and last_updated_by columns.

    michaelmclaughlin

    24 Mar 15 at 10:43 pm

Leave a Reply