DBMS Notes

DBMS Unit 2 Summary - SQL and Relational Model

Comprehensive guide to SQL commands (DDL, DML, DCL, TCL), relational model, data relationships, query processing, and ACID transactions. Master SQL fundamentals with detailed examples and interview preparation.

SQL (Structured Query Language) and the relational model form the backbone of modern database systems. This comprehensive summary covers all Unit 2 essentials for mastering SQL and relational concepts.

SQL Overview

SQL is a standardized language for managing and querying relational databases. It provides a declarative approach to data manipulation.

SQL Command Categories

DDL	CREATE, ALTER, DROP, TRUNCATE, RENAME
DML	SELECT, INSERT, UPDATE, DELETE
DCL	GRANT, REVOKE
TCL	COMMIT, ROLLBACK, SAVEPOINT

DDL Commands - Data Definition

CREATE

Creates new database objects (tables, indexes, views, databases).

sql example

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Salary DECIMAL(10, 2),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

ALTER

Modifies existing table structures or constraints.

sql example

-- Add column
ALTER TABLE Employee
ADD Phone VARCHAR(15);

-- Modify column
ALTER TABLE Employee
MODIFY Name VARCHAR(150) NOT NULL;

-- Drop column
ALTER TABLE Employee
DROP COLUMN Phone;

DROP and TRUNCATE

sql example

-- DROP: Removes entire table structure
DROP TABLE Employee;

-- TRUNCATE: Removes only data, keeps structure
TRUNCATE TABLE Employee;

DML Commands - Data Manipulation

SELECT

Retrieves data from database tables.

sql example

SELECT EmpID, Name, Salary
FROM Employee
WHERE Salary > 50000
ORDER BY Salary DESC;

INSERT

Adds new records to tables.

sql example

INSERT INTO Employee (Name, Salary, DeptID)
VALUES ('John Doe', 75000, 1);

UPDATE

Modifies existing records.

sql example

UPDATE Employee
SET Salary = 80000
WHERE EmpID = 5;

DELETE

Removes records from tables.

sql example

DELETE FROM Employee
WHERE EmpID = 5;

DCL Commands - Data Control

GRANT

Grants privileges to users.

sql example

GRANT SELECT, INSERT ON Database1.*
TO 'user1'@'localhost';

REVOKE

Revokes previously granted privileges.

sql example

REVOKE SELECT ON Database1.*
FROM 'user1'@'localhost';

TCL Commands - Transaction Control

COMMIT

Saves all pending changes.

sql example

BEGIN;
UPDATE Account SET Balance = Balance - 100 WHERE ID = 1;
UPDATE Account SET Balance = Balance + 100 WHERE ID = 2;
COMMIT;

ROLLBACK

Undoes pending changes.

sql example

BEGIN;
UPDATE Account SET Balance = 0;
ROLLBACK; -- Reverts the update

SAVEPOINT

Creates a checkpoint within transaction.

sql example

SAVEPOINT sp1;
UPDATE Table1 SET ...;
ROLLBACK TO sp1;

Relational Model

The relational model organizes data into tables (relations) with rows and columns.

Key Concepts

Relation	Table
Tuple	Row
Attribute	Column
Domain	Valid values for an attribute
Degree	Number of columns
Cardinality	Number of rows

Example Relational Schema

- Student PK	StudentID
- Course PK	CourseID
- Enrollment PK	(StudentID, CourseID)

Relationships in Relational Model

One-to-One

One record in Table A related to exactly one record in Table B.

Example

Employee (1) ──── (1) OfficeAssignment

One-to-Many

One record in Table A related to multiple records in Table B.

Example

Department (1) ──── (Many) Employee

Many-to-Many

Multiple records in Table A related to multiple records in Table B.

Example

Student (Many) ──── (Many) Course (through Enrollment)

Query Processing

Steps in Query Processing

Example

1. Parsing ──→ 2. Validation ──→ 3. Optimization ──→ 4. Code Generation ──→ 5. Compilation ──→ 6. Execution Checks syntax Checks semantics Finds best plan Generates code Converts to Executes & schema validity (cost-based) for execution machine code query

Transactions

ACID Properties

┌─────────────────────────────────────┐ │ ACID Properties of Transactions │ ├─────────────────────────────────────┤ │ Atomicity - All or nothing │ │ Consistency - Valid to valid state │ │ Isolation - Independent execution │ │ Durability - Permanent after commit │ └─────────────────────────────────────┘

Transaction Example

sql example

BEGIN TRANSACTION;
  -- Debit from account A
  UPDATE Accounts SET Balance = Balance - 1000
  WHERE AccountID = 'A123';
  
  -- Credit to account B
  UPDATE Accounts SET Balance = Balance + 1000
  WHERE AccountID = 'B456';
  
  IF @@ERROR = 0
    COMMIT; -- Save if no errors
  ELSE
    ROLLBACK; -- Undo if error occurred
