Relational Model

Module -2
The relational model in Database Management Systems (DBMS) was developed by Edgar F.
Codd.

Codd, a computer scientist at IBM's San Jose Research Laboratory, proposed this new data
representation framework in 1970. His work revolutionized data management by introducing the
concept of representing data in tabular structures, known as relations (or tables), and accessing
them using a high-level, non-procedural language, which later evolved into Structured Query
The Relational
Language (SQL).Model organizes data using tables (relations) consisting of rows and columns.
The relational model represents how data is stored and managed in Relational Databases where data is organized into
tables, each known as a relation.

Each row of a table represents an entity or record and each column represents a particular attribute of that entity.

The relational model transforms conceptual designs from ER diagrams into implementable structures. These structures
are used in relational database systems like Oracle SQL and MySQL.

Example: Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE and AGE shown in
the table.
 Terminologies Related to Relational Model
1.Attribute: Attributes are the properties that define an entity. For example, ROLL_NO, NAME, ADDRESS etc.

2.Relation Schema: A relation schema defines the structure of the relation and represents the name of the relation with its
attributes. For example, STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and AGE) is the relation schema for
STUDENT. If a schema has more than 1 relation it is called Relational Schema.

3.Tuple: A Tuple represents a row in a relation. Each tuple contains a set of attribute values that describe a particular entity. For
example, (1, RAM, DELHI, 9455123451, 18) is a tuple in the STUDENT table.

4.Relation Instance: The set of tuples of a relation at a particular instance of time is called a relation instance. It can change
whenever there is an insertion, deletion or update in the database.

5.Degree: The number of attributes in the relation is known as the degree of the relation. For example, The STUDENT
relation has a degree of 5, as it has 5 attributes.

6.Cardinality: The number of tuples in a relation is known as cardinality. For example, The STUDENT relation defined
above has cardinality 4.

7.NULL Values: The value which is not known or unavailable is called a NULL value. It is represented by NULL. For
example, PHONE of STUDENT having ROLL_NO 4 is NULL.
 Types of Keys in the Relational Model
1.Primary Key: A Primary Key uniquely identifies each tuple in a relation. It must contain unique values and cannot
have NULL values.

2.Candidate Key: A Candidate Key is a set of attributes that can uniquely identify a tuple in a relation.

3.Super Key: A Super Key is a set of attributes that can uniquely identify a tuple.

4.Foreign Key: A Foreign Key is an attribute in one relation that refers to the primary key of another relation.

5.Composite Key: A Composite Key is formed by combining two or more attributes to uniquely identify a tuple.
 Difference Between Primary Key and Foreign Key
•Definition:

• A primary key uniquely identifies each record in a table.

• A foreign key links two tables by referencing the primary key of another table.
•Purpose:

• A primary key ensures each row in the table is unique.

• A foreign key establishes relationships between tables and enforces referential integrity.
•Uniqueness:

• A primary key must be unique for each record and cannot contain null values.
• A foreign key can have duplicate values and may allow null values, depending on the relationship.
•Location:

• A primary key is defined within the table it belongs to.

• A foreign key is defined in one table but references the primary key of another table.
The main types of keys in the relational model of DBMS are:

Super Key: A set of one or more attributes that, taken collectively, uniquely identify a tuple (row) in a relation
(table). It may have redundant attributes that aren’t needed for uniqueness.

Candidate Key: A minimal super key; it has no unnecessary attributes. There can be multiple candidate keys
in a table. Each candidate key is a possible choice for the primary key.

Primary Key: A chosen candidate key that uniquely identifies each tuple in a table. Each table can have only
one primary key. It cannot have NULL values or duplicates.

Alternate Key: Candidate keys that are not chosen as the primary key. They can still uniquely identify tuples
but aren’t used as the primary means of identification.
A Super Key in DBMS is a group of one or more attributes in a table that can uniquely identify every row in that table. It
ensures no two rows have the same combination of values for those attributes.

Employee_ID Name Department Email •Employee_ID uniquely identifies each

