Database Systems in SQL

The Pomona Transit System is planning to develop software that maintains information about the monthly schedules, buses, drivers, trips (bus lines), and bus stops. Also, the software must keep track of the total number of passengers of each trip. Use ERWIN or Visio to generate an ER diagram of this software, and then show the database design. Your answer must be based on the following assumptions

The drivers information includes name, address, and

telephone #, SSN, and driver's licenses #.

The buss information includes bus#, plate#, made, model,

and year.

The bus stops information includes bus stop # and bus stop

address.

The trips information includes trip# (line number), starting

location, and final destination. The last two attributes are usually

names of two specific areas, for example from Diamond Bar to

9th street.

Each trip is related to a fixed sequence of at least 5 bus stops; and it is

possible to have shared bus stops between two or more trips. For each

trip, the driving time between any two consecutive bus stops is fixed.

Your ER diagram must model the sequencing of bus stops and the

driving times.

The Pomona Transit System prints monthly schedule which includes

date, starting time, trip#, driver's name, and arriving time for each bus

stop.

The Pomona Transit System prints monthly statistical report

which includes date, starting time, trip#, driver's name,

arriving time, and total number of passengers.

2. Based on your ER diagram, show the design of tables.