Introduction to DBMS (002204305) VIEAT By Jaydeep

  1. Concept of SQL SQL is a standard language for managing relational databases. Categories: DDL, DML, DCL, TCL . 2. Data Types in SQL Integer, Float, Double → Numeric values Char(n), Varchar(n) → Strings Date, Time, Timestamp → Date & time values Number(p,s) → Numeric with precision 3. DDL Commands CREATE : Creates table. CREATE TABLE Student(RollNo INT , Name VARCHAR ( 50 )); ALTER : Modifies table structure. DROP : Deletes table. RENAME : Changes table name. TRUNCATE : Deletes all rows, keeps structure. COMMENT : Adds comments. 4. DML Commands SELECT : Retrieve data. INSERT : Add rows. UPDATE : Modify rows. DELETE : Remove rows. MERGE : Insert/update simultaneously. CALL : Call stored procedure. EXPLAIN PLAN : Shows execution plan. LOCK TABLE : Prevents changes by others. 5. DCL Commands GRANT : Give access. REVOKE : Remove access. 6. TCL Commands COMMIT : Save changes. ROL...

  1. Database Anomalies Insertion Anomaly : Unable to add data due to missing information. Deletion Anomaly : Unnecessary loss of data when deleting a record. Update Anomaly : Same data stored multiple times; updating one copy may cause inconsistency. Example: A student-course table where deleting the last course of a student also deletes student info. 2. Functional Dependency A functional dependency (FD) exists when one attribute uniquely determines another. Denoted as X → Y , meaning attribute X determines Y. Example: RollNo → StudentName (RollNo uniquely identifies a student). 3. Rules for Functional Dependency Reflexivity : If X is a set of attributes, then X → Y where Y ⊆ X. Augmentation : If X → Y, then XZ → YZ. Transitivity : If X → Y and Y → Z, then X → Z. 4. Types of Functional Dependency Trivial FD : Y ⊆ X (e.g., RollNo,Name → Name). Non-trivial FD : Y is not a subset of X. Fully Functional : Removing any attribute from X ...

  Transaction Management 1. Transaction Concepts A transaction is a logical unit of work. ACID Properties: Atomicity: All or none. Consistency: Must preserve database rules. Isolation: Transactions run independently. Durability: Results persist even after crash. 2. Serializability of Transactions A schedule is serializable if its result is same as executing transactions serially. Types: Conflict Serializable View Serializable 3. Concurrent Executions of Transactions and Problems Dirty Read: Reading uncommitted data. Lost Update: One update overwrites another. Inconsistent Retrievals: Incomplete data is read. 4. Locking Mechanism Ensures serializability by restricting access. Shared Lock: Multiple reads allowed. Exclusive Lock: Only one write allowed. Two-Phase Locking (2PL): Growing phase (locks acquired), Shrinking phase (locks released). Q1. Define transaction in DBMS. Answer: A transaction is a lo...