Database Tutorial

Objectives

There are four objectives for the ninth 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 and use externally managed tables.
• Learn how to use stored procedures to solve complex problems.
• Learn how to create unique indexes across multiple column natural key columns.
• Learn how to query data from externally managed tables.

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):
• Understand the nature and structure of a thick database.
• Understand the utility of internally deployed stored procedures.
• Understand externally managed tables work.
• Understand the purpose and use of indexes.
• Understand how to work with the DISTINCT operator.
• Understand how to work with Oracle’s DATE data type.
• Teach One Another (Lab):
  • Create an externally managed table.
  • Deploy and use stored procedures to solve complex problems.
  • Validate the integrity of complex data inside internally and externally managed tables.
• Ponder and Prove (Internalization):
  • Compare and contrast external table use cases.
  • Compare and contrast stored procedures in thick databases.
  • Examine and understand how to use externally managed tables in business solutions.

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
  • Thick Databases
  • Stored Programs
• Articles
  • External Tables
  • Indexes
• Tutorials
  • Distinct Operator
  • Date Data Types
• 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. Your student images have had the necessary files placed in the proper location to support your lab assignment.

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