DATABASE MANAGEMENT SYSTEM (DBMS) & SQL NOTES

What is Data?
Data refers to raw facts and figures that have not been processed.
Examples
 Student Name: Anu
 Age: 22
 Roll No: 101
These individual facts are called data.
What is Information?
Information is processed data that is meaningful.
Example
Name Marks

Anu 90

John 85
The statement:
"Anu scored the highest mark."
is information.
What is a Database?
A database is an organized collection of related data stored electronically.
Example
College Database
 Student Details
 Faculty Details
 Course Details
 Marks Details
All these are stored together in a database.
Advantages
  Easy storage
 Fast retrieval
 Data sharing
 Reduced redundancy
 Security

What is DBMS?
DBMS (Database Management System) is software used to create, manage, and
manipulate databases.
Examples
 MySQL
 Oracle Database
 Microsoft SQL Server
 PostgreSQL
 MongoDB

Functions of DBMS
1. Data Storage
Stores data permanently.
2. Data Retrieval
Retrieves required data quickly.
3. Data Security
Protects data from unauthorized access.
4. Backup and Recovery
Recovers data after failure.
5. Data Integrity
Maintains correctness of data.
ACID Properties of DBMS
ACID ensures that database transactions are reliable.
A – Atomicity
A transaction is completed fully or not at all.
Example: Money transfer happens completely or is cancelled.
C – Consistency
The database remains correct before and after a transaction.
Example: Total money in the bank remains the same after a transfer.
I – Isolation
Multiple transactions do not affect each other.
Example: Two users can use the database at the same time without conflicts.
D – Durability
Once a transaction is saved, it remains permanently even after a system crash.
Example: A completed online payment record is not lost.

Types of Databases
1. Hierarchical Database
Data is stored in tree structure.
Example
Company
|
Manager
|
Employee
Advantage
Fast access.
Disadvantage
Complex relationships difficult.
2. Network Database
Data is connected through multiple relationships.
Example
A student can enroll in multiple courses.

3. Relational Database (RDBMS)

Data is stored in tables.
Example
Student Table

ID Name

101 Ann

Course Table

ID Course

1 MCA

Most widely used database model.

4. Object-Oriented Database
Stores objects instead of tables.
Used in software development.

SQL vs NoSQL
SQL Database
SQL databases store data in tables.
Examples
 MySQL
 Oracle Database
 Microsoft SQL Server
  PostgreSQL
Characteristics
 Table structure
 Fixed schema
 ACID properties
 Uses SQL language

NoSQL Database
NoSQL means "Not Only SQL".
Stores data differently from tables.
Examples
 MongoDB
 Cassandra
 Redis
Characteristics
 Flexible schema
 Handles big data
 High scalability

SQL vs NoSQL Comparison

Feature SQL NoSQL

Storage Tables Documents/Key-Value

Schema Fixed Flexible

Query Language SQL Various APIs

Scalability Vertical Horizontal

Examples MySQL MongoDB

What is SQL?
SQL stands for Structured Query Language.
Used to communicate with relational databases.
Uses
 Create databases
 Create tables
 Insert records
 Update records
 Delete records
 Retrieve records

Applications of SQL
Banking
Customer records.
Education
Student management systems.
E-commerce
Product and order management.
Hospitals
Patient records.
Social Media
User information management.

SQL Database Systems

MySQL
Open-source database.
Oracle
Enterprise-level database.
SQL Server
Microsoft database system.
PostgreSQL
Advanced open-source database.
SQLite
Lightweight embedded database.

Schema and Instance

Schema
Structure of database.
Example
Student Table:
| ID | Name | Age |
This design is schema.

Instance
Actual data stored at a particular time.
Example

ID Name Age

101 Anu 22

This data is instance.

SQL Data Types

Numeric Data Types
INT
Stores integers.
Age INT
FLOAT
Stores decimal values.
Salary FLOAT
DECIMAL
Stores exact decimal values.
Price DECIMAL(10,2)

Character Data Types

CHAR
Fixed length.
Gender CHAR(1)
VARCHAR
Variable length.
Name VARCHAR(50)

Date and Time Data Types

DATE
DOB DATE
TIME
TIME
DATETIME
DATETIME

SQL Command Categories

DDL (Data Definition Language)
Used to define database objects.
Commands:
 CREATE
 ALTER
 DROP
 TRUNCATE
 RENAME
Example
CREATE TABLE Student(
ID INT,
Name VARCHAR(50)
);

DML (Data Manipulation Language)

Used to manipulate records.
Commands:
 INSERT
 UPDATE
 DELETE
Example
INSERT INTO Student
VALUES(101,'Ann');

DQL (Data Query Language)

Used to retrieve data.
Command:
SELECT
Example
SELECT * FROM Student;
DCL (Data Control Language)
Controls permissions.
Commands:
 GRANT
 REVOKE

TCL (Transaction Control Language)

Transaction management.
Commands:
 COMMIT
 ROLLBACK
 SAVEPOINT

SQL Operators
Arithmetic Operators
Operator Meaning

+ Addition

- Subtraction

* Multiplication

/ Division

% Modulus
Example
SELECT 10+5;

Comparison Operators
Operator Meaning

= Equal

> Greater

< Less

>= Greater Equal

<= Less Equal

<> Not Equal

Example
SELECT * FROM Student
WHERE Age > 20;

Logical Operators
Operator Meaning

AND Both conditions true

OR Any condition true

NOT Reverse result

Example
SELECT *
FROM Student
WHERE Age > 20
AND Marks > 80;

SQL Clauses
1. WHERE Clause
Used to filter records based on a condition.
Syntax
SELECT * FROM Student
WHERE Age > 20;
Example
SELECT * FROM Student
WHERE Marks > 80;
Use
Returns only records that satisfy the condition.

2. ORDER BY Clause
Used to sort records.
Syntax
SELECT * FROM Student
ORDER BY Name;
Descending Order
SELECT * FROM Student
ORDER BY Marks DESC;
Use
Sorts data in ascending or descending order.

3. GROUP BY Clause
Groups rows having the same values.
Example
SELECT Department, COUNT(*)
FROM Employee
GROUP BY Department;
Use
Creates groups for calculations.
4. HAVING Clause
Filters grouped data.
Example
SELECT Department, COUNT(*)
FROM Employee
GROUP BY Department
HAVING COUNT(*) > 5;
Use
Applies conditions on groups.

5. DISTINCT Clause
Removes duplicate values.
Example
SELECT DISTINCT City
FROM Student;
Use
Displays unique values only.

6. LIMIT Clause
Restricts the number of rows returned.
Example
SELECT * FROM Student
LIMIT 5;
Use
Returns first 5 records.

7. Alias (AS)
Gives temporary names to columns or tables.
Example
SELECT Name AS Student_Name
FROM Student;
Use
Improves readability.

SQL Table Operations

Create Table
CREATE TABLE Student(
ID INT,
Name VARCHAR(50),
Age INT
);
Use
Creates a new table.

Rename Table
ALTER TABLE Student
RENAME TO Students;
Use
Changes table name.

Drop Table
DROP TABLE Student;
Use
Deletes table permanently.

Truncate Table
TRUNCATE TABLE Student;
Use
Deletes all rows but keeps table structure.

INSERT Statement
Insert Single Row
INSERT INTO Student
VALUES(101,'Ann',22);

Insert Multiple Rows

INSERT INTO Student
VALUES
(101,'Ann',22),
(102,'John',21),
(103,'Mary',23);

SELECT Statement
Select All Columns
SELECT * FROM Student;

Select Specific Columns

SELECT Name, Age
FROM Student;

UPDATE Statement
Used to modify existing records.
Example
UPDATE Student
SET Age = 23
WHERE ID = 101;
Use
Updates existing data.
DELETE Statement
Deletes records.
Example
DELETE FROM Student
WHERE ID = 101;
Use
Removes selected records.

Delete Duplicate Rows

Example using DISTINCT:
CREATE TABLE NewTable AS
SELECT DISTINCT *
FROM Student;

SQL Operators (Advanced)

LIKE Operator
Used for pattern matching.
Example
SELECT * FROM Student
WHERE Name LIKE 'A%';
Meaning

Symbol Meaning

% Any number of characters

_ Single character

IN Operator
Checks multiple values.
Example
SELECT *
FROM Student
WHERE City IN ('Kochi','Delhi');

NOT IN Operator
SELECT *
FROM Student
WHERE City NOT IN ('Kochi');

BETWEEN Operator
Checks a range.
Example
SELECT *
FROM Student
WHERE Age BETWEEN 18 AND 25;

IS NULL
Finds NULL values.
SELECT *
FROM Student
WHERE Email IS NULL;

IS NOT NULL
SELECT *
FROM Student
WHERE Email IS NOT NULL;

UNION
Combines results and removes duplicates.
SELECT Name FROM Student
UNION
SELECT Name FROM Employee;

UNION ALL
Combines results including duplicates.
SELECT Name FROM Student
UNION ALL
SELECT Name FROM Employee;

EXCEPT
Returns records from first query not present in second.
SELECT Name FROM Student
EXCEPT
SELECT Name FROM Employee;

ANY Operator
Condition must be true for at least one value.
SELECT *
FROM Employee
WHERE Salary > ANY
(SELECT Salary FROM Employee);

ALL Operator
Condition must be true for all values.
SELECT *
FROM Employee
WHERE Salary > ALL
(SELECT Salary FROM Employee
WHERE Department='HR');
SQL Functions
Functions perform calculations and data manipulation.
Types
1. Single Row Functions
2. Aggregate Functions

Numeric Functions
ABS()
Returns absolute value.
SELECT ABS(-20);
Output: 20

CEIL()
Rounds up.
SELECT CEIL(10.2);
Output: 11

FLOOR()
Rounds down.
SELECT FLOOR(10.9);
Output: 10

ROUND()
Rounds number.
SELECT ROUND(10.56,1);
Output: 10.6
String Functions
UPPER()
Converts to uppercase.
SELECT UPPER('ann');
Output: ANN

LOWER()
Converts to lowercase.
SELECT LOWER('ANU');
Output: anu

LENGTH()
Returns string length.
SELECT LENGTH('Database');
Output: 8

TRIM()
Removes spaces.
SELECT TRIM(' SQL ');
Output: SQL

RTRIM()
Removes right-side spaces.
SELECT RTRIM('SQL ');

LTRIM()
Removes left-side spaces.
SELECT LTRIM(' SQL');

CONCAT()
Joins strings.
SELECT CONCAT('Anu',' John’);
Output: Ann John

SQL Aggregate Functions

Aggregate functions perform calculations on multiple rows and return a single
value.
Function Purpose

COUNT() Counts rows

SUM() Calculates total

AVG() Calculates average

MIN() Finds smallest value

MAX() Finds largest value

COUNT()
Counts the number of records.
Example
SELECT COUNT(*) FROM Student;
Use
Counts total students.

SUM()
Returns total value.
Example
SELECT SUM(Marks)
FROM Student;
Use
Calculates total marks.

AVG()
Returns average value.
Example
SELECT AVG(Marks)
FROM Student;
Use
Calculates average marks.

MIN()
Returns minimum value.
Example
SELECT MIN(Marks)
FROM Student;
Use
Finds lowest mark.

MAX()
Returns maximum value.
Example
SELECT MAX(Marks)
FROM Student;
Use
Finds highest mark.
Regular Expressions (REGEXP)
Used for pattern matching.
Example
Names starting with A
SELECT *
FROM Student
WHERE Name REGEXP '^A';
Symbols

Symbol Meaning

^ Starts with

$ Ends with

. Any character

* Zero or more occurrences

+ One or more occurrences

SQL Constraints
Constraints are rules applied to columns to maintain data integrity.

NOT NULL Constraint

Column cannot contain NULL values.
Example
CREATE TABLE Student(
ID INT,
Name VARCHAR(50) NOT NULL
);
Use
Ensures data is always entered.
UNIQUE Constraint
Prevents duplicate values.
Example
CREATE TABLE Student(
Email VARCHAR(50) UNIQUE
);
Use
No two students can have the same email.

PRIMARY KEY Constraint

Uniquely identifies each row.
Example
CREATE TABLE Student(
ID INT PRIMARY KEY,
Name VARCHAR(50)
);
Characteristics
 Unique
 Not NULL
 One primary key per table

FOREIGN KEY Constraint

Creates relationship between tables.
Example
CREATE TABLE Department(
DeptID INT PRIMARY KEY
);

CREATE TABLE Student(

ID INT PRIMARY KEY,
DeptID INT,
FOREIGN KEY(DeptID)
REFERENCES Department(DeptID)
);
Use
Maintains referential integrity.

COMPOSITE KEY
Primary key made of multiple columns.
Example
CREATE TABLE Enrollment(
StudentID INT,
CourseID INT,
PRIMARY KEY(StudentID, CourseID)
);
Use
Uniquely identifies records using multiple columns.

ALTERNATE KEY
Candidate keys not chosen as primary key.
Example
Student(ID, Email)
If ID is Primary Key, Email becomes Alternate Key.

CHECK Constraint
Restricts values.
Example
CREATE TABLE Student(
Age INT CHECK(Age >=18)
);
Use
Prevents invalid data.

DEFAULT Constraint
Assigns default value.
Example
CREATE TABLE Student(
City VARCHAR(20)
DEFAULT 'Kochi'
);
Use
Stores default value if no value is provided.

AUTO INCREMENT
Automatically generates numbers.
Example (MySQL)
CREATE TABLE Student(
ID INT AUTO_INCREMENT,
Name VARCHAR(50),
PRIMARY KEY(ID)
);
Use
Generates IDs automatically.

SEQUENCE
Used to generate number sequences.
Example (Oracle/PostgreSQL)
CREATE SEQUENCE StudentSeq
START WITH 1
INCREMENT BY 1;
Use
Generates unique numbers.

SQL JOINS
Joins combine records from multiple tables.
Example Tables
Student

ID Name DeptID

1 Ann 10

2 John 20

Department

DeptID DeptName

10 MCA

20 MBA

INNER JOIN
Returns matching records from both tables.
SELECT Name, DeptName
FROM Student
INNER JOIN Department
ON [Link] = [Link];
Output

Name DeptName

Ann MCA

John MBA

LEFT JOIN
Returns all rows from left table and matching rows from right table.
SELECT *
FROM Student
LEFT JOIN Department
ON [Link]=[Link];
Use
Keeps all student records.

RIGHT JOIN
Returns all rows from right table and matching rows from left table.
SELECT *
FROM Student
RIGHT JOIN Department
ON [Link]=[Link];
Use
Keeps all department records.

FULL OUTER JOIN

Returns all records from both tables.
SELECT *
FROM Student
FULL OUTER JOIN Department
ON [Link]=[Link];
Use
Shows matched and unmatched records.

CROSS JOIN
Returns Cartesian Product.
SELECT *
FROM Student
CROSS JOIN Department;
Example
2 Students × 2 Departments = 4 Rows

SELF JOIN
A table joins with itself.
Example
Employee Table

EmpID Name ManagerID

1 Alex NULL

2 John 1

SELECT [Link], [Link]

FROM Employee E
JOIN Employee M
ON [Link]=[Link];
Use
Find employee-manager relationships.

UPDATE Using JOIN

UPDATE Student S
JOIN Department D
ON [Link]=[Link]
SET [Link]='Active';

DELETE Using JOIN

DELETE S
FROM Student S
JOIN Department D
ON [Link]=[Link]
WHERE [Link]='MBA';
Key Purpose

Primary Key Unique identification

Foreign Key Links tables

Composite Key Multiple columns form key

Alternate Key Candidate key not selected

Unique Key Prevents duplicates

NORMALIZATION
What is Normalization?
Normalization is the process of organizing data in a database to reduce
redundancy (duplicate data) and improve data integrity.
Example (Before Normalization)

StudentID Name Course1 Course2

101 Ann MCA BCA

Problems:
 Repeated data
 Difficult to update
 Wastes storage
Normalization divides data into smaller related tables.

Why Use Normalization?

1. Reduces Data Redundancy
Avoids storing the same data multiple times.
2. Improves Data Consistency
Data remains accurate throughout the database.
3. Saves Storage Space
Eliminates duplicate records.
4. Simplifies Maintenance
Updates are easier.
5. Improves Data Integrity
Maintains correctness of data.

Types of Normalization
First Normal Form (1NF)
Rule
 Each column contains only atomic (single) values.
 No repeating groups.
Not in 1NF
StudentID Name Courses

101 Ann MCA, BCA

In 1NF
StudentID Name Course

101 Ann MCA

101 Ann BCA

Benefit
Removes repeating groups.

Second Normal Form (2NF)

Rule
 Table must be in 1NF.
 No partial dependency.
Example
StudentID CourseID StudentName
Here StudentName depends only on StudentID.
Move StudentName to a separate Student table.
Benefit
Reduces redundancy.

Third Normal Form (3NF)

Rule
 Table must be in 2NF.
 No transitive dependency.
Example
StudentID DeptID DeptName
DeptName depends on DeptID, not directly on StudentID.
Create separate Department table.
Benefit
Removes unnecessary dependencies.

Boyce-Codd Normal Form (BCNF)

Rule
Every determinant must be a candidate key.
BCNF is a stronger version of 3NF.
Benefit
Removes remaining anomalies.

Normal Forms Summary

Normal Form Purpose

1NF Remove repeating groups

2NF Remove partial dependency

3NF Remove transitive dependency

BCNF Every determinant must be a candidate key

KEYS IN DBMS
Keys are used to identify records uniquely and establish relationships between
tables.

Super Key
A Super Key is a set of one or more attributes that uniquely identifies a record.
Example
Student Table
RollNo Name Email
Possible Super Keys:
 RollNo
 Email
 RollNo + Name
 RollNo + Email
Definition
A Super Key uniquely identifies each row.

Candidate Key
A Candidate Key is a minimal Super Key.
Example
Student Table
RollNo Name Email
Candidate Keys:
 RollNo
 Email
Not Candidate Key:
 RollNo + Name
Because RollNo alone is sufficient.
Definition
A Candidate Key is the smallest possible Super Key.

Difference Between Super Key and Candidate Key

Candidate Key

May contain extra attributes No extra attributes

Uniquely identifies record Minimal unique identifier

Many possible Few possible

Example
Student(RollNo, Name, Email)
Super Keys:
 RollNo
 Email
 RollNo + Name
Candidate Keys:
 RollNo
 Email

TRANSACTION IN DBMS
A transaction is a sequence of SQL operations performed as a single unit of
work.
Example
Bank Transfer
Withdraw ₹1000 from Account A
Deposit ₹1000 to Account B
Both operations together form a transaction.

Transaction Control Language (TCL)

Used to manage transactions.
Commands:
1. COMMIT
2. ROLLBACK
3. SAVEPOINT

COMMIT
Permanently saves changes.
Example
INSERT INTO Student
VALUES(101,'Ann');

COMMIT;
Use
Makes changes permanent.

ROLLBACK
Cancels changes made after the last COMMIT.
Example
DELETE FROM Student
WHERE ID=101;

ROLLBACK;
Use
Restores previous state.

SAVEPOINT
Creates a point within a transaction.
Example
SAVEPOINT S1;
Use
Allows partial rollback.

Rollback to Savepoint
ROLLBACK TO S1;
Use
Returns to a specific savepoint without cancelling the entire transaction.

Transaction Example
UPDATE Account
SET Balance = Balance - 1000
WHERE AccNo=1;

SAVEPOINT S1;

UPDATE Account
SET Balance = Balance + 1000
WHERE AccNo=2;

COMMIT;
If an error occurs after SAVEPOINT S1, we can execute:
ROLLBACK TO S1;

Short Exam Definitions

Normalization
The process of organizing data to reduce redundancy and improve consistency.
Super Key
A set of attributes that uniquely identifies a record.
Candidate Key
A minimal Super Key that uniquely identifies a record.
Transaction
A group of SQL operations executed as a single unit of work.
COMMIT
Permanently saves changes.
ROLLBACK
Undoes changes made in a transaction.
SAVEPOINT
Creates a point to which a transaction can be rolled back.