Database Tutorial

Objectives

There are eight objectives for the second 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 create, drop, and use tables.
• Learn how to create, drop, and use constraints.
• Learn how to distinguish between column and table constraints.
• Learn how to add and remove constraints from tables.
• Learn how to work with a unique namespace for tables and constraints.
• Learn how to write unit test script files.
• Learn how to write an integration test script file.
• Learn how to debug test and integration script files.

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):
• What are records (also called structures, struct, or compound data).
• What are tables.
• What are Data Definition Language (DDL) commands.
• What are constraints.
• Teach One Another (Lab):
  • Copy, edit, and test ten unit test scripts.
  • Query to verify table creations.
  • Query to verify sequence creations.
• Ponder and Prove (Internalization):
  • Compare and contrast the structures of the ten tables.
  • Examine and understand the dependencies between tables and constraints.

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
  • Records
  • Tables
  • Namespaces
  • DDL Commands
  • Constraints
• Articles
  • DDL Commands
    • CREATE Statement
    • ALTER Statement
    • DROP Statement
    • RENAME Statement
    • TRUNCATE Statement
    • COMMENT Statement
  • Constraints
    • NOT NULL
    • CHECK
    • UNIQUE
    • PRIMARY KEY
    • FOREIGN KEY
• Tutorials
  • Unconstrained tables
  • Constrained tables
  • Unit test script
• 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 2: Review Notes to ensure you understand Week 2 material before moving on to Week 3. Please feel free to identify and comment on anything that would benefit form different wording or supplemental ideas.