Database Concept-RDBMS Notes Class 12 IT-802 | CBSE Exam Preparation

Database Concept-RDBMS Notes for Class 12 IT (802) include comprehensive and well-structured notes covering all topics with examples. These notes are specially designed according to the latest CBSE syllabus and curriculum, making them highly useful for board exam preparation. Each topic is explained in a simple, clear, and student-friendly manner to help students understand concepts easily and score better in examinations.

Basics of RDBMS

What is Data?

• Data is a collection of characters, numbers, and symbols that represent values of a situation or variable.
• It is raw facts that have not been processed into useful information.
• The plural and singular form of data is datum.

Examples of data:

• Name, age, gender, contact details of a person
• Images, graphics, animations, audio, video
• Online posts, comments, and messages
• Signals generated by sensors
• Documents and web pages

What is Database?

• A database is a collection of logically related data.
• It is organized and stored in a way that allows easy searching and retrieval.

Properties of Database

• It represents real-world situations
• It is well structured and contains specific data
• It can be small or very large
• It can be managed manually or automatically
• It is atomic (a transaction completes as a whole)
• It is secure and durable (data is not easily lost)

Uses of Database in Real Life Applications

• Banking
• Online shopping
• Payroll system
• Inventory management
• Loan and investment systems
• Reservation systems
• And many more

Need for Database

• To handle large data without redundancy (repetition)
• To manage large data easily without complexity
• To maintain uniform data at different places or files
• To retrieve data easily and quickly from anywhere
• To support controlled data sharing at different user levels

What is DBMS?

• DBMS stands for Database Management System.
• It is software used to create and manage databases efficiently.
• It allows users to create, store, update, delete, and retrieve data.
• It connects and manages data in a consistent way.
• It acts as an interface between database and users/applications.

Examples of DBMS:

MySQL, Oracle, MongoDB, MS Access, MS SQL Server

Characteristics of DBMS

• Self-describing: DBMS stores both data and its description
• Program-data independence: changes in data structure do not affect programs
• Data sharing: multiple users can access data at the same time safely
• Backup and recovery: helps to restore data if it is lost or damaged

Types of DBMS Users

• End users: use database for searching, updating, and reports
• Database Administrator (DBA): manages access, security, and performance
• Application Programmer: writes programs to interact with database using SQL
• System Analyst: studies requirements and helps in database design

Advantages of DBMS Approach

• Reduces redundancy: data is stored at one place, no repetition
• Improves consistency: same data is used by all users
• Improves availability: data can be accessed by multiple users
• Improves security: access can be controlled using passwords and permissions
• User friendly: easy to access, update, and delete data without technical help

Limitations of DBMS Approach

• High cost of installation and setup
• Time consuming to design and implement
• Security and recovery require extra effort and resources

What is RDBMS?

• RDBMS stands for Relational Database Management System.
• It was developed by E.F. Codd (IBM, 1970).
• Data is stored in the form of tables (relations).
• Each row represents a record and each column represents an attribute.

RDBMS Terminologies

• Relation: A table is called a relation
• Tuple: A row in a table is called a tuple (record)
• Attribute: A column in a table is called an attribute
• Domain: Data type of a column is called domain
• Degree: Total number of attributes in a relation
• Cardinality: Total number of tuples (rows) in a relation
• Relation Schema: Structure of a relation (attributes, domains, constraints)
• Relation State: Current set of data in a relation at a time

Characteristics of Relation

• Order of attributes is not important
• Order of tuples is not important
• Each tuple must be unique
• Each value must be atomic (indivisible)
• NULL is used for unknown or not applicable values

What is Constraint in RDBMS?

Constraints are rules applied to ensure accuracy, validity, and reliability of data in a database.

Types of Constraints in RDBMS

Domain Constraint

• Each attribute value must belong to its defined domain (data type).

Key Constraint

• Super Key: A set of attributes that uniquely identifies tuples
• Candidate Key: Minimal super key
• Primary Key: Selected candidate key used to identify records
• Alternate Key: Candidate keys not selected as primary key

Null Value Constraint

• Some attributes cannot have NULL values and are set as NOT NULL

Entity Integrity Constraint

• Ensures all tuples are unique in a table
• Primary key enforces entity integrity

Referential Integrity Constraint

• Ensures relationship between two tables remains consistent
• A foreign key must match a primary key in another table

What is Foreign Key?

• A foreign key is a primary key of another related table
• A table can have more than one foreign key
• Foreign key can have NULL values
• It is used to create relationships between tables

SQL – Creating and Opening Database

What is SQL?

SQL stands for Structured Query Language. It is used to manage data in a database.
With SQL, we can:

