Srinivasa Institute of Engineering & Technology (A)

UNIT-II

Relational Model:

The relational model represents how data is stored in Relational Databases. A relational
database consists of a collection of tables each of which is assigned a unique name. Consider
a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE, and AGE
shown in the table.

Table STUDENT:

• Domain: a set of values that can be stored in a column of a database table. A

domain is usually defined by a column's data type, which determines the kind of
values that can be stored in the column.
• Attribute: Attributes are the properties that define an entity. e.g. ROLL_NO,
NAME, ADDRESS.

• Relation Schema: A relation schema defines the structure of the relation and
represents the name of the relation with its attributes. e.g. 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.

• Tuple: Each row in the relation is known as a tuple. The above relation contains
4 tuples one of which is shown as:

1 RAM DELHI 9455123451 18

• 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.

• Degree: The number of attributes in the relation is known as the degree of

the relation. The STUDENT relation defined above has degree 5.

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

• Cardinality: The number of tuples in a relation is known as cardinality. The

STUDENT relation defined above has cardinality 4.

• Column: The column represents the set of values for a particular attribute.
The column ROLL_NO is extracted from the relation STUDENT.

• NULL Values: The value which is not known or unavailable is called a NULL
value. It is represented by NULL. e.g. PHONE of STUDENT having ROLL_NO 4 is
NULL.

• Relation Key: These are basically the keys that are used to identify the rows
uniquely or also help in identifying tables. These are of the following types:

1. Primary Key

2. Foreign Key

Importance of NULL Value:

It is essential to understand that a NULL value differs from a zero or an empty string. A
NULL value represents missing or undefined data. Since it is often not possible to
determine which interpretation applies, SQL treats all NULL values as distinct and does not
distinguish between them. Typically, it can have one of three interpretations:

1. Value Unknown: The value exists but is not known.

2. Value Not Available: The value exists but is intentionally withheld.

3. Attribute Not Applicable: The value is undefined for a specific record.

Constraints in DBMS-

• They ensure the correctness or consistency of data in the database.

Integrity Constraints:

• Integrity constraints are a set of rules. It is used to maintain the quality of information.
• Integrity constraints ensure that the data insertion, updating, and other processes
have to be performed in such a way that data integrity is not affected.
• They ensure the correctness or consistency of data in the database.

Types of Integrity Constraint:

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

1. Domain constraints:

• Domain constraints can be defined as the definition of a valid set of values for
an attribute.
• The data type of domain includes string, character, integer, time, date, currency, etc.
The value of the attribute must be available in the corresponding domain.

Example:

2. Entity integrity constraints:

• The entity integrity constraint states that primary key value can't be null.
• This is because the primary key value is used to identify individual rows in
relation and if the primary key has a null value, then we can't identify those rows.
• A table can contain a null value other than the primary key field.

Example:

3. Referential Integrity Constraints:

• A referential integrity constraint is specified between two tables.

• In the Referential integrity constraints, if a foreign key in Table 1 refers to the
Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be
null or be available in Table 2.

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

Example:

4. Key constraints:

• Keys are the entity set that is used to identify an entity within its entity set uniquely.
• An entity set can have multiple keys, but out of which one key will be the
primary key. A primary key can contain a unique and null value in the relational
table.

Example:

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

Relational Algebra:
Relational algebra is a procedural query language that works on relational model. The
purpose of a query language is to retrieve data from database or perform various operations
such as insert, update, and delete on the data.
Basic/Fundamental Operations:

1. Select (σ)
2. Project (∏)
3. Union (∪)
4. Set Difference (-)
5. Cartesian product (X)
6. Rename (ρ)

Derived Operations:

2. Left, Right, Full outer join (𝔴, ⟖, 𝔴)

1. Natural Join (⋈)

3. Intersection (∩)

Select Operator (σ):

Select Operator is denoted by sigma (σ) and it is used to find the tuples (or rows) in a
relation (or table) which satisfy the given condition.

Syntax of Select Operator (σ)

σ Condition/Predicate(Relation/Table name)
Select Operator (σ) Example
Table: CUSTOMER

Customer_Id Customer_Name Customer City

C10100 Steve Agra

C10111 Raghu Agra
C10115 Chaitanya Noida
C10117 Ajeet Delhi
C10118 Carl Delhi

Query:

σ Customer City="Agra" (CUSTOMER)

Output:

Customer_Id Customer_Name Customer_City

C10100 Steve Agra

C10111 Raghu Agra

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

Project Operator (∏)

Project operator is denoted by ∏ symbol and it is used to select desired columns (or
attributes) from a table (or relation).

