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.

ER diagrams are often called ERDs, and they are used across schools and industry. They help you plan your database before you write any SQL or create any tables.


+------------------+         +----------------+
| 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.

In NCEA work, an ER diagram is evidence that you planned your data structure logically. It also helps you explain your design choices during evaluation.

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.

Some diagram styles also show primary keys and foreign keys directly in the diagram. This can make the later table-building step faster, but it is not always required at the planning stage.

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.

Some systems need joining entities to handle many-to-many links, such as enrolment between student and class. These extra entities are added when relationships need more structure.


+--------------------+
| 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”.

Sometimes an attribute should be moved into its own entity, such as turning address into an address entity when you need multiple addresses per person. This is a design choice that improves flexibility and reduces duplication.


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.

Some relationships also have their own attributes, such as a borrowed_date on a borrowing relationship. When that happens, the relationship is often modelled as a new entity instead.


+-------------------+    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.

If you get cardinality wrong, your database tables will allow the wrong data, such as multiple teachers for a class when only one should be allowed. Checking cardinality is one of the most important quality steps in an ER diagram.


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.

After your first draft, check for duplication, missing entities, and unclear names. A clean second draft is usually much easier to read than the first sketch.

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.

To record each borrowing event, you would usually add a loan entity that stores dates and return status. This turns the relationship into a trackable history, which many real databases need.


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.

System to model: School course enrolments

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

Suggested real-world rules to model (you can adjust if you justify it): students can enrol in many courses, each course has many students, each course is taught by one teacher, and enrolment links a student to a course with a date.

For NCEA, keep your final diagram tidy and easy to explain, because it supports your evidence of planning. A diagram that you can describe clearly is usually a diagram that is designed well.