Chapter 3: SQL Basics
1. Relational Algebra Operators
Relational Algebra provides a set of operations to manipulate and retrieve data stored in
relational databases. Main Operators include: - SELECT (σ): Selects rows from a table
based on a condition. Example: σ(salary > 50000)(EMPLOYEE) - PROJECT (π): Selects
specific columns. Example: π(name, salary)(EMPLOYEE) - UNION (∪): Combines results
of two queries, removes duplicates. - INTERSECTION (∩): Returns common rows from
two relations. - DIFFERENCE (−): Returns rows in one relation but not in another. -
CARTESIAN PRODUCT (×): Combines each row of one table with every row of another. -
JOIN (■): Combines related rows from two or more tables based on a common attribute.
2. Introduction to SQL (Structured Query Language)
SQL is a standard language used to create, manipulate, and retrieve data from relational
databases. Types of SQL statements: - DDL (Data Definition Language): CREATE,
ALTER, DROP - DML (Data Manipulation Language): SELECT, INSERT, UPDATE,
DELETE - DCL (Data Control Language): GRANT, REVOKE - TCL (Transaction Control
Language): COMMIT, ROLLBACK, SAVEPOINT
3. Data Types in SQL
Common SQL Data Types include: - Numeric: INT, FLOAT, DECIMAL - Character/String:
CHAR(n), VARCHAR(n), TEXT - Date/Time: DATE, TIME, DATETIME - Boolean: TRUE
or FALSE
4. Basic SQL Commands
SELECT: Retrieves data from one or more tables. Example: SELECT name, age FROM
STUDENT; INSERT: Adds new records. Example: INSERT INTO STUDENT (name, age)
VALUES ('Aryan', 20); UPDATE: Modifies existing data. Example: UPDATE STUDENT
SET age = 21 WHERE name = 'Aryan'; DELETE: Removes records. Example: DELETE
FROM STUDENT WHERE age < 18;
5. Simple Queries Using SQL
Use conditional, relational, and logical operators: - Conditional: =, <, >, <=, >=, <> -
Logical: AND, OR, NOT Example: SELECT * FROM EMPLOYEE WHERE department =
'Sales' AND salary > 40000;
6. Action Queries
Action queries modify data in the database using INSERT, UPDATE, DELETE, and MAKE
TABLE queries. They change the actual data unlike SELECT which only retrieves data.
Chapter 4: Normalization and Denormalization
1. Functional Dependencies
A functional dependency (FD) exists when one attribute uniquely determines another.
Notation: A → B means if we know A, we can determine B.
2. Dependency Diagram
A visual representation of functional dependencies in a table. Helps identify primary keys,
redundant data, and normal forms.
3. Introduction to Database Normalization
Normalization is a process of organizing data to reduce redundancy and improve data
integrity by dividing large tables into smaller ones and defining relationships between
them.
4. Normal Forms
First Normal Form (1NF): Each cell contains atomic values (no repeating groups). Each
column has a unique meaning. Second Normal Form (2NF): Must be in 1NF and all
non-key attributes depend on the entire primary key (no partial dependency). Third Normal
Form (3NF): Must be in 2NF and no transitive dependency (non-key attributes depend
only on key attributes).
5. Advantages of Normalization (with examples)
- Eliminates data redundancy - Improves data consistency - Easier to maintain and update
data - Saves storage space Example: Before normalization: | RollNo | Name | Course1 |
Course2 | |--------|------|----------|----------| | 1 | Aryan | DBMS | OS | After normalization:
STUDENT(RollNo, Name) COURSE(RollNo, Course)
6. Denormalization
The process of combining normalized tables to improve query performance. It increases
redundancy but reduces join complexity. Used when speed is more important than
consistency.
