Introduction to Relational Models

Relational Model (RM) represents the database as a collection of relations. A relation is nothing but a table of values. Every row in the table represents a collection of related data values. These rows in the table denote a real-world entity or relationship.

The table name and column names are helpful to interpret the meaning of values in each row. The data are represented as a set of relations. In the relational model, data are stored as tables. However, the physical storage of the data is independent of the way the data are logically organized.

The relational data model was introduced by C. F. Codd in 1970. Currently, it is the most widely used data model.

In the relational model, all data is logically structured within relations, i.e., tables, as mentioned above. Each relation has a name and is formed from named attributes or columns of data. Each tuple or row holds one value per attribute. The greatest strength of the relational model is the simple logical structure that it forms. Behind this simple structure is a sophisticated theoretical foundation that is lacking in the first generation of DBMSs.

The relational model has provided the basis for:

Research on the theory of data/relationship/constraint
Numerous database design methodologies
The standard database access language called structured query language (SQL)
Almost all modern commercial database management systems

The relational data model describes the world as “a collection of inter-related relations (or tables).”

Relational Model Concepts

Attribute:

Each column in a Table. Attributes are the properties which define a relation, name of a column in a particular table (all data is stored in tables). Each attribute A_i must have a domain, dom(A_i).e.g., Student_Rollno, NAME,etc.

Tables

In the Relational model the, relations are saved in the table format. It is stored along with its entities. A table has two properties rows and columns. Rows represent records and columns represent attributes. A database is composed of multiple tables and each table holds the data.

Tuple

It is nothing but a single row of a table, which contains a single record. An ORDERED set of values,< v1, v2, v3, …, vn>, where each v_i is a value from dom( A_i).

Relation Schema:

A relation schema represents the name of the relation with its attributes. The design of one table, containing the name of the table (i.e. the name of the relation), and the names of all the columns, or attributes. Example: STUDENT( Name, SID, Age, GPA)

Degree:

The total number of attributes which in the relation is called the degree of the relation.

Cardinality:

Total number of rows present in the Table.

Column:

The column represents the set of values for a specific attribute. A database stores pieces of information or facts in an organized way. Understanding how to use and get the most out of databases requires us to understand that method of organization.

The principal storage units are called columns or fields or attributes. These house the basic components of data into which your content can be broken down. When deciding which fields to create, you need to think generically about your information, for example, drawing out the common components of the information that you will store in the database and avoiding the specifics that distinguish one item from another.

Relation instance

Relation instance is a finite set of tuples in the RDBMS system. Relation instances never have duplicate tuples.

Relation key

Every row has one, two or multiple attributes, which is called relation key.

Attribute domain

Every attribute has some pre-defined value and scope which is known as attribute domain. A domain is the original sets of atomic values used to model data. By atomic value, we mean that each value in the domain is indivisible as far as the relational model is concerned. For example:

The domain of Marital Status has a set of possibilities: Married, Single, Divorced.
The domain of Shift has the set of all possible days: {Mon, Tue, Wed…}.
The domain of Salary is the set of all floating-point numbers greater than 0 and less than 200,000.
The domain of First Name is the set of character strings that represents names of people.

Relation :

A relation, also known as a table or file, is a subset of the Cartesian product of a list of domains characterized by a name. And within a table, each row represents a group of related data values. A row, or record, is also known as a tuple. The columns in a table is a field and is also referred to as an attribute. You can also think of it this way: an attribute is used to define the record and a record contains a set of attributes.

The steps below outline the logic between a relation and its domains.

Given n domains are denoted by D1, D2, … Dn
And r is a relation defined on these domains
Then r ⊆ D1×D2×…×Dn

Just as the content of any one document or item needs to be broken down into its constituent bits of data for storage in the fields, the link between them also needs to be available so that they can be reconstituted into their whole form. Records allow us to do this. Records contain fields that are related, such as a customer or an employee. As noted earlier, a tuple is another term used for record.

Records :

Records and fields form the basis of all databases. A simple table gives us the clearest picture of how records and fields work together in a database storage project. You can command the database to sift through its data and organize it in a particular way.

Integrity Constraints :

Each relational schema must satisfy the following four types of constraints.

A. Domain constraints :

