- Foundation
- About 12 min
- 5 practice questions
Relational databases and relationships
Explain the relational model and one-to-one, one-to-many and many-to-many relationships.
If something looks wrong, please tell us.
Real data is connected: a teacher teaches many students, a customer places many orders. The relational model keeps such data in separate tables joined by keys.
The relational model
A relational database stores data in tables that are linked through keys. Instead of repeating a student's details in every row of a marks table, the marks table stores only the Roll number, which links back to the student.
Types of relationship
| Type | Meaning | Example |
|---|---|---|
| One-to-one (1:1) | One record matches exactly one record | One person and one Aadhaar-style ID record |
| One-to-many (1:N) | One record matches many records | One class has many students |
| Many-to-many (M:N) | Many records match many records | Students and subjects: a student takes many subjects and a subject has many students |
A many-to-many relationship is handled by adding a third table, often called a junction table, holding the keys of both sides.
Entity-relationship (ER) diagram
An ER diagram is a picture of the entities (things such as Student) and the relationships between them before the database is built. Rectangles show entities, ovals show attributes and diamonds show relationships.
Worked examples
Example 1. A mother can have many children but each child has one birth mother. Which relationship? One-to-many.
Example 2. Books and authors where one book may have several authors and one author may write several books. Many-to-many.
Example 3. A class has 30 students stored in a Students table with a ClassID column. Is ClassID a primary or foreign key there? A foreign key, pointing to the Classes table.
1:1, 1:N, M:N. Many-to-many needs a third (junction) table.
Key takeaways
- Relational databases connect tables with keys.
- Three relationship types: 1:1, 1:N and M:N.
Check your understanding
-
1. One department has many employees, and each employee belongs to one department. From department to employee, the relationship is
- One-to-one
- Many-to-many
- One-to-many
- There is no relationship
Show answer
Answer: C. One department record matches many employee records: one-to-many (1:N).
-
2. In an ER diagram, a relationship is drawn as a
- Rectangle
- Oval
- Triangle
- Diamond
Show answer
Answer: D. Rectangles show entities, ovals show attributes and diamonds show relationships.
-
3. A book can have several authors and an author can write several books. How is this relationship usually stored in a relational database?
- With a third (junction) table holding the keys of both tables
- By typing all the authors' names into one cell of the Books table
- It cannot be stored in a relational database
- By merging books and authors into one table with no keys
Show answer
Answer: A. Books and authors are many-to-many; a junction table holding a book key and an author key on each row stores it.
-
4. A Students table has a ClassID column that points to the Classes table. In the Students table, ClassID is a
- Primary key
- Foreign key
- Entity
- Junction table
Show answer
Answer: B. ClassID in Students refers to the primary key of Classes, so it is a foreign key there.
-
5. Tables in a relational database are linked to each other using
- Formulas typed in cells
- Page numbers
- Keys
- File names
Show answer
Answer: C. The relational model joins tables through keys: a foreign key in one table matches a primary key in another.
Sign in to keep track of the lessons you finish.