Relational model

In database theory, the relational model refers to the type and design of a database system. Other kinds of models are Index and Hierarchical databases which predate the relational model and are still in use today in data centers with high data volume needs or where existing systems are so complex it would be cost prohibitive to migrate to systems employing the relational model. It is particularly suited to dynamic, adhoc retrievels of data and the adhoc addition/subtraction of entities to the database using a specialized descriptive language call Structure Query Language (or SQL). Usually it is adopted because of its inherent flexibility with regard to future database design and growth and also because of its relative low cost due to market forces.

The underlying rules of the relational model are founded upon a branch of math, developed especially for the model, known as relational calculus. The details of the mathematical logic of the calculus are handled by the database engine; however, in order to avoid anomalies during utilization of the database, specific design constraints of the tables and their fields must be met.

In the relation model, an entity or table consists of one or more fields (formally known as attributes). Each field is assigned a domain (often called a data type) which describes the values that the field can take on (e.g. Integers, Dates, Strings of varying length, etc.). One of the characteristic traits of the relational model is that each table in the database is linked or related to another table through primary and/or foreign keys.

An idealized, very simple example of a description of some tables (also called entities) and their attributes:

Customer(Customer ID, Tax ID, Name, Address, City, State, Zip, Phone)

Order(Order No,Customer ID, Invoice No,Date Placed, Date Promised, Terms, Status)

Order Line(Order No,Order Line No,Product Code, Qty)

Invoice(Invoice No,Customer ID, Order No, Date, Status)

Invoice Line(Invoice No,Line No,Product Code, Qty Shipped)

In this design we have 5 tables (Customer, Order, Order Line, Invoice, and Invoice Line). The bold, underlined field names are primary keys. The non-bold, underlined fields are called foreign keys.

A primary key is a unique identifier which implies that no other row in the table will be duplicated since a primary key is unique. (A row is referred to as a tuple in theoretical settings). Below is a physical depiction of the contents of our example Customer table.

Example: Customer Table

Customer ID Tax ID Name Adresss ....

=======================================

Lees12345 555-3-232 Jo Lee 323 Broadwa.

Red14324 555-9-232 Dorothy Red 1200 Main S.

If we attempted to insert a new customer with the ID Lees12345, this would violate the design of the table since Customer ID is a primary key guaranteed to identify one and only one row, and we already have a customer Lees12345.

Foreign keys indicate that the field name is linked to (or relates to) a primary key in another table as in the Customer ID in the Order table (table and entity are used interchangeably). It is this "relating" that is the underpinning idea of the relational model. By relating each table shown above we could query the database for all of the customers, orders, and invoices. If we only wanted the rows for a specific customer, we would specify this using a predicate or condition (see SQL).