Database Tutorial

Course Tutorial Site

Site Admin

Lab #2 : MySQL Tables

with 36 comments

Lab #2: MySQL Assignment

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.

  1. [0 points] Tutorial on creating the SYSTEM_USER_LAB table and the SYSTEM_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.

After making these changes for one table, the 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 ↓

  1. You connect to the VMware instance as the student user. Then, click on Activities to launch the menu. Enter gedit in the search box and click the Return key to launch the gedit program.

CIT225_Form01

  1. Having launched the gedit application, you should see the following image. Click the Open button to find and open the create_mysql_store_ri.sql file.

CIT225_Form02

  1. Having opened the File Manager dialog, you can now click on the Home option in the Places list on the left.

CIT225_Form03

  1. Double click on the Data folder in the Name list.

CIT225_Form04

  1. 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.

CIT225_Form05

  1. 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.

CIT225_Form06

  1. 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.

CIT225_Form07MySQL

  1. 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 the gedit application.

CIT225_Form08MySQL

  1. You should see the following create_mysql_store_ri.sql script in the gedit application.

CIT225_Form09MySQL

  1. After opening the create_mysql_store_ri.sql script, click on the oracle button in the file path above the panes. Then, click on the lib folder. You will see the apply_mysql_lab2.sql file. Click the Open button to open the apply_mysql_lab2.sql file in the gedit file.

CIT225_Form10MySQL

  1. You should see the following apply_mysql_lab2.sql script in the gedit application. You should only edit below the last line, which means don’t change anything above the last comment line.

CIT225_Form11MySQL

  1. 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 to SYSTEM_USER_LAB.
    • Change all lowercase references to system_user to system_user_lab.
    • Change all constraint names from nn_system_user_x to nn_system_user_lab_x.
    • Change all lowercase references to fk_system_user_x to fk_system_user_lab_x.
  1. 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.

  1. [2 points] Create the SYSTEM_USER_LAB table described by the following description. The table should also have an auto-incrementing sequence on the SYSTEM_USER_ID column. The table should have an auto incrementing sequence that should start with a value of 1001:

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 

  1. [2 points] Create the COMMON_LOOKUP_LAB table described by the following. It should have an auto incrementing sequence with a starting value of 1001:

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)
  1. [2 points] Create the MEMBER_LAB table described by the following. It should have an auto incrementing sequence with a starting value of 1001:

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)

  1. [2 points] Create the CONTACT_LAB table described by the following. It should have an auto incrementing sequence with a starting value of 1001:

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)

  1. [2 points] Create the ADDRESS_LAB table described by the following. It should have an auto incrementing sequence with a starting value of 1001:

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)

  1. [2 points] Create the STREET_ADDRESS_LAB table described by the following. It should have an auto incrementing sequence with a starting value of 1001:

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)

  1. [2 points] Create the TELEPHONE_LAB table described by the following. It should have an auto incrementing sequence with a starting value of 1001:

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)
  1. [2 points] Create the RENTAL_LAB table described by the following. It should have an auto incrementing sequence with a starting value of 1001:

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)

  1. [2 points] Create the ITEM_LAB table described by the following. It should have an auto incrementing sequence with a starting value of 1001:

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)

  1. [2 points] Create the RENTAL_ITEM_LAB table described by the following. It should have an auto incrementing sequence with a starting value of 1001:

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)

  1. [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)

Written by michaelmclaughlin

November 21st, 2014 at 1:01 am

Posted in

36 Responses to 'Lab #2 : MySQL Tables'

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

  1. Make sure to fix the path to the cleanup and create_store scripts.

    Jeremy

    14 Jan 15 at 2:00 pm

  2. In Step 1 you have us create the table system_user_lab with the field SYSTEM_USER_TYPE as NOT 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

  3. When we are asked to add the constraints to the system_user_lab table for the system_user_group_id and the system_user_type, the instructions ask us to reference the system_user_lab table and the system_user_id column, but the validation says it references the common_lookup_lab table.

    mar09013

    15 Jan 15 at 10:41 pm

  4. Can you qualify which validation script is wrong by pasting it into a comment.

    michaelmclaughlin

    16 Jan 15 at 2:41 am

  5. Jeremy, It’s fixed. Thanks.

    michaelmclaughlin

    16 Jan 15 at 2:52 am

  6. 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

  7. 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

  8. Step 4

    Currently

    'last name'
