Database Management Systems

Course Objectives :

1. To introduce basic concepts, architecture and characteristics of database systems
2. To introduce relational model concepts and PL/SQL programming
3. To introduce relational database design and Normal forms based on functional dependencies
4. To introduce concepts of object oriented & distributed databases

Course Outcomes (CO) :

• CO 1 Ability to understand advantages of database systems
• CO 2 Ability to use SQL as DDL, DCL and DML
• CO 3 Ability to design database and manage transaction processing
• CO 4 Understand object oriented & distributed databases systems and use them

UNIT – I

Basic concepts: database & database users, characteristics of the database systems, concepts and architecture, date models, schemas & instances, DBMS architecture & data independence, database languages & interfaces, data modelling using the entity-relationship approach. Enhanced ER concepts - Specialization/Generalization, Aggregation, Mapping of ER model to Relational Model.

SQL – DDL, DCL & DML views and indexes in SQL. Basics of SQL, DDL, DML,DCL, structure – creation, alteration, defining constraints – Primary key, foreign key, unique, not null, check, IN operator.

UNIT - II

Relational model concepts, relational model constraints, relational algebra, relational calculus.

SQL – Functions - aggregate functions, Built-in functions – numeric, date, string functions, set operations, subqueries, correlated sub-queries, Use of group by, having, order by, join and its types, Exist, Any, All , view and its types. Transaction control commands – Commit, Rollback, Save point.

UNIT - III

Relational data base design: functional dependencies & normalization for relational databases, normal forms based on functional dependencies, (1NF, 2NF, 3NF & BCNF), lossless join and dependency preserving decomposition, normal forms based on multivalued & join dependencies (4NF & 5NF) & domain key normal form. Properties of Transaction, Transaction states, Transaction Schedule, Serializability, Concurrency control techniques, locking techniques, time stamp ordering, Recoverable schedules, granularity of data items, Deadlock detection and Recovery, recovery techniques: recovery concepts, database backup and recovery from catastrophic failures. Database Programming – control structures, exception handling, stored procedures, Triggers.

UNIT - IV

File Structures and Indexing: Secondary Storage Devices, Operations on Files, Heap Files, Sorted Files, Hashing, Single level indexes, Multi-level indexes, B and B+ tree indexes. Concepts of Object Oriented Database Management systems & Distributed Database Management Systems

Textbooks:

1. R. Elmsari and S. B. Navathe, “Fundamentals of database systems”, Pearson Education, 7th Edition, 2018
2. V. M. Grippa and S. Kumichev, “Learning MySQL”, O’Reilly, 2021.
3. SQL/ PL/SQL, The programming language of Oracle, Ivan Bayross, 4th Edition BPB Publications

References:

1. A. Silberschatz, H. F. Korth and S. Sudershan, “Database System Concept”, McGraw Hill, 6th Edition,2013.
2. Date, C. J., “An introduction to database systems”, 8th Edition, Pearson Education, 2008.
3. P. Rob & C. Coronel, “Database Systems: Design Implementation & Management”, Thomson Learning, 6th Ed. 2004
4. Desai, B., “An introduction to database concepts”, Galgotia publications, 2010
5. H. Garcia-Molina, J. D. Ullman, J. Widom, “Database System: The Complete Book”, PH.
6. Joel Murach, Murach's Mysql””, 3rd Edition-Mike Murach and Associates, Incorporated, 2019.
7. Oracle and MySQL manuals.