row in the table.
101 Alice HR [email protected]
•Any combination of attributes that
102 Bob IT [email protected] includes Employee_ID will also be a
super key because Employee_ID alone is
sufficient for uniqueness.
103 Charlie IT [email protected]

So, from the above table, we can conclude Super Key Candidate Key
the following set of the super keys: A minimal super key that uniquely
A set of one or more attributes that
identifies a row without any unnecessary
{Employee_ID} uniquely identifies a row in a table.
attributes.
{Employee_ID, Name} It may include extra attributes that are not It is always minimal(contains only the
{Employee_ID, Department} required for uniqueness. necessary attributes for uniqueness).
{Employee_ID, Email}
It also ensures rows are unique but in the
{Employee_ID, Name, Department} Ensures rows are unique in the table.
most efficient way.
{Employee_ID, Name, Email}
A table may have one or more candidate
{Employee_ID, Department, Email} A table can have many super keys.
keys.
{Employee_ID, Name, Department, Email}
Every candidate key is a super key. Not every super key is a candidate key.
Example: {Employee_ID}, {Employee_ID,
Example: {Employee_ID} in Employees
Name}in Employees table where
table.
Employee_ID is candidate key.
Foreign Key: An attribute or set of attributes in one table that refers to the primary key of another table, establishing a
relationship between the two tables. Used to maintain referential integrity.

Composite Key: A key composed of two or more attributes that together uniquely identify a tuple in a relation. Each
attribute alone may not be unique.

Unique Key: Ensures that all values in a column or a group of columns are unique across rows. Unlike the primary key, it
can accept a single NULL value.

Surrogate Key: An artificial key (often a system-generated value, like an auto-increment number) not derived from
application data, used solely to uniquely identify tuples.

Compound Key: A type of composite key where every attribute is a foreign key, commonly used to connect tables in
many-to-many relationships.

These keys are fundamental for ensuring data integrity, organization, and relational connections within a database.
 Database Languages

Database languages are categorized based on their function within the DBMS:
•Data Definition Language (DDL):
Used for defining and modifying the database schema. This includes commands like CREATE (to
create tables, indexes, views), ALTER (to modify existing structures), DROP (to delete
objects), TRUNCATE (to remove all records from a table), COMMENT, and RENAME.
•Data Manipulation Language (DML):
Used for manipulating data within the database. Common DML commands include SELECT (to
retrieve data), INSERT (to add new records), UPDATE (to modify existing records), and DELETE (to
remove records).
•Data Control Language (DCL):
Manages access and permissions within the database. Examples include GRANT (to provide user
permissions) and REVOKE (to remove user permissions).
•Transaction Control Language (TCL):
Manages transactions within the database to ensure data consistency and integrity. Commands
like COMMIT (to save changes), ROLLBACK (to undo changes), and SAVEPOINT (to set a point
within a transaction) fall under TCL.
 Database Systems (DBMS)

A DBMS is a software system that facilitates the creation, organization, management, and retrieval
of data from a database. Key aspects of a DBMS include:
•Data Storage and Organization:
DBMS handles how data is stored on disk, including file organization and indexing, to optimize
retrieval and manipulation.
•Query Processing:
It interprets and executes user queries (expressed in languages like SQL) to retrieve or modify data
efficiently.
•Concurrency Control:
Manages simultaneous access by multiple users to maintain data consistency and prevent
conflicts.
•Security and Access Control:
Implements mechanisms to control who can access what data and what operations they can
perform.
•Data Integrity and Recovery:
Ensures data accuracy and provides mechanisms for recovering data in case of system failures.
 Disadvantages of relational model in
1. Scalability Issues: DBMS
•Relational databases can struggle to scale horizontally, meaning adding more servers to handle increased data or
traffic can be complex and costly.
•As the database grows, performance can degrade, requiring careful optimization
2. Performance Challenges:
•Complex queries involving multiple joins can be slow, especially in large databases.
3. Inflexibility with Complex Data:
•The relational model is designed for structured, tabular data and struggles with unstructured or semi-structured data
like documents or graphs.
4. High Cost:
•Licensing and maintaining relational database management systems (RDBMS) can be expensive, particularly for
large deployments.
•Hardware overheads, such as the need for more powerful servers, can also contribute to increased costs
5. Data Redundancy:
•Without proper normalization, the relational model can lead to data redundancy, where the same data is stored in
multiple tables.
6. Maintenance Overhead:
•As the database grows, managing and optimizing the database, including adding new tables or indexes, can become
time-consuming.
7. Schema Rigidity:
•The relational model requires a predefined schema, which can be inflexible when dealing with changing data
requirements.
 Integrity Rules in DBMS