Each attribute Ai must be an atomic value from dom( Ai) for that attribute. The attribute, Name in the example is a BAD DESIGN (because sometimes we may want to search a person by only using their last name.

B. Key Constraints :

Superkey of R, A set of attributes, SK, of R such that no two tuples in any valid relational instance, r( R), will have the same value for SK. Therefore, for any two distinct tuples, t1 and t2 in r( R), t1[ SK] != t2[SK].
Key of R: A minimal superkey. That is, a superkey, K, of R such that the removal of ANY attribute from K will result in a set of attributes that are not a superkey. Example CAR( State, LicensePlateNo, VehicleID, Model, Year, Manufacture

C. Entity Integrity Constraints

The primary key attribute, PK, of any relational schema R in a database cannot have null values in any tuple. In other words, for each table in a DB, there must be a key; for each key, every row in the table must have non-null values. This is because PK is used to identify the individual tuples. Mathematically, t[PK] != NULL for any tuple t ∈ r( R).

D. Referential Integrity

Constraints Referential integrity constraints are used to specify the relationships between two relations in a database. Consider a referencing relation, R1, and a referenced relation, R2. Tuples in the referencing relation, R1, have attributed FK (called foreign key attributes) that reference the primary key attributes of the referenced relation, R2. A tuple, t1, in R1 is said to reference a tuple, t2, in R2 if t1[FK] = t2[PK]. A referential integrity constraint can be displayed in a relational database schema as a directed arc from the referencing (foreign) key to the referenced (primary) key.

Operations in Relational Model

Four basic update operations performed on relational database model are

Insert, update, delete and select.

Insert is used to insert data into the relation
Delete is used to delete tuples from the table.
Modify allows you to change the values of some attributes in existing tuples.
Select allows you to choose a specific range of data.

Insert Operation: Suppose we wish to insert the information of supplier who does not supply any part, can be inserted in S table without any anomaly e.g. S4 can be inserted in Stable. Similarly, if we wish to insert information of a new part that is not supplied by any supplier can be inserted into a P table. If a supplier starts supplying any new part, then this information can be stored in shipment table SP with the supplier number, part number and supplied quantity. So, we can say that insert operations can be performed in all the cases without any anomaly.

Update Operation: Suppose supplier S1 has moved from Qadian to Jalandhar. In that case we need to make changes in the record, so that the supplier table is up-to-date. Since supplier number is the primary key in the S (supplier) table, so there is only a single entry of S 1, which needs a single update and problem of data inconsistencies would not arise. Similarly, part and shipment information can be updated by a single modification in the tables P and SP respectively without the problem of inconsistency. Update operation in relational model is very simple and without any anomaly in case of relational model.

Delete Operation: Suppose if supplier S3 stops the supply of part P2, then we have to delete the shipment connecting part P2 and supplier S3 from shipment table SP. This information can be deleted from SP table without affecting the details of supplier of S3 in supplier table and part P2 information in part table. Similarly, we can delete the information of parts in P table and their shipments in SP table and we can delete the information suppliers in S table and their shipments in SP table.

Whenever one of these operations are applied, integrity constraints specified on the relational database schema must never be violated.

Advantages of using Relational Model

Simplicity: A Relational data model in DBMS is simpler than the hierarchical and network model. We have seen that both the hierarchical and the network database model were conceptually simple. But the relational database model is even simpler at the conceptual level. Since the relational data model frees the designer from the physical data storage details, the designers can concentrate on the logical view of the database.
Structural Independence: The relational database is only concerned with data and not with a structure. This can improve the performance of the model. In relational model, changes in the database structure do not affect the data access. When it is possible to make change to the database structure without affecting the DBMS’s capability to access data, we can say that structural independence has been achieved. So, relational database model has structural independence.
Design, implementation, maintenance and usage ease: The Relational model in DBMS is easy as tables consisting of rows and columns are quite natural and simple to understand. The relational database model\ achieves both data independence and structure independence making the database design, maintenance, administration and usage much easier than the other models.
Ad hoc query capability: The presence of 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 models structured query language or SQL makes ad hoc queries a reality. SQL is a fourth generation language (4GL). A 4 GL allows the user to specify what must be done without specifying how it must be done. So, sing SQL the users can specify what information they want and leave the details of how to get the information to the database. It makes possible for a high-level query language like SQL to avoid complex database navigation.
Data independence: The Structure of Relational database can be changed without having to change any application.
Scalable: Regarding a number of records, or rows, and the number of fields, a database should be enlarged to enhance its usability.

Disadvantages of using Relational Model

The relational model’s disadvantages are very minor as compared to the advantages and their capabilities far outweigh the shortcomings Also, the drawbacks of the relational database systems could be avoided if proper corrective measures are taken. The drawbacks are not because of the shortcomings in the database model, but the way it is being implemented.

Few relational databases have limits on field lengths which can't be exceeded.
Relational databases can sometimes become complex as the amount of data grows, and the relations between pieces of data become more complicated.
Complex relational database systems may lead to isolated databases where the information cannot be shared from one system to another.

Hardware overheads: Relational database system hides the implementation complexities and the physical data storage details from the users. For doing this, i.e. for making things easier for the users, the relational database systems need more powerful hardware computers and data storage devices. So, the RDBMS needs powerful machines to run smoothly. But, as the processing power of modem computers is increasing at an exponential rate and in today’s scenario, the need for more processing power is no longer a very big issue.

Ease of design can lead to bad design: The relational database is an easy to design and use. The users need not know the complex details of physical data storage. They need not know how the data is actually stored to access it. This ease of design and use can lead to the development and implementation of very 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, the poorly designed databases will slow the system down and will result in performance degradation and data corruption.

‘Information island’ phenomenon: As we have said before, the 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 information islands will prevent the information integration that is essential for the smooth and efficient functioning of the organization. These individual databases will also create problems like data inconsistency, data duplication, data redundancy and so on.

But as we have said all these issues are minor when compared to the advantages and all these issues could be avoided if the organization has a properly designed database and has enforced good database standards.