Project operator in relational algebra is similar to the Select statement in SQL.

In this example, we have a table CUSTOMER with three columns, we want to fetch only two
columns of the table, which we can do with the help of Project Operator ∏.

Table: CUSTOMER

Customer_Id Customer_Name Customer_City

C10100 Steve Agra

C10111 Raghu Agra
C10115 Chaitanya Noida
C10117 Ajeet Delhi
C10118 Carl Delhi

Query:

∏ Customer_Name, Customer_City (CUSTOMER)

Output:

Customer_Name Customer_City

Steve Agra
Raghu Agra
Chaitanya Noida
Ajeet Delhi
Carl Delhi

Union Operator (∪)

Union operator is denoted by ∪ symbol and it is used to select all the rows (tuples) from two
tables (relations).

Syntax of Union Operator (∪)

table_name1 ∪ table_name2
Union Operator (∪) Example

Table 1: COURSE

Course_Id Student_Name Student_Id

C101 Aditya S901

C104 Aditya S901
C106 Steve S911
C109 Paul S921
C115 Lucy S931

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

Table 2: STUDENT

Student_Id Student_Name Student_Age

S901 Aditya 19
S911 Steve 18
S921 Paul 19
S931 Lucy 17
S941 Carl 16
S951 Rick 18

Query:

∏ Student Name (COURSE) ∪ ∏ Student Name (STUDENT)

Output:

Student Name

Aditya
Carl
Paul
Lucy
Rick
Steve

Note: As you can see there are no duplicate names present in the output even though we had
few common names in both the tables, also in the COURSE table we had the duplicate
name itself.

Intersection Operator (∩)

Intersection operator is denoted by ∩ symbol and it is used to select common rows (tuples)
from two tables (relations).

Lets say we have two relations R1 and R2 both have same columns and we want to select all
those tuples(rows) that are present in both the relations, then in that case we can apply
intersection operation on these two relations R1 ∩ R2.

Note: Only those rows that are present in both the tables will appear in the result set.

Syntax of Intersection Operator (∩)

table_name1 ∩ table_name2
Intersection Operator (∩) Example

Table 1: COURSE

Course_Id Student_Name Student_Id

C101 Aditya S901

C104 Aditya S901
C106 Steve S911
C109 Paul S921
C115 Lucy S931

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

Table 2: STUDENT

Student_Id Student_Name Student_Age

S901 Aditya 19
S911 Steve 18
S921 Paul 19
S931 Lucy 17
S941 Carl 16
S951 Rick 18

Query:

∏ Student_Name (COURSE) ∩ ∏ Student_Name (STUDENT)

Output:

Student_Name

Aditya
Steve
Paul
Lucy

Set Difference (-)

Set Difference is denoted by – symbol. Lets say we have two relations R1 and R2 and we
want to select all those tuples(rows) that are present in Relation R1 but not present in
Relation R2, this can be done using Set difference R1 – R2.

Syntax of Set Difference (-)

table_name1 - table_name2
Set Difference (-) Example
∏ Student_Name (STUDENT) - ∏ Student_Name (COURSE)

Output:

Student_Name

Carl
Rick

Cartesian product (X)

Cartesian Product is denoted by X symbol. Lets say we have two relations R1 and R2 then
the cartesian product of these two relations (R1 X R2) would combine each tuple of first
relation R1 with the each tuple of second relation R2. I know it sounds confusing but once
we take an example of this, you will be able to understand this.

Syntax of Cartesian product (X)

R1 X R2

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

Cartesian product (X) Example

Table 1: R

Col_A Col_B

AA 100
BB 200
CC 300

Table 2: S

Col_X Col_Y
----- -----
XX 99
YY 11
ZZ 101

Query:
Let’s find the Cartesian product of table R and S.

R X S

Output:

Col_A Col_B Col_X Col_Y

AA 100 XX 99
AA 100 YY 11
AA 100 ZZ 101
BB 200 XX 99
BB 200 YY 11
BB 200 ZZ 101
CC 300 XX 99
CC 300 YY 11
CC 300 ZZ 101

Note: The number of rows in the output will always be the cross product of number of rows
in each table. In our example table 1 has 3 rows and table 2 has 3 rows so the output has 3×3
= 9 rows.

Rename (ρ)

Rename (ρ) operation can be used to rename a relation or an attribute of a relation.

Rename (ρ) Syntax:
ρ(new_relation_name, old_relation_name)

Rename (ρ) Example

