Database Tutorial

Objectives

There are eight objectives for the third week. The best way to accomplish these objectives is two fold. First, you need to read the articles and web pages referenced through links in the summary page. This also includes any subordinate pages referenced through links. Second, you need to do the work and use the tools to copy existing scripts, edit scripts, and test scripts.

• Learn how to describe data models.
• Learn how to normalize data models.
• Learn how to define natural keys.
• Learn how to to define foreign keys.
• Learn how to write scalar subqueries.
• Learn how to use automatic numbering.
• Learn how to write static INSERT statements.
• Learn how to write dynamic INSERT statements with scalar subqueries.

Reading Assignments

You should prepare by reading the following articles. There will be many new terms. You may want to check the Glossary in the Oracle Database 12c PL/SQL Programming book to understand most of those new terms. After your preparation, you should work through the examples provided for you in the reading.

• Prepare (Reading):
• Explore the keyword definitions.
• What is normalization.
• What is first normal form.
• What is second normal form.
• What is third normal form.
• What is a common lookup table.
• What is “who-audit” security.
• Teach One Another (Lab):
  • Copy, edit, and test insert test script.
  • Change table constraints for tables containing data.
• Ponder and Prove (Internalization):
  • Compare and contrast the INSERT statement variations.
  • Examine and understand the INSERT statement variations.

Reference Materials

After you’ve read the article and tutorials, you should experiment with the examples provided in the article or tutorials. The second week’s lab starts with copying, editing, and testing unit and integration testing scripts:

• Reading
  • Definitions
  • Data Modeling
    • 1NF Modeling
    • 2NF Modeling
    • 3NF Modeling
  • Common Lookup
  • Who Audit Security
• Articles
  • INSERT
  • Scalar Subqueries
  • Automatic Numbering
• Tutorials
  • Independent Insert
  • Dependent Insert
• Lab
  • Description
  • Help Section
  • Instructions

The lab relies on your ability to navigate in the SQL*Plus command-line interface and the ability to edit a script file.

You should read through the Quiz 3: Review Notes to ensure you understand Week 3 material before moving on to Week 4. Please feel free to identify and comment on anything that would benefit form different wording or supplemental ideas.