Posts

Practical - 4 Implement SQL Queries for ALTER, UPDATE, DELETE, DROP, and SELECT Commands

  Implement SQL Queries for ALTER, UPDATE, DELETE, DROP, and SELECT Commands Aim To study and implement SQL commands ALTER , UPDATE , DELETE , DROP , and SELECT for modifying database structures, manipulating records, deleting data, removing database objects, and retrieving information from relational database tables. Objectives After completing this experiment, students will be able to: Understand the purpose of SQL DDL, DML, and DQL commands. Modify the structure of an existing table using the ALTER command. Update existing records using the UPDATE command. Delete specific or multiple records using the DELETE command. Permanently remove database objects using the DROP command. Retrieve and display records using different forms of the SELECT command. Apply filtering, sorting, and projection techniques while retrieving data. Understand the importance of the WHERE clause in SQL commands. Theory Introduction to SQL Commands Structured Query Language (SQL) is the standard langu...
Post a Comment
Read more

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. ...
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