Lab #6: MySQL at Database Tutorial

Lab #6: MySQL

Lab #6: MySQL Assignment

You begin these steps after running the cleanup_mysql.sql, create_mysql_store_ri2.sql, seed_mysql_store_ri2.sql, and apply_mysql_lab5.sql scripts. 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/lab5/apply_mysql_lab5.sql
 
TEE apply_mysql_lab6.txt
 
... insert code here ...
 
NOTEE

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

[2 points] Add the RENTAL_ITEM_PRICE and RENTAL_ITEM_TYPE columns to the RENTAL_ITEM table. Both columns should use a NUMBER data type in Oracle, and an int unsigned data type for MySQL. While you will place a NOT NULL constraint on the RENTAL_ITEM_PRICE and RENTAL_ITEM_TYPE columns of the RENTAL_ITEM table, you won’t do it until all rows hold data.

Table Name: RENTAL_ITEM
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
RENTAL_ITEM_TYPE	FOREIGN KEY	COMMON_LOOKUP	COMMON_LOOKUP_ID	Integer	Maximum
RENTAL_ITEM_PRICE	NOT NULL			Integer	Maximum

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

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

It should display the following results:

+-------------+------------------+-------------------+----------+------------------+
| table_name  | ordinal_position | column_name       | nullable | column_type      |
+-------------+------------------+-------------------+----------+------------------+
| rental_item |                1 | rental_item_id    | NOT NULL | int(10) unsigned |
| rental_item |                2 | rental_id         | NOT NULL | int(10) unsigned |
| rental_item |                3 | item_id           | NOT NULL | int(10) unsigned |
| rental_item |                4 | created_by        | NOT NULL | int(10) unsigned |
| rental_item |                5 | creation_date     | NOT NULL | date             |
| rental_item |                6 | last_updated_by   | NOT NULL | int(10) unsigned |
| rental_item |                7 | last_update_date  | NOT NULL | date             |
| rental_item |                8 | rental_item_type  |          | int(10) unsigned |
| rental_item |                9 | rental_item_price |          | int(10) unsigned |
+-------------+------------------+-------------------+----------+------------------+
9 rows in set (0.01 sec)

[3 points] Create the following PRICE table as per the specification, like the description below.

Table Name: PRICE
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
PRICE_ID	PRIMARY KEY			Integer	Maximum
ITEM_ID	FOREIGN KEY	ITEM	ITEM_ID	Integer	Maximum
ITEM_ID	NOT NULL	ITEM	ITEM_ID	Integer	Maximum
PRICE_TYPE	FOREIGN KEY	COMMON_LOOKUP	COMMON_LOOKUP_ID	Integer	Maximum
ACTIVE_FLAG	NOT NULL			Text	1
ACTIVE_FLAG	CHECK(‘Y’,’N’)			Text	1
START_DATE	NOT NULL			Date	Date
END_DATE				Date	Date
AMOUNT	NOT NULL			Integer	Maximum
CREATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATED_BY	NOT NULL	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_UPDATED_BY	NOT NULL	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			Date	Date

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

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

It should display the following results:

+------------+------------------+-------------------+----------+------------------+
| table_name | ordinal_position | column_name       | nullable | column_type      |
+------------+------------------+-------------------+----------+------------------+
| price      |                1 | price_id          | NOT NULL | int(10) unsigned |
| price      |                2 | item_id           | NOT NULL | int(10) unsigned |
| price      |                3 | price_type        |          | int(10) unsigned |
| price      |                4 | active_flag       | NOT NULL | enum('Y','N')    |
| price      |                5 | start_date        | NOT NULL | date             |
| price      |                6 | end_date          |          | date             |
| price      |                7 | amount            | NOT NULL | double(10,2)     |
| price      |                8 | created_by        | NOT NULL | int(10) unsigned |
| price      |                9 | creation_date     | NOT NULL | date             |
| price      |               10 | last_updated_by   | NOT NULL | int(10) unsigned |
| price      |               11 | last_updated_date | NOT NULL | date             |
+------------+------------------+-------------------+----------+------------------+
11 rows in set (0.04 sec)

[10 points] Insert new data as follows:

Rename the ITEM_RELEASE_DATE column of the ITEM table to RELEASE_DATE.

The following query checks whether you’ve renamed the column correctly:

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

It should return the following:

