Advanced Database Systems

Paper Code: 
MCA 324
Credits: 
04
Periods/week: 
04
Max. Marks: 
100.00
Objective: 

This course has been designed to introduce the students to applications of systems designed to manage the data resources of organizations. It provides them an opportunity to study the hands-on implementation of a database in technical and industrial environment.

10.00
Unit I: 
Introduction & Overview of SQL

Introduction & Overview of SQL: Data Independence, Architecture of DBMS, Data Models, Normalization (1NF, 2 NF, 3NF, BCNF, 4NF, 5NF),  inclusion dependencies, loss less join decompositions, Codd’s rules, Relational Data Model & Relational Algebra, SQL data types and literals, Types of SQL commands: DDL, DML, TCL, DCL, SQL Operators, Constraints.

12.00
Unit II: 
Database Querying

Steps In Query Processing, Algorithms For Selection, Sorting And Join Operations, Understanding Cost Issues In Queries, Query Optimization, Transformation Of Relational Expressions, Query Evaluation Plans.

12.00
Unit III: 
Transaction Management & Concurrency Control

Transactions:  Concepts, ACID Properties, States Of Transaction, Serializaibility, Conflict & View Serializable Schedule, Checkpoints, Deadlock Handling.

Concurrency Control: Locks Based Protocols, Time Stamp Based Protocols, Validation Based Protocol, Multiple Granularity, Multi-version Schemes

10.00
Unit IV: 
Recovery System & Security

Failure Classifications, Recovery & Atomicity, Log Base Recovery, Recovery With Concurrent Transactions, Shadow Paging, Failure With Loss Of Non-Volatile Storage, Recovery From Catastrophic Failure.

Introduction to Security & Authorization

16.00
Unit V: 
Introduction to SQL & PL/SQL

SQL: Group By and Having Clause, Order By Clause, Queries and sub queries, Functions (Aggregate/ Numeric/ String/ Date & Time), Union, Intersection, Minus, Joins.

PL/SQL Basics, Blocks, Architecture, Variables, Constants, Attributes, Character Set, Data Types, Conditional Statements, Iteration, Cursors, Exceptions, Triggers, Procedures, Functions And Packages.

ESSENTIAL READINGS: 
  • Elmasri, Navathe, “Fundamentals of Database Systems”, Addison Wesley, 6th Edition, 2011
  • Korth, Silberschatz, Sudarshan, “Database Concepts”, McGraw Hill, 6th Edition, 2010
REFERENCES: 
  • Thomas Connolly and Carolyn Begg, “Database Systems: A Practical Approach to Design, Implementation, and Management, Addison Wesley , 5th Edition, 2010
  • Ramakrishnan, Gehrke, “Database Management System”, McGraw Hill, 3rd Edition, Jan 2007
  • Date C J, “An Introduction to Database System”, Addision Wesley, 8th Edition 2003
  • Bipin C. Desai, “An Introduction to Database Systems”, Galgotia Publication, Revised Edition, 2010
  • Majumdar & Bhattacharya, “Database Management System”, TMH, 2005
  • Paul Beynon Davies, “Database Systems”, Palgrave Macmillan, 3rd Edition, 2003
Academic Year: 
2018-19
  • Printer-friendly version