Sports Day Entry Sheet

The PE department has been recording Sports Day entries on this sheet. Each student has one row, and every event they entered is listed in that row.

StudentID	StudentName	House	HouseCaptain	Events entered (EventCode · EventName · Venue · Position)
S04	Aria Baptiste	Red	Kyle Mohan	E1 · 100 m Sprint · Track A · 1st E2 · Long Jump · Sand Pit · 3rd
S09	Marcus Ali	Blue	Dante Lewis	E3 · Shot Put · Field B · 2nd
S12	Tamara Rampersad	Red	Kyle Mohan	E1 · 100 m Sprint · Track A · 2nd E4 · 800 m · Track A · 1st
S15	Jaden Cooper	Green	Aisha Ward	E1 · 100 m Sprint · Track A · 3rd E2 · Long Jump · Sand Pit · 1st E3 · Shot Put · Field B · 3rd
S21	Nadia Khan	Green	Aisha Ward	E3 · Shot Put · Field B · 1st E4 · 800 m · Track A · 2nd

Business rules

• Each student belongs to exactly one House. Each House has exactly one captain.

• Each event has one name and one venue.

• A student may enter many events. Position is that student’s result in that event.

All names are fictional.

STUDENT · EACH

WORKSHEET

Scissors Normalisation

Name: ____________________________ Group: ______

Task 1 · Unnormalised form (Phase 1)

Write Sheet A in standard notation. Underline the primary key and put the repeating group inside { }.

Task 2 · First normal form (Phase 1)

Rewrite S04 and S09 so that each row holds one event and every cell holds one value.

StudentID

StudentName

House

HouseCaptain

EventCode

EventName

Venue

Position

Which field, or fields, now form the primary key? _______________________ Why? ______________________________

Task 3 · Dependency detective (Phase 3)

Tick the field each non-key field depends on directly.

Field	StudentID only	EventCode only	Both (whole key)	Another non-key field (name it)
StudentName
House
HouseCaptain
EventName
Venue
Position

Partial dependencies found: __________________ Transitive dependency found: __________________

Task 4 · Your 3NF tables (Phase 3)

Write each mat in notation. Underline primary keys and mark foreign keys with *.

Cells on Sheet B: 80 · Cells left on your mats (not crossed out): ______ · How many times is “Aisha Ward” stored now? ______

Task 5 · Re-run the relay (Phase 4)

Call	Cells touched: Sheet B	Cells touched: 3NF	Facts lost: Sheet B	Facts lost: 3NF
1 Update venue
2 Update captain
3 Insert event
4 Delete entry

Task 6 · Exit ticket (no notes) — 6 marks

A library records: LOAN (MemberID, BookID, MemberName, BookTitle, AuthorID, AuthorName, DateBorrowed)

Identify one partial dependency. (1)
Identify the transitive dependency. (1)
Describe an update anomaly this table could cause. (1)
Write the tables in 3NF, in notation. (3)

STUDENT · GROUP

SHEET B · THE 1NF TABLE · HAND OUT AFTER PHASE 1

Sports Day in 1NF: one row per entry (primary key: StudentID + EventCode)

✂ Phase 2: do not cut yet. Highlight on this sheet. ✂ Phase 3: cut along the dashed lines to get 8 column strips. Keep the rows in order.

STUDENT · GROUP

SHEET C · KEY COPY STRIPS · HAND OUT IN PHASE 3

Keys are the only columns you are allowed to copy. Use a copy wherever a table needs a key to link to another table.

Anomaly Relay call cards (teacher cuts these out)

CALL 1 · UPDATE

Shot Put moves from Field B to Field C.

Highlight every cell that must change.

CALL 2 · UPDATE

Red House has a new captain: Priya Seepersad.

Highlight every cell that must change.

CALL 3 · INSERT

New event: E5 · 4×100 m Relay · Track B. Nobody has entered yet.

Where does it go? Can you add it?

CALL 4 · DELETE

Marcus Ali (S09) withdraws from Shot Put.

Delete his entry. What else disappears with it?