DBMS & SQL

DSSSB TGT CS — Section B P2 (Rank ~9). Focus: keys, NF, ACID, SQL clauses, joins, MySQL functions.

---

1. DBMS vs File System

	File system	DBMS
Data	Application-owned files	Centralized managed data
Redundancy	High / uncontrolled	Controlled
Integrity	App responsibility	Constraints, transactions
Concurrency	Hard	Managed (locks/isolation)
Query	Custom code	Declarative SQL
Security	OS-level mainly	Fine-grained users/roles

DBMS = software to define, create, maintain, and control access to databases.

---

2. Data Models

Model	Idea
Hierarchical	Tree (parent–child)
Network	Graph / many-many via links
Relational	Tables (relations) — dominant exam focus
Object / Object-relational	Objects + relations
Document / NoSQL	Flexible documents (awareness)

---

3. Relational Terms

Term	Meaning
Relation	Table
Attribute	Column
Tuple	Row
Domain	Allowed value set for an attribute
Degree	Number of attributes (columns)
Cardinality	Number of tuples (rows)
Schema	Structure definition
Instance	Data at a moment

---

4. Keys

Key	Definition
Super key	Set of attributes that uniquely identify a tuple
Candidate key	Minimal super key
Primary key	Chosen candidate key (no NULL)
Alternate key	Candidate keys not chosen as PK
Foreign key	Attribute(s) referencing PK/unique of another (or same) relation

• Composite key = multi-attribute key.
• Trap: Every candidate key is a super key; not every super key is candidate (may have extras).

---

5. Normalization (brief)

Form	Rule (exam level)
1NF	Atomic values; no repeating groups
2NF	1NF + no partial dependency on part of composite PK
3NF	2NF + no transitive dependency (non-key → non-key)
BCNF	For every FD X→Y, X is a super key (stricter than 3NF)

Goal: reduce redundancy and update anomalies.

• Trap: BCNF is stricter than 3NF; a relation can be 3NF but not BCNF.

---

6. ACID

Property	Meaning
Atomicity	All-or-nothing transaction
Consistency	DB moves between valid states
Isolation	Concurrent txns don’t interfere wrongly
Durability	Committed data survives crashes

---

7. SQL Language Groups

Group	Purpose	Examples
DDL	Structure	CREATE, ALTER, DROP, TRUNCATE
DML	Data	INSERT, UPDATE, DELETE, SELECT*
DCL	Access	GRANT, REVOKE
TCL	Transactions	COMMIT, ROLLBACK, SAVEPOINT

*Some classify SELECT as DQL; many exam keys still put it under DML/query.

---

8. Core SQL Statements

CREATE TABLE Student(id INT PRIMARY KEY, name VARCHAR(50), age INT);
ALTER TABLE Student ADD marks INT;
DROP TABLE Student;

INSERT INTO Student VALUES (1,'Asha',15);
UPDATE Student SET age=16 WHERE id=1;
DELETE FROM Student WHERE id=1;

SELECT name, age FROM Student WHERE age > 14 ORDER BY name;

JOIN types

Join	Result
INNER	Matching rows only
LEFT OUTER	All left + matches (NULL if none)
RIGHT OUTER	All right + matches
FULL OUTER	All from both (NULLs where no match)
CROSS	Cartesian product

---

9. Aggregates & GROUP BY

Function	Role
COUNT	Number of rows/values
SUM	Total
AVG	Average
MIN / MAX	Extremes

SELECT dept, COUNT(*) FROM Emp GROUP BY dept HAVING COUNT(*) > 5;

• WHERE filters rows before grouping; HAVING filters groups.
• Trap: Non-aggregated SELECT columns must appear in GROUP BY (standard SQL).

---

10. MySQL Functions (exam-common)

String

Fn	Use
CONCAT, LENGTH / CHAR_LENGTH	Join / length
UPPER, LOWER	Case
SUBSTRING / SUBSTR	Slice
TRIM, LTRIM, RTRIM	Spaces
REPLACE	Replace substring

Math

Fn	Use
ABS, CEIL, FLOOR, ROUND	Rounding family
MOD, POW / POWER, SQRT	Arithmetic

Date

Fn	Use
NOW(), CURDATE(), CURTIME()	Current
YEAR, MONTH, DAY	Extract
DATE_ADD, DATE_SUB, DATEDIFF	Arithmetic / difference

---

11. Views & Indexes

Concept	Idea
View	Virtual table from a query; stored definition, not (usually) base data
Index	Auxiliary structure (e.g. B-tree) to speed lookups — tradeoff: space + slower writes

• Views simplify queries and can hide columns.
• Primary key typically auto-indexed.

---

12. ER Diagram Symbols (exam)

Symbol	Meaning
Rectangle	Entity set
Ellipse / oval	Attribute
Diamond	Relationship
Double ellipse	Multivalued attribute
Dashed ellipse	Derived attribute
Double rectangle	Weak entity
Underline	Primary key attribute
Lines	Links entity–attribute / entity–relationship

Cardinality notations: 1:1, 1:N, M:N (crow’s foot or labeled).

---

Quick Revision Traps

1. Degree = columns; cardinality = rows.
2. FK references PK/unique — enforces referential integrity.
3. WHERE vs HAVING.
4. DELETE removes rows; DROP removes table; TRUNCATE empties table (DDL-ish).
5. 2NF about partial dependency; 3NF about transitive.
6. ACID → transaction reliability, not normalization.