Normalization — 1NF, 2NF, 3NF

8. Practice Problems

Sixteen problems below. Most have a runnable SQLite answer — try them yourself first, then check.

1. Identify the 1NF violation in a column called tags that stores 'sale,featured,new'.
   যদি একটি কলামে 'sale,featured,new'-এর মতো মান থাকে, সেটি 1NF লঙ্ঘন কেন?
   ✨ Show Answer (উত্তর দেখুন)

   Answer: The cell holds multiple values. To filter by tag you must use LIKE or split — proof of non-atomicity. Fix by creating a product_tags(product_id, tag) child table.

   একই cell-এ একাধিক মান আছে। filter করতে হলে split বা LIKE লাগে — অর্থাৎ atomic নয়। সমাধান: একটি child table product_tags(product_id, tag)।

2. Convert the multi-valued tags field into a 1NF table and run a query that finds all products tagged "sale".
   multi-valued tags-কে 1NF টেবিলে রূপান্তর করুন এবং "sale" tag-যুক্ত পণ্য বের করুন।
   ✨ Show Answer (উত্তর দেখুন)

   ans2.sql

   CREATE TABLE product_tags (
       product_id INTEGER,
       tag        TEXT,
       PRIMARY KEY (product_id, tag)
   );
   INSERT INTO product_tags VALUES
    (1, 'sale'), (1, 'featured'),
    (2, 'new'),
    (3, 'sale');
   
   SELECT p.name
   FROM products p
   JOIN product_tags t ON t.product_id = p.product_id
   WHERE t.tag = 'sale';

   Run Copy Open in SQLite Online

3. Given enrolment(student_id, course_id, student_name, course_title) with PK (student_id, course_id), list every partial dependency.
   উপরের টেবিলে কোন কোন partial dependency আছে?
   ✨ Show Answer (উত্তর দেখুন)

   Answer: student_id → student_name (depends on only part of the key) and course_id → course_title. Both are partial dependencies — the table is not in 2NF.

   student_id → student_name এবং course_id → course_title — দুটিই partial dependency, তাই টেবিলটি 2NF-এ নেই।

4. Decompose the enrolment table above into 2NF and verify with a SQL join.
   উপরের টেবিলকে 2NF-এ ভাঙুন এবং join দিয়ে যাচাই করুন।
   ✨ Show Answer (উত্তর দেখুন)

   ans4.sql

   CREATE TABLE students  (student_id INTEGER PRIMARY KEY, student_name TEXT);
   CREATE TABLE courses   (course_id  INTEGER PRIMARY KEY, course_title TEXT);
   CREATE TABLE enrolment (
       student_id INTEGER,
       course_id  INTEGER,
       PRIMARY KEY (student_id, course_id)
   );
   INSERT INTO students VALUES (1, 'Arif'), (2, 'Sadia');
   INSERT INTO courses  VALUES (101, 'DBMS'), (102, 'OS');
   INSERT INTO enrolment VALUES (1, 101), (1, 102), (2, 101);
   
   SELECT s.student_name, c.course_title
   FROM enrolment e
   JOIN students s ON s.student_id = e.student_id
   JOIN courses  c ON c.course_id  = e.course_id;

   Run Copy Open in SQLite Online

5. Find the transitive dependency in employees(emp_id, dept_id, dept_name, dept_location) with PK emp_id.
   উপরের টেবিলে transitive dependency কোনটি?
   ✨ Show Answer (উত্তর দেখুন)

   Answer: emp_id → dept_id → dept_name and emp_id → dept_id → dept_location. Both dept_name and dept_location depend on the key transitively through dept_id. Move them to a departments table.

   dept_name এবং dept_location — দুটিই dept_id-র মাধ্যমে indirect-ভাবে নির্ভরশীল। এদের একটি আলাদা departments টেবিলে সরাতে হবে।

