Fourth Semester
Database Management System
Course Description: The course covers the basic concepts of databases, database system concepts and architecture, data modeling using ER diagram, relational model, SQL, relational algebra and calculus, normalization, transaction processing, concurrency control, and database recovery.
Course Objectives: The main objective of this course is to introduce the basic concepts of database, data modeling techniques using entity relationship diagram, relational algebra and calculus, basic and advanced features SQL, normalization, transaction processing, concurrency control, and recovery techniques
Contents of Chapter |
|---|
Unit 1: Database and Database Users (2 Hrs.)Traditional file processing system; Definition of database and database management system with example, Self-describing nature of a database system; Insulation between programs and data, and data abstraction; Support of multiple views of the data; Sharing of data and multiuser transaction processing, Database administrators; Database designers; End users; System Analysts and Application Programmers, DBMS system designers and implementers; Tool developers; Operators and maintenance personnel, Controlling redundancy; Restricting unauthorized access; Providing persistent storage; Providing storage structures and search techniques for efficient query processing; Providing backup and recovery; providing multiple user interfaces; Enforcing integrity constraints; Reduced application development time; Flexibility; Availability of upto-date information; Economies of scale |
Unit 2:Database System – Concepts and Architecture (3 Hrs.)Definition of data abstraction and data model; Categories of data models (high level, low level, and representational data models) – Introduction to entity-relationship model, relational data model, network data model, hierarchical model, network model, object data model, and self-describing data models; Concept of schema and instance, Concept of three-schema architecture; Logical and physical data independence, Concept of DDL, SDL, VDL, DML, procedural and non-procedural languages; Concept of interfaces , Concept of database system environment, Basics of centralized and client/server architectures, Classification based on data models, number of users, number of sites, cost and type of access path |
Unit 3: Data Modelling Using the Entity-Relational Model (6 Hrs.)Concept of conceptual design, Concept of entity types, entity sets, attributes, and keys; Concept of relationship types and relationship sets, roles and constraints, Concept of weak entity types and partial keys, Drawing ER diagrams using ER notations, naming conventions and design issues, Concept of higher degree relationships, Concept of enhanced ER (EER) model, superclasses, subclasses and subclasses, Concept of specialization and generalization, Different constraints and characteristics of specialization and generalization |
Unit 4: The Relational Data Model and Relational Database Constraints (3 Hrs.)Concept of domain, attributes, tuples, and relations; Characteristics of relations; Relational model notation, Different categories of constraints; Domain constraints; Key and NULL values constraints;, Relational databases and relational database schemas; Entity integrity, referential integrity, and foreign key, Concept of insert, delete, and update operations; Concept of transactions |
Unit 5: The Relational Algebra and Relational Calculus (5 Hrs.)Concept and example of SELECT and PROJECT operations; Sequences of operations; RENAME operation, Concept and example of UNION, INTERSECTION, MINUS, and CARTESIAN PRODUCT operations, Concept and example of JOIN operation and its variations; Concept and example of DIVISION operation, Concept of generalized projection, aggregate functions, OUTER JOIN operations, and OUTER UNION operation, Introduction to tuple relational calculus with examples , Introduction to domain relational calculus with examples |
Unit 6: SQL (8 Hrs.)CREATE TABLE command; Attribute data types and domains; ALTER and DROP commands, Attribute constraints and attribute defaults; Key and referential integrity constraints, SELECT-FROM-WHERE structure; Ambiguous attribute names, aliasing, renaming, and tuple variables; Unspecified WHERE clause and use of asterisk (*); Pattern matching and arithmetic operators, Comparisons involving NULL; Nested queries, Concept and example of INSERT, DELETE, and UPDATE commands, Concept of views; CREATE VIEW command |
Unit 7: Relational Database Design (7 Hrs.)Converting ER / EER models to relations with examples, Imparting clear semantics to attributes in relations; Redundant information in tuples and update anomalies; NULL values in tuples; Generation of spurious tuples, Definition and concept of functional dependencies with example, Concept of normalization; Practical use of normal forms; Keys and attributes participating in keys; Concept of first, second, and third forms with example, General definitions of second and third normal forms, Concept and example of boyce-codd normal form, Definition and concept of multivalued dependencies with example; Concept of fourth normal form, Dependency preservation property and nonadditive (lossless) join property |
Unit 8:Introduction to Transaction Processing Concepts and Theory (4 Hrs.)Single-user versus multiuser system; Transactions, Database items, Read and write operations, and DBMS buffers; Why do we need concurrency control? Why do we need recovery? , Transaction states and operations; The system log; Commit point; Buffer replacement policies , Desirable properties of transactions, Concept of schedule; Characterizing schedules based on recoverability, Conflict serializability; Testing for conflict serializability; View equivalent and view seializability; How serializability is used for concurrency control |
Unit 9: Concurrency Control Techniques (4 Hrs.)Concept of two-phase locking; Types of locks and system lock tables; Lock conversion; Guaranteeing serializability by two-phase locking; Basic, conservative, strict, and rigorous two-phase locking; Dealing with deadlock and starvation, Timestamp ordering concurrency control concept; Basic and strict timestamp ordering; Thomas’s Write rule , Concept of multiversion concurrency control technique; Multiversion technique based on timestamp ordering; Multiversion locking using certify locks, Concept of validation (optimistic) techniques and snapshot isolation concurrency control |
Unit 10: Database Recovery Techniques (3 Hrs.)Recovery outline and categorization of recovery algorithms; Caching (Buffering) of disk blocks; Writeahead logging, steal/no-steal, and force/no-force; Checkpoints and fuzzy checkpointing; Transaction rollback and cascading rollback , Concept of no-undo/redo recovery based on deferred update, Concept of recovery technique based on immediate update, Concept of Shadow Paging, Concept of database backup and recovery from catastrophic failures |