Answer Happy

You are member of a data science team which wants to analyse the Sydney public transport data. This dataset contains millions of tap-on / tap-off events where public transport users were swiping on and off from trains, buses and ferries with their Opal Card (the public transport card in Sydney). The format is: CardEvents (id: integer, day: date, card: integer, mode: text, tap_on: timestamp, tap_off: timestamp) Each trip on the Sydney public transport network is recorded in this table with corresponding tap on and tap_off timestamps as well as the day when the trip started, the card number used, and which mode of transport ('bus', 'lightrail', 'train' or 'ferry'). A common query on the above relations is: (from and until are values given by the data scientist) SELECT COUNT(*) FROM CardEvents WHERE mode = 'train' AND tap on BETWEEN :from AND :until; Your DBMS supports hash and B+-tree secondary indexes, and indexes can be clustered or unclustered.

Consider again the following relation about public transport usage data of users tapping on/off with their Opal cards: CardEvents (id: integer, day: date, card: integer, mode: text, tap_on: timestamp, tap_off: timestamp) This table contains a record for each trip on the Sydney public transport network with corresponding tap on and tap_off timestamps as well as the day when the trip started, the card number used, and which mode of transport was used ('bus', 'lightrail', 'train' or 'ferry'). For this scenario, give 1. an example of an SQL SELECT query, and 2. a corresponding CREATE INDEX statement so that your query will be executed with an index-only execution plan using your index. Edit View Insert Format Tools Table 12pt ✓ Paragraph I B U Av Q✓ T²V P To √x ! D₂ !!!! 189