A
AiTechWorlds
AiTechWorlds
No architect hands a contractor a description and says "build something." They draw blueprints first — precise drawings that show every wall, door, and dimension before a single brick is laid. Changing a wall on paper costs five minutes. Changing it after construction costs thousands of dollars.
Database design works the same way. Before writing a single line of SQL, a good designer draws an Entity-Relationship (ER) diagram — a visual map that shows what things exist in the system, what properties they have, and how they connect.
Skip this step and you'll spend weeks later reorganizing tables, losing data, and rewriting queries. Do it well and your database almost builds itself.
Every ER diagram is made of three components.
An entity is a distinct, real-world thing that the system needs to track. If you can point to one and say "this is a thing," it's probably an entity.
Examples: Student, Course, Professor, Library Book, Customer, Order
Entities become tables in the final database.
An attribute is a property of an entity — a piece of information you store about it.
Attributes become columns in the final table.
Types of attributes:
A relationship describes how two entities connect.
"A Student enrolls in a Course." That enrollment is a relationship. "A Professor teaches a Course." Teaching is a relationship.
Cardinality defines how many of one entity can relate to how many of another.
Each instance on the left relates to exactly one on the right, and vice versa.
Example: A Person has one Passport. A Passport belongs to one Person.
These are rare. Often signals the two entities could be merged into one table.
One instance on the left relates to many on the right. Each right instance relates to only one left.
Example: A Department has many Professors. Each Professor belongs to one Department.
This is the most common relationship in databases.
Many on the left relate to many on the right.
Example: A Student can enroll in many Courses. A Course can have many Students.
Many-to-many relationships cannot be directly stored in a relational database. They always require a junction table (also called a bridge table or associative entity) between them.
A strong entity can exist independently and has its own primary key. Example: A Student exists whether or not they're enrolled in any courses.
A weak entity depends on another entity for its existence. It cannot be uniquely identified without its parent. Example: An OrderItem only exists as part of an Order. Without the Order, the item has no meaning.
Weak entities are shown with a double rectangle in formal notation and use a partial key (underlined with a dashed line) combined with their parent's key.
Step 1: Identify the entities.
What things does a university need to track?
Step 2: Identify the attributes.
Student: StudentID (PK), FirstName, LastName, Email, GPA
Course: CourseID (PK), CourseName, Credits
Professor: ProfessorID (PK), FirstName, LastName, Email, Salary
Department: DeptID (PK), DeptName, Building
Step 3: Identify the relationships and cardinality.
Step 4: Draw the ER diagram.
| ER Concept | Becomes in SQL |
|---|---|
| Strong entity | One table, PK = entity's primary key |
| Weak entity | Table with composite PK (own partial key + parent FK) |
| 1:N relationship | FK in the "many" side table |
| M:N relationship | New junction table with FKs to both sides |
| 1:1 relationship | FK in either table (usually the weaker side) |
| Multi-valued attribute | Separate table referencing the parent |
| Composite attribute | Separate columns for each sub-part |
Mistake 1: Making a relationship into an entity when it shouldn't be. "Enrollment" is a valid entity only because it has its own attributes (grade, date). A simple "likes" with no extra data might just be a relationship.
Mistake 2: Missing the junction table for M:N. You cannot store "Student has CourseIDs: 101, 102, 103" in a single column. That violates the first rule of normalization.
Mistake 3: Storing derived data. If you can calculate Age from BirthDate, don't store Age as a column. It will become stale the moment the person has a birthday.
Mistake 4: Modeling too early — before understanding the queries. Your ER diagram should be driven by the questions the system must answer. If you need "find all students in courses taught by Professor Smith," then the path Student → Enrollment → Course → Professor must exist in your diagram.
An ER diagram is not bureaucracy. It's thinking before building. Every ambiguity you resolve on paper saves hours of migration scripts later.
The university diagram above will convert directly to four SQL CREATE TABLE statements in the next lessons. Every line in those statements traces back to a decision made here.
Next lesson: The formal rules of the relational model — keys, constraints, and referential integrity.
Get this course's notes on Telegram!
Free cheat sheets, summaries & practice exercises