Skip to content
Ameen Abozaid
Englishالعربية
BEG-049

Relational modeling

  • 6 Lessons
  • 73 min of reading
  • 3 h practice (estimate)
Start module
On this page 8 topics

Overview

Topics

  • Tables
  • columns
  • rows
  • primary/foreign keys
  • relationships
  • normalization basics

Learn first

73 min of reading · 3 h of practice (estimate)

Learning outcomes

  1. Model entities as tables with appropriate columns and types.
  2. Choose primary keys and define foreign keys.
  3. Model one-to-many and many-to-many relationships (junction tables).
  4. Normalize a flat table to third normal form and explain the anomalies removed.

Lessons

  1. 01Tables, rows, and columns12 min read
  2. 02Primary keys12 min read
  3. 03Foreign keys and relationships12 min read
  4. 04Many-to-many12 min read
  5. 05Normalization in practice12 min read
  6. 06Demo: normalizing a library spreadsheet into tables13 min read

Lab and exercises

Lab

BEG-049-LAB1 · Library schema

Outcomes: 1–4 · Time: ~3 h Design the ER diagram for books, authors, members, and loans, with columns, types, PKs, and FKs. Have a peer review it. Acceptance: 3NF; relationships correct; peer-reviewed.

Exercises (assessment: normalize a provided spreadsheet)

IDOutcomeTask
BEG-049-EX011Model a members table: choose columns and a type for each.
BEG-049-EX022Choose a primary key for members and say why email is a poor choice.
BEG-049-EX032Add the foreign key that links loans to members and books.
BEG-049-EX043Model "a book can have many authors" with a junction table.
BEG-049-EX053Fix a books table that has a column tags containing "java,sql,web".
BEG-049-EX064Normalize the provided flat spreadsheet to 3NF (list resulting tables).
BEG-049-EX074Name the update, insert, and delete anomalies in the flat sheet.
Show solutions
  • EX01 members(id integer, name text, email text, joined_on date).
  • EX02 id, a generated number; emails can change and may be reused.
  • EX03 loans(id, member_id FK -> members.id, book_id FK -> books.id, loaned_on, due_on, returned_on).
  • EX04 book_authors(book_id, author_id) with a composite PK and two FKs.
  • EX05 Create tags(id, name) and book_tags(book_id, tag_id); one tag per row.
  • EX06 Typically members, books, authors, book_authors, loans, each with a PK; no repeated member or book facts.
  • EX07 Update: a changed phone must be edited on every loan row. Insert: a new member cannot exist before a loan. Delete: deleting a member's only loan loses the member.

Quiz

Outcomes: O1 tables and columns · O2 keys · O3 relationships · O4 normalization.

A self-check for you: nothing is graded, saved or sent anywhere. Choose an answer, then check it. You can try again as often as you like.

  1. 1In a table, one row represents:O1
  2. 2Which is the best type for a birth date?O1
  3. 3A primary key must be:O2
  4. 4A foreign key is:O2
  5. 5One author, many books: where does the FK go?O3
  6. 6Many-to-many needs:O3
  7. 7A column holding "java,sql,web" breaks:O3
  8. 83NF means columns depend on:O4
  9. 9Changing one phone number in many rows is an:O4
  10. 10Normalization mainly:O4

Practical part

Assessment: normalize a provided spreadsheet to 3NF (EX06) plus the library ER diagram lab. Pass: at least 8 of 10 quiz questions and a correct 3NF result.

Common mistakes

Comma-separated values in a column; duplicated data across tables.

Assessment

Normalize a provided spreadsheet.

Career relevance

The Library capstone; data modeling at scale (MID-019).

Type to search levels, phases, modules and lessons.