6. Convert the employees table to 3NF and write a query that lists every employee with their department location.
   employees টেবিলকে 3NF-এ আনুন এবং প্রতিটি কর্মীর department location দেখান।
   ✨ Show Answer (উত্তর দেখুন)

   ans6.sql

   CREATE TABLE departments (
       dept_id       INTEGER PRIMARY KEY,
       dept_name     TEXT,
       dept_location TEXT
   );
   CREATE TABLE employees (
       emp_id  INTEGER PRIMARY KEY,
       name    TEXT,
       dept_id INTEGER REFERENCES departments(dept_id)
   );
   INSERT INTO departments VALUES (10, 'Engineering', 'Dhaka'), (20, 'Sales', 'Sylhet');
   INSERT INTO employees   VALUES (1, 'Arif', 10), (2, 'Sadia', 20);
   
   SELECT e.name, d.dept_name, d.dept_location
   FROM employees e
   JOIN departments d ON d.dept_id = e.dept_id;

   Run Copy Open in SQLite Online

7. A library has book(isbn, title, author_name, author_country) with PK isbn. Is it in 3NF? Justify.
   উপরের library টেবিল কি 3NF-এ আছে? ব্যাখ্যা করুন।
   ✨ Show Answer (উত্তর দেখুন)

   Answer: No. author_country depends on author_name, not on isbn. Transitive dependency. Split into authors(author_id, name, country) and reference it from book.

   না। author_country নির্ভর করে author_name-এর ওপর, isbn-এর ওপর নয়। তাই এটি 3NF-এ নেই — author-কে আলাদা টেবিলে আনতে হবে।

8. Show an insertion anomaly that exists in 1NF but disappears in 2NF (use the enrolment example).
   উপরের enrolment উদাহরণে এমন একটি insertion anomaly দেখান যা 1NF-এ আছে কিন্তু 2NF-এ নেই।
   ✨ Show Answer (উত্তর দেখুন)

   Answer: In 1NF you cannot insert a new course (e.g. "Networks") until at least one student enrols, because (student_id, course_id) is the primary key — a row needs both. In 2NF you simply INSERT INTO courses with no enrolments, no anomaly.

   1NF-এ কোনো ছাত্র enrol না করা পর্যন্ত নতুন course যোগ করা যায় না, কারণ key-এ student_id লাগবেই। 2NF-এ courses টেবিলে সরাসরি যোগ করা যায়।

9. Write SQL that demonstrates an update anomaly on the un-normalized orders_v1 table: change the manager and observe the duplication.
   un-normalized টেবিলে manager update করার সময় anomaly প্রদর্শন করুন।
   ✨ Show Answer (উত্তর দেখুন)

   ans9.sql

   CREATE TABLE orders_v1 (
       order_id INTEGER, product TEXT,
       category TEXT, category_manager TEXT
   );
   INSERT INTO orders_v1 VALUES
    (1, 'Phone',   'Electronics', 'Mr. Karim'),
    (2, 'Charger', 'Electronics', 'Mr. Karim'),
    (3, 'TV',      'Electronics', 'Mr. Karim');
   
   -- Forget even one row and the data is now inconsistent:
   UPDATE orders_v1 SET category_manager = 'Ms. Tahmina'
   WHERE order_id < 3;
   
   SELECT * FROM orders_v1;

   Run Copy Open in SQLite Online

   Notice the contradiction: order 1 and 2 say "Ms. Tahmina", order 3 still says "Mr. Karim".

10. Given book_loan(loan_id, member_id, member_name, isbn, title, due_date) with PK loan_id, list every functional dependency.
    উপরের লাইব্রেরি টেবিলে কোন কোন functional dependency আছে?
    ✨ Show Answer (উত্তর দেখুন)

    Answer: loan_id → member_id, isbn, due_date; member_id → member_name; isbn → title. The last two are transitive — table is in 2NF but not 3NF.

