# Relational Model Study Notes for GATE Computer Science (CS) Exam

By BYJU'S Exam Prep

Updated on: September 25th, 2023

The relational model is a conceptual framework for organizing and structuring data in a relational database. It was introduced by Edgar F. Codd in 1970 and has since become the foundation for most modern database management systems (DBMS).

In the relational model, data is organized into tables, also known as relations. A table consists of rows and columns, where each row represents a record or a tuple, and each column represents an attribute or a field. The columns have predefined data types that specify the kind of data that can be stored in them, such as integers, strings, dates, or floating-point numbers.

Table of content

## Relational Model

The relational model establishes relationships between tables using keys. A primary key uniquely identifies each row in a table and ensures its uniqueness. A foreign key is a column in one table that refers to the primary key of another table, creating a link between the two tables.

The model is based on a set theory and provides several key principles:

1. Entity Integrity: Each row in a table must be uniquely identifiable using a primary key, and no primary key value can be null.

2. Referential Integrity: Foreign key values must match the primary key values they refer to, or be null.

3. Domain Integrity: Columns must contain values of the specified data type and satisfy any additional constraints or rules defined for them.

4. Relational Operations: The relational model provides a set of operations for manipulating and querying the data stored in tables, such as select, project, join, and union.

5. Data Independence: The model allows for separation between the logical structure of the database (schema) and its physical storage, providing flexibility and ease of maintenance.

## Relational Algebra

The relational model is completely based on relational algebra. It consists of a collection of operators that operate on relations. Its main objective is data retrieval. It is more operational and very much useful to represent execution plans, while relational calculus is non-operational and declarative.

Here, declarative means user define queries in terms of what they want, not in terms of how they compute it.

Formulas for GATE Computer Science Engineering – Databases

## Relational Query Languages

Used for data manipulation and data retrieval. Relational model support simple yet powerful query languages. The two most widely used relational query languages are SQL (Structured Query Language) and its variations.

To understand SQL, we need good understanding of two relational query languages (i.e., relational algebra and relational calculus).

## Basic Operation in Relational Algebra

The operations in relational algebra are classified as follows.

Selection (σ): The select operation selects tuples/rows that satisfy a given predicate or condition. We use (σ) to denote selection. The predicate/condition appears as a subscript to σ.

Projection (π): It selects only required/specified columns/attributes from a given relation/table. Projection operator eliminates duplicates (i.e., duplicate rows from the result relation).

Union (∪): It forms a relation from rows/tuples which are appearing in either or both of the specified relations. For a union operation R ∪ S to be valid, below two conditions must be satisfied.

• The relations Rand S must be of the same entity i.e., they must have the same number of attributes.
• The domains. of the i th attribute of R and i th attribute of S must be the same, for all i.

Intersection (∩): It forms a relation of rows/ tuples which are present in both the relations R and S. Ensure that both relations are compatible for union and intersection operations.

Set Difference (-): It allows us to find tuples that are in one relation but are not in another. The expression R – S produces a relation containing those tuples in R but not in S.

Cross Product/Cartesian Product (×): Assume that we have n1 tuples in R and n2 tuples in S. Then, there are n1 * n2 ways of choosing a pair of tuples; one tuple from each relation. So, there will be (n1 * n2) tuples in result relation P if P = R × S.

So, we can say that Cross product between two relation relate each and every tuple of one relation to the other relation.

Natural Join(): A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.

Conditional Join(C): It is performed by obtaining the cross product on given tables and then followed by applying the condition C on the obtained result. it is similar to conditional join except the fact that condition can be applied for any attributes (not restricted to the common attributes).

Outer Join: it is further divided into three parts:

• Left Outer Join(⟕): Output the records of natural join plus the record of left table which fails to appear in natural join.
• Right Outer Join(⟖): Output the records of natural join plus the record of right table which fails to appear in natural join.
• Full Outer Join(⟗): Output the records of natural join plus the record of left table as well as right table which fails to appear in natural join.

Rename Operator(ρ): It is used to rename the instance of a relation. it can also be used to rename the attributes of the given relation. also, the changes are not reflected to the original database.

Division Operator(/):

• The relation returned by division operator will return those tuples from relation A which are associated to every B’s tuple.
• For R/S, Attributes of S should be proper subset of R.

Cardinalities: For a relation R with n distinct tuples and relation S with m distinct tuples-

 Operation Cardinality R×S n*m tuples R⋈S 0 to nm tuples R⋈CS 0 to nm tuples R⟕S n to nm tuples R⟖S m to nm tuples R⟗S max(m,n) to mn tuples R∪S max(m,n) to m+n tuples R∩S 0 to min(m,n) tuples R-S 0 to n tuples R/S 0 to n/m tuples

You can complete comprehensive information about the GATE exam pattern, cut-off marks, and other related details on the official YouTube channel of BYJU’S Exam Prep.