Chuẩn hóa

Tình huống mở đầu

Tiệm Máy Tính Anh Bảy chép sổ tay vào một bảng phẳng: mỗi dòng ghi đủ tên khách, thành phố, tên sản phẩm, số lượng. Sau một tháng bảng có 6 dòng bán hàng cho 4 đơn. Hôm nay hãng đổi tên sản phẩm mouse thành mouse wireless. Anh Bảy gõ một lệnh UPDATE nhưng chỉ sửa dòng của đơn đầu tiên, vì nghĩ rằng "đơn 1 là đơn có mouse".

Cuối tháng, báo cáo doanh thu theo sản phẩm hiện hai dòng cho cùng một cái mouse: một dòng 1 lần bán, một dòng 2 lần bán. Không có lỗi nào được báo. Hai ngày sau, Anh Bảy muốn ghi một sản phẩm mới là headset chưa bán được cái nào, và bảng từ chối.

Hãy đoán trước: theo bạn, điểm chung của hai chuyện này là gì, và nếu chia bảng ra thì chia thế nào?

Ôn nhanh bài trước: quan hệ nhiều-nhiều giữa đơn và sản phẩm được lưu bằng gì?

Đáp án: một bảng nối có hai khóa ngoại và khóa chính ghép. Bài này đi tiếp: khi bạn không tách bảng đúng, dữ liệu lặp lại và mâu thuẫn nhau.

Mục tiêu bài học

Sau bài này bạn có thể:

• Nhận ra ba loại anomaly (cập nhật, thêm, xóa) khi một bảng gom nhiều thực thể.
• Kiểm tra một bảng có đạt 1NF, 2NF và 3NF không bằng cách hỏi cột này phụ thuộc vào gì.
• Tách bảng phẳng thành các bảng nhỏ bằng INSERT ... SELECT DISTINCT mà không mất dữ liệu.
• Giải thích khi nào một bảng có khóa đơn không thể vi phạm 2NF, và vì sao đôi khi người ta chủ động không chuẩn hóa.

Bức tranh lớn

Xuyên suốt bài, mỗi thông tin là một tờ giấy dán trên tường tiệm. Nếu cùng một tờ giá được dán ở ba chỗ, đổi giá phải đi bóc ba lần, và sót một lần là khách thấy hai giá. Chuẩn hóa là gom mỗi tờ về đúng một chỗ. Dự án xuyên suốt là bảng bán hàng phẳng của shop.

Hình dưới trả lời một câu hỏi: từ cuốn sổ tay đến bảng gọn, dữ liệu đi qua những bước nào?

Cách đọc hình: đọc từ trên xuống. Ô đỏ là điểm xuất phát có vấn đề, ô xanh dương là bảng phẳng, ô tím là kết quả sau khi tách theo khóa, ô xanh lá là đích. Chữ trên mũi tên là quy tắc áp dụng ở bước đó. Màu chỉ là tín hiệu phụ; chữ trong ô mới là nội dung chính.

Chỗ ẩn dụ không còn đúng: tờ giấy dán tường không bị lệch tự động, còn dữ liệu trùng lặp bị lệch mà không ai hay, vì máy không biết hai dòng đó là một sự thật.

Khái niệm cốt lõi

Bài này có bốn khái niệm.

1. Anomaly

Anomaly là lỗi dữ liệu sinh ra từ thiết kế bảng, có ba dạng. Anomaly cập nhật: một sự thật nằm ở nhiều dòng, sửa sót dòng nào thì dữ liệu mâu thuẫn. Anomaly thêm: không ghi được một sự thật vì thiếu một sự thật khác đi kèm. Anomaly xóa: xóa dòng này làm mất luôn một sự thật không liên quan.

2. 1NF: mỗi ô một giá trị

Bảng đạt 1NF khi mỗi ô chứa đúng một giá trị nguyên tử và mỗi dòng xác định được bằng khóa. Không có danh sách trong một ô, không có nhóm cột lặp như phone1, phone2, phone3.

3. 2NF: phụ thuộc đủ cả khóa

