|
Objectives:
To develop understanding of concepts and techniques for data management and learn about
widely used systems for implementation and usage.
Expected Learning Outcomes:
1. Master concepts of stored procedure and triggers and its use.
2. Learn about using PL/SQL for data management
3. Understand concepts and implementations of transaction management and crash
recovery
Unit I
Stored Procedures: Types and benefits of stored procedures, creating stored
procedures, executing stored procedures, altering stored procedures, viewing
stored procedures.
Triggers: Concept of triggers, Implementing triggers – creating triggers,
Insert, delete, and update triggers, nested triggers, viewing, deleting and
modifying triggers, and enforcing data integrity through triggers.
Sequences: creating sequences, referencing, altering and dropping a sequence.
File Organization and Indexing: Cluster, Primary and secondary indexing,
Index data structure: hash and Tree based indexing, Comparison of file
organization: cost model, Heap files, sorted files, clustered files. Creating,
dropping and maintaining indexes.
15L
Fundamentals of PL/SQL: Defining variables and constants, PL/SQL
expressions and comparisons: Logical Operators, Boolean Expressions, CASE
Expressions Handling, Null Values in Comparisons and Conditional
Statements, PL/SQL Datatypes: Number Types, Character Types, Boolean
Type, Datetime and Interval Types.
Unit II
Overview of PL/SQL Control Structures: Conditional Control: IF and
CASE Statements, IF-THEN Statement, IF-THEN-ELSE Statement,
IFTHEN-ELSIF Statement, CASE Statement, Iterative Control: LOOP and
EXIT Statements, WHILE-LOOP, FOR-LOOP, Sequential Control: GOTO
and NULL Statements
15L
Unit III
Transaction Management: ACID Properties, Serializability, Two-phase
Commit Protocol, Concurrency Control, Lock Management, Lost Update
Problem, Inconsistent Read Problem , Read-Write Locks, Deadlocks Handling,
Two Phase Locking protocol.
DCL Statements: Defining a transaction, Making Changes Permanent with
COMMIT, Undoing Changes with ROLLBACK, Undoing Partial Changes
with SAVEPOINT and ROLLBACK
Crash Recovery: ARIES algorithm. The log based recovery, recovery related
structures like transaction and dirty page table, Write-ahead log protocol, check
points, recovery from a system crash, Redo and Undo phases.
15L
Textbook(s):
1) Ramakrishnam, Gehrke, Database Management Systems, Bayross, McGraw‐Hill,3rd Edition
2) Abraham Silberschatz, Henry F. Korth,S. Sudarshan , Database System Concepts, 6th Edition
3) Ivan Bayross, “SQL,PL/SQL -The Programming language of Oracle”, B.P.B. Publications
Additional Reference(s):
1) Ramez Elmasri & Shamkant B.Navathe, Fundamentals of Database Systems,
Pearson Education
2) Robert Sheldon, Geoff Moes, Begning MySQL, Wrox Press.
3) Joel Murach, Murach’s MySQL, Murach