Entity relationship diagrams
What an Entity Relationship Diagram Is
An entity relationship diagram is a visual tool for planning databases
. .It shows the structure of information
before any tables are created . .An ER diagram represents real world things as entities
. .It also shows relationships
so you can see how the data links together . .Attributes describe the details that belong to each entity
, such as names and identifiers . .Keys help you tell one record apart from another
inside the same entity . .
Write one sentence that explains what an ER diagram is without using the words “table” or “SQL”. List three possible entities you might find in a school database. For one of your entities, write two attributes it would need.
+------------------+ +----------------+
| student | ------- | class |
+------------------+ +----------------+Why ER Diagrams Are Useful
ER diagrams help you organise information before you build a database
. .They let you break a big problem into smaller parts
that you can check one by one . .A diagram is easier to discuss than a set of table definitions
. .You can show it to a classmate, a teacher, or a client
to confirm the rules make sense . .ER diagrams reduce redesign work later because errors are found early
. .They also make it clearer which tables and links you will need to create
when you implement the solution . .
In one sentence, explain how an ER diagram can prevent mistakes later. Name one person who could give feedback on your diagram, and state what they should check. Write one example of a mistake you might catch early by drawing an ER diagram.
The Main Parts of an ER Diagram
Every ER diagram uses entities, attributes, and relationships to model a system
. .These parts work together to describe what data exists and how it connects
as a complete design . .Entities represent the main categories of data you store, like student or book
. .Attributes represent the fields inside an entity, like name or date of birth
as stored values . .Relationships show how one entity links to another in the real world
. .Cardinality symbols show how many items can be involved on each side
in that relationship . .
Write down one entity, one attribute, and one relationship for a “school club” system. Underline which item is the entity, circle which item is the attribute, and box which item is the relationship. Explain in one sentence what cardinality tells you.
entity + attributes + relationship
+--------------------+ borrows +-------------------+
| member | -------------------- | book |
|--------------------| |-------------------|
| PK member_id | | PK book_id |
| name | | title |
+--------------------+ +-------------------+Understanding Entities
An entity is a thing you store data about in your database
. .Each entity usually becomes a table once you implement the design
in your DBMS . .Entity names are usually singular nouns like student, book, or order
. .This keeps the meaning clear because one row represents one record
of that type . .An entity contains many records, so you need a way to tell them apart
. .That is why each entity needs an attribute that uniquely identifies each record
as a primary key . .
Choose a system such as “library”, “sports team”, or “online shop”. List three entities for that system. For one entity, write a suitable primary key attribute name.
+--------------------+
| student |
|--------------------|
| student_id |
| first_name |
| last_name |
+--------------------+Understanding Attributes
Attributes are the pieces of information stored for each entity
. .For a student, this could include student_id, first_name, and date_of_birth
as separate fields . .A good attribute stores one clear value, not a mixed bundle of information
. .This makes your data easier to search, validate, and update
without errors . .One attribute is chosen as the primary key, which uniquely identifies each record in the entity
. .Primary keys are essential because they allow relationships to be created reliably
between tables . .
Write three attributes for an entity called “book”, and make sure each attribute stores only one value. Identify which attribute should be the primary key, and explain why. Rewrite one bad attribute into two better attributes, such as turning “full_name” into “first_name” and “last_name”.
primary key highlighted
+--------------------+
| student |
|--------------------|
| PK student_id | <-- unique identifier
| first_name |
| date_of_birth |
+--------------------+Understanding Relationships
Relationships show how two entities are connected in your system
. .They represent real world actions or links, such as a member borrows a book
in a library system . .Relationship names often use verbs like borrows, teaches, or belongs_to
. .Clear names help readers understand the purpose of the link
without guessing . .A relationship line is drawn between the two entities it connects
. .Symbols on the ends of the line show the relationship rules, including how many links are allowed
from each side . .
Write two entities and a relationship name that connects them, such as “customer places order”. Explain what the relationship means in the real world in one sentence. Predict one question a database designer would ask to confirm the relationship rules.
+-------------------+ borrows +-------------------+
| member | ------------- | book |
+-------------------+ +-------------------+Cardinality and Optionality
Cardinality tells you how many records can be linked in a relationship
. .The main patterns are one-to-one, one-to-many, and many-to-many
as standard database cases . .Optionality tells you whether the link is required or whether it can be missing
. .For example, a student might need to belong to a class, but a class might exist before any students enrol
in the system . .Crow’s foot notation shows these rules using symbols at the ends of relationship lines
. .A bar represents one, a crow’s foot represents many, and a circle often represents optional
in common notation sets . .
Decide the cardinality for “teacher teaches class” and justify your choice. Decide whether “class has students” is optional or mandatory on each side, and explain why. Write one example of a relationship that is many-to-many.
crow's foot style (ascii approximation)
one-to-many:
+---------+ 1 many +---------+
| class | |--------------< | student |
+---------+ +---------+
optional-to-many:
+---------+ 0 many +---------+
| class | O--------------< | student |
+---------+ +---------+Steps for Drawing an ER Diagram
Start by listing the entities that the system needs to store
. .Use real world nouns that match the purpose of the database
in plain language . .Add the attributes each entity needs, and choose a primary key for each one
. .Keep attributes simple so each field stores one clear value
that can be validated . .Draw relationships between entities and name them clearly
. .Then add cardinality and optionality symbols so the rules are unambiguous
to any reader . .
For a “cafeteria ordering” system, list three entities and one primary key for each. Add one relationship line between two entities, and write a verb name for it. Choose a cardinality for that relationship and explain your reasoning.
A Worked Example
In a library system, you might store information about members and books
. .A member can borrow books, and a book can be borrowed by members over time
as repeated events . .You would create a member entity with attributes like member_id, name, and email
. .You would create a book entity with attributes like book_id, title, and author
as book details . .You would then connect member and book with a borrows relationship
. .The cardinality would show that one member can borrow many books, and one book can be borrowed many times across different members
over the year . .
Identify the two entities and list two attributes for each. Name the relationship and describe what it represents. State the cardinality on both sides and explain what it allows.
member borrows book (with loan entity)
+---------+ 1 many +---------+ many 1 +---------+
| member | |--------------< | loan | >--------------| | book |
+---------+ +---------+ +---------+Tips for Clear, High-Quality ER Diagrams
Use simple, consistent entity names and keep them singular
. .Use precise attributes and avoid repeating the same attribute in multiple entities
unless it is truly required . .Make relationships readable by spacing entities out and avoiding crossed lines
. .Add cardinality and optionality clearly so the rules cannot be misread
during implementation . .Check your diagram by asking whether it would allow incorrect data, such as duplicate IDs or impossible links
. .A good ER diagram should match how the real world system works
without exceptions . .
List two design mistakes that would make an ER diagram hard to read. Write one rule you will follow every time you name entities and relationships. Explain how you would check whether your ER diagram matches the real world.
Final Challenge (from scratch): Create an ER diagram for this system using the entities and attributes below. Add relationships, relationship names, cardinality, and optionality. Make sure each entity has a primary key and that your diagram is neat and readable.
(( student: student_id, first_name, last_name, year_level
course: course_id, course_name, credits
teacher: teacher_id, last_name, room
enrolment: enrolment_id, enrol_date ))