2. Theory

2.1 Relational Databases

A database is a collection of related data stored in a computer.  These data can be arranged and stored in many different ways.  Various models explain the way in which data is arranged in the database.  One of the most common and widely model is the relational database model, which is used in relational database architectures.

2.1.1 Relational database model

"A relation is defined mathematically based on set theory.  The basic component of a set is an object.  An object has series of attributes, where attribute is a property of the object.  A series of these attributes describe an object which are organised in a tuple (record) of values.  A set of objects of same type forms set of tuples, which then form a relation" [3 pg 21].  These logical relations are represented universally by two-dimensional tables.  Attributes related to the columns of the table and tuples forms the table rows.  These concepts can be represented in a diagram as below.

Figure 1: Relational model concepts [3 pg 21]

Relational databases consist of number of these relations or tables.  The relations have a primary key and optional foreign key(s).  "The primary key is an attribute (or combinations of attributes), which uniquely identifies each tuple in a relation" [2 pg 209].  "Foreign key is an attribute in a relation, which serves as an primary key in another relation in the same database" [2 pg 210].  This foreign key and the matching primary key of the other relation are used to create logical table joins are relationships.  This concept can be explained using the following example.

Relation Supplier (supplier_id, name, address, telephone)

Relation Product  (product_id, description, suppier_id, cost, quantity)

First attribute in both relations is their primary key.  The supplier_id in the relation ‘Product’ is a foreign key as it serves as the primary key of the ‘Supplier’ table.  Therefore supplier_id attribute in the two relations (tables) are used to create the relationship between the two tables.  Advantage of such breakdown is that the same supplier details do not need to be repeated over an over in the product table.  Also the relational model does not allow such repeated values in relations.  Some of these properties [2 pg 210] of the relations are,

1. each relation in a database has a unique name
2. there are no multi-valued attributes in a relation
3. no two rows can be identical
4. each attribute (column name) within a relation (table) must be unique
5. order in which table columns and rows arranged is insignificant as interchanging these columns or rows does not change the meaning of the relation

The relational model consists of three parts [2 pg 208].

1. data structure - as explained , data is organised in tables with rows and columns
2. data integrity - various rules that defines how the data need to be managed.  There are three different rules that govern data integrity [2 pg 213-214].
• domain constraints - type, size (or length) or range of values that is acceptable
• entity integrity - every relation has a primary key with all primary key data been valid values (not null).
• referential integrity - either the foreign key value matches a primary key value in the other relation or foreign key value is null.
3. data manipulation - various operations use to manipulate the data stored in the database

Relational database management systems (RDBMS) need to handle all the above operations.  These DBMS not only have to manipulate data correctly, but also need to provide a means of creating, altering, deleting tables and rows in the database.  The language used to do these operations is the SQL (Structured Query Language), which is explained in more details in the next section.

Theory - SQL