In DBMS systems, integrity rules are designed to ensure that the quality of data is always high, with no
inconsistencies or errors. The set of principles, also known as the integrity rules or constraints, helps to manage
the data stored in the system in the right way and determine whether it’s suitable for certain purposes.

What are Integrity Rules?

Integrity rules normally represent the pre-existing conditions, or constraints, of data stored in the database
that will ensure the data is valid and consistent. These rules, defining permissible values, relationships, and
data operations enclosed in the database system, ensure the accuracy and reliability of the data to be used for
its purposes. Rules for integrity are considered barriers towards the possibility of some operations over the
database, such as deletion, modification, or insertion of data whose accuracy is doubtful or inconsistent, thus
enabling the database to be of high quality.
Entity Integrity: Ensures that every table has a primary key and that this key is unique and not NULL for
each record. This means each row can be uniquely identified and there are no duplicate or missing
primary key values.
Referential Integrity: Guarantees valid relationships between tables. It requires that any foreign key
value in a child table must either match an existing primary key value in the parent table. This prevents
invalid references and ensures that data across related tables remains consistent.
Domain Integrity: Ensures that all entries in a column fall within a defined valid set (domain), such as
being of the correct data type, length, or format, or within a permitted range. This is often enforced by
setting data types, check constraints, and allowed values for columns.
User-defined Integrity: These are custom rules defined according to specific business rules or
requirements outside the standard constraints. They help enforce additional, organization-specific data
integrity requirements.
 Codd's Rules in DBMS
Codd's rules are proposed by a computer scientist named Dr. Edgar F. Codd and he also invent the relational
model for database management. These rules are made to ensure data integrity, consistency, and usability.
This set of rules basically signifies the characteristics and requirements of a relational database management
system.
Rule 1: The Information Rule
All information, whether it is user information or metadata, that is stored in a database must be entered as a
value in a cell of a table. It is said that everything within the database is organized in a table layout.

Rule 2: The Guaranteed Access Rule

Each data element is guaranteed to be accessible logically with a combination of the table name, primary key
(row value), and attribute name (column value).

Rule 3: Systematic Treatment of NULL Values

Every Null value in a database must be given a systematic and uniform treatment.

Rule 4: Active Online Catalog Rule

The database catalog, which contains metadata about the database, must be stored and accessed using the
same relational database management system.
Rule 5: The Comprehensive Data Sublanguage Rule
A crucial component of any efficient database system is its ability to offer an easily understandable data
manipulation language (DML) that facilitates defining, querying, and modifying information within the
database.

Rule 6: The View Updating Rule

All views that are theoretically updatable must also be updatable by the system.

Rule 7: High-level Insert, Update, and Delete

A successful database system must possess the feature of facilitating high-level insertions, updates, and
deletions that can grant users the ability to conduct these operations with ease through a single query.

Rule 8: Physical Data Independence

Application programs and activities should remain unaffected when changes are made to the physical
storage structures or methods.
Rule 9: Logical Data Independence
Application programs and activities should remain unaffected when changes are made to the logical
structure of the data, such as adding or modifying tables.

Rule 10: Integrity Independence

Integrity constraints should be specified separately from application programs and stored in the catalog.
They should be automatically enforced by the database system.

Rule 11: Distribution Independence

The distribution of data across multiple locations should be invisible to users, and the database system
should handle the distribution transparently.

Rule 12: Non-Subversion Rule

If the interface of the system is providing access to low-level records, then the interface must not be able
to damage the system and bypass security and integrity constraints.
 Relational Algebra

Relational Algebra is a formal language used to query and manipulate relational databases, consisting of a
set of operations like selection, projection, union, and join.

