Course Description

This course provides a comprehensive introduction to Database Management Systems (DBMS) and Structured Query Language (SQL). It covers the fundamental concepts of database design using ER diagrams, relational algebra, and normalization to ensure data integrity. Students will explore advanced topics such as transaction management, ACID properties, serializability, and file indexing structures like B-Trees. Additionally, the course offers practical training in SQL, covering Data Definition Language (DDL), Data Manipulation Language (DML), complex joins, and aggregate functions for robust data retrieval and management.

Course Topics Outline

• Introduction to DBMS
  • Definition of Database and DBMS.
  • Comparison between DBMS and File-processing systems.
  • Key challenges addressed by DBMS (redundancy, inconsistency, security).
• Data Modeling and Design
  • ER Diagrams: Entity sets (strong vs. weak), attributes, and relationship sets.
  • Relationship Types: Unary, Binary, Ternary, and N-ary.
  • Cardinality Constraints: One-to-One, One-to-Many, Many-to-One, and Many-to-Many.
  • Attribute Types: Simple, Composite, Multi-valued, Derived, and Key attributes.
• Relational Model and Constraints
  • Relational Constraints: Domain, Tuple Uniqueness, Key, Entity Integrity, and Referential Integrity.
  • Key Concepts: Super Keys, Candidate Keys, Primary Keys, Alternate Keys, Foreign Keys, Composite Keys, and Unique Keys.
• Normalization and Functional Dependencies
  • Functional Dependency: Trivial and Non-Trivial dependencies.
  • Decomposition: Lossless vs. Lossy join decomposition and dependency preservation.
  • Normal Forms: 1NF, 2NF, 3NF, and Boyce-Codd Normal Form (BCNF).
• Transaction Management
  • Transaction Operations: Read and Write.
  • Transaction States: Active, Partially Committed, Committed, Failed, Aborted, and Terminated.
  • ACID Properties: Atomicity, Consistency, Isolation, and Durability.
• Concurrency Control and Schedules
  • Schedules: Serial vs. Non-Serial schedules.
  • Serializability: Conflict and View serializability.
  • Recoverability: Irrecoverable, Recoverable, Cascading, Cascadeless, and Strict schedules.
• Relational Algebra
  • Basic Operators: Selection, Projection, Cross Product, Union, Minus, and Rename.
  • Extended Operators: Intersection, Joins (Conditional, Equi, Natural), Outer Joins (Left, Right, Full), and Division.
• Storage and Indexing
  • File Structures: Primary, Clustering, and Secondary indexing.
  • Tree-based Indexing: Properties of B Trees and B+ Trees.
• Structured Query Language (SQL)
  • SQL Subsets: DDL (CREATE, ALTER, DROP, TRUNCATE), DML (SELECT, INSERT, UPDATE, DELETE), DCL (GRANT, REVOKE), and TCL (COMMIT, ROLLBACK, SAVEPOINT).
  • Data Retrieval: WHERE clause, AND/OR/NOT operators, ORDER BY, SELECT DISTINCT, and SELECT TOP/LIMIT.
  • Pattern Matching and Ranges: LIKE operator (wildcards % and _) and BETWEEN.
  • Data Aggregation: MIN, MAX, COUNT, AVG, SUM, and the GROUP BY and HAVING clauses.
  • Advanced SQL: Joins (Inner, Left, Right, Full), UNION, and handling NULL values.
  • Schema Management: Creating/dropping databases and tables, and altering table structures.