CBSE CLASS XII – COMPUTER SCIENCE (083)

UNIT 3: DATABASE MANAGEMENT

Easy, Detailed & Exam-Oriented Notes

1. DATABASE CONCEPTS

1.1 What is Data?

Data means raw facts and figures that can be processed to obtain useful information.

Examples

101
Rajesh
95
Computer Science

These individual values are data.

1.2 What is Information?

Information is processed and organised data that has meaning.

Example

Student Name : Rajesh
Marks        : 95
Subject      : Computer Science

This is meaningful information.

Data → Processing → Information

+-----------+       +-------------+       +-------------+
|   DATA    | ----> |  PROCESSING  | ----> | INFORMATION |
+-----------+       +-------------+       +-------------+

2. What is a Database?

A database is an organised collection of related data that can be easily stored, accessed, managed and updated.

Example

A school database may contain:

Students
Teachers
Classes
Subjects
Fees
Attendance
Marks

A student table may look like:

RollNo	Name	Class	Marks
101	Rahul	XII	85
102	Priya	XII	91
103	Amit	XII	78

3. Why Do We Need a Database?

A database is needed to:

Store large amounts of data.
Organise data systematically.
Search data quickly.
Add new records.
Modify existing records.
Delete unwanted records.
Reduce unnecessary duplication.
Maintain data consistency.
Provide controlled access to data.
Generate useful reports.

Example

Without a database:

Student1.txt
Student2.txt
Student3.txt
Student4.txt
...

Finding a particular student's information can be difficult.

With a database:

              SCHOOL DATABASE
                    |
       +------------+------------+
       |            |            |
    Students     Teachers      Fees
       |
    Search / Update / Delete

4. Database Management System (DBMS)

A Database Management System (DBMS) is software used to create, store, organise, retrieve, update and manage databases.

Examples

MySQL
PostgreSQL
Oracle Database
Microsoft SQL Server
SQLite

For CBSE Class 12, MySQL is commonly used for SQL practical work.

5. DATABASE vs DBMS

Database	DBMS
Collection of related data	Software used to manage data
Contains the actual data	Provides tools to manipulate data
Example: Student database	Example: MySQL
Data is stored in tables in a relational database	Creates, modifies and manages tables

6. RELATIONAL DATA MODEL

A relational data model stores data in the form of tables.

A table consists of:

                 STUDENT
        +--------+--------+-------+
        | RollNo | Name   | Marks |
        +--------+--------+-------+
        | 101    | Rahul  | 85    |
        | 102    | Priya  | 91    |
        | 103    | Amit   | 78    |
        +--------+--------+-------+

In relational terminology:

Table       → Relation
Column      → Attribute
Row         → Tuple
Column values → Domain

7. RELATION

A relation is a table consisting of rows and columns.

Example:

STUDENT
+--------+---------+-------+
| RollNo | Name    | Marks |
+--------+---------+-------+
| 101    | Rahul   | 85    |
| 102    | Priya   | 91    |
| 103    | Amit    | 78    |
+--------+---------+-------+

The above table is a relation named STUDENT.

8. ATTRIBUTE

An attribute is a column of a relation.

Example:

STUDENT
+--------+---------+-------+
| RollNo | Name    | Marks |
+--------+---------+-------+

Attributes are:

RollNo
Name
Marks

Easy definition

Attribute = Column

9. TUPLE

A tuple is a row/record in a relation.

Example:

101 | Rahul | 85

is one tuple.

Easy definition

Tuple = Row / Record

10. DOMAIN

A domain is the set of permitted values for an attribute.

Example:

Age → 1 to 100
Gender → Male, Female, Other
Marks → 0 to 100

For:

Marks

the domain may be:

0, 1, 2, 3, ........, 100

11. DEGREE

The degree of a relation is the number of attributes (columns) in the table.

Example:

+--------+---------+-------+
| RollNo | Name    | Marks |
+--------+---------+-------+

There are 3 columns.

Therefore:

Degree = 3

Formula

Degree = Number of columns

12. CARDINALITY

The cardinality of a relation is the number of tuples (rows) in the table.

Example:

+--------+---------+-------+
| RollNo | Name    | Marks |
+--------+---------+-------+
| 101    | Rahul   | 85    |
| 102    | Priya   | 91    |
| 103    | Amit    | 78    |
+--------+---------+-------+

There are 3 rows.

Therefore:

Cardinality = 3

Formula

Cardinality = Number of rows

13. DEGREE vs CARDINALITY

Degree	Cardinality
Number of columns	Number of rows
Represents attributes	Represents tuples
Vertical structure	Horizontal records
Example: 4 columns → degree 4	Example: 10 rows → cardinality 10

Memory Trick

DEGREE      → Columns
CARDINALITY → Rows

14. KEYS IN A RELATIONAL DATABASE

A key is an attribute or group of attributes used to identify records uniquely or establish relationships between tables.

Important keys:

Candidate Key
Primary Key
Alternate Key
Foreign Key

15. CANDIDATE KEY

A candidate key is an attribute or combination of attributes that can uniquely identify every record in a table.

Example:

STUDENT
+--------+----------------+----------------+
| RollNo | Email          | Name           |
+--------+----------------+----------------+
| 101    | r@gmail.com    | Rahul          |
| 102    | p@gmail.com    | Priya          |
+--------+----------------+----------------+

Suppose both RollNo and Email are unique.

Then:

Candidate Keys:
    RollNo
    Email

16. PRIMARY KEY

A primary key is the candidate key selected to uniquely identify each record in a table.

Example:

CREATE TABLE Student
(
    RollNo INT PRIMARY KEY,
    Name VARCHAR(30),
    Marks INT
);

Here:

RollNo → Primary Key

Important properties

A primary key:

Must uniquely identify each row.
Cannot contain duplicate values.
Cannot contain NULL.
There can be only one primary key constraint for a table.

