Normalization & Naming

Preview — 3 of 10 questions

Which of the following violations breaks First Normal Form (1NF)?

AA table has no primary key defined

BA column stores a comma-separated list of values (e.g., "tag1,tag2,tag3")

CTwo columns contain identical data

DA row references a non-existent foreign key

A table order_items(order_id, product_id, quantity, product_name) has a composite primary key (order_id, product_id). Which dependency violates 2NF?

javascript

-- After 2NF normalization:
CREATE TABLE products    (product_id SERIAL PRIMARY KEY, product_name TEXT NOT NULL);
CREATE TABLE order_items (order_id INT, product_id INT REFERENCES products(product_id),
                          quantity INT, PRIMARY KEY (order_id, product_id));

Aquantity depends on (order_id, product_id) — the full composite key

Bquantity depends on order_id alone

Corder_id functionally determines product_id

Dproduct_name depends only on product_id — a partial key dependency

A table employees(emp_id, dept_id, dept_name) has emp_id as the primary key. Which normal form does this violate?

javascript

CREATE TABLE departments (dept_id SERIAL PRIMARY KEY, dept_name TEXT NOT NULL);
CREATE TABLE employees   (emp_id SERIAL PRIMARY KEY, dept_id INT REFERENCES departments(dept_id));

AFirst Normal Form — dept_name is not atomic

BSecond Normal Form — dept_name is a partial dependency

CThird Normal Form — dept_name has a transitive dependency on emp_id via dept_id

DNo violation — this schema is fully normalized

Answer all 10 questions (7 more), see explanations for every answer, and track your score.