Database Tutorial

Lab #3: Oracle Assignment Underdevelopment

Objectives

The lab teaches you how to work with mapping external denormalized CSV (comma-separated value) files to relational tables. It teaches you:

• The concept of mapping: How you map natural key column values in a denormalized extract file (CSV) with the natural key column values in normalized tables.
• The concept translation: How you map natural key column values in a denormalized extract file (CSV) with the natural key column values of a COMMON_LOOKUP table to capture the primary key column value.

Business Scenario

It is possible to build a perfect application but too often we fall short and some forms let incorrect data into our data model. As an application DBA or developer, you need to fix this bad data.

You typically fix the data by extracting it into a denomalized CSV file. You typically remove many but not necessary all surrogate primary and foreign key values from the CSV file because you always extract the natural key. The natural key contains those column values that uniquely describe every row in your normalized tables.

You deliver the denormalized CSV file to a function expert in finance or accounting, and they fix the data typically in Microsoft Excel. After the functional expert fixes the file, they return the file to you and you must upload it back into the database and renormalize the data.

You have received the file, and built an external table to read the file content into the database. Now, you must figure out how to map or translate values from the denormalized CSV file into the normalized tables. It is possible that the functional expert has added new rows or modified existing rows. You must write logic that lets you add new rows (using an INSERT or MERGE statement), and lets you change existing rows (using and UPDATE or MERGE statement).

Help Section

The following is an set of sample queries that show you how to return