Course

1. Write advanced SQL queries using subqueries, nested subqueries, and correlated subqueries.
2. Implement and apply joins (inner, outer, natural, self joins) and set operations (UNION, INTERSECT, EXCEPT) on multitable databases.
3. Develop SQL queries using EXISTS, WITH clause (CTE), and recursive queries for hierarchical data representation.
4. Create and manipulate views, including updatable views, and analyze their role in logical data abstraction.
5. Develop PL/SQL programs using variables, control structures, procedures, functions, cursors, and exception handling.
6. Simulate transaction processing, demonstrating ACID properties, transaction states, serializability, and locking mechanisms.
7. Implement case studies illustrating concurrency control issues such as lost updates, dirty reads, nonrepeatable reads, and phantom reads.
8. Design SQL queries to demonstrate parallel execution plans and observe query behavior using database explain/analysis tools.
9. Implement objectrelational database features:
   • Creation of complex data types
   • Structured userdefined types
   • Methods and inheritance in SQL
10. Create and manipulate databases using table inheritance and array data types, and write queries on inherited tables.
11. Create and manipulate JSON data in databases:
    • JSON syntax and datatypes
    • Storing JSON objects
    • Querying JSON attributes
12. Design and query XML databases using:
    • XML schema / DTD
    • XPath / XQuery
    • Storage and retrieval of XML data
13. Comparative case study:
    • Compare JSON vs XML for data representation
    • Use JSON with PHP or Python to interact with the database
    • Demonstrate a small integrated application

Course Description

The course gives theoretical knowledge and practical skills in the various aspects of databases including architecture, advanced queries, query processing, optimization, and the diverse types of databases including object-based databases which will help students to pursue a career in this field. The students will become more proficient in writing queries as well as in doing database design for a distributed database-oriented application.

Course Objectives

• To improve the database design skills of the students

• To develop strong foundations of DBMS for industry-level competence

• To use complex databases for scalable performance

Course Outcomes

CO-PO Mapping

• Database Systems Concepts, Silberschatz, Abraham, Henry F. Korth, and S.Sudarshan. McGraw-Hill, ISBN 9780078022159, 2019, 7th Edition 
• Fundamentals of Database Systems, Ramez Elmasri and Shamkant Navathe, 7th Edition, Addison Wesley, 2015 (Unit V) 
• Database Systems: The Complete Book, Hector Garcia-Molina, Jeffrey Ullman and Jennifer Widom, Second Edition, Prentice Hall, 2008 
• PostgreSQL Documentation.