FIGURE $8\mathrm{.}42$ E$8\mathrm{.}2$ Consider the following scenario involving the City Police Department. The City Police Department wants to create an analytical database to analyze its ticket revenue. The two available data sources, Source $1$ and Source $2,$ are described below. Source $1$ The City Police Department maintains the Ticketed Violations Database, shown in Figure $8\mathrm{.}43.$ Source $2$ The Department of Motor Vehicles $($DMV$)$ maintains the Vehicle Registration Table, shown in Figure $8\mathrm{.}44.$ The data warehouse has to enable an analysis of ticket revenues by: $$ date, including: $$ full date $$ day of week day of month $$ month $$ quarter year officer, including: officer ID $$ officer name $$ officer rank $.$ payer of the ticket, including: payer DLN payer name payer gender payer birth year vehicle, including: $$ vehicle LPN vehicle make $$ vehicle model $$ vehicle year DTID DTDate DLN Driver Name Officer Officer Name $-04$ DRIVINGTICKETT Issued To DRIVER Issues OFFICER HE DriverGender Driver Birth Year Isor Officer Rank DTViolation PTTypeID Issued for DTFee DTICKETTYPE PTViolation offe PTICKETTYPE DTTypeID Issues PTFee VehicleMake Isor $+$ VEHICLE Issued for Vehicle PN Vehicle Model PARKINGTICKET PTID PTDate OFFICER OfficerID Officer Name Officer Rank DRIVINGTICKET DTID DTDate OfficeriD $($FK$)$ DLN $($FK$)$ VehicleLPN $($FK$)$ DTTypeID $($FK$)$ DRIVER DLN Driver Name Driver Gender Driver BirthYear DTICKETTYPE DTTypeID DTViolation DTF PARKINGTICKET PTID PTDate OfficerID $($FK$)$ VehicleLPN $($FK$)$ PTTypeID $($FK$)$ VEHICLE VehiclePN VehicleMake Vehicle Model PTICKETTYPE PTTypeID PTViolation PTFee OFFICER Officer D OfficerName Officer Rank $1$ Joe Sergeant $2$ Mike Patrolman $3$ Bob Patrolman DRIVER DLN Driver Name DiverGender Diver BirthYear $111$ BII Male $1988222$ Suzy Female $1977$ VEHICLE VehicleLPN VehicleMake VehicleModel IL$1111$ Honda CRV IL$2222$ Honda Civic IL $3333$ Honda Civic DRIVINGTICKET DTID DTDate Officer D DLN VehicleLPN DTTypeID DT$11111-$Jan$-20131111111111$ D$1$ DT$22222$ Jan $20132222$ L$2222$ D$1$ DT$33332$ Jan $2013311111111$ D$2$ DTICKETTYPE DTTypeID DTViolation DTFree D$1$ Red Light $$200$ D$2$ Seat Belt $$100$ PTICKETTYPE PTTypeID PTViolation DTFee P$1$ Meter Expired $$100$ P$2$ Hydrant $$150$ PARKINGTICKET PTID PTDate Officerid VehicleLPN PTTypeID PT$11111-$Jan$-20131$ IL$1111$ P$1$ PT$22221-$Jan$-20132$ IL$2222$ P$2$ PT$33332$ Jan $20133$ IL$3333$ P$2$ FIGURE $8\mathrm{.}43$ Source $1$: The City Police Department Ticketed Violations Database. VEHICLE REGISTRATION TABLE VehicleLPN VehicleMake VehicleModel Vehicle Year OwnerDLN OwnerName OwnerGender OwnerBirth Year IL$1111$ Honda CRV $2007111$ BAI Male $1988$ IL$2222$ Honda Civic $2012222$ Suzy Female $1977$ IL $3333$ Honda Civic $2005333$ David Male $1966$ FIGURE $8\mathrm{.}44$ Source $2$: The DMV Vehicle Registration Table. vehicle owner DLN $$ vehicle owner name $$ vehicle owner gender vehicle owner birth year $.$ ticket type, including: $.$ticket category $($driving or parking$)$ ticket violation $.$ticket fee Figure $8\mathrm{.}45$ illustrates how a ticket revenue analysis data warehouse based on these sources and requirements would be designed if dimensional modeling techniques were used. E$8\mathrm{.}2$a Using source data from Figures $8\mathrm{.}43$ and $8\mathrm{.}44,$ illustrate how the empty tables in Figure $8\mathrm{.}46$ would be populated. CALENDAR Dimension Calendarkey FullDate DayOfWeek DayOfMonth Month Quarter Year PAYER Dimension Payerkey PayerDLN PayerName PayerGender PayerBirthYear OFFICER Dimension OfficerKey OfficerID OfficerName OfficerRank TICKETREVENUE Fact table Calendarkey $($FK$)$ Officerkey $($FK$)$ Payerkey $($FK$)$ Vehiclekey $($FK$)$ TicketTypeKey $($FK$)$ TicketID Amount TICKETTYPE Dimension TicketTypeKey TicketCategory Ticket Violation TicketFee VEHICLE Dimension Vehiclekey VehicleLPN VehicleMake VehicleModel Vehicle Year VehicleOwnerDLN VehicleOwnerName VehicleOwnerGender VehicleOwnerBirthYear FIGURE $8\mathrm{.}45$ A dimensionally modeled data warehouse with the subject ticket revenue. CALENDAR Dimension Calendarkey FullDate Dayor Dayor Month at Year Week Month PAYER Dimension PayerKey PayerDLN PayerName Payer Gender Payer Birth Year OFFICER Dimension Officer Key OfficeriD Officer Officer Name Rank VEHICLE Dimension Vehicle Key Vehicle Vehicle Vehicle Vehicle Vehicle LPN Make Model Year OwnerDLN Vehicle Vehicle Owner Owner Name Gender Vehicle Owner Birth Year TICKETTYPE Dimension Ticket Ticket TypeKey ategory TICKETREVENUE Fact table Calendar Key Officer Key Payer Key Ticket atior Ticket Fee VehicleKey Ticket TypeKey Ticket ID Amount FIGURE $8\mathrm{.}46$ E$8\mathrm{.}2$b Create a dimensional model containing an aggregated fact table, where a fact table shows a summary of daily revenue amount for each officer. E$8\mathrm{.}2$c Populate the tables created in E$8\mathrm{.}2$b with the data. Use the data that you used to populate the tables in the Figure $8\mathrm{.}46$ as a basis for aggregation.