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

Need a 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

Need a 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

Need a 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

Need a hint?

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