+------------+------------------+------------------+----------+------------------+
| table_name | ordinal_position | column_name      | nullable | column_type      |
+------------+------------------+------------------+----------+------------------+
| item       |                1 | item_id          | NOT NULL | int(10) unsigned |
| item       |                2 | item_barcode     | NOT NULL | char(18)         |
| item       |                3 | item_type        | NOT NULL | int(10) unsigned |
| item       |                4 | item_title       | NOT NULL | char(60)         |
| item       |                5 | item_subtitle    |          | char(60)         |
| item       |                6 | item_rating_id   | NOT NULL | int(10) unsigned |
| item       |                7 | release_date     | NOT NULL | date             |
| item       |                8 | created_by       | NOT NULL | int(10) unsigned |
| item       |                9 | creation_date    | NOT NULL | date             |
| item       |               10 | last_updated_by  | NOT NULL | int(10) unsigned |
| item       |               11 | last_update_date | NOT NULL | date             |
+------------+------------------+------------------+----------+------------------+
11 rows in set (0.02 sec)

Insert three new DVD releases into the ITEM table. The RELEASE_DATE column’s value for new rows in the ITEM table should be less than 31 days at all times. The easiest way to achieve this requirement uses a SYSDATE value in the RELEASE_DATE column value.

The following query checks whether you’ve entered three compliant rentals and rental items:

SELECT   i.item_title
,        UTC_DATE() AS today
,        i.release_date
FROM     item i
WHERE    DATEDIFF(UTC_DATE(),i.release_date) < 31;

It should return the following (though the titles and dates may differ):

+----------------------+------------+--------------+
| item_title           | today      | release_date |
+----------------------+------------+--------------+
| Tron                 | 2014-06-11 | 2014-06-08   |
| The Avengers         | 2014-06-11 | 2014-06-08   |
| Thor: The Dark World | 2014-06-11 | 2014-06-08   |
+----------------------+------------+--------------+
3 rows in set (0.00 sec)

Insert a new row in the MEMBER table, and three new rows in the CONTACT, ADDRESS, STREET_ADDRESS, and TELEPHONE tables. The new contacts should be Harry, Ginny, and Lily Luna Potter.

The following query checks whether you’ve renamed the column correctly:

SELECT   m.member_id
,        c.contact_id
,        CONCAT(c.last_name,', ',c.first_name) AS full_name
,        a.city
,        a.state_province AS state
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 INNER JOIN street_address sa
ON       a.address_id = sa.address_id INNER JOIN telephone t
ON       c.contact_id = t.contact_id
WHERE    c.last_name = 'Potter';

It should return the following:

+-----------+------------+---------------+-------+-------+
| member_id | contact_id | full_name     | city  | state |
+-----------+------------+---------------+-------+-------+
|         9 |         13 | Potter, Harry | Provo | Utah  |
|         9 |         14 | Potter, Ginny | Provo | Utah  |
|         9 |         15 | Potter, Lily  | Provo | Utah  |
+-----------+------------+---------------+-------+-------+
3 rows in set (0.00 sec)

Insert three new rows in the RENTAL and four new rows in the RENTAL_ITEM tables. Two of the rows in the RENTAL_ITEM table should link to the same row in the RENTAL table for the rental of Harry Potter. In other words, there you should insert data for: (a) Harry Potter renting two rental items for a 1-Day Rental period (one new release and one old release), (b) Ginny Potter renting one new release rental item for a 3-Day Rental period, and (c) Lily Luna Potter renting one new release rental item for a 5-Day Rental period.

You should set the addresses for the Potter family members in the city of Provo, Utah. The addresses are critical and have an impact on Lab #7. If you choose an address other than Provo, Utah, you’ll need to add it to the AIRPORT table in Lab #8 because it is beyond the scope of subsequent lab steps.

The following query checks whether you’ve entered three compliant rentals and rental items:

SELECT   CONCAT(c.last_name,', ',c.first_name,' ',IFNULL(c.middle_name,'')) AS full_name
,        r.rental_id
,        CONCAT(DATEDIFF(r.return_date,r.check_out_date),'-DAY RENTAL') AS rental_days
,        COUNT(DISTINCT r.rental_id) AS rentals
,        COUNT(ri.rental_item_id) AS items
FROM     rental r INNER JOIN rental_item ri
ON       r.rental_id = ri.rental_id INNER JOIN contact c
ON       r.customer_id = c.contact_id
WHERE    DATEDIFF(UTC_DATE(),r.check_out_date) < 15
AND      c.last_name = 'Potter'
GROUP BY CONCAT(c.last_name,', ',c.first_name,' ',c.middle_name)
,        r.rental_id
,        CONCAT(DATEDIFF(r.return_date,r.check_out_date),'-DAY RENTAL')
ORDER BY 2;

It should return the following:

+--------------------+-----------+-------------+---------+-------+
| full_name          | rental_id | rental_days | rentals | items |
+--------------------+-----------+-------------+---------+-------+
| Potter, Harry      |      1006 |           1 |       1 |     2 |
| Potter, Ginny      |      1007 |           1 |       1 |     1 |
| Potter, Lily Luna  |      1008 |           1 |       1 |     1 |
+--------------------+-----------+-------------+---------+-------+
3 rows in set (0.00 sec)

[20 points] Modify the design of the COMMON_LOOKUP table, insert new data into the model, and update old non-compliant design data in the model.

Hint for Step #4

You can shift the contents of one column to another column in the same row. An UPDATE statement that moves the content from one column’s value to another for all rows in a table is:

1 2	UPDATE common_lookup SET common_lookup_table = common_lookup_context;

The following shows you how UPDATE an existing row before you ALTER TABLE to DROP the column:

UPDATE   common_lookup
SET      common_lookup_table = 'ITEM'
,        common_lookup_column = 'ITEM_TYPE'
WHERE    common_lookup_context = 'ITEM';

The two rows containing the MULTIPLE context values, should migrate to four rows, as shown in the following table.

Table Name: COMMON_LOOKUP
Code	Lookup Table	Lookup Column	Lookup Context	Lookup Type
Old			MULTIPLE	HOME
Old			MULTIPLE	WORK
New	ADDRESS	ADDRESS_TYPE		HOME
New	ADDRESS	ADDRESS_TYPE		WORK
New	TELEPHONE	TELEPHONE_TYPE		HOME
New	TELEPHONE	TELEPHONE_TYPE		WORK

After you’ve made these data changes in the COMMON_LOOKUP table, you need to update the old TELEPHONE_TYPE or ADDRESS_TYPE column values with the correct new values. It depends on how you do it. If you UPDATE the existing rows from MULTIPLE to TELEPHONE_TYPE, then you’ll need to update the rows in the ADDRESS table to point to the correct new rows in the COMMON_LOOKUP table.

The best practice in this case is to write two UPDATE statements. One updates the HOME values in the ADDRESS table. The other updates the WORK values in the ADDRESS table.

Implementation Steps:

Drop the COMMON_LOOKUP_U1 index.

The following query verifies that addition of the columns:

SELECT   table_name
,        constraint_name
,        constraint_type
FROM     information_schema.table_constraints
WHERE    table_name = 'common_lookup';

It should return when you’ve removed the unique and non-unique indexes:

+---------------+-------------------+-----------------+
| table_name    | constraint_name   | constraint_type |
+---------------+-------------------+-----------------+
| common_lookup | PRIMARY           | PRIMARY KEY     |
| common_lookup | common_lookup_fk1 | FOREIGN KEY     |
| common_lookup | common_lookup_fk2 | FOREIGN KEY     |
+---------------+-------------------+-----------------+
3 rows in set (0.00 sec)

Add three new columns to the COMMON_LOOKUP table.

The COMMON_LOOKUP_TABLE column.
The COMMON_LOOKUP_COLUMN column.
The COMMON_LOOKUP_CODE column.

The following query verifies that addition of the columns:

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

It should return:

+---------------+------------------+-----------------------+----------+------------------+
| table_name    | ordinal_position | column_name           | nullable | column_type      |
+---------------+------------------+-----------------------+----------+------------------+
| common_lookup |                1 | common_lookup_id      | NOT NULL | int(10) unsigned |
| common_lookup |                2 | common_lookup_context | NOT NULL | char(30)         |
| common_lookup |                3 | common_lookup_type    | NOT NULL | char(30)         |
| common_lookup |                4 | common_lookup_meaning | NOT NULL | char(30)         |
| common_lookup |                5 | created_by            | NOT NULL | int(10) unsigned |
| common_lookup |                6 | creation_date         | NOT NULL | date             |
| common_lookup |                7 | last_updated_by       | NOT NULL | int(10) unsigned |
| common_lookup |                8 | last_update_date      | NOT NULL | date             |
| common_lookup |                9 | common_lookup_table   |          | varchar(30)      |
| common_lookup |               10 | common_lookup_column  |          | varchar(30)      |
| common_lookup |               11 | common_lookup_code    |          | varchar(30)      |
+---------------+------------------+-----------------------+----------+------------------+
11 rows in set (0.02 sec)