It provides a mathematical framework for querying databases, ensuring efficient data retrieval and
manipulation.

Relational algebra serves as the mathematical foundation for query SQL.

Relational algebra simplifies the process of querying databases and makes it easier to understand and
optimize query execution for better performance.

It is essential for learning SQL because SQL queries are based on relational algebra operations, enabling
users to retrieve data effectively.
 Key Concepts in Relational Algebra

Before explaining relational algebra operations, let's define some fundamental concepts:

1. Relations: In relational algebra, a relation is a table that consists of rows and columns, representing
data in a structured format. Each relation has a unique name and is made up of tuples.

2. Tuples: A tuple is a single row in a relation, which contains a set of values for each attribute. It
represents a single data entry or record in a relational table.

3. Attributes: Attributes are the columns in a relation, each representing a specific characteristic or
property of the data. For example, in a "Students" relation, attributes could be "Name", "Age", and
"Grade".

4. Domains: A domain is the set of possible values that an attribute can have. It defines the type of data
that can be stored in each column of a relation, such as integers, strings, or dates.
 Basic Operators in Relational Algebra

Relational algebra consists of various basic operators that help us to fetch and manipulate data from
relational tables in the database to perform certain operations on relational data. Basic operators are
fundamental operations that include .

selection (σ)
projection (π)
union (U)
set difference (−)
Cartesian product (×)
rename (ρ)
Select (σ)
Select operation is done by Selection Operator which is represented by "sigma"(σ). It is
used to retrieve tuples(rows) from the table where the given condition is satisfied. It is
a unary operator means it requires only one operand.

Notation : σ p(R)
Where σ is used to represent SELECTION
R is used to represent RELATION
p is the logic formula
Let's understand this with an example:
Suppose we want the row(s) from STUDENT Relation where "AGE" is 20
This will return the following output:
σ AGE=20 (STUDENT)
Project (∏)
Project operation is done by Projection Operator which is represented by
"pi"(∏). It is used to retrieve certain attributes(columns) from the table. It is
also known as vertical partitioning as it separates the table vertically. It is also
a unary operator.

Notation : ∏ a(r)
Where ∏ is used to represent PROJECTION
r is used to represent RELATION
a is the attribute list
Let's understand this with an example:
Suppose we want the names of all students from STUDENT Relation.

∏ NAME(STUDENT)
Union (∪)
Union operation is done by Union Operator which is represented by "union"(∪). It is
the same as the union operator from set theory, i.e., it selects all tuples from both
relations but with the exception that for the union of two relations/tables both
relations must have the same set of Attributes. It is a binary operator as it requires two
operands.
Notation: R ∪ S
Where R is the first relation S is the second relation
If relations don't have the same set of attributes, then the union of such relations will
result in NULL.
Let's have an example to clarify the concept:
Suppose we want all the names from STUDENT and EMPLOYEE relation.

As we can see from the above output it also eliminates duplicates.

Set Difference (-)
Set Difference as its name indicates is the difference between two relations (R-S). It is denoted by a "Hyphen"(-) and
it returns all the tuples(rows) which are in relation R but not in relation S. It is also a binary operator.
Notation : R - S
Where R is the first relation S is the second relation
Just like union, the set difference also comes with the exception of the same set of attributes in both relations.

Let's take an example where we would like to know

the names of students who are in STUDENT Relation
but not in EMPLOYEE Relation.

∏ NAME(STUDENT) - ∏ NAME(EMPLOYEE)
Cartesian product (X)
Cartesian product is denoted by the "X" symbol. Let's say we have two relations R and S. Cartesian product will combine
every tuple(row) from R with all the tuples from S.

Notation: R X S
Where R is the first relation
S is the second relation
As we can see from the notation it is also a binary operator.

Let's combine the two relations STUDENT and EMPLOYEE.

STUDENT X EMPLOYEE
Rename (ρ)
Rename operation is denoted by "Rho"(ρ). As its name suggests it is used to rename the output relation. Rename
operator too is a binary operator.
Notation: ρ(R,S)
Where R is the new relation name
S is the old relation name
Let's have an example to clarify this
Suppose we are fetching the names of students from STUDENT relation. We would like to rename this relation as
STUDENT_NAME.

