➢ The relational model in DBMS is an approach to logically represent and manage the data stored in a database. In this model, the data is organized into a collection of two- dimensional inter-related tables, also known as relations. Each relation is a collection of columns and rows, where the column represents the attributes of an entity and the rows (or tuples) represent the records.
Let's look at a scenario to understand the relational model:
➢ Consider a case where you wish to store the name, the CGPA attained, and the roll number of all the students of a particular class. This structured data can be easily stored in a table as described below:
Terminology • Relation: Two-dimensional table used to store a collection of data elements. • Tuple: Row of the relation, depicting a real-world entity. • Attribute/Field: Column of the relation, depicting properties that define the relation. • Attribute Domain: Set of pre-defined atomic values that an attribute can take i.e., it describes the legal values that an attribute can take. • Degree: It is the total number of attributes present in the relation. • Cardinality: It specifies the number of entities involved in the relation i.e., it is the total number of rows present in the relation. • Relational Schema: It is the logical blueprint of the relation i.e.; it describes the design and the structure of the relation. It contains the table name, its attributes, and their types:
For our student relation example, the relational schema will be:
STUDENT (ROLL_NUMBER INTEGER, NAME VARCHAR (20), CGPA FLOAT)
• Relational Instance: It is the collection of records present in the relation at a given
time. • Relation Key: It is an attribute or a group of attributes that can be used to uniquely identify an entity in a table or to determine the relationship between two tables. Relation keys can be of 6 different types: 1. Candidate Key 2. Super Key 3. Composite Key 4. Primary Key 5. Alternate Key 6. Foreign Key
Highlights:
1. A Relation is a collection of rows (tuples) and columns (attributes).
2. In a relation, the tuples depict real-world entities, while the attributes are the properties that define the relation. 3. The structure of the relation is described by the relational schema. 4. Relational keys are used to uniquely identify a row in a table or to determine the relationship between two tables.
What is the Relational Database?
➢ A relational database is a collection of information that organizes data in predefined relationships where data is stored in one or more tables (or "relations") of columns and rows, making it easy to see and understand how different data structures relate to each other.
Relational table between product and customer
Advantages of using the relational model. The advantages and reasons due to which the relational model in DBMS is widely accepted as a standard are:
• Simple and Easy to Use - Storing data in tables is much easier to understand and implement as compared to other storage techniques. • Manageability - Because of the independent nature of each relation in a relational database, it is easy to manipulate and manage. This improves the performance of the database. • Query capability - With the introduction of relational algebra, relational databases provide easy access to data via high-level query language like SQL. • Data integrity - With the introduction and implementation of relational constraints, the relational model can maintain data integrity in the database.
Disadvantages of using the relational model.
The main disadvantages of relational model in DBMS occur while dealing with a huge amount of data as:
• The performance of the relational model depends upon the number of relations present in the database. • Hence, as the number of tables increases, the requirement of physical memory increases. • The structure becomes complex and there is a decrease in the response time for the queries. • Because of all these factors, the cost of implementing a relational database increase.
Relational Integrity Constraints: -
Relational integrity constraints, also known as referential integrity constraints, are rules that establish relations in a relational database model. These rules help maintain data consistency and integrity by preventing duplicate entries and keeping data together.
There are many types of Integrity Constraints in DBMS. Constraints on the Relational database management system is mostly divided into four main categories are:
Domain Constraints These are attribute-level constraints. An attribute can only take values that lie inside the domain range. e.g.; If a constraint AGE>0 is applied to STUDENT relation, inserting a negative value of AGE will result in failure.
Key Integrity Every relation in the database should have at least one set of attributes that defines a tuple uniquely. Those set of attributes is called keys. e.g.; ROLL_NO in STUDENT is key. No two students can have the same roll number. So, a key has two properties: i. It should be unique for all tuples. ii. It can’t have NULL values.
Entity integrity constraint
An entity integrity constraint states that the primary key value cannot be null. This is because it becomes impossible to identify and distinguish rows within a table without a valid primary key value. However, other fields in a table can contain null values.
Referential Integrity Constraints
Referential Integrity constraints in DBMS are based on the concept of Foreign Keys. A foreign key is an important attribute of a relation which should be referred to in other relationships. Referential integrity constraint state happens where relation refers to a key attribute of a different or same relation. However, that key element must exist in the table. Example: -
Here, Department table contain one key element that is 1 but in student table we have two foreign key elements 1 and 6. 1 is present in both table so it validates but 6 is not present so it will be an error.
Note: Study Properties and characteristics of relational
