Island Spirit Mas Band keeps its registrations in a messy notebook. Your group is designing its database: choose the keys, work out the relationships, build the ERD with yarn, and then test it.
Business rules: Island Spirit Mas Band
1. Every masquerader plays in exactly one section (for example Sailor Mas or Midnight Robber). A section has many masqueraders.
2. Masqueraders pay for their costumes in instalments. Each payment is made by one masquerader.
3. The band holds events (fetes, rehearsals, the parade). A masquerader attends many events, and each event has many masqueraders. The band records each check-in time.
4. Each section has exactly one costume design, and each design is for one section.
| MasID | Name | Phone | DOB | SectionID | |
|---|---|---|---|---|---|
| M101 | Kerry-Ann Joseph | kajoseph@mail.tt | 868-555-0142 | 11 Mar 2007 | S2 |
| M102 | Darnell Baptiste | dbap@mail.tt | 868-555-0199 | 02 Nov 2006 | S1 |
| M103 | Kerry-Ann Joseph | kerryj08@mail.tt | 868-555-0310 | 25 Jun 2008 | S3 |
| M104 | Jada Joseph | jadaj@mail.tt | 868-555-0142 | 19 Aug 2009 | S2 |
| M105 | Shivani Ramlal | shiv.r@mail.tt | 868-555-0257 | 19 Jan 2007 | S1 |
| M106 | Marcus Charles | mcharles@mail.tt | 868-555-0388 | 11 Mar 2007 | S3 |
| MasID | EventID | CheckInTime |
|---|---|---|
| M101 | E1 | 19:05 |
| M101 | E2 | 18:40 |
| M102 | E1 | 19:20 |
| M103 | E2 | 18:55 |
| M102 | E3 | 20:10 |
| M104 | E1 | 19:05 |
All names, emails and phone numbers are fictional.
Name: ____________________________ Group: ______
Check the sample data. Could each field be used to identify a masquerader? Give evidence from the data.
| Field | Unique in the data? ✓/✗ | Could it be a candidate key? Why or why not? |
|---|---|---|
| MasID | ||
| Name | ||
| Phone | ||
| DOB |
Primary key: ______________ Alternate key(s): ______________ Why did you choose that PK? ________________________________
Is MasID unique in ATTENDANCE? ______ Is EventID? ______ What must the primary key be, and what is this kind of key called?
For each pair, complete both sentences, then decide the relationship type.
| Pair | 1:1, 1:M, M:M or none? | Where does the FK go (or what do you need)? |
|---|---|---|
| SECTION & MASQUERADER | ||
| MASQUERADER & PAYMENT | ||
| MASQUERADER & EVENT | ||
| SECTION & COSTUME_DESIGN | ||
| EVENT & PAYMENT |
Lay out the entity cards and connect them with yarn. Mark each end: one sticky note with a single bar for “one”, or a sticky note with a crow’s foot for “many”. Resolve any many-to-many with the blank card. Add sticky notes for the attributes, underlining the PKs and marking FKs with *. Then copy your ERD here.
Write every table from your ERD. Underline primary keys and mark foreign keys with *.
Your teacher will try to put five changes into your database. For each one, decide: accepted or blocked? If blocked, which rule stops it: primary key, referential integrity or validation?
| # | Accepted / Blocked | Rule that decides it | Explain |
|---|---|---|---|
| 1 | |||
| 2 | |||
| 3 | |||
| 4 | |||
| 5 |
A driving school records: INSTRUCTOR (InstructorID, Name), LEARNER (LearnerID, Name, Phone, LicenceNo), and VEHICLE (VehicleID, Make, RegNo). Each learner has one instructor, and an instructor has many learners. A learner has lessons in many vehicles, and each vehicle is used by many learners. A lesson happens on a date.