ρ(STUDENT_NAME,∏ NAME(STUDENT))
We have learned:

Select (σ) is used to retrieve tuples(rows) based on certain conditions.

Project (∏) is used to retrieve attributes(columns) from the relation.

Union (∪) is used to retrieve all the tuples from two relations.

Set Difference (-) is used to retrieve the tuples which are present in R but not in S(R-S).

Cartesian product (X) is used to combine each tuple from the first relation with each tuple from the second
relation.

Rename (ρ) is used to rename the output relation.

 Derived Operations

Also known as extended operations, these operations can be derived from basic operations and hence named Derived Operations. These
include three operations: Join Operations, Intersection operations, and Division operations.

Join Operations
Join Operation in DBMS are binary operations that allow us to combine two or more relations.
They are further classified into two types: Inner Join, and Outer Join.

First, let's have two relations EMPLOYEE consisting of E_NO, E_NAME, CITY and EXPERIENCE .
EMPLOYEE table contains employee's information such as id, name, city, and experience of employee(In Years).
The other relation is DEPARTMENT consisting of D_NO, D_NAME, E_NO and MIN_EXPERIENCE.

DEPARTMENT table defines the mapping of an employee to their department. It contains Department Number, Department Name,
Employee Id of the employee working in that department, and the minimum experience required(In Years) to be in that department.
Theta Join (θ)

Theta Join combines two relations using a condition. This condition is represented by the symbol "theta"(θ). Here
conditions can be inequality conditions such as >,<,>=,<=, etc.
Notation : R ⋈θ S
Where R is the first relation
S is the second relation

Let's have a simple example to understand this.

Suppose we want a relation where EXPERIENCE from EMPLOYEE >= MIN_EXPERIENCE from DEPARTMENT.

EMPLOYEE⋈θ EMPLOYEE.EXPERIENCE>=DEPARTMENT.MIN_EXPERIENCE DEPARTMENT

Equi Join

Equi Join is a special case of theta join where the condition can only contain **equality(=)** comparisons.
A non-equijoin is the inverse of an equi join, which occurs when you join on a condition other than "=".

Let's have an example where we would like to join EMPLOYEE and DEPARTMENT relation where E_NO from EMPLOYEE =
E_NO from DEPARTMENT.

EMPLOYEE ⋈EMPLOYEE.E_NO = DEPARTMENT.E_NO DEPARTMENT

Natural Join (⋈)
A comparison operator is not used in a natural join. It does not concatenate like a Cartesian product. A Natural Join can be
performed only if two relations share at least one common attribute. Furthermore, the attributes must share the same
name and domain.
Natural join operates on matching attributes where the values of the attributes in both relations are the same and remove
the duplicate ones.
Preferably Natural Join is performed on the foreign key.

Notation : R ⋈ S

Where R is the first relation S is the second relation

Let's say we want to join EMPLOYEE and DEPARTMENT relation with E_NO as a common attribute.
Notice, here E_NO has the same name in both the relations and also consists of the same domain, i.e., in both relations
E_NO is a string.

EMPLOYEE ⋈ DEPARTMENT
Outer Join
Unlike Inner Join which includes the tuple that satisfies the given condition, Outer Join also includes some/all the tuples
which don't satisfy the given condition. It is also of three types: Left Outer Join, Right Outer Join, and Full Outer Join.
Let's say we have two relations R and S, then
Below is the representation of Left, Right, and Full Outer Joins.
Left Outer Join

As we can see from the diagram, Left Outer Join returns the matching tuples(tuples present in both relations) and
the tuples which are only present in Left Relation, here R.

However, if the matching tuples are NULL, then attributes/columns of Right Relation, here S are made NULL in the
output relation.

Let's understand this a bit more using an example:

EMPLOYEE ⟕EMPLOYEE.E_NO = DEPARTMENT.E_NO DEPARTMENT