Bảng đạt 2NF khi đã 1NF và mọi cột không thuộc khóa phụ thuộc vào toàn bộ khóa chính, không chỉ một phần của nó. Vi phạm chỉ có thể xảy ra khi khóa chính gồm nhiều cột. Trong bảng bán hàng có khóa (order_id, product_id), cột product_name chỉ phụ thuộc product_id, nên vi phạm.

4. 3NF: không phụ thuộc bắc cầu

Bảng đạt 3NF khi đã 2NF và không có cột không thuộc khóa nào phụ thuộc vào một cột không thuộc khóa khác. Chuỗi order_id quyết định customer_id, quyết định zip, quyết định city: cột city chỉ phụ thuộc vào khóa thông qua zip, nên vi phạm.

Cách nhớ quen thuộc: mỗi cột phải phụ thuộc vào khóa, toàn bộ khóa, và không gì ngoài khóa. Hình dưới vẽ các chuỗi phụ thuộc trong bảng phẳng của bài.

Cách đọc hình: mỗi mũi tên là "xác định": biết giá trị bên trái thì biết đúng giá trị bên phải. Nhánh trên là chuỗi bắc cầu bốn ô, ô đỏ cuối cùng (city) là cột vi phạm 3NF. Nhánh dưới chỉ có hai ô: product_name chỉ cần một nửa khóa nên vi phạm 2NF.

Một điều cần cẩn thận: các mũi tên này là luật nghiệp vụ ("một mã bưu chính thuộc đúng một thành phố"), không phải điều đọc ra từ dữ liệu đang có. Dữ liệu tình cờ khớp không chứng minh được luật.

Ví dụ đầu tiên

Bắt đầu từ 1NF. Cuốn sổ tay của Anh Bảy gom món hàng vào một ô, và câu hỏi "đơn nào có mouse?" cho kết quả sai.

CREATE TABLE notebook (
  order_id INT PRIMARY KEY,
  customer VARCHAR(50) NOT NULL,
  items    VARCHAR(200) NOT NULL        -- nhiều món trong một ô
);
INSERT INTO notebook VALUES (1, 'An', 'keyboard, mouse'), (2, 'Binh', 'mouse pad, cable'), (3, 'Chi', 'monitor');

SELECT order_id FROM notebook WHERE items LIKE '%mouse%';

Dự đoán trước khi xem kết quả: có 3 đơn: keyboard, mouse, mouse pad, cable và monitor. Truy vấn trả về mấy đơn?

Kết quả [Đã chạy: MySQL 8.0.44]:

+----------+
| order_id |
+----------+
|        1 |
|        2 |
+----------+

Đơn 2 chỉ mua mouse pad (tấm lót chuột) nhưng vẫn bị lấy vì chuỗi mouse pad chứa chữ mouse. Tách mỗi món thành một dòng thì câu trả lời đúng:

CREATE TABLE notebook_items (
  order_id INT NOT NULL,
  item     VARCHAR(50) NOT NULL,        -- mỗi ô chỉ một giá trị
  PRIMARY KEY (order_id, item)
);
INSERT INTO notebook_items VALUES (1, 'keyboard'), (1, 'mouse'), (2, 'mouse pad'), (2, 'cable'), (3, 'monitor');

SELECT order_id FROM notebook_items WHERE item = 'mouse';

+----------+
| order_id |
+----------+
|        1 |
+----------+

Bây giờ đến bảng phẳng đầy đủ của shop, đã đạt 1NF nhưng gom nhiều thực thể vào một dòng:

CREATE TABLE sales_flat (
  order_id      INT NOT NULL,
  product_id    INT NOT NULL,
  qty           INT NOT NULL,
  product_name  VARCHAR(100) NOT NULL,   -- chỉ phụ thuộc product_id
  customer_id   INT NOT NULL,            -- chỉ phụ thuộc order_id
  customer_name VARCHAR(50) NOT NULL,
  zip           VARCHAR(10) NOT NULL,
  city          VARCHAR(50) NOT NULL,    -- phụ thuộc zip, không phụ thuộc trực tiếp vào khóa
  PRIMARY KEY (order_id, product_id)
);
INSERT INTO sales_flat VALUES
  (1, 1, 1, 'keyboard', 1, 'An',   '70000', 'Ho Chi Minh'),
  (1, 2, 1, 'mouse',    1, 'An',   '70000', 'Ho Chi Minh'),
  (2, 3, 1, 'monitor',  1, 'An',   '70000', 'Ho Chi Minh'),
  (3, 2, 2, 'mouse',    2, 'Binh', '10000', 'Ha Noi'),
  (3, 4, 1, 'cable',    2, 'Binh', '10000', 'Ha Noi'),
  (4, 2, 1, 'mouse',    3, 'Chi',  '70000', 'Ho Chi Minh');

Ba anomaly lần lượt xảy ra với bảng này [Đã chạy: MySQL 8.0.44]. Đầu tiên là sửa sót: đổi tên mouse chỉ ở đơn 1.

UPDATE sales_flat SET product_name = 'mouse wireless' WHERE order_id = 1 AND product_id = 2;
SELECT product_name, COUNT(*) AS sale_rows
FROM sales_flat
WHERE product_id = 2
GROUP BY product_name
ORDER BY product_name;

Dự đoán trước: GROUP BY sẽ cho mấy dòng, mỗi dòng bao nhiêu lần bán?

+----------------+-----------+
| product_name   | sale_rows |
+----------------+-----------+
| mouse          |         2 |
| mouse wireless |         1 |
+----------------+-----------+

Hai tên cho cùng sản phẩm 2: dữ liệu mâu thuẫn mà không có lỗi nào. Tiếp theo là thêm sản phẩm chưa bán:

INSERT INTO sales_flat (product_id, product_name) VALUES (5, 'headset');

ERROR 1364 (HY000): Field 'order_id' doesn't have a default value

Bảng từ chối vì order_id là một phần của khóa chính: không có đơn thì không có dòng, mà sản phẩm headset chưa thuộc đơn nào. Cuối cùng là xóa đơn 2, đơn duy nhất bán monitor:

DELETE FROM sales_flat WHERE order_id = 2;
SELECT DISTINCT product_id, product_name FROM sales_flat ORDER BY product_id;

+------------+--------------+
| product_id | product_name |
+------------+--------------+
|          1 | keyboard     |
|          2 | mouse        |
|          4 | cable        |
+------------+--------------+

Danh sách sản phẩm mất monitor (id 3): xóa một đơn đã làm biến mất luôn thông tin về sản phẩm.

Xem từng bước

Animation dưới đây so sánh việc đổi tên sản phẩm trước và sau khi chuẩn hóa. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.

Bảng trace bằng chữ [Đã chạy: MySQL 8.0.44]:

Bạn vừa làm được: chỉ ra ba anomaly của một bảng phẳng và nói được cột nào gây ra từng cái.

Concept Check

Dùng bảng sales_flat ở trên.

Câu 1. Cột product_name có khóa chính (order_id, product_id) và chỉ phụ thuộc product_id. Bảng vi phạm dạng chuẩn nào?

• A. 1NF, vì tên sản phẩm là chuỗi và có thể chứa nhiều từ.
• B. 2NF, vì cột phụ thuộc vào một phần của khóa ghép.
• C. 3NF, vì tên sản phẩm phụ thuộc vào cột không thuộc khóa.
• D. Không vi phạm dạng nào, vì bảng có khóa chính hợp lệ.

Câu 2. Bảng customers(id, name, zip, city) có khóa id đơn. Biết zip quyết định city. Bảng vi phạm dạng chuẩn nào?

• A. 2NF, vì khóa chính có nhiều cột.
• B. 1NF, vì city lặp lại giá trị ở nhiều dòng.
• C. Không dạng nào, vì chỉ bảng khóa ghép mới có thể vi phạm.
• D. 3NF, vì city phụ thuộc vào khóa thông qua zip.

Câu 3. Vì sao tách sales_flat thành 5 bảng không làm mất dữ liệu?

