Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
 Posted - 2010-04-28 : 03:57:07
|
| HiI have an interesting database design problem.We need to design a normalized database for a bus ticketing system. In each trip, the conductor of the bus will give tickets to its passengers after collecting fare from them. Passengers travel from a various source places to various destination places.The system must be able to give a report of the places for which the number of passengers was more than 2.Suppose the stops for the bus are L1,L2, L3 and L4 Suppose passenger P1 travels from L1 to L4. P2 travels from L2 to L4. P3 travels from L3 to L4.The report should list only (L3-L4) for which it has more than 2 travelers.Can you please help me to solve the following problems1) Designing a normalized database2) Writing a query for the reportNote 1:When the following design is used there are limitations(TripID PassID StartLoc EndLoc) (1 P1 L1 L4) (1 P2 L2 L4) (1 P3 L3 L4)When we group by using start location and end location, each one of them will make separate group. No records will be selected. Note 2: We should not be using the order of records in Location table as the order of actual locationThanks Lijo |
|
|
prasannabhogavalli
Starting Member
1 Post |
Posted - 2010-04-28 : 12:58:32
|
| ok, here are the tables : 1. Passengers- PassengerId, PassengerName... Eg P1, P2, P3.... 2. Locations - LocationId, LocatoinName ... Eg L1, L2, L3,...3. Vehicle : VehicleId, VehicleName .... V1, V2 , V33. Route : VehicleId, StopNo, LocationId - This table consists of the route that everyy bus takes. So, in your example, the following records should be inserted for your vehicle (bus): V1 , 1, L1 -- V1 vehicle's 1st stop is in location L1 V1 , 2, L2 -- V1 vehicle's 2nd stop is in location L2 V1 , 3, L3 -- and so on... V4 , 4, L4 As per your example, the design and data of the transaction table will be: Tickets:PassengerId, VehicleId, StartLocation, EndLocation P1 , V1 , 1, 4P2 , V1 , 2, 4P3 , V1 , 3, 4For your report, you can do this: select * from Tickets t inner join Route r on t.VehicleId = r.VehicleId and r.StopNo >= t.StartLocation and r.StopNo <= t.EndLocation From the above query, you will get all the stops that every passenger stopped at. Then you can do a group by on your StopNo. Column. NOTE: This design is making the assumption that every vehicle has only one route. If a single bus can travel in different routes, then you will need a routeId defined in the Route table. Let me know if this helped. Prasanna Bhogavalli |
 |
|
|
|
|
|
|
|
Database Design for Bus Ticket Collection System