Development of a normalized database for a bus ticket sales system. Passengers travel from different places to different destinations.

Question

I have an interesting problem with the design of the database, which I formulated when traveling by bus, returning from my home.

Create a normalized database for the bus ticket system (not a booking system). On each trip, the bus conductor will issue tickets to his passengers after collecting the fare. Passengers travel from different sources to various destinations.

The system should be able to report on places for which the number of passengers was more than 2.

Suppose a stop for bus L1, L2, L3 and L4 Suppose passenger P1 moves from L1 to L4. P2 moves from L2 to L4. P3 moves from L3 to L4.

Only the list (L3-L4) for which he has more than 2 people should be indicated in the report.

Could you help me solve the following problems.

1) Create a normalized database

2) Write a request for a report

3) Is there a site that provides such interesting questions and answers to questions about database design?

Answer 1

Database Design:

Location Table

 - LocationID  (p)
 - LocationName

TravelTable 

 - TravelID  (p)
 - PassengerID
 - LocationFrom (F) - (Location - LocationID)
 - LocationTo  (F) - (Location - LocationID)

PassengerTable

 - PassengerID (p)
 - PassengerName

Sites where you get the correct answer from:

• https://blog.sqlauthority.com/
• https://www.sqlservercentral.com/

Answer 2

Key Table:

trip (trip_id, passenger_id, start_location_id, end_location_id)

You may have a location table with information about stops.

Then the request will be simple

select start_location_id, end_location_id, count(*)
from trip
group by start_location_id, end_location_id
having count(*)>=2

Edit

, , . , 2 , , 2 , ? , (Al, L1, L2), (Betty, L1, L2), (Carl, L1, L3), (Donna, L2, L4), :

L1, L2, 2

( )?

L1, 3
L2, 3

, . , :

select location, sum(visits)
from
(
select start_location_id as location, count(*) as visits
from trip
union
select end_location_id as location, count(*) as visits
from trip
)
group by location
having sum(visits)>=2
order by location

, .

, , trip_stop:

trip_stop (passenger_id, location_id, stop_number)

stop_number - , , 1 2 .

, , , , , .

select location_id, count(*)
from trip_stop
group by location_id
having count(*)>=2
order by location_id

Answer 3

. , , , . . , x (, ) , .