Thursday, January 3, 2008

Oracle Database Sample Schemas

I have written several articles related to the Oracle database and have presented several presentations on the Oracle database. In many of these situations, I like to use the Oracle database-provided default learning schema (such as scott/tiger in the regular database and hr/hr in the Express Edition database). The reason that I prefer using these (when they are sufficient to demonstrate the point that I am making) is that their use improves the chances of those reading my article or listening to my presentation being able to more quickly understand what I am relaying. If the reader or audience member does not need to learn a new database schema, I can take advantage of his or her experience with a well-known schema to focus on learning the concept I am trying to convey.

In Oracle's Sample Schemas: Saying Goodbye to Scott, Steve Callan provides a nice overview of the new schemas that Oracle has introduced for learning and teaching about its database products.

Oracle has released several versions (at least since 9i) of the document Oracle Database Sample Schemas (11g Release 1 [11.1] PDF). The links in the previous sentence are to the 11g version of the document. It is useful for understanding why Oracle has introduced these sample schemas in place of the SCOTT sample schema.

An obvious question is, "Why does Oracle provide so many sample schemas?" One of the key reasons seems to be to tailor sample schemas to different functionality provided by the database. By having multiple schemas, each schema can be relatively straightforward and focus on certain Oracle database features. If all the features were demonstrated in a single schema, it would be a much more complex schema and therefore more difficult to learn for someone new to the Oracle database. The Overview section of the Oracle Database Sample Schemas document explains in concise terms why the SCOTT schema is inadequate for the needs now met by the new schemas. The section "Oracle Database Sample Schemas Design Principles" in the Overview explains that the sample schemas are designed for four end purposes (simplicity, typical user revelance, software trend revelance, and extensibility) and explains how the schemas achieve these purposes.

The Oracle Database Sample Schemas document linked to above discusses the focus of each of the database's sample schemas. The Rationale section of the document demonstrates how several sample schemas together constitute a fictitious company's database needs and briefly summarizes how each sample schema accomplishes this.

The Oracle Database Sample Schemas document opens with (section 1.1) a brief sentence describing the use of each of the sample schemas in the "About Sample Schemas" section. For most introductory articles and presentations, the HR schema is sufficient for my needs. This makes sense when we read in the Oracle Database Sample Schemas documentation that HR schema is intended for basic topics. For intermediate topics, the documentation suggests that the OE schema may be more appropriate. The documentation then goes on to outline specific types of Oracle functionality that is best learned and taught in each schema.

If your sense of nostalgia makes you miss the SCOTT schema, don't fear. It is still provided with the Oracle database. It simply is not emphasized any longer and its only discussion in the Oracle Database Sample Schemas document is to say that it is no longer adequate for demonstrating Oracle's basic features and has been replaced by the sample schemas covered in the document.

A very brief summary of the history of the SCOTT schema can be found at this Burleson Consulting page, at this ORA FAQ page, and on this Oracle Wiki page.

No comments: