Posts

UNIT – IV: Structured Query Language (SQL)

  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...
Post a Comment
Read more

UNIT – III: Normalization

  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 ...
Post a Comment
Read more

UNIT – V: Transaction Management

  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...
Post a Comment
Read more

Unit – II :ER Model and Relational Algebra

 1. Basic Concepts of E–R (Entity-Relationship) Model The Entity–Relationship (ER) Model is a high-level data model proposed by Peter Chen in 1976 . It provides a conceptual framework to describe the structure of a database using real-world objects (entities), their characteristics (attributes), and how they interact (relationships). ER Models are mainly represented by ER Diagrams which are graphical notations. 1. Entities An Entity is an object in the real world that is distinguishable from other objects . Entities can be physical (Student, Book, Car) or conceptual (Course, Department, Salary). Types of Entities: Strong Entity Can exist independently. Has a primary key that uniquely identifies it. Example: Student (identified by Roll_No). 🔹 ER Diagram Notation: Rectangle (e.g., STUDENT ) Weak Entity Cannot exist without being related to a strong entity. Identified using a partial key (discriminator) and linked with a strong entity through an ide...
Post a Comment
Read more

Unit – I: Introduction to Database Management System

Image
1. Introduction to DBMS and Its Advantages Definition: A Database Management System (DBMS) is software that helps in storing, managing, and retrieving data from databases efficiently. Example: A library system where books, members, issue/return records are stored in a database and managed using software like MySQL or Oracle DB. Advantages of DBMS: Advantage Description Data Redundancy Control Avoids duplication of data. Data Consistency Ensures all users see the same data. Data Integrity Maintains accuracy and consistency. Data Security Provides user-level access control. Concurrent Access Multiple users can access data simultaneously without conflict. Backup and Recovery Automatically handles data backup and recovery after failure. Better Data Sharing Centralized a...
Post a Comment
Read more