Structured Query Language (SQL) - Design and Use of Relational Databases in Chemistry - page 21

chapter 3
Structured Query
Language (SQL)
3.1 Introduction
In Chapter 2, the concept of relational tables was introduced. In this
chapter, the most common way of working with tables in an RDBMS is
introduced. The SQL language provides ways to create tables, insert data,
select data, delete data, update data, join tables, create table schemas,
define functions, etc. SQL has many other features, not all of which are
covered here.
3.2 Databases, Schemas, Tables,
Rows, and Columns
The word database is used informally to refer to any collection of data.
For example, one might call a file of e-mail addresses a database. There
is a more formal definition of a database in an RDBMS. Information in
an RDBMS is structured in a sort of hierarchy. An RDBMS contains data-
bases, which contain schemas, which contain tables, which contain rows,
which contain columns. A schema is a collection of tables. Rather than
have tens or hundreds of tables in your database, the tables can be orga-
nized into schemas for clarity and convenience. In one sense, a schema
is just a type of name space that allows a richer naming convention. For
example, a database might contain several tables of structures. It might
be convenient to use the table name structure to contain these structures.
Rather than try to fit all the various structures together into a single table,
or use coded table names such as nci_structures, pubchem_structures, or
vendor_structures, separate schemas could be created. The fully qualified
table names would then be nci.structure, pubchem.structure, and vendor.
structure. The tables are segregated from each other by virtue of belong-
ing to a separate schema, yet they are shown to be similar by sharing the
same table name structure. Tables in different schemas are not isolated
from each other; for example, they can be joined as readily as tables that
belong to the same schema.
21