ER Diagram – SQL Server Database Design | Lesson 6
Introduction
Before a builder pours concrete, an architect draws a plan. Before you create tables, you draw an ER diagram: a picture of what the database stores and how the pieces connect.
In this lesson, you turn the school's requirements from Lesson 3 into the complete ER diagram of the SMS.
By the end of this lesson, you will be able to:
- Explain entities, attributes, and relationships.
- Read the three kinds of relationships: one-to-one, one-to-many, and many-to-many.
- Read crow's foot notation on a diagram.
- Turn a many-to-many relationship into a junction table.
- Explain every relationship in the SMS diagram.
Why this matters
Changing a drawing takes a minute. Changing a table that already holds a year of attendance takes a weekend and a careful data migration.
The diagram also catches mistakes early. Can a student have two guardians, and can a guardian have two children in the school?
If the answer is yes and your design says no, the diagram shows it before any data is lost.
The concept
ER stands for Entity-Relationship.
- An entity is a thing the school needs to remember, such as a student or an exam. Each entity becomes a table.
- An attribute is a detail of an entity, such as a student's date of birth. Each attribute becomes a column.
- A relationship is how two entities are connected, such as "a student is enrolled in a section". Relationships become foreign keys.
Example: entities and attributes
Each box is an entity, and inside are its attributes with a short type. Three markers can appear next to attributes:
- PK marks the primary key.
- UK marks a unique key: no two rows can share the value.
- FK, which you will see below, marks a foreign key.
Example: kinds of relationships
Cardinality means "how many on each side". There are three kinds:
| Kind | Meaning | SMS example |
|---|---|---|
| One-to-one | One A has at most one B | A teacher has at most one login account |
| One-to-many | One A has many B; each B has one A | A class has many sections; a section belongs to one class |
| Many-to-many | One A has many B, and one B has many A | A student has many guardians; a guardian can have many children |
Diagrams show cardinality with crow's foot notation: symbols at each end of a line.
| Symbol | Read it as |
|---|---|
|| | exactly one |
o| | zero or one |
|{ | one or more |
o{ | zero or more |
The "crow's foot" is the three-pronged { end: it means "many".
Example: one-to-many
Read it from left to right: one class has one or more sections. From right to
left: each section belongs to exactly one class. The foreign key goes on the
"many" side, so Sections gets a ClassId column.
Example: many-to-many and junction tables
A relational table cannot hold "many" on both sides directly. You add a third table in the middle, called a junction table, with one foreign key to each side.
Each row in StudentGuardians links one student to one guardian. Rajesh Sharma
appears twice: once for Aarav and once for Advika. The junction table can also hold
facts about the link itself, such as Relationship (Father, Mother, Guardian).
Apply it to the SMS
Build the diagram in three steps, straight from the requirements in Lesson 3.
Step 1: find the entities
Read the requirements and underline the important nouns. Each one that the school must remember becomes an entity:
| Area | Entities |
|---|---|
| Masters | AcademicYears, Classes, Sections, Subjects |
| People | Teachers, Students, Guardians |
| Academic | Enrollments, SubjectAllocations, ClassTeachers, AttendanceRecords, Exams, ExamSubjects, Marks |
| Fees | FeeStructures, FeeInvoices, FeeInvoiceItems, FeePayments |
| Security | Users, Roles |
Two more tables appear because of many-to-many links: StudentGuardians and UserRoles. That makes 22 tables in total.
Step 2: find the relationships
For every pair of entities that are connected, ask two questions, one in each direction:
| Pair | Can one A have many B? | Can one B have many A? | Result |
|---|---|---|---|
| Class and Section | Yes, A and B | No, one class | One-to-many |
| Student and Enrollment | Yes, one per year | No, one student | One-to-many |
| Student and Guardian | Yes, father and mother | Yes, brothers and sisters | Many-to-many |
| Teacher and User | No, one login | No, one teacher | One-to-one |
Two "yes" answers always mean a junction table. One "yes" means the foreign key goes on the side that has many rows. Two "no" answers mean one-to-one.
Step 3: draw each area
Draw the diagram in three parts so each one stays readable on one screen.
Example: masters, people and enrollment
Example: one enrollment per student per year
Notice that Students has no SectionId column. A student is in 9-A this year and
10-A next year. If the section lived on the student, last year's history would be
overwritten.
Instead, Enrollments holds one row per student per academic year. Aarav has
one row for 2025-26 (Class 9-A) and one for 2026-27 (Class 10-A). Attendance, marks,
and fees all link to the enrollment, so every record knows which year and section it
belongs to.
Example: attendance, exams and marks
An exam such as "Unit Test 1" has many papers: one per class and subject. Each paper has its own maximum marks. A mark belongs to one paper and one enrollment.
Example: fees, users and roles
An invoice has many payments, which is how part payments work. Teachers ||--o| Users
is the one-to-one case: a teacher has zero or one login.
The full list of columns for every table is in the reference you will build from in Module 3. The diagram shows the shape; the next two lessons decide the details.
Common mistakes
- Putting a list in one column. "Guardians: Rajesh, Neha" in one cell cannot be searched or linked. Fix: use a separate table and a relationship.
- Putting the foreign key on the wrong side. In one-to-many, the key always goes on the "many" side. Fix: ask "which side has many rows?" and put the key there.
- Storing changing facts on the main entity. A section on
Studentsloses history every year. Fix: model it as its own entity with a year, likeEnrollments. - Skipping the diagram. Teams then discover missing tables in production.
Fix: draw and review the diagram before writing
CREATE TABLE.
In real projects
On every project I lead, the ER diagram is reviewed with someone from the business, not only developers. A school administrator spotted in two minutes that one guardian can have three children in the school. That single comment turned a column into a junction table before any data existed.
I also keep the diagram as text, like the Mermaid blocks in this lesson, next to the code. A picture saved on someone's laptop goes out of date in a month. Text diagrams are reviewed and updated in the same commit as the table change.
Best practices
- One entity per real-world thing; one table per entity.
- Put the foreign key on the "many" side of a one-to-many relationship.
- Use a junction table for every many-to-many relationship.
- Model facts that change over time as their own entity with dates or years.
- Review the diagram with someone who knows the business.
Recap
- Entities become tables, attributes become columns, relationships become foreign keys.
- Cardinality: one-to-one, one-to-many, many-to-many.
- Crow's foot:
||exactly one,o|zero or one,|{one or more,o{zero or more. - Many-to-many needs a junction table, such as
StudentGuardians. Enrollmentskeeps one row per student per year, so history is never lost.- The SMS has 22 tables in three areas.
Homework
Library module task: Draw the ER diagram for the Library module with Mermaid. Use three entities, Books, BookCopies, and BookIssues, and connect BookIssues to Students. Paste your diagram into mermaid.live to check it renders.
A correct result looks like: Books one-to-many BookCopies, BookCopies one-to-many BookIssues, and Students one-to-many BookIssues.
Show the solution
Each book has one or more copies. A copy can be issued many times over the years, but only once at a time. That "once at a time" rule is enforced later with a constraint.
Quiz
In an ER diagram, what does an entity become in the database?
Show answer
Answer: B. A table Entities become tables; attributes become columns.
In a one-to-many relationship between Classes and Sections, where does the foreign key go?
Show answer
Answer: B. On Sections The foreign key goes on the many side: Sections gets ClassId.
What does the symbol o{ mean in crow's foot notation?
Show answer
Answer: D. Zero or more o means zero is allowed, and { (the crow's foot) means many.
How do you build a many-to-many relationship between Students and Guardians?
Show answer
Answer: C. Add a junction table with both keys A junction table such as StudentGuardians holds one row per student-guardian link.
Why does the SMS store sections in Enrollments instead of on Students?
Show answer
Answer: B. Because a student's section changes every year and history must be kept One enrollment row per year keeps every year's section, attendance, marks, and fees.
Interview questions
Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.
Fresher (0-2 yrs)
F What is an ER diagram, and what are entities and attributes? concept
An ER (Entity-Relationship) diagram is a picture of what a database stores and how the parts connect. An entity is a thing to remember, such as a student, and becomes a table. An attribute is a detail of an entity, such as a date of birth, and becomes a column.
F What are one-to-one, one-to-many, and many-to-many relationships? concept
One-to-one: one row on each side at most, such as a teacher and their login. One-to-many: one row links to many, such as a class and its sections. Many-to-many: many on both sides, such as students and guardians, which needs a junction table.
F Give an example of a one-to-many relationship and say where the foreign key goes. concept
A class has many sections, and each section belongs to one class. The foreign key always
goes on the "many" side, so Sections has a ClassId column that points to Classes.
Mid (3-5 yrs)
M How do you implement a many-to-many relationship in tables? concept
Add a junction table with a foreign key to each side, and make the pair of keys its
primary key. For students and guardians, StudentGuardians has StudentId and
GuardianId; each row is one link.
M How do you read crow's foot notation? concept
The symbols at each end of a line show how many rows can be on that side: || is exactly
one, o| is zero or one, |{ is one or more, and o{ is zero or more. The three-pronged
"crow's foot" means many. Classes ||--|{ Sections reads "one class has one or more sections".
M What is a junction table, and can it have columns of its own? concept
A junction table connects two tables in a many-to-many relationship. Yes, it can hold
facts about the link itself. StudentGuardians stores Relationship (Father, Mother,
Guardian) and IsPrimaryContact, because those describe the pair, not either side alone.
Senior (6-9 yrs)
S Why does the SMS have an Enrollments table instead of a SectionId column on Students? scenario
A student changes section every year. A SectionId on Students would overwrite last
year's section and break the history of attendance, marks, and fees. Enrollments
keeps one row per student per academic year, and everything year-specific links to it.
How to answer: Explain that the section is a fact about a student in a given year, not about the student forever. Walk through Aarav in 9-A last year and 10-A this year. Mention the trade-off: queries need one more join, which is cheap compared to losing history.
S How do you model a relationship that changes over time, such as which teacher teaches a subject? design
Make the relationship its own table and add the period it is valid for. In the SMS,
SubjectAllocations links teacher, subject, section, and academic year, with a unique
rule on (year, section, subject). Next year gets new rows, and old years stay correct
for reports.
How to answer: State the pattern (a time-scoped association table). Use the SMS allocation example and show how a past report card still shows the right teacher. Discuss the alternative of start and end dates, which handles mid-year changes but makes queries more complex.
Architect / Lead (10+ yrs)
A How would you design attendance so it can later become per period without breaking existing data? design
Keep attendance in its own table linked to the enrollment, and plan an optional period column now. Daily records leave it empty; period records fill it, with a unique rule on enrollment, date, and period. Reports read both, so old data stays valid and new data adds detail.
How to answer: Show that you design for likely change without building it all now. Describe the migration path (add a nullable column, add a new unique index, backfill nothing). Weigh it against a separate PeriodAttendance table, which is cleaner but means every report must read two tables.
Resources
- Code for this lesson: git tag
lesson-6-er-diagram(code repository coming soon) - Files:
docs/er-diagram.md - Video: coming soon
What's next
The diagram shows which tables exist. Next, you prove the design is free of repeated data with normalization. Lesson 7: Normalization 1NF to 3NF
Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.