ERD Model Assistance

Question

ERD Model Assistance

mmoreauza

SSC Rookie

Points: 49
More actions
June 1, 2012 at 7:46 am

#392408

Hey all.
I'm busy revising for an exam and I'm going through ER Modeling. I have an example that I've attempted but I'm not sure if it's correct.
Perhaps somebody could point out if what I've done is correct?
Thanks very much.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

The only thing that seems off is the GameTeam table. Why do you just not have two teams in the Game table thet FK back to the Team table? There isn't really a need to break that up that I can think of right now.

Otherwise, I think you've modeled it pretty good.

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

I agree with Steve... a game is always between 2 teams, so I would get rid of GameTeam and just have a foreign key to Team for HomeTeam and AwayTeam in the Game table.

I would also have GamePlayer as the many-to-many link between Game and Player. Goal should then be a child of GamePlayer only. The way your diagram looks, the only route from Game to Player is via Goal... if a player doesn't score a goal there will be nothing to link a Player to a Game.

mmoreauza SSC Rookie Points: 49 More actions · Answer 3

Thanks for the quick reply.

I see what you mean and that does seem more efficient.

Thanks for pointing out the goal story, I have no idea why I put it there 🙂

I've done one more example, is this one correct?

Thanks a lot for your time, I really appreciate it!

TomThomson SSC Guru Points: 104773 More actions · Answer 4

The Stumble Inn diagram is missing checkin information from Booking.

The link between Booking and Invoice is marked 1-1 in both directions, but it isn't really: there will be bookings for which there is no invoice (your booking data correctly omits any reference to invoice).

Tom

mmoreauza SSC Rookie Points: 49 More actions · Answer 5

I see what you mean. Should I rather link invoice to customer then?

TomThomson SSC Guru Points: 104773 More actions · Answer 6

mmoreauza (6/3/2012)
I see what you mean. Should I rather link invoice to customer then?

No, I think that you should just change the annotation on the link between invoice and booking. I'm not sure what annotation you would use in an ERD, because I dislike them so I don't touch them or remember much about them.

There's an injection on invoice into booking, but it can't be surjective and there can't be any complete mapping on booking to invoice, since your description says the invoice is created on departure not on booking; so the mapping in the reverse direction is incomplete, not an injection on booking into invoice. Your annotations seem to say that the relationship include an injection on booking into invoice as well as in the opposite direction, because you mark both directions with "1:1" which is the standard notation in maths (category theory, general algebra, topology, set theory, and relational algebra) to indicate an injection. I would simply remove the 1:1 mark relating to the direction where it isn't appropriate, but I don't know if your style of diagram allows that or not.

In SQL terms, or in terms of Codd's relational theory with nulls, this would man that booking can be a foerign key in invoice, but if you want invoice to be a foreign key in booking you have to allow that field in booking to contain null; in practical database implementation terms you would simply not try to make invoice a foreign key in booking (that's why I pointed out that your not having the field was correct - you hadn't made a very common beginner's mistake), so that you don't hit that problem and don't need to introduce a nullable field - if you want fast discovery of invoice from booking you just provide an index on the booking field of invoice. That of course is also what you would have to do in realtional theory without nulls.

Tom