UNIT 3 — DATABASE MANAGEMENT
1️⃣ Database Concepts
A database is an organized collection of data stored so that it can be easily accessed, managed, and
updated.
✔ Why do we need a database?
To store large amounts of data in an organized way
To reduce duplication of data
To easily insert, update, delete, or search data
To maintain security, backup, and consistency
Example:
A school database stores student records, fees, teachers, attendance, etc.
2️⃣ Relational Data Model
The relational model stores data in tables (called relations).
⭐ Important Terms
✔ Relation (Table)
A table containing rows and columns.
Example:
Student(id, name, age, class)
✔ Attribute (Column)
Each column of a table is an attribute.
Example: id, name, age
✔ Tuple (Row)
A single row in a table.
Example:
(1, "Amit", 18, "10A")
✔ Domain
The set of valid values for an attribute.
Example:
age domain = 1 to 120
✔ Degree
Number of columns in a table.
If Student table has 4 columns → degree = 4
✔ Cardinality
Number of rows in a table.
If Student table has 100 rows → cardinality = 100
✔ Keys
Keys uniquely identify rows.
Types of Keys:
Key Meaning Example
Candidate Key uniquely identifies a record roll_no, email
Primary Key main key chosen from candidate keys roll_no
Foreign Key primary key of another table class_id
3️⃣ STRUCTURED QUERY LANGUAGE
(SQL)
SQL is used to create, update, delete, and retrieve data from a database.
SQL has the following types:
DDL (Data Definition Language)
DML (Data Manipulation Language)
DCL/TCL (Control Languages)
⭐ A. DDL Commands (Structure of Database)
✔ 1. CREATE DATABASE
CREATE DATABASE school;
✔ 2. SHOW DATABASES
SHOW DATABASES;
✔ 3. USE DATABASE
USE school;
✔ 4. CREATE TABLE
CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(50),
age INT NOT NULL,
dob DATE
);
✔ 5. SHOW TABLES
SHOW TABLES;
✔ 6. DESCRIBE TABLE
DESCRIBE student;
✔ 7. DROP TABLE
DROP TABLE student;
✔ 8. ALTER TABLE
Add a column
ALTER TABLE student ADD city VARCHAR(30);
Remove a column
ALTER TABLE student DROP COLUMN dob;
⭐ B. DML – Data Manipulation Language
✔ 1. INSERT
INSERT INTO student VALUES(1, 'Amit', 18, '2006-01-01');
✔ 2. UPDATE
UPDATE student SET age = 19 WHERE id = 1;
✔ 3. DELETE
DELETE FROM student WHERE id = 1;
✔ 4. SELECT
SELECT * FROM student;
4️⃣ SQL Operators
✔ Relational Operators
=, !=, >, <, >=, <=
Example:
SELECT * FROM student WHERE age > 18;
✔ Logical Operators
AND, OR, NOT
SELECT * FROM student WHERE age > 18 AND city = 'Delhi';
✔ BETWEEN
SELECT * FROM student WHERE age BETWEEN 10 AND 20;
✔ IN
SELECT * FROM student WHERE city IN ('Delhi', 'Mumbai');
✔ LIKE (pattern matching)
SELECT * FROM student WHERE name LIKE 'A%';
✔ IS NULL
SELECT * FROM student WHERE city IS NULL;
5️⃣ Aggregate Functions
Function Meaning
MAX() returns highest value
MIN() returns lowest value
AVG() returns average
SUM() returns total
COUNT() number of rows
Example:
SELECT COUNT(*), MAX(age), MIN(age) FROM student;
6️⃣ Order By
SELECT * FROM student ORDER BY age DESC;
7️⃣ Group By + Having
SELECT city, COUNT(*)
FROM student
GROUP BY city
HAVING COUNT(*) > 3;
8️⃣ Joins
✔ 1. Cartesian Product
All rows from both tables.
SELECT * FROM student, class;
✔ 2. Equi Join
Join using matching columns.
SELECT * FROM student s
JOIN class c
ON s.class_id = [Link];
✔ 3. Natural Join
Automatically matches same column names.
SELECT * FROM student NATURAL JOIN class;
9️⃣ Interface of Python with SQL Database
Python can connect to SQL databases to perform queries.
✔ Step 1: Connect
import [Link]
con = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)
cursor = [Link]()
✔ Inserting data
[Link]("INSERT INTO student VALUES (1, 'Amit', 18, 'Delhi')")
[Link]()
✔ Selecting data
[Link]("SELECT * FROM student")
rows = [Link]()
for r in rows:
print(r)
✔ Update
[Link]("UPDATE student SET city='Mumbai' WHERE id=1")
[Link]()
✔ Delete
[Link]("DELETE FROM student WHERE id=1")
[Link]()
✔ Row Count
print([Link])
✔ Close connection
[Link]()