Entity-Relationship model in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Entities and Attributes

In an Entity-Relationship (ER) model, which statement correctly describes the difference between an entity and an attribute?

A An entity represents a real-world object or concept, while an attribute describes properties or characteristics of that entity.

B An attribute represents a real-world object, while an entity describes properties of that attribute.

C Entities and attributes are the same and can be used interchangeably in ER diagrams.

D An attribute is a relationship between two entities.

💡 Hint

Think about what you want to represent as a thing versus what describes that thing.

Entities are objects or concepts like 'Student' or 'Car'. Attributes are details about entities, like 'Name' or 'Color'.

📋 Factual

intermediate

Cardinality in Relationships

What does the cardinality 'one-to-many' mean in an ER model?

A Many entity instances in the first set can be associated with only one instance in the second set, and vice versa.

B One entity instance in the first set can be associated with many instances in the second set, but each instance in the second set is associated with only one instance in the first set.

C Each entity instance in both sets can be associated with many instances in the other set.

D Each entity instance in both sets can be associated with only one instance in the other set.

💡 Hint

Consider the direction of 'one' and 'many' in the relationship.

One-to-many means one entity on the left relates to many on the right, but each on the right relates to only one on the left.

🔍 Analysis

advanced

Identifying Weak Entities

Given an ER diagram where an entity 'OrderItem' depends on 'Order' for its identification, which characteristic best identifies 'OrderItem' as a weak entity?

A 'OrderItem' is connected to 'Order' by a many-to-many relationship.

B 'OrderItem' has a unique primary key independent of 'Order'.

C 'OrderItem' has a partial key and cannot be uniquely identified without 'Order'.

D 'OrderItem' has no attributes.

💡 Hint

Think about what makes an entity weak in terms of identification.

A weak entity depends on another entity for its key; it has a partial key that needs the owner's key to be unique.

❓ Comparison

advanced

Difference Between Generalization and Specialization

Which option correctly distinguishes between generalization and specialization in the ER model?

A Generalization combines several entities into a higher-level entity, while specialization divides an entity into lower-level entities based on differences.

B Generalization divides an entity into sub-entities, while specialization merges entities into one.

C Both generalization and specialization mean the same and can be used interchangeably.

D Generalization is used only for attributes, specialization only for relationships.

💡 Hint

Think about whether you are grouping or splitting entities.

Generalization groups entities into a broader category; specialization breaks an entity into more specific types.

❓ Reasoning

expert

Determining the Number of Relationships

Consider an ER model with entities 'Student', 'Course', and 'Instructor'. Each student can enroll in many courses, and each course can have many students. Each course is taught by exactly one instructor, but an instructor can teach many courses. How many total relationships are there in this model?

A Four relationships: Student-Course, Course-Instructor, Student-Instructor, and Instructor-Student.

B Three relationships: Student-Course, Course-Instructor, and Student-Instructor.

C One relationship: Student-Course-Instructor combined.

D Two relationships: one between Student and Course, and one between Course and Instructor.

💡 Hint

Count only direct relationships between entities described.

There are two direct relationships: students enroll in courses, and courses are taught by instructors. No direct relationship between students and instructors is described.

Practice

1. What does an Entity-Relationship (ER) model primarily represent in a database?

easy

A. Entities, their attributes, and relationships

B. Only the tables and columns

C. The programming code for database queries

D. The physical storage details of data

Solution

1. Step 1: Understand the purpose of ER model

   The ER model is designed to visually represent entities, their attributes, and how they relate to each other in a database.

2. Step 2: Compare options with ER model definition

   Options B, C, and D describe other aspects of databases but not the ER model's main focus.

3. Final Answer:

   Entities, their attributes, and relationships -> Option A

4. Quick Check:

   ER model = Entities + Attributes + Relationships [OK]

Hint: ER model = entities + attributes + relationships [OK]

Common Mistakes:

• Confusing ER model with physical database design
• Thinking ER model shows SQL code
• Assuming ER model only shows tables

2. Which shape is commonly used to represent an entity in an ER diagram?

easy