Here we are combining EMPLOYEE and DEPARTMENT relation with the constraint that EMPLOYEE's E_NO must be
equal to DEPARTMENT's E_NO.
Right Outer Join

Right Outer Join returns the matching tuples and the tuples which are only present in Right Relation here S.

The same happens with the Right Outer Join, if the matching tuples are NULL, then the attributes of Left Relation, here R
are made NULL in the output relation.

We will combine EMPLOYEE and DEPARTMENT relations with the same constraint as above.

EMPLOYEE ⟖EMPLOYEE.E_NO = DEPARTMENT.E_NO DEPARTMENT

Full Outer Join

Full Outer Join returns all the tuples from both relations. However, if there are no matching tuples then, their
respective attributes are made NULL in output relation.

Again, combine the EMPLOYEE and DEPARTMENT relation with the same constraint.

EMPLOYEE ⟗EMPLOYEE.E_NO = DEPARTMENT.E_NO DEPARTMENT

What is Relational Calculus?

Before understanding Relational calculus in DBMS, we need to understand different types of Languages.

1. Procedural Language - Those Languages which clearly define how to get the required results from the Database are
called Procedural Language. Relational algebra is a Procedural Language.

2. Declarative Language - Those Language that only cares about What to get from the database without getting into
how to get the results are called Declarative Language.
• It focuses on the desired result's properties rather than the step-by-step procedure, unlike procedural languages.
• Relational calculus is a non-procedural (or declarative) query language

• So Relational Calculus is a Declarative Language that uses mathematical predicate logic or First-Order Logic to
determine the results from Database.

• It exists in two forms: Tuple Relational Calculus (TRC), which uses variables that range over tuples (rows), and
Domain Relational Calculus (DRC), which uses variables that range over the domain of attribute values (columns).
Tuple Relational Calculus (TRC) is a non-procedural query language used to retrieve data from relational databases by
describing the properties of the required data (not how to fetch it).
It is based on first-order predicate logic and uses tuple variables to represent rows of tables.

Syntax: The basic syntax of TRC is as follows:

{ t | P(t) }
t: Tuple variable (row placeholder)
P(t): Predicate condition to satisfy
{}: Denotes a set of result tuples

Logical Operators in TRC:

∧ : AND (conjunction or logical AND symbol, indicating that two statements are
connected and both must be true for the combined statement to be true. In logic)
∨ : OR (logical OR or disjunction and represents the logical operation where a
statement is true if at least one of the connected statements is true)
¬ : NOT (negation symbol or logical not which reverses the truth value of a statement
from true to false or false to true)
Quantifiers:
∃ t ∈ r (Q(t)) → There exists a tuple t in relation r satisfying predicate Q(t)
∀ t ∈ r (Q(t)) → For all tuples t in relation r, predicate Q(t) holds

∃ - Existential Quantifier
∀- Universal Quantifier
Tuple Relational Calculus (TRC)
Tuple Relational Calculus in DBMS uses a tuple variable (t) that goes to each row of the table and checks if the predicate
is true or false for the given row. Depending on the given predicate condition, it returns the row or part of the row.

The Tuple Relational Calculus expression Syntax

{t | P(t)}

Where t is the tuple variable that runs over every Row, and P(t) is the predicate logic expression or condition.
Let's take an example of a Customer Database and try to see how TRC expressions work.
Example 1: Write a TRC query to get all the data of customers whose zip code is 12345.
TRC Query: {t | t ∈ Customer ∧ t.Zipcode = 12345}
or
TRC Query: {t | Customer(t) ∧ t[Zipcode] = 12345 }
Workflow of query - The tuple variable "t" will go through every tuple of the Customer table. Each row will check whether
the Cust_Zipcode is 12345 or not and only return those rows that satisfies the Predicate expression condition.
The TRC expression above can be read as "Return all the tuple which belongs to the Customer Table and whose Zipcode
is equal to 12345."
Result of the TRC expression above: Customer_id Name Zip code

Customer_id Name Zip code

1 Rohit 12345

1 Rohit 12345 2 Rahul 13245

4 Amit 12345 3 Rohit 56789

4 Amit 12345
Example 2: Write a TRC query to get the customer id of all the Customers.