17. ALTERNATE KEY

Candidate keys that are not selected as the primary key are called alternate keys.

Example:

Candidate Keys:
    RollNo
    Email

If:

RollNo → Primary Key

then:

Email → Alternate Key

Structure

Candidate Keys
       |
       +---- Primary Key
       |
       +---- Alternate Key

18. FOREIGN KEY

A foreign key is an attribute in one table that refers to a primary key in another table.

Example:

CUSTOMER

CustomerID	Name
1	Rahul
2	Priya

ORDER

OrderID	CustomerID	Amount
101	1	500
102	2	800

Here:

CUSTOMER
CustomerID → Primary Key

ORDER
CustomerID → Foreign Key

Relationship

CUSTOMER                     ORDER
+------------+              +------------+
| CustomerID |<-------------| CustomerID |
+------------+              +------------+
   Primary Key                Foreign Key

19. SQL – STRUCTURED QUERY LANGUAGE

SQL stands for:

Structured Query Language

SQL is used to communicate with a relational database.

SQL can be used to:

Create databases.
Create tables.
Insert data.
Retrieve data.
Update data.
Delete data.
Modify table structures.

20. TYPES OF SQL COMMANDS

For this syllabus, two important categories are:

SQL
 |
 +-----------------------+
 |                       |
DDL                     DML
 |                       |
Structure                Data

21. DDL – DATA DEFINITION LANGUAGE

DDL commands are used to define or modify the structure of database objects such as tables.

Important commands:

CREATE
ALTER
DROP

Examples:

CREATE DATABASE School;
ALTER TABLE Student ADD City VARCHAR(30);
DROP TABLE Student;

22. DML – DATA MANIPULATION LANGUAGE

DML commands are used to manipulate data stored in tables.

Important commands:

INSERT
UPDATE
DELETE

Example:

INSERT INTO Student VALUES
(101, 'Rahul', 85);

23. IMPORTANT SQL DATA TYPES

23.1 CHAR(n)

Stores fixed-length character/string data.

Example:

Name CHAR(20)

If fewer than 20 characters are stored, the field has fixed length semantics.

23.2 VARCHAR(n)

Stores variable-length character/string data.

Example:

Name VARCHAR(30)

It can store up to 30 characters.

CHAR vs VARCHAR

CHAR	VARCHAR
Fixed length	Variable length
Suitable for fixed-size values	Suitable for varying-length text
Example: code of fixed length	Example: names

24. INT

Stores integer values.

Example:

Age INT
Marks INT

Examples:

25. FLOAT

Stores approximate decimal/floating-point values.

Example:

Percentage FLOAT

Example:

85.5
91.75

26. DATE

Stores date values.

Example:

DOB DATE

Common MySQL date format:

YYYY-MM-DD

Example:

2008-05-15

27. SQL CONSTRAINTS

Constraints are rules applied to table columns to maintain valid data.

Important constraints in this syllabus:

NOT NULL
UNIQUE
PRIMARY KEY

28. NOT NULL

NOT NULL ensures that a column cannot contain NULL.

Example:

Name VARCHAR(30) NOT NULL

The name must be supplied.

29. UNIQUE

UNIQUE prevents duplicate values in a column.

Example:

Email VARCHAR(50) UNIQUE

Two records should not have the same email value under this constraint.

30. PRIMARY KEY CONSTRAINT

Example:

RollNo INT PRIMARY KEY

It provides unique identification and does not allow NULL.

31. CREATING A DATABASE

Syntax:

CREATE DATABASE database_name;

Example:

CREATE DATABASE School;

32. SHOW DATABASES

To display available databases:

SHOW DATABASES;

Possible output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| School             |
+--------------------+

33. USE DATABASE

To select a database:

USE School;

After this, SQL commands will normally operate on the selected database unless another database is specified.

34. DROP DATABASE

To permanently remove a database:

DROP DATABASE School;

⚠️ Important: This removes the database and its tables/data. Use carefully.

35. SHOW TABLES

To display tables in the currently selected database:

SHOW TABLES;

36. CREATE TABLE

Syntax:

CREATE TABLE table_name
(
    column1 datatype,
    column2 datatype,
    column3 datatype
);

Example:

CREATE TABLE Student
(
    RollNo INT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Marks INT,
    City VARCHAR(20)
);

37. DESCRIBE TABLE

To see the structure of a table:

DESC Student;

or:

DESCRIBE Student;

Possible output:

+--------+-------------+------+-----+---------+
| Field  | Type        | Null | Key | Default |
+--------+-------------+------+-----+---------+
| RollNo | int         | NO   | PRI | NULL    |
| Name   | varchar(30) | NO   |     | NULL    |
| Marks  | int         | YES  |     | NULL    |
| City   | varchar(20) | YES  |     | NULL    |
+--------+-------------+------+-----+---------+

38. ALTER TABLE

ALTER TABLE is used to modify the structure of an existing table.

It can be used to:

Add a column.
Remove a column.
Add a primary key.
Remove a primary key.

39. ADD AN ATTRIBUTE/COLUMN

Syntax:

ALTER TABLE Student
ADD Phone VARCHAR(15);

Now:

Student
+--------+------+-------+------+-------+
| RollNo | Name | Marks | City | Phone |
+--------+------+-------+------+-------+

40. REMOVE AN ATTRIBUTE/COLUMN

Syntax:

ALTER TABLE Student
DROP COLUMN Phone;

41. ADD PRIMARY KEY

Suppose a table does not have a primary key:

ALTER TABLE Student
ADD PRIMARY KEY (RollNo);

42. REMOVE PRIMARY KEY

Syntax:

ALTER TABLE Student
DROP PRIMARY KEY;

43. DROP TABLE

DROP TABLE permanently removes a table and its data.

Syntax:

DROP TABLE Student;

Difference

DROP DATABASE
      ↓
