Database Design   «Prev  Next»

Lesson 2The relational database model
ObjectiveDescribe the Relational Database Model

Describe Relational Database Model

The structure of every database is determined by a database model. A database model is a formal structure for organizing and linking data in a database.

What are the Standard Database Models?

The standard database models are:
  1. Relational model: A formal structure that organizes data into relations (i.e., tables).
  2. Object-oriented model: Object/relational database management systems (ORDBMSs) add new object storage capabilities to the relational systems at the core of modern information systems
  3. hierarchical model: A database model that organizes data in a top-down (inverted tree) structure.
  4. network model: An extension of the hierarchical model. (See hierarchical model.)
The major types of data models in the history of Databases are listed below.

1) Relational Model organizes data into two dimensional arrays known as relations(tables) and each relation consists of rows and columns. Another major characteristic of relational model is that of keys, designated columns in a relation used to order data or establish relations.
Relational Model
Relational Model

2) Object Model aims to reduce the overhead of converting information representation in the database to an application specific representation. Unlike a traditional database, an object model allows for data persistence and storage by storing objects in the databases. The relationships between various objects are inherent in the structure of the objects. This is mainly used for complex data structures such as 2D and 3D graphics which must otherwise be flattened before storage in a relational database.
Diagram of the Object Model
2) Diagram of the Object Model

Evolution of Object Databases: Object oriented databases or object databases incorporate the object data model to define data structures on which database operations such as CRUD can be performed. They store objects rather than data such as integers and strings. The relationship between various data is implicit to the object and manifests as object attributes and methods Object database management systems extend the object programming language with
  1. transparently persistent data,
  2. concurrency control,
  3. data recovery,
  4. associative queries, and
  5. other database capabilities.

3) Hierarchical model contains data organized into a tree-like structure. This supports parent-child relationships between data similar to a tree data structure where object types are represented by nodes and their relationships are represented by arcs. This model is restrictive in that it only allows one to many relationship (a parent can have many children but a child can only have one parent) An example of this is Jump to search IBM's Information Management System (IMS), which is a joint hierarchical database with extensive transaction processing capabilities.

4) Network Model is similar to the hierarchical model in representation of data but allows for greater flexibility in data access as it supports many to may relationships.
The Network Model is a database model that was developed as an evolution of the hierarchical model to address some of its limitations. It allows for more flexible and complex relationships between data records, representing them as a graph-like structure. The Network Model was standardized by the Conference on Data Systems Languages (CODASYL) in the late 1960s. Here are some key characteristics of the Network Model with respect to databases:
  1. Record-based structure: Similar to the hierarchical model, the Network Model organizes data into records, which are similar to rows in a relational database. Each record consists of fields (attributes) that store the actual data.
  2. Sets: The relationships between records are represented as sets. A set consists of an owner record and one or more member records. Each set represents a one-to-many relationship between the owner and its member records. Unlike the hierarchical model, a record in the Network Model can be an owner in one or more sets and a member in multiple sets.
  3. Graph-like structure: The Network Model allows for complex relationships between records, forming a graph-like structure. This flexibility enables representation of more complex data relationships compared to the hierarchical model, which only supports tree-like structures.
  4. Pointers: The relationships between records in the Network Model are established using pointers. Each record contains pointers that refer to other related records, which allows for efficient navigation between records in the database.
  5. Navigational access: Data access in the Network Model is navigational, meaning that applications need to traverse the database structure, following pointers from one record to another, to access the desired data. This can be more efficient for certain types of data access patterns but makes querying the data more complex compared to the relational model, which uses a declarative query language.
  6. Data integrity: The Network Model supports referential integrity through the use of pointers and owner-member relationships. When a record is deleted, its related records can be automatically updated or deleted, maintaining data consistency.
  7. Complexity: The Network Model is more complex than the hierarchical model, as it requires managing pointers and understanding the relationships between records. This complexity can make it harder to design and maintain network databases compared to more modern, relational databases.

Although the Network Model provided more flexibility and improved data modeling capabilities compared to the hierarchical model, it has been largely replaced by the relational database model, which offers a more straightforward and powerful way to model and query data using declarative SQL.

The Relational Model

A relational database is based on the relational model, which organizes data into tables comprising columns and rows. In the relational database model:
  1. A table[1] is the largest structure.
  2. A field[2] (column) is the smallest structure.
  3. A record: (row) contains all the fields in a table. A particular instance of the subject of a table.

Table, field, and record are terms typically used in general database discussions. Table, column, and row are used to physically describe a tabular format.

Definition: The relational model consists of five components:
  1. An open ended collection of scalar types, including type BOOLEAN in particular
  2. A relation type generator and an intended interpretation for relations of types generated thereby
  3. Facilities for defining relation variables of such generated relation types
  4. A relational assignment operator for assigning relation values to such relation variables
  5. A relationally complete (but otherwise open ended) collection of generic relational operators for deriving relation values from other relation values

Relational Model Concepts

The relational model represents the database as a collection of relations. Informally, each relation resembles a table of values or, to some extent, a flat file of records. It is called a flat file because each record has a simple linear or flat structure. There are important differences between relations and files, as we shall soon see. When a relation is thought of as a table of values, each row in the table represents a collection of related data values. A row represents a fact that typically corresponds to a real-world entity or relationship. The table name and column names are used to help to interpret the meaning of the values in each row. For example, a student table is called STUDENT because each row represents facts about a particular student entity. The column names 1) Name, 2)Student_number, 3) Class, and 4) Major specify how to interpret the data values in each row, based on the column each value is in. All values in a column are of the same data type.
In the formal relational model terminology,
  1. a row is called a tuple,
  2. a column header is called an attribute, and
  3. the table is called a relation.
The data type describing the types of values that can appear in each column is represented by a domain of possible values.

XML model

The XML model has emerged as a standard for exchanging data over the Web, and has been used as a basis for implementing several prototype native XML systems. XML uses hierarchical tree structures. It combines database concepts with concepts from document representation models. Data is represented as elements; with the use of tags, data can be nested to create complex hierarchical structures. This model conceptually resembles the object model but uses different terminology. XML capabilities have been added to many commercial DBMS products. In the next lesson, we will examine a relational database table in detail.
[1] table: A collection of data arranged in rows and columns is known as a table and is the largest structure in a relational database.
[2] field: The smallest structure in a table; contains data describing the subject of a table.