9 Replies - 1556 Views - Last Post: 18 May 2013 - 12:32 PM Rate Topic: -----

#1 James1992  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 130
  • Joined: 30-October 12

Class Diagram unary relationship to SQL

Posted 10 May 2013 - 03:48 AM

Hi,

I currently have the following class diagram. I am mapping the class diagram into a table design for a relational database. I have so far created the SQL tables for passenger, train, Station and also created a table in between to remove the "many to many" relationship. I have done this by taking the primary key from passenger (name) and the primary key from train (trainNumber, departureDate). I am confused however, do I create one intermediate table for TrainStation to remove the many to many, or do I need to create 2 intermediate tables because I have 2 associations?

Also, with the unary relationship, what would I reference?

Attached Image

This post has been edited by James1992: 10 May 2013 - 03:48 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Class Diagram unary relationship to SQL

#2 AndrewMack  Icon User is offline

  • New D.I.C Head

Reputation: 8
  • View blog
  • Posts: 45
  • Joined: 12-April 13

Re: Class Diagram unary relationship to SQL

Posted 10 May 2013 - 04:54 AM

Instead of another table to handle the 2 different Station "types" (Arrival and Departure) you could instead add another column to your link table (in-between-table) to distinguish between arrival or departure.

Just an idea!
Was This Post Helpful? 0
  • +
  • -

#3 James1992  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 130
  • Joined: 30-October 12

Re: Class Diagram unary relationship to SQL

Posted 10 May 2013 - 10:10 AM

View PostAndrewMack, on 10 May 2013 - 04:54 AM, said:

Instead of another table to handle the 2 different Station "types" (Arrival and Departure) you could instead add another column to your link table (in-between-table) to distinguish between arrival or departure.

Just an idea!


I kind of understand this. Would I not need to add 2 columns, both arrival and departure. In this link table, these attributes can then be NULL (as the class diagram shows that the association between train and station may have 0)

Also, you are referring to the link table between train and station, not passenger and train?
Appreciate your advice so far Andrew :)/>

This post has been edited by James1992: 10 May 2013 - 10:13 AM

Was This Post Helpful? 0
  • +
  • -

#4 James1992  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 130
  • Joined: 30-October 12

Re: Class Diagram unary relationship to SQL

Posted 10 May 2013 - 10:40 AM

Also Andrew, I have looked at some other persons work on a similar problem (unary).

Because I have "nextStation" and "previousStation", I would create another table and store these as attributes. They both would also reference Station (Name).

Would you agree?
Was This Post Helpful? 0
  • +
  • -

#5 AndrewMack  Icon User is offline

  • New D.I.C Head

Reputation: 8
  • View blog
  • Posts: 45
  • Joined: 12-April 13

Re: Class Diagram unary relationship to SQL

Posted 10 May 2013 - 10:47 AM

Ah I apologize, sir - I read your scenario under the impression that the Train and Station tables required the link table due to me misreading your post. I only had one coffee this morning - work was busy (but a good kind of busy!). I hope I have not caused you any confusion.

What you're trying to accomplish is a table which holds the Passenger, the Passenger's initial/beginning station, and the Passenger's destination station - correct?

If that is the case then most certainly a link table for the Passengers and Stations tables is useful. And yes, you would have one column for the Passenger ID, one for the departing station, and one for the destination station. I would also consider a column for the Train ID.
Was This Post Helpful? 0
  • +
  • -

#6 James1992  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 130
  • Joined: 30-October 12

Re: Class Diagram unary relationship to SQL

Posted 10 May 2013 - 11:02 AM

Not sure how I would create a link table between passengers and stations as there is no association between these two tables.
Was This Post Helpful? 0
  • +
  • -

#7 AndrewMack  Icon User is offline

  • New D.I.C Head

Reputation: 8
  • View blog
  • Posts: 45
  • Joined: 12-April 13

Re: Class Diagram unary relationship to SQL

Posted 10 May 2013 - 11:18 AM

Yes, that would be smart as well.

I would create a table Routes for the different routes. Then create another table to assign stations to the routes - in this table add a column for sequencing which you can use to get the next/previous station.
Was This Post Helpful? -1
  • +
  • -

#8 AndrewMack  Icon User is offline

  • New D.I.C Head

Reputation: 8
  • View blog
  • Posts: 45
  • Joined: 12-April 13

Re: Class Diagram unary relationship to SQL

Posted 10 May 2013 - 11:35 AM

Depends on what you want to keep track of - I was thinking you would want to know what station your passengers got on and off at.
Was This Post Helpful? 0
  • +
  • -

#9 James1992  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 130
  • Joined: 30-October 12

Re: Class Diagram unary relationship to SQL

Posted 10 May 2013 - 11:45 AM

My aim is to map the above class diagram into a table which I can create using SQL. Thank you for the help :)
Was This Post Helpful? 0
  • +
  • -

#10 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Class Diagram unary relationship to SQL

Posted 18 May 2013 - 12:32 PM

Actually, your class diagram has several problems that you'll want to resolve before mapping it to a database.

First, you don't really have a unary relationship, because stations don't directly associate with stations. They do so through Trains. (If you think about it, your next and previous roles in your unary assocation are only next and previous stations because they are the next and previous stations that a train goes to and comes from.)

Next, you have a many to many relationship between stations and trains, which you can resolve with a Trip class (for want of a more precise word: Trip in this case means one run of a train between two stations).

Next, passengers don't directly associate with trains, they take trips on trains. So, now you have another many-to-many relationship between Passengers and Trips, which you resolve through a Booking or Ticket class. Once you set all that up, it will be much clearer where your methods go, and also much clearer how to map the class diagram to a database.

UML has a few specific conventions that you can use in your diagram. First, you can set up (and it is more typical to set up) the associative entities (i. e. your Trip and Booking classes) like this (top diagram):
Posted Image
For more information, see UML Superstructure Specification v2.0, Section 7.3.4.

Next, you might want to use proper UML notation for the Arrival and Departure roles that you have in your diagram for Station. (Nobody says you have to, given the principle that "just enough" documentation is the right amount.) To do this, you'll need to use the more heavyweight notation (second diagram) for AssociationClass in the diagram above, drawing rectangles for the roles so:
Posted Image
I'll leave it to you to work out the multiplicities in this diagram, in case you're interested in the exercise. For more information on this, see UML Superstructure Specification v2.0, Section 7.3.44, down by the end. This doc is a bit confusing, because the qualifier (in other words, the Role attribute) is called a "Property". In this case we are talking about the fact that Qualifier is a Property of the Class metaclass--in other words, a property of the Class diagram element itself. (Dangit! I'm talking about the diagram element that you use to denote a class, which is called the "Class" diagram element. Blech.) We are not talking about the properties of the class itself, e. g. StationName. Those get listed inside the class element in the section right below the label, as you have them in your diagram.

So, if you revisit your UML diagram first, the mapping to a db will be much more obvious.

This post has been edited by BobRodes: 18 May 2013 - 02:17 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1