Lab #4 : Inserting Records
This lab works on developing the students understanding of the syntax to insert data into tables that are constrained by FOREIGN KEY database-level constraints. The lab also tests their ability to solve the best practice for controlling key insertions that support subsequent join operations.
Objectives ↓
These are the lab objectives. They provide the description of the database tables that you will work with to insert data. You have the table names, column names, and data types for each table, and the you should have a script to create these tables in Oracle and MySQL databases. The MySQL database script should create all tables by using the ENGINE=InnoDB clause at the end of the CREATE TABLE statement.
Group Objectives
Group assignments are cooperative situations where you should learn from one another. Each team member should engage and type solution components individually. This tends to maximize the student learning opportunity. You should avoid the “expert and followers” paradigm, and become cooperative and interactive problem solvers. The “expert and followers” paradigm is where one person in a group does the work and learns, while others look over the shoulder at the work without truly understanding it. Team members become interactive problem solvers when they collaborate and work together on solving problems.
If something comes up, like an illness or excused absence, you may share files. The caution here is that the student receiving the work must master the concepts in the material before submitting the work to the instructor. The instructor may interview the student to determine their level of mastery and post group credit that may differ from other team members who performed the work.
- Learn how to use the
INSERTstatement in an Oracle database, which is a Data Manipulation Language (DML) command. - Learn how to insert data that meets the limitations of
FOREIGN KEYconstraints in an Oracle database. - Learn how to work with patterns of automatic numbering and efficient use of sequence values between the table holding the
PRIMARY KEYand the table holding theFOREIGN KEYcolumns for an Oracle database.
Individual Objectives
Individual assignments are situations where you apply the generalized concepts acquired in the group exercise, and transfer your skills to another database engine. You may ask questions but you should only receive general concepts, principles, or directions to published materials (such as the product documentation found in the resources). Likewise, those are the types of answers you should provide when asked questions by your peers. You should not share files in this part of the lab.
- Learn how to use the
INSERTstatement in a MySQL database, which is a Data Manipulation Language (DML) command. - Learn how to insert data that meets the limitations of
FOREIGN KEYconstraints in an MySQL database. - Learn how to work with patterns of automatic numbering and efficient use of sequence values between the table holding the
PRIMARY KEYand the table holding theFOREIGN KEYcolumns for an MySQL database.
Resources ↓
These are the lab resources.
Deliverables ↓
The table definitions are in the lab deliverables.
| Table Name: MEMBER | |||||
|---|---|---|---|---|---|
| Column Name | Constraint | Data Type |
Physical Size |
||
| Type | Reference Table | Reference Column | |||
| MEMBER_ID | PRIMARY KEY | Integer | Maximum | ||
| MEMBER_TYPE | FOREIGN KEY | COMMON_LOOKUP | COMMON_LOOKUP_ID | Integer | Maximum |
| ACCOUNT_NUMBER | String | 10 | |||
| CREDIT_CARD_NUMBER | String | 19 | |||
| CREDIT_CARD_TYPE | FOREIGN KEY | COMMON_LOOKUP | COMMON_LOOKUP_ID | Integer | Maximum |
| CREATED_BY | FOREIGN KEY | SYSTEM_USER | SYSTEM_USER_ID | Integer | Maximum |
| CREATION_DATE | NOT NULL | DATE | Date | ||
| LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER | SYSTEM_USER_ID | Integer | Maximum |
| LAST_UPDATE_DATE | NOT NULL | DATE | Date | ||
| Table Name: CONTACT | |||||
|---|---|---|---|---|---|
| Column Name | Constraint | Data Type |
Physical Size |
||
| Type | Reference Table | Reference Column | |||
| CONTACT_ID | PRIMARY KEY | Integer | Maximum | ||
| MEMBER_ID | FOREIGN KEY | MEMBER | MEMBER_ID | Integer | Maximum |
| CONTACT_TYPE | FOREIGN KEY | COMMON_LOOKUP | COMMON_LOOKUP_ID | Integer | Maximum |
| FIRST_NAME | String | 20 | |||
| MIDDLE_NAME | String | 20 | |||
| LAST_NAME | String | 20 | |||
| CREATED_BY | FOREIGN KEY | SYSTEM_USER | SYSTEM_USER_ID | Integer | Maximum |
| CREATION_DATE | NOT NULL | DATE | Date | ||
| LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER | SYSTEM_USER_ID | Integer | Maximum |
| LAST_UPDATE_DATE | NOT NULL | DATE | Date | ||
| Table Name: ADDRESS | |||||
|---|---|---|---|---|---|
| Column Name | Constraint | Data Type |
Physical Size |
||
| Type | Reference Table | Reference Column | |||
| ADDRESS_ID | PRIMARY KEY | Integer | Maximum | ||
| CONTACT_ID | FOREIGN KEY | CONTACT | CONTACT_ID | Integer | Maximum |
| ADDRESS_TYPE | FOREIGN KEY | COMMON_LOOKUP | COMMON_LOOKUP_ID | Integer | Maximum |
| CITY | NOT NULL | String | 30 | ||
| STATE_PROVINCE | NOT NULL | String | 30 | ||
| POSTAL_CODE | String | 20 | |||
| CREATED_BY | FOREIGN KEY | SYSTEM_USER | SYSTEM_USER_ID | Integer | Maximum |
| CREATION_DATE | NOT NULL | DATE | Date | ||
| LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER | SYSTEM_USER_ID | Integer | Maximum |
| LAST_UPDATE_DATE | NOT NULL | DATE | Date | ||
| Table Name: STREET_ADDRESS | |||||
|---|---|---|---|---|---|
| Column Name | Constraint | Data Type |
Physical Size |
||
| Type | Reference Table | Reference Column | |||
| STREET_ADDRESS_ID | PRIMARY KEY | Integer | Maximum | ||
| ADDRESS_ID | FOREIGN KEY | ADDRESS | ADDRESS_ID | Integer | Maximum |
| STREET_ADDRESS | NOT NULL | String | 30 | ||
| CREATED_BY | FOREIGN KEY | SYSTEM_USER | SYSTEM_USER_ID | Integer | Maximum |
| CREATION_DATE | NOT NULL | DATE | Date | ||
| LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER | SYSTEM_USER_ID | Integer | Maximum |
| LAST_UPDATE_DATE | NOT NULL | DATE | Date | ||
The TELEPHONE table contains an ADDRESS_ID column without a NOT NULL constraint. This isn’t an omission. The business rule in this type of case is that you may have a telephone number or an address for customers. That means a row in the TELEPHONE table may not have a relationship immediately to the ADDRESS table. The absence of the constraint makes the column optional, which means it has a 0..1 cardinality.
| Table Name: TELEPHONE | |||||
|---|---|---|---|---|---|
| Column Name | Constraint | Data Type |
Physical Size |
||
| Type | Reference Table | Reference Column | |||
| TELEPHONE_ID | PRIMARY KEY | Integer | Maximum | ||
| CONTACT_ID | FOREIGN KEY | CONTACT | CONTACT_ID | Integer | Maximum |
| ADDRESS_ID | FOREIGN KEY | ADDRESS | ADDRESS_ID | Integer | Maximum |
| TELEPHONE_TYPE | FOREIGN KEY | COMMON_LOOKUP | COMMON_LOOKUP_ID | Integer | Maximum | COUNTRY_CODE | NOT NULL | String | 3 | AREA_CODE | NOT NULL | String | 6 | TELEPHONE_NUMBER | String | 10 | CREATED_BY | FOREIGN KEY | SYSTEM_USER | SYSTEM_USER_ID | Integer | Maximum | CREATION_DATE | NOT NULL | DATE | Date | LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER | SYSTEM_USER_ID | Integer | Maximum | LAST_UPDATE_DATE | NOT NULL | DATE | Date |
Group Deliverables
Groups should demonstrate to the instructor or lab assistant during class time that they have developed a single re-runnable (found here by clicking on the Re-runnable Referential Integrity Scripts) script that inserts 10 rows in each table for Oracle that inserts data into the above referenced tables that meets the FOREIGN KEY constraints. You may need to modify the structure of a table to add a column to it, or seed additional data in the COMMON_LOOKUP table. Team members should also show the following:
- Every team member should be capable of running and explaining elements of the script running against an Oracle database.
- Every team member can confidently explain the potential signatures that are available when inserting data into tables in an Oracle database.
Individual Deliverables
Individuals should demonstrate to the instructor or lab assistant during class time that you have developed a single re-runnable (found here by clicking on the Re-runnable Referential Integrity Scripts) script that inserts 10 rows in each table for MySQL that inserts data into the above referenced tables. You may need to modify the structure of a table to add a column to it, or seed additional data in the COMMON_LOOKUP table.
- Every team member should be capable of running and explaining elements of the script running against a MySQL database.
- Every team member can confidently explain the potential database constraints that are available when defining tables in a MySQL database.
CREDIT_CARD_TYPE in member ADDRESS_ID in telephone are missing the foreign key information on this page.
William Sawyer
27 Jan 10 at 2:51 pm
Another thing we found: The table definition of MEMBER of this page shows MEMBER with the column MEMBER_TYPE. In the create_store.sql script MEMBER does not have that column.
William Sawyer
27 Jan 10 at 5:09 pm
The
create_store.sqlscript doesn’t contain the column, and they should add the column with anALTER TABLEstatement.michaelmclaughlin
28 Jan 10 at 12:42 am
I’ve fixed the table definition for
CREDIT_CARD_TYPE, and it now contains a foreign key reference. The other isn’t an error but it was missing a buisness rule. I’ve noted it in the page above. Thanks for the great catch.michaelmclaughlin
28 Jan 10 at 12:47 am
In the CONTACT table MEMBER_ID is referenced incorrectly to common_lookup, It should be referenced to the MEMBER table.
William Sawyer
28 Jan 10 at 1:49 pm
Suggestion: middle_name column be renamed to middle_initial in the contact table for clarity.
Blaine Forbush
29 Jan 10 at 12:46 pm
Actually in the provided script on I-Learn middle_name is of CHAR(20) just like first_name and last_name so this is appropriate. He just needs to update the table on this website to reflect that. Also the order in the CONTACT table goes first_name, middle_name, then last_name. He has last_name, first_name, middle_name. So for this lab it is better to actually use the script he provides as reference.
Trent Davis
29 Jan 10 at 7:03 pm
Great catch. The Oracle script differs from the MySQL script. That wasn’t the intention. I’m fixing the scripts so they’re mirrors. In the meantime, I’ve updated this page to reflect the correct model for the future.
michaelmclaughlin
29 Jan 10 at 11:01 pm
“The TELEPHONE table contains an ADDRESS_ID column without a foreign key constraint”
Should read “without a NOT NULL constraint”
Andrew Thimmig
21 Jan 11 at 2:25 pm