6.2 RELATIONAL DATABASE

The relational database model was developed by E. F. Codd in 1970, which is an effective means to store and manage data. It is able to eliminate redundant data representation, which is, however, absent in the previous database models. Also it is able to organize data logically and represent logical hierarchies clearly. A RDBMS is capable of effectively declaring and maintaining the relationship between various related tables through its distinctive working mechanisms. With the fast development of hardware and computer technologies, even the sophisticated relational database management systems can run well in today's most basic computer. Furthermore, SQL commonly used in the relational database is fairly easy to grasp. A novice developer can learn how to perform a majority of operations on a relational database in a short time. The simplicity of relational database management system is also an important factor contributing to the prominent uses of relational databases in various real-world database applications nowadays. Below are the fundamental concepts and terms in any relational database:

• A database is a collection of persistent data, and a relational database is a collection of related tables. The relational database has two outstanding features; i.e., data are stored in form of tables, which are related to one another via common fields. The data presentation in form of tables is a logical construct so it has nothing to do with the details on how the data is physically stored.

• A table (a.k.a. an entity or a relation) is a collection of rows and columns.

• Records (a.k.a. tuples) are the horizontal rows in the table. A record represents a collection of information on an individual item.

• Fields (a.k.a. attributes) are the vertical columns in the table. A field represents a specific characteristic of an item. Field types include character, numerical, Boolean, datetime, and others. A field is said to be null when it contains nothing.

• Domain (a.k.a. field specification) means the possible values that the field can accept.

• A key is used for the logic access to database tables. It can be used to locate the target records as well as traverse the relationships between tables. The key can be any field or the combination of multiple fields, which is able to uniquely identify a record. A key able to identify unique record in a particular table is referred to a primary key. A relationship between two tables is created by choosing a common field, termed foreign keys, between them. The common field must be a primary key to one table. Foreign keys ensure the referential integrity and allow for cascading deletion and updates.

• An index is used to improve the database performance. It should be noted that indexes are part of the physical instead of logical structure.

• A view is a virtual table composed of a subset of the overall real tables. Views are a structure allowing users to access data, and they do not contain any data by themselves. They can be used to achieve the security objectives. When the user needs to access only a certain portion of a table, the remaining portion of a table is hidden from viewing and manipulating.

• A relationship in the relational database refers to a logical link between two tables. In the one-to-one relationship, each instance of table A corresponds to only one instance of table B, and vice versa. In the one-to-many relationship, for each instance of table A, there are many instances of table B, but for each instance of table B, there is only one instance of table A. In the many-to-many relationship, for each instance of table A, there are many instances of table B, and for each instance of table B, there are many instances of table A.

• Data integrity refers to the accuracy, validity, and consistency of data. For instance, a record's name should be stored identically in multiple different places.

• The technique of database normalization is used to prevent data anomalies and improve data integrity.

• A relational database management system (RDBMS) is responsible for relating the information among different tables.

The relational database model is firmly based on the mathematical theory of relational algebra and calculus. Twelve rules are defined that a database management system (DBMS) must adhere to in order to be considered as a relational database. Below are Codd's 12 Rules for the relational database:

• Data are presented in tables: A table is a logical grouping of related data in form of rows and columns. A set of related tables constitutes a database. Each row describes an item, and each column describes a single characteristic about an item. Each value is defined by the intersection of a row and column, and these values are atomic. There are no physical relationships among tables since the relationships are purely logical.

• Data are logically accessible: A relational database does not refer data by its physical location. Instead, each piece of data must be logically accessed through reference of a table, a key value, or a column.

• Nulls are treated uniformly as unknown: The null value in the table must always be seen as an unknown value. Nulls might cause confusion and errors in the database if not dealt with correctly.

• Database is self-describing: In a RDBMS, there are normally two types of tables. Except for the user tables including the working data, there are also system tables indicating the database structure. For instance, metadata are used to describe the database structure as well as various object definitions together with their associations. These two types of tables can be accessed in the same way.

• A single language is used to communicate with the database management system (DBMS): There must exist a unified language capable of tackling all communications with the DBMS by providing various relational operations including data definition, modification, and administration. Structured Query Language (SQL) is a common standard for a relational database language, which is a nonprocedural and declarative language. It is a type of "fourth-generation language," because it allows users to express their intended operation without specifying the details on how to implement it.

• Provides alternatives for viewing data: A relational database must not be limited to source tables when presenting data to the user. Views are the abstractions of source tables. Views allow the creation of custom tables that are tailored to special user needs.

• Supports set-based or relational operations: Rows in a database are viewed as sets for various data manipulation operations. A relational database must support fundamental relational algebra operations as well as set operations. It should be noted that a database only able to support navigational operations does not fulfill the requirement, and therefore it does not fall into the relational database domain.

• Physical data independence: Changes in the physical structure should not affect the applications which are accessing the data in a relational database. Meanwhile, the application does not need to know exactly how the data are physically stored in disk and how they are accessed.

• Logical data independence: Logical data independence means that the relationships between tables can change without affecting application functionality and queries. The database schema or table structures and logical relationships can change without having to re-create the database or the applications that use it.

• Data integrity is a function of the DBMS: Data integrity must be incorporated as an intrinsic property in DBMS instead of an external tool. It refers to the data consistency and accuracy in the database. There are primarily three types of data integrity: entity, domain, and referential integrity. Data integrity cannot be fully ensured without effective database management.

• Supports distributed operations: Data in a relational database can be stored and operated in a centralized or distributed manner. Users should be allowed to conduct database operations on data from tables on multiple servers located at different places as well as from heterogeneous relational databases. Data integrity should be guaranteed during such database operations.

• Data integrity cannot be subverted: There should not be other paths to the database that may subvert data integrity. The DBMS must keep data from being illegally altered.

A relational database management system (RDBMS) allows users to generate, refresh, and manage a relational database. Most commercial RDBMS's support Structured Query Language (SQL) to access the database. The prominent RDBMS products in the current market include Oracle's Oracle, IBM's DB2, and Microsoft's SQL Server. Although nowadays many innovative database management systems have been created or are being developed, the mainstream database management systems in most corporations are still RDBMS. With the more powerful functions and heterogeneous relational databases, relational database has now been applied into numerous industrial and business domains such as Decision Support System (DSS), Data Warehouse, Data Mart, and many others. Other emerging databases such as object-oriented database systems are all extended from the relational database.