Database Tutorial

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.

• Lab
  • Description
  • Help Section
  • Instructions

Objectives

The lab is designed to teach you how to work with the INSERT and UPDATE statements, disable and enable database constraints, and use scalar subqueries. The anonymous block PL/SQL program should achieve these objectives:

• Learn how to write a positional and named INSERT statement.
• Learn how to disable and enable constraints.
• Learn how to use the .NEXTVAL pseudo column in an INSERT statement.
• Learn how to write a scalar query.
• Learn how to use a subquery in the SET clause of an UPDATE statement.
• Learn how to write an INSERT statement with embedded scalar subqueries.

Business Scenario

Building data-centric application software often requires that you pre-seed data in certain database tables. The process of pre-seeding involves inserting rows of data. Sometimes the Entity Relationship Diagram (ERD) can create conflicts, which require you to temporarily disable constraints to insert data.

The lab is designed to teach you how to write INSERT statements. The INSERT statements use:

• Literal values
• Pseudo columns for sequence values
• Subqueries that retrieve surrogate primary key values from tables by using the natural keys of the tables

You will disable foreign key constraints and remove not null constraints to pre-seed some of the tables with data. After inserting the new data, you will add re-enable foreign key constraints and re-add not null constraints. You should use the lab1/preseed/preseed_oracle_store.sql script as a reference model for SQL syntax.