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.

Practice

1. In an ER diagram, what does an entity typically become when converting to a database schema?

easy

A. A table with columns for each attribute

B. A single column in a table

C. A database index

D. A stored procedure

Solution

1. Step 1: Understand what an entity represents

   An entity in an ER diagram represents a real-world object or concept with attributes.

2. Step 2: Map entity to database structure

   Each entity is converted into a table, where each attribute becomes a column in that table.

3. Final Answer:

   A table with columns for each attribute -> Option A

4. Quick Check:

   Entity = Table [OK]

Hint: Entities become tables with columns for attributes [OK]

Common Mistakes:

• Confusing entities with indexes
• Thinking entities become single columns
• Assuming entities become procedures

2. Which of the following is the correct way to represent a one-to-many relationship in tables derived from an ER diagram?

easy

A. Create a new table with only primary keys from both tables

B. Add a foreign key column in the 'many' side table referencing the 'one' side

C. Add a foreign key column in the 'one' side table referencing the 'many' side

D. Use a trigger to link the two tables

Solution

1. Step 1: Understand one-to-many relationship

   One-to-many means one record in the first table relates to many records in the second table.

2. Step 2: Map relationship to tables

   The 'many' side table gets a foreign key column referencing the 'one' side table's primary key.

3. Final Answer:

   Add a foreign key column in the 'many' side table referencing the 'one' side -> Option B

4. Quick Check:

   Foreign key on 'many' side = Add a foreign key column in the 'many' side table referencing the 'one' side [OK]

Hint: Foreign key goes in the 'many' side table [OK]

Common Mistakes:

• Placing foreign key on the 'one' side
• Creating unnecessary tables for one-to-many
• Using triggers instead of foreign keys

3. Given two entities Author(id, name) and Book(id, title, author_id) with a one-to-many relationship from Author to Book, what will be the result of this SQL query?

SELECT a.name, b.title FROM Author a JOIN Book b ON a.id = b.author_id WHERE a.name = 'Alice';

medium

A. Syntax error due to join condition

B. List of all authors and their books

C. List of books with no authors

D. List of all books written by Alice

Solution

1. Step 1: Analyze the JOIN condition

   The query joins Author and Book on matching author IDs, linking books to their authors.

2. Step 2: Apply the WHERE filter

   It filters authors with name 'Alice', so only books by Alice are selected.

3. Final Answer:

   List of all books written by Alice -> Option D

4. Quick Check:

   Join + filter by author name = books by Alice [OK]

Hint: JOIN on foreign key filters books by author [OK]

Common Mistakes:

• Confusing join condition causing no results
• Ignoring WHERE clause filtering
• Thinking it lists all authors

4. You have two tables from an ER diagram: Student(id, name) and Enrollment(student_id, course_id). You want to add a foreign key constraint to Enrollment.student_id. Which SQL statement is correct?

medium

A. ALTER TABLE Enrollment ADD FOREIGN KEY (student_id) REFERENCES Student(id);

B. ALTER TABLE Student ADD FOREIGN KEY (id) REFERENCES Enrollment(student_id);

C. CREATE FOREIGN KEY Enrollment.student_id REFERENCES Student.id;

D. ALTER Enrollment ADD CONSTRAINT FOREIGN KEY student_id Student(id);

Solution

1. Step 1: Identify the correct syntax for adding foreign key

   The standard syntax is ALTER TABLE [table] ADD FOREIGN KEY (column) REFERENCES [other_table](column).

2. Step 2: Apply to given tables

   Enrollment.student_id references Student.id, so the statement must alter Enrollment table.

3. Final Answer:

   ALTER TABLE Enrollment ADD FOREIGN KEY (student_id) REFERENCES Student(id); -> Option A

4. Quick Check:

   ALTER TABLE + ADD FOREIGN KEY + REFERENCES [OK]

Hint: Foreign key added on referencing table with ALTER TABLE [OK]

Common Mistakes:

• Adding foreign key on referenced table
• Wrong ALTER TABLE syntax
• Using CREATE FOREIGN KEY instead of ALTER TABLE

5. Consider an ER diagram with entities Employee(emp_id, name), Project(proj_id, title), and a many-to-many relationship WorksOn between them. How should you map this relationship into tables?

hard

A. Add proj_id as a foreign key column in Employee table

B. Add emp_id as a foreign key column in Project table

C. Create a new table WorksOn with columns emp_id and proj_id as foreign keys

D. Merge Employee and Project tables into one

Solution

1. Step 1: Understand many-to-many relationships

   Many-to-many means multiple employees can work on multiple projects and vice versa.

2. Step 2: Map many-to-many to tables

   This requires a new table (junction table) that holds foreign keys from both Employee and Project tables.

3. Final Answer:

   Create a new table WorksOn with columns emp_id and proj_id as foreign keys -> Option C

4. Quick Check:

   Many-to-many = junction table with two foreign keys [OK]

Hint: Many-to-many needs a new table with two foreign keys [OK]

Common Mistakes:

• Adding foreign key to only one table
• Merging unrelated tables
• Ignoring the need for a junction table