DataBase Management System

UNIT 2
Relational Model ,Basic SQL

1.1 Introduction to relational model

 The relational Model was proposed by E.F. Codd to model data in the form of relations (tables).
 Each relation is a collection of columns and rows, where the column represents the attributes of
an entity and the rows (or tuples) represents the records.
 The use of tables to store the data provided a straightforward, efficient, and flexible
 Dr [Link], also known as the ‘Father of Database Management
1.2concepts of domain, attribute, tuple, relation
In the context of databases, specifically relational databases, the terms domain, attribute, tuple, and
relation have specific meanings.
1. Domain
A domain refers to the set of permissible values that an attribute (column) can take.
It defines the data type and constraints on the data that can be stored in a particular attribute.
Example:
If we have an attribute "Age", the domain of "Age" could be the set of all integers between 0
and 120, or more simply, it could be defined as "non-negative integers."
Key points:
A domain specifies the allowed values for an attribute.
It can include data types like integers, strings, dates,
etc.
It can also include constraints, such as valid ranges or specific formats.
What is the 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.
Roll_No Name Address Phone Age
24KX1A3001 Akhila Ananthapuramu 94973421311 17
238P1A3002 Bhaskar Chittoor 94973421313 18
24KX1A3003 Charan Bengaluru 94973422122 17
24KX1A3004 David Hyderabad 18
24KX1A3005 Fayaz New Delhi 64932232422 18

Page 1 of 23
DataBase Management System

Student Table

Page 2 of 23
DataBase Management System

Important Terminologies
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:
24KX1A3001 Akhila Ananthapuramu 94973421311 17
Relation Instance:
The set of tuples of a relation at a particular instance of time is called a relation instance.
The above table shows the relation instance of STUDENT at a particular time. 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.
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.
Roll_No

24KX1A3001

24KX1A3002

24KX1A3003

24KX1A3004

24KX1A3005

These concepts form the foundation of the relational model of databases, where data is structured
tables (relations) with rows (tuples) and columns (attributes), with each column constrained by a domain.
Page 3 of 23
DataBase Management System

1.3 importance of null values

 Null values are a crucial concept in relational databases and play an important role in
data management.
 In relational theory, a null represents the absence of a value, an unknown value, or a value that is
inapplicable. It's different from an empty string, zero, or a default value, as null specifically
indicates that no data is present.
Here’s an exploration of the importance and uses of null values:

1. Representation of Missing Data

 Real-World Scenarios: Null values allow databases to represent missing or unknown information.
 For example, if an employee has not provided their phone number, that field can be set to
null, meaning "unknown" or "not applicable."
Example:
 An employee's Date of Birth may be unknown for some records. Rather than leaving it blank or
entering an arbitrary value, you can store it as null to indicate that the data is not yet provided or
is unknown.
 Importance: Without null values, we would have to use placeholder values (like zero or empty
strings), which could lead to misunderstanding of data and loss of meaning.

2. Distinguishing Between 'No Data' and 'Zero Data'

 Null vs. Zero: Null helps to differentiate between an actual value of zero (0) and the absence of
a value. For example, in a sales database:
 A null value for "Sales Amount" might indicate that no sale was recorded for the day.
 A zero value (0) would indicate that a sale was made, but the total amount of the sale was zero.
 Importance: Using null allows for more accurate and nuanced data interpretation, preventing
false conclusions about the data (such as mistakenly assuming no sale occurred when zero was
entered).
3. Handling Optional Attributes
 In many cases, attributes are optional and not every row will have a value for every attribute.
Nulls provide an elegant way to indicate this.
 Example: In a database of books, an attribute like "Book's Cover Image URL" may be optional. If
no cover image is available for a particular book, you can store null in that column rather than
leaving it blank or entering an incorrect placeholder.
 Importance: Nulls allow the database to support optional or incomplete data without causing errors

Page 4 of 23
DataBase Management System

or confusion.

Page 5 of 23
DataBase Management System

4. Avoiding Data Overload and Redundancy

 Sometimes, using null values helps avoid redundancy in the data. If a value is not applicable to
a particular record, storing null is often more space-efficient and conceptually correct than using
redundant placeholders like "N/A" or empty strings.
 Example: If an attribute like "Social Security Number" is not applicable to a person (e.g.,
someone from outside the U.S.), it’s better to store null than to store a placeholder like "000-00-
0000."
 Importance: Null values reduce unnecessary repetition in the database and help maintain clarity .
