Details of CS2206 (Spring 2027)
| Level: 2 | Type: Laboratory | Credits: 4.0 |
| Course Code | Course Name | Instructor(s) |
|---|---|---|
| CS2206 | Database Management Systems Laboratory | Priyanka Singh
, Saptarshi Pyne |
| Preamble |
|---|
| Data is the new oil. From mobile banking applications to online shops to social media platforms, the data behind every application is efficiently managed by a particular type of software known as the database management system (DBMS). In
this course, we will master the basics of DBMS through hands-on exercises. |
| Syllabus |
|---|
| 1. Database Environment and DBMS Architecture
Introduction to DBMS laboratory environment Installation and configuration of a relational DBMS such as MySQL/PostgreSQL/Oracle Database, schema, table, view, and instance Client-server database architecture Database users, roles, and interfaces Creating, selecting, and managing databases 2. Entity-Relationship Modeling and Database Design Identification of entities, attributes, relationships, and keys Strong and weak entities Cardinality and participation constraints ER diagram development for real-world applications Mapping ER diagrams to relational schemas Database design case study 3. Relational Model and Integrity Constraints Creating relational database schemas Primary key and candidate key Foreign key and referential integrity NOT NULL, UNIQUE, CHECK, and DEFAULT constraints Domain and entity integrity Handling constraint violations Practical exercises on maintaining database integrity 4. SQL Data Definition and Data Manipulation SQL data types CREATE, ALTER, DROP, and TRUNCATE INSERT, UPDATE, and DELETE Simple and compound queries Operators and expressions Filtering, sorting, and limiting results Practical database creation and data population 5. SQL Queries and Relational Operations Selection and projection Joins: inner, outer, self, and cross joins Nested and correlated subqueries Aggregate functions GROUP BY and HAVING Set operations: UNION, INTERSECT, and EXCEPT Relational algebra concepts implemented through SQL queries 6. Advanced SQL: Views, Functions, Procedures, and Triggers Creating and managing views Updatable and non-updatable views Stored procedures User-defined functions Parameters and control structures Database triggers BEFORE and AFTER triggers Automated auditing and validation using triggers 7. Normalization and Database Optimization Functional dependencies Identifying insertion, deletion, and update anomalies Normalization up to 1NF, 2NF, 3NF, and BCNF Decomposition of relational schemas Lossless-join and dependency-preserving decomposition Index creation and management Introduction to query execution plans Basic query optimization techniques 8. Transactions, Concurrency Control, and Database Recovery Transaction properties and ACID COMMIT, ROLLBACK, and SAVEPOINT Transaction schedules Concurrent transaction problems Lock-based concurrency control Shared and exclusive locks Deadlocks Isolation levels Database recovery and backup/restore Practical transaction and concurrency experiments 9. Database Security, Authorization, and SQL Injection Database users and roles GRANT and REVOKE Privileges and authorization Role-based access control Data security and access management SQL injection vulnerabilities Prepared statements and parameterized queries Secure database programming Basic auditing and security practices |
| Prerequisite |
|---|
| CS1101: Introduction to Computer Programming (or the permission of instructor(s)) |
| References |
|---|
| References
Bayross, I. (2010). SQL, PL/SQL - The Programming Language of Oracle. 4th Revised Edition. BPB Publications. https://neon.com/postgresql/tutorial |
Course Credit Options
| Sl. No. | Programme | Semester No | Course Choice |
|---|---|---|---|
| 1 | IP | 2 | Not Allowed |
| 2 | IP | 4 | Not Allowed |
| 3 | MP | 2 | Not Allowed |
| 4 | MP | 4 | Not Allowed |
| 5 | MR | 2 | Not Allowed |
| 6 | MR | 4 | Not Allowed |
| 7 | MS | 10 | Not Allowed |
| 8 | MS ( Computational and Data Sciences ) | 4 | Core |
| 9 | MS | 6 | Not Allowed |
| 10 | MS | 8 | Not Allowed |
| 11 | RS | 1 | Not Allowed |
| 12 | RS | 2 | Not Allowed |