DBMS-II

Paper Code: 
CBCA 501
Credits: 
03
Periods/week: 
03
Max. Marks: 
100.00
Objective: 

The course will enable the students to

  1. Learn various advance concepts of transaction processing and recovery.
  2. Applying MySQL to design and manipulate databases.

Course Outcomes (COs):

Learning Outcome (at course level)

Learning and teaching strategies

Assessment Strategies

The students will:

CO 196.Analyse and compare transaction processing and concurrency control techniques

CO 197.Evaluate concurrency control techniques and its role in maintaining the database’s integrity.

CO 198.Implement different types of DDL, DML and DCL statements in MySQL.

CO 199.Choose the type of joins to create and manipulate queries and subqueries for different problems.

CO 120.Create database connectivity through MySQL to Java.

Approach in teaching:

Interactive Lectures, Discussion, Tutorials, Reading assignments, Demonstration,

Learning activities for the students:

Self-learning assignments, Effective questions, Simulation, Seminar presentation, Giving tasks.

Class test, Semester end examinations, Quiz, Solving problems in tutorials, Assignments, Presentation, Individual and group projects

 

 

9.00
Unit I: 

Transaction Processing: Introduction to Transaction Processing, Transaction and System concepts, Desirable properties of Transactions. Characterizing Schedules Based on Recoverability, Characterizing Schedules Based on Serializability.

Concurrency control techniques: Locking techniques for concurrency control, Concurrency control based on Timestamp ordering.

 

9.00
Unit II: 

Database recovery concepts: Recovery outline, Caching of Disk blocks, Write ahead logging, Steal/No Steal and Force/No Force, Checkpoints in the System Log and Fuzzy Check pointing, Transaction Rollback. Recovery Techniques Based on Deferred Update and on Immediate Update.

Database Integrity and Security: Domain constraints, Referential Integrity, Introduction to Assertions, Need for Triggers, Security violation and Authorization.

 

9.00
Unit III: 

Relational Database Management Systems: Brief history of MySQL, Key Features, MySQL server and Clients, MySQL and SQL, Classification of SQL statements, Some MySQL SQL elements (Literals, Data Types, Null values, comments), SQL command syntax.

Databases in MySQL (Create, Open, Remove). Creating Tables: Create Command. Viewing structure of table using DESC command.

Changing data with DML commands: INSERT INTO command, inserting values through substitution, inserting NULL values, inserting dates, inserting data from other table.

Select Statement, using WHERE clause.

Operators: Relational operators, Logical operators. Condition based on a range, list, pattern match.

Searching and Sorting: Searching for NULL (IS NULL), Sorting results (ORDER BY Clause), Sorting by Column Alias. Grouping result – Group by command, Having Clause. Eliminating redundant data with Distinct keyword.

MySQL Functions and Grouping: Types of MySQL Functions (String, Numeric, Date/Time).

 

9.00
Unit IV: 

Changing data with DML commands: Modifying data with update command, Deleting data with delete command. DDL commands: ALTER TABLE command, adding columns, modifying data-type and size of a column, DROP TABLE.

Managing Constraints: Creating constraints, Unique, Primary Key, Default, Check and Foreign Key, table constraints, dropping constraints, enabling and disabling constraints, deferring constraints checks. Sub Queries: Concept of Sub-Query, Sub Query to Solve a Problem, Guidelines for Using Sub Queries, Types of Sub-Queries (Single Row and Multiple Row) and (Single Column and Multiple Column); Single Row Sub-Query and its Execution.

Displaying Data from Multiple Tables: Concept of Join, Result of Join, Cartesian Product and Generating Cartesian Product example using Mathematical Set), Types of Joins, AND operator, Table Aliases.

 

9.00
Unit V: 

Database connectivity to MySQL: Introduction, Classes used for Database connectivity, Prerequisites for connecting to MySQL from Java, Connecting to MySQL from Java.

 

ESSENTIAL READINGS: 

1.   R. Elmasri and S. B. Navathe, “Fundamentals of Database Systems”, Addison Wesley, 3th Ed., 2000

2.   Sumita Arora, “Informatics Practices”, Dhanpat Rai & Co.

 

REFERENCES: 
  1. James R. Groff & Paul N. Weinberg, “The Complete Reference SQL”, Tata McGraw Hill.
  2. Alexis Leon & Mathews Leon, “SQL: A Complete Reference”, Tata McGraw Hill.
  3. Abraham Silberschatz, Henry Korth, S. Sudarshan, “Database Systems Concepts”, 6th Edition, McGraw Hill International Edition, 1997.

 

Academic Year: 
2021-22
  • Printer-friendly version