Migrate data and populate (or seed) new columns with existing data.

Migrate COMMON_LOOKUP_CONTEXT column values:

The state of the COMMON_LOOKUP table before any data changes:

Update the COMMON_LOOKUP_TABLE column with the value of the COMMON_LOOKUP_CONTEXT column when the COMMON_LOOKUP_CONTEXT column’s value isn’t equal to a value of 'MULTIPLE'.
Update the COMMON_LOOKUP_TABLE column with the value of of 'ADDRESS' when the COMMON_LOOKUP_CONTEXT column value is equal to 'MULTIPLE'.

Create COMMON_LOOKUP_COLUMN column values based on references to existing data in the COMMON_LOOKUP table by following these rules:

Update the COMMON_LOOKUP_COLUMN column with the value of the COMMON_LOOKUP_CONTEXT column and a '_TYPE' string when the COMMON_LOOKUP_TABLE value is 'MEMBER' and COMMON_LOOKUP_TYPE column values is 'INDIVIDUAL' or 'GROUP'.
Update the COMMON_LOOKUP_COLUMN column with a value of 'CREDIT_CARD_TYPE' when the COMMON_LOOKUP_TYPE column value is 'VISA_CARD', 'MASTER_CARD', or 'DISCOVER_CARD'.
Update the COMMON_LOOKUP_COLUMN column with a value of 'ADDRESS_TYPE' when the COMMON_LOOKUP_CONTEXT value is 'MULTIPLE'.
Update the COMMON_LOOKUP_COLUMN column with the value of the COMMON_LOOKUP_CONTEXT column and a '_TYPE' string when the COMMON_LOOKUP_CONTEXT value is anything other than 'MEMBER' or 'MULTIPLE'.
The table will look like the following after migrating COMMON_LOOKUP_CONTEXT values to the COMMON_LOOKUP_TABLE and COMMON_LOOKUP_COLUMN column values:

Add two new rows to the COMMON_LOOKUP table to support the 'HOME' and 'WORK' possibilities for the TELEPHONE_TYPE column. (Note: This means you’ll need to change the value of foreign keys in the TELEPHONE_TYPE column of the TELEPHONE table to one of those found in the new COMMON_LOOKUP table rows.)

The state of the COMMON_LOOKUP table after the two INSERT statements for the TELEPHONE table.

Update the now obsolete foreign key values in the TELEPHONE_TYPE column of the TELEPHONE table to their correct values, which are found in the two new rows of the COMMON_LOOKUP table.

You should use the following formatting and query to verify completion of the re-structuring of the COMMON_LOOKUP table in this step:

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

It should return the following:

+---------------+------------------+-----------------------+----------+------------------+
| table_name    | ordinal_position | column_name           | nullable | column_type      |
+---------------+------------------+-----------------------+----------+------------------+
| common_lookup |                1 | common_lookup_id      | NOT NULL | int(10) unsigned |
| common_lookup |                2 | common_lookup_type    | NOT NULL | char(30)         |
| common_lookup |                3 | common_lookup_meaning | NOT NULL | char(30)         |
| common_lookup |                4 | created_by            | NOT NULL | int(10) unsigned |
| common_lookup |                5 | creation_date         | NOT NULL | date             |
| common_lookup |                6 | last_updated_by       | NOT NULL | int(10) unsigned |
| common_lookup |                7 | last_update_date      | NOT NULL | date             |
| common_lookup |                8 | common_lookup_table   | NOT NULL | varchar(30)      |
| common_lookup |                9 | common_lookup_column  | NOT NULL | varchar(30)      |
| common_lookup |               10 | common_lookup_code    |          | varchar(30)      |
+---------------+------------------+-----------------------+----------+------------------+
10 rows in set (0.01 sec)

You should use the following formatting and query to verify completion of the data updates to the COMMON_LOOKUP table in this step:

SELECT   common_lookup_table
,        common_lookup_column
,        common_lookup_type
FROM     common_lookup
ORDER BY 1, 2, 3;

It should display the following results:

+---------------------+----------------------+--------------------+
| common_lookup_table | common_lookup_column | common_lookup_type |
+---------------------+----------------------+--------------------+
| ADDRESS             | ADDRESS_TYPE         | HOME               |
| ADDRESS             | ADDRESS_TYPE         | WORK               |
| CONTACT             | CONTACT_TYPE         | CUSTOMER           |
| CONTACT             | CONTACT_TYPE         | EMPLOYEE           |
| ITEM                | ITEM_TYPE            | BLU-RAY            |
| ITEM                | ITEM_TYPE            | DVD_FULL_SCREEN    |
| ITEM                | ITEM_TYPE            | DVD_WIDE_SCREEN    |
| ITEM                | ITEM_TYPE            | NINTENDO_GAMECUBE  |
| ITEM                | ITEM_TYPE            | PLAYSTATION2       |
| ITEM                | ITEM_TYPE            | XBOX               |
| MEMBER              | CREDIT_CARD_TYPE     | DISCOVER_CARD      |
| MEMBER              | CREDIT_CARD_TYPE     | MASTER_CARD        |
| MEMBER              | CREDIT_CARD_TYPE     | VISA_CARD          |
| MEMBER              | MEMBER_TYPE          | GROUP              |
| MEMBER              | MEMBER_TYPE          | INDIVIDUAL         |
| SYSTEM_USER         | SYSTEM_USER_TYPE     | DBA                |
| SYSTEM_USER         | SYSTEM_USER_TYPE     | SYSTEM_ADMIN       |
| TELEPHONE           | TELEPHONE_TYPE       | HOME               |
| TELEPHONE           | TELEPHONE_TYPE       | WORK               |
+---------------------+----------------------+--------------------+
19 rows in set (0.02 sec)

After checking the new structure and data set of the COMMON_LOOKUP table, you should check the migration of data in the ADDRESS and TELEPHONE tables with the following query:

SELECT   cl.common_lookup_table
,        cl.common_lookup_column
,        cl.common_lookup_type
,        COUNT(a.address_id) AS count_dependent
,        COUNT(cl.common_lookup_table) AS count_lookup
FROM     address a RIGHT JOIN common_lookup cl
ON       a.address_type = cl.common_lookup_id
WHERE    cl.common_lookup_table = 'ADDRESS'
AND      cl.common_lookup_column = 'ADDRESS_TYPE'
AND      cl.common_lookup_type IN ('HOME','WORK')
GROUP BY cl.common_lookup_table
,        cl.common_lookup_column
,        cl.common_lookup_type
UNION
SELECT   cl.common_lookup_table
,        cl.common_lookup_column
,        cl.common_lookup_type
,        COUNT(t.telephone_id) AS count_dependent
,        COUNT(cl.common_lookup_table) AS count_lookup
FROM     telephone t RIGHT JOIN common_lookup cl
ON       t.telephone_type = cl.common_lookup_id
WHERE    cl.common_lookup_table = 'TELEPHONE'
AND      cl.common_lookup_column = 'TELEPHONE_TYPE'
AND      cl.common_lookup_type IN ('HOME','WORK')
GROUP BY cl.common_lookup_table
,        cl.common_lookup_column
,        cl.common_lookup_type;

It should display the following results:

+---------------------+----------------------+--------------------+-----------------+--------------+
| common_lookup_table | common_lookup_column | common_lookup_type | count_dependent | count_lookup |
+---------------------+----------------------+--------------------+-----------------+--------------+
| ADDRESS             | ADDRESS_TYPE         | HOME               |              15 |           15 |
| ADDRESS             | ADDRESS_TYPE         | WORK               |               0 |            1 |
| TELEPHONE           | TELEPHONE_TYPE       | HOME               |              15 |           15 |
| TELEPHONE           | TELEPHONE_TYPE       | WORK               |               0 |            1 |
+---------------------+----------------------+--------------------+-----------------+--------------+
4 rows in set (0.03 sec)

Remove obsolete columns, apply not null constraints, and create a new unique index for the natural key of the COMMON_LOOKUP table.

Drop the now obsolete COMMON_LOOKUP_CONTEXT column from COMMON_LOOKUP table.
After seeding the new columns in the COMMON_LOOKUP table with data, ALTER the any new columns of the natural key for COMMON_LOOKUP table with NOT NULL constraints. They are:

The COMMON_LOOKUP_TABLE column.
The COMMON_LOOKUP_COLUMN column.

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

It should return the following:

