Lab #9: MySQL
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.
- [4 points] Create the following
TRANSACTION
table as per the specification, but do so understanding the business logic of the model.
- 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) |
- 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) |
- [2 points] Insert the following two
TRANSACTION_TYPE
rows and fourPAYMENT_METHOD_TYPE
rows into theCOMMON_LOOKUP
table. They should have valid who-audit column data. After you insert theTRANSACTION_TYPE
andPAYMENT_METHOD_TYPE
rows in theCOMMON_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) |
- [14 points] Create the following
AIRPORT
andACCOUNT_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.
- 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) |
- You need to create a unique index for the
AIRPORT
table, namednk_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) |
- You need to seed the
AIRPORT
table with at least these cities, and any others that you’ve used for inserted values in theCONTACT
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) |
- 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) |
- 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) |
- 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 allSTATE_PROVINCE
values with their full state names because a subsequent seeding and the import program rely on fullSTATE_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'; |
- You need to run the script that creates the
UPDATE_MEMBER_ACCOUNT
procedure, and then you need to call it to update values in theMEMBER
andACCOUNT_LIST
tables. It relies on you having run theSEED_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.
- [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) |
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
“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
Abel, All of your reported items are fixed.
michaelmclaughlin
18 Jun 14 at 8:00 pm
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 beCHAR(3)
and the rest beVARCHAR2(30)
?Abel Goodwin
18 Jun 14 at 8:22 pm
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
Brandon, Step #4 is completely new and contains the change.
michaelmclaughlin
19 Jun 14 at 2:34 pm
Yes, that’s correct. Only one column is limited to 3 characters.
michaelmclaughlin
19 Jun 14 at 3:17 pm
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
We’re missing the update_member_account create script with it’s check statement.
Abel Goodwin
19 Jun 14 at 3:57 pm
Just curious, when will the MYSQL part of lab9 be finished? THANKS!
Bryce Franzen
19 Jun 14 at 7:20 pm
Abel, It’s there now.
michaelmclaughlin
20 Jun 14 at 1:23 am
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
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
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
Abel, great catch. I’ve fixed it.
michaelmclaughlin
22 Jun 14 at 12:08 am
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
Matthew, Good catch, it’s fixed.
michaelmclaughlin
24 Jun 14 at 12:03 pm
Step 3g has the initial instructions from step 3e.
Matthew Mason
24 Jun 14 at 9:44 pm
Matt, Great catch, it was wrong in both Oracle and MySQL versions. They’re both fixed.
michaelmclaughlin
25 Jun 14 at 12:31 am
In step 3G I needed to add this to the top of your setup script for mine to run properly.
Nathan Birch
7 Nov 14 at 5:55 pm
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.michaelmclaughlin
8 Nov 14 at 2:25 am
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
There’s a discrepancy on the first transaction instruction. we are instructed to create
payment_account_number
asvarchar(19)
, but the expected result shown isvarchar(20)
.Robert Thorne
7 Mar 15 at 4:21 pm
Robert, Thanks for catching that, it’s now fixed.
michaelmclaughlin
7 Mar 15 at 6:15 pm
Step 2 says 9 rows in set, but there are less in the query.
Jeremy
13 Mar 15 at 9:40 am
Jeremy, Yes, the row count was inccorect. Now, it’s fixed.
michaelmclaughlin
13 Mar 15 at 9:50 am
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
Jeremy, Lab #9 is fixed for the
created_by
andlast_updated_by
columns.michaelmclaughlin
24 Mar 15 at 10:43 pm