Database Management Systems

Database management systems vs. file systems, ANSI/SPARC architecture (external, conceptual, internal levels), Data independence (logical/physical), Entity-Relationship model (entities, relationships, attributes, keys), ER diagram notation and cardinality constraints, Enhanced ER (EER) features (specialization, generalization, aggregation), Converting ER diagrams to relational schemas.

Relational data model (domains, relations, tuples), Codd's relational algebra operations (selection, projection, union, set difference, Cartesian product, joins), Relational calculus (tuple, domain), SQL fundamentals (DDL, DML, DCL, TCL), Nested queries and correlated subqueries, Aggregate functions, GROUP BY, HAVING, EXISTS/IN operators, Views and virtual tables.

ACID properties, Transaction states and implementation, Serializability (conflict serializability, view serializability), Concurrency control protocols (two-phase locking, timestamp ordering), Deadlock prevention, detection, recovery, Granularity of locking (database, table, row level), Isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable).

Physical file organization (heap, sequential, hashed), RAID levels and redundancy, Index structures (primary, secondary, clustered, non-clustered), B+ tree index organization and search algorithms, Dynamic hashing (extendible, linear), Query processing (cost estimation, equivalence rules), Join algorithms (nested loop, block nested loop, sort-merge, hash join).

Query execution plans and optimization strategies, Heuristic optimization rules, Cost-based optimization, Distributed databases (fragmentation, replication, transparency), Query decomposition and distributed query processing, Object-relational databases, XML/NoSQL integration concepts, Database recovery techniques (log-based, checkpointing, shadow paging).