Reviewing the Sample Schemas

Module Objectives

Purpose

The sample schemas that are delivered with Oracle9i are used throughout the Oracle9i by Example series. In this module, you will learn how these schemas are set up.

Objectives

After completing this module, you should be able to:

	List the schemas and what their purpose is
	Describe the tables in each schema

Prerequisites

Before starting this module, you should have completed the following modules:

	Preinstallation Tasks
	Installing the Oracle9i Database

Reference Material

The following is useful reference material if you want additional information about the topics in this module:

Documentation: Sample Schemas

Overview

The sample company illustrated by the Oracle9i sample schemas operates worldwide to fill orders for several different products.

The company has several divisions, each represented by a schema:

The Human Resources (HR) Schema

In the company’s human resource records, each employee has a unique identification number, e-mail address, and job identification number, as well as a salary and a manager. Some employees earn commissions in addition to their salaries, and these commissions are also tracked. When an employee switches jobs, the company records the start date and end date of the former job, the job identification number, and department.

The company also tracks information about jobs within the organization. Each job has an identification number, job title, and a minimum and maximum salary.

The sample company is regionally diverse, so it tracks the locations of not only its warehouses but also of its departments. Each of the company’s employees is assigned to a department. Each department is identified by a unique department code and a short name, and is associated with one location. Each location has a full address that includes the street address; postal code; city, state, or province; and country code.

For each country where it has facilities, the company records the country name, currency symbol, currency name, and geographical region.

Order Entry (OE) Schema

The company sells several categories of products, including computer hardware and software, music, clothing, and tools. The company maintains product information that includes a product identification number, the category into which the product falls, the weight group (for shipping purposes), the warranty period if applicable, the supplier, the status of the product, a list price, a minimum price at which a product can be sold, and a URL for manufacturer information. Inventory information is also recorded for all products, including the warehouse where the product is available and the quantity on hand. Because products are sold worldwide, the company maintains the names of the products and their descriptions in several different languages.

The company maintains warehouses in several locations to facilitate filling customer orders. Each warehouse has a warehouse identification number, name, and location identification number.

Customer information is tracked in some detail. Each customer is assigned an identification number. Customer records include name, street address, city or province, country, phone numbers (up to five phone numbers for each customer), and postal code. Some customers order over the Internet, so e-mail addresses are also recorded. Because of language differences among customers, the company records the National Language Support (NLS) language and territory of each customer.

The company places a credit limit on its customers to limit the amount they can purchase at one time. The company also tracks whether a given customer has an account manager, and, if so, who the manager is.

When a customer places an order, the company tracks the date of the order, mode of the order, status, shipping mode, total amount of the order, and sales representative who helped place the order. (This person can be the account manager for a customer, or someone else; in the case of an order over the Internet, the sales representative is not recorded.) In addition to the order information, the company also tracks the products ordered, number of items ordered, and unit price.

For each country in which it does business, the company records the country name, currency symbol, currency name, and geographical region.

Product Media (PM) Schema

The company stores multimedia and print information about its products in the database. Examples of such information include:

	Promotional audio and video clips
	Product images and thumbnail images for Web publishing
	Press releases
	Print media advertisements
	Other promotional texts
	Translations

Schema Diagrams for the OE and PM Schemas

Shipping (QS) Schema

The QS schema is designed to handle messaging between shipping centers. In this case, the sample company has decided to test the use of messaging to manage its proposed business-to-business applications. The plan calls for a small test that allows a user from outside the firewall to place an order and track its status. The order needs to be booked into the main system. Then, depending on the location of the customer, the order is routed to the nearest region for shipping.

In addition, the schema is set up so that the company can expand beyond its current in-house distribution system to a system that allows other businesses to provide the shipping. Because of this planned expansion, the messages sent between the businesses must also travel over HTTP and be in a self-contained format. XML is the perfect format for the message, and both the Advanced Queueing servlet and Oracle Internet Directory provide the appropriate routing between the queues.

After the orders are either shipped or back-ordered, a message needs to be sent back to appropriate employees to inform them of the order's status and to initiate the billing cycle. It is critical that the message be delivered only once and that there be a system for tracking and reviewing messages to facilitate the resolution of any discrepancies with the order.

Schema Diagram for the QS Schema

Sales History (SH) Schema

The sample company does a high volume of business, so it runs business statistics reports to aid in decision support. Many of these reports are time-based and nonvolatile. That is, they analyze past data trends. The company loads data into its data warehouse regularly to gather statistics for these reports. Some examples of these reports include annual, quarterly, monthly, and weekly sales figures by product.

The company also runs reports on the distribution channels through which its sales are delivered. When the company runs special promotions on its products, it analyzes the impact of these promotions on sales. It also analyzes sales by geographical area.

Schema Diagram for the SH Schema

Copyright © 2002 Oracle Corporation. All Rights Reserved.

Close Window