• A. Vì MySQL lưu một bản sao ẩn của bảng gốc để dự phòng.
• B. Vì mỗi bảng mới giữ đủ khóa để JOIN lại ra đúng các dòng cũ.
• C. Vì các bảng mới có cùng số cột với bảng gốc nên khớp nhau.
• D. Vì DISTINCT tự động gắn kèm dữ liệu gốc cho từng dòng.

Đáp án và giải thích:

• Câu 1: B. A nhầm: 1NF nói về giá trị nguyên tử, tên chuỗi vẫn là một giá trị. C mô tả chuỗi bắc cầu, đây là phụ thuộc một phần. D sai vì khóa hợp lệ chưa đủ để đạt 2NF.
• Câu 2: D. A sai vì khóa đơn mới là điều kiện không thể vi phạm 2NF. B nhầm giữa lặp giá trị và ô nhiều giá trị. C sai vì vi phạm 3NF xảy ra cả với khóa đơn.
• Câu 3: B. A bịa ra cơ chế không tồn tại. C sai vì số cột mỗi bảng ít hơn. D sai vì DISTINCT chỉ loại dòng trùng.

Làm cùng mình

Bây giờ bạn chia bảng phẳng thành các bảng gọn, đúng như hình journey ở đầu bài.

Bước 1. Vẽ ra giấy

Với mỗi cột của sales_flat, hỏi: cột này phụ thuộc vào gì? Gom các cột phụ thuộc cùng một thứ vào một hộp. Mỗi hộp là một bảng với khóa là thứ đó.

Gợi ý: sẽ có năm hộp: products (khóa product_id), zip_city (khóa zip), customers (khóa customer_id), orders (khóa order_id) và order_items (khóa ghép).

Bước 2. Điền chỗ trống

Khung dưới tạo hai bảng đầu và đổ dữ liệu từ bảng phẳng. Cần điền bảng customers và lệnh chuyển dữ liệu:

CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE zip_city (zip VARCHAR(10) PRIMARY KEY, city VARCHAR(50) NOT NULL);
-- TODO 1: tạo bảng customers(id, name, zip) với zip là khóa ngoại tới zip_city(zip)
INSERT INTO products SELECT DISTINCT product_id, product_name FROM sales_flat;
INSERT INTO zip_city SELECT DISTINCT zip, city FROM sales_flat;
-- TODO 2: đổ customers từ sales_flat, mỗi khách một dòng

Gợi ý cho chỗ trống thứ hai: SELECT DISTINCT customer_id, customer_name, zip FROM sales_flat. Nhớ đổ zip_city trước customers, vì khóa ngoại đòi cha có trước.

Chạy xong bạn sẽ thấy [Đã chạy] số dòng của từng bảng và số dòng khi ghép lại. So với dự đoán: bảng phẳng có 6 dòng, ghép lại phải ra đúng 6. Đáp án ở mục Lời giải và đáp án.

Bước 3. Vẽ lại ra giấy

Vẽ lại ba tình huống ở đầu bài (đổi tên, thêm headset, xóa đơn 2) trên các bảng mới, và ghi mỗi tình huống cần bao nhiêu lệnh.

Thử phá nó đi

Ba sai lầm thường gặp khi chuẩn hóa.

Điểm quan trọng nhất của bài là bạn không mất gì khi tách. Kết quả sau khi tách, bằng chứng chạy thật [Đã chạy: MySQL 8.0.44]:

CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE zip_city (zip VARCHAR(10) PRIMARY KEY, city VARCHAR(50) NOT NULL);
CREATE TABLE customers (
  id   INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  zip  VARCHAR(10) NOT NULL,
  FOREIGN KEY (zip) REFERENCES zip_city(zip)
);
CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE order_items (
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  qty        INT NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);
INSERT INTO products SELECT DISTINCT product_id, product_name FROM sales_flat;
INSERT INTO zip_city SELECT DISTINCT zip, city FROM sales_flat;
INSERT INTO customers SELECT DISTINCT customer_id, customer_name, zip FROM sales_flat;
INSERT INTO orders SELECT DISTINCT order_id, customer_id FROM sales_flat;
INSERT INTO order_items SELECT order_id, product_id, qty FROM sales_flat;