+---------------+------------------+-----------------------+----------+------------------+
| table_name    | ordinal_position | column_name           | nullable | column_type      |
+---------------+------------------+-----------------------+----------+------------------+
| common_lookup |                1 | common_lookup_id      | NOT NULL | int(10) unsigned |
| common_lookup |                2 | common_lookup_type    | NOT NULL | char(30)         |
| common_lookup |                3 | common_lookup_meaning | NOT NULL | char(30)         |
| common_lookup |                4 | created_by            | NOT NULL | int(10) unsigned |
| common_lookup |                5 | creation_date         | NOT NULL | date             |
| common_lookup |                6 | last_updated_by       | NOT NULL | int(10) unsigned |
| common_lookup |                7 | last_update_date      | NOT NULL | date             |
| common_lookup |                8 | common_lookup_table   | NOT NULL | varchar(30)      |
| common_lookup |                9 | common_lookup_column  | NOT NULL | varchar(30)      |
| common_lookup |               10 | common_lookup_code    |          | varchar(30)      |
+---------------+------------------+-----------------------+----------+------------------+
10 rows in set (0.01 sec)

Create a nk_common_lookup unique index across the COMMON_LOOKUP_TABLE, COMMON_LOOKUP_COLUMN, and COMMON_LOOKUP_TYPE columns found in the COMMON_LOOKUP table.

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

It should return the following:

+---------------+------------------+------------------+----------------------+-----------------+
| table_name    | constraint_name  | ordinal_position | column_name          | constraint_type |
+---------------+------------------+------------------+----------------------+-----------------+
| common_lookup | nk_common_lookup |                1 | common_lookup_table  | UNIQUE          |
| common_lookup | nk_common_lookup |                2 | common_lookup_column | UNIQUE          |
| common_lookup | nk_common_lookup |                3 | common_lookup_type   | UNIQUE          |
+---------------+------------------+------------------+----------------------+-----------------+
3 rows in set (0.01 sec)

Implementation Note: The COMMON_LOOKUP table uses a single lookup row to solve problems across multiple tables. This makes it non-conforming to our normalized model, where a small set of rows should answer questions about a column in a single table. You need to change this table structure, while preserving the data. This means you can’t drop and recreate it. Please read the business rule and system logic to understand the issues.

Business Rule: You want sets of lookup values that are uniquely identified across the table, column, type values in the COMMON_LOOKUP table.

System Logic: You need to modify the table to align it with the business rule. This means: (a) removing a unique index that constrains the table, (b) adding the appropriate columns, (c) migrating existing data, and (d) constraining the columns by a unique index or table-level constraint key. After you’ve updated the data, you’ll need to drop the COMMON_LOOKUP_CONTEXT column because your changes have effectively deprecated the column. The new UNIQUE constraint will be across the COMMON_LOOKUP_TABLE, COMMON_LOOKUP_COLUMN, and COMMON_LOOKUP_TYPE columns.

Written by michaelmclaughlin

May 27th, 2014 at 8:58 pm

Posted in

24 Responses to 'Lab #6: MySQL'

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

Please add member_id to the select query to make sure the Potters were inserted correctly.

Amy Norman

21 Jul 14 at 3:46 pm
Amy, I’ve opted to add both member_id and contact_id because together they should help students avoid rework.

michaelmclaughlin

22 Oct 14 at 12:58 am
“You should use the following formatting and query to verify completion of the data updates to the COMMON_LOOKUP table in this step:”

The picture of the output table shows SYSTEM_USER_ID instead of SYSTEM_USER_TYPE.

Jordan Hawkes

25 Oct 14 at 12:54 pm
Jordan, I can’t find what you’re referencing. Can you give me a task number reference?

michaelmclaughlin

25 Oct 14 at 1:37 pm
Lines 3 and 13 in your verification scripts for 3d are wrong. it is using double bars for conncatenation.

They should be
3
, CONCAT(DATEDIFF(r.return_date,r.check_out_date), '-DAY RENTAL') AS rental_days
, CONCAT(DATEDIFF(r.return_date,r.check_out_date), '-DAY RENTAL') AS rental_days
and
13
, CONCAT(DATEDIFF(r.return_date,r.check_out_date), '-DAY RENTAL')
, CONCAT(DATEDIFF(r.return_date,r.check_out_date), '-DAY RENTAL')
brett Moan

