Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ERD Model Assistance Expand / Collapse
Author
Message
Posted Friday, June 1, 2012 7:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 4, 2012 1:17 AM
Points: 3, Visits: 9
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.

Post #1309732
Posted Friday, June 1, 2012 8:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:13 PM
Points: 33,100, Visits: 15,210
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1309778
Posted Friday, June 1, 2012 8:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:22 AM
Points: 1,327, Visits: 4,506
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.



Post #1309793
Posted Friday, June 1, 2012 9:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 4, 2012 1:17 AM
Points: 3, Visits: 9
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!



Post #1309828
Posted Friday, June 1, 2012 10:00 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 8,573, Visits: 9,081
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
Post #1309842
Posted Sunday, June 3, 2012 3:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 4, 2012 1:17 AM
Points: 3, Visits: 9
I see what you mean. Should I rather link invoice to customer then?
Post #1310268
Posted Sunday, June 3, 2012 6:10 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 8,573, Visits: 9,081
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
Post #1310285
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse