Relational Databases: Tables, Keys and Relationships · seed 1 · A4, ink-friendly. The answer key prints on its own page for grown-ups.

Tables, keys and the links between them

Computing · Computer Systems · ages 17-18
Name ______________________   Date ____________

  1. Which proposed key identifies every student and avoids changes when personal details change?

    • A first name that may be shared by several enrolled students
    • A unique, permanent student ID assigned to every row
    • A favourite colour recorded separately for every enrolled student

  2. In a table with one record for each student, what does one row stand for?

    • One student
    • One column
    • One database

  3. Memberships.StudentID is a foreign key to Students.StudentID. With its constraint enforced, every non-null membership StudentID must match an existing student.

    Circle one:   True   False

  4. A student may join several clubs, and a club may have several students. Which relationship does this design allow?

    • One student linked to exactly one club
    • Many students linked to many clubs
    • Many students linked to one club only

  5. In Memberships, students may join several clubs and clubs may have several students. Both IDs are non-null, and each student-club pair may occur only once. Which primary key fits these rules?

    • StudentID alone, even when a student joins several clubs
    • ClubID alone, even when several students join that club
    • Both IDs together, with each pair allowed only once

  6. Two member records each store the same club's current name. Keeping these separate copies guarantees that they agree after a rename.

    Circle one:   True   False

  7. An email address is unique and non-null, but pupils may change addresses. Other tables reference it as the pupil's primary key. What is the main design concern?

    • An email cannot be a primary key because every database key must always be a number
    • A unique email stays unchanged whenever a pupil gets a new address
    • Changing an email may require updating its referencing foreign keys

  8. Students has StudentID 42 for Noor. Clubs has ClubID 7 for Chess. Which Memberships row links Noor to Chess using the two tables' keys?

    • StudentID=42, ClubID=7
    • StudentName=Noor, ClubName=Chess
    • StudentID=7, ClubID=42
LightMySky · lightmysky.comW1-mt_i7pZV6H902-s1

Answer key

For grown-ups. Fold this page away before handing over the rest.

Tables, keys and the links between them W1-mt_i7pZV6H902-s1

  1. A unique, permanent student ID assigned to every row · The proposed ID is unique, present for every row and permanent. First names and colours may be shared.
  2. One student · This table has one record per student. The columns contain properties of that student.
  3. True · The enforced constraint requires a matching student key for each non-null reference. It rejects a reference to a missing student.
  4. Many students linked to many clubs · Each side can have several matches on the other side, so Students to Clubs is many to many.
  5. Both IDs together, with each pair allowed only once · Each ID can repeat on its own. The pair is unique under the stated rule, so the two columns together can identify a membership.
  6. False · A rename can update one copy and miss another. Storing the current name once reduces this copy-update risk.
  7. Changing an email may require updating its referencing foreign keys · The email can meet the key requirements now, but a later change affects references to it. A stable ID avoids key changes caused by new email addresses.
  8. StudentID=42, ClubID=7 · StudentID 42 selects Noor, and ClubID 7 selects Chess. Their pair records the membership without copying the names.
Worksheet · LightMySky