Converting ER diagrams to relational schema in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Mapping a Weak Entity to a Relational Schema

In an ER diagram, a weak entity depends on a strong entity for its identification. When converting this to a relational schema, how is the weak entity typically represented?

A As a separate table including its own partial key and the primary key of the strong entity as a foreign key

B Merged into the strong entity's table without a separate table

C As a table with only its partial key as the primary key

D Ignored in the relational schema since it cannot exist independently

💡 Hint

Think about how the weak entity depends on the strong entity's key for identification.

A weak entity is represented as a separate table that includes its own partial key and the primary key of the related strong entity. Together, these form the composite primary key of the weak entity's table.

📋 Factual

intermediate

Primary Key Selection for a 1:N Relationship

When converting a one-to-many (1:N) relationship from an ER diagram to a relational schema, how is the relationship typically represented?

A Create a new table with foreign keys from both entities as a composite primary key

B Add the primary key of the 'one' side as a foreign key in the 'many' side table

C Add the primary key of the 'many' side as a foreign key in the 'one' side table

D Ignore the relationship since it is implied by the entities

💡 Hint

Consider which side can hold the foreign key without duplication.

In a 1:N relationship, the primary key of the 'one' side is added as a foreign key in the 'many' side table to represent the relationship.

🔍 Analysis

advanced

Handling Many-to-Many Relationships in Relational Schema

Given an ER diagram with a many-to-many (M:N) relationship between two entities, how should this relationship be converted into a relational schema?

A Create a new table with foreign keys referencing the primary keys of both entities, and use these foreign keys as a composite primary key

B Add the primary key of one entity as a foreign key in the other entity's table

C Merge both entities into a single table to represent the relationship

D Ignore the relationship and rely on application logic to manage it

💡 Hint

Think about how to represent multiple associations between two entities in tables.

A many-to-many relationship is represented by creating a new table that includes foreign keys from both entities. These foreign keys together form the composite primary key of the new table, capturing all associations.

❓ Comparison

advanced

Difference Between Total and Partial Participation in Relational Schema

How does total participation of an entity in a relationship affect the relational schema compared to partial participation?

A Total participation requires merging tables, partial participation requires separate tables

B Total participation means the entity is not included in the schema, partial participation means it is included

C Total participation requires the foreign key to be NOT NULL, while partial participation allows NULL values

D There is no difference in the relational schema between total and partial participation

💡 Hint

Consider how mandatory relationships affect foreign key constraints.

Total participation means every instance of the entity must be related, so the foreign key cannot be NULL. Partial participation means some instances may not be related, so the foreign key can be NULL.

❓ Reasoning

expert

Determining the Number of Tables from an ER Diagram

An ER diagram has 3 strong entities, 1 weak entity dependent on one strong entity, and 2 many-to-many relationships among the strong entities. How many tables will the relational schema have after conversion?

A 7

B 8

C 5

D 6

💡 Hint

Count tables for entities and relationships carefully, including weak entities and M:N relationships.

There are 3 tables for strong entities, 1 for the weak entity, and 2 for the many-to-many relationships, totaling 6 tables.

Practice

1. In converting an ER diagram to a relational schema, how is a one-to-many relationship typically represented?

easy

A. Add a foreign key in the table on the 'many' side referencing the 'one' side

B. Create a new table for the relationship

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

D. Merge both entities into a single table

Solution

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

   In a one-to-many relationship, one entity instance relates to many instances of another entity.

2. Step 2: Represent relationship in relational schema

   The foreign key is placed in the table representing the 'many' side, pointing to the 'one' side's primary key.

3. Final Answer:

   Add a foreign key in the table on the 'many' side referencing the 'one' side -> Option A

4. Quick Check:

   One-to-many = foreign key on many side [OK]

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

Common Mistakes:

• Placing foreign key on the 'one' side
• Creating unnecessary new tables for one-to-many
• Merging entities incorrectly

2. Which of the following is the correct way to represent a many-to-many relationship in a relational schema?

easy

A. Add foreign keys in both entity tables referencing each other

