RELATIONAL DATABASE MODEL: ELEMENTS, HOW TO DO IT, EXAMPLE - TECHNOLOGY - 2025

The relational database model is a method of structuring data using relationships, using grid-like structures, consisting of columns and rows. It is the conceptual principle of relational databases. It was proposed by Edgar F. Codd in 1969.

It has since become the dominant database model for business applications, when compared to other database models, such as hierarchical, network, and object.

Source: pixabay.com

Codd had no idea how extremely vital and influential his work as a platform for relational databases would be. Most people are very familiar with the physical expression of a relationship in a database: the table.

The relational model is defined as the database that allows grouping its data elements in one or more independent tables, which can be related to each other through the use of fields common to each related table.

Database management

A database table is similar to a spreadsheet. However, the relationships that can be created between the tables allow a relational database to efficiently store a large amount of data, which can be retrieved effectively.

The purpose of the relational model is to provide a declarative method for specifying data and queries: users directly declare what information the database contains and what information they want from it.

On the other hand, they leave it to the database management system software to describe the data structures for storage and the retrieval procedure to answer the queries.

Most relational databases use the SQL language for querying and defining the data. Currently there are many relational database management systems or RDBMS (Relational Data Base Management System), such as Oracle, IBM DB2 and Microsoft SQL Server.

Features and elements

- All data is conceptually represented as an ordered arrangement of data in rows and columns, called a relation or table.

- Each table must have a header and a body. The header is simply the list of columns. The body is the set of data that fills the table, organized in rows.

- All values ​​are scalars. That is, at any given row / column position in the table, there is only a single value.

-Elements

The following figure shows a table with the names of its basic elements, which make up a complete structure.

Tuple

Each row of data is a tuple, also known as a record. Each row is an n-tuple, but the "n-" is generally discarded.

Column

Each column in a tuple is called an attribute or field. The column represents the set of values ​​that a specific attribute can have.

Key

Each row has one or more columns called a table key. This combined value is unique for all rows in a table. By means of this key each tuple will be uniquely identified. That is, the key cannot be duplicated. It is called the primary key.

On the other hand, a foreign or secondary key is the field in a table that refers to the primary key of some other table. It is used to reference the primary table.

-Rules of integrity

When designing the relational model, you define some conditions that must be met in the database, called integrity rules.

Key integrity

The primary key must be unique for all tuples and cannot be null (NULL). Otherwise, you will not be able to uniquely identify the row.

For a multi-column key, none of those columns can contain NULL.

Referential integrity

Each value of a foreign key must match a value of the primary key of the referenced or primary table.

A row with a foreign key can only be inserted in the secondary table if that value exists in a primary table.

If the value of the key changes in the primary table, due to the row being updated or deleted, then all the rows in the secondary tables with this foreign key should be updated or deleted accordingly.

How to make a relational model?

-Collect data

The necessary data must be collected to be stored in the database. These data are divided into different tables.

An appropriate data type must be chosen for each column. For example: whole numbers, floating point numbers, text, date, etc.

-Define primary keys

For each table, a column (or few columns) must be chosen as the primary key, which will uniquely identify each row in the table. The primary key is also used to refer to other tables.

-Create relationships between tables

A database consisting of independent, unrelated tables serves little purpose.

The most crucial aspect in designing a relational database is identifying the relationships between the tables. The relationship types are:

One to many

In a "Class Listing" database, a teacher can teach zero or more classes, while a class is taught by a single teacher. This type of relationship is known as one-to-many.

This relationship cannot be represented in a single table. In the database «List of classes» you can have a table called Teachers, which stores information about teachers.

To store the classes taught by each teacher, you could create additional columns, but you would face a problem: how many columns to create.

On the other hand, if you have a table called Classes, which stores information about a class, you could create additional columns to store information about the teacher.

However, since a teacher can teach many classes, his data would be duplicated across many rows in the Classes table.

Design two tables

Therefore, you need to design two tables: a Classes table to store information about the classes, with Class_Id as the primary key, and a Teachers table to store information about the teachers, with Teacher_Id as the primary key.

The one-to-many relationship can then be created by storing the primary key from the Master table (Master_Id) in the Classes table, as illustrated below.

The Master_Id column in the Classes table is known as a foreign key or secondary key.

For each Master_Id value in the Master table, there can be zero or more rows in the Classes table. For each Class_Id value in the Classes table, there is only one row in the Teachers table.

Many to many

In a "Product Sale" database, a customer order can contain multiple products, and a product can appear in multiple orders. This type of relationship is known as many to many.

You can start the "Product sales" database with two tables: Products and Orders. The Products table contains information about the products, with productID as the primary key.

On the other hand, the Orders table contains the customer's orders, with orderID as the primary key.

