Database Management System & SQL – The Comprehensive Guide

1. Introduction to Database Management

A Database Management System (DBMS) is software designed to store, retrieve, define, and manage data in a database. It solves the limitations of traditional file-processing systems, such as Data Redundancy (duplication) and Data Inconsistency (mismatched data across files).

1.1 The Relational Data Model

Proposed by E.F. Codd in 1970, this model organizes data into tables (relations). This is the foundation of SQL. For the PGT exam, precise terminology is essential.

Term	Alternate Name	Definition
Relation	Table	A collection of rows and columns organized to store data.
Tuple	Row / Record	A single horizontal entry representing a complete data set for one item.
Attribute	Column / Field	A vertical entity representing a specific property of the data.
Domain	Data Type	The pool of legal values from which a column draws its data.
Degree	—	The total number of Attributes (Columns) in a relation.
Cardinality	—	The total number of Tuples (Rows) in a relation.

Mathematical Implication:

If Table A has \(M \) rows and \(N \) columns:

• Cardinality = \(M \)
• Degree = \(N \)
• If a new record is inserted, Cardinality becomes \(M + 1 \), but Degree remains \(N \).

---

2. Keys in a Relational Database

Keys are constraints used to establish relationships and ensure uniqueness within a table.

1. Candidate Key: The minimal set of attributes that can uniquely identify a tuple. A table can have multiple Candidate Keys (e.g., Student_ID, Aadhaar_Number, Email).
2. Primary Key (PK): One specific Candidate Key chosen by the database designer to act as the unique identifier for the table.
   • Constraint: It cannot contain NULL values and must be unique.
3. Alternate Key: All Candidate Keys that were not chosen as the Primary Key.
4. Foreign Key (FK): An attribute in one table that refers to the Primary Key of another table. It is used to enforce Referential Integrity.

---

3. Structured Query Language (SQL)

SQL is the standard language for interacting with Relational Databases (RDBMS) like MySQL, Oracle, and PostgreSQL. It is case-insensitive, though keywords are conventionally written in uppercase.

3.1 Classification of SQL Commands

A. Data Definition Language (DDL)

Commands that define or modify the structure (schema) of the database.

• CREATE: Creates a new table or database.
• ALTER: Modifies the structure of an existing table (add/drop columns, change data types).
• DROP: Deletes the entire table structure and data permanently.

B. Data Manipulation Language (DML)

Commands that manipulate the data stored within the tables.

• INSERT: Adds new rows.
• UPDATE: Modifies existing data in rows.
• DELETE: Removes specific rows.
• SELECT: Retrieves data from the database.

Critical Distinction:

• DELETE FROM Table removes all rows but keeps the table structure. (DML)
• DROP TABLE Table removes the rows and the structure. (DDL)

---

4. Data Types and Constraints

When creating a table, specific rules (constraints) are applied to ensure data validity.

4.1 Common Data Types

• CHAR(n): Fixed-length string. If data is shorter than ‘n’, it pads with spaces. Faster for fixed-length data (e.g., Pincodes).
• VARCHAR(n): Variable-length string. Uses only as much space as needed. Efficient for names/addresses.
• INT / INTEGER: Whole numbers.
• DATE: Stores date in YYYY-MM-DD format.

4.2 Constraints

1. NOT NULL: Ensures a column cannot have a NULL value.
2. DEFAULT: Provides a default value if none is specified during insertion.
3. UNIQUE: Ensures all values in a column are distinct.
4. CHECK: Ensures values satisfy a specific condition (e.g., CHECK (Marks BETWEEN 0 AND 100)).
5. PRIMARY KEY: A combination of NOT NULL and UNIQUE.

---

5. SQL Functions

The KVS/NVS syllabus lists specific functions that are frequently tested. These can be categorized into Single-Row functions and Aggregate functions.

5.1 Single-Row Functions

These functions operate on a single row and return one result per row.

A. Math Functions

• POWER(x, y): Returns \(x^y \).
  • SELECT POWER(2, 3); \(\rightarrow \) 8.
• ROUND(number, decimals): Rounds to the specified decimal place.
  • SELECT ROUND(15.729, 2); \(\rightarrow \) 15.73.
  • SELECT ROUND(15.729); \(\rightarrow \) 16.
• MOD(x, y): Returns the remainder of division.
  • SELECT MOD(10, 3); \(\rightarrow \) 1.

B. Text/String Functions

• UCASE(str) / UPPER(str): Converts to uppercase.
• LCASE(str) / LOWER(str): Converts to lowercase.
• MID(str, pos, len) / SUBSTR(str, pos, len): Extracts a substring.
  • Note: SQL indexing starts at 1, not 0.
  • SELECT MID('COMPUTER', 1, 4); \(\rightarrow \) ‘COMP’.
• LENGTH(str): Returns the number of characters (bytes).
• TRIM(str): Removes leading and trailing spaces.
• INSTR(str, substr): Returns the position of the first occurrence of a substring.
  • SELECT INSTR('EXAMINATION', 'AM'); \(\rightarrow \) 3.

C. Date Functions

• NOW(): Returns current date and time.
• DATE(): Extracts the date part from a datetime expression.
• YEAR(date) / MONTH(date) / DAY(date): Extracts respective parts.
• DAYNAME(date): Returns the name of the day (e.g., ‘Monday’).

5.2 Aggregate (Multiple-Row) Functions

These functions ignore NULL values (except COUNT(*)) and return a single summary value.

• MAX() / MIN(): Maximum / Minimum value.
• SUM(): Sum of values.
• AVG(): Average of values.
• COUNT(col): Counts non-null rows in a column.
• COUNT(*): Counts all rows in the table, including NULLs.

---

6. Advanced Querying: Grouping and Sorting

To analyze data, we often need to group records.

6.1 The GROUP BY Clause

Used to group rows that have the same values into summary rows.

• Rule: When using GROUP BY, the SELECT statement can only contain the grouped column and aggregate functions.

SQL

-- Example: Count number of students in each City
SELECT City, COUNT(*) 
FROM Student 
GROUP BY City;

6.2 The HAVING Clause

Used to filter groups created by GROUP BY.

• WHERE vs. HAVING:
  • WHERE: Filters individual rows before grouping. Cannot use aggregate functions.
  • HAVING: Filters groups after grouping. Can use aggregate functions.

SQL

-- Example: Show cities with more than 50 students
SELECT City, COUNT(*) 
FROM Student 
GROUP BY City 
HAVING COUNT(*) > 50;

6.3 The ORDER BY Clause

Sorts the result set.

• ASC: Ascending order (Default).
• DESC: Descending order.
• Usage: SELECT * FROM Student ORDER BY Marks DESC;

---

7. Joins: Working with Multiple Tables

A Join is used to combine rows from two or more tables based on a related column.

7.1 Cartesian Product (Cross Join)

Combines every row of the first table with every row of the second table.

• Cardinality: \(\text{Rows}_A \times \text{Rows}_B \)
• Degree: \(\text{Cols}_A + \text{Cols}_B \)

7.2 Equi-Join

Joins tables using an equality condition (usually PK = FK).

SQL

SELECT Student.Name, Stream.StreamName 
FROM Student, Stream 
WHERE Student.StreamID = Stream.StreamID;