Introduction to Database Systems

Concept & Overview of Database Management Systems (DBMS) - Characteristics of Database system, Database Users, structured, semi-structured and unstructured data. Data Models and Schema - Three Schema architecture. Database Languages, Database architectures, and classification.ER model - Basic concepts, entity set and attributes, notations, Relationships and constraints, cardinality, participation, notations, weak entities, relationships of degree 3.

Structure of Relational Databases - Integrity Constraints, Synthesizing ER diagram to relational schema. Introduction to Relational Algebra - select, project, cartesian product operations, join - Equi-join, natural join. query examples, introduction to Structured Query Language (SQL), SQL data types, Data Definition Language (DDL), Table definitions and operations – CREATE, DROP, ALTER, INSERT, DELETE, UPDATE.

SQL DML (Data Manipulation Language) - SQL queries on single and multiple tables, Nested queries (correlated and non-correlated), Aggregation and grouping, Views, and Assertions. PL/SQL-Functions, Procedures, Triggers, and Cursors. Physical Data Organization - Review of terms: physical and logical records, blocking factor, pinned and unpinned organization. Heap files, Indexing, Singe level indices, numerical examples, Multi-level indices, numerical examples, B-Trees & B+-Trees (structure only, algorithms not required), Extendible Hashing.

Different anomalies in designing a database, The idea of normalization, Functional dependency, Armstrong’s Axioms (proofs not required), Closures and their computation, Equivalence of functional Dependencies (FD), and Minimal Cover (proofs not required). First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), Boyce Codd Normal Form (BCNF).

Transaction Processing Concepts - Overview of concurrency control, Transaction Model, Significance of concurrency control and recovery, Transaction States, System Log, and Desirable Properties of Transactions. Serial schedules, Concurrent and Serializable Schedules, Conflict equivalence and conflict serializability, Recoverable and cascade-less schedules, Locking, Two-phase locking, and its variations. Log-based recovery, Deferred database modification, check-pointing. Introduction to NoSQL Databases, Main characteristics of Key-value DB (examples from Redis), Document DB (examples from MongoDB) Main characteristics of Column- Family DB (examples from Cassandra), and Graph DB (examples from: Arango DB) Java Database Connectivity (JDBC) - JDBC overview, Creating and Executing Queries – create table, delete, insert, select.