Write any three differences between DDL and DML. Give examples of each. [3+2] OR What is normalization ? Explain 2NF and 3NF. [2+3]

Model Answer: Differences between DDL and DML

Three Key Differences Between DDL and DML

Examples of DDL and DML

DDL Example:

-- Creating a table
CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10,2)
);

-- Modifying a table
ALTER TABLE Employee ADD Department VARCHAR(30);

-- Dropping a table
DROP TABLE Employee;

DML Example:

-- Inserting data
INSERT INTO Employee (EmpID, Name, Salary)
VALUES (101, 'Hari', 35000.00);

-- Updating data
UPDATE Employee SET Salary = 40000.00 WHERE EmpID = 101;

-- Deleting data
DELETE FROM Employee WHERE EmpID = 101;

---

Model Answer: Normalization (2NF and 3NF)

What is Normalization?

Normalization is a database design technique that organizes data to minimize redundancy and dependency, improving data integrity and efficiency. It involves dividing a database into tables and establishing relationships between them using normal forms (1NF, 2NF, 3NF, BCNF, etc.).

---

Second Normal Form (2NF)

A table is in 2NF if:

1. It is already in 1NF (atomic values, primary key defined).
2. All non-key attributes are fully functionally dependent on the entire primary key (no partial dependency).

Problem in 1NF (Partial Dependency): Consider a table OrderDetails with:

• Composite primary key: (OrderID, ProductID)
• Non-key attributes: ProductName, Quantity, Price

Here, ProductName depends only on ProductID (partial dependency), violating 2NF.

Solution (2NF): Split into two tables:

1. Orders (OrderID, ProductID, Quantity, Price)
2. Products (ProductID, ProductName)

Now, ProductName depends only on ProductID (removed partial dependency).

---

Third Normal Form (3NF)

A table is in 3NF if:

1. It is in 2NF.
2. No transitive dependency exists (non-key attributes should not depend on other non-key attributes).

Problem in 2NF (Transitive Dependency): Consider a table Student with:

• Primary key: StudentID
• Non-key attributes: Name, Department, DeanName

Here, DeanName depends on Department (not directly on StudentID), causing transitive dependency (violates 3NF).

Solution (3NF): Split into two tables:

1. Students (StudentID, Name, Department)
2. Departments (Department, DeanName)

Now, DeanName depends only on Department (removed transitive dependency).

---

Key Takeaway:

• 2NF eliminates partial dependencies (composite keys).
• 3NF eliminates transitive dependencies (non-key → non-key). Normalization ensures data integrity, efficiency, and minimal redundancy.