01
Data modeling and ERDs: the blueprint that turns a business into data
Nobody builds a house by stacking bricks right away. First you draw a blueprint: how many rooms, where the doors go. Databases work the same way. Sketching out what data the business has and how it all connects is called data modeling, and what you end up with is a data model.
Modeling has three characteristics.
- Abstraction: pull out what countless real-world facts have in common and express it in a fixed format
- Simplification: use an agreed notation so anyone can understand it easily
- Clarification: remove anything vague so it can only be read one way
There are also three ways of looking at a business: what it manages (the data perspective), what it does (the process perspective), and how that work affects the data (the data–process interaction perspective). Data modeling centers on the data perspective.
Conceptual → logical → physical: getting more concrete in three stages
A blueprint starts as a big picture and gets more detailed as you go. Modeling is split into three stages too.
| Stage | What you do | Characteristics |
|---|---|---|
| Conceptual modeling | Find the core entities across the whole business and how they relate | Highest level of abstraction. The big, enterprise-wide picture |
| Logical modeling | Pin down keys, attributes and relationships exactly, and normalize | Independent of any particular DBMS. Highest reusability |
| Physical modeling | Fit it to a real DBMS: tables, column types, indexes | Takes performance and storage into account. Lowest level of abstraction |
The three-schema architecture and data independence
The same data looks different depending on who needs it. ANSI/SPARC splits a database into three levels. The point of splitting them is that changing one level affects the others as little as possible — that's data independence.
| Level | Whose view | Description |
|---|---|---|
| External schema | Users and applications | Just the part each of them needs (views). There can be many |
| Conceptual schema | The whole organization | All data and relationships brought together into one. There is only one |
| Internal schema | The storage device | How the data is actually stored (file and index structures) |
Because the external and conceptual levels are kept apart, users' screens stay the same even when the conceptual schema changes — that's logical data independence. Because the conceptual and internal levels are kept apart, you can change how data is stored without touching the conceptual schema — that's physical data independence.
The problems that bad modeling causes also come in three: redundancy (the same data kept in several places), inflexibility (a small change in the business forces a big change in the model) and inconsistency (related pieces of data stop agreeing with each other).
ERDs: pictures made of entities, relationships and attributes
Data models are usually drawn as an ERD (Entity Relationship Diagram). It has three building blocks: entities, relationships and attributes. The two most common notations are IE notation, which draws the ends of relationship lines as crow's feet, and Barker notation, which draws optional relationships as dashed lines. You usually draw in this order:
- Draw the entities
- Arrange the entities sensibly — the important ones go at the top left
- Connect related entities with relationships
- Write the relationship names
- Mark how many take part in each relationship (one or many)
- Mark whether each relationship is required (mandatory or optional)
Step through the figure below to watch the Department–Employee ERD come together. You can also switch the same model to Barker notation.
6. Mark the optionality — IE notation
- Reads as
- Each department may contain many employees / each employee must belong to exactly one department
Step through how an ERD gets drawn, then switch the same model to Barker notation. Green marks what's new in that step.
Mark whether the relationship is required. Every employee must belong to a department, so the Department side gets a bar (mandatory). A department can have no employees, so the Employee side gets a circle (optional). Now it reads "each department may contain many employees" and "each employee must belong to exactly one department."
If a question says… → pick this
- “The modeling with the highest level of abstraction”Conceptual modeling
- “The model that doesn’t depend on any DBMS and is the most reusable”Logical model
- “The stage that fits the model to a DBMS, weighing performance and storage”Physical modeling
- “The conceptual schema changes but users’ screens stay the same”Logical data independence
- “The storage structure changes but the conceptual schema stays the same”Physical data independence
- “The three building blocks of an ERD”Entities, relationships, attributes
- “Where the important entities go in an ERD”Top left
Advertisement
02
Entities and attributes
Each box in an ERD is an entity, and what you write inside the box are its attributes. The first job is deciding what becomes an entity and what becomes an attribute.
Entities: sets of things the business manages
An entity is something the business needs and has to keep track of over time — things you can put a name on, like employees, departments and orders. Here's the part that trips people up: an entity isn't one thing, it's a set. The Employee entity isn't one employee; it's all employees, and each individual employee in it is called an instance.
To count as an entity, something has to meet these conditions:
- It's information the business needs and wants to manage
- It has an identifier that tells its instances apart one by one
- It has two or more instances — if there's only one, there's no reason to make it an entity
- Business processes use it (create, read or update it)
- It has attributes
- It has at least one relationship with another entity — with exceptions like code or statistics entities, where relationships are left out on purpose
Entities are classified by two criteria.
| Criterion | Type | Examples |
|---|---|---|
| Is it tangible? | Tangible entity — has a physical form | Employee, Product, Instructor |
| Conceptual entity — no physical form, but a concept you manage | Organization, Insurance Product | |
| Event entity — arises as the business operates | Order, Invoice, Course Enrollment | |
| When does it arise? | Basic (key) entity — arises on its own, without other entities | Customer, Product, Department |
| Main entity — arises from basic entities and sits at the center of the business | Order, Contract | |
| Action entity — arises from two or more parents, changes often and keeps growing | Order Item, Change History |
Name entities with the words the business actually uses: no abbreviations, singular nouns, and no duplicate names within the model. Call it ‘Order’, not a plural like ‘Order List’.
Attributes: properties of an entity that can't be split any further
An attribute is a property of an entity that the business needs and that, by its meaning, you don't split any further. An employee's name and salary are attributes, and the ‘Ben’ that the instance Ben has for the Name attribute is an attribute value. Here's how the four fit together:
- One entity is a set of two or more instances
- One entity has two or more attributes
- One attribute holds only one value per instance — if there are several values, split them off into their own entity
Switch between the terms in the figure below to see where each logical-model word lands in the table.
Logical model (ERD)
Employee
PKEmployee No
Name
Dept Code
Salary
Physical model (table) and data
| PKEMP_NO | NAME | DEPT_CODE | SALARY |
|---|---|---|---|
| 101 | Ava | D10 | 300 |
| 102 | Ben | D10 | 250 |
| 103 | Chris | D20 | 400 |
| 104 | Dana | D20 | 350 |
Entity = table
Pick a term. The green highlight shows where that logical-model word lands in the table.
A set of things the business needs to keep track of. The "Employee" entity isn't one employee — it's the name for all employees together. In the physical model it becomes a table.
Attributes are classified by a few criteria as well.
| Criterion | Type | Description |
|---|---|---|
| Nature | Basic attribute | A value the business already uses — name, order date |
| Designed attribute | A value newly created during design — product code, serial number | |
| Derived attribute | A value calculated from other attributes — order total. It can drift from the source values, so the fewer the better | |
| Role in the entity | PK attribute / FK attribute / ordinary attribute | Is it the identifier, did it come from another entity, or neither? |
| Can it be split? | Simple attribute / composite attribute | One piece, like age / several parts, like an address (city, street, number) |
| How many values? | Single-valued attribute / multi-valued attribute | One, like a national ID number / several, like phone numbers — split multi-valued ones into an entity |
The range of values an attribute can take is its domain. You define it with a data type, a size and constraints. For example, the domain of ‘Score’ is whole numbers from 0 to 100.
If a question says… → pick this
- “Something with only one instance”Don’t make it an entity
- “An entity with a physical form, like employees or goods”Tangible entity
- “An entity that arises as the business operates, like orders or invoices”Event entity
- “An entity that arises on its own, without other entities”Basic (key) entity
- “An entity that arises from two or more parents and keeps growing”Action entity
- “An attribute calculated from other attributes”Derived attribute — keep to a minimum for consistency
- “An attribute with several values, like phone numbers”Multi-valued attribute → split into an entity
- “The range of values an attribute can take”Domain
Pro only
That’s the free part of this guide
The other 3 chapters are open to Pro subscribers. Each one continues with 3D visuals and “if a question says…” tips, and the last chapter lets you check yourself with a short quiz.
- 03Relationships and identifiers
- 04Normalization
- 05Denormalization and other modeling considerations
Advertisement
PasslyExam
You have read the concept. Now test it on exam questions
PasslyExam offers original practice questions, explanations and full mock exams built around each certification’s exam guide. The explanations cover why the right option is right and why the look-alike options are wrong. Sign up to practice for free every day.