-3

I am learning DBMS in my college. Recently I was given an assignment to draw a E-R Model of a Bus Reservation system which handles Reservations, Ticketing and cancellations.
I understand the theory about E-R model when I study from a book, but it gets confusing when I try to draw one from scratch.
How should one proceed? There seem to be a lot of ways to model a E-R diagram for a particular requirement. It's really confusing.
Can anyone explain taking Bus reservation System as an example?

Here is the model I made (But I am not confident with it because at every step, I could think of many more alternatives!) -

Entity_Set Passenger(passengerID,name,age,gender)

Entity_Set Ticket(ticketID,status)

//Status is either WaitingList , Confirmed or Cancelled

Entity_Set Bus (busID,MaxSeats,Type)

//Type is Ac or Non-AC

Entity_Set Route(routeID,ArrivalTime,DepartureTime,Source,Destination)

And a Ternary relationship between Passenger, Ticket and Bus with attributes as passengerID, ticketID, busID .
Binary relationship between Bus and Route with attributes as busID, routeID .

I have few doubts regarding -
1 . Should we take Time as a composite attribute with Arrival and Departure as its attributes (What's the difference if we take that way?)
2 . The same with Source and Destination. Should they be made into a composite attribute "Place" or something like "Location"?
3. Are there any weak entity sets here? I have no idea at all what to take as a Weak entity set?

Community
  • 1
progammer
  • 751
  • 3
  • 11
  • 15

1 Answers1

3

An E-R diagram simply displays the objects (entities) and how they relate to one another.

So, to draw your diagram, first identify your objects. These are often identified by nouns (people, places, things - and objects are things). We'll start with the following:

Bus
Reservation

From there, each object has certain attributes that help describe them (often, they're fields that hold adjectives). So you might have something like:

Bus
-ID
-LicensePlate
-Capacity
-PurchaseDate

Reservation
-ID
-BusID
-PassengerName

Now, how do these things relate to one another? Well, a bus can hold many passengers, but any given passenger can only be on one bus at any given time (as indicated in the above entity definitions by the BusID attribute in Reservation). Therefore, the relationship between a Bus and a Reservation would be one to many and is generally displayed by something similar to |---> (this is "Crow's Foot" notation, which is what I was taught as what's typically used, adjust if necessary, of course). To display the relationship, you might have something like:

Bus <----| Reservation

The head of the arrow should be on the side of "one" (one bus, many reservations). If you're still stuck, just start identifying all the possible objects and draw your blocks, then start filling in the attributes, then look for the relationships between them. A diagram tool, such as Visio or Dia, makes it really easy to draw, change, and delete diagram blocks, so I recommend grabbing something like that to help you work it out.

Edit - Your diagram looks good to me. You mention that you can think of many more alternatives, so ask yourself these questions:

  1. If the alternative is attributes, are they required for my assignment? Remember, this can be either explicit or implicit (keep in mind, too, your professor's expectations for past assignments to help you predict what is needed). If not, then you probably don't need to include it.
  2. If the alternative is a different way of expressing the relationships, does the alternate way follow the rules of the E-R diagram you're building?
  3. On relationships again, if you've learned about normalization, does it follow normalization rules? (This may or may not be a requirement for you right now, depending on whether you're expected to know about data normalization techniques.)
  4. Does it actually make sense? It might be worth drawing out your alternatives just to see if they make as much sense "on paper" as they did in your head. This is where using a drawing tool comes in handy, since you can draw it out fairly quickly and easily in another file and compare the two side by side.
Shauna
  • 3,519
  • 2
  • 20
  • 19
  • Thanks for your answer :) . In a real world database (same example) , will they even consider PurchaseDate attribute for a bus ? Since its not at all needed in our case . – progammer Mar 27 '12 at 12:52
  • And I am stuck with a E-R model where I considered _Passenger_ , _Ticket_ , _Bus_ , _Route_ as the entities and took a ternary relationship between Passenger , Ticket and Bus and a Binary relationship between Bus and Route . Can you find out any flaws in this model ? – progammer Mar 27 '12 at 12:56
  • @Appy - Most likely, yes, for depreciation reasons. For a business, buses are considered assets, and as such, have a certain monetary value assigned to them, but they lose their value over time, so the purchase date gives them an age that the accountants would then use to factor how much a given bus is worth (a brand new bus is worth more than a 10 year old bus, for example). – Shauna Mar 27 '12 at 12:57
  • But how will a noob like me know what all to take as attributes . My imagination is very vast and I feel like including many more attributes . The question seems too unclear . Can you guide me ? :) – progammer Mar 27 '12 at 12:59
  • @Appy - It would depend in part on what you're storing in the "Passenger" entity, but in general, I don't see anything wrong with it. It's entirely feasible for a bus company to keep a list of passengers for things like perks (frequent rider miles!), and as such, the "Ticket" entity would become the point of joining between Passenger and Bus (ie - Ticket would have BusID and PassengerID, and Bus and Passenger would not be directly related to each other). – Shauna Mar 27 '12 at 13:00
  • @Appy - The number of attributes you include would depend on your circumstances. Does the attribute meet (or help a different one meet) your requirements? If not, then you probably don't need it. Don't get too hung up on the attributes, you can always add more in later if needed. – Shauna Mar 27 '12 at 13:07
  • Can you comment on my model ? I edited my post . Please have a look at it :) – progammer Mar 27 '12 at 13:13
  • @Appy - Check my edit. – Shauna Mar 27 '12 at 13:27
  • What is meant by this "_follow the rules of the E-R diagram you're building?_" ? – progammer Mar 27 '12 at 13:45
  • There are different ER diagrams (key-based diagram, fully attributed diagram, entity-only, etc). Each one has its own rules (ex - the key-based diagram only has attributes that are primary and/or foreign keys). – Shauna Mar 27 '12 at 14:08