Syllabus: DBMS

 Course Objectives:

1. Understand database management systems architecture and functioning.
2. Learn the relational model.
3. Use structured query language (SQL) and its syntax, transactions, database recovery, and query optimization techniques.
4. Acquaint with various normalization forms and query processing.
5. Learn indexing methods.

Course Outcomes: After completing the course, students should be able to:

1. Explain the need for database management.
2. Design and implement a database schema for a given problem domain.
3. Normalize a database.
4. Create and query a database using SQL DML/DDL commands, stored procedures, and functions.
5. Declare and enforce integrity constraints on a database.
6. Demonstrate an understanding of indexing methods.

Course Contents: UNIT I: Introduction and Entity-Relationship Model

• Basic concepts, advantages of DBMS over file-processing systems
• Data abstraction, data models, and data independence
• Components and overall structure of DBMS
• Data modeling, entities, attributes, relationships, constraints
• Keys, E-R diagrams, and components of the E-R model

UNIT II: Relational Model and SQL-I

• Basic concepts of the relational model
• Attributes, domains, integrity and referential constraints
• Schema diagram, relational query languages
• Relational algebra and relational calculus
• Tuple and domain relational calculus
• Introduction to SQL, characteristics, advantages, data types, and literals
• DDL, creating, modifying, and deleting tables

UNIT III: Views and SQL-II

• Creating, dropping, and updating views
• DML operators, SQL DML queries, SELECT query and clauses
• Set operations, predicates, and joins
• Set membership, tuple variables, set comparison
• Ordering of tuples, aggregate functions
• Nested queries, database modification using SQL
• Insert, update, and delete queries
• Dynamic SQL and stored procedures
• Query-by-example

UNIT IV: Relational Database Design and Storage Systems

• Notion of normalized relations
• Functional dependency, decomposition, and properties
• Normalization using functional dependency, multi-valued dependency, and join dependency
• Storage systems: secondary storage, RAID, file organization
• Indices, static and dynamic hashing
• B-Trees and B+ Trees

UNIT V: Query Processing and Transaction Management

• Measures of query cost
• Selection operation, sorting, and join operation
• Transaction concept, components of transaction management
• Concurrency and recovery system
• Concurrency control protocols (timestamps, locking), validation
• Multiple granularity, deadlock handling
• Crash recovery methods (log-based recovery, shadow-paging)
• Buffer management and remote backup system

Text Books:

1. Abraham Silberschatz, Henry F. Korth, and S. Sudarshan, "Database System Concepts," McGraw Hill Education, 6th Edition, 2011.
2. Ramez Elmasri and Shamkant B. Navathe, "Fundamental Database Systems," Pearson Education, 7th Edition, 2015.
3. Raghu Ramkrishnan, Johannes Gehrke, "Database Management Systems," McGraw Hill Education, 3rd Edition, 2007.

Reference Books:

1. Carlos Coronel, Steven Morris, "Database Systems: Design Implementation and Management," Cengage Learning Press, 11th Edition, 2014.
2. J. Murach, "Murach's MySQL," Shroff Publication, 2nd Edition, 2016.
3. J. Murach, "Murach's Oracle SQL and PL/SQL: Works with All Versions Through 11g," Shroff Publication, 2008.

Share this

p L