A. Diamond

B. Arrow

C. Oval

D. Rectangle

Solution

1. Step 1: Recall ER diagram symbols

   In ER diagrams, entities are shown as rectangles, relationships as diamonds, and attributes as ovals.

2. Step 2: Match shapes to their meanings

   Since the question asks about entities, the correct shape is rectangle.

3. Final Answer:

   Rectangle -> Option D

4. Quick Check:

   Entity = Rectangle [OK]

Hint: Entity = rectangle, Relationship = diamond, Attribute = oval [OK]

Common Mistakes:

• Mixing entity and relationship shapes
• Choosing oval for entity
• Confusing arrows with entities

3. Consider an ER diagram where a 'Student' entity is connected to a 'Course' entity with a 'Registers' relationship. If one student can register for many courses, but each course can have many students, what type of relationship is this?

medium

A. One-to-One

B. Many-to-Many

C. Many-to-One

D. One-to-Many

Solution

1. Step 1: Analyze the relationship cardinality

   One student can register for many courses, and each course can have many students.

2. Step 2: Identify the relationship type

   This means multiple students relate to multiple courses, which is a many-to-many relationship.

3. Final Answer:

   Many-to-Many -> Option B

4. Quick Check:

   Multiple students and courses = Many-to-Many [OK]

Hint: Both sides many = Many-to-Many relationship [OK]

Common Mistakes:

• Choosing One-to-Many when both sides are many
• Confusing direction of relationship
• Ignoring the 'many' on both sides

4. In an ER diagram, a relationship is shown as a rectangle instead of a diamond. What is the error in this diagram?

medium

A. Attribute symbol is incorrect; should be rectangle

B. Entity symbol is incorrect; should be oval

C. Relationship symbol is incorrect; should be diamond

D. No error; rectangle is correct for relationships

Solution

1. Step 1: Recall ER diagram symbols

   Relationships are represented by diamonds, not rectangles.

2. Step 2: Identify the mistake

   Using a rectangle for a relationship is incorrect; rectangles represent entities.

3. Final Answer:

   Relationship symbol is incorrect; should be diamond -> Option C

4. Quick Check:

   Relationship = Diamond, not rectangle [OK]

Hint: Relationship = diamond, not rectangle [OK]

Common Mistakes:

• Confusing entity and relationship symbols
• Thinking rectangles can represent relationships
• Ignoring standard ER diagram conventions

5. You want to design an ER diagram for a library system where each book can have multiple authors, and each author can write multiple books. Additionally, each book has a unique ISBN and a title. How should you represent the authors and books relationship and the attributes in the ER model?

hard

A. Use entities 'Book' and 'Author' connected by a many-to-many relationship; attributes ISBN and title belong to 'Book'

B. Use a single entity 'BookAuthor' with attributes ISBN, title, and author name

C. Use entities 'Book' and 'Author' connected by a one-to-many relationship; ISBN and title as attributes of 'Author'

D. Use entities 'Book' and 'Author' with no relationship; attributes ISBN and title belong to 'Author'

Solution

1. Step 1: Identify entities and attributes

   'Book' and 'Author' are entities. ISBN and title are attributes of 'Book'.

2. Step 2: Determine relationship type

   Since books can have multiple authors and authors can write multiple books, the relationship is many-to-many.

3. Step 3: Match options to correct design

   Use entities 'Book' and 'Author' connected by a many-to-many relationship; attributes ISBN and title belong to 'Book' correctly uses two entities connected by a many-to-many relationship and assigns attributes properly.

4. Final Answer:

   Use entities 'Book' and 'Author' connected by a many-to-many relationship; attributes ISBN and title belong to 'Book' -> Option A

5. Quick Check:

   Many-to-many + correct attributes = Use entities 'Book' and 'Author' connected by a many-to-many relationship; attributes ISBN and title belong to 'Book' [OK]

Hint: Many-to-many for multiple authors/books; attributes with correct entity [OK]

Common Mistakes:

• Using one-to-many instead of many-to-many
• Assigning book attributes to author
• Combining entities incorrectly