SELECT 'products' AS tbl, COUNT(*) AS n FROM products
UNION ALL SELECT 'zip_city', COUNT(*) FROM zip_city
UNION ALL SELECT 'customers', COUNT(*) FROM customers
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items;

+-------------+---+
| tbl         | n |
+-------------+---+
| products    | 4 |
| zip_city    | 2 |
| customers   | 3 |
| orders      | 4 |
| order_items | 6 |
+-------------+---+

Số dòng khi ghép năm bảng lại:

SELECT COUNT(*) AS rebuilt_rows
FROM order_items i
JOIN orders o ON o.id = i.order_id
JOIN customers c ON c.id = o.customer_id
JOIN zip_city z ON z.zip = c.zip
JOIN products p ON p.id = i.product_id;

+--------------+
| rebuilt_rows |
+--------------+
|            6 |
+--------------+

Ghép lại ra đúng 6 dòng như bảng phẳng. Giờ chạy lại ba tình huống anomaly trên các bảng mới: đổi tên, thêm headset, xóa đơn 2.

UPDATE products SET name = 'mouse wireless' WHERE id = 2;
SELECT p.name, COUNT(*) AS sale_rows
FROM order_items i
JOIN products p ON p.id = i.product_id
WHERE i.product_id = 2
GROUP BY p.name;
INSERT INTO products VALUES (5, 'headset');
DELETE FROM order_items WHERE order_id = 2;
SELECT id, name FROM products ORDER BY id;

+----------------+-----------+
| name           | sale_rows |
+----------------+-----------+
| mouse wireless |         3 |
+----------------+-----------+
+----+----------------+
| id | name           |
+----+----------------+
|  1 | keyboard       |
|  2 | mouse wireless |
|  3 | monitor        |
|  4 | cable          |
|  5 | headset        |
+----+----------------+

Chỉ có một tên mouse wireless cho cả ba lần bán, headset được thêm, và monitor vẫn còn trong danh sách dù đơn 2 đã mất dòng hàng.

Tự tay làm

Dùng chung dữ liệu sales_flat và các bảng đã tách của bài. Mỗi bài nêu đầu vào, kết quả mong đợi và tiêu chí đạt.

Cơ bản

Bài 1. Điền bảng phụ thuộc. Đầu vào: cột qty, product_name, customer_name và city của sales_flat. Với mỗi cột, ghi cột đó phụ thuộc vào gì (toàn bộ khóa, một phần khóa, hay qua một cột khác) và vi phạm dạng chuẩn nào, nếu có. Tiêu chí đạt: chỉ ra đúng qty không vi phạm, product_name và customer_name vi phạm 2NF, city vi phạm 3NF. Gợi ý: hỏi "biết một mình product_id có đủ biết cột này chưa?"

Vận dụng

Bài 2. Học viên và khóa học. Đầu vào: bảng phẳng (student_id, course_id, student_name, grade) với khóa (student_id, course_id). Chỉ ra vi phạm 2NF, tách thành ba bảng, chèn 2 học viên, 2 khóa học, 3 lượt học, rồi đổi tên học viên 1 bằng đúng một lệnh. Tiêu chí đạt: truy vấn ghép lại hiện tên mới ở mọi lượt học của học viên 1. Gợi ý: student_name chỉ phụ thuộc student_id.

Thử thách (tùy chọn)

Bài 3. Nhân viên và phòng ban. Đầu vào: bảng (emp_id, emp_name, dept_id, dept_name, dept_manager) với khóa emp_id. Tìm vi phạm 3NF và tách bảng. Tiêu chí đạt: đổi trưởng phòng ban Sales bằng đúng một lệnh UPDATE, và truy vấn ghép lại hiện trưởng phòng mới cho mọi nhân viên Sales. Gợi ý: khóa của bảng này là đơn, nên vi phạm không phải 2NF.

