Essential Database Management Systems Fundamentals

What is a DBMS?

• DBMS: Software used to store, manage, and retrieve data.
• Acts as a bridge between the user and the database.

Advantages of DBMS

• Reduces data redundancy
• Improves data consistency
• Provides data security
• Enables backup and recovery
• Facilitates data sharing

File System vs. DBMS

• File System: Lower security, higher redundancy.
• DBMS: High security, lower redundancy.

Three-Schema Architecture

1. External Level: User view.
2. Conceptual Level: Logical structure.
3. Internal Level: Physical storage.

Schema and Instance

• Schema: Structure of the database (rarely changes).
• Instance: Actual data at a specific moment (changes frequently).

Data Models

• Hierarchical
• Network
• Relational (most used)
• Object-oriented

Relational Model

• Data stored in tables (relations).

Key Terms

• Tuple: Row
• Attribute: Column
• Relation: Table
• Domain: Allowed values

Database Keys

• Primary key
• Candidate key
• Super key
• Foreign key

Integrity Constraints

• Domain constraint
• Entity integrity
• Referential integrity

Relational Algebra

• SELECT
• PROJECT
• UNION
• INTERSECTION
• DIFFERENCE
• JOIN

Types of SQL Commands

• DDL: CREATE, ALTER, DROP
• DML: INSERT, UPDATE, DELETE
• DQL: SELECT
• DCL: GRANT, REVOKE
• TCL: COMMIT, ROLLBACK

SQL Clauses

• WHERE
• GROUP BY
• HAVING
• ORDER BY

SQL Joins

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

Database Views

• Virtual table
• Increases security

What is Normalization?

• Process of reducing data redundancy.
• Improves data consistency.

Problems Without Normalization

• Insertion anomaly
• Deletion anomaly
• Update anomaly

Normal Forms

• 1NF: Atomic values
• 2NF: No partial dependency
• 3NF: No transitive dependency
• BCNF: Strong version of 3NF

Functional Dependency

• A → B (A determines B)

Transaction Management

• A set of operations executed as a single unit.

ACID Properties

• Atomicity: All or nothing.
• Consistency: Valid state.
• Isolation: Transactions do not interfere.
• Durability: Changes are permanent.

Transaction States

• Active
• Partially committed
• Committed
• Failed
• Aborted

Concurrency Problems

• Lost update
• Dirty read
• Unrepeatable read

Concurrency Control

• Locking
• Timestamp ordering

Database Recovery

• Restores database after failure.

Types of Failures

• Transaction failure
• System failure
• Disk failure

Recovery Techniques

• Log-based recovery
• Checkpointing
• Undo & Redo

Database Security

• Protects data from unauthorized access.

Security Techniques

• Authentication
• Authorization
• Encryption
• Backup
• Access control

SQL Injection

• Threat: Attacker injects malicious SQL code.
• Prevention: Prepared statements, input validation.