Lab #2 : MySQL Tables
You begin these steps by running the cleanup_mysql.sql
and create_mysql_store.sql
scripts. A great starting point for this lab is to review the create_mysql_store.sql
script. The create_mysql_store.sql
script creates 10 tables. Your Lab #2 script creates 10 tables with some changes and alterations. 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/lib/cleanup_mysql.sql \. /home/student/Data/cit225/mysql/lib/create_mysql_store_ri.sql TEE apply_mysql_lab2.txt ... insert code here ... NOTEE |
You should embed the verification queries inside your apply_mysql_lab2.sql
script.
- [0 points] Tutorial on creating the
SYSTEM_USER_LAB
table and theSYSTEM_USER_LAB_S1
sequence, which is Step #1. The reasons for making the changes are:- Table, sequence, and constraint names are unique inside a schema or database.
- All the tables, sequences, and constraints names must have counterparts that use an
_LAB
suffix or an_LAB_
between the base table constraint name and its number.
SYSTEM_USER_LAB
table, you repeat the process by copying segments and editing them until you replicate the entire create_mysql_store_ri.sql
script for tables with the _LAB
suffix or an _LAB_
element between the base table constraint name and its number.
Tutorial Details ↓
- You connect to the VMware instance as the
student
user. Then, click on Activities to launch the menu. Entergedit
in the search box and click the Return key to launch thegedit
program.
- Having launched the
gedit
application, you should see the following image. Click the Open button to find and open thecreate_mysql_store_ri.sql
file.
- Having opened the File Manager dialog, you can now click on the Home option in the Places list on the left.
- Double click on the Data folder in the Name list.
- After double clicking on the Data folder, the File Manager opens the Data folder and puts the Data folder in the list of folders. Next, click on the cit225 folder the Name list.
- After double clicking on the cit225 folder, the File Manager opens the cit225 folder and puts the cit225 folder in the list of folders. Next, click on the mysql folder the Name list.
- After double clicking on the mysql folder, the File Manager opens the mysql folder and puts the mysql folder in the list of folders. Next, click on the lib folder the Name list.
- After double clicking on the lib folder, the File Manager opens the lib folder and puts the lib folder in the list of folders. Next, click on the Open button to open the
create_mysql_store_ri.sql
file in thegedit
application.
- You should see the following
create_mysql_store_ri.sql
script in thegedit
application.
- After opening the
create_mysql_store_ri.sql
script, click on the oracle button in the file path above the panes. Then, click on thelib
folder. You will see theapply_mysql_lab2.sql
file. Click the Open button to open theapply_mysql_lab2.sql
file in thegedit
file.
- You should see the following
apply_mysql_lab2.sql
script in thegedit
application. You should only edit below the last line, which means don’t change anything above the last comment line.
- Copy the content from line 11 to 55 from the create_mysql_store.sql script and paste it to lines 32 through 76. Then, make the following changes:
- Change all uppercase references to
SYSTEM_USER
toSYSTEM_USER_LAB
. - Change all lowercase references to
system_user
tosystem_user_lab
. - Change all constraint names from
nn_system_user_x
tonn_system_user_lab_x
. - Change all lowercase references to
fk_system_user_x
tofk_system_user_lab_x
.
- Change all uppercase references to
- After you make the changes to the script, you need to copy the confirmation queries from the Instruction Details of tasks 1 through 12 (below) into your
apply_oracle_lab2.sql
script. Then, you need to run it and verify that you have the same output as the web page.
- [2 points] Create the
SYSTEM_USER_LAB
table described by the following description. The table should also have an auto-incrementing sequence on theSYSTEM_USER_ID
column. The table should have an auto incrementing sequence that should start with a value of1001
:
Instruction Details ↓
Some of the data values require that you look them up. You look them up in the COMMON_LOOKUP_LAB
table by using the COMMON_LOOKUP_CONTEXT
and COMMON_LOOKUP_TYPE
columns in the WHERE
clause. Then, you figure out (a) how to write a subquery to return the correct COMMON_LOOKUP_LAB_ID
value and (b) how to embed the subquery in an INSERT
statement.
Table Name: SYSTEM_USER_LAB | |||||
---|---|---|---|---|---|
Column Name | Constraint | Data Type |
Physical Size |
||
Type | Reference Table | Reference Column | |||
SYSTEM_USER_LAB_ID | PRIMARY KEY | Integer | Maximum | ||
SYSTEM_USER_NAME | NOT NULL | String | 20 | ||
SYSTEM_USER_GROUP_ID | FOREIGN KEY | COMMON_LOOKUP_LAB | COMMON_LOOKUP_LAB_ID | Integer | Maximum |
NOT NULL | |||||
SYSTEM_USER_TYPE | FOREIGN KEY | COMMON_LOOKUP_LAB | COMMON_LOOKUP_LAB_ID | Integer | Maximum |
NOT NULL | |||||
FIRST_NAME | String | 20 | |||
MIDDLE_NAME | String | 20 | |||
LAST_NAME | String | 20 | |||
CREATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_ID | Integer | Maximum |
NOT NULL | |||||
CREATION_DATE | NOT NULL | DATE | Date | ||
LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_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 = 'system_user_lab' ORDER BY 2; |
It should display the following results:
+-----------------+------------------+----------------------+----------+------------------+ | table_name | ordinal_position | column_name | nullable | column_type | +-----------------+------------------+----------------------+----------+------------------+ | system_user_lab | 1 | system_user_lab_id | NOT NULL | int(10) unsigned | | system_user_lab | 2 | system_user_name | NOT NULL | char(20) | | system_user_lab | 3 | system_user_group_id | NOT NULL | int(10) unsigned | | system_user_lab | 4 | system_user_type | NOT NULL | int(10) unsigned | | system_user_lab | 5 | first_name | | char(20) | | system_user_lab | 6 | middle_name | | char(20) | | system_user_lab | 7 | last_name | | char(20) | | system_user_lab | 8 | created_by | NOT NULL | int(10) unsigned | | system_user_lab | 9 | creation_date | NOT NULL | date | | system_user_lab | 10 | last_updated_by | NOT NULL | int(10) unsigned | | system_user_lab | 11 | last_update_date | NOT NULL | date | +-----------------+------------------+----------------------+----------+------------------+ 11 rows in set (0.02 sec) |
You should use the following formatting and query to verify completion of the constraints for this step:
1 2 3 4 5 | SELECT tc.table_name , tc.constraint_name , tc.constraint_type FROM information_schema.table_constraints tc WHERE table_name = 'system_user_lab'; |
It should display the following results:
+-----------------+---------------------+-----------------+ | table_name | constraint_name | constraint_type | +-----------------+---------------------+-----------------+ | system_user_lab | PRIMARY | PRIMARY KEY | | system_user_lab | system_user_lab_fk1 | FOREIGN KEY | | system_user_lab | system_user_lab_fk2 | FOREIGN KEY | +-----------------+---------------------+-----------------+ 3 rows in set (0.01 sec) |
You can validate that you created the two self-referencing foreign key constraints with the following query:
SELECT CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint" , CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key" , CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key" FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'system_user_lab' ORDER BY tc.table_name , tc.constraint_name; |
It should display only the following:
+-----------------------------------------------+-------------------------------------------+----------------------------------------------+ | Constraint | Foreign Key | Primary Key | +-----------------------------------------------+-------------------------------------------+----------------------------------------------+ | studentdb.system_user_lab.system_user_lab_fk1 | studentdb.system_user_lab.created_by | studentdb.system_user_lab.system_user_lab_id | | studentdb.system_user_lab.system_user_lab_fk2 | studentdb.system_user_lab.last_updated_by | studentdb.system_user_lab.system_user_lab_id | +-----------------------------------------------+-------------------------------------------+----------------------------------------------+ 2 rows in set (0.05 sec) |
Please note that the foreign keys on the SYSTEM_USER_GROUP_ID
and SYSTEM_USER_TYPE
columns haven’t been created at this point. That’s because they can’t exists until you create the COMMON_LOOKUP_LAB
table.
You should then create a SYSTEM_USER_LAB_U1
unique index on the SYSTEM_USER_NAME
column of the SYSTEM_USER_LAB
table. You can then use this query to display the unique constants on the table:
SELECT kcu.constraint_name , kcu.column_name , kcu.ordinal_position FROM information_schema.key_column_usage kcu WHERE kcu.table_name = 'system_user_lab'; |
It should display:
+-----------------+---------------------+-----------------+--------------------+ | TABLE_NAME | constraint_name | constraint_type | column_name | +-----------------+---------------------+-----------------+--------------------+ | system_user_lab | PRIMARY | PRIMARY KEY | system_user_lab_id | | system_user_lab | system_user_lab_u1 | UNIQUE | system_user_name | | system_user_lab | system_user_lab_fk1 | FOREIGN KEY | created_by | | system_user_lab | system_user_lab_fk2 | FOREIGN KEY | last_updated_by | +-----------------+---------------------+-----------------+--------------------+ 4 ROWS IN SET (0.05 sec) |
After you create the SYSTEM_USER_LAB
table, insert a single row with the following values. You can use a \G
(in place of a [;
] semicolon) to display the columns on the left and the values on the right, like this:
SELECT * FROM system_user_lab\G |
It should return these results:
Table Name: SYSTEM_USER_LAB | |
---|---|
Column | Value |
SYSTEM_USER_LAB_ID | 1001 |
SYSTEM_USER_NAME | SYSADMIN |
SYSTEM_USER_GROUP_ID | 1001 |
SYSTEM_USER_TYPE | 1001 |
FIRST_NAME | |
MIDDLE_NAME | |
LAST_NAME | |
CREATED_BY | 1001 |
CREATION_DATE | SYSDATE |
LAST_UPDATED_BY | 1001 |
LAST_UPDATE_DATE | SYSDATE |
- [2 points] Create the
COMMON_LOOKUP_LAB
table described by the following. It should have an auto incrementing sequence with a starting value of1001
:
Instruction Details ↓
Table Name: COMMON_LOOKUP_LAB | |||||
---|---|---|---|---|---|
Column Name | Constraint | Data Type |
Physical Size |
||
Type | Reference Table | Reference Column | |||
COMMON_LOOKUP_LAB_ID | PRIMARY KEY | Integer | Maximum | ||
COMMON_LOOKUP_CONTEXT | NOT NULL | String | 30 | ||
COMMON_LOOKUP_TYPE | NOT NULL | String | 30 | ||
COMMON_LOOKUP_MEANING | NOT NULL | String | 30 | ||
CREATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_ID | Integer | Maximum |
NOT NULL | |||||
CREATION_DATE | NOT NULL | DATE | Date | ||
LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_ID | Integer | Maximum |
NOT NULL | |||||
LAST_UPDATE_DATE | NOT NULL | DATE | Date |
After you create the common_lookup_lab
table, you should create a non-unique common_lookup_n1
. index on the common_lookup_context
column. There isn’t one in the base script but you can find the syntax on the CREATE Statement web page.
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 = 'common_lookup_lab' ORDER BY 2; |
It should display the following results:
+-------------------+------------------+-----------------------+----------+------------------+ | table_name | ordinal_position | column_name | nullable | column_type | +-------------------+------------------+-----------------------+----------+------------------+ | common_lookup_lab | 1 | common_lookup_lab_id | NOT NULL | int(10) unsigned | | common_lookup_lab | 2 | common_lookup_context | NOT NULL | char(30) | | common_lookup_lab | 3 | common_lookup_type | NOT NULL | char(30) | | common_lookup_lab | 4 | common_lookup_meaning | NOT NULL | char(30) | | common_lookup_lab | 5 | created_by | NOT NULL | int(10) unsigned | | common_lookup_lab | 6 | creation_date | NOT NULL | date | | common_lookup_lab | 7 | last_updated_by | NOT NULL | int(10) unsigned | | common_lookup_lab | 8 | last_update_date | NOT NULL | date | +-------------------+------------------+-----------------------+----------+------------------+ 8 rows in set (0.00 sec) |
You should use the following formatting and query to verify completion of the constraints for this step:
1 2 3 4 5 | SELECT tc.table_name , tc.constraint_name , tc.constraint_type FROM information_schema.table_constraints tc WHERE table_name = 'common_lookup_lab'; |
It should display the following results:
+-------------------+-----------------------+-----------------+ | table_name | constraint_name | constraint_type | +-------------------+-----------------------+-----------------+ | common_lookup_lab | PRIMARY | PRIMARY KEY | | common_lookup_lab | common_lookup_lab_u1 | UNIQUE | | common_lookup_lab | common_lookup_lab_fk1 | FOREIGN KEY | | common_lookup_lab | common_lookup_lab_fk2 | FOREIGN KEY | +-------------------+-----------------------+-----------------+ 4 rows in set (0.01 sec) |
You can validate that you created the two foreign key constraints with the following query:
SELECT CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint" , CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key" , CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key" FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'common_lookup_lab' ORDER BY tc.table_name , tc.constraint_name; |
It should display only the following:
+---------------------------------------------------+---------------------------------------------+----------------------------------------------+ | Constraint | Foreign Key | Primary Key | +---------------------------------------------------+---------------------------------------------+----------------------------------------------+ | studentdb.common_lookup_lab.common_lookup_lab_fk1 | studentdb.common_lookup_lab.created_by | studentdb.system_user_lab.system_user_lab_id | | studentdb.common_lookup_lab.common_lookup_lab_fk2 | studentdb.common_lookup_lab.last_updated_by | studentdb.system_user_lab.system_user_lab_id | +---------------------------------------------------+---------------------------------------------+----------------------------------------------+ 2 rows in set (0.06 sec) |
You should then create a COMMON_LOOKUP_LAB_U1
unique index on the COMMON_LOOKUP_CONTEXT
and COMMON_LOOKUP_TYPE
columns of the COMMON_LOOKUP
table. You can validate the creation of the unique index on the COMMON_LOOKUP
table with the following query:
SELECT s.table_name , s.index_name , s.seq_in_index , s.column_name FROM information_schema.statistics s WHERE s.table_name = 'common_lookup_lab' AND s.non_unique = FALSE AND NOT s.index_name = 'primary' AND EXISTS (SELECT null FROM information_schema.table_constraints tc WHERE s.table_name = tc.table_name AND s.index_name = tc.constraint_name) ORDER BY s.index_name , s.seq_in_index; |
It should display:
+-------------------+----------------------+--------------+-----------------------+ | table_name | index_name | seq_in_index | column_name | +-------------------+----------------------+--------------+-----------------------+ | common_lookup_lab | common_lookup_lab_u1 | 1 | common_lookup_context | | common_lookup_lab | common_lookup_lab_u1 | 2 | common_lookup_type | +-------------------+----------------------+--------------+-----------------------+ 2 rows in set (0.08 sec) |
After you create the COMMON_LOOKUP_LAB_U1
unique index on the COMMON_LOOKUP
table, you should create a COMMON_LOOKUP_LAB_N1
non-unique index on the COMMON_LOOKUP_CONTEXT
column of the COMMON_LOOKUP
table.
You can use the following query to verify the creation of the COMMON_LOOKUP_LAB_N1
index:
SELECT s.table_name , s.index_name , s.seq_in_index , s.column_name FROM information_schema.statistics s WHERE s.table_name = 'common_lookup_lab' AND s.non_unique = TRUE AND NOT EXISTS (SELECT null FROM information_schema.table_constraints tc WHERE s.table_name = tc.table_name AND s.index_name = tc.constraint_name) ORDER BY s.index_name , s.seq_in_index; |
It should display:
+-------------------+------------------+--------------+-----------------------+ | table_name | index_name | seq_in_index | column_name | +-------------------+------------------+--------------+-----------------------+ | common_lookup_lab | common_lookup_n1 | 1 | common_lookup_context | +-------------------+------------------+--------------+-----------------------+ 1 row in set (0.19 sec) |
After creating the COMMON_LOOKUP_LAB
table, you need to seed the COMMON_LOOKUP_LAB
table with at least one row that maps to a COMMON_LOOKUP_ID
column value of 1
(the surrogate key value). While you’re at it, insert the following rows in the COMMON_LOOKUP_LAB
table:
Table Name: COMMON_LOOKUP_LAB | ||||
---|---|---|---|---|
Context | Type | Meaning | ||
SYSTEM_USER_LAB | SYSTEM_ADMIN | System Administrator | ||
SYSTEM_USER_LAB | DBA | Database Administrator | ||
CONTACT_LAB | EMPLOYEE | Employee | ||
CONTACT_LAB | CUSTOMER | Customer | ||
MEMBER_LAB | INDIVIDUAL | Individual Membership | ||
MEMBER_LAB | GROUP | Group Membership | ||
MEMBER_LAB | DISCOVER_CARD | Discover Card | ||
MEMBER_LAB | MASTER_CARD | Master Card | ||
MEMBER_LAB | VISA_CARD | VISA Card | ||
MULTIPLE | HOME | Home | ||
MULTIPLE | WORK | Work | ||
ITEM_LAB | DVD_FULL_SCREEN | DVD: Full Screen | ||
ITEM_LAB | DVD_WIDE_SCREEN | DVD: Wide Screen | ||
ITEM_LAB | NINTENDO_GAMECUBE | Nintendo Gamecube | ||
ITEM_LAB | PLAYSTATION2 | PlayStation2 | ||
ITEM_LAB | XBOX | XBox | ||
ITEM_LAB | BLU-RAY | Blu-ray |
You can query the results with the following statement:
SELECT * FROM common_lookup_lab; |
It should return the following data set:
+----------------------+-----------------------+--------------------+------------------------+ | common_lookup_lab_id | common_lookup_context | common_lookup_type | common_lookup_meaning | +----------------------+-----------------------+--------------------+------------------------+ | 1 | SYSTEM_USER_LAB | SYSTEM_ADMIN | System Administrator | | 2 | SYSTEM_USER_LAB | DBA | Database Administrator | | 3 | CONTACT_LAB | EMPLOYEE | Employee | | 4 | CONTACT_LAB | CUSTOMER | Customer | | 5 | MEMBER_LAB | INDIVIDUAL | Individual Membership | | 6 | MEMBER_LAB | GROUP | Group Membership | | 7 | MEMBER_LAB | DISCOVER_CARD | Discover Card | | 8 | MEMBER_LAB | MASTER_CARD | Master Card | | 9 | MEMBER_LAB | VISA_CARD | VISA Card | | 10 | MULTIPLE_LAB | HOME | Home | | 11 | MULTIPLE_LAB | WORK | Work | | 12 | ITEM_LAB | DVD_FULL_SCREEN | DVD: Full Screen | | 13 | ITEM_LAB | DVD_WIDE_SCREEN | DVD: Wide Screen | | 14 | ITEM_LAB | NINTENDO_GAMECUBE | Nintendo GameCube | | 15 | ITEM_LAB | PLAYSTATION2 | PlayStation2 | | 16 | ITEM_LAB | XBOX | XBOX | | 17 | ITEM_LAB | BLU-RAY | Blu-ray | +----------------------+-----------------------+--------------------+------------------------+ 17 rows in set (0.00 sec) |
After creating the COMMON_LOOKUP_LAB
table, you can now create the two missing foreign key constraints for the SYSTEM_USER_LAB
table. One foreign key is on the SYSTEM_USER_GROUP_ID
and the other is on the SYSTEM_USER_TYPE
column. Both of these foreign keys reference the COMMON_LOOKUP_LAB_ID
column in the COMMON_LOOKUP_LAB
table.
You can add these two foreign key constraints with the ALTER TABLE
statement. Then, you can validate these foreign key constraints with the following query:
SELECT CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint" , CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key" , CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key" FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'system_user_lab' AND kcu.referenced_table_name = 'system_user_lab' ORDER BY tc.table_name , kcu.column_name; |
It should display only the following:
+-----------------------------------------------+------------------------------------------------+--------------------------------------------------+ | Constraint | Foreign Key | Primary Key | +-----------------------------------------------+------------------------------------------------+--------------------------------------------------+ | studentdb.system_user_lab.system_user_lab_fk3 | studentdb.system_user_lab.system_user_group_id | studentdb.common_lookup_lab.common_lookup_lab_id | | studentdb.system_user_lab.system_user_lab_fk4 | studentdb.system_user_lab.system_user_type | studentdb.common_lookup_lab.common_lookup_lab_id | +-----------------------------------------------+------------------------------------------------+--------------------------------------------------+ 2 rows in set (0.05 sec) |
- [2 points] Create the
MEMBER_LAB
table described by the following. It should have an auto incrementing sequence with a starting value of1001
:
Instruction Details ↓
Table Name: MEMBER_LAB | |||||
---|---|---|---|---|---|
Column Name | Constraint | Data Type |
Physical Size |
||
Type | Reference Table | Reference Column | |||
MEMBER_LAB_ID | PRIMARY KEY | Integer | Maximum | ||
MEMBER_TYPE | FOREIGN KEY | COMMON_LOOKUP_LAB | COMMON_LOOKUP_LAB_ID | Integer | Maximum |
NOT NULL | |||||
ACCOUNT_NUMBER | NOT NULL | String | 10 | ||
CREDIT_CARD_NUMBER | NOT NULL | String | 20 | ||
CREDIT_CARD_TYPE | FOREIGN KEY | COMMON_LOOKUP_LAB | COMMON_LOOKUP_LAB_ID | Integer | Maximum |
NOT NULL | |||||
CREATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_ID | Integer | Maximum |
NOT NULL | |||||
CREATION_DATE | NOT NULL | DATE | Date | ||
LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_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 = 'member_lab' ORDER BY 2; |
It should display the following results:
+------------+------------------+--------------------+----------+------------------+ | table_name | ordinal_position | column_name | nullable | column_type | +------------+------------------+--------------------+----------+------------------+ | member_lab | 1 | member_lab_id | NOT NULL | int(10) unsigned | | member_lab | 2 | member_type | | int(10) unsigned | | member_lab | 3 | account_number | NOT NULL | char(10) | | member_lab | 4 | credit_card_number | NOT NULL | char(19) | | member_lab | 5 | credit_card_type | NOT NULL | int(10) unsigned | | member_lab | 6 | created_by | NOT NULL | int(10) unsigned | | member_lab | 7 | creation_date | NOT NULL | date | | member_lab | 8 | last_updated_by | NOT NULL | int(10) unsigned | | member_lab | 9 | last_update_date | NOT NULL | date | +------------+------------------+--------------------+----------+------------------+ 9 rows in set (0.00 sec) |
You should use the following formatting and query to verify completion of the constraints for this step:
1 2 3 4 5 | SELECT tc.table_name , tc.constraint_name , tc.constraint_type FROM information_schema.table_constraints tc WHERE table_name = 'member_lab'; |
It should display the following results:
+------------+-----------------+-----------------+ | table_name | constraint_name | constraint_type | +------------+-----------------+-----------------+ | member_lab | PRIMARY | PRIMARY KEY | | member_lab | member_fk1 | FOREIGN KEY | | member_lab | member_fk2 | FOREIGN KEY | +------------+-----------------+-----------------+ 3 rows in set (0.01 sec) |
You can validate that you created the two foreign key constraints with the following query:
SELECT CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint" , CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key" , CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key" FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'member_lab' ORDER BY tc.table_name , kcu.column_name; |
It should display only the following:
+-------------------------------------+--------------------------------------+----------------------------------------------+ | Constraint | Foreign Key | Primary Key | +-------------------------------------+--------------------------------------+----------------------------------------------+ | studentdb.member_lab.member_lab_fk1 | studentdb.member_lab.created_by | studentdb.system_user_lab.system_user_lab_id | | studentdb.member_lab.member_lab_fk2 | studentdb.member_lab.last_updated_by | studentdb.system_user_lab.system_user_lab_id | +-------------------------------------+--------------------------------------+----------------------------------------------+ 2 rows in set (0.05 sec) |
You should then create a MEMBER_N1
non-unique index on the CREDIT_CARD_TYPE
column of the MEMBER
table:
SELECT s.table_name , s.index_name , s.seq_in_index , s.column_name FROM information_schema.statistics s WHERE s.table_name = 'member_lab' AND s.non_unique = TRUE AND NOT EXISTS (SELECT null FROM information_schema.table_constraints tc WHERE s.table_name = tc.table_name AND s.index_name = tc.constraint_name) ORDER BY s.index_name , s.seq_in_index; |
It should display:
+------------+------------+--------------+--------------------+ | TABLE_NAME | index_name | seq_in_index | column_name | +------------+------------+--------------+--------------------+ | member_lab | member_n1 | 1 | credit_card_type | +------------+------------+--------------+--------------------+ 1 ROWS IN SET (0.23 sec) |
- [2 points] Create the
CONTACT_LAB
table described by the following. It should have an auto incrementing sequence with a starting value of1001
:
Instruction Details ↓
Table Name: CONTACT_LAB | |||||
---|---|---|---|---|---|
Column Name | Constraint | Data Type |
Physical Size |
||
Type | Reference Table | Reference Column | |||
CONTACT_LAB_ID | PRIMARY KEY | Integer | Maximum | ||
MEMBER_LAB_ID | FOREIGN KEY | MEMBER_LAB | MEMBER_LAB_ID | Integer | Maximum |
NOT NULL | |||||
CONTACT_TYPE | FOREIGN KEY | COMMON_LOOKUP_LAB | COMMON_LOOKUP_LAB_ID | Integer | Maximum |
NOT NULL | |||||
FIRST_NAME | NOT NULL | String | 20 | ||
MIDDLE_NAME | NOT NULL | String | 20 | ||
LAST_NAME | String | 20 | |||
CREATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_ID | Integer | Maximum |
NOT NULL | |||||
CREATION_DATE | NOT NULL | DATE | Date | ||
LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_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 = 'contact_lab' ORDER BY 2; |
It should display the following results:
+-------------+------------------+------------------+----------+------------------+ | table_name | ordinal_position | column_name | nullable | column_type | +-------------+------------------+------------------+----------+------------------+ | contact_lab | 1 | contact_lab_id | NOT NULL | int(10) unsigned | | contact_lab | 2 | member_lab_id | NOT NULL | int(10) unsigned | | contact_lab | 3 | contact_type | NOT NULL | int(10) unsigned | | contact_lab | 4 | first_name | NOT NULL | char(20) | | contact_lab | 5 | middle_name | | char(20) | | contact_lab | 6 | last_name | NOT NULL | char(20) | | contact_lab | 7 | created_by | NOT NULL | int(10) unsigned | | contact_lab | 8 | creation_date | NOT NULL | date | | contact_lab | 9 | last_updated_by | NOT NULL | int(10) unsigned | | contact_lab | 10 | last_update_date | NOT NULL | date | +-------------+------------------+------------------+----------+------------------+ 10 rows in set (0.00 sec) |
You should use the following formatting and query to verify completion of the constraints for this step:
1 2 3 4 5 | SELECT tc.table_name , tc.constraint_name , tc.constraint_type FROM information_schema.table_constraints tc WHERE table_name = 'contact_lab'; |
It should display the following results:
+-------------+-----------------+-----------------+ | table_name | constraint_name | constraint_type | +-------------+-----------------+-----------------+ | contact_lab | PRIMARY | PRIMARY KEY | | contact_lab | contact_fk1 | FOREIGN KEY | | contact_lab | contact_fk2 | FOREIGN KEY | | contact_lab | contact_fk3 | FOREIGN KEY | +-------------+-----------------+-----------------+ 4 rows in set (0.00 sec) |
You can validate that you created the two foreign key constraints with the following query:
SELECT CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint" , CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key" , CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key" FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'contact_lab' ORDER BY 1; |
It should display only the following:
+---------------------------------------+---------------------------------------+----------------------------------------------+ | Constraint | Foreign Key | Primary Key | +---------------------------------------+---------------------------------------+----------------------------------------------+ | studentdb.contact_lab.contact_lab_fk1 | studentdb.contact_lab.member_lab_id | studentdb.member_lab.member_lab_id | | studentdb.contact_lab.contact_lab_fk2 | studentdb.contact_lab.created_by | studentdb.system_user_lab.system_user_lab_id | | studentdb.contact_lab.contact_lab_fk3 | studentdb.contact_lab.last_updated_by | studentdb.system_user_lab.system_user_lab_id | +---------------------------------------+---------------------------------------+----------------------------------------------+ 3 rows in set (0.05 sec) |
You should then create a CONTACT_N1
non-unique index on the MEMBER_ID
column of the CONTACT
table, and a CONTACT_N2
non-unique index on the CONTACT_TYPE
column of the CONTACT
table.
You can see the non-unique indexes with the following query:
SELECT s.table_name , s.index_name , s.seq_in_index , s.column_name FROM information_schema.statistics s WHERE s.table_name = 'contact_lab' AND s.non_unique = TRUE AND NOT EXISTS (SELECT null FROM information_schema.table_constraints tc WHERE s.table_name = tc.table_name AND s.index_name = tc.constraint_name) ORDER BY s.index_name , s.seq_in_index; |
It should display:
+-------------+----------------+--------------+---------------+ | table_name | index_name | seq_in_index | column_name | +-------------+----------------+--------------+---------------+ | contact_lab | contact_lab_n1 | 1 | member_lab_id | | contact_lab | contact_lab_n2 | 1 | contact_type | +-------------+----------------+--------------+---------------+ 2 rows in set (0.22 sec) |
- [2 points] Create the
ADDRESS_LAB
table described by the following. It should have an auto incrementing sequence with a starting value of1001
:
Instruction Details ↓
Table Name: ADDRESS_LAB | |||||
---|---|---|---|---|---|
Column Name | Constraint | Data Type |
Physical Size |
||
Type | Reference Table | Reference Column | |||
ADDRESS_LAB_ID | PRIMARY KEY | Integer | Maximum | ||
CONTACT_LAB_ID | FOREIGN KEY | CONTACT_LAB | CONTACT_LAB_ID | Integer | Maximum |
NOT NULL | |||||
ADDRESS_TYPE | FOREIGN KEY | COMMON_LOOKUP_LAB | COMMON_LOOKUP_LAB_ID | Integer | Maximum |
NOT NULL | |||||
LAST_NAME | NOT NULL | String | 20 | ||
CITY | NOT NULL | String | 30 | ||
STATE_PROVINCE | NOT NULL | String | 30 | ||
POSTAL_CODE | NOT NULL | String | 20 | ||
CREATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_ID | Integer | Maximum |
NOT NULL | |||||
CREATION_DATE | NOT NULL | DATE | Date | ||
LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_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 = 'address_lab' ORDER BY 2; |
It should display the following results:
+-------------+------------------+------------------+----------+------------------+ | table_name | ordinal_position | column_name | nullable | column_type | +-------------+------------------+------------------+----------+------------------+ | address_lab | 1 | address_lab_id | NOT NULL | int(10) unsigned | | address_lab | 2 | contact_lab_id | NOT NULL | int(10) unsigned | | address_lab | 3 | address_type | NOT NULL | int(10) unsigned | | address_lab | 4 | city | NOT NULL | char(30) | | address_lab | 5 | state_province | NOT NULL | char(30) | | address_lab | 6 | postal_code | NOT NULL | char(20) | | address_lab | 7 | created_by | NOT NULL | int(10) unsigned | | address_lab | 8 | creation_date | NOT NULL | date | | address_lab | 9 | last_updated_by | NOT NULL | int(10) unsigned | | address_lab | 10 | last_update_date | NOT NULL | date | +-------------+------------------+------------------+----------+------------------+ 10 rows in set (0.02 sec) |
You should use the following formatting and query to verify completion of the constraints for this step:
1 2 3 4 5 | SELECT tc.table_name , tc.constraint_name , tc.constraint_type FROM information_schema.table_constraints tc WHERE table_name = 'address_lab'; |
It should display the following results:
+-------------+-----------------+-----------------+ | table_name | constraint_name | constraint_type | +-------------+-----------------+-----------------+ | address_lab | PRIMARY | PRIMARY KEY | | address_lab | address_fk1 | FOREIGN KEY | | address_lab | address_fk2 | FOREIGN KEY | | address_lab | address_fk3 | FOREIGN KEY | +-------------+-----------------+-----------------+ 4 rows in set (0.00 sec) |
You can validate that you created the two foreign key constraints with the following query:
SELECT CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint" , CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key" , CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key" FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'address_lab' ORDER BY tc.table_name , tc.constraint_name; |
It should display only the following:
+---------------------------------------+---------------------------------------+----------------------------------------------+ | Constraint | Foreign Key | Primary Key | +---------------------------------------+---------------------------------------+----------------------------------------------+ | studentdb.address_lab.address_lab_fk1 | studentdb.address_lab.contact_lab_id | studentdb.contact_lab.contact_lab_id | | studentdb.address_lab.address_lab_fk2 | studentdb.address_lab.created_by | studentdb.system_user_lab.system_user_lab_id | | studentdb.address_lab.address_lab_fk3 | studentdb.address_lab.last_updated_by | studentdb.system_user_lab.system_user_lab_id | +---------------------------------------+---------------------------------------+----------------------------------------------+ 3 rows in set (0.06 sec) |
You should then create a ADDRESS_LAB_N1
non-unique index on the CONTACT_ID
column of the ADDRESS_LAB
table, and a CONTACT_N2
non-unique index on the ADDRESS_TYPE
column of the ADDRESS_LAB
table.
You can see the non-unique indexes with the following query:
SELECT s.table_name , s.index_name , s.seq_in_index , s.column_name FROM information_schema.statistics s WHERE s.table_name = 'address_lab' AND s.non_unique = TRUE AND NOT EXISTS (SELECT null FROM information_schema.table_constraints tc WHERE s.table_name = tc.table_name AND s.index_name = tc.constraint_name) ORDER BY s.index_name , s.seq_in_index; |
It should display:
+-------------+----------------+--------------+----------------+ | table_name | index_name | seq_in_index | column_name | +-------------+----------------+--------------+----------------+ | address_lab | address_lab_n1 | 1 | contact_lab_id | | address_lab | address_lab_n2 | 1 | address_type | +-------------+----------------+--------------+----------------+ 2 rows in set (0.27 sec) |
- [2 points] Create the
STREET_ADDRESS_LAB
table described by the following. It should have an auto incrementing sequence with a starting value of1001
:
Instruction Details ↓
Table Name: STREET_ADDRESS_LAB | |||||
---|---|---|---|---|---|
Column Name | Constraint | Data Type |
Physical Size |
||
Type | Reference Table | Reference Column | |||
STREET_ADDRESS_LAB_ID | PRIMARY KEY | Integer | Maximum | ||
ADDRESS_LAB_ID | FOREIGN KEY | ADDRESS_LAB | ADDRESS_LAB_ID | Integer | Maximum |
NOT NULL | |||||
STREET_ADDRESS | NOT NULL | String | 30 | ||
CREATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_ID | Integer | Maximum |
NOT NULL | |||||
CREATION_DATE | NOT NULL | DATE | Date | ||
LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_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 = 'street_address_lab' ORDER BY 2; |
It should display the following results:
+--------------------+------------------+-----------------------+----------+------------------+ | table_name | ordinal_position | column_name | nullable | column_type | +--------------------+------------------+-----------------------+----------+------------------+ | street_address_lab | 1 | street_address_lab_id | NOT NULL | int(10) unsigned | | street_address_lab | 2 | address_lab_id | NOT NULL | int(10) unsigned | | street_address_lab | 3 | street_address | NOT NULL | char(30) | | street_address_lab | 4 | created_by | NOT NULL | int(10) unsigned | | street_address_lab | 5 | creation_date | NOT NULL | date | | street_address_lab | 6 | last_updated_by | NOT NULL | int(10) unsigned | | street_address_lab | 7 | last_update_date | NOT NULL | date | +--------------------+------------------+-----------------------+----------+------------------+ 7 rows in set (0.00 sec) |
You should use the following formatting and query to verify completion of the constraints for this step:
1 2 3 4 5 | SELECT tc.table_name , tc.constraint_name , tc.constraint_type FROM information_schema.table_constraints tc WHERE TABLE_NAME = 'street_address_lab'; |
It should display the following results:
+--------------------+------------------------+-----------------+ | table_name | constraint_name | constraint_type | +--------------------+------------------------+-----------------+ | street_address_lab | PRIMARY | PRIMARY KEY | | street_address_lab | street_address_lab_fk1 | FOREIGN KEY | | street_address_lab | street_address_lab_fk2 | FOREIGN KEY | | street_address_lab | street_address_lab_fk3 | FOREIGN KEY | +--------------------+------------------------+-----------------+ 4 rows in set (0.02 sec) |
You can validate that you created the two foreign key constraints with the following query:
SELECT CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint" , CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key" , CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key" FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'street_address_lab' ORDER BY tc.table_name , tc.constraint_name; |
It should display only the following:
+-----------------------------------------------------+----------------------------------------------+----------------------------------------------+ | Constraint | Foreign Key | Primary Key | +-----------------------------------------------------+----------------------------------------------+----------------------------------------------+ | studentdb.street_address_lab.street_address_lab_fk1 | studentdb.street_address_lab.address_lab_id | studentdb.address_lab.address_lab_id | | studentdb.street_address_lab.street_address_lab_fk2 | studentdb.street_address_lab.created_by | studentdb.system_user_lab.system_user_lab_id | | studentdb.street_address_lab.street_address_lab_fk3 | studentdb.street_address_lab.last_updated_by | studentdb.system_user_lab.system_user_lab_id | +-----------------------------------------------------+----------------------------------------------+----------------------------------------------+ 3 rows in set (0.05 sec) |
- [2 points] Create the
TELEPHONE_LAB
table described by the following. It should have an auto incrementing sequence with a starting value of1001
:
Instruction Details ↓
Table Name: TELEPHONE_LAB | |||||
---|---|---|---|---|---|
Column Name | Constraint | Data Type |
Physical Size |
||
Type | Reference Table | Reference Column | |||
TELEPHONE_LAB_ID | PRIMARY KEY | Integer | Maximum | ||
CONTACT_LAB_ID | FOREIGN KEY | CONTACT_LAB | CONTACT_LAB_ID | Integer | Maximum |
NOT NULL | |||||
ADDRESS_LAB_ID | FOREIGN KEY | ADDRESS_LAB | ADDRESS_LAB_ID | Integer | Maximum |
NOT NULL | |||||
TELEPHONE_TYPE | FOREIGN KEY | COMMON_LOOKUP_LAB | COMMON_LOOKUP_LAB_ID | Integer | Maximum |
NOT NULL | |||||
COUNTRY_CODE | NOT NULL | String | 3 | ||
AREA_CODE | NOT NULL | String | 6 | ||
TELEPHONE_NUMBER | NOT NULL | String | 10 | ||
CREATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_ID | Integer | Maximum |
NOT NULL | |||||
CREATION_DATE | NOT NULL | DATE | Date | ||
LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_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 = 'telephone_lab' ORDER BY 2; |
It should display the following results:
+---------------+------------------+------------------+----------+------------------+ | table_name | ordinal_position | column_name | nullable | column_type | +---------------+------------------+------------------+----------+------------------+ | telephone_lab | 1 | telephone_lab_id | NOT NULL | int(10) unsigned | | telephone_lab | 2 | contact_lab_id | NOT NULL | int(10) unsigned | | telephone_lab | 3 | address_lab_id | | int(10) unsigned | | telephone_lab | 4 | telephone_type | NOT NULL | int(10) unsigned | | telephone_lab | 5 | country_code | NOT NULL | char(3) | | telephone_lab | 6 | area_code | NOT NULL | char(6) | | telephone_lab | 7 | telephone_number | NOT NULL | char(10) | | telephone_lab | 8 | created_by | NOT NULL | int(10) unsigned | | telephone_lab | 9 | creation_date | NOT NULL | date | | telephone_lab | 10 | last_updated_by | NOT NULL | int(10) unsigned | | telephone_lab | 11 | last_update_date | NOT NULL | date | +---------------+------------------+------------------+----------+------------------+ 11 rows in set (0.00 sec) |
You should use the following formatting and query to verify completion of the constraints for this step:
1 2 3 4 5 | SELECT tc.table_name , tc.constraint_name , tc.constraint_type FROM information_schema.table_constraints tc WHERE table_name = 'telephone_lab'; |
It should display the following results:
+---------------+-----------------+-----------------+ | table_name | constraint_name | constraint_type | +---------------+-----------------+-----------------+ | telephone_lab | PRIMARY | PRIMARY KEY | | telephone_lab | telephone_fk1 | FOREIGN KEY | | telephone_lab | telephone_fk2 | FOREIGN KEY | | telephone_lab | telephone_fk3 | FOREIGN KEY | | telephone_lab | telephone_fk4 | FOREIGN KEY | +---------------+-----------------+-----------------+ 5 rows in set (0.00 sec) |
You can validate that you created the two foreign key constraints with the following query:
SELECT CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint" , CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key" , CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key" FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'telephone_lab' ORDER BY 1; |
It should display only the following:
+-------------------------------------------+-----------------------------------------+----------------------------------------------+ | Constraint | Foreign Key | Primary Key | +-------------------------------------------+-----------------------------------------+----------------------------------------------+ | studentdb.telephone_lab.telephone_lab_fk1 | studentdb.telephone_lab.contact_lab_id | studentdb.contact_lab.contact_lab_id | | studentdb.telephone_lab.telephone_lab_fk2 | studentdb.telephone_lab.address_lab_id | studentdb.address_lab.address_lab_id | | studentdb.telephone_lab.telephone_lab_fk3 | studentdb.telephone_lab.created_by | studentdb.system_user_lab.system_user_lab_id | | studentdb.telephone_lab.telephone_lab_fk4 | studentdb.telephone_lab.last_updated_by | studentdb.system_user_lab.system_user_lab_id | +-------------------------------------------+-----------------------------------------+----------------------------------------------+ 4 rows in set (0.05 sec) |
You should then create a TELEPHONE_LAB_N1
non-unique index on the CONTACT_ID
and ADDRESS_ID
columns of the TELEPHONE_LAB
table, a TELEPHONE_N2
non-unique index on the TELEPHONE_TYPE
column of the TELEPHONE_LAB
table:
SELECT s.table_name , s.index_name , s.seq_in_index , s.column_name FROM information_schema.statistics s WHERE s.table_name = 'telephone_lab' AND s.non_unique = TRUE AND NOT EXISTS (SELECT null FROM information_schema.table_constraints tc WHERE s.table_name = tc.table_name AND s.index_name = tc.constraint_name) ORDER BY s.index_name , s.seq_in_index; |
It should display:
+---------------+------------------+--------------+----------------+ | table_name | index_name | seq_in_index | column_name | +---------------+------------------+--------------+----------------+ | telephone_lab | telephone_lab_n1 | 1 | address_lab_id | | telephone_lab | telephone_lab_n2 | 1 | telephone_type | +---------------+------------------+--------------+----------------+ 2 rows in set (0.28 sec) |
- [2 points] Create the
RENTAL_LAB
table described by the following. It should have an auto incrementing sequence with a starting value of1001
:
Instruction Details ↓
Table Name: RENTAL_LAB | |||||
---|---|---|---|---|---|
Column Name | Constraint | Data Type |
Physical Size |
||
Type | Reference Table | Reference Column | |||
RENTAL_LAB_ID | PRIMARY KEY | Integer | Maximum | ||
CUSTOMER_ID | FOREIGN KEY | CONTACT_LAB | CONTACT_LAB_ID | Integer | Maximum |
NOT NULL | |||||
CHECK_OUT_DATE | NOT NULL | DATE | Date | ||
RETURN_DATE | NOT NULL | DATE | Date | ||
CREATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_ID | Integer | Maximum |
NOT NULL | |||||
CREATION_DATE | NOT NULL | DATE | Date | ||
LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_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 = 'rental_lab' ORDER BY 2; |
It should display the following results:
+------------+------------------+------------------+----------+------------------+ | table_name | ordinal_position | column_name | nullable | column_type | +------------+------------------+------------------+----------+------------------+ | rental_lab | 1 | rental_lab_id | NOT NULL | int(10) unsigned | | rental_lab | 2 | customer_id | NOT NULL | int(10) unsigned | | rental_lab | 3 | check_out_date | NOT NULL | date | | rental_lab | 4 | return_date | NOT NULL | date | | rental_lab | 5 | created_by | NOT NULL | int(10) unsigned | | rental_lab | 6 | creation_date | NOT NULL | date | | rental_lab | 7 | last_updated_by | NOT NULL | int(10) unsigned | | rental_lab | 8 | last_update_date | NOT NULL | date | +------------+------------------+------------------+----------+------------------+ 8 rows in set (0.02 sec) |
You should use the following formatting and query to verify completion of the constraints for this step:
1 2 3 4 5 | SELECT tc.table_name , tc.constraint_name , tc.constraint_type FROM information_schema.table_constraints tc WHERE TABLE_NAME = 'rental_lab'; |
It should display the following results:
+------------+-----------------+-----------------+ | table_name | constraint_name | constraint_type | +------------+-----------------+-----------------+ | rental_lab | PRIMARY | PRIMARY KEY | | rental_lab | rental_fk1 | FOREIGN KEY | | rental_lab | rental_fk2 | FOREIGN KEY | | rental_lab | rental_fk3 | FOREIGN KEY | +------------+-----------------+-----------------+ 4 rows in set (0.02 sec) |
You can validate that you created the two foreign key constraints with the following query:
SELECT CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint" , CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key" , CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key" FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'rental_lab' ORDER BY tc.table_name , tc.constraint_name; |
It should display only the following:
+-------------------------------------+--------------------------------------+----------------------------------------------+ | Constraint | Foreign Key | Primary Key | +-------------------------------------+--------------------------------------+----------------------------------------------+ | studentdb.rental_lab.rental_lab_fk1 | studentdb.rental_lab.customer_id | studentdb.contact_lab.contact_lab_id | | studentdb.rental_lab.rental_lab_fk2 | studentdb.rental_lab.created_by | studentdb.system_user_lab.system_user_lab_id | | studentdb.rental_lab.rental_lab_fk3 | studentdb.rental_lab.last_updated_by | studentdb.system_user_lab.system_user_lab_id | +-------------------------------------+--------------------------------------+----------------------------------------------+ 3 rows in set (0.05 sec) |
- [2 points] Create the
ITEM_LAB
table described by the following. It should have an auto incrementing sequence with a starting value of1001
:
Instruction Details ↓
Table Name: ITEM_LAB | |||||
---|---|---|---|---|---|
Column Name | Constraint | Data Type |
Physical Size |
||
Type | Reference Table | Reference Column | |||
ITEM_LAB_ID | PRIMARY KEY | Integer | Maximum | ||
ITEM_BARCODE | NOT NULL | String | 14 | ||
ITEM_TYPE | FOREIGN KEY | COMMON_LOOKUP_LAB | COMMON_LOOKUP_LAB_ID | Integer | Maximum |
NOT NULL | |||||
ADDRESS_LAB_ID | FOREIGN KEY | ADDRESS_LAB | ADDRESS_LAB_ID | Integer | Maximum |
NOT NULL | |||||
ITEM_TITLE | NOT NULL | String | 60 | ||
ITEM_SUBTITLE | String | 60 | |||
ITEM_RATING | NOT NULL | String | 8 | ||
ITEM_RELEASE_DATE | NOT NULL | DATE | Date | ||
CREATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_ID | Integer | Maximum |
NOT NULL | |||||
CREATION_DATE | NOT NULL | DATE | Date | ||
LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_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 = 'item_lab' ORDER BY 2; |
It should display the following results:
+------------+------------------+-------------------+----------+------------------+ | table_name | ordinal_position | column_name | nullable | column_type | +------------+------------------+-------------------+----------+------------------+ | item_lab | 1 | item_lab_id | NOT NULL | int(10) unsigned | | item_lab | 2 | item_barcode | NOT NULL | char(14) | | item_lab | 3 | item_type | NOT NULL | int(10) unsigned | | item_lab | 4 | item_title | NOT NULL | char(60) | | item_lab | 5 | item_subtitle | | char(60) | | item_lab | 6 | item_rating | NOT NULL | char(8) | | item_lab | 7 | item_release_date | NOT NULL | date | | item_lab | 8 | created_by | NOT NULL | int(10) unsigned | | item_lab | 9 | creation_date | NOT NULL | date | | item_lab | 10 | last_updated_by | NOT NULL | int(10) unsigned | | item_lab | 11 | last_update_date | NOT NULL | date | +------------+------------------+-------------------+----------+------------------+ 11 rows in set (0.00 sec) |
You should use the following formatting and query to verify completion of the constraints for this step:
1 2 3 4 5 | SELECT tc.table_name , tc.constraint_name , tc.constraint_type FROM information_schema.table_constraints tc WHERE TABLE_NAME = 'item_lab'; |
It should display the following results:
+------------+-----------------+-----------------+ | table_name | constraint_name | constraint_type | +------------+-----------------+-----------------+ | item_lab | PRIMARY | PRIMARY KEY | | item_lab | item_fk1 | FOREIGN KEY | | item_lab | item_fk2 | FOREIGN KEY | | item_lab | item_fk3 | FOREIGN KEY | +------------+-----------------+-----------------+ 4 rows in set (0.00 sec) |
You can validate that you created the two foreign key constraints with the following query:
SELECT CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint" , CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key" , CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key" FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'item_lab' ORDER BY tc.table_name , tc.constraint_name; |
It should display only the following:
+---------------------------------+------------------------------------+--------------------------------------------------+ | Constraint | Foreign Key | Primary Key | +---------------------------------+------------------------------------+--------------------------------------------------+ | studentdb.item_lab.item_lab_fk1 | studentdb.item_lab.item_type | studentdb.common_lookup_lab.common_lookup_lab_id | | studentdb.item_lab.item_lab_fk2 | studentdb.item_lab.created_by | studentdb.system_user_lab.system_user_lab_id | | studentdb.item_lab.item_lab_fk3 | studentdb.item_lab.last_updated_by | studentdb.system_user_lab.system_user_lab_id | +---------------------------------+------------------------------------+--------------------------------------------------+ 3 rows in set (0.05 sec) |
- [2 points] Create the
RENTAL_ITEM_LAB
table described by the following. It should have an auto incrementing sequence with a starting value of1001
:
Instruction Details ↓
Table Name: RENTAL_ITEM_LAB | |||||
---|---|---|---|---|---|
Column Name | Constraint | Data Type |
Physical Size |
||
Type | Reference Table | Reference Column | |||
RENTAL_ITEM_LAB_ID | PRIMARY KEY | Integer | Maximum | ||
RENTAL_LAB_ID | FOREIGN KEY | RENTAL_LAB | RENTAL_LAB_ID | Integer | Maximum |
NOT NULL | |||||
ITEM_LAB_ID | FOREIGN KEY | ITEM_LAB | ITEM_LAB_ID | Integer | Maximum |
NOT NULL | |||||
CREATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_ID | Integer | Maximum |
NOT NULL | |||||
CREATION_DATE | NOT NULL | DATE | Date | ||
LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER_LAB | SYSTEM_USER_LAB_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 = 'rental_item_lab' ORDER BY 2; |
It should display the following results:
+-----------------+------------------+--------------------+----------+------------------+ | table_name | ordinal_position | column_name | nullable | column_type | +-----------------+------------------+--------------------+----------+------------------+ | rental_item_lab | 1 | rental_item_lab_id | NOT NULL | int(10) unsigned | | rental_item_lab | 2 | rental_lab_id | NOT NULL | int(10) unsigned | | rental_item_lab | 3 | item_lab_id | NOT NULL | int(10) unsigned | | rental_item_lab | 4 | created_by | NOT NULL | int(10) unsigned | | rental_item_lab | 5 | creation_date | NOT NULL | date | | rental_item_lab | 6 | last_updated_by | NOT NULL | int(10) unsigned | | rental_item_lab | 7 | last_update_date | NOT NULL | date | +-----------------+------------------+--------------------+----------+------------------+ 7 rows in set (0.02 sec) |
You should use the following formatting and query to verify completion of the constraints for this step:
1 2 3 4 5 | SELECT tc.table_name , tc.constraint_name , tc.constraint_type FROM information_schema.table_constraints tc WHERE TABLE_NAME = 'rental_item_lab'; |
It should display the following results:
+-----------------+-----------------+-----------------+ | table_name | constraint_name | constraint_type | +-----------------+-----------------+-----------------+ | rental_item_lab | PRIMARY | PRIMARY KEY | | rental_item_lab | rental_item_fk1 | FOREIGN KEY | | rental_item_lab | rental_item_fk2 | FOREIGN KEY | | rental_item_lab | rental_item_fk3 | FOREIGN KEY | | rental_item_lab | rental_item_fk4 | FOREIGN KEY | +-----------------+-----------------+-----------------+ 5 rows in set (0.02 sec) |
You can validate that you created the two foreign key constraints with the following query:
SELECT CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint" , CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key" , CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key" FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'rental_item_lab' ORDER BY 1; |
It should display only the following:
+-----------------------------------------------+-------------------------------------------+----------------------------------------------+ | Constraint | Foreign Key | Primary Key | +-----------------------------------------------+-------------------------------------------+----------------------------------------------+ | studentdb.rental_item_lab.rental_item_lab_fk1 | studentdb.rental_item_lab.rental_lab_id | studentdb.rental_lab.rental_lab_id | | studentdb.rental_item_lab.rental_item_lab_fk2 | studentdb.rental_item_lab.item_lab_id | studentdb.item_lab.item_lab_id | | studentdb.rental_item_lab.rental_item_lab_fk3 | studentdb.rental_item_lab.created_by | studentdb.system_user_lab.system_user_lab_id | | studentdb.rental_item_lab.rental_item_lab_fk4 | studentdb.rental_item_lab.last_updated_by | studentdb.system_user_lab.system_user_lab_id | +-----------------------------------------------+-------------------------------------------+----------------------------------------------+ 4 rows in set (0.05 sec) |
- [0 points] You can confirm the creation of the ten tables with the following query:
Confirmation Details for Tables ↓
SELECT a.table_name_base , b.table_name_lab FROM (SELECT table_name AS table_name_base FROM information_schema.tables WHERE table_name IN ('SYSTEM_USER' ,'COMMON_LOOKUP' ,'MEMBER' ,'CONTACT' ,'ADDRESS' ,'STREET_ADDRESS' ,'TELEPHONE' ,'ITEM' ,'RENTAL' ,'RENTAL_ITEM')) a INNER JOIN (SELECT table_name AS table_name_lab FROM information_schema.tables WHERE table_name IN ('SYSTEM_USER_LAB' ,'COMMON_LOOKUP_LAB' ,'MEMBER_LAB' ,'CONTACT_LAB' ,'ADDRESS_LAB' ,'STREET_ADDRESS_LAB' ,'TELEPHONE_LAB' ,'ITEM_LAB' ,'RENTAL_LAB' ,'RENTAL_ITEM_LAB')) b ON a.table_name_base = substring(b.table_name_lab,1,instr(b.table_name_lab,'_lab') - 1) ORDER BY CASE WHEN table_name_base LIKE 'SYSTEM_USER%' THEN 0 WHEN table_name_base LIKE 'COMMON_LOOKUP%' THEN 1 WHEN table_name_base LIKE 'MEMBER%' THEN 2 WHEN table_name_base LIKE 'CONTACT%' THEN 3 WHEN table_name_base LIKE 'ADDRESS%' THEN 4 WHEN table_name_base LIKE 'STREET_ADDRESS%' THEN 5 WHEN table_name_base LIKE 'TELEPHONE%' THEN 6 WHEN table_name_base LIKE 'ITEM%' THEN 7 WHEN table_name_base LIKE 'RENTAL%' AND NOT table_name_base LIKE 'RENTAL_ITEM%' THEN 8 WHEN table_name_base LIKE 'RENTAL_ITEM%' THEN 9 END; |
It returns:
+-----------------+--------------------+ | table_name_base | table_name_lab | +-----------------+--------------------+ | system_user | system_user_lab | | common_lookup | common_lookup_lab | | member | member_lab | | contact | contact_lab | | address | address_lab | | street_address | street_address_lab | | telephone | telephone_lab | | item | item_lab | | rental | rental_lab | | rental_item | rental_item_lab | +-----------------+--------------------+ 10 rows in set (0.02 sec) |
Make sure to fix the path to the cleanup and create_store scripts.
Jeremy
14 Jan 15 at 2:00 pm
In Step 1 you have us create the table system_user_lab with the field
SYSTEM_USER_TYPE
asNOT NULL
. But when you have us insert the row into the table you do not specifiy a value. Should we put in a 1?Tyler
15 Jan 15 at 8:10 pm
When we are asked to add the constraints to the
system_user_lab
table for thesystem_user_group_id
and thesystem_user_type
, the instructions ask us to reference thesystem_user_lab
table and thesystem_user_id
column, but the validation says it references thecommon_lookup_lab
table.mar09013
15 Jan 15 at 10:41 pm
Can you qualify which validation script is wrong by pasting it into a comment.
michaelmclaughlin
16 Jan 15 at 2:41 am
Jeremy, It’s fixed. Thanks.
michaelmclaughlin
16 Jan 15 at 2:52 am
Tyler, That’s true. It’s fixed and note that you’ll need to update that row after inserting values into the
COMMON_LOOKUP_LAB
table.michaelmclaughlin
16 Jan 15 at 3:00 am
MYSQL step 1
Last name column in the first table shows that the character length is
'1'
but in a later table it shows as'20'
which is correct?Joseph Tracy
16 Jan 15 at 12:21 pm
Step 4
Currently
The other tables have been
Joseph Tracy
16 Jan 15 at 3:52 pm
Under all of the sections the check output needs to have “_lab” added to them under the primary key and foreign key columns.
Tyler Nelson
16 Jan 15 at 5:06 pm
Bro. Mclaughlin, when you state the column names in step 6’s table, you have
LINE_NUMBER
NOT NULL
Integer
Maximum, when it is not actually in the rest of the lab.Tad Livingston
17 Jan 15 at 10:56 am
In step #1, it instructs us to create a unique index called
SYSTEM_USER_LAB_U1
. After creating it, the instructions read, “You can then use this query to display the unique constants on the table:” The example table does not show the unique index; however, when I run my program, the unique index is display. Is this a mistake on the lab’s part or mine?Carissa L
17 Jan 15 at 11:58 am
Carissa, You were right, the query was incorrect and the displayed output was wrong. I’ve updated both.
michaelmclaughlin
21 Jan 15 at 12:52 am
Tad, You’re right the web page was wrong and the column was removed from the list of the
STREET_ADDRESS_LAB
table.michaelmclaughlin
21 Jan 15 at 12:57 am
Joseph, You’re right. It’s a typo and now it’s fixed.
michaelmclaughlin
21 Jan 15 at 1:06 am
Joseph, It’s a great catch, and it’s now fixed.
michaelmclaughlin
21 Jan 15 at 1:10 am
Tyler, Great catch. It’s fixed now.
michaelmclaughlin
21 Jan 15 at 2:04 am
Step 2 states:
You should then create a
COMMON_LOOKUP_LAB_U1
unique index on theCOMMON_LOOKUP
table, and aCOMMON_LOOKUP_LAB_N1
non-unique index on theCOMMON_LOOKUP
table:However, no columns are given for the
N1
index, and the expected results do not show the N1 index. The instructions either need to remove the reference to theN1
index, or specify which column(s) should be in the index.Dave Stevenson
23 Jan 15 at 6:17 pm
In step 4, after creating non unique indexes
CONTACT_N1
andCONTACT_N2
, the verification code is expected to query the non-unique indexes. Instead the wrong verification code is present; it shows the foreign key constraints instead.Dave Stevenson
23 Jan 15 at 6:36 pm
I believe the constraint names in step 6:
should have ‘
_lab
‘ in them:Dave Stevenson
23 Jan 15 at 6:57 pm
In step 7, the instructions state the
telephone_lab_n1
index consists of two columns, but the verification code expected results show only one of them.Dave Stevenson
23 Jan 15 at 7:11 pm
Dave, Great catch! You’re right, and I’ve updated the page with the correction.
michaelmclaughlin
26 Jan 15 at 2:41 am
step 4. reference for the FK for member_lab_id is incorrect.
Joseph Tracy
31 Jan 15 at 12:30 pm
step 9.
address_lab_id
is included in lab 2 item_lab but is not included in lab 4item_lab
and throws an error for missing input.Joseph Tracy
31 Jan 15 at 1:59 pm
On step 2, creating the
common_lookup_lab
unique and non unique indexes, you don’t tell them in the instructions which columns to add the indexes on. Also, in the validation query, you don’t have anything for the non-unique index.Jeremy
2 Feb 15 at 12:14 pm
The first paragraph has many instance of ‘oracle’ instead of mysql
“You begin these steps by running the cleanup_oracle.sql and create_oracle_store.sql scripts. A great starting point for this lab is to review the create_oracle_store.sql script. The create_oracle_store.sql script creates 10 tables. Your Lab #2 script creates 10 tables with some changes and alterations. You should use the script provided in the downloaded instance or create a script like”
Brett
20 Feb 15 at 3:45 pm
Your script is running ri, and later labs say to run ri2. I think there needs to be some consistency in what ri to run. as well, the instructions need to match consistently to which ri you are using. Example lab 7’s verification and all instruction from lab 5 on is based on ri2. However in this lab they do not learn about the rating_agency table which would benefit them as they have no idea of its existence until lab 6 when they need to insert into item. and find out they cannot insert into item with strings for rating.
Brett
20 Feb 15 at 3:55 pm
Joseph, I believe that the
address_lab_id
is fixed.michaelmclaughlin
8 Mar 15 at 2:52 am
Joseph, That
MEMBER_LAB_ID
reference is fixed. Thanks.michaelmclaughlin
8 Mar 15 at 2:58 am
Brett, The Oracle references have been replaced with MySQL references. Thanks!
michaelmclaughlin
8 Mar 15 at 3:01 am
Brett,
The lab design has two purposes and yes using the
_ri
and_ri2
scripts are a critical components for accomplishing certain goals:create_mysql_store_ri.sql
script during this lab and thecreate_mysql_store_ri2.sql
script.INSERT
statements (and yes, there are architectural details later that introduce additional primary and foreign key concepts).You’re correct, the
INSERT
statements from Lab #4 aren’t reusable because of the differences but there are examples in theseed_mysql_store_ri2.sql
script that the student can leverage in Lab #6.michaelmclaughlin
8 Mar 15 at 12:21 pm
Jeremy, Great catch! I’ve fixed it. Thanks!
michaelmclaughlin
8 Mar 15 at 11:37 pm
Dave, The diagnostic program was changed earlier along with the output for Step #7. If you notice that any further change is required, please post another comment.
michaelmclaughlin
9 Mar 15 at 12:18 am
Dave, Below the validation of foreign key constraints, you’ll find the validation script for
CONTACT_N1
andCONTACT_N2
non-unique constraints. Post another comment if you still see a problem.michaelmclaughlin
9 Mar 15 at 12:24 am
Dave, Great catch! I’ve added the material for the
COMMON_LOOKUP_LAB_N1
index.michaelmclaughlin
9 Mar 15 at 12:46 am
In step one on instruction L the oracle instructions are presented instead of the mySQL instructions.
Copy the content from line 27 to 79 from the create_oracle_store.sql script and paste it to lines 23 through 75. Then, make the following changes:
Jason
23 Sep 15 at 1:17 pm
Jason, I’ve replaced it with the following:
Copy the content from line 11 to 55 from the create_mysql_store.sql script and paste it to lines 32 through 76.
michaelmclaughlin
24 Sep 15 at 11:46 pm