ER diagram to table mapping in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Mapping a One-to-Many Relationship

Consider an ER diagram with two entities: Author and Book. Each author can write many books, but each book has only one author.

How should this relationship be represented in the relational tables?

A Merge <code>Author</code> and <code>Book</code> into a single table with all attributes.

B Add a foreign key column <code>book_id</code> in the <code>Author</code> table referencing <code>Book</code>.

C Create a separate table <code>AuthorBook</code> with foreign keys to both <code>Author</code> and <code>Book</code>.

D Add a foreign key column <code>author_id</code> in the <code>Book</code> table referencing <code>Author</code>.

💡 Hint

Think about which entity can have multiple related records.

In a one-to-many relationship, the table on the 'many' side (Book) gets a foreign key referencing the 'one' side (Author).

❓ query_result

intermediate

Result of Mapping a Many-to-Many Relationship

Given an ER diagram with entities Student and Course having a many-to-many relationship, which table structure correctly represents this in SQL?

A Tables: <code>Student</code>, <code>Course</code>, and a join table <code>StudentCourse</code> with foreign keys to both.

B Tables: <code>Student</code> with a foreign key to <code>Course</code>.

C Tables: <code>Course</code> with a foreign key to <code>Student</code>.

D Single table combining <code>Student</code> and <code>Course</code> attributes.

💡 Hint

Many-to-many relationships require an extra table to connect the two entities.

Many-to-many relationships are implemented using a join table that holds foreign keys to both related tables.

📝 Syntax

advanced

Identify the Correct SQL Table Creation for a Weak Entity

A weak entity Dependent depends on Employee with a partial key dependent_name. Which SQL table definition correctly represents this weak entity?

SQL

CREATE TABLE Dependent (
  employee_id INT,
  dependent_name VARCHAR(50),
  birth_date DATE,
  PRIMARY KEY (employee_id, dependent_name),
  FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
);

A Use <code>employee_id</code> as primary key and foreign key without <code>dependent_name</code>.

B Use only <code>dependent_name</code> as primary key without foreign keys.

C Use a composite primary key of <code>employee_id</code> and <code>dependent_name</code> with a foreign key to <code>Employee</code>.

D Use <code>dependent_id</code> as a surrogate primary key without foreign keys.

💡 Hint

Weak entities need the owner's key plus their partial key as primary key.

Weak entities use a composite primary key combining the owner's key and their own partial key, with a foreign key referencing the owner.

❓ optimization

advanced

Optimizing Table Design for Subclass Entities

An ER diagram has a superclass Vehicle and subclasses Car and Truck. Which table design optimizes storage and query performance?

A Create separate tables for Vehicle, Car, and Truck with Car and Truck referencing Vehicle by foreign key.

B Create only Car and Truck tables without a Vehicle table.

C Create one table for Vehicle with all attributes of Car and Truck, using NULLs where not applicable.

D Create a single table combining Car and Truck attributes but no Vehicle table.

💡 Hint

Think about normalization and avoiding NULLs.

Using separate tables for subclasses referencing the superclass avoids NULLs and keeps data organized, improving performance.

🔧 Debug

expert

Debugging Incorrect Foreign Key Placement

Given an ER diagram with entities Order and Customer where each order belongs to one customer, the following SQL tables are created:

CREATE TABLE Customer (
  customer_id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE `Order` (
  order_id INT PRIMARY KEY,
  order_date DATE,
  customer_id INT,
  FOREIGN KEY (order_id) REFERENCES Customer(customer_id)
);

What is the error in this design?

A The <code>Order</code> table is missing a primary key.

B The foreign key references the wrong column; it should be <code>customer_id</code> referencing <code>Customer(customer_id)</code>.

C The <code>Customer</code> table should have a foreign key to <code>Order</code>.

D The <code>Order</code> table should not have a foreign key.

💡 Hint

Check which column should reference which in the foreign key.

The foreign key in Order should be on customer_id referencing Customer(customer_id), not on order_id.