Entire database removed

DROP TABLE
      ↓
Only selected table removed

44. INSERT COMMAND

INSERT is used to add records to a table.

Method 1 – Insert values for all columns

INSERT INTO Student
VALUES
(101, 'Rahul', 85, 'Basti');

Method 2 – Specify columns

INSERT INTO Student
(RollNo, Name, Marks, City)
VALUES
(102, 'Priya', 91, 'Lucknow');

This method is clearer and useful when not inserting into every column.

45. SELECT COMMAND

SELECT is used to retrieve data.

Display all columns

SELECT * FROM Student;

Possible output:

+--------+-------+-------+---------+
| RollNo | Name  | Marks | City    |
+--------+-------+-------+---------+
| 101    | Rahul | 85    | Basti   |
| 102    | Priya | 91    | Lucknow |
+--------+-------+-------+---------+

Display selected columns

SELECT Name, Marks
FROM Student;

Output:

+-------+-------+
| Name  | Marks |
+-------+-------+
| Rahul | 85    |
| Priya | 91    |
+-------+-------+

46. DELETE COMMAND

DELETE removes records from a table.

Example:

DELETE FROM Student
WHERE RollNo = 101;

This deletes the record whose RollNo is 101.

Important

DELETE FROM Student;

without a WHERE condition can delete all rows from the table.

47. UPDATE COMMAND

UPDATE modifies existing records.

Example:

UPDATE Student
SET Marks = 90
WHERE RollNo = 101;

The marks of student 101 become 90.

Multiple columns

UPDATE Student
SET Marks = 90, City = 'Delhi'
WHERE RollNo = 101;

⚠️ Without WHERE, an UPDATE can affect every row.

48. SQL OPERATORS

SQL operators are used in expressions and conditions.

Main categories:

Mathematical operators
Relational operators
Logical operators

49. MATHEMATICAL OPERATORS

Common mathematical operators:

Operator	Meaning
`+`	Addition
`-`	Subtraction
`*`	Multiplication
`/`	Division
`%`	Modulo/remainder

Example:

SELECT Marks + 5
FROM Student;

50. RELATIONAL OPERATORS

Operator	Meaning
`=`	Equal to
`<>` or `!=`	Not equal to
`>`	Greater than
`<`	Less than
`>=`	Greater than or equal to
`<=`	Less than or equal to

Example:

SELECT *
FROM Student
WHERE Marks >= 80;

51. LOGICAL OPERATORS

Important logical operators:

AND
OR
NOT

AND

Both conditions must be true.

SELECT *
FROM Student
WHERE Marks >= 80 AND City = 'Basti';

OR

At least one condition must be true.

SELECT *
FROM Student
WHERE City = 'Basti' OR City = 'Lucknow';

NOT

Reverses a condition.

SELECT *
FROM Student
WHERE NOT City = 'Basti';

52. ALIASING

An alias gives a temporary alternative name to a column or table in a query result.

Column alias

SELECT Name AS StudentName
FROM Student;

Output heading:

+-------------+
| StudentName |
+-------------+
| Rahul       |
| Priya       |
+-------------+

AS is commonly used for aliasing.

53. DISTINCT CLAUSE

DISTINCT removes duplicate values from the result.

Example:

Suppose:

City
---------
Basti
Lucknow
Basti
Delhi
Lucknow

Query:

SELECT DISTINCT City
FROM Student;

Result:

Basti
Lucknow
Delhi

54. WHERE CLAUSE

WHERE is used to specify a condition.

Example:

SELECT *
FROM Student
WHERE Marks > 80;

Only students with marks greater than 80 are displayed.

Structure

SELECT
   ↓
Choose columns
   ↓
FROM
   ↓
Choose table
   ↓
WHERE
   ↓
Apply condition

55. IN OPERATOR

IN checks whether a value belongs to a specified list of values.

Instead of:

WHERE City = 'Basti'
OR City = 'Lucknow'
OR City = 'Delhi'

we can write:

WHERE City IN ('Basti', 'Lucknow', 'Delhi');

This is shorter and easier to read.

56. BETWEEN OPERATOR

BETWEEN checks whether a value lies within an inclusive range.

Example:

SELECT *
FROM Student
WHERE Marks BETWEEN 60 AND 80;

This includes:

60
...
80

Both boundary values are included.

57. ORDER BY

ORDER BY sorts query results.

Ascending order

SELECT *
FROM Student
ORDER BY Marks ASC;

ASC means ascending.

Descending order

SELECT *
FROM Student
ORDER BY Marks DESC;

DESC means descending.

Example

Before:
85
72
95
60

ORDER BY Marks ASC:

60
72
85
95

58. NULL VALUE

NULL means that a value is missing, unknown, or not available.

It does not mean:

''

It does not mean:

False

Important

NULL ≠ 0
NULL ≠ empty string
NULL ≠ False

59. IS NULL

To find records containing NULL:

SELECT *
FROM Student
WHERE Phone IS NULL;

60. IS NOT NULL

To find records where the value is not NULL:

SELECT *
FROM Student
WHERE Phone IS NOT NULL;

Important

Do NOT normally write:

WHERE Phone = NULL

Use:

WHERE Phone IS NULL

61. LIKE OPERATOR

LIKE is used for pattern matching.

Two important wildcards are:

%  → zero or more characters
_  → exactly one character

Example 1 – Starts with R

SELECT *
FROM Student
WHERE Name LIKE 'R%';

Matches:

Rahul
Ravi
Riya

Example 2 – Ends with a

SELECT *
FROM Student
WHERE Name LIKE '%a';

Example 3 – Contains "an"

SELECT *
FROM Student
WHERE Name LIKE '%an%';

Example 4 – Exactly four characters

SELECT *
FROM Student
WHERE Name LIKE '____';

Each _ represents one character.

62. AGGREGATE FUNCTIONS

