Question
You are hired by the MBTAs Commuter Rail division to use SQL to query their asset management system. The system manages the locomotives and cars,
You are hired by the MBTAs Commuter Rail division to use SQL to query their asset management system. The system manages the locomotives and cars, which are put together into trains. The system also manages the maintenance of locomotives and cars at depots. The details of the asset management system are described below:
Each train is pulled by one or more locomotives and has one or more cars. Train numbers are unique.
Each locomotive has a unique number and can either be electric or diesel.
Each car has a unique number and can either be single-level or double-deck.
A locomotive or car will usually be assigned to multiple trains, since trips are short.
Cars and locomotives are assigned to the same trains every day.
Locomotives and cars are serviced at depots. There are multiple depots.
Each depot has a unique number and can serve one or more locomotives/cars.
Each locomotive is serviced at one depot.
Each car is serviced at one depot.
You are given the following entity-relationship diagram for the system described above, which specifies the tables, columns and relationships of the database:
You are now asked to write the following three SQL queries against the database. Briefly define any variable you use that isnt obvious. Using MySql
a) List the train number and number of cars assigned to each train, showing the trains with the most cars first.
b) List the train number and number of cars assigned to each train, showing the trains with the most cars first.
c) List the locomotive numbers, and number of assigned trains, of locomotives serviced at depot 22 that are assigned to more than 3 trains.
ra in TrainNbr Train Locomotive Locomotive Type LocomotiveType ve Train TrainNbr LocomotiveLocomotiveNbr LocomotiveNbr Locomotive TypeLocomotiveType DepotDepotNbr Depot DepotNbr Train Car Car Train TrainNbr CarCarNbr TCarNbr -|-| f CarType Car TypoCar Type DepotDepotNbrStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started