Entity-Relationship model in DBMS Theory - Mini Project: Build & Apply

Building an Entity-Relationship (ER) Model

📖 Scenario: You are designing a simple database for a library system. The system needs to keep track of books, authors, and the categories of books.

🎯 Goal: Create an Entity-Relationship (ER) model that shows the entities, their attributes, and the relationships between them for the library system.

📋 What You'll Learn

Define entities for Book, Author, and Category

Specify at least two attributes for each entity

Create relationships between Book and Author, and between Book and Category

Label the relationships with appropriate names

💡 Why This Matters

🌍 Real World

ER models help design databases by visually organizing data and their connections, which is essential for building efficient library management systems.

💼 Career

Understanding ER models is crucial for database designers, software developers, and data analysts to create clear and effective database structures.

1

Define Entities and Attributes

Create three entities named Book, Author, and Category. For each entity, list exactly two attributes: Book should have BookID and Title, Author should have AuthorID and Name, and Category should have CategoryID and CategoryName.

DBMS Theory

# Define entities and their attributes here
# Your code here

Hint

Think of each entity as a set with its attributes listed inside curly braces.

2

Define Relationships

Create two relationships: one named WrittenBy between Book and Author, and another named BelongsTo between Book and Category. Represent each relationship as a tuple with the relationship name and the two entities it connects.

DBMS Theory

Book = {"BookID", "Title"}
Author = {"AuthorID", "Name"}
Category = {"CategoryID", "CategoryName"}
# Define relationships here
# Your code here

Hint

Use tuples to represent relationships with the format (relationship_name, entity1, entity2).

3

Specify Cardinality for Relationships

Add cardinality information to the relationships. For WrittenBy, specify that one book is written by one author (1:1). For BelongsTo, specify that one book belongs to one category, but one category can have many books (1:N). Represent cardinality as a tuple added to each relationship, for example, ("WrittenBy", "Book", "Author", "1:1").

DBMS Theory

Book = {"BookID", "Title"}
Author = {"AuthorID", "Name"}
Category = {"CategoryID", "CategoryName"}
WrittenBy = ("WrittenBy", "Book", "Author")
BelongsTo = ("BelongsTo", "Book", "Category")
# Add cardinality to relationships here
# Your code here

Hint

Cardinality shows how many instances of one entity relate to instances of another.

4

Complete the ER Model Summary

Create a dictionary named ER_Model that contains the entities and relationships. Use keys "Entities" and "Relationships". Assign the list of entities (Book, Author, Category) to "Entities" and the list of relationships (WrittenBy, BelongsTo) to "Relationships".

DBMS Theory

Book = {"BookID", "Title"}
Author = {"AuthorID", "Name"}
Category = {"CategoryID", "CategoryName"}
WrittenBy = ("WrittenBy", "Book", "Author", "1:1")
BelongsTo = ("BelongsTo", "Book", "Category", "1:N")
# Create ER_Model dictionary here
# Your code here

Hint

Use a dictionary to group entities and relationships for the ER model.

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