ER diagram notation in DBMS Theory - Mini Project: Build & Apply

Understanding ER Diagram Notation

📖 Scenario: You are designing a simple database for a library. You want to represent the main parts of the system using an Entity-Relationship (ER) diagram. This diagram will help you understand how different pieces of information relate to each other.

🎯 Goal: Build a basic ER diagram notation using text descriptions for entities, attributes, and relationships. You will create the main entities, define their attributes, set a key attribute, and describe the relationship between entities.

📋 What You'll Learn

Create entities with their exact names

List attributes for each entity with the exact names

Mark the primary key attribute for each entity

Define the relationship between entities with the exact name

Specify the cardinality of the relationship

💡 Why This Matters

🌍 Real World

ER diagrams are used to plan and design databases by visually showing how data is organized and connected.

💼 Career

Database designers, developers, and analysts use ER diagrams to communicate database structure clearly before implementation.

1

Create Entities with Attributes

Create two entities called Book and Author. For Book, add attributes BookID, Title, and Genre. For Author, add attributes AuthorID, Name, and Country.

DBMS Theory

# Define entities Book and Author with their attributes
# Your code here

Hint

Think of entities as objects and attributes as their properties. Use dictionaries to represent each entity with a list of attributes.

2

Mark Primary Keys

Add a key called primary_key to each entity dictionary. Set BookID as the primary key for Book and AuthorID as the primary key for Author.

DBMS Theory

Book = {
    'attributes': ['BookID', 'Title', 'Genre']
}

Author = {
    'attributes': ['AuthorID', 'Name', 'Country']
}
# Add primary_key to each entity
# Your code here

Hint

The primary key uniquely identifies each record in an entity. Add it as a separate key in the dictionary.

3

Define Relationship Between Entities

Create a dictionary called Writes to represent the relationship between Author and Book. Include keys entities with the list ['Author', 'Book'] and relationship_type with the value one-to-many.

DBMS Theory

Book = {
    'attributes': ['BookID', 'Title', 'Genre'],
    'primary_key': 'BookID'
}

Author = {
    'attributes': ['AuthorID', 'Name', 'Country'],
    'primary_key': 'AuthorID'
}
# Define Writes relationship
# Your code here

Hint

The relationship dictionary shows which entities are connected and how many instances relate to each other.

4

Complete ER Diagram Notation Summary

Create a dictionary called ER_Diagram that includes the keys entities with the list [Book, Author] and relationships with the list [Writes].

DBMS Theory

Book = {
    'attributes': ['BookID', 'Title', 'Genre'],
    'primary_key': 'BookID'
}

Author = {
    'attributes': ['AuthorID', 'Name', 'Country'],
    'primary_key': 'AuthorID'
}

Writes = {
    'entities': ['Author', 'Book'],
    'relationship_type': 'one-to-many'
}
# Combine entities and relationships into ER_Diagram
# Your code here

Hint

This final dictionary summarizes the ER diagram by grouping all entities and relationships together.

Practice

1. Which shape in an ER diagram represents an entity?

easy

A. Rectangle

B. Oval

C. Diamond

D. Triangle

Solution

1. Step 1: Recall ER diagram shapes

   Entities are shown as rectangles in ER diagrams.

2. Step 2: Match shape to entity

   Rectangles represent entities, ovals represent attributes, diamonds represent relationships.

3. Final Answer:

   Rectangle -> Option A

4. Quick Check:

   Entity = Rectangle [OK]

Hint: Entities are rectangles, attributes are ovals [OK]

Common Mistakes:

• Confusing attributes (ovals) with entities
• Thinking diamonds are entities
• Mixing shapes with cardinality symbols

2. In ER diagrams, which shape is used to represent an attribute?

easy

A. Diamond

B. Rectangle

C. Hexagon

D. Oval

Solution

1. Step 1: Identify attribute symbol

   Attributes are shown as ovals in ER diagrams.

2. Step 2: Confirm shape usage

   Ovals represent attributes, rectangles represent entities, diamonds represent relationships.

3. Final Answer:

   Oval -> Option D

4. Quick Check:

   Attribute = Oval [OK]

Hint: Attributes are always ovals in ER diagrams [OK]

Common Mistakes:

• Choosing diamond instead of oval
• Confusing entity and attribute shapes
• Selecting shapes not used in ER diagrams

3. Consider an ER diagram where a diamond connects two rectangles with a line labeled '1' on one side and 'N' on the other. What does this cardinality mean?

medium

A. One entity relates to one entity only

B. Many entities relate to one entity

C. One entity relates to many entities

D. Many entities relate to many entities

Solution

1. Step 1: Understand cardinality symbols

   '1' means one entity, 'N' means many entities.

2. Step 2: Interpret relationship

   The side with '1' relates to many entities on the 'N' side, so one entity relates to many entities.

3. Final Answer:

   One entity relates to many entities -> Option C

4. Quick Check:

   Cardinality 1 to N = One to many [OK]

Hint: 1 to N means one entity connects to many [OK]

Common Mistakes:

• Reversing the direction of cardinality
• Confusing 'N' with one
• Ignoring the diamond relationship

4. Identify the error in this ER diagram description: "A diamond represents an attribute, and an oval represents a relationship."

medium

A. Diamond represents a relationship, not an attribute

B. Oval represents an entity, not a relationship

C. Rectangle represents an attribute, not a relationship

D. There is no error in the description

Solution

1. Step 1: Recall ER diagram shape meanings

   Diamonds represent relationships, ovals represent attributes.

2. Step 2: Check the description correctness

   The description wrongly says diamond is attribute and oval is relationship, which is incorrect.

3. Final Answer:

   Diamond represents a relationship, not an attribute -> Option A

4. Quick Check:

   Diamond = Relationship [OK]

Hint: Diamonds = relationships, ovals = attributes [OK]

Common Mistakes:

• Mixing diamond and oval meanings
• Thinking rectangles are attributes
• Assuming no error exists

5. You want to model a "Student" entity with attributes "StudentID" (unique), "Name", and "PhoneNumber" in an ER diagram. Which notation correctly shows the uniqueness of "StudentID"?

hard

A. "StudentID" attribute inside a diamond connected to the Student rectangle

B. "StudentID" attribute underlined inside an oval connected to the Student rectangle

C. "StudentID" attribute inside a rectangle connected to the Student oval

D. "StudentID" attribute inside an oval with a double border connected to the Student rectangle

Solution

1. Step 1: Recall notation for key attributes

   Key (unique) attributes are shown as underlined text inside ovals.

2. Step 2: Apply notation to "StudentID"

   "StudentID" should be underlined inside an oval connected to the Student entity rectangle.

3. Final Answer:

   "StudentID" attribute underlined inside an oval connected to the Student rectangle -> Option B

4. Quick Check:

   Key attribute = Underlined oval [OK]

Hint: Underline key attributes inside ovals [OK]

Common Mistakes:

• Using diamond for attributes
• Placing attributes inside rectangles
• Using double border for uniqueness