B. Merge both entities into a single table

C. Create a new table with foreign keys referencing both entities

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

Solution

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

   Many-to-many means multiple instances of one entity relate to multiple instances of another.

2. Step 2: Represent many-to-many in relational schema

   Create a new table (junction table) with foreign keys referencing the primary keys of both entities.

3. Final Answer:

   Create a new table with foreign keys referencing both entities -> Option C

4. Quick Check:

   Many-to-many = new junction table [OK]

Hint: Use a new table for many-to-many relationships [OK]

Common Mistakes:

• Adding foreign keys directly in entity tables
• Merging entities incorrectly
• Ignoring the need for a junction table

3. Given an ER diagram with entities Student(SID, Name) and Course(CID, Title) connected by a many-to-many relationship Enrolls, which relational schema correctly represents this?

medium

A. Student(SID, Name), Course(CID, Title, SID), Enrolls(SID, CID)

B. Student(SID, Name), Course(CID, Title), Enrolls(SID, CID)

C. Student(SID, Name, CID), Course(CID, Title)

D. Student(SID, Name), Course(CID, Title)

Solution

1. Step 1: Identify entities and relationship type

   Student and Course are entities; Enrolls is many-to-many between them.

2. Step 2: Convert entities and relationship to tables

   Student and Course become tables with their attributes. Enrolls becomes a new table with foreign keys SID and CID.

3. Final Answer:

   Student(SID, Name), Course(CID, Title), Enrolls(SID, CID) -> Option B

4. Quick Check:

   Many-to-many needs separate table [OK]

Hint: Many-to-many always needs a separate table [OK]

Common Mistakes:

• Adding foreign key in Course for Student
• Adding foreign key in Student for Course
• Omitting the Enrolls table

4. You converted an ER diagram with a one-to-one relationship between Person and Passport by adding a foreign key PersonID in Passport. However, the database allows multiple passports for one person. What is the likely mistake?

medium

A. Foreign key in Passport lacks a UNIQUE constraint

B. Foreign key should be in Person table, not Passport

C. Passport table should be merged with Person table

D. PersonID should be primary key in Passport table

Solution

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

   One-to-one means each person has at most one passport and vice versa.

2. Step 2: Check foreign key constraints

   Foreign key alone allows many passports per person unless a UNIQUE constraint is added to enforce one-to-one.

3. Final Answer:

   Foreign key in Passport lacks a UNIQUE constraint -> Option A

4. Quick Check:

   One-to-one needs UNIQUE foreign key [OK]

Hint: Add UNIQUE to foreign key for one-to-one [OK]

Common Mistakes:

• Assuming foreign key alone enforces one-to-one
• Placing foreign key in wrong table
• Merging tables unnecessarily

5. An ER diagram has entities Author(AID, Name), Book(BID, Title), and a many-to-many relationship Writes with an attribute RoyaltyPercentage. How should you convert this to a relational schema?

hard

A. Create Author(AID, Name), Book(BID, Title), Writes(AID, BID)

B. Create Author(AID, Name), Book(BID, Title, RoyaltyPercentage), Writes(AID, BID)

C. Create Author(AID, Name, RoyaltyPercentage), Book(BID, Title), Writes(AID, BID)

D. Create Author(AID, Name), Book(BID, Title), Writes(AID, BID, RoyaltyPercentage)

Solution

1. Step 1: Identify entities and relationship with attribute

   Author and Book are entities; Writes is many-to-many with attribute RoyaltyPercentage.

2. Step 2: Convert entities and relationship with attribute

   Entities become tables with their attributes. The many-to-many relationship becomes a new table including foreign keys and the attribute RoyaltyPercentage.

3. Final Answer:

   Create Author(AID, Name), Book(BID, Title), Writes(AID, BID, RoyaltyPercentage) -> Option D

4. Quick Check:

   Relationship attributes go in junction table [OK]

Hint: Put relationship attributes in the new junction table [OK]

Common Mistakes:

• Adding relationship attribute to entity tables
• Omitting the junction table
• Placing attribute in wrong table