Sidebar: Some Relational Database Fundamentals

Relational databases are based on set theory and relational mathematics. Data is stored in tables (relations). Tables are made up of rows (tuples) and columns (attributes). All columns that make up a table should describe a particular object (entity). Each column belongs to a domain, which is a mathematical set of elements all belonging to the same data type. A data type determines how the domain's elements are represented, as well as how much storage to allocate each element. Each element of a domain is atomic, meaning that it cannot be decomposed. Tables actually exist over underlying domains.

Consider the table in Figure 1. The table's name defines an entity, "employee." Each column describes the "employee" entity. Since both first and middle names come from the same pool of names, the f_name and m_name columns belong to the same domain. Domain to columns is an example of a one-to-many relationship. Although each element of the date_of_birth column contains a month, day, and year, it is not possible to decompose the date because elements of a domain must be atomic. Many relational databases provide functions that extract components from an element of a domain. Those functions do not change the way that data is stored internally within the table.

The f_name, m_name, and l_name columns consist of elements that are character data types. The dependent column consists of integer data type elements, allowing addition and subtraction of those elements. Because no mathematical expressions are performed on Social Security numbers, they can be defined as character data types.

Tables are considered objects in a relational database. Relational databases use SQL to create and operate on database objects. SQL is divided into three sublanguages. The first sublanguage is known as Data Definition Language (DDL) and is used to create database objects. The second sublanguage is known as Data Manipulation Language (DML) and is used to operate on the data within a database object. The third sublanguage is known as Data Control Language (DCL) and is used to specify access permissions on database objects. An SQL expression operates on data stored in tables, producing results in tabular form. This is the closure property of SQL.

Create table is a DDL SQL expression. The syntax of a create table statement is:

CREATE TABLE <tablename> (
column_name   datatype    [ Null | Not Null ],
column_name   datatype    [ Null | Not Null ]  )
on segment_name
go

A segment refers to a raw disk partition, or a collection of raw disk partitions. If no segment is specified, the database engine will place the newly created table on the default segment. Notice that the last column is not followed by a comma.