TRC query: { t | ∃ s ∈ (Customer ∧ s.Customer_id = t.customer_id) }

Result of the TRC Query:

Customer_id
1
2
3
4
Domain Relational Calculus (DRC)
Domain Relational Calculus uses domain Variables to get the column values required from the database
based on the predicate expression or condition.

The Domain realtional calculus expression syntax: {<x1,x2,x3,x4...> | P(x1,x2,x3,x4...)}

where, <x1,x2,x3,x4...> are domain variables used to get the column values required, and P(x1,x2,x3...) is
predicate expression or condition.

Let's take the example of Customer Database and try to understand DRC queries with some examples.
Example 1: Write a DRC query to get the data of all customers with Zip code 12345.

DRC query: {<x1,x2,x3> | <x1,x2> ∈ Customer ∧ x3 = 12345 }

Workflow of Query: In the above query x1,x2,x3 (ordered) refers to the attribute or column which we need in
the result, and the predicate condition is that the first two domain variables x1 and x2 should be present
while matching the condition for each row and the third domain variable x3 should be equal to 12345.
Example 2: Write a DRC query to get the customer id of all the customer.

DRC Query: { <x1> | ∃ x2,x3(<x1,x2,x3> ∈ Customer ) }

Result of the above Query will be:

 Introduction to SQL

SQL is a standard language for accessing and manipulating databases.

What is SQL?
•SQL stands for Structured Query Language
•SQL lets you access and manipulate databases
•SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of
the International Organization for Standardization (ISO) in 1987

What Can SQL do?

•SQL can execute queries against a database
•SQL can retrieve data from a database
•SQL can insert records in a database
•SQL can update records in a database
•SQL can delete records from a database
•SQL can create new databases
•SQL can create new tables in a database
•SQL can create stored procedures in a database
•SQL can create views in a database
•SQL can set permissions on tables, procedures, and views
 Features of Structured Query Language (SQL)

Here are some key features of Structured Query Language (SQL):

1.Data Definition Language (DDL): SQL provides a set of commands to define and modify the structure of a database,
including creating tables, modifying table structure, and dropping tables.

2.Data Manipulation Language (DML): SQL provides a set of commands to manipulate data within a database, including
adding, modifying, and deleting data.

3.Query Language: SQL provides a rich set of commands for querying a database to retrieve data, including the ability to
filter, sort, group, and join data from multiple tables.

4.Transaction Control: SQL supports transaction processing, which allows users to group a set of database operations into a
single transaction that can be rolled back in case of failure.

5.Data Integrity: SQL includes features to enforce data integrity, such as the ability to specify constraints on the values that
can be inserted or updated in a table, and to enforce referential integrity between tables.

6.User Access Control: SQL provides mechanisms to control user access to a database, including the ability to grant and
revoke privileges to perform certain operations on the database.

7.Portability: SQL is a standardized language, meaning that SQL code written for one database management system can be
used on another system with minimal modification.
 SQL Languages
1. DDL (Data Definition Language)
The DDL is used to define the database's internal structure and Pattern of the Database. It is used to define and
modify the structure of the database itself, including the tables, views, indexes and other schema-related objects. It deals
with the creation and modification of database schema, but it doesn't deal with the data itself. Following are the five DDL
commands in SQL:
•CREATE: Used to create database objects like tables, indexes or views.

•ALTER: Used to modify the structure of an existing database object, such as adding a new column to a table.

•DROP: Used to delete database objects.

•TRUNCATE: Used to remove all rows from a table, without affecting the structure.
2. DCL (Data Control Language)
DCL is used to control the access permissions of users to the database. DCL commands help grant or revoke
privileges to users, determining who can perform actions like reading or modifying data. DCL commands are
transactional, meaning they can be rolled back if necessary. The two main DCL commands are:
•Grant: Gives user access to the database, it is used to provide specific permissions or privileges to users or roles. This
command allows administrators to control access to database objects, ensuring that only authorized users can perform
certain actions, such as selecting, inserting, updating or deleting data.

