Relational Model

Relational model stores data in the form of tables. This concept purposed by Dr. E.F. Codd, a researcher of IBM in the year 1960s. The relational model consists of three major components:

1. The set of relations and set of domains that defines the way data can be represented (data structure).

2. Integrity rules that define the procedure to protect the data (data integrity).

3. The operations that can be performed on data (data manipulation).

A rational model database is defined as a database that allows you to group its data items into one or more independent tables that can be related to one another by using fields common to each related table.

Characteristics of Relational Database

Relational database systems have the following characteristics:

• The whole data is conceptually represented as an orderly arrangement of data into rows and columns, called a relation or table.

.All values are scalar. That is, at any given row/column position in the relation there is one and only one value.

. All operations are performed on an entire relation and result is an entire relation, a concept known as closure.

Dr. Codd, when formulating the relational model, chose the term “relation” because it vas comparatively free of connotations, unlike, for example, the word “table”. It is a common misconception that the relational model is so called because relationships are established between tables. In fact, the name is derived from the relations on whom it is based. Notice that the model requires only that data be conceptually represented as a relation, it does not specify how the data should be physically implemented. A relation is a relation provided that it is arranged in row and column format and its values are scalar. Its existence is completely independent of any physical representation.

Basic Terminology used in Relational Model

The figure shows a relation with the. Formal names of the basic components marked the entire structure is, as we have said, a relation.

Tuples of a Relation

Each row of data is a tuple. Actually, each row is an n-tuple, but the “n-” is usually dropped.

Cardinality of a relation: The number of tuples in a relation determines its cardinality. In this case, the relation has a cardinality of 4.

Degree of a relation: Each column in the tuple is called an attribute. The number of attributes in a relation determines its degree. The relation in figure has a degree of 3.

Domains: A domain definition specifies the kind of data represented by the attribute.

More- particularly, a domain is the set of all possible values that an attribute may validly contain. Domains are often confused with data types, but this is inaccurate. Data type is a physical concept while domain is a logical one. “Number” is a data type and “Age” is a domain. To give another example “StreetName” and “Surname” might both be represented as text fields, but they are obviously different kinds of text fields; they belong to different domains.

Domain is also a broader concept than data type, in that a domain definition includes a more specific description of the valid data. For example, the domain Degree A warded, which represents the degrees awarded by a university. In the database schema, this attribute might be defined as Text [3], but it’s not just any three-character string, it’s a member of the set {BA, BS, MA, MS, PhD, LLB, MD}. Of course, not all domains can be defined by simply listing their values. Age, for example, contains a hundred or so values if we are talking about people, but tens of thousands if we are talking about museum exhibits. In such instances it’s useful to define the domain in terms of the rules, which can be used to determine the membership of any specific value in the set of all valid values.

For example, Person Age could be defined as “an integer in the range 0 to 120” whereas Exhibit Age (age of any object for exhibition) might simply by “an integer equal to or greater than 0.”

Body of a Relation: The body of the relation consists of an unordered set of zero or more tuples. There are some important concepts here. First the relation is unordered. Record numbers do not apply to relations. Second a relation with no tuples still qualifies as a relation. Third, a relation is a set. The items in a set are, by definition, uniquely identifiable. Therefore, for a table to qualify as a relation each record must be uniquely identifiable and the table must contain no duplicate records.

Keys of a Relation

It is a set of one or more columns whose combined values are unique among all occurrences in a given table. A key is the relational means of specifying uniqueness. Some different types of keys are:

Primary key is an attribute or a set of attributes of a relation which posses the properties of uniqueness and irreducibility (No subset should be unique). For example: Supplier number in S table is primary key, Part number in P table is primary key and the combination of Supplier number and Part Number in SP table is a primary key

Foreign key is the attributes of a table, which refers to the primary key of some another table. Foreign key permit only those values, which appears in the primary key of the table to which it refers or may be null (Unknown value). For example: SNO in SP table refers the SNO of S table, which is the primary key of S table, so we can say that SNO in SP table is the foreign key. PNO in SP table refers the PNO of P table, which is the primary key of P table, so we can say that PNO in SP table is the foreign key.

The database of Customer-Loan, which we discussed earlier for hierarchical model and network model, is now represented for Relational model as shown.

In can easily understood that, this model is very simple and has no redundancy. The total database is divided in to two tables. Customer table contains the information about the customers with CNO as the primary key. The Cutomer_Loan table stores the information about CNO, LNO and AMOUNT. It has the primary key combination of CNO and LNO. Here, CNO also acts as the foreign key and refers to CNO of Customer table. It means, only those customer number are allowed in transaction table Cutomer_Loan that have their entry in the master Customer table.

Relational View of Sample database

Let us take an example of a sample database consisting of supplier, parts and shipments tables. The table structure and some sample records for supplier, parts and shipments tables are given as Tables as shown below:

As we discussed earlier, we assume that each row in Supplier table is identified bya unique SNo (Supplier Number), which uniquely identifies the entire row of the table.Likewise each part has a unique PNo (Part Number). Also, we assume that no more than one shipment exists for a given supplier/part combination_in the shipments table.

Note that the relations Parts and Shipments have PNo (Part Number) in common and Supplier and Shipments relations have SNo (Supplier Number) in common. The Supplier and Parts relations have City in common. For example, the fact that supplier S3 and part P2 are located in the same city is represented by the appearance of the same value, Amritsar, in the city column of the two tuples in relations.

Operations in Relational Model

The four basic operations Insert, Update, Delete and Retrieve operations are shown below on the sample database in relational model:

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.

Record Retrieval: Record retrieval methods for relational model are simple and symmetric which can be clarified with the following queries:

Query1: Find the supplier numbers for suppliers who supply part P2.

Solution: In order to get this information we have to search the information of part P2 in the SP table (shipment table). For this a loop is constructed to find the records of P2 and on getting the records, corresponding supplier numbers are printed.

Algorithm

do until no more shipments;

get next shipment where PNO=P2;

print SNO;

end;

Query2: Find part numbers for parts supplied by supplier 52.

Solution: In order to get this information we have to search the information of supplier S2 in the SP table (shipment table). For this a loop is constructed to find the records of S2 and on getting the records corresponding part numbers are printed.

Algorithm

do until no more parts;

get next shipment where SNO=S2;

print PNO;

end;

Since, both the queries involve the same logic and are very simple, so we can conclude that retrieval operation of this model is simple and symmetric.

Conclusion: As explained earlier, we can conclude that relational model does not suffer from the Insert anomalies, Update anomalies and Deletion anomalies, also the retrieval operation is very simple and symmetric, as compared to hierarchical and network models, thus we can say that relational model is best suitable for most of the applications.

Advantages and Disadvantages of Relational Model

The major advantages of the relational model are:

Structural independence: 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.

Conceptual simplicity: 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.

Design, implementation, maintenance and usage ease: 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.

Disadvantages of 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.

Some of the disadvantages are:

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.