Advanced Database Systems

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

 The course will enable the students to

  1. Introduce the students to applications of Database System design.
  2. To demonstrate the principles behind systematic database design approaches.
  3. Develop understandingto manage the Data Resources of Organizations.
  4. To explore the methodologies adopted for implementation of a database in technical and industrial environment.
  5. To understand the application of logical  database  design principles, including E-R diagrams and database normalization.
  6. To learn and understand the concept of transaction and query processing, concurrency, storage and recovery

  Course Learning Outcomes (CLOs):

 

Learning Outcome (at course level)

Students will be able to:

Learning and teaching strategies

Assessment Strategies

  1. Understand applications of Database System design.
  2. Describe the principles behind systematic database design approaches by covering conceptual design, logical design through normalization.
  3. Illustrate and manage the Data Resources of Organizations.
  4. Compare different tools used in Industry.
  5. Experiment and apply  logical  database  design principles, including E-R diagrams and database normalization.
  6. Develop understanding of database transaction and other related database facilities.

Approach in teaching:

Interactive Lectures, Tutorials, Demonstrations, Flipped classes.

 

Learning activities for the students:

Self-learning assignments, Quizzes, Presentations, Discussions

 

  • Assignment
  • Written test in classroom
  • Classroom activity
  • Multiple choice questions
  • Semester End Examination

 

 

 

 

 

10.00
Unit I: 

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