25 Oct 14 at 2:31 pm
Brett, Great catch. They’re fixed.

michaelmclaughlin

25 Oct 14 at 5:31 pm
Yes, I’m referencing 4c, the second image that shows the result table. The oracle lab shows SYSTEM_USER_TYPE and this one says SYSTEM_USER_ID. I believe we added “_TYPE” to every value in this column unless it was “MEMBER” or “MULTIPLE“.

Jordan Hawkes

27 Oct 14 at 1:34 pm
Jordan, I think it’s now fixed. Thanks!

michaelmclaughlin

28 Oct 14 at 8:39 pm
The directions for all of this lab is VERY confusing as to what needs to be done in step 3d is Harry Potter (who we recently inserted) renting the videos or is Harry Potter (the movies) being rented? There is not enough information about this business and what their business rules are. what is the difference between rental and rental_item? is one a reciept that lists all the items?

From the Lab:
There should be: (a) a 1-Day Rental with two rental items for Harry Potter,

(were two Harry Potter movies rented out for one day, were there two movies rented BY the customer Harry Potter also did he some two seperate times? it is kind of ambiguous )

(b) a 3-Day Rental with one rental item for Ginny Potter, and
(c) a 5-Day Rental with one rental item for Lily Luna Potter.

( it would be better for understanding to say that Ginny/Lily rented one item for 3/5 days)

Bob The Goldfish

28 Oct 14 at 9:08 pm
Bob, I’ve taken a stab at rewriting it. Please let me know if you believe it needs more work.

michaelmclaughlin

30 Oct 14 at 8:37 pm
3.a has results that show item barcode as char(18). The files included in the fedora image have it as char(14). I don’t know if that matters but just thought I’d point it out.

Keith Banner

1 Nov 14 at 2:46 pm

On 3.d. I believe it should return the following:

+-------------------+-----------+--------------+---------+-------+
| full_name         | rental_id | rental_days  | rentals | items |
+-------------------+-----------+--------------+---------+-------+
| Potter, Harry     |         6 | 1-DAY RENTAL |       1 |     2 |
| Potter, Ginny     |         7 | 3-DAY RENTAL |       1 |     1 |
| Potter, Lily Luna |         8 | 5-DAY RENTAL |       1 |     1 |
+-------------------+-----------+--------------+---------+-------+
3 ROWS IN SET (0.01 sec)

Keith Banner

1 Nov 14 at 6:01 pm

Keith,

The create_mysql_store_ri2.sql it’s sets the starting value of the auto sequencing ID is 1001, so what’s shown is correct.

