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

Schemas can also be used to contain functions, which are discussed
later. For example, if there were several functions to compute the logP of a
structure, it might be convenient to segregate the functions into separate
schemas with fully qualified function names such as xlogp.logp, clogp.
logp, or gnova.logp. In many ways, schemas function like folders or direc-
tories of files. In PostgreSQL, the default schema is called public. Any
table or function created without specifying a particular schema belongs
to the public schema. In Oracle, schema names have traditionally been
associated with user names, but this is not part of the SQL standard nor is
it required by Oracle.
A database can be thought of as a collection of schemas. It is possible to
have many databases managed by one RDBMS, but each database is inde-
pendent of any other. SQL was not designed to facilitate access to data in
different databases. Recently, methods such as dbSwitch 1 or dblink 2 have
made it possible to link together different databases. However, these are
not considered here because they do not conform to the SQL standard and
are implemented is various ways in different RDBMS. In the examples
in this topic, all schemas, table, functions, etc., are contained within one
database.
3.3 Create
To create a schema named achemcompany, use the following SQL
command:
Create Schema achemcompany;
To create a table using SQL, the name of the table is required along
with the names and data types of the columns making up the table.
Consider the following SQL command:
Create Table achemcompany.structure (
smiles Text,
id Integer,
mw Numeric(6,2),
added Timestamp(0));
This creates a table of four columns in the schema achemcompany.
The column named smiles is intended to store the SMILES representa-
tion of a chemical structure, the id column will store an integer identifier
to be used for joining other tables, the column mw will store the molecular
weight with a precision of 2 digits to the right of the decimal point, and
the column named added will record when this structure was entered
into the table. As defined above, any character string could be entered
into the smiles column, any integer into the id column, and any valid