An ER diagram becomes a table, and gets rules to enforce
Unit 1's entities and relationships now become actual relations — tables with rows and columns — plus a small set of constraints that keep every row honest and every reference valid.
After this lesson
You should be able to
- Name a relation's parts (relation, tuple, attribute, domain) using correct vocabulary.
- Distinguish a primary key, a candidate key, and a foreign key constraint.
A relation is a table with a strict, formal name for every part
A relation is what you would casually call a table — a named set of rows, each row structured the same way. Formally, each row is a tuple, each column heading is an attribute, and the set of legal values an attribute can hold is its domain (roll_number's domain might be positive integers only). This is the same struct-array idea from CS205ES, formalized: a relation is a set of tuples, the same way an array is a set of struct values sharing one struct definition.
Unlike an array, a relation has no fixed order among its tuples — asking for "the third row" makes no sense without sorting by something specific first. This is a deliberate design choice: it frees the DBMS to store and reorder rows however is fastest internally, as long as query results stay logically correct.
Keys: the rules that make each row findable and connected
A candidate key is any minimal set of attributes that uniquely identifies each tuple — a Student relation might have roll_number as one candidate key and aadhaar_number as another, since either alone is enough. The primary key is the one candidate key the designer chooses as the main identifier; the DBMS actively enforces that it is never null and never repeated across rows.
A foreign key is an attribute in one relation that refers to the primary key of another relation — a Course relation's dept_id column pointing to the Department relation's dept_id primary key. This is the mechanism that keeps two tables genuinely connected instead of just coincidentally sharing a column name; the DBMS refuses to insert a Course row whose dept_id does not actually exist in Department.
From ER diagram to relations: a design process, not magic
Logical database design is the disciplined process of turning an ER diagram into a set of relations: every entity set typically becomes one relation, its attributes become columns, and its key attribute becomes the primary key. Relationships typically become either a foreign key added to one side (for 1:1 or 1:N) or an entirely new relation holding both sides' keys (for M:N) — exactly the decision Unit 1 flagged as depending on relationship degree.
Try it yourself
For the Book and Member entities from Unit 1, sketch the relations they become, naming each relation's primary key and marking any foreign key.
Need a hint?
Book and Member each become their own relation. The many-to-many "Borrows" relationship from Unit 1 becomes its own relation, holding both isbn and membership_id as foreign keys.
Check the worked solution
Book(isbn PK, title, author). Member(membership_id PK, name, contact_number). Borrows(isbn FK to Book, membership_id FK to Member, borrow_date, due_date) — a separate relation because the relationship is many-to-many and carries its own attributes, exactly as Unit 1's relationship-attribute rule predicted.
Quick check
Why does a DBMS refuse to insert a Course row whose dept_id does not exist in the Department relation?
Why this lesson exists
Syllabus mapping
Introduction to the Relational Model, Integrity Constraints Over relations · Enforcing Integrity constraints, Querying relational data, Logical database Design
Maps to course outcomes CO1, CO2.