Release 2 (9.2)
Part Number A96539-01
 Home |
 Book List |
 Master Index |
 Feedback |
 Contents |
| Oracle9i Sample Schemas Release 2 (9.2) Part Number A96539-01 |
|
The Oracle9i Sample Schemas are based on a fictitious company that sells goods through various channels. This chapter describes the fictitious company and contains these sections:
The sample company portrayed by the Oracle9i Sample Schemas operates worldwide to fill orders for several different products. The company has several divisions:
Each of these divisions is represented by a schema.
In the company's human resource records, each employee has a unique identification number, email address, job identification number, salary, and manager. Some employees earn a commission in addition to their salary, which is also tracked.
The company also tracks information about jobs within the organization. Each job has an identification number, 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 jobs within the company. When an employee switches jobs, the company records the start date and end date of the former job, the job identification number, and the department.
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. Each department 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 where it has facilities, the company records the country name, currency symbol, currency name and the region where the county resides geographically.
The company sells several categories of products, including computer hardware and software, music, clothing, and tools. The company maintains product information that includes product identification numbers, 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 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 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 through the Internet, so email 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 they can purchase at one time. Some of customers have account managers, which we monitor. We keep track of a customer's phone numbers. In this day, we never know how many phone numbers a customer might have, but we try to keep track of all of them. Because of the language differences of our customers, we identify the language and territory of each customer.
When a customer places an order, the company tracks the date of the order, the mode of the order, status, shipping mode, total amount of the order, and the sales representative who helped place the order. This may be the same individual as the account manager for a customer, it may be different, or, in the case of an order over the Internet, the sales representative is not recorded. In addition to the order information, we also track the number of items ordered, the unit price, and the products ordered.
For each country in which it does business, the company records the country name, currency symbol, currency name, and the region where the county resides geographically. This data is useful customers living in different geographic regions around the world.
The OC subschema of the OE schema addresses an online catalog merchandising scenario. The same customers and products are used as in the OE schema proper, but the OC subschema organizes the categories that the OE products belong to into a hierarchy of parent categories and subcategories. This hierarchy corresponds to the arrangement on an e-commerce portal site where the user navigates to specific products by drilling down through ever more specialized categories of products.
The company stores multimedia and print information about its products in the database. Examples of such information are:
The sample company has decided to test the use of messaging to manage its proposed B2B applications. The plan calls for a small test that will allow 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.
Eventually, the company intends to expand beyond its current in-house distribution system to a system that will allow other businesses to provide the shipping. Therefore, 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 resolution of any discrepancies with the order.
For the purpose of this test application, the company utilizes a single database server and a single application server. The application provides a mechanism for examining the XML messages as well as looking at the queues. To demonstrate connectivity from outside the firewall, both the generation of a new order and customer service reporting are performed using queues. The new order application directly enqueues a queue, while the customer service queries require XML messaging to dequeue a queue.
The users associated with this application are:
QS (Queue Shipping)QS_ES (Eastern Shipping)QS_WS (Western Shipping)QS_OS (Overseas Shipping)QS_CB (Customer Billing)QS_CS (Customer Service)QS_ADM (Administration), andQS_CBADM (Customer Billing Administration)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 non-volatile. 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 and annual, quarterly, monthly, and weekly sales figures by product.
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.
|
 Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|