Relational Database Management Systems: Core Concepts
Database Fundamentals
Primary and Candidate Keys
A Candidate Key is a minimal set of attributes that can uniquely identify a tuple. A Primary Key is the candidate key chosen by the designer to uniquely identify records in a table (cannot be NULL).
Integrity Constraints
- Referential Integrity: Ensures that a foreign key value in one table must either match a primary key value in the referenced table or be NULL, maintaining consistency between related tables.
- Entity Integrity: States that the primary key of a table cannot contain NULL values, ensuring every entity (row) is uniquely identifiable.
Relational Algebra and SQL Objects
Relational Algebra is a procedural query language that takes relations (tables) as input and produces relations as output, using operations like Selection (σ), Projection (π), Union (∪), and Join (⋈).
A View is a virtual table based on the result of a SQL query; it does not store data physically but provides a customized presentation of data. A Trigger is a stored procedure that automatically executes in response to a specific event (INSERT, UPDATE, DELETE) on a table.
DELETE vs. TRUNCATE
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Type | DML command | DDL command |
| Scope | Removes specific rows via WHERE | Removes all rows at once |
| Rollback | Can be rolled back | Cannot be rolled back |
| Performance | Slower (row-by-row) | Faster |
Normalization and Dependencies
Functional Dependency
A Functional Dependency X → Y means that the value of attribute Y is uniquely determined by the value of attribute X (X is the determinant).
Normalization Process
Normalization is the process of organizing data to reduce redundancy and avoid anomalies (Insertion, Update, Deletion) by decomposing tables into smaller, well-structured ones.
- 1NF: Atomic values, no repeating groups.
- 2NF: 1NF + no partial dependency.
- 3NF: 2NF + no transitive dependency.
- BCNF: 3NF + for every FD X → Y, X must be a super key.
Advanced SQL and PL/SQL
Aggregate Functions and Cursors
Aggregate functions perform calculations on a set of values and return a single value (e.g., COUNT(), SUM(), AVG(), MAX(), MIN()). A Cursor is a pointer to a context area used to process multiple rows returned by a query, one row at a time.
SQL Joins
A Join combines rows from two or more tables based on a related column:
- INNER JOIN: Returns matching rows in both tables.
- LEFT JOIN: All rows from the left table + matched rows from the right.
- RIGHT JOIN: All rows from the right table + matched rows from the left.
- FULL JOIN: All rows from both tables, with NULLs where no match exists.
Database Design Approaches
- ER Modeling: Top-down conceptual design.
- Universal Relation: Theoretical decomposition starting from one large relation.
- Synthesis: Bottom-up approach using functional dependencies.
- View Integration: Combining multiple user requirements into a global schema.