•Revoke: Removes access or permissions from the user, remove previously granted permissions or privileges from users
or roles
3. DML (Data Manipulation Language)

The DML is used to manage and manipulate data within a database. With DML, you can perform various operations
such as inserting, updating, selecting and deleting data. These operations allow you to work with the actual content in your
database tables. Here are the key DML commands:
•SELECT: Retrieves data from the table based on specific criteria.

•INSERT: Adds new rows of data into an existing table.

•UPDATE: Modifies existing data in a table.

•DELETE: Removes data from a table.

4. TCL ( Transaction Control Language )

TCL commands are used to manage and control transactions in a database, grouping them into logical units. These
commands help ensure the integrity of data and consistency during complex operations. Here are the two main commands in
this category:
•Commit: Saves all the changes made during the current transaction to the database. These are very useful in the banking
sector. This command ensures that modifications made by DML statements (such as INSERT, UPDATE or DELETE) become
permanent in the database.

•Rollback: used to restore the database to its original state from the last commit. This command also plays an important role in
Banking Sectors.
 various SQL operations based on languages
SQL operations are categorized into several sub-languages, each serving a distinct purpose in
database management:

•Data Definition Language (DDL):

Used for defining and modifying the structure of a database.
•CREATE: Creates database objects like tables, views, and indexes.
•ALTER: Modifies the structure of existing database objects.
•DROP: Deletes database objects.
•TRUNCATE: Removes all records from a table, effectively emptying it while keeping the table
structure.

•Data Manipulation Language (DML):

Used for manipulating data within database objects.
•SELECT: Retrieves data from a database.
•INSERT: Adds new records into a table.
•UPDATE: Modifies existing records in a table.
•DELETE: Removes records from a table.
•Data Query Language (DQL):
Often considered a subset of DML, it focuses specifically on data retrieval.
•SELECT: The primary command for querying data.

•Data Control Language (DCL):

Manages access and permissions to the database.
•GRANT: Provides specific privileges to users.
•REVOKE: Removes previously granted privileges from users.

•Transaction Control Language (TCL):

Manages transactions within the database to ensure data integrity.
•COMMIT: Saves all changes made during a transaction permanently.
•ROLLBACK: Undoes all changes made during a transaction, reverting to the last committed
state.
•SAVEPOINT: Sets a point within a transaction to which you can later roll back.
 Assignment-2, question no-4

4. Using the following schema answer the following queries in Relational Algebra: Suppliers (sid, sname, saddress) Parts
(pid, pname, color) Catalog (sid, pid, cost)
(i). List the the names of suppliers who supply blue part
ii). List the supplier details who supply “Bolts” colored “Red”
iii)List the supplier details and part details of those whose cost is less than $30.
iv) List the part details that are supplied by supplier whose name starts with “S”
Answer :

Suppliers (sid, sname, saddress) Parts (pid, pname, color) Catalog (sid, pid, cost)

(i). List the the names of suppliers who supply blue part

πsname ( (σcolor='Blue’ (Parts)) ⨝ Catalog ⨝ Suppliers )

Explanation:
•Select parts where color is 'Blue'
•Join with Catalog to get supplier IDs
•Join with Suppliers to get supplier names
•Project only sname
ii). List the supplier details who supply “Bolts” colored “Red”
π sid, sname, saddress ( (σ pname='Bolts' ∧ color='Red’ (Parts)) ⨝ Catalog ⨝ Suppliers )
Explanation:
•Select parts named 'Bolts' and colored 'Red'
•Join with Catalog and Suppliers
•Project full supplier details
iii)List the supplier details and part details of those whose cost is less than $30.

π (σ cost<30 (Suppliers ⨝ Catalog ⨝ Parts))

sid, sname, saddress, pid, pname, color
Explanation:
•Join all three relations
•Select where cost < 30
•Project both supplier and part details
iv) List the part details that are supplied by supplier whose name starts with “S”
π pid, pname, color ( (σ sname LIKE 'S%')(Suppliers) ⨝ Catalog ⨝ Parts )
Explanation:
•Select suppliers whose name starts with 'S'
•Join with Catalog and Parts
•Project part details