Database Tutorial

Course Tutorial Site

Site Admin

Help Section

without comments

Lab #3: Modeling Data

Learn data modeling definitions, theory, and normalization process; learn how to use common lookup tables; who-audit columns to manage row-level security; and the INSERT statement.

Help Section

The following is an set of sample programs and steps that address concepts in the lab. The instructor should review these in classroom or in a Google Hangout with students.

This lab deploys an imperfect attempt at a core database design pattern, which is known as the lookup pattern. The idea requires that you store commonly used terms for drop down or popup lists that let you choose values, like Yes or No.

The following articles cover key aspects of INSERT statements and how you can write a scalar subquery to lookup surrogate key values with the natural key values. You will also learn how to write INSERT statements that use named notation. Named notation is superior to positional notation because table designs may change over time and lead to positional notation INSERT statements putting data where it doesn’t belong.

The data modeling concepts are taken from Chapter 3 of MySQL Workbench: Data Modeling and Development (and provided as a PDF file), the common lookup design pattern is in the online reading, and the who-audit security concepts is in the online reading:

Click the Instructional Material link to see the preparation lesson material.

Instructional Material →

You should check the CREATE statement of a table to discover the foreign key constraints. Alternatively, you can use the following diagnostic script to find the foreign key constraints of the SYSTEM_USER_LAB table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A40 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = 'SYSTEM_USER_LAB'
ORDER BY ucc1.table_name
,        uc.constraint_name;

It should print:

Constraint Name:                           References:
 Table.Column                               Table.Column
------------------------------------------ ----------------------------------------
FK_SYSTEM_USER_LAB_1                       REFERENCES
(SYSTEM_USER_LAB.CREATED_BY)               (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)
 
FK_SYSTEM_USER_LAB_2                       REFERENCES
(SYSTEM_USER_LAB.LAST_UPDATED_BY)          (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)
 
FK_SYSTEM_USER_LAB_3                       REFERENCES
(SYSTEM_USER_LAB.SYSTEM_USER_GROUP_ID)     (COMMON_LOOKUP_LAB.COMMON_LOOKUP_LAB_ID)
 
FK_SYSTEM_USER_LAB_4                       REFERENCES
(SYSTEM_USER_LAB.SYSTEM_USER_TYPE)         (COMMON_LOOKUP_LAB.COMMON_LOOKUP_LAB_ID)

You should check the CREATE statement of a table to discover the not null constraints. Alternatively, you can use the following diagnostic script to find and list the not null constraints of the SYSTEM_USER table:

1
2
3
4
5
6
7
8
9
10
11
12

COLUMN constraint_name   FORMAT A22
COLUMN search_condition  FORMAT A36
COLUMN constraint_type   FORMAT A1
SELECT   uc.constraint_name
,        uc.search_condition
,        uc.constraint_type
FROM     user_constraints uc INNER JOIN user_cons_columns ucc
ON       uc.table_name = ucc.table_name
AND      uc.constraint_name = ucc.constraint_name
WHERE    uc.table_name = UPPER('system_user_lab')
AND      uc.constraint_type = UPPER('c')
ORDER BY uc.constraint_name;

It should print:

CONSTRAINT_NAME        SEARCH_CONDITION                     C
---------------------- ------------------------------------ -
NN_SYSTEM_USER_LAB_1   "SYSTEM_USER_NAME" IS NOT NULL       C
NN_SYSTEM_USER_LAB_2   "SYSTEM_USER_GROUP_ID" IS NOT NULL   C
NN_SYSTEM_USER_LAB_3   "SYSTEM_USER_TYPE" IS NOT NULL       C
NN_SYSTEM_USER_LAB_4   "CREATED_BY" IS NOT NULL             C
NN_SYSTEM_USER_LAB_5   "CREATION_DATE" IS NOT NULL          C
NN_SYSTEM_USER_LAB_6   "LAST_UPDATED_BY" IS NOT NULL        C
NN_SYSTEM_USER_LAB_7   "LAST_UPDATE_DATE" IS NOT NULL       C

You can disable a constraint to work around its limitation with the following syntax: 

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

You can enable a constraint to work around its limitation with the following syntax: 

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

You can remove a not null constraint by redefining the column as null, like this: 

ALTER TABLE table_name
MODIFY (column_name  VARCHAR2(10) NULL);

You can enable a not null constraint by redefining the column as null, like this: 

ALTER TABLE table_name
MODIFY (column_name  VARCHAR2(10) CONSTRAINT constraint_name NOT NULL);

Written by michaelmclaughlin

August 13th, 2018 at 12:05 am

Posted in