• Create databases and tables
• Insert data
• Update data
• Delete data
• Retrieve data

Types of SQL Commands

• DDL (Data Definition Language): Used to define structure of database like tables, schema (Create, Alter, Drop)
• DML (Data Manipulation Language): Used to manage data (Insert, Update, Delete, Retrieve)

Data Types used in SQL

Different data types are used to store different kinds of data:

Data Type	Use	Example
CHAR(n)	Fixed length text	CHAR(5): “Ravi”, “Neha”
VARCHAR(n)	Variable length text	VARCHAR(10): “Aman”, “Sita”
DATE	Stores date (YYYY-MM-DD)	‘2023-11-15’
INTEGER	Whole numbers	120, 450
DECIMAL(m,d)	Numbers with decimal points	DECIMAL(4,2): 12.50
FLOAT	Decimal/real numbers	98.76

Creating and populating tables

Create Table Command

Used to create a new table in the database.

Syntax:

CREATE TABLE <table name>
(
<column 1> <data type> [constraint],
<column 2> <data type> [constraint],
<column 3> <data type> [constraint]
);

Example:

CREATE TABLE Product
(
ProductID CHAR(4) PRIMARY KEY,
ProductName VARCHAR(25),
Price FLOAT NOT NULL,
Stock INT
);

Types of Database Constraints

NOT NULL

Ensures a column cannot be empty.

Example:

CREATE TABLE STUDENT
(
StudentID INT,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20),
Age INT,
Class VARCHAR(10)
);

DEFAULT

Sets a default value if no value is given.

Example:

CREATE TABLE STUDENT
(
StudentID INT,
FirstName VARCHAR(20),
LastName VARCHAR(20),
Age INT,
Class VARCHAR(10),
Fees FLOAT DEFAULT 5000
);

CHECK

Used to restrict values in a column.

Example:

CREATE TABLE EMPLOYEE
(
EmpID INT,
EmpName VARCHAR(20) NOT NULL,
Gender CHAR(1),
Salary FLOAT,
Department VARCHAR(20) CHECK (Department IN (‘HR’, ‘IT’, ‘Sales’))
);

PRIMARY KEY

• Must be unique
• Cannot be NULL
• Only one per table

Example:

CREATE TABLE EMPLOYEE
(
EmpID INT PRIMARY KEY,
EmpName VARCHAR(20) NOT NULL,
Gender CHAR(1),
Salary FLOAT
);

Referential Integrity Constraint

A Referential Integrity Constraint ensures that relationships between two tables (relations) remain consistent in a database. Its main purpose is to maintain consistency of data when one table refers to another table.
The Foreign Key is used to establish this relationship between two tables.

Example: Consider the following tables:

Product Table – (ProductID, ProductName, Price)
Customer Order Table – CustomerOrder (OrderID, OrderDate, ProductID, Quantity, TotalAmount)

Here, ProductID in the CustomerOrder table is a Foreign Key that references ProductID of the Product table, which is the Primary Key.

SQL Command:

