Database Tutorial

Course Tutorial Site

Site Admin

Lab #3 : Inserting Data

without comments

Lab #3: Oracle Assignment

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 preseed_oracle_store.sql script (available in the maclochlainn/lib2/preseed folder on Github.com) as a reference model for SQL syntax.

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, the common lookup design pattern is found on ToadWorld.com, and the common lookup table discussion are found on technical blog:

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

The lab has twelve parts. The test cases should insert two rows in the SYSTEM_USER table and twenty rows in the COMMON_LOOKUP table.

Lab Description

[25 points] Click the Lab Instructions link to open the instructions inside the current webpage.

The lab requires that you run four preparation scripts. The cleanup_oracle.sql removes all but the target environment tables and views from the instance. The create_oracle_store2.sql script creates all the tables and sequences. The apply_oracle_lab2.sql script inserts pre-seeded data for subsequent labs. The apply_oracle_lab2.sql should create tables like the create_oracle_store2.sql script with an _LAB suffix added to the table names.

-- Run the prior lab script.
@/home/student/Data/cit225/oracle/lab2/apply_oracle_lab2.sql
@/home/student/Data/cit225/oracle/lib2/preseed/preseed_oracle_store.sql
 
-- insert calls to other SQL script files here ...
 
SPOOL apply_oracle_lab3.txt
 
-- insert your SQL statements here ... 
-- start with the preseed_oracle_store.sql script
-- edit the preseed_oracle_store.sql script to work with the _lab tables. 
 
SPOOL OFF

Click the Lab Instructions link to display the lab steps:

Test Case

Click the Test Case Instructions link to open the test case instructions inside the current webpage.

Written by michaelmclaughlin

December 29th, 2009 at 4:40 pm

Posted in

Leave a Reply