Aggregate functions perform calculations on a group of values and normally return one result per group.

Important functions:

MAX()
MIN()
AVG()
SUM()
COUNT()

63. MAX()

Returns the largest value.

SELECT MAX(Marks)
FROM Student;

Example output:

64. MIN()

Returns the smallest value.

SELECT MIN(Marks)
FROM Student;

65. AVG()

Returns the average.

SELECT AVG(Marks)
FROM Student;

66. SUM()

Returns the total.

SELECT SUM(Marks)
FROM Student;

67. COUNT()

Counts records/values depending on the expression.

Count rows

SELECT COUNT(*)
FROM Student;

This counts all rows.

Count non-NULL values in a column

SELECT COUNT(Phone)
FROM Student;

This counts non-NULL values in Phone.

68. AGGREGATE FUNCTION SUMMARY

Function	Meaning
`MAX()`	Highest value
`MIN()`	Lowest value
`AVG()`	Average
`SUM()`	Total
`COUNT()`	Count

Memory Trick

MAX → Maximum
MIN → Minimum
AVG → Average
SUM → Total
COUNT → Number

69. GROUP BY

GROUP BY groups rows having the same value in specified columns.

Suppose:

Name	City	Marks
Rahul	Basti	80
Amit	Basti	90
Priya	Lucknow	85
Neha	Lucknow	95

Query:

SELECT City, AVG(Marks)
FROM Student
GROUP BY City;

Conceptually:

Basti
 ├── Rahul 80
 └── Amit  90
       ↓
   Average = 85

Lucknow
 ├── Priya 85
 └── Neha  95
       ↓
   Average = 90

Result:

+---------+------------+
| City    | AVG(Marks) |
+---------+------------+
| Basti   | 85         |
| Lucknow | 90         |
+---------+------------+

70. HAVING CLAUSE

HAVING is used to apply conditions to groups created by GROUP BY.

Example:

SELECT City, AVG(Marks)
FROM Student
GROUP BY City
HAVING AVG(Marks) > 85;

This displays only those cities whose average marks are greater than 85.

71. WHERE vs HAVING

WHERE	HAVING
Filters individual rows before grouping	Filters groups after grouping
Commonly used with normal conditions	Commonly used with aggregate/group conditions
Comes before `GROUP BY`	Comes after `GROUP BY`

Example

SELECT City, AVG(Marks)
FROM Student
WHERE Marks >= 50
GROUP BY City
HAVING AVG(Marks) > 80;

Conceptual order:

FROM
 ↓
WHERE
 ↓
GROUP BY
 ↓
HAVING
 ↓
SELECT
 ↓
ORDER BY

72. JOINS

A join combines related information from two or more tables.

Suppose we have:

STUDENT

RollNo	Name	DeptID
1	Rahul	10
2	Priya	20

DEPARTMENT

DeptID	DeptName
10	Computer
20	Science

We can combine these tables using a join.

73. CARTESIAN PRODUCT

A Cartesian product combines every row of the first table with every row of the second table.

Suppose:

Table A → 2 rows
Table B → 3 rows

Then:

Number of combinations = 2 × 3 = 6

SQL:

SELECT *
FROM Student, Department;

or explicitly:

SELECT *
FROM Student
CROSS JOIN Department;

Conceptual structure:

Student Row 1 → Department Row 1
              → Department Row 2
              → Department Row 3

Student Row 2 → Department Row 1
              → Department Row 2
              → Department Row 3

74. EQUI-JOIN

An equi-join combines rows from two tables using an equality condition.

Example:

SELECT Student.Name, Department.DeptName
FROM Student, Department
WHERE Student.DeptID = Department.DeptID;

Here:

Student.DeptID = Department.DeptID

is the equality condition.

Result:

+-------+----------+
| Name  | DeptName |
+-------+----------+
| Rahul | Computer |
| Priya | Science  |
+-------+----------+

75. NATURAL JOIN

A natural join automatically joins tables using columns with the same name and compatible data types.

Example:

SELECT *
FROM Student
NATURAL JOIN Department;

If both tables contain:

DeptID

the common column is used for matching.

Important difference

Equi-Join
    ↓
Explicit equality condition

Natural Join
    ↓
Automatically uses common-name columns

76. SQL COMMAND STRUCTURE – QUICK MAP

                    SQL
                     |
       +-------------+-------------+
       |                           |
      DDL                         DML
       |                           |
 CREATE / ALTER / DROP       INSERT / UPDATE / DELETE

Data retrieval is performed using:

SELECT

77. COMPLETE SQL EXAMPLE

Let's create a Student database.

Step 1 – Create database

CREATE DATABASE School;

Step 2 – Select database

USE School;

Step 3 – Create table

CREATE TABLE Student
(
    RollNo INT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Marks INT,
    City VARCHAR(20)
);

Step 4 – Insert records

INSERT INTO Student
VALUES
(101, 'Rahul', 85, 'Basti');

INSERT INTO Student
VALUES
(102, 'Priya', 91, 'Lucknow');

INSERT INTO Student
VALUES
(103, 'Amit', 76, 'Basti');

INSERT INTO Student
VALUES
(104, 'Neha', 95, 'Lucknow');

Step 5 – Display all records

SELECT * FROM Student;

78. USEFUL SQL QUERIES ON STUDENT TABLE

Display names

SELECT Name
FROM Student;

Display students with marks above 80

SELECT *
FROM Student
WHERE Marks > 80;

Display students from Basti

SELECT *
FROM Student
WHERE City = 'Basti';

Display students from Basti or Lucknow

SELECT *
FROM Student
WHERE City IN ('Basti', 'Lucknow');

Display marks from 80 to 95

SELECT *
FROM Student
WHERE Marks BETWEEN 80 AND 95;

Sort by marks

SELECT *
FROM Student
ORDER BY Marks DESC;

Highest marks

SELECT MAX(Marks)
FROM Student;