Lets say we have a table customer, we are fetching customer names and we are renaming the
resulted relation to CUST_NAMES.

Table: CUSTOMER

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

Customer_Id Customer_Name Customer_City

C10100 Steve Agra

C10111 Raghu Agra
C10115 Chaitanya Noida
C10117 Ajeet Delhi
C10118 Carl Delhi

Query:

ρ(CUST_NAMES, ∏(Customer_Name)(CUSTOMER))

Output:

CUST_NAMES

Steve
Raghu
Chaitanya
Ajeet
Carl

Join Operations:

Join operation denoted by ⋈.

JOIN operation also allows joining variously related tuples from different relations.

A JOIN clause is used to combine rows from two or more tables, based on a related column
between them.

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

• INNER JOIN: Returns records that have matching values in both tables
• LEFT OUTER JOIN: Returns all records from the left table, and the
matched records from the right table
• RIGHT OUTER JOIN: Returns all records from the right table, and the
matched records from the left table
• FULL OUTER JOIN: Returns all records when there is a match in either left or
right table

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

Relational Calculus:

Relational calculus is a non-procedural query language that tells the system what data to be
retrieved but doesn’t tell how to retrieve it.

1. Tuple Relational Calculus (TRC)

Tuple relational calculus is used for selecting those tuples that satisfy the given condition.
Table: Student

First_Name Last_Name Age

Ajeet Singh 30
Chaitanya Singh 31
Rajeev Bhatia 27
Carl Pratap 28

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

Let’s write relational calculus queries.

Query to display the last name of those students where age is greater than 30

{t.Last_Name | Student (t) AND t.age > 30 }

In the above query you can see two parts separated by | symbol. The second part is where we
define the condition and in the first part we specify the fields which we want to display for
the selected tuples.

The result of the above query would be:

Last_Name

Singh

2. Domain Relational Calculus (DRC)

In domain relational calculus the records are filtered based on the domains.
Again we take the same table to understand how DRC works.
Table: Student

First_Name Last_Name Age

Ajeet Singh 30
Chaitanya Singh 31
Rajeev Bhatia 27
Carl Pratap 28

Query to find the first name and age of students where student age is greater than 27

{< First_Name, Age > | ∈ Student ∧ Age > 27}

Output:

First_Name Age

Ajeet 30
Chaitanya 31
Carl 28

Database Schema:

A database schema is the design or structure of a database that defines how data is organized
and how different data elements relate to each other.

• It defines how data is logically organized, including tables, fields, and relationships.

• It outlines the relationships between entities, such as primary and foreign keys.

• Database schemas guide how data is accessed, modified, and maintained.

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

In simple terms, the schema provides the framework that makes it easier to understand,
manage, and use data in a database. It’s created by database designers to ensure the data is
consistent and efficiently organized.

Types of Database Schemas

Physical Database Schema:

• A physical schema defines how data is stored in the storage system, including the
arrangement of files, indices and other storage structures. It specifies the actual
code and syntax needed to create the database structure. Essentially, it determines
where and how the data is stored in the physical storage medium.

• The database administrator decides the storage locations and organization of data
within the storage blocks. This schema represents the lowest level of abstraction

Logical Database Schema:

• A logical database schema defines the logical structure of the data, including
tables, views, relationships, and integrity constraints. It describes how data is
organized in tables and how the attributes of these tables are connected. The logical
schema ensures that the data is stored in an organized manner, while maintaining
data integrity.

• Using Entity-Relationship (ER) modelling, the logical schema outlines the

relationships between different data components. It also defines integrity
constraints to ensure the quality of data during insertion and updates.

• This schema represents a higher level of abstraction compared to the physical

schema, focusing on logical constraints and how the data is structured, without
dealing with the physical storage details.

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

View Database Schema:

• The view schema is the highest level of abstraction in a database, focusing on

how users interact with the database. It defines the interface through which users
can access and manipulate data, without needing to understand the underlying
storage mechanisms.

• A database can have multiple view schemas, also known as subschemas, each
providing a different perspective of the data. These schemas describe only a part of
the database.

Creating Database Schema:

For creating a schema, the statement “CREATE SCHEMA” is used in every database. But
different databases have different meanings for this.

1. MySQL: In MySQL, we use the “CREATE SCHEMA” statement for creating the
database, because, in MySQL CREATE SCHEMA and CREATE DATABASE, both
statements are similar.

SQL Data Types:

Each column in a database table is required to have a name and a data type.

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

Numeric Data Types in SQL:

Numeric data types store all numerical values or integer values

Data Type Description Range/Size

Stores whole numbers. Commonly

