Introduction to Sample Schemas

1 Introduction to Sample Schemas

The sample database schemas can be used for product documentation, courseware, software development, and application demos.

About the Sample Schemas

The sample database schemas provide a common platform for examples in each release of Oracle Database. The sample schemas are a set of interlinked database schemas. This set provides the following approaches to complexity:

Schema Human Resources (hr) is useful for introducing basic topics. An extension to this schema supports Oracle Internet Directory demos.
Schema Customer Orders (co) is a modern schema useful for demos of e-commerce transactions. It allows the storage of semi-structured data using JSON.
Schema Sales History (sh) is designed to allow for demos with large amounts of data. An extension to this schema provides support for advanced analytic processing.

The following schemas are no longer updated, but are still available:

Schema Order Entry (oe) is useful for dealing with matters of intermediate complexity. Many data types are available in this schema, including nonscalar data types.
Schema Online Catalog (oc) is a collection of object-relational database objects built inside the oe schema.
Schema Product Media (pm) is dedicated to print media data types.

Note:

The Business Intelligence (bi), Information Exchange (ix), and Shipping (qs) schemas are no longer available.

Design Principles for Sample Schemas

The sample database schemas were created with the following design principles in mind:

Simplicity and ease of use. The hr schema is intentionally simple. It provides a graduated path from simple to intermediate levels of database use.
Relevance for typical users. The base schemas and their extensions bring to the foreground the functionality that customers typically use. Only the most commonly used database objects are built automatically in the schemas. The entire set of schemas provides a foundation upon which you can expand to illustrate additional functionality.
Extensibility. The sample schemas provide a logical and physical foundation for adding objects to demonstrate functionality beyond the fundamental scope.
Relevance. The sample schemas are designed to be applicable to e-business and other significant industry trends (for example, XML). When this goal conflicts with the goal of simplicity, schema extensions are used to showcase the trends in focus.

Customer Benefits of Sample Schemas

Benefits provided by the sample schemas include the following:

Continuity of context. When encountering the same set of tables everywhere, users, students, and developers can spend less time becoming familiar with the schema and more time understanding or explaining the technical concepts.
Usability. Customers can use these schemas in the seed database to run examples that are shown in Oracle Database documentation and training materials. This first-hand access to examples facilitates both conceptual understanding and application development.
Quality. Through central maintenance and testing of both the creation scripts that build the sample schemas and the examples that run against the schemas, the quality of Oracle Database documentation and training materials is enhanced.

Overview of the Sample Schemas

The Oracle Database sample schemas are based on a fictitious sample company that sells goods through various channels. The company operates worldwide to fill orders for products. It has several divisions, each of which is represented by a sample database schema.

HR Sample Schema

The Human Resources division tracks information about the company employees and facilities. In the Human Resource (hr) records, each employee has an identification number, e-mail address, job identification code, salary, and manager. Some employees earn commissions in addition to their salary.

The company also tracks information about jobs within the organization. Each job has an identification code, job title, and a minimum and maximum salary range for the job. Some employees have been with the company for a long time and have held different positions within the company. When an employee resigns, the duration the employee was working, the job identification number, and the department are recorded.

The sample company is regionally diverse, so it tracks the locations of its warehouses and departments. Each employee is assigned to a department, and each department is identified either by a unique department number or a short name. Each department is associated with one location, and each location has a full address that includes the street name, postal code, city, state or province, and the country code.

In places where the departments and warehouses are located, the company records details such as the country name, currency symbol, currency name, and the region where the country is located geographically.

CO Sample Schema

The Customer Orders division tracks customer, product, store, and order data. The Customer Orders (co) schema records the details of transactions made by a retail application.

The co schema highlights features such as JSON support.

The company sells a variety of products, which are maintained in the products table. Each product has a unique identification number, name, price, details stored in a JSON object and product image details.

The orders placed by the customer are tracked in the orders table using the order identification number, date and time when the order was placed, customer details, order status and the store information.

The details of the products in a particular order are also tracked in the order_items table using the order identification number. Details of the products, price at the time of purchase, quantity and shipment are recorded.

The information of a customer placing an order is tracked in the customers table. Each customer has an identification number, name, and email address that is used for communication of the orders.

The customers can purchase the products in stores or online through the company's website. The information for all of the stores and their corresponding physical and virtual addresses is tracked in the stores table. The store information is also recorded in the order details.

The shipment details of the orders placed such as the delivery address, customer details, store information and the shipment status are stored in the shipments table.

An inventory table stores the details of each product such as the quantity available at each store.

SH Sample Schema

The Sales division tracks business statistics to facilitate business decisions. The sample company does a high volume of business, so it runs business statistics reports to aid in decision making. 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. These reports include annual, quarterly, monthly, and weekly sales figures by product. These reports are stored by using the schema Sales History (sh).

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

OE Sample Schema

Note:

The oe schema is no longer updated, but is still available.

The company sells several products, such as computer hardware and software, music, clothing, and tools. The company maintains information about these products, such as product identification numbers, the category into which the product falls, order entry (oe), the weight group (for shipping purposes), the warranty period if applicable, the supplier, the availability status of the product, a list price, a minimum price at which a product will be sold, and a URL address 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 languages.

The company maintains warehouses in several locations to fulfill customer needs. Each warehouse has a warehouse identification number, name, facility description, and location identification number.

Customer information is also tracked. Each customer has an identification number. Customer records include customer name, street name, city or province, country, phone numbers (up to five phone numbers for each customer), and postal code. Some customers place orders through the Internet, so e-mail addresses are also recorded. Because of language differences among customers, the company records the native language and territory of each customer.

The company places a credit limit on its customers, to limit the amount of products they can purchase at one time. Some customers have an account manager, and this information is also recorded.

When a customer places an order, the company tracks the date of the order, how the order was placed, the current status of the order, shipping mode, total amount of the order, and the sales representative who helped place the order. The sales representative may or may not be the same person as the account manager for a customer. If an order is placed over the Internet, no sales representative is recorded. In addition to order information, the company also tracks the number of items ordered, the unit price, and the products ordered.

The oe schema also contains XML purchase-order documents. You can access these documents by using SQL to query the purchaseorder table, or by querying the public views RESOURCE_VIEW and PATH_VIEW.

OC Sample Schema

Note:

The oe schema is no longer updated, but is still available.

The Online Catalog (oc) subschema of database schema oe addresses an online catalog merchandising scenario. The same customers and products are used in oc as in schema oe proper, but subschema oc organizes the products into a hierarchy of parent categories and subcategories. This hierarchy corresponds to the arrangement on an e-commerce portal site, where users navigate to specific products by drilling down through increasingly specialized categories of products.

PM Sample Schema

Note:

The pm schema is no longer updated, but is still available.

The company stores print information about its products in a database. The Product Media (pm) schema is used to store such information. Examples of such information are:

Press release texts
Print media advertisements
Other promotional texts and translations