Database Schema

What Is a Schema?
A schema is a collection of certain database objects, such as tables, indexes, and
views, all of which are owned by a user account. You can think of a “schema” as
being the same thing as a user account, but there is a slight difference—the user
account houses the objects owned by a user, and the schema is that set of objects
housed therein.

One definition of “schema” that you’ll often find in Oracle’s
documentation—and elsewhere—is that a schema is a “logical collection of database
objects”. Technically that’s true, but it depends on how logical the user chooses to be
when building and placing those objects within his or her user account. Ideally there
should be some sense to why all those objects are in there, and ideally a “schema”
shouldn’t be just a random collection of objects, but the fact is that there is nothing
built into the Oracle or SQL systems that prevents a user from doing just that—
randomly collecting objects into a user account, and thus creating a “schema” of
random objects.

Ideally, though, a user account should be seen and used as a logical
collection of database objects, driven by business rules, collected into one organized
entity—the schema.

A schema has the same name as the user account. Keep in mind, though, that
it’s entirely possible to create a schema (i.e., a user account) whose “owner” isn’t
a human being at all, but perhaps is an application process, or some other sort of
virtual entity—perhaps a particular background process—or whatever makes sense to suit the business rules that are in force. So in other words, one user will often have one user account, and therefore one schema. But the opposite isn’t necessarily true.There can be more user accounts than there are actual users.

Now that you understand what a “schema” is, and what a user account is, we can
begin to look at different types of database objects, some of which are owned by a
user—and are thereby “schema” objects—and some of which are not schema objects
but are still database objects nonetheless.

Basics of the RDBMS and SQL

Define and Understand the Basics of the RDBMS

  • A relational database consists of collections of data known as tables. A table
    could be a list of ship names and some statistics about each ship. Another table
    might be a list of employees who work on different ships. The “relational”
    aspect to a “relational database” has to do with the common information that
    “relates” two tables together—for example, the list of employees might include
    an entry for each employee’s ship assignment, which would relate back to the
    list of ships and each ship’s statistics.
  • A relational database management system, or RDBMS, is a system in which
    these relational tables and related objects can be created easily, using common
    functions to add, change, and remove data and database objects from
    the RDBMS.

Understand the Unique Role of SQL in Modern Software Systems

  • SQL is most widely used fourth-generation language (4GL) in commercial
    use today.
  • SQL is the only language for interacting with the RDBMS. Any other
    programming language must use embedded SQL calls to interact with the
    RDBMS.
  • The constantly changing nature of databases makes them a tricky place
    to test software. If a SQL script is written and tested successfully today, it’s
    entirely possible that it may break down and produce erroneous information
    later on. The solution is that the script must not only be tested, but must
    originally be designed and written by a capable SQL developer who understands
    proper database design and is thoroughly versed in the RDBMS and
    SQL syntax.