END TRANSACTION;

Quick Revision Notes

DDL - CREATE, ALTER, DROP, TRUNCATE (Schema operations)
DML - SELECT, INSERT, UPDATE, DELETE (Data operations)
DCL - GRANT, REVOKE (Permission management)
TCL - COMMIT, ROLLBACK, SAVEPOINT (Transaction control)
Relational Model - Tables with rows and columns
Relationships - One-to-one, One-to-many, Many-to-many
ACID - Atomicity, Consistency, Isolation, Durability

Interview Q&A

Q1: What are the main SQL command categories?

A: The four categories are DDL (CREATE, ALTER, DROP), DML (SELECT, INSERT, UPDATE, DELETE), DCL (GRANT, REVOKE), and TCL (COMMIT, ROLLBACK).

Q2: What's the difference between TRUNCATE and DELETE?

A: DELETE removes rows one by one and can be rolled back; TRUNCATE removes all rows at once, faster, frees space, and cannot be rolled back in some databases. DELETE allows WHERE clause; TRUNCATE doesn't.

Q3: Explain ACID properties with an example.

A: ACID ensures transactions are reliable. For a bank transfer: Atomicity (both debit/credit complete), Consistency (total balance unchanged), Isolation (concurrent transfers don't interfere), Durability (completed transaction persists).

Q4: What are the three types of relationships in relational model?

A: One-to-one (1:1), One-to-many (1:N), and Many-to-many (M:N). Foreign keys establish these relationships in relational tables.

Q5: What is query optimization?

A: Query optimization finds the most efficient execution plan for a query by analyzing different strategies, estimating costs, and selecting the one with minimum resource usage.

Q6: What's the difference between DDL and DML?

A: DDL (Data Definition Language) defines database structure (CREATE, ALTER, DROP); DML (Data Manipulation Language) works with data (SELECT, INSERT, UPDATE, DELETE).

Created: 2024 | Author: Database Design Expert Difficulty: Intermediate | Estimated Read Time: 25 minutes

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for DBMS Unit 2 Summary - SQL and Relational Model.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Database Management Systems (DBMS) topic.

Search Terms

dbms, database management systems (dbms), unit, summary, dbms unit 2 summary - sql and relational model

Related Database Management Systems (DBMS) Topics

Continue learning this concept

Unit 2Unit 2 — Relational Model & SQLThis unit forms the backbone of practical database work. While Unit 1 introduced conceptual modeling with ER diagrams, this unit translates those conceptual designs into Unit 2Constraints in SQLConstraints are rules applied to table columns that restrict the type of data that can be inserted or updated. They enforce data integrity at the database...Unit 2CursorsA cursor is a database object that allows row-by-row processing of query results inside a stored procedure or PL/SQL block. While SQL naturally operates on...Unit 2DCL and TCL CommandsDCL commands control access and permissions to database objects. They manage security by defining who can do what.Unit 2DDL CommandsData Definition Language (DDL) commands are used to define, modify, and remove database objects such as tables, indexes, views, and schemas. DDL commands...Unit 2DML CommandsData Manipulation Language (DML) commands are used to manipulate the data stored in database tables. DML operates on the instance (rows/data), not the schema.

PreviousSQL Introduction NextTriggers in SQL — Database Management Systems

DBMS Notes

DBMS Unit 2 Summary - SQL and Relational Model

SQL Overview

SQL is a standardized language for managing and querying relational databases. It provides a declarative approach to data manipulation.

SQL Command Categories

DDL	CREATE, ALTER, DROP, TRUNCATE, RENAME
DML	SELECT, INSERT, UPDATE, DELETE
DCL	GRANT, REVOKE
TCL	COMMIT, ROLLBACK, SAVEPOINT

DDL Commands - Data Definition

CREATE

Creates new database objects (tables, indexes, views, databases).

sql example

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Salary DECIMAL(10, 2),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

ALTER

Modifies existing table structures or constraints.

sql example

-- Add column
ALTER TABLE Employee
ADD Phone VARCHAR(15);

-- Modify column
ALTER TABLE Employee
MODIFY Name VARCHAR(150) NOT NULL;

-- Drop column
ALTER TABLE Employee
DROP COLUMN Phone;

DROP and TRUNCATE

sql example

-- DROP: Removes entire table structure
DROP TABLE Employee;

-- TRUNCATE: Removes only data, keeps structure
TRUNCATE TABLE Employee;

DML Commands - Data Manipulation

SELECT

Retrieves data from database tables.

sql example

SELECT EmpID, Name, Salary
FROM Employee
WHERE Salary > 50000
ORDER BY Salary DESC;

INSERT

Adds new records to tables.

sql example

INSERT INTO Employee (Name, Salary, DeptID)
VALUES ('John Doe', 75000, 1);

UPDATE

Modifies existing records.

sql example

UPDATE Employee
SET Salary = 80000
WHERE EmpID = 5;

DELETE

Removes records from tables.

sql example

DELETE FROM Employee
WHERE EmpID = 5;

DCL Commands - Data Control

GRANT

Grants privileges to users.

sql example

GRANT SELECT, INSERT ON Database1.*
TO 'user1'@'localhost';

REVOKE

Revokes previously granted privileges.

sql example

REVOKE SELECT ON Database1.*
FROM 'user1'@'localhost';

TCL Commands - Transaction Control

COMMIT

Saves all pending changes.

sql example

BEGIN;
UPDATE Account SET Balance = Balance - 100 WHERE ID = 1;
UPDATE Account SET Balance = Balance + 100 WHERE ID = 2;
COMMIT;

ROLLBACK

Undoes pending changes.

sql example

BEGIN;
UPDATE Account SET Balance = 0;
ROLLBACK; -- Reverts the update

SAVEPOINT

Creates a checkpoint within transaction.

sql example

SAVEPOINT sp1;
UPDATE Table1 SET ...;
ROLLBACK TO sp1;

Relational Model

The relational model organizes data into tables (relations) with rows and columns.

Key Concepts

Relation	Table
Tuple	Row
Attribute	Column
Domain	Valid values for an attribute
Degree	Number of columns
Cardinality	Number of rows

Example Relational Schema

- Student PK	StudentID
- Course PK	CourseID
- Enrollment PK	(StudentID, CourseID)

Relationships in Relational Model

One-to-One

One record in Table A related to exactly one record in Table B.

Example

Employee (1) ──── (1) OfficeAssignment

One-to-Many

One record in Table A related to multiple records in Table B.

Example

Department (1) ──── (Many) Employee

Many-to-Many

Multiple records in Table A related to multiple records in Table B.

Example

Student (Many) ──── (Many) Course (through Enrollment)

Query Processing

Steps in Query Processing

Example

Transactions

ACID Properties

Transaction Example

sql example

BEGIN TRANSACTION;
  -- Debit from account A
  UPDATE Accounts SET Balance = Balance - 1000
  WHERE AccountID = 'A123';
  
  -- Credit to account B
  UPDATE Accounts SET Balance = Balance + 1000
  WHERE AccountID = 'B456';
  
  IF @@ERROR = 0
    COMMIT; -- Save if no errors
  ELSE
    ROLLBACK; -- Undo if error occurred
END TRANSACTION;

Quick Revision Notes

DDL - CREATE, ALTER, DROP, TRUNCATE (Schema operations)
DML - SELECT, INSERT, UPDATE, DELETE (Data operations)
DCL - GRANT, REVOKE (Permission management)
TCL - COMMIT, ROLLBACK, SAVEPOINT (Transaction control)
Relational Model - Tables with rows and columns
Relationships - One-to-one, One-to-many, Many-to-many
ACID - Atomicity, Consistency, Isolation, Durability

Interview Q&A

Q1: What are the main SQL command categories?

A: The four categories are DDL (CREATE, ALTER, DROP), DML (SELECT, INSERT, UPDATE, DELETE), DCL (GRANT, REVOKE), and TCL (COMMIT, ROLLBACK).

Q2: What's the difference between TRUNCATE and DELETE?

Q3: Explain ACID properties with an example.

Q4: What are the three types of relationships in relational model?

A: One-to-one (1:1), One-to-many (1:N), and Many-to-many (M:N). Foreign keys establish these relationships in relational tables.

Q5: What is query optimization?

A: Query optimization finds the most efficient execution plan for a query by analyzing different strategies, estimating costs, and selecting the one with minimum resource usage.

Q6: What's the difference between DDL and DML?

A: DDL (Data Definition Language) defines database structure (CREATE, ALTER, DROP); DML (Data Manipulation Language) works with data (SELECT, INSERT, UPDATE, DELETE).

Created: 2024 | Author: Database Design Expert Difficulty: Intermediate | Estimated Read Time: 25 minutes

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for DBMS Unit 2 Summary - SQL and Relational Model.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Database Management Systems (DBMS) topic.

Search Terms

dbms, database management systems (dbms), unit, summary, dbms unit 2 summary - sql and relational model

Related Database Management Systems (DBMS) Topics

Continue learning this concept

PreviousSQL Introduction NextTriggers in SQL — Database Management Systems