Posts

Checkpoint

Image
  What is a Checkpoint in DBMS? A checkpoint in a Database Management System (DBMS) is a mechanism used to minimize the amount of work needed for recovery after a system crash. It essentially acts like a “save point” — the system writes all modified data (dirty pages) from the buffer (RAM) to the disk and records a checkpoint in the log file . Purpose of Checkpoint Reduce recovery time — only redo or undo transactions after the last checkpoint. Ensure consistency between database and log files. Flush dirty pages (modified data not yet written to disk). Mark a safe state from which the system can recover. How It Works  When a checkpoint occurs: All committed transactions before the checkpoint are written to disk . The checkpoint record is written to the log file . Transactions after the checkpoint may still be in progress (these are handled by recovery if crash occurs). During recovery: Start from the last checkpoint , not from the b...
Post a Comment
Read more

Trigger and Stored Procedure

Trigger and Stored Procedure  Tables Setup CREATE TABLE Department (     D_id INT PRIMARY KEY,     D_name VARCHAR(50) ); CREATE TABLE Student (     S_id INT PRIMARY KEY,     S_name VARCHAR(50),     D_id INT,     FOREIGN KEY (D_id) REFERENCES Department(D_id) ); CREATE TABLE Subject (     Sub_id INT PRIMARY KEY,     Sub_name VARCHAR(50) ); CREATE TABLE Marks (     S_id INT,     Sub_id INT,     Marks INT,     FOREIGN KEY (S_id) REFERENCES Student(S_id),     FOREIGN KEY (Sub_id) REFERENCES Subject(Sub_id) ); Stored Procedure Example 👉 Example Task: Find average marks of a student. DELIMITER $$ CREATE PROCEDURE Get_AvgMarks (IN student_id INT) BEGIN     SELECT S.S_name, AVG(M.Marks) AS Average_Marks     FROM Marks M     JOIN Student S ON M.S_id = S.S_id     WHERE M.S_id = student_id     GROUP BY S.S_name; EN...
Post a Comment
Read more

Transaction Control

Consider the following schema Part A CREATE TABLE Accounts ( AccNo INT PRIMARY KEY, Name VARCHAR ( 50 ), Balance DECIMAL ( 10 , 2 ) ); INSERT INTO Accounts VALUES ( 101 , 'Raj' , 10000 ), ( 102 , 'Meena' , 15000 ( 103 , 'Karan' , 20000 ); Q1. Start a transaction where: Raj transfers 2000 to Meena. After the transfer, commit the transaction. Show the balances of all accounts. START TRANSACTION; UPDATE Accounts SET Balance = Balance - 2000 WHERE AccNo = 101; -- Raj UPDATE Accounts SET Balance = Balance + 2000 WHERE AccNo = 102; -- Meena COMMIT; SELECT * FROM Accounts; Q2. Start a transaction where: Raj transfers 5000 to Karan. Before committing, you realize Raj doesn’t have sufficient balance and rollback the transaction. Show the balances of all accounts after rollback. START TRANSACTION; UPDATE Accounts SET Balance = Balance - 5000 WHERE AccNo = 101; -- Raj UPDATE Accounts SET Balance = Balance + 5000 WHERE AccNo = 103; -- Karan ROLLBACK;...
Post a Comment
Read more

Data Control Language

 Data Control Language (DCL) 🔹 Step 1: Login as Admin (root) Open MySQL command line and login as root (or another admin account): mysql -u root -p Enter your root password. 🔹 Step 2: Create Database and Table CREATE DATABASE UniversityDB; USE UniversityDB; CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR ( 50 ), Marks INT ); INSERT INTO Students VALUES ( 1 , 'Amit' , 85 ), ( 2 , 'Raj' , 70 ), ( 3 , 'Neha' , 92 ), ( 4 , 'Sneha' , 60 ); 🔹   Step 3: Create Users CREATE USER 'user1' @ 'localhost' IDENTIFIED BY 'pass1' ; CREATE USER 'user2' @ 'localhost' IDENTIFIED BY 'pass2' ; CREATE USER 'user3' @ 'localhost' IDENTIFIED BY 'pass3' ; 'localhost' means these users can log in only from the same computer where MySQL is running. If you want t...
Post a Comment
Read more

SUB Queries

 SUB Queries in SQL Table 1:  Students student_id name age department 1 Asha 19 Computer 2 Ravi 20 Mechanical 3 Priya 21 Computer 4 Suresh 20 Civil 5 Meena 19 Computer Table 2: Marks mark_id student_id subject marks 1 1 DBMS 85 2 1 Networks 78 3 2 DBMS 69 4 2 Networks 75 5 3 DBMS 92 6 3 Networks 88 7 4 DBMS 55 8 4 Networks 60 9 5 DBMS 70 10 5 Networks 72 CREATE TABLE Students (     student_id INT PRIMARY KEY,     name VARCHAR(50) NOT NULL,     age INT,     department VARCHAR(50) ); CREATE TABLE Marks (     mark_id INT PRIMARY KEY,     student_id INT,     subject VARCHAR(50),     marks INT,     FOREIGN KEY (student_id) REFERENCES Students(student_id) ); INSERT INTO Students (student_id, name, age, department) VALUES (1, 'Asha', 19, 'Computer'), (2, 'Ravi', 20, 'Mechanical'), (3, 'P...
Post a Comment
Read more