INT -2,147,483,648 to 2,147,483,647
Used

Used for floating-point numbers

FLOAT which are approximate numeric -1.79E + 308 and 1.79E + 308
values.

-9,223,372,036,854,775,808 to
BIGINT Stores larger whole numbers
9,223,372,036,854,775,807

SMALLINT Stores smaller whole numbers -32,768 to 32,767

TINYINT Stores very small whole numbers 0 to 255

Stores fixed-precision decimal

DECIMAL/NUMERIC Up to 38 digits
numbers

Character Data Types in SQL:

Character data types store all alphabetic values and special characters.

Data Type Description Range/Size

Stores fixed-length text of n

CHAR Up to 8,000 characters
character

Stores variable-length text of up

VARCHAR Up to 8,000 characters or MAX
to n characters

Stores large text data.(use

TEXT Up to 2GB
VARCHAR(MAX) instead)

Stores fixed-length Unicode text

NCHAR Up to 4,000 charcaters
of n characters

Stores variable-length. Unicode

NVARCHAR Up to 4,000 characters
text of upto n characters

Stores large Unicode text

NTEXT data(use NVARCHAR(MAX) Up to 2 GB
instead)

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

Date and Time Data Types in SQL:

A date or a date/time value is stored in the Date and Time data types.

Data Type Description Range/Size

Date Stores date only(no time) 0001-01-01 to 9999-12-31

00:00:00.0000000 to
Time Stores time only(no date)
23:59:59:9999999

Date Time Stores date and time 1753-01-01 to 9999-12-31

Stores date and time which is 0001-01-01 00:00:00 to 9999-

Timestamp
specific to time zones 12-31 23:59:59.9999999

SQL Table:

Table is a collection of data, organized in terms of rows and columns. In DBMS term, table is
known as relation and row as tuple.

Table is the simple form of data storage. A table is also considered as a convenient
representation of relations.

Employee
EMP_NAME ADDRESS SALARY
Ankit Lucknow 15000
Raman Allahabad 18000
Mike New York 20000

In the above table, "Employee" is the table name, "EMP_NAME", "ADDRESS" and
"SALARY" are the column names. The combination of data of multiple columns forms a row
e.g. "Ankit", "Lucknow" and 15000 are the data of one row.

SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database.

Syntax:
CREATE TABLE table name (
column1 datatype,
column2 datatype,
column3 datatype,

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

....
);

The column parameters specify the names of the columns of the table.

The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer,
date, etc.).

The following example creates a table called "Persons" that contains five columns: Person
ID, Last Name, First Name, Address, and City:

Example:
CREATE TABLE Persons (
Person ID int,
Last Name varchar (255),
First Name varchar (255),
Address varchar (255),
City varchar (255)
);

The Person ID column is of type int and will hold an integer.

The Last Name, First Name, Address, and City columns are of type varchar and will hold
characters, and the maximum length for these fields is 255 characters.

The empty "Persons" table will now look like this:

Person ID Last Name First Name Address City

SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing
table.

ALTER TABLE - ADD Column

To add a column in a table, use the following syntax:

ALTER TABLE table name

ADD column name datatype;

The following SQL adds an "Email" column to the "Customers" table:

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

Example:
ALTER TABLE Customers
ADD Email varchar (255);

ALTER TABLE - DROP COLUMN:

To delete a column in a table, use the following syntax (notice that some database systems
don't allow deleting a column):

ALTER TABLE table name

DROP COLUMN column name;

The following SQL deletes the "Email" column from the "Customers" table:

Example:
ALTER TABLE Customers
DROP COLUMN Email;

DML Operations:

SQL SELECT Statement:

The SELECT statement is used to select data from a database.

Example:

Return data from the Customers table:

SELECT Customer Name, City FROM Customers;

The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

Example:

The following SQL statement inserts a new record in the "Customers" table:

INSERT INTO Customers (Customer Name, City, Postal Code, Country)

VALUES ('Ram', 'AMP', '5332021', 'India’);

The selection from the "Customers" table will now look like this:

Customer Name City Postal Code Country

Ram AMP 533201 India

DBMS- Unit-II Department of aiml

Srinivasa Institute of Engineering & Technology (A)

The SQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

Example: Customers

Customer ID Customer Name City Postal Code Country

01 Ram AMP 533201 India

UPDATE Customers
SET Customer Name = Raju, City= 'KKD'
WHERE Customer ID = 1;

Customer ID Customer Name City Postal Code Country

01 Raju KKD 533201 India

DBMS- Unit-II Department of aiml