0

I am a teacher and am querying a solution given by our examination board. The question is shown below and I'd like to ask for help in understanding why my solution would not be acceptable.

Here's the original question / scenario:


enter image description here

enter image description here


Here is my solution:


enter image description here


The Solution, given by our examining board, is shown below. As you'll see, they indicate the need for a 'AssignedSeat' entity. I understanding they are resolving a Many-to-Many relationship here, but I asking if my solution (in blue) would be equally valid by just using these 2 entities?


Standard Mark Scheme enter image description here

1

1 Answer 1

3

Your solution is not equally valid since it has redundancy that can be eliminated. Consider a customer who books 10 seats for a certain show. In your case you have 10 rows (each with 3 items, for a total of 30 items) to represent this fact. In the solution shown, instead, there are only 23 items to represent the same information (10 rows with 2 items, and a single row with 3 items). Since redundancy is bad in database design, the solution with three relations is better than the solution with only two relations.

From a formal point of view, we have that

ShowingId, SeatNumber -> CustomerId

but in the relation BOOKING(CustomerId, ShowingId, SeatNumber), the pair (ShowingId, SeatNumber) is not a candidate key, so the relation is not in Boyce-Codd Normal Form (although it is in Third Normal Form).

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.