Is this MEMBER-BOOKING-COURT design in 3rd Normal Form?

Question

I'm a student and new to database design. I've got this requirement:

Here's my answer (primary key is in bold):

MEMBER (Member number, member firstname, member surname)

BOOKING (Member number + Court number, booking date, booking time)

COURT (Member number + Court number, duration, payment)

Is my solution in 3rd normal form yet? And does Member number + Court number make an appropriate composite key for the COURT table?

Member number + Court number is already the key for BOOKING, which is a weak entity here I think. The reason I chose Member number + Court number as the composite key for COURT table is that Court number can be repeated so it doesn't make a very good primary key for the table.

Answer 1

No.

Think about what uniquely identifies a "court". And every attribute of "court" needs to be dependent on the key, the whole key and nothing but the key.

I think an candidate key for "court" would be something like "court number".

And a "court" doesn't have a duration... a "booking" has a duration. A "court" doesn't have a payment, a "booking" has a payment.

---

For "booking", I don't think you have a valid candidate key.

It depends on the interpretation/clarification of the "only one court at a time" rule/constraint. Seems to me that rule is saying that at a given date and time, a member can reserve at most one court. That is, a member can reserve a court for 3pm on Wednesday, and court for 10am on Saturday... but a member cannot reserve two courts for 10am on Saturday.

To help enforce that, you would probably want a unique constraint on the tuple (member_number,booking_date,booking_time). Maybe that's a candidate key for "booking".

(To fully enforce the "one court at a time" rule, there would be some additional logic involved, beyond that unique constraint. To prevent member Sally from booking a court at 10AM on Saturday for 2 hours, and another court at 11AM (that same Saturday) for 1 hour. The unique constraint by itself wouldn't prevent Sally from having two courts booked from 11AM to noon.)

---

An alternative (or additional) interpretation would be that a "court" can be "booked" by at most one member at a given time. That suggest a unique constraint on the booking entity... on the (court_number,booking_date,booking_time) tuple.

Again, the same issue with overlapping periods, different start times but a duration long enough that it overlaps another booking. Again, we wouldn't want to allow a single "court" to be booked from 10AM for 2 hours, and also at 11AM for 1 hour.

---

Clarifying these types of requirements with the data owner/system owner is the purpose for "modeling" sessions, which produce a Entity Relationship Diagram (ERD) as an artifact of the discovery process.

We would have the data owner clarify the requirements by asking appropriate questions...

What is the minimum duration and maximum duration for a "booking"?

Can two bookings for a single court overlap? (the 10AM 2 hour, 11AM 1 hour thing)

Is a member allowed to reserve ("book") a court for Tuesday, and Thursday and Saturday?

etc.

We want to be careful that the constraints that we build into the data model are hard and fast rules, real constraints, which are true all of the time. And not just "Well, that's true most of the time. But Doctor Stevens, we let him book two."