Summary

One of the definitions of a database is "a collection of objects and processes for their manipulation." In a relational database, the objects are created through either standard SQL99 or vendor-specific SQL. The CREATE <object> syntax can be very complicated and usually belongs to the database administrator's domain; nevertheless, understanding how database objects are created is important.

The most important objects introduced in this chapter include table, index, view, materialized view, synonym, schema, and sequence. This list by all means does not cover all objects that could be created within RDBMS using SQL.

The central and most important database object is table — a logical concept of a data holder implemented in a database file structure. Tables can be temporary or permanent, according their life span within an RDBMS. Even such a basic concept can be implemented differently by the different vendors. Most differences occur in the context of the CREATE syntax employed in temporary tables, whereas the syntaxes used in permanent tables tend to adhere more closely to the SQL standard.

Indexes are not part of the SQL standard specification and are hidden from users. The purpose of an index is to speed up data retrieval from a table. A view differs from a table by being populated with data on demand from its base tables (with the exception of materialized views); views are defined by an SQL SELECT query and are dependent on the tables this query specifies. Views are used for many reasons, explained in the chapter.

Synonyms are used to facilitate database object referencing by replacing long, fully qualified names with easy-to-use names.

Some other database objects mentioned in the chapter are domains, tablespaces, filegroups, and constraints.

Each vendor has chosen to implement a different subset of database objects in its RDBMS software; some of these objects are not part of the standard SQL, and some of the higher-level standard-mandated objects are not implemented. Syntax for creating these objects also differs among the vendors and should be checked against vendors' documentation.