DBMS Assignment
Q1) What is DDL and DML Commands? Write Syntax and Example
DDL (Data Definition Language)
Data Definition Language (DDL) is a set of SQL commands used to define, create, modify,
and delete database structures such as tables, schemas, and indexes.
DDL commands mainly deal with the structure of the database rather than the data stored
inside it.
Common DDL Commands
1. CREATE – Creates a new table or database object
2. ALTER – Modifies the structure of an existing table
3. DROP – Deletes a table or database
4. TRUNCATE – Removes all records from a table but keeps the table structure
Syntax and Examples
1. CREATE Command
Syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype
);
Example
CREATE TABLE Student (
Student_ID INT,
Name VARCHAR(50),
Course VARCHAR(50)
);
This command creates a table named Student with three columns.
2. ALTER Command
Syntax
ALTER TABLE table_name
ADD column_name datatype;
Example
ALTER TABLE Student
ADD Age INT;
This command adds a new column Age to the Student table.
3. DROP Command
Syntax
DROP TABLE table_name;
Example
DROP TABLE Student;
This command deletes the Student table completely from the database.
4. TRUNCATE Command
Syntax
TRUNCATE TABLE table_name;
Example
TRUNCATE TABLE Student;
This command removes all records from the Student table but keeps the table structure.
DML (Data Manipulation Language)
Data Manipulation Language (DML) commands are used to manipulate or manage the data
stored in tables.
These commands allow users to insert, update, delete, and retrieve data.
Common DML Commands
1. INSERT
2. UPDATE
3. DELETE
4. SELECT
1. INSERT Command
Syntax
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);
Example
INSERT INTO Student (Student_ID, Name, Course)
VALUES (101, 'Rahul', 'Computer Science');
This command inserts a new record into the Student table.
2. UPDATE Command
Syntax
UPDATE table_name
SET column_name = value
WHERE condition;
Example
UPDATE Student
SET Course = 'Information Technology'
WHERE Student_ID = 101;
This command updates the course for student with ID 101.
3. DELETE Command
Syntax
DELETE FROM table_name
WHERE condition;
Example
DELETE FROM Student
WHERE Student_ID = 101;
This command deletes a specific record from the Student table.
Conclusion
DDL commands are used to define the structure of the database, whereas DML commands
are used to manipulate the data inside the database tables.
Q2) Define Triggers. Write Syntax and Example
Definition
A Trigger is a special type of stored procedure that automatically executes when a specific
event occurs in a database table.
Triggers are commonly used for:
• Enforcing business rules
• Maintaining data integrity
• Logging changes in the database
Triggers are executed automatically when operations like INSERT, UPDATE, or DELETE occur.
Types of Triggers
1. BEFORE Trigger – Executes before the event occurs
2. AFTER Trigger – Executes after the event occurs
Syntax of Trigger
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON table_name
FOR EACH ROW
BEGIN
SQL statements
END;
Example of Trigger
Suppose we have a table Employee and we want to log every new employee inserted into
another table.
Employee Table
Employee
--------
Emp_ID
Name
Salary
Log Table
Employee_Log
------------
Emp_ID
Action
Trigger Example
CREATE TRIGGER employee_insert
AFTER INSERT
ON Employee
FOR EACH ROW
BEGIN
INSERT INTO Employee_Log
VALUES (NEW.Emp_ID, 'New Employee Added');
END;
Explanation
• When a new employee record is inserted into the Employee table,
• The trigger automatically inserts a log entry into the Employee_Log table.
Advantages of Triggers
• Maintain data consistency
• Automatically enforce rules
• Provide auditing and tracking of data changes
Q3) Explain Aggregate Functions with Syntax and Example
Definition
Aggregate functions are SQL functions that perform calculations on multiple rows of data
and return a single result.
They are mainly used with SELECT statements.
Common Aggregate Functions
1. COUNT()
2. SUM()
3. AVG()
4. MAX()
5. MIN()
1. COUNT()
Counts the number of rows in a table.
Syntax
SELECT COUNT(column_name)
FROM table_name;
Example
SELECT COUNT(Student_ID)
FROM Student;
This returns the total number of students.
2. SUM()
Calculates the total sum of a numeric column.
Syntax
SELECT SUM(column_name)
FROM table_name;
Example
SELECT SUM(Salary)
FROM Employee;
This returns the total salary of all employees.
3. AVG()
Calculates the average value of a column.
Syntax
SELECT AVG(column_name)
FROM table_name;
Example
SELECT AVG(Salary)
FROM Employee;
This returns the average salary.
4. MAX()
Returns the highest value in a column.
Syntax
SELECT MAX(column_name)
FROM table_name;
Example
SELECT MAX(Salary)
FROM Employee;
This returns the highest salary.
5. MIN()
Returns the smallest value in a column.
Syntax
SELECT MIN(column_name)
FROM table_name;
Example
SELECT MIN(Salary)
FROM Employee;
This returns the lowest salary.
Conclusion
Aggregate functions help summarize large amounts of data and provide useful statistical
information from the database.
Q4) Explain Deadlock Handling
Definition
A deadlock is a situation in a database where two or more transactions are waiting for each
other to release resources, causing none of them to proceed.
In simple words, each transaction is waiting for a resource that another transaction holds.
Example of Deadlock
Consider two transactions:
Transaction T1 locks Table A and wants Table B.
Transaction T2 locks Table B and wants Table A.
Now both transactions wait for each other indefinitely. This situation is called deadlock.
Deadlock Handling Methods
1. Deadlock Prevention
This method prevents deadlocks by ensuring that at least one of the deadlock conditions does
not occur.
Techniques include:
• Resource ordering
• Requesting all resources at once
• Avoiding circular waiting
2. Deadlock Detection
The system allows deadlocks to occur but detects them using algorithms.
Once detected, one of the transactions is terminated to resolve the deadlock.
3. Deadlock Recovery
In this method, once a deadlock is detected, the system rolls back one of the transactions so
the other transaction can continue.
Importance of Deadlock Handling
Deadlock handling is important because it:
• Prevents system freeze
• Maintains database performance
• Ensures smooth transaction processing
Q5) What is Normalization? Explain 1NF, 2NF, 3NF with Example
Definition
Normalization is the process of organizing data in a database to reduce redundancy and
improve data integrity.
It involves dividing large tables into smaller tables and defining relationships between them.
Normalization improves:
• Data consistency
• Storage efficiency
• Data integrity
First Normal Form (1NF)
A table is in 1NF if:
• Each column contains atomic values
• No repeating groups
• Each record is unique
Example (Not in 1NF)
Student_ID Name Subjects
1 Rahul Math, Science
This column contains multiple values.
Convert to 1NF
Student_ID Name Subject
1 Rahul Math
1 Rahul Science
Now each field contains only one value.
Second Normal Form (2NF)
A table is in 2NF if:
• It is already in 1NF
• All non-key attributes depend on the entire primary key
Example
| Student_ID | Course_ID | Student_Name | Course_Name |
Here Student_Name depends only on Student_ID, not on the full key.
Convert to 2NF
Student Table
| Student_ID | Student_Name |
Course Table
| Course_ID | Course_Name |
Enrollment Table
| Student_ID | Course_ID |
Third Normal Form (3NF)
A table is in 3NF if:
• It is already in 2NF
• No transitive dependency
Example
| Student_ID | Student_Name | Department | HOD |
Here HOD depends on Department, not Student_ID.
Convert to 3NF
Student Table
| Student_ID | Student_Name | Department |
Department Table
| Department | HOD |
Conclusion
Normalization helps organize data efficiently by reducing redundancy and improving data
integrity. The first three normal forms (1NF, 2NF, 3NF) are widely used to design efficient
database structures.