You cannot store the ordered products within the Orders table, since you do not know how many columns to reserve for the products. Nor can orders be stored in the Products table for the same reason.

To support a many-to-many relationship, you need to create a third table, known as a join table (OrderDetails), where each row represents an item in a particular order.

For the OrderDetails table, the primary key consists of two columns: orderID and productID, uniquely identifying each row.

The orderID and productID columns in the OrderDetails table are used to reference the Orders and Products tables. Therefore, they are also foreign keys in the OrderDetails table.

One by one

In the "Sale of products" database, a product can have optional information, such as additional description and its image. Keeping it inside the Products table would generate a lot of empty spaces.

Therefore, another table (ProductExtras) can be created to store the optional data. Only one record will be created for products with optional data.

The two tables, Products and ProductExtras, have a one-to-one relationship. For each row in the Products table there is a maximum of one row in the ProductExtras table. The same productID must be used as the primary key for both tables.

Advantage

Structural independence

In the relational database model, changes to the structure of the database do not affect access to the data.

When it is possible to make changes to the structure of the database without affecting the ability of the DBMS to access the data, it can be said that structural independence has been achieved.

Conceptual simplicity

The relational database model is even more conceptually simple than the hierarchical or network database model.

Since the relational database model frees the designer from the details of the physical storage of the data, designers can focus on the logical view of the database.

Ease of design, implementation, maintenance and use

The relational database model achieves both data independence and structure independence, making the design, maintenance, administration, and use of the database much easier than the other models.

Ad-hoc query capacity

The presence of a very powerful, flexible and easy-to-use query capability is one of the main reasons for the immense popularity of the relational database model.

The query language of the relational database model, called structured query language, or SQL, makes ad-hoc queries a reality. SQL is a fourth generation language (4GL).

A 4GL allows the user to specify what should be done, without specifying how it should be done. Thus, with SQL, users can specify what information they want and leave the details of how to get the information to the database.

Disadvantages

Hardware expenses

The relational database model hides the complexities of its implementation and the details of the physical storage of user data.

To do this, relational database systems need computers with more powerful hardware and data storage devices.

Therefore, the RDBMS needs powerful machines to run smoothly. However, as the processing power of modern computers is increasing at an exponential rate, the need for more processing power in today's scenario is no longer a very big problem.

Ease of design can lead to poor design

The relational database is easy to design and use. Users do not need to know the complex details of the physical storage of data. They don't need to know how the data is actually stored to access it.

This ease of design and use can lead to the development and implementation of poorly designed database management systems. Because the database is efficient, these design inefficiencies will not come to light when the database is designed and when there is only a small amount of data.

As the database grows, poorly designed databases will slow down the system and lead to performance degradation and data corruption.

Phenomenon of «information islands»

As mentioned before, relational database systems are easy to implement and use. This will create a situation where too many people or departments will create their own databases and applications.

These islands of information will prevent the integration of information, which is essential for the smooth and efficient functioning of the organization.

These individual databases will also create problems such as data inconsistency, data duplication, data redundancy, etc.

Example

Suppose a database consisting of the Suppliers, Parts, and Shipments tables. The structure of the tables and some sample records are as follows:

Each row in the Suppliers table is identified by a unique supplier number (SNo), uniquely identifying each row in the table. Likewise, each part has a unique part number (PNo).

Furthermore, there cannot be more than one shipment for a given Supplier / Part combination in the Shipments table, since this combination is the primary key of Shipments, which serves as a union table, as it is a many-to-many relationship.

The relationship between the Parts and Shipments tables is given by having the field PNo (part number) in common and the relationship between Suppliers and Shipments arises by having the SNo field (supplier number) in common.

Analyzing the Shipments table, information can be obtained that a total of 500 nuts are being sent from Suneet and Ankit suppliers, 250 each.

Similarly, 1,100 bolts in total were shipped from three different suppliers. 500 blue screws were shipped from the Suneet supplier. There are no shipments of red screws.

References

1. Wikipedia, the free encyclopedia (2019). Relational model. Taken from: en.wikipedia.org.
2. Techopedia (2019). Relational Model. Taken from: ceilingpedia.com.
3. Dinesh Thakur (2019). Relational Model. Ecomputer Notes. Taken from: ecomputernotes.com.
4. Geeks for Geeks (2019). Relational Model. Taken from: geeksforgeeks.org.
5. Nanyang Technological University (2019). A Quick-Start Tutorial on Relational Database Design. Taken from: ntu.edu.sg.
6. Adrienne Watt (2019). Chapter 7 The Relational Data Model. BC Open Textbooks. Taken from: opentextbc.ca.
7. Toppr (2019). Relational Databases and Schemas. Taken from: toppr.com.