• 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

Relationships
TypeMeaningExample
One-to-one (1:1)One record matches exactly one recordOne person and one Aadhaar-style ID record
One-to-many (1:N)One record matches many recordsOne class has many students
Many-to-many (M:N)Many records match many recordsStudents 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.

— Remember

Key takeaways

  • Relational databases connect tables with keys.
  • Three relationship types: 1:1, 1:N and M:N.

Check your understanding

  1. 1. One department has many employees, and each employee belongs to one department. From department to employee, the relationship is

    1. One-to-one
    2. Many-to-many
    3. One-to-many
    4. There is no relationship
    Show answer

    Answer: C. One department record matches many employee records: one-to-many (1:N).

  2. 2. In an ER diagram, a relationship is drawn as a

    1. Rectangle
    2. Oval
    3. Triangle
    4. Diamond
    Show answer

    Answer: D. Rectangles show entities, ovals show attributes and diamonds show relationships.

  3. 3. A book can have several authors and an author can write several books. How is this relationship usually stored in a relational database?

    1. With a third (junction) table holding the keys of both tables
    2. By typing all the authors' names into one cell of the Books table
    3. It cannot be stored in a relational database
    4. 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. 4. A Students table has a ClassID column that points to the Classes table. In the Students table, ClassID is a

    1. Primary key
    2. Foreign key
    3. Entity
    4. Junction table
    Show answer

    Answer: B. ClassID in Students refers to the primary key of Classes, so it is a foreign key there.

  5. 5. Tables in a relational database are linked to each other using

    1. Formulas typed in cells
    2. Page numbers
    3. Keys
    4. 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.

Next lesson → SQL: asking questions of a database

Privacy · Terms · Refunds · Delivery