Databases, Schemas, Tables, Rows, and Columns

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 independent of any other. SQL was not designed to facilitate access to data in different databases. Recently, methods such as dbSwitch1 or dblink2 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 book, all schemas, table, functions, etc., are contained within one database. 

To create a schema named achemcompany, use the following SQL command  

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 representation 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 

---