Department Events
DBMS-II
The course will enable the students to
1. Learn various advance concepts of transaction processing and recovery.
1. Applying MySQL to design and manipulate databases.
Course Outcomes (COs):
COURSE OUTCOME (AT COURSE LEVEL) | LEARNING AND TEACHING STRATEGIES | ASSESSMENT STRATEGIES |
|---|---|---|
The students will: | Approach in teaching: | Class test, Semester |
CO198. Analyse and compare transaction processing and concurrency control techniques | Interactive Lectures, Discussion, Tutorials, Reading assignments, Demonstration, Learning activities for the students: | end examinations, Quiz, Solving problems in tutorials, Assignments, |
CO199. Evaluate concurrency control techniques and its role in maintaining the database’s integrity. | Self-learning assignments, Effective questions, Simulation, Seminar presentation, Giving tasks. | Presentation, Individual and group projects |
CO200. Implement different types of DDL, DML and DCL statements in MySQL. | ||
CO201. Choose the type of joins to create and manipulate queries and subqueries for different problems. | ||
CO202. Create database connectivity through MySQL to Java. |
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.
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.
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).
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.
Database connectivity to MySQL: Introduction, Classes used for Database connectivity, Prerequisites for connecting to MySQL from Java, Connecting to MySQL from Java
1. R. Elmasri and S. B. Navathe, “Fundamentals of Database Systems”, Addison Wesley, 7th Edition, 2015
2. Sumita Arora, “Informatics Practices”, Dhanpat Rai & Co., 2020
Suggested READINGS:
1. Abraham Silberschatz, Henry Korth, S. Sudarshan, “Database Systems Concepts”, 7th Edition, McGraw Hill International Edition, 2019
2. James R. Groff & Paul N. Weinberg, “The Complete Reference SQL”, McGraw Hill Education, 3rd Edition, 2017
3. Alexis Leon & Mathews Leon, “SQL: A Complete Reference”, Tata McGraw Hill, 6th Edition, 2008
JOURNALS:
1. https://www.elsevier.com/books/transaction-processing/gray/978-0-08-051955-5
2. https://www.elsevier.com/books/principles-of-transaction-processing/bernstein/978-1-55860-623-4
3. https://www.elsevier.com/books/sql-clearly-explained/harrington/978-1-55860-876-4
4. https://www.elsevier.com/books/concurrency-control-in-distributed-database-systems/cellary/978-0-444-70409-2
5. https://www.elsevier.com/books/principles-of-transaction-processing/bernstein/978-1-55860-623-4
6. https://www.elsevier.com/books/relational-database-design-and-implementation/harrington/978-0-12-804399-8
7. https://www.elsevier.com/books/database/oneil/978-1-4831-8404-3
8. https://www.elsevier.com/books/database-management-systems/gorman/978-0-7506-0135-1
E-RESOURCES:
1. https://www.w3schools.com/mysql/mysql_sql.asp
2. https://www.slideshare.net/search/slideshow?searchfrom=header&q=mysql
3. https://www.slideshare.net/search/slideshow?searchfrom=header&q=transaction+processing&ud=any&ft=all&lang=**&sort=
4. https://www.slideshare.net/search/slideshow?searchfrom=header&q=database+recovery&ud=any&ft=all&lang=**&sort=
5. https://www.slideshare.net/search/slideshow?searchfrom=header&q=rdbms&ud=any&ft=all&lang=**&sort=
6. https://www.slideshare.net/search/slideshow?searchfrom=header&q=relational+database+management+systems&ud=any&ft=all&lang=**&sort=
