SQL Database Structured Design Quiz

A customer table stores multiple phone numbers in a single field separated by commas. When an employee tries to search for a specific number, the results become inconsistent. Which normalization guideline addresses this issue?

An employee table stores both department name and department location, even though multiple employees belong to the same department. This causes repeated location values. Which normalization concept helps avoid such duplication?

A schema stores product details and supplier details in the same table. When a supplier updates their phone number, many rows must be changed. What undesirable effect does this illustrate?

A table violates 1NF because rows contain varying numbers of values in a single field. What structural fix typically resolves this?

A bookstore wants to prevent storing an author's biography repeatedly with each book. Which structural approach supports better schema design?

A table shows that an attribute depends not on the full key but only part of it, leading to partial dependency. Which normal form resolves this issue?

A company stores city and state information in an orders table, even though these values depend on the customer's address, not the order itself. This illustrates what dependency issue?

A design team notices that deleting the last order of a customer also removes stored address information, making it impossible to retain the customer record. Which anomaly is this?

A team wants to prevent storing derived attributes like 'order_count' that could be computed from related tables. Which design principle does this follow?

A store places customer loyalty level inside the orders table, even though it depends solely on the customer. Which normalization rule suggests removing it?

A course enrollment table includes instructor name, though instructors belong to a separate instructors entity. Why is this problematic?

A team designing a schema wants each table to represent a single subject or entity. Which principle are they following?

A design team identifies attributes that depend entirely on the primary key and not on other attributes. What does this indicate?

A student table includes advisor office number, which belongs to the advisors entity. What design improvement applies here?

A system includes supplier email in a product table. Each time a supplier changes contact details, product rows must be updated. This is a sign the database lacks what?

A table contains both customer details and order details. The design team wants to split it into two tables to avoid mixed responsibilities. This aligns with what rule?

A designer finds that a column's value can be derived entirely from another table. Storing it may produce inconsistencies. What should they do?

A large warehouse table mixes product details with zone locations. These two concepts change at different rates and introduce redundancy. Which design issue appears?

A designer wants to ensure that every attribute describes the entity represented by the table, not external entities. Which guideline applies?

A schema includes multiple repeating address columns such as address1, address2, address3. Which normalization principle suggests using a child table instead?

A team designing an academic system wants to avoid storing student major names repeatedly in every course enrollment row. What structural improvement helps?

A company realizes that employees often move between departments. Instead of storing department name directly in the employee table, they want a more flexible approach. Which structure is ideal?

A composite key relation includes an attribute that depends only on one part of the composite key. Which rule suggests refactoring the design?

A design team wants to avoid mixing transactional data (like orders) with descriptive master data (like product details). Which principle does this support?

This structure places repeating phone numbers in the same row. What normalization rule is being broken?

CREATE TABLE contacts (
  id SERIAL PRIMARY KEY,
  phone_numbers TEXT
);

This table mixes supplier details into the product table. What design issue emerges?

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  supplier_name TEXT,
  supplier_phone TEXT
);

This structure tries to store multiple roles as CSV text. Which normal form is being violated?

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  roles TEXT
);

Here a single table mixes products and categories. What structure should replace it?

CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  product_name TEXT,
  category_name TEXT
);

What normalization problem does this design represent?

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_name TEXT,
  customer_city TEXT
);

This definition uses a composite key. One column depends only on part of it. Which normal form is violated?

CREATE TABLE enrollment (
  student_id INT,
  course_id INT,
  student_major TEXT,
  PRIMARY KEY (student_id, course_id)
);

This structure duplicates project details for every task. What issue appears?

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  project_name TEXT,
  project_client TEXT
);

This refactor extracts project data. What benefit does it provide?

CREATE TABLE projects (
  id SERIAL PRIMARY KEY,
  name TEXT,
  client TEXT
);

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  project_id INT REFERENCES projects(id)
);

This table stores derived values like total_price. What design issue might occur?

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  quantity INT,
  unit_price DECIMAL,
  total_price DECIMAL
);

This design uses a link table to structure many-to-many relations. What normalization benefit does this provide?

CREATE TABLE student_courses (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);

This example decomposes a single table into two. What purpose does decomposition serve?

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title TEXT
);

CREATE TABLE book_details (
  book_id INT REFERENCES books(id),
  isbn TEXT,
  publisher TEXT
);

This design uses foreign keys to connect orders with customers. Which normalization rule does this support?

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id)
);

This structure stores address details in a separate table. What benefit does normalization provide here?

CREATE TABLE addresses (
  id SERIAL PRIMARY KEY,
  street TEXT,
  city TEXT,
  country TEXT
);

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  address_id INT REFERENCES addresses(id)
);

This incorrect design stores both product and supplier information redundantly. What is the recommended alternative?

CREATE TABLE inventory (
  id SERIAL PRIMARY KEY,
  product_name TEXT,
  supplier_phone TEXT
);

This design checks whether normalization is applied correctly. What does this table violate?

CREATE TABLE staff (
  id SERIAL PRIMARY KEY,
  role TEXT,
  role_description TEXT
);

This restructuring removes derived fields from orders. What normalization improvement does this represent?

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  quantity INT,
  price DECIMAL
);
-- total_price removed