1.4 constraints (Domain, Key constraints, integrity constraints)
In the context of databases, these terms refer to the rules and conditions that rule how data is stored, organized,
and maintained in a relational database management system (RDBMS).
1. Domain
Constraints:
Definition:
A domain defines the set of permissible values that a column in a table can have. It specifies the
acceptable data type and, sometimes, the format or range of values.
Examples:
For a column storing ages, the domain may define that only integer values between 0 and 90 are
allowed.
For a column storing dates, the domain might restrict entries to valid date formats (e.g., YYYY-MM-DD).
Purpose:
Domain constraints ensure that the data inserted into a column is appropriate for that column's intended
purpose and conforms to expected formats or ranges.
Example:
If you have a column called salary in an employee table, the domain constraint could ensure that
values in the salary column are positive numeric values, with no salary being less than 0.
2. Key Constraints:
Definition:
A key is a column (or set of columns) that uniquely identifies each row in a table. Key constraints are rules
that enforce uniqueness and ensure that each value (or combination of values) in the key column(s) is
unique.
Types of Key Constraints:
Primary Key:
A primary key constraint ensures that a column (or set of columns) has unique values for every row and
cannot contain NULLs. It is the main way of identifying rows in a table.
Page 6 of 23
DataBase Management System

Example: A table Students might have a primary key constraint on the student_id column to ensure each
student has a unique ID.

Page 7 of 23
DataBase Management System

Unique Key:
Similar to the primary key, but unlike the primary key, a table can have multiple unique key
constraints. It ensures that all values in the constrained column(s) are unique, but unlike the primary
key, it can accept NULL values (depending on the RDBMS).
Example: A Students table might have a unique constraint on the email column to ensure no two students
have the same email address.
Foreign Key:
A foreign key is a column (or set of columns) that creates a relationship between two tables.
It points to the primary key or unique key in another table, ensuring referential integrity.
Example:
A Courses table might have a foreign key student_id that refers to the primary key in the Students
table. This ensures that only students who exist in the Students table can be enrolled in a course.
Purpose:
Key constraints ensure that each row can be uniquely identified, maintain data consistency, and
support relationships between different tables.
3. Integrity Constraints:
Definition:
Integrity constraints are rules that ensure the accuracy and consistency of data in a relational database.
They enforce valid relationships between tables and data values.
Types of Integrity Constraints:
Entity Integrity:
This type of integrity is maintained through the primary key constraint. It ensures that each row in a
table is uniquely identifiable and that the primary key column(s) cannot contain NULL values.
Example: In a Students table, the student_id must not be NULL and must be unique for each student.
Referential Integrity:
This type of integrity is maintained through foreign key constraints.
It ensures that a value in a foreign key column corresponds to a valid value in the referenced primary
key or unique key column of another table.
Example: In an Enrollments table, the student_id foreign key must refer to an existing student_id in the
Students table, ensuring that every enrollment record corresponds to an actual student.
Domain Integrity:
This type of integrity is maintained through domain constraints that ensure values in a column are
within a specified set of valid values or conform to a particular data type.
Example: A birth_date column in a Students table might have a domain integrity constraint ensuring that the

Page 8 of 23
DataBase Management System

values are valid dates within an appropriate range.

Page 9 of 23
DataBase Management System

Summary Table
Constr Description Example
aint
Type
Domain Specifies valid data types, ranges, or formats for a Age (between 0 and 120), valid email
Constraints column's values. format.

Ensures uniqueness and identifies rows in a table. Key

Primary key (student_id), Foreign key
Key constraints include Primary Key, Unique Key, and
(course_id).
Cons Foreign Key.
train
ts
Enforces rules to maintain the accuracy and Entity Integrity (unique, non-null
Integrity
consistency of data. Includes Entity, Referential, primary key), Referential Integrity
Constraints
Domain, and User-Defined Integrity constraints. (foreign key checks).

1.5 Relational Algebra, Relational Calculus

Relational algebra is a procedural query language. It gives a step by step process to obtain the result of the
query. It uses operators to perform queries.

Types of Relational operation

Types of Relational Operation

SELECT OPERATION
 The select operation selects tuples that satisfy a given predicate.
 It is denoted by sigma (σ).
 Notation: σ p(r)
Where:
 σ is used for selection prediction
 r is used for relation
 p is used as a propositional logic formula which may use connectors like: AND OR and
NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.
Page 10 of 23
DataBase Management System

For example: LOAN Relation

Branch_Name Loan-No Amount

Madanapalle L-1123 23000
Chittoor L-3243 340000
Punganur L-2143 45000
Ananthapuramu L-0976 347000
Tirupati L-6640 54000
Punganur L-7655 670000
Input:
σ BRANCH_NAME="Punganur" (LOAN)
Output:
Branch_Name Loan-No Amount
Punganur L-2143 45000
PROJECT OPERATION
Punganur L-7655 670000
 This operation shows the list of those attributes that we wish to appear in the result. Rest of the
attributes are eliminated from the table.
 It is denoted by ∏.
 Notation: ∏ A1, A2, An (r)
Where
 A1, A2, A3 is used as an attribute name of relation r.
Example: CUSTOMER RELATION
Name Street City
Rajesh Teachers Colony Madanapalle
Kumar Swamy Society Colony Tirupati
Rajesh Chowdary Nehru Nagar Ongole
Sanjay Shinde Gandhi Nagar Mumbai
Input:
∏ NAME, CITY (CUSTOMER)
Output:
Name City
Rajesh Madanapalle
Kumar Swamy Tirupati
Rajesh Chowdary Ongole
Sanjay Shinde Mumbai

Page 11 of 23
DataBase Management System

UNION OPERATION

 Suppose there are two tuples R and S. The union operation contains all the tuples that are either in R
or S or both in R & S.
 It eliminates the duplicate tuples. It is denoted by ∪.
 Notation: R ∪ S
A union operation must hold the following condition:
 R and S must have the attribute of the same number.
 Duplicate tuples are eliminated automatically.
Example:
Depositor Relation
Customer_Name Account_No
MD. Shafi A-1234
Kumar Swamy A-4567
Rajesh Chowdary A-9876
Sanjay Shinde A-3424
Borrow Relation
Customer_Name Loan-No
Feroz L-435
Nageswar L-876
Sanjay Shinde L-453
Sankar L-332
Input:
∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)
Output:
Customer_Name
Rajesh
Kumar Swamy
Rajesh Chowdary
Sanjay Shinde
Feroz
Nageswar
Sankar

Page 12 of 23
DataBase Management System

SET INTERSECTION
 Suppose there are two tuples R and [Link] set intersection contains all tuples that are in both R & S.
 It is denoted by intersection ∩.
Notation: R ∩ S
Example: Using the above DEPOSITOR table and BORROW table
Input:
∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)
Output:
Customer_Name
Sanjay Shinde
SET DIFFERENCE
 Suppose there are two tuples R and S. The set difference operation contains all tuples that are in R but not
in S.
 It is denoted by intersection minus (-).
Notation: R - S
Example: Using the above DEPOSITOR table and BORROW table
Input:
∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)
Output:
Customer_Name
Rajesh
Kumar Swamy
Rajesh Chowdary
CARTESIAN PRODUCT

 The Cartesian product is used to combine each row in one table with each row in the other table. It is also
known as a cross product.
 It is denoted by X.
Notation: E X D
Example:
Employee Relation
Emp_ID Emp_Name Emp_Dept
VJAM135 [Link] AID
VJAM154 D. Siva Sankar CSE
VJAM176 K. Lakshmaiah ECE

Page 13 of 23
DataBase Management System

VJAM121 U. Mastan Raju H&S

Page 14 of 23
DataBase Management System

Department Relation
Dept_No Dept_Name
AID Artificial Intelligence & Data Science
CSE Computer Science & Engineering
ECE Electronics & Communication Engineering
H&S Humanities & Sciences
Input:
EMPLOYEE X DEPARTMENT
Output:
Emp_ID Emp_Name Emp_Dept Dept_No Dept_Name
Artificial Intelligence & Data
VJAM135 [Link] AID AID
Science
VJAM154 D. Siva Sankar CSE CSE Computer Science & Engineering
Electronics & Communication
VJAM176 K. Lakshmaiah ECE ECE
Engineering
VJAM121 U. Mastan Raju H&S H&S Humanities & Sciences

B) RELATIONAL CALCULUS
 There is an alternate way of formulating queries known as Relational Calculus.
 Relational calculus is a non-procedural query language.
 In the non-procedural query language, the user is concerned with the details of how to obtain the end
results.
 The relational calculus tells what to do but never explains how to do
 Relational Calculus is based on Predicate calculus, a name derived from branch of symbolic language.
 A predicate is a truth-valued function with arguments.
 On substituting values for the arguments, the function result in an expression called a proposition.
 It can be either true or false. It is a modified version of a subset of the Predicate Calculus to
communicate with the relational database.
Many of the calculus expressions involves the use of Quantifiers.
There are two types of quantifiers:
1. Universal Quantifiers: The universal quantifier denoted by ∀ is read as for all which means that in a
given set of tuples exactly all tuples satisfy a given condition.
2. Existential Quantifiers: The existential quantifier denoted by ∃ is read as for all which means that in
a given set of tuples there is at least one occurrences whose value satisfy a given condition.

Page 15 of 23
DataBase Management System

We need to know the concept of Free and Bound Variables.

 A tuple variable ‘t’ is bound if it is quantified which means that if it appears in any occurrences a
variable that is not bound is said to be free.
 Free and bound variables may be compared with global and local variable of programming languages.
Types of Relational Calculus

Types of Relational Calculus

Tuple Relational Calculus (TRC)
 It is a non-procedural query language which is based on finding a number of tuple variables also
known as range variable for which predicate holds true.
 It describes the desired information without giving a specific procedure for obtaining that information.
 The tuple relational calculus is specified to select the tuples in a relation.
 In TRC, filtering variable uses the tuples of a relation.
 The result of the relation can have one or more tuples.
Notation:A Query in the tuple relational calculus is expressed as following notation
{T | P (T)} or {T | Condition (T)}
Where
 T is the resulting tuples
 P(T) is the condition used to fetch T.
Example:
{ [Link] | Author(T) AND [Link] = 'database' }
Output:
This query selects the tuples from the AUTHOR relation. It returns a tuple with 'name' from Author who has
written an article on 'database'.
Tuple Relation Calculus) can be quantified. In TRC, we can use Existential (∃) and Universal Quantifiers (∀).

{ R| ∃T ∈ Authors([Link]='database' AND [Link]=[Link])}

Example:

Output:

Page 16 of 23
DataBase Management System

This query will yield the same result as the previous one.

Page 17 of 23
DataBase Management System

Domain Relational Calculus (DRC)

 The second form of relation is known as Domain relational calculus.
 In domain relational calculus, filtering variable uses the domain of attributes.
 Domain relational calculus uses the same operators as tuple calculus.
 It uses logical connectives ∧ (and), ∨ (or) and ┓ (not). It uses Existential (∃) and Universal
Quantifiers (∀) to bind the variable.
 The QBE or Query by example is a query language related to domain relational calculus.
Notation:

{ a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}

Where

a1, a2 are attributes

P stands for formula built by inner attributes

{< article, page, subject > | ∈ javatpoint ∧ subject = 'database'}

Example:

Output:
This query will yield the article, page, and subject from the relational javatpoint, where the subject is a
database.
BASIC SQL
1.6 Simple Database schema
 A database schema is the logical representation of a database, which shows how the data is
stored logically in the entire database.
 It contains list of attributes and instruction that informs the database engine that how the data
is organized and how the elements are related to each other.
 A database schema contains schema objects that may include tables, fields, packages, views,
relationships, primary key, foreign key.
 In actual, the data is physically stored in files that may be in unstructured form, but to retrieve it
and use it, we need to put it in a structured form. To do this, a database schema is used.
 It provides knowledge about how the data is organized in a database and how it is linked with
other data.
 The schema does not physically contain the data itself; instead, it gives information about
the shape of data and how it can be related to other tables or models.
A database schema object includes the following:
 Consistent formatting for all data entries.
 Database objects and unique keys for all data entries.
 Tables with multiple columns, and each column contains its name and datatype.
Page 18 of 23
DataBase Management System

1.7 data types

SQL Data Types
In SQL, data types define the kind of data that can be stored in a column of a table.
Common SQL data types:
1. Numeric Data Types
These data types are used to store numeric values, including integers and floating-point numbers.
 INTEGER / INT: Stores whole numbers (no decimal points).
Example: INT, INTEGER , Range (for most DBMS): -2,147,483,648 to 2,147,483,647
 SMALLINT: Stores smaller integers.
Example: SMALLINT , Range: -32,768 to 32,767
 BIGINT: Stores large integers.