Lowest marks

SELECT MIN(Marks)
FROM Student;

Average marks

SELECT AVG(Marks)
FROM Student;

Total marks

SELECT SUM(Marks)
FROM Student;

Number of students

SELECT COUNT(*)
FROM Student;

79. PYTHON – SQL DATABASE CONNECTIVITY

Python can be connected to an SQL database so that a Python program can:

                 PYTHON PROGRAM
                       |
                       ↓
                  SQL Connector
                       |
                       ↓
                  SQL DATABASE
                       |
          +------------+------------+
          |            |            |
        INSERT       UPDATE       DELETE
          |            |            |
          +------------+------------+
                       |
                       ↓
                    SELECT
                       |
                       ↓
                   DISPLAY

For MySQL, Python commonly uses the MySQL Connector/Python package.

A typical import is:

import mysql.connector

80. CONNECTING PYTHON WITH MYSQL

Example:

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="School"
)

print("Database connected successfully")

Explanation

mysql.connector
      ↓
Python module for MySQL connectivity

connect()
      ↓
Creates database connection

81. IMPORTANT PYTHON DATABASE FUNCTIONS/OBJECTS

The CBSE syllabus focuses on:

connect()
cursor()
execute()
commit()
fetchone()
fetchall()
rowcount

82. connect()

connect() establishes a connection between Python and the database.

Example:

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="School"
)

83. cursor()

A cursor is used to execute SQL statements and retrieve results.

Example:

cur = con.cursor()

Structure:

Python
  |
  ↓
Connection
  |
  ↓
Cursor
  |
  ↓
SQL Query

84. execute()

execute() is used to execute an SQL query.

Example:

cur.execute("SELECT * FROM Student")

85. commit()

commit() permanently saves changes made by commands such as:

INSERT
UPDATE
DELETE

Example:

con.commit()

Easy concept

INSERT / UPDATE / DELETE
          |
          ↓
       commit()
          |
          ↓
Changes saved

86. fetchone()

fetchone() retrieves one row from a result set.

Example:

cur.execute("SELECT * FROM Student")

row = cur.fetchone()

print(row)

Possible output:

(101, 'Rahul', 85, 'Basti')

87. fetchall()

fetchall() retrieves all remaining rows from the result set.

Example:

cur.execute("SELECT * FROM Student")

rows = cur.fetchall()

for row in rows:
    print(row)

Possible output:

(101, 'Rahul', 85, 'Basti')
(102, 'Priya', 91, 'Lucknow')
(103, 'Amit', 76, 'Basti')
(104, 'Neha', 95, 'Lucknow')

88. fetchone() vs fetchall()

fetchone()	fetchall()
Returns one row	Returns all remaining rows
Useful when one record is required	Useful when multiple records are required
Returns a single row or `None` if no row is available	Returns a list of rows

89. rowcount

rowcount gives the number of rows affected by certain operations or returned/available according to the database connector's behaviour.

Example:

cur.execute(
    "UPDATE Student SET Marks = 90 WHERE RollNo = 101"
)

print(cur.rowcount)

con.commit()

Possible output:

This means one row was affected.

90. PYTHON PROGRAM – INSERT RECORD

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="School"
)

cur = con.cursor()

sql = """
INSERT INTO Student
(RollNo, Name, Marks, City)
VALUES (%s, %s, %s, %s)
"""

data = (105, "Ravi", 88, "Basti")

cur.execute(sql, data)

con.commit()

print("Record inserted successfully")

cur.close()
con.close()

91. `%s` FORMAT SPECIFIER IN SQL QUERIES

When using MySQL Connector/Python, parameter placeholders are commonly written as:

%s

Example:

sql = "INSERT INTO Student VALUES (%s, %s, %s, %s)"

data = (105, "Ravi", 88, "Basti")

cur.execute(sql, data)

Important

Here %s is a parameter placeholder used by the database connector.

It does not mean that every value must actually be a Python string.

For example:

data = (105, "Ravi", 88, "Basti")

contains integers and strings.

92. WHY USE PARAMETERISED QUERIES?

Instead of constructing SQL by joining user input into the SQL string, use placeholders.

Example:

sql = "SELECT * FROM Student WHERE RollNo = %s"

cur.execute(sql, (101,))

This is safer and cleaner than manually constructing SQL strings from user input.

93. PYTHON PROGRAM – UPDATE RECORD

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="School"
)

cur = con.cursor()

sql = "UPDATE Student SET Marks = %s WHERE RollNo = %s"

data = (95, 101)

cur.execute(sql, data)

con.commit()

print(cur.rowcount, "record updated")

cur.close()
con.close()

Possible output:

1 record updated

94. PYTHON PROGRAM – DELETE RECORD

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="School"
)

cur = con.cursor()

sql = "DELETE FROM Student WHERE RollNo = %s"

data = (105,)

cur.execute(sql, data)

con.commit()

print(cur.rowcount, "record deleted")

cur.close()
con.close()

95. PYTHON PROGRAM – DISPLAY ALL RECORDS

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="School"
)

cur = con.cursor()

cur.execute("SELECT * FROM Student")

rows = cur.fetchall()

for row in rows:
    print(row)

cur.close()
con.close()

96. PYTHON PROGRAM – DISPLAY ONE RECORD

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="School"
)

cur = con.cursor()

roll = 101

cur.execute(
    "SELECT * FROM Student WHERE RollNo = %s",
    (roll,)
)

row = cur.fetchone()

if row:
    print(row)
else:
    print("Record not found")

cur.close()
con.close()

Important

For a single parameter, the Python tuple is written as:

(roll,)

The comma is important.

97. USING format()

Queries can also be constructed using Python's format() method, but parameterised queries using %s are generally preferred for user input because they avoid unsafe SQL construction.

Example for controlled/non-user-generated values:

roll = 101

