UNIT 3: DATABASE MANAGEMENT — NOTES
1. DATABASE CONCEPTS
1.1 What is a Database?
A database is an organized collection of data stored so that it can be easily accessed, managed,
and updated.
Examples
School database
Bank database
Library database
Ecommerce customer database
1.2 Need for a Database
To reduce data redundancy (duplicate data)
To maintain data integrity (accuracy and consistency)
To allow efficient retrieval & storage
To provide data security & privacy
To support multi-user access
To ensure backup & recovery
2. RELATIONAL DATA MODEL
A relational database stores data in the form of tables (relations).
2.1 Basic Terms
1) Relation
A table in a database. Example: STUDENT table.
2) Attribute
A column of the table.
Example: RollNo, Name, Class
3) Tuple
A row in a table (record).
4) Domain
The set of possible values an attribute can take.
Example: Grade = {A, B, C, D}
5) Degree
Number of attributes/columns in a relation.
6) Cardinality
Number of tuples/rows in a relation.
2.2 Keys in Relational Model
1) Candidate Key
A minimal set of attributes that uniquely identifies a tuple.
A table can have multiple candidate keys.
2) Primary Key
A chosen candidate key that uniquely identifies each row.
Cannot be NULL or duplicate.
3) Alternate Key
Candidate keys not chosen as the primary key.
4) Foreign Key
A field in one table that refers to the primary key of another table.
Used to maintain referential integrity.
3. STRUCTURED QUERY LANGUAGE (SQL)
3.1 Introduction
SQL is a language used to create, modify, and retrieve data from a relational database.
3.2 SQL Categories
A) Data Definition Language (DDL)
Used to define database structure.
Commands:
CREATE
DROP
ALTER
DESCRIBE
B) Data Manipulation Language (DML)
Used to manipulate data.
Commands:
INSERT
UPDATE
DELETE
SELECT
3.3 SQL Data Types
Data Type Description
char(n) Fixed-length character string
varchar(n) Variable-length string
int Integer values
float Decimal values
date Date values (YYYY-MM-DD)
3.4 SQL Constraints
Constraint Description
NOT NULL Value cannot be left empty
UNIQUE Prevents duplicate values
PRIMARY KEY Unique + Not Null
3.5 Database Commands
1) Create a database
CREATE DATABASE school;
2) Show databases
SHOW DATABASES;
3) Use database
USE school;
4) Drop database
DROP DATABASE school;
3.6 Table Commands
Create table
CREATE TABLE student (
rollno INT PRIMARY KEY,
name VARCHAR(30),
marks INT
);
Show tables
SHOW TABLES;
Describe table
DESCRIBE student;
Alter table
Add column
ALTER TABLE student ADD age INT;
Remove column
ALTER TABLE student DROP COLUMN age;
Add primary key
ALTER TABLE student ADD PRIMARY KEY(rollno);
Remove primary key
ALTER TABLE student DROP PRIMARY KEY;
Drop table
DROP TABLE student;
3.7 DML Commands
Insert data
INSERT INTO student VALUES (1, 'Amit', 85);
Delete data
DELETE FROM student WHERE rollno = 1;
Update data
UPDATE student SET marks = 90 WHERE rollno = 2;
Select data
SELECT * FROM student;
3.8 SQL Operators
1) Mathematical
+, –, *, /, %
2) Relational
=, >, <, >=, <=, <>, !=
3) Logical
AND, OR, NOT
3.9 Important SQL Clauses
Aliasing
SELECT name AS StudentName FROM student;
DISTINCT
SELECT DISTINCT city FROM employee;
WHERE
SELECT * FROM student WHERE marks > 80;
IN
SELECT * FROM student WHERE rollno IN (1, 3, 5);
BETWEEN
SELECT * FROM student WHERE marks BETWEEN 60 AND 80;
ORDER BY
SELECT * FROM student ORDER BY marks DESC;
3.10 Working with NULL
IS NULL
SELECT * FROM student WHERE age IS NULL;
IS NOT NULL
SELECT * FROM student WHERE marks IS NOT NULL;
3.11 LIKE (Pattern Matching)
Pattern Meaning
% Any number of characters
_ Single character
SELECT * FROM student WHERE name LIKE 'A%';
3.12 Aggregate Functions
Function Meaning
MAX() Highest value
MIN() Lowest value
AVG() Average value
SUM() Sum of values
COUNT() Number of rows
Example
SELECT AVG(marks) FROM student;
3.13 GROUP BY
SELECT class, AVG(marks)
FROM student
GROUP BY class;
3.14 HAVING Clause
(Used to filter groups)
SELECT class, AVG(marks)
FROM student
GROUP BY class
HAVING AVG(marks) > 80;
3.15 JOINS
1) Cartesian Product
SELECT * FROM A, B;
2) Equi-Join
SELECT *
FROM student s, class c
WHERE s.class_id = c.class_id;
3) Natural Join
Automatically joins on common attributes.
SELECT * FROM student NATURAL JOIN class;
4. PYTHON - SQL CONNECTIVITY
Python connects to SQL using a database connector (e.g., MySQL Connector).
4.1 Steps for Database Connectivity
1) Import module
import [Link]
2) Establish connection
con = [Link](
host="localhost",
user="root",
passwd="1234",
database="school"
)
3) Create cursor
cur = [Link]()
4) Execute queries
[Link]("SELECT * FROM student")
5) Fetch results
rows = [Link]()
for r in rows:
print(r)
6) Commit changes
[Link]()
4.2 Important Methods
Method Purpose
connect() Creates connection
cursor() Creates cursor object
execute() Executes SQL query
fetchone() Fetch one row
fetchall() Fetch all rows
rowcount Number of affected rows
commit() Save changes
4.3 Insert, Update, Delete using Python
Insert
[Link]("INSERT INTO student VALUES (%s, %s, %s)", (1, "Amit", 80))
[Link]()
Update
[Link]("UPDATE student SET marks=%s WHERE rollno=%s", (90, 1))
[Link]()
Delete
[Link]("DELETE FROM student WHERE rollno=%s", (1,))
[Link]()
4.4 Using format()
name = "Amit"
query = "SELECT * FROM student WHERE name = '{}'".format(name)
[Link](query)