The relational model of databases 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..
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 relation 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..
Article index
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 let the database management system software be in charge of describing the data structures for storage and the retrieval procedure to answer the queries..
Most of the 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.
- 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.
The following figure shows a table with the names of its basic elements, which make up a complete structure.
Each row of data is a tuple, also known as a record. Each row is an n-tuple, but the "n-" is generally discarded.
Each column in a tuple is called an attribute or field. The column represents the set of values that a specific attribute can have.
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. Used to reference the primary table.
When designing the relational model, you define some conditions that must be met in the database, called integrity rules.
The primary key must be unique for all tuples and cannot be null. Otherwise, you will not be able to uniquely identify the row.
For a multi-column key, none of those columns can contain NULL.
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, by updating or deleting the row, then all the rows in the secondary tables with this foreign key should be updated or deleted accordingly.
The necessary data must be collected to be stored in the database. This data is divided into different tables.
An appropriate data type must be chosen for each column. For example: whole numbers, floating point numbers, text, date, etc..
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.
A database consisting of independent and unrelated tables has little purpose.
The most crucial aspect in designing a relational database is identifying the relationships between the tables. The relationship types are:
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, additional columns could be created, 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, as a teacher can teach in many classes, his data would be duplicated in many rows of the Classes table.
Therefore, two tables need to be designed: 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..
Then the one-to-many relationship can be created by storing the primary key of 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.
In a "Product Sale" database, a customer's 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..
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. Same productID must be used as primary key for both tables.
In the relational database model, changes in the database structure do not affect data access.
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.
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.
The relational database model achieves both data independence and structure independence, which makes the design, maintenance, administration and use of the database much easier than the other models..
The presence of a very powerful, flexible and easy-to-use query capacity 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..
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..
The relational database is easy to design and use. Users do not need to know the complex details of physical data storage. 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. Since 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..
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..
Suppose a database that consists 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).
In addition, 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 acts 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 field SNo (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. No shipments of red screws.
Yet No Comments