sql = "SELECT * FROM Student WHERE RollNo = {}".format(roll)

cur.execute(sql)

Important exam point

For values supplied by users, prefer:

cur.execute(
    "SELECT * FROM Student WHERE RollNo = %s",
    (roll,)
)

rather than directly inserting the input into the SQL string.

98. COMPLETE PYTHON DATABASE APPLICATION

A simple menu-driven application can perform:

              STUDENT DATABASE APPLICATION
                         |
        +----------------+----------------+
        |                |                |
      INSERT           UPDATE           DELETE
        |                |                |
        +----------------+----------------+
                         |
                       DISPLAY

Example:

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="School"
)

cur = con.cursor()

while True:

    print("\n1. Insert")
    print("2. Display")
    print("3. Update")
    print("4. Delete")
    print("5. Exit")

    choice = int(input("Enter choice: "))

    if choice == 1:

        roll = int(input("Enter Roll No: "))
        name = input("Enter Name: ")
        marks = int(input("Enter Marks: "))
        city = input("Enter City: ")

        sql = """
        INSERT INTO Student
        (RollNo, Name, Marks, City)
        VALUES (%s, %s, %s, %s)
        """

        data = (roll, name, marks, city)

        cur.execute(sql, data)
        con.commit()

        print("Record inserted")

    elif choice == 2:

        cur.execute("SELECT * FROM Student")

        rows = cur.fetchall()

        for row in rows:
            print(row)

    elif choice == 3:

        roll = int(input("Enter Roll No: "))
        marks = int(input("Enter new marks: "))

        sql = """
        UPDATE Student
        SET Marks = %s
        WHERE RollNo = %s
        """

        cur.execute(sql, (marks, roll))
        con.commit()

        print(cur.rowcount, "record updated")

    elif choice == 4:

        roll = int(input("Enter Roll No: "))

        cur.execute(
            "DELETE FROM Student WHERE RollNo = %s",
            (roll,)
        )

        con.commit()

        print(cur.rowcount, "record deleted")

    elif choice == 5:

        break

    else:
        print("Invalid choice")

cur.close()
con.close()

print("Database connection closed")

99. COMPLETE DATABASE CONNECTIVITY STRUCTURE

                 PYTHON PROGRAM
                       |
                       |
                 import connector
                       |
                       ↓
                    connect()
                       |
                       ↓
                 DATABASE CONNECTION
                       |
                       ↓
                    cursor()
                       |
                       ↓
                    execute()
                       |
          +------------+------------+
          |            |            |
        INSERT       UPDATE       DELETE
          |            |            |
          +------------+------------+
                       |
                       ↓
                    commit()
                       |
                       ↓
                    DATABASE
                       |
                       ↓
                    SELECT
                       |
             +---------+---------+
             |                   |
          fetchone()          fetchall()
             |                   |
             +---------+---------+
                       |
                       ↓
                    OUTPUT

100. SQL COMMAND QUICK REFERENCE

Task	SQL Command
Create database	`CREATE DATABASE`
Select database	`USE`
Show databases	`SHOW DATABASES`
Delete database	`DROP DATABASE`
Show tables	`SHOW TABLES`
Create table	`CREATE TABLE`
Display structure	`DESC` / `DESCRIBE`
Modify table	`ALTER TABLE`
Delete table	`DROP TABLE`
Add record	`INSERT`
Display records	`SELECT`
Modify records	`UPDATE`
Delete records	`DELETE`

101. VERY IMPORTANT SQL CLAUSES

Clause	Purpose
`WHERE`	Select rows according to condition
`DISTINCT`	Remove duplicate result values
`IN`	Match values from a list
`BETWEEN`	Match values in a range
`LIKE`	Pattern matching
`ORDER BY`	Sort result
`GROUP BY`	Create groups
`HAVING`	Filter groups

102. IMPORTANT SQL DIFFERENCES

DELETE vs DROP

DELETE	DROP
Removes rows	Removes table/database object
Table remains	Table is removed
Can use `WHERE`	Removes the object
Example: `DELETE FROM Student WHERE RollNo=101;`	Example: `DROP TABLE Student;`

WHERE vs HAVING

WHERE
 ↓
Filters rows

HAVING
 ↓
Filters groups

PRIMARY KEY vs FOREIGN KEY

Primary Key	Foreign Key
Uniquely identifies records	Refers to key in another table
Cannot be NULL	Can contain NULL unless restricted
Unique	May contain duplicate values
One primary key constraint per table	A table can have multiple foreign keys
Maintains entity identification	Helps establish relationships

CHAR vs VARCHAR

CHAR
 ↓
Fixed length

VARCHAR
 ↓
Variable length

fetchone() vs fetchall()

fetchone()
   ↓
One row

fetchall()
   ↓
All remaining rows

103. DATABASE TERMINOLOGY – ONE LOOK

Database
   ↓
Collection of related data

Table / Relation
   ↓
Rows + Columns

Column
   ↓
Attribute

Row
   ↓
Tuple

Permitted values
   ↓
Domain

Number of columns
   ↓
Degree

Number of rows
   ↓
Cardinality

Unique identifier
   ↓
Key

104. KEY CONCEPT DIAGRAM

                 KEYS
                   |
          +--------+--------+
          |                 |
     Candidate Key      Foreign Key
          |
     +----+----+
     |         |
 Primary     Alternate
   Key          Key

Example:

Candidate Keys = RollNo, Email

Selected Primary Key = RollNo

Remaining Candidate Key = Email
                           ↓
                     Alternate Key

105. SQL QUERY THINKING EXAMPLE

Question:

Display names and marks of students whose marks are greater than 80 in descending order.

Step 1

Required columns:

Name, Marks

Step 2

Table:

Student

Step 3

Condition:

Marks > 80

Step 4

Sorting:

Marks DESC

Final query:

SELECT Name, Marks
FROM Student
WHERE Marks > 80
ORDER BY Marks DESC;

106. ANOTHER QUERY EXAMPLE

Question:

Display the names of students whose city is Basti or Lucknow.

SELECT Name
FROM Student
WHERE City IN ('Basti', 'Lucknow');

107. ANOTHER QUERY EXAMPLE

Question:

Find the highest, lowest and average marks.

SELECT
    MAX(Marks),
    MIN(Marks),
    AVG(Marks)
FROM Student;

108. GROUP BY + HAVING EXAMPLE

Question:

Display each city and the number of students from that city, but display only cities having more than one student.

SELECT City, COUNT(*)
FROM Student
GROUP BY City
HAVING COUNT(*) > 1;

109. JOIN EXAM EXAMPLE

Tables:

STUDENT

RollNo | Name | DeptID
-------+------+-------
101    | Rahul| 10
102    | Priya| 20

DEPARTMENT

DeptID | DeptName
-------+---------
10     | Computer
20     | Science

Query:

SELECT Student.Name, Department.DeptName
FROM Student, Department
WHERE Student.DeptID = Department.DeptID;

Output:

+-------+----------+
| Name  | DeptName |
+-------+----------+
| Rahul | Computer |
| Priya | Science  |
+-------+----------+

110. PYTHON DATABASE PROGRAM – EXAM TEMPLATE

Students should remember this basic pattern:

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="School"
)

cur = con.cursor()

cur.execute("SQL QUERY")

con.commit()

cur.close()
con.close()

For retrieving records:

cur.execute("SELECT * FROM Student")

rows = cur.fetchall()

for row in rows:
    print(row)

111. COMMON ERRORS IN PYTHON-SQL PROGRAMS

Error 1 – Database does not exist

Possible reason:

database name is incorrect

Check:

SHOW DATABASES;

Error 2 – Table does not exist

Check:

SHOW TABLES;

Error 3 – Incorrect column name

Check table structure:

DESC Student;

Error 4 – Forgetting commit()

After:

INSERT
UPDATE
DELETE

use:

con.commit()

Error 5 – Incorrect parameter

Correct:

cur.execute(
    "SELECT * FROM Student WHERE RollNo = %s",
    (101,)
)

Notice:

(101,)

is a one-element tuple.

112. GOLDEN RULES FOR SQL

Remember:

SQL commands generally end with ;.
Database names and table names should be written correctly.
Text values are normally enclosed in single quotes.
Numeric values normally do not require quotes.
Use IS NULL, not = NULL.
Use WHERE carefully with UPDATE and DELETE.
DISTINCT removes duplicate result values.
BETWEEN includes both boundary values.
% means zero or more characters in LIKE.
_ means one character in LIKE.
GROUP BY creates groups.
HAVING filters groups.
ORDER BY sorts results.
PRIMARY KEY uniquely identifies records.
FOREIGN KEY establishes a relationship with another table.

113. GOLDEN RULES FOR PYTHON-MYSQL CONNECTIVITY

Remember this sequence:

import
  ↓
connect()
  ↓
cursor()
  ↓
execute()
  ↓
commit()        ← for INSERT/UPDATE/DELETE
  ↓
fetchone()/fetchall()  ← for SELECT
  ↓
close()

Most important functions

connect()
cursor()
execute()
commit()
fetchone()
fetchall()

114. EXAM-ORIENTED ONE-LINE DEFINITIONS

Database

An organised collection of related data.

DBMS

Software used to create, store, manage and retrieve data from databases.

Relation

A table in a relational database.

Attribute

A column of a relation.

Tuple

A row/record of a relation.

Domain

The set of permitted values for an attribute.

Degree

Number of attributes/columns in a relation.

Cardinality

Number of tuples/rows in a relation.

Candidate Key

An attribute or set of attributes that can uniquely identify each record.

Primary Key

The candidate key selected to uniquely identify records.

Alternate Key

A candidate key not selected as the primary key.

Foreign Key

A field that refers to a key in another table, typically its primary key.

SQL

Structured Query Language used to work with relational databases.

DDL

Commands used to define or modify database structure.

DML

Commands used to manipulate data in database tables.

NULL

Represents a missing, unknown or unavailable value.

Join

A technique used to combine related rows from multiple tables.

Cursor

An object used by Python database connectivity code to execute SQL statements and retrieve results.

115. MOST IMPORTANT EXAM QUESTIONS

Short Answer Questions

What is a database?
What is DBMS?
What is a relation?
Define attribute and tuple.
What is a domain?
Define degree and cardinality.
Differentiate between degree and cardinality.
What is a candidate key?
What is a primary key?
What is an alternate key?
What is a foreign key?
What is SQL?
Differentiate between DDL and DML.
What is the use of CREATE DATABASE?
What is the use of USE?
What is the purpose of ALTER TABLE?
What is the difference between DELETE and DROP?
What is the use of DISTINCT?
What is the use of WHERE?
What is NULL?
What is the use of IS NULL?
What is the use of LIKE?
What are % and _ in LIKE?
What is GROUP BY?
What is HAVING?
What is a Cartesian product?
What is an equi-join?
What is a natural join?
What is connect() in Python database connectivity?
What is the purpose of cursor()?
What is execute()?
What is commit()?
What is the difference between fetchone() and fetchall()?
What is rowcount?

116. IMPORTANT SQL PRACTICE QUESTIONS

Consider:

STUDENT
+--------+-------+-------+---------+
| RollNo | Name  | Marks | City    |
+--------+-------+-------+---------+
| 101    | Rahul | 85    | Basti   |
| 102    | Priya | 91    | Lucknow |
| 103    | Amit  | 76    | Basti   |
| 104    | Neha  | 95    | Lucknow |
+--------+-------+-------+---------+

Write SQL commands to:

1. Display all records

SELECT * FROM Student;

2. Display Name and Marks