Tự kiểm tra

Trả lời bằng lời của bạn, không nhìn lại bài. Mỗi câu ghi mục tiêu được kiểm tra.

1. (Mục tiêu 1, nhớ lại) Kể tên ba loại anomaly và cho mỗi loại một ví dụ trong bảng sales_flat.
2. (Mục tiêu 2, dự đoán) Bảng notebook có ô items là mouse pad, cable. Câu WHERE items LIKE '%mouse%' trả về dòng này không, và vì sao đó là dấu hiệu vi phạm 1NF?
3. (Mục tiêu 2, giải thích) Vì sao bảng có khóa chính một cột không thể vi phạm 2NF?
4. (Mục tiêu 3, dự đoán) Sau khi tách sales_flat (6 dòng) thành 5 bảng, ghép lại bằng JOIN ra bao nhiêu dòng?
5. (Mục tiêu 4, tìm lỗi) Một đồng nghiệp lưu thêm cột orders.total dù total tính được từ order_items. Đó có phải vi phạm chuẩn hóa không, và rủi ro là gì?

Điều cần nhớ

• Anomaly xuất hiện khi một bảng gom nhiều thực thể: sửa sót, không thêm được, xóa mất thông tin khác.
• 1NF: mỗi ô một giá trị. 2NF: cột phụ thuộc toàn bộ khóa. 3NF: cột không phụ thuộc cột không thuộc khóa khác.
• Tách bảng bằng INSERT ... SELECT DISTINCT rồi nối lại bằng khóa ngoại không làm mất dữ liệu.
• Phụ thuộc là luật nghiệp vụ, không phải điều suy ra từ vài dòng dữ liệu hiện có.

Cheat sheet:

Hình tóm tắt của bài là sơ đồ ở mục Bức tranh lớn.

Câu hỏi để dành cho ngày mai: bạn đã dùng cụm "khóa chính" và "khóa ngoại" rất nhiều lần trong hai bài. Nhưng một bảng có thể có nhiều ứng viên để làm khóa chính, vậy cái nào được chọn, và những cái còn lại gọi là gì?

Đào sâu

Phần này dành cho bạn tò mò.

• BCNF. Boyce-Codd Normal Form chặt hơn 3NF một chút: mọi cột quyết định phải là một khóa ứng viên. Hầu hết bảng đạt 3NF thì cũng đạt BCNF, và trường hợp chúng khác nhau chỉ xuất hiện khi bảng có nhiều khóa ứng viên chồng lên nhau. Bài này chưa đi sâu.
• Không chuẩn hóa có chủ đích. Cột orders.total lưu tổng tiền là dữ liệu dư: tính được từ order_items. Giữ lại có thể giúp báo cáo đọc nhanh, nhưng ai sửa dòng hàng mà quên cập nhật total thì hai nơi mâu thuẫn nhau. Nếu giữ, hãy cập nhật cả hai trong cùng một transaction.
• Kho dữ liệu phân tích. Bảng phục vụ báo cáo thường chủ động giữ dạng phẳng để đọc nhanh, trong khi bảng ghi giao dịch nên chuẩn hóa.
• Giới hạn của bài. Bài này chưa nói đến 4NF, 5NF, và chưa thảo luận kỹ cách tìm mọi phụ thuộc hàm của một bảng lớn.
• Đọc thêm. MySQL 8.0 Reference Manual, trang INSERT ... SELECT: https://dev.mysql.com/doc/refman/8.0/en/insert-select.html

Bài tiếp theo

Bài sau là các loại khóa: khóa chính, khóa ứng viên, khóa phụ, khóa ngoại, khóa ghép, và điều gì xảy ra khi bạn xóa hoặc sửa dòng cha. Mini-dự án nối tiếp: chọn khóa cho bảng users của shop và thử các hành động ON DELETE.

Lời giải và đáp án

Làm cùng mình, Bước 2. Hai chỗ cần điền:

CREATE TABLE customers (
  id   INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  zip  VARCHAR(10) NOT NULL,
  FOREIGN KEY (zip) REFERENCES zip_city(zip)
);
INSERT INTO customers SELECT DISTINCT customer_id, customer_name, zip FROM sales_flat;

