Oracle’s Demo HR Schema

See Oracle: Tips and Tricks for similar articles.

A schema is a collection of tables, views, procedures, indexes and other logical objects. Each schema is owned by a database user and has the same name as that user.

Because there is essentially a 1-to-1 relationship between schemas and users, you can think of a schema as a user and a user as a schema. In setting up for Webucator’s Oracle courses, students create the C##HR user and then ran a couple of scripts to create and populate schema objects for a fictional Human Resources company. This is one of Oracle’s demo schemas. A simple entity relationship diagram showing the relationships between the tables in this schema is shown below:

HR ED
HR Schema

Connection Lines

Note that the connection lines only show the relationships between the tables. They do not point to the specific fields within each table that make the connection. Often you can tell which fields make the connection because they share the same names (e.g., region_id in the regions and countries tables). But this is not always the case. For example, the manager_id field in the departments table is a foreign key connecting to the employee_id field in the employees table.

Tables

Each table in the schema is represented as follows:

ED Table Design

A table is broken into columns (or fields), each of which is of a certain data type. In some cases, the precision or size of that data type (e.g., the number of characters) is specified in parentheses.

Take some time to study the relationships in the diagram. Note that the connection lines show one-to-many relationships with the many side ending with a crow’s foot (a branched line). For example, many countries can be in a single region:

HR ED: Regions

Here’s a quick video introducing the full schema:

Written by Nat Dunn. Follow Nat on Twitter.


Related Articles

  1. Getting Oracle’s HR Schema
  2. How to Install Oracle Express Edition and SQL Developer
  3. Oracle’s Demo HR Schema (this article)
  4. How to choose between a procedure and a function in PL/SQL