CREATE TABLE CustomerOrder
(
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    ProductID INT,
    Quantity INT,
    TotalAmount INT,
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

Modifying the content and structure of table

DROP TABLE

Used to delete a table completely (structure + data)

Example:

DROP TABLE ORDERS;

ALTER TABLE

Used to modify table structure.

Add Column

ALTER TABLE Product ADD ManufactureDate DATE;

Drop Column

ALTER TABLE Product DROP COLUMN Stock;

Modify Column

ALTER TABLE Product ALTER COLUMN Price SET DEFAULT 100;

Drop Primary Key

ALTER TABLE Product DROP PRIMARY KEY;

INSERT Command

Used to add new data into a table.

Example:

INSERT INTO Product VALUES (101, ‘Laptop’, 55000, 10);

UPDATE Command

Used to modify existing data.

Example:

UPDATE Product
SET Price = 60000
WHERE ProductID = 101;

DELETE Command

Used to remove records from a table.

Example:

DELETE FROM Product
WHERE ProductID = 102;

SELECT Command

Used to retrieve data from a table.

Examples:

SELECT * FROM Product;

SELECT ProductName, Price FROM Product;

SELECT ProductName FROM Product WHERE Price > 1000;

SELECT * FROM Product WHERE ProductName LIKE ‘L%’;

SELECT * FROM Product WHERE Price < 50000 AND Stock > 5;

DISTINCT Command

Removes duplicate values.

Example:

SELECT DISTINCT Department FROM EMPLOYEE;

WHERE clause

• Where clause is used to define Criteria for the records to be retrieved.
• It can be used with any DML command such as SELECT, DELETE and UPDATE.
• To define criteria various operators like Arithmetic, Range or Comparison operators are used in WHERE clause.

Example: Retrieve item details which rate is less than 100 and qty is more than 100

Select * from item where rate < 100 and qty > 100;

Operators used with WHERE clause

• Arithmetic operators
• Relation operators
• Logical operators
• BETWEEN operators
• IN operators
• LIKE operators

Arithmetic operators

• We can use arithmetic operators to define expressions in DML SQL commands.
• Arithmetic operators are used with numeric values.
• Following table depicts various arithmetic operators:

Arithmetic Operator	Description
+	For adding values
–	For subtracting values
*	For multiplying values
/	For dividing values
%	For finding remainders

Relational operators

• We can use Relational operators to compare values in SQL.
• Mostly used to define criteria in WHERE clause to filter records.

Relational Operator	Description
=	Equal to
>	Greater than
<	Lesser than
>=	Greater than equal to
<=	Lesser than equal to
!= or <>	Not equal to

Logical operators

• Logical operators are used to combine multiple criteria/expression in DML SQL commands
• Mostly used to combine criteria in WHERE clause to filter records.
• Following table depicts various Logical operators:

Logical Operator	Description
AND	Returns true when all criteria/expressions are true
OR	Returns true when at least a criteria/expression is true
NOT	Negates the result

BETWEEN operator

• It is used to define range of values for a particular field in WHERE clause.
• It can be used to define range of numerical or chronological values
• It includes upper and lower bound given in the range.

IN operator

• It is used to set multiple text comparison for a particular field in WHERE clause.
• It can only be used for text comparision.

LIKE operator

• It is used to define string expressions (pattern matching) in WHERE clause.
• It uses different wildcards (described in image given below) to define string expression.

Wildcard	Description
%	Used to match the occurrence of set of characters of any length
_	Underscore used to match occurrence of any single character

Ordering and Grouping

Order by clause

• Used to display records in sequential manner in data resultset retrieved by WHERE clause.
• Bydefault displays records in ascending order.
• Keyword ASC (Ascending) or DESC (descending) can be used with order by to arrange the data resultset.

Example:

SELECT * FROM item ORDER BY iname;

SELECT * FROM item OREDER BY iname DESC;

Aggregate Functions

• Are also known as Multiple Row Functions.
• It works on Multiple rows together rather single row
• It helps to summarize large volume of data
• It returns single value for entire table or set of rows

Function	Description	Example
Sum(field)	Returns sum of all rows of specified field	Select sum(qty_sold) from order; Output: 10230
Avg(field)	Returns average or mean value of all rows of specified field	Select avg(qty_sold) from order; Output: 780
Max(field)	Returns largest value among all rows of specified field	Select max(qty_sold) from order; Output: 1460
Min(field)	Returns smallest value among all rows of specified field	Select min(qty_sold) from order; Output: 470
count(field)	Counts and return total rows of specified field	Select count(qty_sold) from order; Output: 72

Group By

The Group By clause helps to summarize large volume of records. It combines all those records that have identical values in a particular field or a group of fields and produces one summary record per group.

Syntax:

SELECT <field(s)>, <aggregate function(s)>
FROM <table name>
WHERE <condition>
GROUP By <field(s)>;

Example:

Having clause

• Helps to filter summarized result produced after grouping.
• It is like post filter which retrieves records from resultset produced after group by query.
• It can include aggregate functions to set the filter criteria

Syntax:

SELECT <field(s)>, <aggregate function(s)>
FROM <table name>
WHERE <condition>
GROUP By <field(s)>
HAVING <expression>;

Operating with multiple tables

Joins

• JOIN operation is used to combine records (tuples) from two tables based on a specified condition.
• JOIN helps in retrieving meaningful and related data from multiple tables.
• It is commonly used in relational databases to establish relationships between tables.
• The related attributes used in JOIN are generally:
  • Primary Key in one table
  • Foreign Key in another table

Example: Consider two tables: Student(Student_ID, Name) and Marks(Student_ID, Marks). In the Student table, Student_ID is the Primary Key because it uniquely identifies each student. In the Marks table, Student_ID is the Foreign Key because it establishes a relationship with the Student table.
Display student names along with their marks by combining records from both tables, the following SQL query can be used:

Using WHERE clause:

SELECT Student.Name, Marks.Marks
FROM Student, Marks
WHERE Student.Student_ID = Marks.Student_ID;

Using the JOIN:

SELECT Student.Name, Marks.Marks
FROM Student
JOIN Marks
ON Student.Student_ID = Marks.Student_ID;

Both queries display student names along with their marks by matching the common attribute Student_ID.