Kết quả đầy đủ nằm ở mục Thử phá nó đi (norm, norm_count và norm_join): sản phẩm 4 dòng, thành phố 2 dòng, khách 3 dòng, đơn 4 dòng, dòng hàng 6 dòng, ghép lại 6 dòng.

Bài 1. qty phụ thuộc cả order_id và product_id, không vi phạm. product_name chỉ phụ thuộc product_id và customer_name chỉ phụ thuộc order_id, nên cả hai vi phạm 2NF. city phụ thuộc zip, nên vi phạm 3NF.

Bài 2. Vi phạm 2NF: student_name chỉ phụ thuộc student_id. Tách thành students, courses và enrollments:

CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
CREATE TABLE courses (id INT PRIMARY KEY, title VARCHAR(100) NOT NULL);
CREATE TABLE enrollments (
  student_id INT NOT NULL,
  course_id  INT NOT NULL,
  grade      INT NOT NULL,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);
INSERT INTO students VALUES (1, 'Mai'), (2, 'Nam');
INSERT INTO courses VALUES (10, 'SQL'), (20, 'Java');
INSERT INTO enrollments VALUES (1, 10, 9), (1, 20, 8), (2, 10, 7);
UPDATE students SET name = 'Mai Tran' WHERE id = 1;
SELECT s.name, c.title, e.grade
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
ORDER BY s.id, c.id;

+----------+-------+-------+
| name     | title | grade |
+----------+-------+-------+
| Mai Tran | SQL   |     9 |
| Mai Tran | Java  |     8 |
| Nam      | SQL   |     7 |
+----------+-------+-------+

Bài 3. Vi phạm 3NF: dept_name và dept_manager phụ thuộc dept_id, không phụ thuộc trực tiếp emp_id. Tách phòng ban ra bảng riêng:

CREATE TABLE departments (
  id      INT PRIMARY KEY,
  name    VARCHAR(50) NOT NULL,
  manager VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
  id      INT PRIMARY KEY,
  name    VARCHAR(50) NOT NULL,
  dept_id INT NOT NULL,
  FOREIGN KEY (dept_id) REFERENCES departments(id)
);
INSERT INTO departments VALUES (1, 'Sales', 'Lan'), (2, 'Support', 'Hung');
INSERT INTO employees VALUES (1, 'Bao', 1), (2, 'Cuong', 1), (3, 'Dao', 2);
UPDATE departments SET manager = 'Minh' WHERE id = 1;
SELECT e.name AS employee, d.name AS dept, d.manager
FROM employees e JOIN departments d ON d.id = e.dept_id
ORDER BY e.id;

+----------+---------+---------+
| employee | dept    | manager |
+----------+---------+---------+
| Bao      | Sales   | Minh    |
| Cuong    | Sales   | Minh    |
| Dao      | Support | Hung    |
+----------+---------+---------+

Một lệnh UPDATE departments đổi trưởng phòng Sales cho cả hai nhân viên Bao và Cuong.

Tự kiểm tra.

1. Cập nhật: đổi tên mouse chỉ ở một đơn làm hai tên cho một sản phẩm. Thêm: không ghi được headset chưa bán (lỗi 1364). Xóa: xóa đơn 2 làm mất thông tin monitor.
2. Có, LIKE '%mouse%' lấy cả mouse pad. Đó là dấu hiệu của 1NF bị vi phạm vì phải tìm chuỗi con trong một ô thay vì so sánh giá trị nguyên tử.
3. Vi phạm 2NF là phụ thuộc vào một phần của khóa. Khóa chỉ có một cột thì không có "một phần".
4. 6 dòng, đúng bằng số dòng bảng phẳng.
5. Về lý thuyết là dữ liệu dư, nhưng có thể giữ có chủ đích để đọc nhanh. Rủi ro: hai nơi lệch nhau khi chỉ sửa một nơi, nên phải cập nhật cả hai trong cùng một transaction.