Multiple choiceQuestion 1 · Recall
Loading…
By the end you can identify one-to-one, one-to-many and many-to-many relationships, draw them on an entity-relationship (E-R) diagram, and turn each one into tables with the right keys.
A database is not a pile of unrelated tables. The links between them carry meaning: this loan belongs to that student; this order was placed by that customer.
The type of a relationship is described by cardinality — how many records on one side can be linked to how many on the other.
| Type | Meaning | Example |
|---|---|---|
| One-to-one (1:1) | One record in A links to at most one in B, and vice versa | A student has one student card; a card belongs to one student |
| One-to-many (1:M) | One record in A links to many in B; each B links to one A | One customer places many orders; each order belongs to one customer |
| Many-to-many (M:N) | Many records in A link to many in B | Many students take many courses; each course has many students |
Say the relationship out loud in both directions before choosing a type. "One customer, many orders" and "one order, one customer" together mean one-to-many.
E-R diagrams are a design document. They show what the database should contain before anyone writes SQL.
Student, Book.borrows, places.1 and 1, 1 and M (or a crow's foot for "many"), or M and N.Different textbooks draw the symbols slightly differently. What matters in an exam is that entities, relationships and cardinality are all clear and consistent.
Worked example: Turning many-to-many into tables
A library lets students borrow books. A student can borrow many books over time, and a book can be borrowed by many students. Design tables for this relationship.
You can store a many-to-many relationship by putting a foreign key in one of the two tables.
A single foreign key field can hold only one value per record. A student with five books needs five references, which one field cannot hold. Use a junction table with one row per link.
One-to-many means one table has more rows than the other.
It describes how records are linked, not how many rows exist. A table of countries and a table of cities is one-to-many even if both happen to have a similar number of rows.
The relationship name is just decoration on the diagram.
The verb fixes the meaning and often reveals the cardinality. "A student borrows a book" does not tell you whether a book can be borrowed again; "a book is borrowed many times" does.
A junction table needs no primary key of its own.
DueDate describes a loan, not a book.It needs a way to identify each link uniquely. That may be its own ID, or a composite key such as (StudentID, BookID, DateBorrowed).