Section D.1. Relational Databases

D.1. Relational Databases

Data storage and retrieval are two of the biggest tasks facing most enterprise applications. There are lots of ways to store data on a disk, but for large-scale applications, a relational database management system (RDBMS) is far and away the most popular choice.

Data in an RDBMS is organized into tables, which contain rows and columns. You can think of an individual table as a spreadsheet with a little more organization: column data types are fixed, and there may be rules governing the formatting of each column. This alone is enough for a database system (plain DBMS). A relational database system has one other key attribute: individual tables can be related based on some common data. Figure D-1 shows three tables in a relational structure. The CUSTOMERS table is related to the ORDERS table based on the CUSTOMER_ID field, and the ORDERS table is related to the ITEMS table based on the ORDER_ID field. SQL provides a standardized means of accessing the data in tables and working with the relationships between tables.

Figure D-1. Three related tables

The highest-level organizational concept in an RDBMS system is a cluster.^[2] A cluster contains one or more catalogs, which are usually the highest-level object a programmer ever has to deal with. A catalog contains a group of schemas. A schema contains a group of tables and other objects and is generally assigned to a particular application or user account. Generally, the database administrator is responsible for dealing with clusters and catalogs. Most users work within a particular schema.

^[2] The naming comes from the older SQL standards, where a cluster represented physical storage space. This is no longer the case, but the name persists.

To reference a table within a particular schema, separate the schema name and the table name with a dot:

 schema_name.table_name 

To reference a particular column within a particular schema:

 schema_name.table_name.column_name 

To set a particular schema as the default, use the SET SCHEMA SQL statement:

 SET SCHEMA schema_name 

When you log into a database, you are generally assigned a default schema. When accessing tables within the default schema, you may omit the schema name.

When creating new objects, the names you assign to them must be unique within the schema. SQL-92 allows names up to 128 characters, including letters, numbers, and the underscore ( _ ) character.