Entities, Attributes & Relationships
You'll learn to
- -Turn a plain-English scenario into entities, attributes and relationships
- -Decide whether something is its own entity or just an attribute, using one test
- -Name a relationship's cardinality (one-to-one, one-to-many, many-to-many) before drawing any table
A database schema is the blueprint of a database: which tables exist, which columns each has, and how the tables connect. Designing one starts the same way a good class design does, by finding the nouns. An entity is a distinct thing the system must keep track of, such as a passenger, a booking or a cabin. An attribute is one piece of data that describes a single instance of that thing, such as a passenger's age.
An Analogy: Planning a Filing Cabinet
Before you buy a filing cabinet you decide the drawers, one for each kind of thing you keep: one for passengers, one for cabins. Then you decide what goes on each card inside a drawer: a passenger card has a name and an age. Last, you decide how cards in different drawers refer to each other: a passenger card says which cabin card they sleep in. Entities are the drawers, attributes are the lines on a card, and relationships are the references between cards. Getting this first pass right matters more than any later tuning, because keys, normalization and indexing all build on it.
From a Scenario to a First Entity List
Take the scenario: "track passengers boarding a ship, each with a name, an age, and the cabin they are assigned to." Underline the nouns, exactly as the UML chapter in Phase 1 taught: passenger, name, age, cabin. Passenger and Cabin are substantial enough to be entities, because they have several attributes and an identity of their own. Name and age are not entities. They are attributes that belong to Passenger. The statement below creates the first table. CREATE TABLE belongs to DDL, data definition language, the part of SQL that describes the shape of the data.
CREATE TABLE passengers (
id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
age INTEGER
);The Test: Entity or Attribute?
Ask: does this thing have its own identity and its own attributes, or does it only ever exist as a property of something else? A cabin number can stay an attribute of a booking if the system never has to ask about cabins on their own. The moment the requirements say "each cabin has a deck, a class and a maximum occupancy", Cabin has earned its own table. It now has facts of its own that do not belong to any one passenger.
Relationships: The Verbs Between Entities
Relationships come from the verbs that connect the nouns: "a passenger is assigned to a cabin". Before a relationship can become a foreign key (a column that points at a row in another table), you need to know its cardinality, meaning how many of one side can relate to how many of the other.
- -One-to-one: each passenger has at most one passport record, and each passport record belongs to one passenger.
- -One-to-many: one cabin can sleep up to four passengers, but each passenger sleeps in one cabin.
- -Many-to-many: a passenger can attend many dining sittings, and a sitting holds many passengers.
Each shape needs a different table structure, and the next two chapters show how. An ER diagram (entity-relationship diagram) is simply the sketch of these boxes and lines.
Sketch the entity list and a rough set of relationships before writing any CREATE TABLE. It is the same discipline as drawing a class diagram before coding. Jumping 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.
Discussion0
Join the Discussion
Sign in to leave comments, reply to others, or like insights.
No comments yet. Be the first to start the thread!