Database Normalization & ER Diagrams Explained
PART 1 — Normalization
Normalization = cleaning your tables to avoid:
- Repeated data
- Partial dependencies
- Non-key columns depending on other non-key columns
1NF — First Normal Form
Rule: Each cell must contain a single atomic value.
Bad Example:
student_id | subjects
1 | Math, English
Fix:
student_id | subject
1 | Math
1 | English
2NF — Second Normal Form
Rule: No partial dependency on a composite key.
Bad Example:
(student_id, subject) → primary key
student_name depends only on student_id
Fix: Split into:
- Students
- Student_Subjects
3NF — Third Normal Form
Rule: No transitive dependency (non-key → non-key).
Bad Example:
city → pincode
Fix: Split into:
- Students
- City_Info
PART 2 — ER Modeling
Entity = noun (Student, Teacher, Order)
Attribute = property (name, age, phone)
Relationship = how entities connect
Cardinality = 1–1, 1–M, M–M
ER Diagram Examples
1. Library System
Book (book_id, title, category)
Member (member_id, name, phone)
Borrow (borrow_id, date, return_date)
Book M ——< Borrow >—— M Member
2. Hospital System
Doctor (doctor_id, name, specialty)
Patient (patient_id, name, age)
Appointment (appointment_id, date, doctor_id, patient_id)
Doctor 1 ——< Appointment >—— M Patient
3. Ecommerce Product–Category
Category (cat_id, cat_name)
Product (pid, name, price, cat_id)
Category 1 ——< Product M
PART 3 — Normalize Your Messy Table
Raw Table:
student_id | student_name | course | teacher_name | teacher_phone
101 | Riya | DBMS | Sharma | 9999000011
101 | Riya | OS | Sharma | 9999000011
Step 1: Identify entities:
- Student
- Course
- Teacher
Step 2: 3NF Tables:
Students
---------
student_id (PK)
student_name
Teachers
---------
teacher_id (PK)
teacher_name
teacher_phone
Courses
---------
course_id (PK)
course_name
teacher_id (FK)
Student_Course (Enrollment)
----------------
student_id (FK)
course_id (FK)
PART 4 — 20 MCQs
Click "Show Answer" to check the correct answer.
1. 1NF eliminates which of the following?
- A. Transitive dependency
- B. Non-atomic values
- C. Partial dependency
- D. Foreign keys
2. A table violates 2NF when:
- A. It contains multiple candidate keys
- B. A non-key column depends on part of a composite key
- C. It contains NULL values
- D. It has a foreign key
3. 3NF prevents:
- A. Repeating groups
- B. Composite keys
- C. Transitive dependencies
- D. Primary keys
4. Which is a valid composite key example?
- A. student_id
- B. order_id
- C. (student_id + course_id)
- D. phone_number
5. “city → pincode” is a violation of:
- A. 1NF
- B. 2NF
- C. 3NF
- D. BCNF
6. In ER modeling, an entity is:
- A. A verb
- B. A table column
- C. A noun representing an object
- D. A primary key
7. Relationship cardinality M:N requires:
- A. No tables
- B. One table
- C. Two tables
- D. Three tables
8. A 1:M relationship typically uses:
- A. A bridge table
- B. Composite keys
- C. A foreign key on the “many” side
- D. No keys
9. In 3NF, which is allowed?
- A. Non-key → non-key
- B. Candidate key → non-key
- C. Non-key → candidate key
- D. Part of composite key → non-key
10. A teacher teaches many courses. Best ER design?
- A. Teacher ——1:1—— Course
- B. Teacher ——1:M—— Course
- C. Teacher ——M:M—— Course
- D. Teacher ——No relation—— Course
11. Which table is required for M:M between Student and Subject?
- A. Student_Subjects
- B. Student_Data
- C. Subject_Master
- D. Enrollment_Info only if teacher exists
12. A table with attributes (A, B, C) has A → B and B → C. It violates:
- A. 1NF
- B. 2NF
- C. 3NF
- D. None
13. A multi-valued attribute in ER is fixed by:
- A. Merging tables
- B. Splitting into a separate entity
- C. Adding more columns
- D. Adding a composite key
14. A primary key must be:
- A. Unique and nullable
- B. Unique and not null
- C. Non-unique but indexed
- D. Always a composite
15. Which is an example of transitive dependency?
- A. ID → Name
- B. ID → City, City → State
- C. (ID, Course) → Marks
- D. ID → Phone
16. In a normalized design, where does a teacher’s phone belong?
- A. Course table
- B. Enrollment table
- C. Teacher table
- D. Student table
17. 2NF applies only when:
- A. A table has a single primary key column
- B. A table has a composite key
- C. The table has no primary key
- D. The table has multiple candidate keys
18. A weak entity:
- A. Has its own strong primary key
- B. Depends on another entity for identification
- C. Is same as a super key
- D. Cannot have attributes
19. ER diagram uses diamonds for:
- A. Attributes
- B. Entities
- C. Primary keys
- D. Relationships
20. After full normalization, what’s the real goal?
- A. Zero tables
- B. Faster coding
- C. Reduce redundancy + maintain integrity
- D. Remove all relationships
Comments Coming Soon
We're working on bringing you a seamless commenting experience.