Understanding Schemas

A table consists of rows and columns of data that deal with a specific type of entity such as marketing calls, sales statistics, customers, orders, payroll, and so on. A schema is the collection of related tables. Thus, a schema is to tables what tables are to individual data items. While a table brings together related data items so that they describe an entity when considered a row at a time in a table, the schema is the set of related tables and organizational structure that describe your department or company.

Suppose that you worked in a sales organization with five departments as shown in Figure 12.1.

 

Figure 12.1: Database with tables grouped into five schemas, one for each department in the company

The tables in the marketing department, for example, might include tables showing the history of marketing calls made, marketing representative appointment setting statistics, an appointment list, and demographic data on prospects. The collections department, meanwhile, would have tables that deal with customer information, payments made on accounts, and collections activity such as scheduling dunning letters and field calls for payment pickups. All of the tables shown in Figure 12.1 would exist in a single database. However, each department has its own set of activities, as reflected in the set tables of data it maintains. Each department's tables could be organized into a separate schema within the company's database.

The schema is more of a "container" for objects than just a "grouping" of related tables because a schema includes:

• Tables. Related data items arranged in columns of rows that describe a physical or logical entity. The schema includes the tables as well as all of the components of a table (described in Tip 8, "Understanding the Components of a Table"), which include the column domains, check constraints, primary and foreign keys, and so on).
• Views. Virtual tables defined by an SQL query that display data from one or more base tables (as described in Tip 11, "Understanding Views"). The schema includes the definition of all views that use base tables of "real" data included in the schema. (As you learned in Tip 11, the virtual tables exist only for the duration of the SQL statement that references the view.)
• Assertions. Database integrity constraints that place restrictions on data relationships between tables in a schema. You will learn more about assertions in Tip 33, "Understanding Assertions."
• Privileges. Access rights that individual users and groups of users have to create or modify table structures, and to query and/or update database table data (or data in only specific columns in tables through views). You will learn all about the SQL security privileges in Tips 135-158.
• Character sets. Database structures used to allow SQL to display non-Roman characters such as Cyrillic (Russian), Kanji (Asian), and so on.
• Collations. Define the sorting sequences for a character set.
• Translations. Control how text character sequences are to be translated from one character set to another. Translations let you store data in Kanji, for example, and display it in Cyrillic, Kanji, and Roman-depending on the user's view of the data. In addition to showing which character(s) in one character set maps to which character(s) in another, translations define how text strings in one character set compare to text strings in another when used in comparison operations.

In short, the schema is a container that holds a set of tables, the metadata that describes the data (columns) in those tables, the domains and constraints that limit what data can be put into a table's columns, the keys (primary and foreign) that limit the rows that can be added to and removed from a table, and the security that defines who is allowed to do what to objects in the schema.

When you use the CREATE TABLE statement (which you will learn about in Tip 46, "Using the CREATE TABLE Statement to Create Tables"), the DBMS automatically creates your table in the default schema for your interactive session, the schema named . Thus, if users Konrad and Karen each log in to the database and execute the SQL statement

CREATE TABLE CALL_HISTORY
  (PHONE_REP_ID CHAR(3) NOT NULL,
   PHONE_NUMBER INTEGER NOT NULL,
   DATE_TO_CALL DATE,
   TIME_TO_CALL INTEGER,
   DATE_CALLED  DATE    NOT NULL,
   TIME_CALLED  INTEGER NOT NULL,
   HANGUP_TIME  INTEGER NOT NULL,
   DISPOSITION  CHAR(4) NOT NULL)

the DBMS will add a table to each of two schemas, as shown in Figure 12.2.

   

In short, anytime you use the CREATE statement to create a database object such as a table, view, domain, assertion, and so on, the DBMS will create that object in the default "container" schema.

If you want to create an object in a specific schema "container," the container must exist and you must use the qualified name for the object you are creating. Qualified object names are an extension of the qualified table names you learned about in Tip 9, "Understanding Table Names." Instead of using .

For example, to create the MARKETING schema shown in Figure 12.1, you could use the SQL statement

CREATE SCHEMA MARKETING AUTHORIZATION KONRAD
   CREATE TABLE CALLS_HISTORY
     (PHONE_REP_ID CHAR (3) NOT NULL,
      PHONE_NUMBER INTEGER, NOT NULL,
      DATE_CALLED  DATE)
   CREATE TABLE  MARKETING REPS
     (REP_ID   CHAR(3),
      REP_NAME CHAR(25))
   CREATE TABLE APPOINTMENTS
     (APPOINTMENT_DATE  DATE,
      APPOINTMENT_TIME INTEGER,
      PHONE_NUMBER      INTEGER)
   CREATE TABLE PROSPECTS
     PHONE_NUMBER INTEGER,
     NAME         CHAR(25),
     ADDRESS      CHAR(35))

to create the MARKETING schema and the structure for its four tables. The AUTHORIZATION predicate in the CREATE SCHEMA statement authorizes Konrad to modify the schema and its objects. As such, Konrad could then use an ALTER TABLE MARKETING.

statement to change the columns, domains, and constraints of columns in the tables included in the MARKETING schema. Moreover, Konrad can create additional tables in the MARKETING schema by specifying the schema name in a CREATE TABLE statement, such as:

CREATE TABLE MARKETING.CONTESTS
   (DESCRIPTION CHAR(25),
    RULES       VARCHAR(100),
    WIN_LEVEL1  MONEY,
    WIN_LEVEL2  MONEY,
    WIN_LEVEL3  MONEY)

All DBMS products have schema, or "containers," that hold a collection of tables and related objects. However, the name you can give to a schema varies from product to product. Oracle, Informix, and Sybase, for example, require that the schema name and username be the same. Each also limits the types of objects you can define in the CREATE SCHEMA statement. Thus, you must check the syntax of the CREATE SCHEMA statement in your DBMS manual (or Help system) to see what objects can be grouped together in schema "containers" and what names you can give to schema itself.