'first name'
'middle name'

    The other tables have been

    'first name'
'middle name'
'last name'

    Joseph Tracy

    16 Jan 15 at 3:52 pm

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. Joseph, You’re right. It’s a typo and now it’s fixed.

    michaelmclaughlin

    21 Jan 15 at 1:06 am

  15. Joseph, It’s a great catch, and it’s now fixed.

    michaelmclaughlin

    21 Jan 15 at 1:10 am

  16. Tyler, Great catch. It’s fixed now.

    michaelmclaughlin

    21 Jan 15 at 2:04 am

  17. Step 2 states:

    You should then create a COMMON_LOOKUP_LAB_U1 unique index on the COMMON_LOOKUP table, and a COMMON_LOOKUP_LAB_N1 non-unique index on the COMMON_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 the N1 index, or specify which column(s) should be in the index.

    Dave Stevenson

    23 Jan 15 at 6:17 pm

  18. In step 4, after creating non unique indexes CONTACT_N1 and CONTACT_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

  19. I believe the constraint names in step 6:

    +--------------------+--------------------+-----------------+
| table_name         | constraint_name    | constraint_type |
+--------------------+--------------------+-----------------+
| street_address_lab | PRIMARY            | PRIMARY KEY     |
| street_address_lab | street_address_fk1 | FOREIGN KEY     |
| street_address_lab | street_address_fk2 | FOREIGN KEY     |
| street_address_lab | street_address_fk3 | FOREIGN KEY     |
+--------------------+--------------------+-----------------+

    should have ‘_lab‘ in them:

    +--------------------+------------------------+-----------------+
| 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     |
+--------------------+------------------------+-----------------+

    Dave Stevenson

    23 Jan 15 at 6:57 pm

  20. 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

  21. Dave, Great catch! You’re right, and I’ve updated the page with the correction.

    michaelmclaughlin

    26 Jan 15 at 2:41 am

  22. step 4. reference for the FK for member_lab_id is incorrect.

    Joseph Tracy

    31 Jan 15 at 12:30 pm

  23. step 9.

    address_lab_id is included in lab 2 item_lab but is not included in lab 4 item_lab and throws an error for missing input.

    Joseph Tracy

    31 Jan 15 at 1:59 pm

  24. 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

  25. 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

  26. 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

  27. Joseph, I believe that the address_lab_id is fixed.

    michaelmclaughlin

    8 Mar 15 at 2:52 am

  28. Joseph, That MEMBER_LAB_ID reference is fixed. Thanks.

    michaelmclaughlin

    8 Mar 15 at 2:58 am

  29. Brett, The Oracle references have been replaced with MySQL references. Thanks!

    michaelmclaughlin

    8 Mar 15 at 3:01 am

  30. Brett,

    The lab design has two purposes and yes using the _ri and _ri2 scripts are a critical components for accomplishing certain goals:

    • First, they ensure that the scripts the students develop are incompatible with labs five forward, that’s why they use the create_mysql_store_ri.sql script during this lab and the create_mysql_store_ri2.sql script.
    • Second, they present differences that compel re-writes of the lab and inability to reuse any of the 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 the seed_mysql_store_ri2.sql script that the student can leverage in Lab #6.

    michaelmclaughlin

    8 Mar 15 at 12:21 pm

  31. Jeremy, Great catch! I’ve fixed it. Thanks!

    michaelmclaughlin

    8 Mar 15 at 11:37 pm

  32. 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

  33. Dave, Below the validation of foreign key constraints, you’ll find the validation script for CONTACT_N1 and CONTACT_N2 non-unique constraints. Post another comment if you still see a problem.

    michaelmclaughlin

    9 Mar 15 at 12:24 am

  34. Dave, Great catch! I’ve added the material for the COMMON_LOOKUP_LAB_N1 index.

    michaelmclaughlin

    9 Mar 15 at 12:46 am

  35. 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

  36. 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

Leave a Reply