SELECT Name, Marks
FROM Student;

3. Display students with marks above 80

SELECT *
FROM Student
WHERE Marks > 80;

4. Display students from Basti

SELECT *
FROM Student
WHERE City = 'Basti';

5. Display unique cities

SELECT DISTINCT City
FROM Student;

6. Display marks between 80 and 95

SELECT *
FROM Student
WHERE Marks BETWEEN 80 AND 95;

7. Sort marks in descending order

SELECT *
FROM Student
ORDER BY Marks DESC;

8. Find maximum marks

SELECT MAX(Marks)
FROM Student;

9. Find minimum marks

SELECT MIN(Marks)
FROM Student;

10. Find average marks

SELECT AVG(Marks)
FROM Student;

11. Count students

SELECT COUNT(*)
FROM Student;

117. FINAL QUICK REVISION

DATABASE
   ↓
Organised collection of related data

RELATIONAL DATABASE
   ↓
Data stored in tables

TABLE
   ↓
Relation

COLUMN
   ↓
Attribute

ROW
   ↓
Tuple

COLUMNS COUNT
   ↓
Degree

ROWS COUNT
   ↓
Cardinality

KEYS
   ↓
Candidate → Primary / Alternate
Foreign → Relationship between tables

SQL
   ↓
DDL + DML + SELECT/queries

DDL
   ↓
CREATE, ALTER, DROP

DML
   ↓
INSERT, UPDATE, DELETE

QUERY
   ↓
SELECT + FROM + WHERE + GROUP BY
+ HAVING + ORDER BY

AGGREGATE
   ↓
MAX, MIN, AVG, SUM, COUNT

JOIN
   ↓
Combine tables

PYTHON + SQL
   ↓
connect()
   ↓
cursor()
   ↓
execute()
   ↓
commit()
   ↓
fetchone()/fetchall()
   ↓
close()

118. SUPER-IMPORTANT MEMORY TRICKS

Table terminology

Attribute = Column
Tuple     = Row
Degree    = Columns
Cardinality = Rows

Keys

Candidate
    ↓
Primary OR Alternate

Foreign
    ↓
Connects related tables

SQL

CREATE → Create
ALTER  → Change structure
DROP   → Remove object

INSERT → Add
SELECT → Display
UPDATE → Modify
DELETE → Remove records

Conditions

WHERE     → Filter rows
IN        → List of values
BETWEEN   → Range
LIKE      → Pattern
IS NULL   → Missing values

Grouping

GROUP BY
    ↓
Make groups

HAVING
    ↓
Filter groups

Python Database Connectivity

CONNECT
   ↓
CURSOR
   ↓
EXECUTE
   ↓
COMMIT
   ↓
FETCH
   ↓
CLOSE

END OF UNIT 3 – DATABASE MANAGEMENT

Exam Focus

For CBSE Class 12 Computer Science, students should be especially comfortable with:

Relation, attribute, tuple, domain
Degree and cardinality
Candidate, primary, alternate and foreign keys
DDL and DML
SQL data types and constraints
CREATE, ALTER, DROP
INSERT, SELECT, UPDATE, DELETE
WHERE, DISTINCT, IN, BETWEEN, LIKE
NULL, IS NULL, IS NOT NULL
ORDER BY
MAX, MIN, AVG, SUM, COUNT
GROUP BY and HAVING
Cartesian product, equi-join and natural join
Python connect(), cursor(), execute(), commit()
fetchone(), fetchall() and rowcount
Python programs performing INSERT, UPDATE, DELETE and SELECT
Parameterised SQL queries using %s

C12_U3_RDBMS_20

CBSE CLASS XII – COMPUTER SCIENCE (083)

UNIT 3: DATABASE MANAGEMENT

Easy, Detailed & Exam-Oriented Notes

1. DATABASE CONCEPTS

1.1 What is Data?

Examples

1.2 What is Information?

Example

Data → Processing → Information

2. What is a Database?

Example

3. Why Do We Need a Database?

Example

4. Database Management System (DBMS)

Examples

5. DATABASE vs DBMS

6. RELATIONAL DATA MODEL

7. RELATION

8. ATTRIBUTE

Easy definition

9. TUPLE

Easy definition

10. DOMAIN

11. DEGREE

Formula

12. CARDINALITY

Formula

13. DEGREE vs CARDINALITY

Memory Trick

14. KEYS IN A RELATIONAL DATABASE

15. CANDIDATE KEY

16. PRIMARY KEY

Important properties

17. ALTERNATE KEY

Structure

18. FOREIGN KEY

CUSTOMER

ORDER

Relationship

19. SQL – STRUCTURED QUERY LANGUAGE

20. TYPES OF SQL COMMANDS

21. DDL – DATA DEFINITION LANGUAGE

22. DML – DATA MANIPULATION LANGUAGE

23. IMPORTANT SQL DATA TYPES

23.1 CHAR(n)

23.2 VARCHAR(n)

CHAR vs VARCHAR

24. INT

25. FLOAT

26. DATE

27. SQL CONSTRAINTS

28. NOT NULL

29. UNIQUE

30. PRIMARY KEY CONSTRAINT

31. CREATING A DATABASE

32. SHOW DATABASES

33. USE DATABASE

34. DROP DATABASE

35. SHOW TABLES

36. CREATE TABLE

37. DESCRIBE TABLE

38. ALTER TABLE

39. ADD AN ATTRIBUTE/COLUMN

40. REMOVE AN ATTRIBUTE/COLUMN

41. ADD PRIMARY KEY

42. REMOVE PRIMARY KEY

43. DROP TABLE

Difference

44. INSERT COMMAND

Method 1 – Insert values for all columns

Method 2 – Specify columns

45. SELECT COMMAND

Display all columns

Display selected columns

46. DELETE COMMAND

Important

47. UPDATE COMMAND

Multiple columns

48. SQL OPERATORS