DBMS II

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.