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

Differences between DDL and DML with Examples

Examples:

1. DDL Example:

   CREATE TABLE Student (
       RollNo INT PRIMARY KEY,
       Name VARCHAR(50),
       Email VARCHAR(50)
   );
   

   • This command creates a new table called Student.

2. DML Example:

   INSERT INTO Student (RollNo, Name, Email)
   VALUES (1, 'Ram', 'ram@example.com');
   

   • This command inserts a new record into the Student table.

---

Normalization, 2NF, and 3NF

What is Normalization?

Normalization is the process of organizing data in a database to minimize redundancy and dependency, ensuring data integrity. It involves dividing large tables into smaller, related tables and defining relationships between them.

Second Normal Form (2NF)

A table is in 2NF if:

1. It is in 1NF (no repeating groups, atomic values).
2. All non-key attributes are fully functionally dependent on the primary key (no partial dependencies).

Example: Consider a table Enrollment with (StudentID, CourseID, Grade) as attributes.

• If StudentID is the primary key, CourseID and Grade depend on both StudentID and CourseID (composite key).
• If StudentID alone were the key, CourseID would be partially dependent (violating 2NF).
• Solution: Split into two tables:
  • StudentCourse (StudentID, CourseID, Grade)
  • Student (StudentID, Name, Address)

Third Normal Form (3NF)

A table is in 3NF if:

1. It is in 2NF.
2. There are no transitive dependencies (non-key attributes should not depend on other non-key attributes).

Example: In the Student table above, if Address depends on City (which is also stored), it violates 3NF.

• Solution: Split into:
  • Student (StudentID, Name, City)
  • City (City, Address)

Summary of Normalization Rules:

1. 1NF: Atomic values, no repeating groups.
2. 2NF: No partial dependencies (requires composite keys).
3. 3NF: No transitive dependencies (non-key attributes depend only on the primary key).