Example: BIGINT , Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
 DECIMAL / NUMERIC: Stores exact numeric values with a fixed number of digits before and
after the decimal point.
Example: DECIMAL(10, 2) stores a number with up to 10 digits, 2 of which are after the decimal point
(e.g., 12345678.90).
Precision refers to the total number of digits, and scale refers to the number of digits after the decimal
point.
 FLOAT / REAL: Stores floating-point numbers, which are numbers with a decimal
point. REAL: Typically stores 4-byte floating point values.
 DOUBLE PRECISION: Stores floating-point numbers with double the precision of FLOAT.
Example: DOUBLE PRECISION
2. String Data Types
These data types are used to store text or character-based data.
CHAR (or) CHARACTER): Fixed-length character string. If the string is shorter than the specified length,
it is padded with spaces.
Example: CHAR(10) stores a 10-character string (padded with spaces if fewer than 10 characters are entered).
VARCHAR (or CHARACTER VARYING):
Variable-length character string. The length can be defined, but the string will only occupy as much space
as it needs.
Example: VARCHAR(255) stores a string of up to 255 characters.
TEXT:
Stores large amounts of text. This is often used for fields where the length of the string is unknown or
variable.

Page 19 of 23
DataBase Management System

Example: TEXT (No need to specify a length in many DBMS).

3. Date and Time Data Types
 These data types are used to store date and time information.
 DATE: Stores the date (year, month, day) without
time. Example: DATE (e.g., 2024-12-03).
 TIME: Stores the time (hours, minutes, seconds) without a date.
Example: TIME (e.g., 14:30:00).
 DATETIME: Stores both date and time (without time
zone). Example: DATETIME (e.g., 2024-12-03
14:30:00).
 TIMESTAMP: Stores both date and time (may include time zone depending on the
DBMS). Example: TIMESTAMP (e.g., 2024-12-03 14:30:00.123456).
 TIME WITH TIMEZONE / TIMESTAMPTZ: Stores the time with the time zone, or stores
a timestamp with time zone information.
Example: TIMESTAMPTZ (e.g., 2024-12-03 14:30:00+01:00).
4. Boolean Data Types
Used to store truth values.
 BOOLEAN: Stores TRUE or FALSE values.
Example: BOOLEAN (can store TRUE, FALSE, or NULL depending on the DBMS).
 BIT: Stores binary data, often used for flags or small binary values.
Example: BIT(1) stores a single bit, often used for Boolean-like
values.

Choosing the Right Data Type:

 Storage Efficiency: Use appropriate types that match your data requirements. For example,
use SMALLINT instead of INT for smaller numeric ranges to save space.
 Precision: For financial or other critical calculations, use DECIMAL instead of FLOAT to
avoid rounding errors.
 Performance: Use data types that are optimized for your queries. For example, using VARCHAR for
variable-length strings saves space over CHAR.

Page 20 of 23
DataBase Management System

1.8 table definitions (create, alter)

Tables are the primary structures in a database where data is stored.
Creating and altering tables are fundamental operations in SQL.
Creating a Table
The CREATE TABLE statement is used to define a new table. CREATE
TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
BirthDate DATE,
HireDate DATE,
Salary FLOAT
);
Altering a Table
The ALTER TABLE statement is used to modify an existing table. This
can include adding, modifying, or dropping columns.
Add a Column:
ALTER TABLE Employees ADD COLUMN Email VARCHAR(100);
Modify a Column:
ALTER TABLE Employees MODIFY COLUMN Salary DOUBLE;
Drop a Column:
ALTER TABLE Employees DROP COLUMN Email;

1.9 different DML operations (insert, delete,

update) DATA MANIPULATION LANGUAGE (DML) OPERATIONS
DML operations are used to manage data within tables.
The primary DML statements are INSERT, DELETE, and UPDATE. Inserting a
Data: The INSERT statement is used to add new rows to a table.
INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, HireDate, Salary) VALUES
(1, 'Ram', 'MD', '1974-07-01', '2005-05-01', 80000);
Deleting Data: The DELETE statement is used to remove rows from a table.
DELETE FROM Employees WHERE EmployeeID = 1;
Updating Data: The UPDATE statement is used to modify existing rows in a table.
UPDATE EmployeesSET Salary = 65000WHERE EmployeeID = 1;

Page 21 of 23
DataBase Management System

Page 22 of 23
DataBase Management System

Page 23 of 23