Entities, Attributes & Relationships
You'll learn to
- -Translate a real-world scenario into entities, attributes, and the relationships between them
- -Draw a first-pass ER diagram before writing a single line of DDL
Every schema design starts the same way a good LLD class design does: by finding the nouns. An entity is a distinct "thing" the system needs to track - a passenger, a booking, a cabin - and an attribute is a piece of data that describes one instance of that thing. Getting this first pass right matters more than any later optimization, because every subsequent decision (keys, normalization, indexing) builds on top of it.
From a Scenario to a First Entity List
Take a scenario like "track passengers boarding a ship, each with a name, age, and the cabin they are assigned to." Underline the nouns exactly like the UML chapter in Phase 1 taught: passenger, name, age, cabin. Passenger and Cabin are substantial enough to be their own entities - they have multiple attributes and an independent identity. Name and age are not entities themselves; they are attributes that belong to Passenger.
CREATE TABLE passengers (
id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
age INTEGER
);The Test for "Is This an Entity or an Attribute?"
The practical test: does this "thing" have its own identity and its own independent attributes, or does it only ever exist as a property of something else? A cabin number is an attribute of a booking if a system never needs to query cabins independently (list all cabins, check a cabin's deck level) - but the moment the requirements say "each cabin has a deck, a class, and a maximum occupancy," Cabin has earned its own table, because it now has independent attributes and its own identity separate from any one passenger.
Relationships: The Verbs Connecting Entities
Once entities are identified, relationships come from the verbs connecting them: "a Passenger is assigned to a Cabin." Just like the composition-vs-aggregation distinction from the UML chapter, a database relationship needs a direction and a cardinality (one-to-one, one-to-many, many-to-many) before it can become a foreign key - which is exactly the subject of the next two chapters.
Draw the entity list and a rough sketch of relationships before writing any CREATE TABLE statement - exactly the same discipline as sketching a class diagram before writing code in Phase 1. Skipping straight to DDL is where schema designs go wrong early and expensively.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design The Passenger Manifest and Cabin Assignments in the LLD Lab's Titanic act.