Lab #6: MySQL
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
andRENTAL_ITEM_TYPE
columns to theRENTAL_ITEM
table. Both columns should use aNUMBER
data type in Oracle, and anint unsigned
data type for MySQL. While you will place aNOT NULL
constraint on theRENTAL_ITEM_PRICE
andRENTAL_ITEM_TYPE
columns of theRENTAL_ITEM
table, you won’t do it until all rows hold data.
Table Name: RENTAL_ITEM | |||||
---|---|---|---|---|---|
Column Name | Constraint | Data Type |
Physical Size |
||
Type | Reference Table | Reference Column | |||
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:
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 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 |
||
Type | Reference Table | Reference Column | |||
PRICE_ID | PRIMARY KEY | Integer | Maximum | ||
ITEM_ID | FOREIGN KEY | ITEM | ITEM_ID | Integer | Maximum |
NOT NULL | |||||
PRICE_TYPE | FOREIGN KEY | COMMON_LOOKUP | COMMON_LOOKUP_ID | Integer | Maximum |
ACTIVE_FLAG | NOT NULL | Text | 1 | ||
CHECK(‘Y’,’N’) | |||||
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 |
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 formatting and 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 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 theITEM
table toRELEASE_DATE
.
The following query checks whether you’ve renamed the column correctly:
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 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. TheRELEASE_DATE
column’s value for new rows in theITEM
table should be less than 31 days at all times. The easiest way to achieve this requirement uses aSYSDATE
value in theRELEASE_DATE
column value.
The following query checks whether you’ve entered three compliant rentals and rental items:
1 2 3 4 5 | 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 theCONTACT
,ADDRESS
,STREET_ADDRESS
, andTELEPHONE
tables. The new contacts should be Harry, Ginny, and Lily Luna Potter.
The following query checks whether you’ve renamed the column correctly:
1 2 3 4 5 6 7 8 9 10 11 | 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 theRENTAL_ITEM
tables. Two of the rows in theRENTAL_ITEM
table should link to the same row in theRENTAL
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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:
1 2 3 4 | 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:
1 2 3 4 5 | 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:
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 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 theCOMMON_LOOKUP_CONTEXT
column when theCOMMON_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 theCOMMON_LOOKUP_CONTEXT
column value is equal to'MULTIPLE'
. - Create
COMMON_LOOKUP_COLUMN
column values based on references to existing data in theCOMMON_LOOKUP
table by following these rules: - Update the
COMMON_LOOKUP_COLUMN
column with the value of theCOMMON_LOOKUP_CONTEXT
column and a'_TYPE'
string when theCOMMON_LOOKUP_TABLE
value is'MEMBER'
andCOMMON_LOOKUP_TYPE
column values is'INDIVIDUAL'
or'GROUP'
. - Update the
COMMON_LOOKUP_COLUMN
column with a value of'CREDIT_CARD_TYPE'
when theCOMMON_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 theCOMMON_LOOKUP_CONTEXT
value is'MULTIPLE'
. - Update the
COMMON_LOOKUP_COLUMN
column with the value of theCOMMON_LOOKUP_CONTEXT
column and a'_TYPE'
string when theCOMMON_LOOKUP_CONTEXT
value is anything other than'MEMBER'
or'MULTIPLE'
. - The table will look like the following after migrating
COMMON_LOOKUP_CONTEXT
values to theCOMMON_LOOKUP_TABLE
andCOMMON_LOOKUP_COLUMN
column values: - Add two new rows to the
COMMON_LOOKUP
table to support the'HOME'
and'WORK'
possibilities for theTELEPHONE_TYPE
column. (Note: This means you’ll need to change the value of foreign keys in theTELEPHONE_TYPE
column of theTELEPHONE
table to one of those found in the newCOMMON_LOOKUP
table rows.) - Update the now obsolete foreign key values in the
TELEPHONE_TYPE
column of theTELEPHONE
table to their correct values, which are found in the two new rows of theCOMMON_LOOKUP
table.
The state of the COMMON_LOOKUP
table after the two INSERT
statements for the TELEPHONE
table.
You should use the following formatting and query to verify completion of the re-structuring of the COMMON_LOOKUP
table in 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 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:
1 2 3 4 5 | 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:
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 | 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 fromCOMMON_LOOKUP
table. - After seeding the new columns in the
COMMON_LOOKUP
table with data,ALTER
the any new columns of the natural key forCOMMON_LOOKUP
table withNOT NULL
constraints. They are: - The
COMMON_LOOKUP_TABLE
column. - The
COMMON_LOOKUP_COLUMN
column. - Create a
nk_common_lookup
unique index across theCOMMON_LOOKUP_TABLE
,COMMON_LOOKUP_COLUMN
, andCOMMON_LOOKUP_TYPE
columns found in theCOMMON_LOOKUP
table.
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 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) |
1 2 3 4 5 6 7 8 9 10 11 12 | 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.
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
andcontact_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 ofSYSTEM_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
and
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 saysSYSTEM_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 aschar(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:
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 is1001
, so what’s shown is correct.michaelmclaughlin
6 Nov 14 at 1:00 pm
Keith,
The
create_mysql_store_ri2.sql
sets it as aCHAR(18)
, as you can see: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:
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
andcommon_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 unsignedint
.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:COMMON_LOOKUP
table.COMMON_LOOKUP_CONTEXT
column fromCOMMON_LOOKUP
table.COMMON_LOOKUP
table with data,ALTER
the any new columns of the natural key forCOMMON_LOOKUP
table withNOT NULL
constraints. They are:COMMON_LOOKUP_TABLE
column.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