Question

You are in charge of keeping track of Rutgers bus system. You operate a number of different kind of bus, for example, “LX” or “REXL.” For each kind of bus, you want to keep track of the operating hours and days of operations (weekday or weekend). For example, “A” bus operates from 7:00 AM until 9:30 PM during the weekdays and “H” bus operates from 6:00 AM until 2:30 AM during the weekdays. For each bus, you want to store bus ID, date acquired, current mileage, and maximum mileage. Once current mileage reaches the maximum mileage, the bus must be retired. Each kind of bus uses multiple buses, but, for simplicity, assume one bus can be used in exactly one kind of bus. If a bus B12255039 is “LX” bus, then the bus will be “LX” until it reaches the maximum mileage.

Also, you have access to a table of stations. For each station, you want to store station ID, name, and the description of location.

The event of a bus leaving a station by a driver is called a dispatch. Every time a bus dispatches, you want to record which bus was dispatched, who was the driver, the date/time and the station the dispatch started, the date/time the dispatch ended. Each driver has a unique employee ID, along with a first name, last name, middle name, date of birth, cell phone number, and date of last safety training.

In addition, you want to keep track of which kind of bus covers which stations. The following database is the result of the problem description above.
BUS DateAcquired Current Mile MadMille Route ROUTE DISPATCH DRIVER Y RouteID Operating Hours Operating Days Dispatchi Begins Ends Emplo BusID Stationis Emplo First Name Last Name Middle Name BirthDate CellPhone Last Training COVERS 1 Routelo 1 Stationis STATION 1 Stationis Station Name StationDesc ROUTE X Field Name RoutelD Operating Hours OperatingDays Data Type Short Text Short Text Short Text

Answer 1

1.

Select FirstName, LastName, CellPhone from Driver;

2.

Select FirstName, LastName , BirthDate from Driver inner join Dispatch on Driver.EmpID = Dispatch.EmpID where Year(BirthDate) = 1960 and FirstName = 'Peter' or FirstName = 'Mary';

3.

Select BusID, CurrentMile from Bus where Year(DateAcquired) = 2014 order by DateAcquired, CurrentMile;

4.

Select Route.RouteID, StationName from Route, Covers, Station where Route.RouteID = Covers.RouteID and Covers.StationID = Station.StationID where Route.RouteID = 'LX';

5.

Select Route.RouteID, Count(Station.StationID) from Route, Covers, Station where Route.RouteID = Covers.RouteID and Covers.StationID = Station.StationID group by Route.RouteID ;

6.

Select Count((MaxMile - CurrentMile)) *100000 as TotalCostFor New Buses  from Bus where (MaxMile - CurrentMile) < 10000;

7.

Select Route.RouteID, Count(Driver.EmpID) as NumDriver from Route, Bus,Dispatch,Driver where Route.RouteID = Bus.RouteID and Bus.BusID = Dispatch.BusID and Driver.EmpID = Dispatch.EmpID and Route.RouteID = 'LX' and Month(Begins) = 2 and Year(Begins) = 2020 order by NumDriver desc;

8.

Select FirstName, LastName , Count(Dispatch.DispatchID) as NumOfDispatches from Driver inner join Dispatch on Driver.EmpID = Dispatch.EmpID where Begins = 'January, 21, 2020' and Ends = 'March ,13 ,2020' order by NumOfDispatches desc, LastName;

Do ask if any doubt. Please up-vote.