Relational Model in DBMS

 

Relational Model (RM) represents the database as a collection of relationships. A relationship is nothing more than a table of values. Each row of the table represents a collection of related data values. These rows of the table denote a real-world entity or relationship.

The table name and column names are useful for interpreting the meaning of the values in each row. Data are represented as a set of relationships. In the relational model, the data are stored as tables. However, the physical storage of data is independent of how data is organized logically.

Some popular relational database management systems are:

• DB2 and Informix Dynamic Server – IBM
• Oracle and RDB – Oracle
• SQL Server and Access – Microsoft

Concepts of relational model in DBMS

1. Attribute: Each column in a table. Attributes are the properties that define a relationship. e.g. Student_Rollno, NAME, etc.
2. Tables – In the Relational model the relationships are saved in table format. It is stored together with its entities. A table has two rows and columns of properties. Rows represent records and columns represent attributes.
3. Tuple – Nothing is more than a single row of a table, which contains a single record.
4. Relationship Scheme: A relationship scheme represents the name of the relation with its attributes.
5. Grade: The total number of attributes that in the relation is called the degree of the relation.
6. Cardinality: Total number of rows present in the Table.
7. Column: The column represents the set of values for a specific attribute.
8. Relationship Instance – The relationship instance is a finite set of tuples in the RDBMS system. Relationship instances never have duplicate tuples.
9. Relational key – Each line has one, two or multiple attributes, which is called the relation key.
10. Attribute domain – Each attribute has some predefined value and scope that is known as attribute domain

 

Restrictions on relational integrity

Restrictions on relational integrity in the DBMS refer to conditions that must be present for a valid relationship. These relational constraints in the DBMS are derived from the rules of the mini-world that the database represents.

There are many types of integrity constraints in the DBMS. The constraints in the relational database management system are mainly divided into three main categories:

1. Domain restrictions
2. Main restrictions
3. Restrictions on Referential Integrity

Domain restrictions

Domain restrictions can be violated if an attribute value does not appear in the corresponding domain or if it is not the appropriate data type.

Domain restrictions specify this within each tuple and the value of each attribute must be unique. This is specified as data types that include integer default data types, real numbers, characters, Booleans, variable-length strings, etc.

Example:

Create DOMAIN CustomerName 
CHECK (value not NULL)

The example shown demonstrates the creation of a domain restriction such that CustomerName is not NULL.

Main restrictions

An attribute that can uniquely identify a tuple in a relationship is called a key in the table. The value of the attribute for different tuples in the relationship must be unique.

Example:

In the given table, CustomerID is a key attribute of the Customer table. It is more likely that there is a single key for one customer, CustomerID 1 is only for CustomerName “Google”.

Identification of the Client	Name of the customer	State
1	Google	Active
2 2	Amazon	Active
3	Apple Apple	Inactive

Restrictions on Referential Integrity

The referential integrity constraints in DBMS are based on the concept of foreign keys. A foreign key is an important attribute of a relationship that must be referred to in other relationships. The referential integrity constraint state occurs when the relation refers to a key attribute of a different or equal relationship. However, this key element must exist in the table.

Example:

In the example above, we have 2 relationships, Client and Billing.

The tuple for CustomerID 1 is referenced twice in the Billing relationship. Therefore, we know that CustomerName-Google has a billing value of $300

Operations in the Relational Model

Four basic update operations performed on the relational database model are

Insert, update, delete and select.

• Insert is used to enter data in the interface
• Delete is used to exclude tuples from the table.
• Modification allows you to change the values of some attributes in existing tuples.
• Selecting allows you to choose a specific range of data.

Whenever one of these operations is applied, the integrity constraints specified in the relational database schema should never be violated.

Insert operation

The insertion operation provides attribute values for a new tuple that must be inserted into a relation.

 

 

Update operation

You can see that in the relationship table provided below CustomerName, ‘Apple’ is updated from Active Inactive.

 

 

Exclude operation

To specify the deletion, a condition in the attributes of the relationship selects the tuple to be deleted.

 

 

In the example given above, CustomerName “Apple” is excluded from the table.

The Delete operation may violate the referential integrity if the tuple deleted is referenced by foreign keys of other tuples in the same database.

Select the operation:

In the example above, CustomerName”Amazon” is selected

Best Practices for Creating a Relational Model

• Data must be represented as a collection of relationships
• Each relationship must be clearly described in the table
• Rows must contain data on an entity
• The columns must contain data about entity attributes
• Table cells must contain a single value
• Each column must be given a unique name
• No two lines can be identical
• The values of an attribute must be of the same domain

Advantages of relational database model

• Simplicity: A relational data model in DBMS is simpler than the hierarchical and network model.
• Structural Independence: The relational database is concerned only with data and not with a structure. This can improve the model performance.
• Easy to use: The relational model in DBMS is easy, because the tables that consist of rows and columns are quite natural and simple to understand
• Query Ability: Makes a high-level query language such as SQL possible to avoid complex navigation in the database.
• Data Independence: The structure of the relational database can be changed without the need to change any applications.
• In relation to the number of records, or lines, and the number of fields, a database must be expanded to improve its usability.

Disadvantages of relational model

• Few relational databases have field length limits that cannot be exceeded.
• Sometimes relational databases can become complex as the amount of data increases and the relationships between data become more complicated.
• Complex systems of relational databases can lead to isolated databases where information cannot be shared from one system to another.

Summary

• Relational database modeling represents the database as a collection of relationships (tables)
• Attribute, Tables, Tuple, Relationship Scheme, Degree, Cardinality, Column, Relationship Instance, are some important components of the Relational Model
• Restrictions on relational integrity refer to conditions that must be present for a relationship approach valid in the DBMS
• Domain restrictions can be violated if an attribute value does not appear in the corresponding domain or is not the appropriate data type
• Inserting, Selecting, Modify and Delete are the operations performed in the constraints of the Relational Model
• The relational database is concerned only with data and not with a structure that can improve model performance
• Advantages of relational model in DBMS are simplicity, structural independence, ease of use, ability to query, data independence, scalability, etc.
• Few relational databases have field length limits that cannot be exceeded.