11. Decompose the book_loan table into 3NF.
    উপরের book_loan-কে 3NF-এ আনুন।
    ✨ Show Answer (উত্তর দেখুন)

    ans11.sql

    CREATE TABLE members (member_id INTEGER PRIMARY KEY, member_name TEXT);
    CREATE TABLE books   (isbn      TEXT    PRIMARY KEY, title       TEXT);
    CREATE TABLE loans (
        loan_id   INTEGER PRIMARY KEY,
        member_id INTEGER REFERENCES members(member_id),
        isbn      TEXT    REFERENCES books(isbn),
        due_date  TEXT
    );
    INSERT INTO members VALUES (1, 'Arif');
    INSERT INTO books   VALUES ('978-1', 'C Programming');
    INSERT INTO loans   VALUES (100, 1, '978-1', '2026-06-01');
    SELECT * FROM loans;

    Run Copy Open in SQLite Online

12. Why is "phone number" sometimes a 1NF violation even though it is a single string?
    phone number কখন 1NF লঙ্ঘন হতে পারে যদিও সেটি একটি single string?
    ✨ Show Answer (উত্তর দেখুন)

    Answer: If a customer can have multiple phone numbers and you store them as "017xxx,018xxx", that single string is multi-valued — a 1NF violation. Either store one phone per customer column (limited) or, better, create a customer_phones(customer_id, phone) child table.

13. Is invoice(invoice_id, total_amount, line_count) in 3NF if line_count is computed from invoice lines?
    উপরের invoice টেবিলে line_count অন্য টেবিল থেকে গণনা করা — এটি কি 3NF-এ আছে?
    ✨ Show Answer (উত্তর দেখুন)

    Answer: Strictly speaking, yes — there is no transitive dependency among the three columns. But it stores derivable data, which is a different code-smell (denormalization for performance). Pure normalization theory has no quarrel here; in module 28 we discuss when you might keep such a column on purpose.

14. Insert two rows that violate 3NF on the products table from §4 and explain.
    §4-এর products টেবিলে এমন দুটি সারি দিন যা 3NF লঙ্ঘন করবে।
    ✨ Show Answer (উত্তর দেখুন)

    Answer: Insert ('Laptop', 60000, 'Electronics', 'Mr. Karim') and ('Tablet', 18000, 'Electronics', 'Ms. Different'). Now two products in the same Electronics category disagree on the manager — a direct contradiction caused by the transitive dependency category → category_manager not being isolated.

15. Take the bKash transaction sheet tx(tx_id, sender_phone, sender_name, receiver_phone, receiver_name, amount) and bring it to 3NF.
    উপরের bKash সারণিকে 3NF-এ আনুন।
    ✨ Show Answer (উত্তর দেখুন)

    ans15.sql

    CREATE TABLE users (
        phone TEXT PRIMARY KEY,
        name  TEXT
    );
    CREATE TABLE tx (
        tx_id           INTEGER PRIMARY KEY,
        sender_phone    TEXT REFERENCES users(phone),
        receiver_phone  TEXT REFERENCES users(phone),
        amount          INTEGER
    );
    INSERT INTO users VALUES ('017', 'Arif'), ('018', 'Sadia');
    INSERT INTO tx    VALUES (1, '017', '018', 1500);
    
    SELECT t.tx_id, s.name AS sender, r.name AS receiver, t.amount
    FROM tx t
    JOIN users s ON s.phone = t.sender_phone
    JOIN users r ON r.phone = t.receiver_phone;

    Run Copy Open in SQLite Online

16. When is keeping a "computed" or "duplicated" column actually correct? Give one good and one bad reason.
    কখন duplicate column রাখা ঠিক হতে পারে — একটি ভালো এবং একটি খারাপ কারণ দিন।
    ✨ Show Answer (উত্তর দেখুন)

    Good reason: a frozen historical value — e.g. order_lines.unit_price_at_purchase. The product price will change tomorrow but the invoice must keep the price at the moment of sale. Storing it is correct, not denormalization.

    Bad reason: "It is faster to read." Without measuring, this is premature optimization that introduces every anomaly we just spent an hour eliminating.