Course Objectives:
- Understand database management systems architecture and functioning.
- Learn the relational model.
- Use structured query language (SQL) and its syntax, transactions, database recovery, and query optimization techniques.
- Acquaint with various normalization forms and query processing.
- Learn indexing methods.
Course Outcomes: After completing the course, students should be able to:
- Explain the need for database management.
- Design and implement a database schema for a given problem domain.
- Normalize a database.
- Create and query a database using SQL DML/DDL commands, stored procedures, and functions.
- Declare and enforce integrity constraints on a database.
- 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:
- Abraham Silberschatz, Henry F. Korth, and S. Sudarshan, "Database System Concepts," McGraw Hill Education, 6th Edition, 2011.
- Ramez Elmasri and Shamkant B. Navathe, "Fundamental Database Systems," Pearson Education, 7th Edition, 2015.
- Raghu Ramkrishnan, Johannes Gehrke, "Database Management Systems," McGraw Hill Education, 3rd Edition, 2007.
Reference Books:
- Carlos Coronel, Steven Morris, "Database Systems: Design Implementation and Management," Cengage Learning Press, 11th Edition, 2014.
- J. Murach, "Murach's MySQL," Shroff Publication, 2nd Edition, 2016.
- J. Murach, "Murach's Oracle SQL and PL/SQL: Works with All Versions Through 11g," Shroff Publication, 2008.