-- ------------------------------------------------------------------
-- Create RENTAL table.
-- ------------------------------------------------------------------
SELECT 'RENTAL' AS "Create Table";
CREATE TABLE rental
( rental_id                   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, customer_id                 INT UNSIGNED NOT NULL
, check_out_date              DATE         NOT NULL
, return_date                 DATE         NOT NULL
, created_by                  INT UNSIGNED NOT NULL
, creation_date               DATE         NOT NULL
, last_updated_by             INT UNSIGNED NOT NULL
, last_update_date            DATE         NOT NULL
, KEY rental_fk1 (customer_id)
, CONSTRAINT rental_fk1 FOREIGN KEY (customer_id)
  REFERENCES contact (contact_id)
, KEY rental_fk2 (created_by)
, CONSTRAINT rental_fk2 FOREIGN KEY (created_by)
  REFERENCES system_user (system_user_id)
, KEY rental_fk3 (last_updated_by)
, CONSTRAINT rental_fk3 FOREIGN KEY (last_updated_by)
  REFERENCES system_user (system_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-- Create RENTAL table.
SELECT 'RENTAL' AS "ALTER TABLE";
ALTER TABLE rental AUTO_INCREMENT = 1001;

michaelmclaughlin

6 Nov 14 at 1:00 pm

Keith,

The create_mysql_store_ri2.sql sets it as a CHAR(18), as you can see:

-- ------------------------------------------------------------------
-- Create ITEM table.
-- ------------------------------------------------------------------
SELECT 'ITEM' AS "Create Table";
CREATE TABLE item
( item_id                     INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, item_barcode                CHAR(18)     NOT NULL
, item_type                   INT UNSIGNED NOT NULL
, item_title                  CHAR(60)     NOT NULL
, item_subtitle               CHAR(60)
, item_rating_id              INT UNSIGNED NOT NULL
, item_release_date           DATE         NOT NULL
, created_by                  INT UNSIGNED NOT NULL
, creation_date               DATE         NOT NULL
, last_updated_by             INT UNSIGNED NOT NULL
, last_update_date            DATE         NOT NULL
, KEY item_fk1 (item_type)
, CONSTRAINT item_fk1 FOREIGN KEY (item_type)
  REFERENCES common_lookup (common_lookup_id)
, KEY item_fk2 (item_rating_id)
, CONSTRAINT item_fk2 FOREIGN KEY (item_rating_id)
  REFERENCES rating_agency (rating_agency_id)
, KEY item_fk3 (created_by)
, CONSTRAINT item_fk3 FOREIGN KEY (created_by)
  REFERENCES system_user (system_user_id)
, KEY item_fk4 (last_updated_by)
, CONSTRAINT item_fk4 FOREIGN KEY (last_updated_by)
  REFERENCES system_user (system_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

michaelmclaughlin

6 Nov 14 at 1:05 pm

When attempting Lab 8, we learn that the items that the Potter Family rents are actually mostly new releases (The items we just entered). While this can easily be debugged in the midst of lab 8, it would be nice to know in this lab when we are actually entering the data. The following would be helpful:

Harry rents 1 new release and 1 old release.
Ginny rents a new release
Lily rents a new release

(As seen from the pricing, found in the validation of step 3, lab 8)

Joseph Hales

8 Nov 14 at 1:18 am
Joseph, Great catch! I’ve updated Lab #6, Oracle and MySQL, Step #3d, with those details.

michaelmclaughlin

8 Nov 14 at 2:21 am
I just noticed a small error. In the script you have us put in before we start:
-- Run the prior lab script. . /home/student/Data/cit225/oracle/lab5/apply_mysql_lab5.sql TEE apply_mysql_lab6.txt ... insert code here ... NOTEE
-- Run the prior lab script. . /home/student/Data/cit225/oracle/lab5/apply_mysql_lab5.sql TEE apply_mysql_lab6.txt ... insert code here ... NOTEE
It says .../oracle/lab5... and for this lab it should be .../mysql/lab...
Colby

4 Feb 15 at 2:25 pm
3a.

on the table that you show us after running the validation script shows column ‘item_rating_id‘ has a datatype of ‘INT‘ but shouldn’t that be a ‘CHAR‘ to house the ‘PG‘ rating of any given movie?

Joseph Tracy

11 Feb 15 at 4:59 pm
Somewhere between step 4b and step 4c, the not-null constraints for common_lookup_table and common_lookup_column magically appear in the given tables. I searched in the instructions and couldn’t find a place where it told you to add in the not-nulls. Just wondering if that’s an oversight.

Madeleine

13 Feb 15 at 5:09 pm
Joseph, The ERD models differ between the Oracle and MySQL versions. This is correct and the item_rating_id column should be an unsigned int.

michaelmclaughlin

7 Mar 15 at 1:55 am
Colby, Great catch. These raw HTML files are around 4,200+ lines of code each. So, I wrote the Oracle ones and then edited the MySQL ones. It appears I didn’t catch this but it’s fixed now. Thanks!

michaelmclaughlin

8 Mar 15 at 2:35 am
Madeleine,

It isn’t magical at all, the NOT NULL instruction is found in Step 4d. Below, you can see an excerpt from the Lab #6 MySQL post:
1. Remove obsolete columns, apply not null constraints, and create a new unique index for the natural key of the COMMON_LOOKUP table.
- Drop the now obsolete COMMON_LOOKUP_CONTEXT column from COMMON_LOOKUP table.
- After seeding the new columns in the COMMON_LOOKUP table with data, ALTER the any new columns of the natural key for COMMON_LOOKUP table with NOT NULL constraints. They are:
- The COMMON_LOOKUP_TABLE column.
- The COMMON_LOOKUP_COLUMN column.
Hope that helps.
michaelmclaughlin

8 Mar 15 at 12:43 pm
Validation for 3(d) should not all be Potter, Harry.

Jeremy

13 Mar 15 at 1:40 pm
Jeremy, Good catch on the MySQL web page. I’ve fixed it.

michaelmclaughlin

22 Mar 15 at 7:58 pm

Database Tutorial

Lab #6: MySQL

24 Responses to 'Lab #6: MySQL'

Leave a Reply

My Technical Blog

Tutorial Pages

Blogroll