Draw ER diagram for the given scenario:
Consider a bus ticketing system that records information about the passegner, bus and route. Passenger is assigned to a bus that travels to a route. A bus contains many passegners and a passegner can be assigned into only one bus. Many busses travel in same route but a bus can travel in only one route. The attributes of passegner are pid(unique), gender and phone_no(multi-values). Similarly bus contains regno(unique) and color and route contains rid(unique), distance and rate.
A: Draw an ER Diagram for Bus Ticketing System: 93
ER diagram with steps
For a more detailed explanation of each step, reading the article(Steps to create ER Diagram) is strongly recommended.
Back to top1. Identify the Entities
The question has clearly stated the entities that the bus ticketing system is interested in. The entities are:
- Passenger
- Bus
- Route
2. Relationships
The given relationships are:
- Passenger is 'assigned to' Bus.
- Bus 'travels in' Route.
3. Participation Constraints of the Relationships
Passenger is 'assigned to' Bus.
- A passenger must be assigned to at least one Bus. Hence, the participation of Passenger in the relationship 'assigned to' is Total.
- A Bus may sometimes be empty(without any passengers). Hence, the participation of Bus in the relationship 'assigned to' is Partial.
Bus 'travels in' Route.
- A Bus must travel to at least one Route. Hence, the participation of 'Bus' in the relationship 'travels in' is Total.
- We are assuming, that all Routes must have at least one Bus. Hence, the participation of the 'Route' in the relationship 'travels in' is also Total.
4. Cardinality Constraints of the Relationships
Based on the information given in the question:
- A bus contains many passegners and a passegner can be assigned into only one bus. Hence "Passenger is assigned to a Bus" is a Many-to-One relationship.
- Many busses travel in same route but a bus can travel in only one route. Hence, "Bus travels in Route" is also a Many-to-One relationship.
5. Attributes
The question has stated the attributes very clearly for each entity.
| Entity | Attributes |
|---|---|
| Passenger |
pid |
| Bus | regno color |
| Route | rid distance rate |
Back to top
6. Final ER Diagram
Back to top