Question : Identify areas where an inheritance structure could be used and create a ER model. Create a version of ER model with the inheritance structures incorporated

In Allegheny County, where Duquesne U. is located, one can take rides on buses and the subway (known as the T) to get around the city without a vehicle. The buses and subway are operated by the PAAC of Allegheny County (PAAC). PAAC currently uses a third-party vendor to manage their payment cards (Connect Cards) and payment system, but is exploring migrating the data in house so it can be used for analysis and decision support purposes. For example, PAAC would like to be able to look at trends in certain bus and subway stops, frequency of usage during specific time periods, and frequency of usage of the Connect Card kiosks. Thus, they would like to have a database to store the relevant information described below.

PAAC converted to a cashless system and now requires riders to pay using Connect Cards. In order to pay for the rides, customers can acquire Connect Cards from the PAAC and use them repeatedly by adding value and making payments with the added value. Each card has an assigned number associated with it and can have either an active or inactive status. Riders can register their Connect Cards by contacting the PAAC, which store the riders name, email address, and phone number in association with their Connect Card. The date the rider registered is also recorded, and if a rider calls to update the information, the date of the update is also stored. Registration is optional, and some customers have multiple cards for convenience.

Each time a customer uses the card, a transaction is recorded, which includes the transaction amount and the date and time of the transaction. Each transaction is either the addition of value to the card or a ride payment ($2.50 currently). For transactions that add value, the kiosk where the transaction was conducted needs to be stored as well as the last four digits of the debit/credit card number and the expiration date of the debit/credit card used for payment. For transactions that are ride payments, the route associated with the ride must be stored. Each route has a unique letter-number combination associated with it (e.g., 51C or 71D), a route name, and a date the route was added to the transportation system.

Additional Information: Each route can either be a subway route or a bus route. If it is a subway route, it is stored whether the route goes underground. If it is a bus route, the total mileage of the route is stored. Along each route, there are a number of stops, and each stop can be utilized by multiple routes (e.g., some stops have both buses and subways that deposit/pick-up riders at the stop). The latitudinal and longitudinal coordinates of each stop are stored along with the nearest cross streets (one being a north/side street and one being an east/west street). Some stops have parking areas. For these stops only, the number of handicap accessible parking spaces and the total number of parking spaces are stored. If a ride payment using a Connect Card is stored, the stop the rider made the payment at should also be stored along side the information mentioned above.