Step1: Use Access to create the operational database (E8.2) City Police Department and input sample data as shown in Figure 8.39 (1^st Edition 8.43) and Figure 8.40 (1^st Edition, 8.44).

Step 2: Use SQL commands to create the data warehouse table structures (without data) based on the star schema as shown in Figure 8.41 (1^st Edition: 8.45) and Figure 8.42 (1^st Edition 8.46).

Step 3: Use SQL statements to populate the data warehouse tables.

Step 4: Use SQL commands to:

8.39

8.40

8.41

8.42

Officed Office Name DTID DTDate DLN DriverName OFFICER Issues DRIVING TICKET Issued to DRIVER Officer Rank DriverGender (Oriver BirthYear Isof DTViolation DTFee DTICKET TYPE PTTypeID Issued for PTViolation DTTypeID PTICKETTYPE Issues Isor PTFee VehicleMake PARKING TICKET IssuedFor VEHICLE PTID PTDate VehicleLPN VehicleModel DRIVINGTICKET OFFICER DTID DTDate Officer ID OfficeriD (FO DRIVER Officerfame DLN (FK) DLN OfficerRank Vehicle PN PRO Driver Name DTTypeID IKI DriverGender Driver BirthYear DTICKETTYPE DTTypeID DTViolation DTFee PARKINGTICKET PTID PTDate OfficeriD (FK) VEHICLE VehicleLPN (FK) VehicleLPN PTTypeID (FK) VehicleMake Vehicle Model PTICKETTYPE PTTypeID DTViolation OFFICER PTF Officer ID Officer Name officer Rank PTICKETTYPE DTICKETTYPE 1 Joe Sergeant PTTypeID PTViolation PTF DTTypeID DTViolation DTFee 2 Mike Patrolman P1 Meter Expired $100 01 Red Light $200 3 Bob Patrolman P2 Hydrant $150 D2 Seat Belt $100 DRIVINGTICKET VEHICLE DTID DTDate OfficerID DLN Vehicle PN DTTypeID VehicleLPN VehicleMake Vehicle Model DT1111 1-Jan-20201 111 IL1111 DI IL1111 Honda CRV DT2222 2-Jan-20202 222 1L2222 D: 1L2222 Honda DT3333 2-Jan-20203 111 IL1111 D2 IL3333 Honda Civic Clic PARKINGTICKET PTID PTData Officer ID VehicleLPN PTTypeID PT1111 1-Jan-20201 IL1111 P1 PT2222 1-Jan-20202 TL2222 P2 PT3333 2-Jan-2020 3 IL3333 P2 DRIVER DLN Driver Name Diver Gender Diver BirthYear 111 Bill Male 1998 222 Suzy Female 1987 VEHICLE REGISTRATION TABLE VehicleLPN Vehicle Vehicle Make Model IL1111 Honda CRV IL2222 Honda Civic Vehicle Year Owner DLN Owner Gender Owner Birth Year 1998 2017 Owner Name Bill Suzy David 111 Male 2020 222 Female 1987 IL 3333 Honda Civic 2018 333 Male 1976 CALENDAR Dimension Calendarkey FullDate DayOfWeek DayOfMonth Month Quarter Year PAYER Dimension PayerKey PayerDLN PayerName PayerGender PayerBirth Year OFFICER Dimension Officer Key OfficerID OfficerName Officer Rank TICKETREVENUE Fact table Calendarkey (FK) Officer Key (FK) PayerKey (FK) VehicleKey (FK) TicketTypeKey (FK) TicketID Amount TICKETTYPE Dimension TicketTypeKey TicketCategory TicketViolation TicketFee VEHICLE Dimension Vehiclekey VehicleLPN VehicleMake Vehicle Model Vehicle Year VehicleOwnerDLN Vehicle OwnerName VehicleOwnerGender VehicleOwnerBirth Year CALENDAR Dimension Calendarkey FullDate PAYER Dimension PayerKey PayerDLN Month Qtr Dayof Dayof Week Month Year Payer Payer Name Gender Payer Birth Year OFFICER Dimension officerkey officerID officer officer Name Rank TICKETREVENUE Fact table Calendar Officer Payer Vehicle Key Key Key Key TicketType Key TicketID Amount TICKETTYPE Dimension TicketTypeKey Ticket Ticket Ticket Category Violation Fee VEHICLE Dimension Vehiclekey Vehicle Vehicle LPN Make Vehicle Model Vehicle Year Vehicle Vehicle Owner Owner DLN Name Vehicle Owner Gender Vehicle Owner Birth Year Officed Office Name DTID DTDate DLN DriverName OFFICER Issues DRIVING TICKET Issued to DRIVER Officer Rank DriverGender (Oriver BirthYear Isof DTViolation DTFee DTICKET TYPE PTTypeID Issued for PTViolation DTTypeID PTICKETTYPE Issues Isor PTFee VehicleMake PARKING TICKET IssuedFor VEHICLE PTID PTDate VehicleLPN VehicleModel DRIVINGTICKET OFFICER DTID DTDate Officer ID OfficeriD (FO DRIVER Officerfame DLN (FK) DLN OfficerRank Vehicle PN PRO Driver Name DTTypeID IKI DriverGender Driver BirthYear DTICKETTYPE DTTypeID DTViolation DTFee PARKINGTICKET PTID PTDate OfficeriD (FK) VEHICLE VehicleLPN (FK) VehicleLPN PTTypeID (FK) VehicleMake Vehicle Model PTICKETTYPE PTTypeID DTViolation OFFICER PTF Officer ID Officer Name officer Rank PTICKETTYPE DTICKETTYPE 1 Joe Sergeant PTTypeID PTViolation PTF DTTypeID DTViolation DTFee 2 Mike Patrolman P1 Meter Expired $100 01 Red Light $200 3 Bob Patrolman P2 Hydrant $150 D2 Seat Belt $100 DRIVINGTICKET VEHICLE DTID DTDate OfficerID DLN Vehicle PN DTTypeID VehicleLPN VehicleMake Vehicle Model DT1111 1-Jan-20201 111 IL1111 DI IL1111 Honda CRV DT2222 2-Jan-20202 222 1L2222 D: 1L2222 Honda DT3333 2-Jan-20203 111 IL1111 D2 IL3333 Honda Civic Clic PARKINGTICKET PTID PTData Officer ID VehicleLPN PTTypeID PT1111 1-Jan-20201 IL1111 P1 PT2222 1-Jan-20202 TL2222 P2 PT3333 2-Jan-2020 3 IL3333 P2 DRIVER DLN Driver Name Diver Gender Diver BirthYear 111 Bill Male 1998 222 Suzy Female 1987 VEHICLE REGISTRATION TABLE VehicleLPN Vehicle Vehicle Make Model IL1111 Honda CRV IL2222 Honda Civic Vehicle Year Owner DLN Owner Gender Owner Birth Year 1998 2017 Owner Name Bill Suzy David 111 Male 2020 222 Female 1987 IL 3333 Honda Civic 2018 333 Male 1976 CALENDAR Dimension Calendarkey FullDate DayOfWeek DayOfMonth Month Quarter Year PAYER Dimension PayerKey PayerDLN PayerName PayerGender PayerBirth Year OFFICER Dimension Officer Key OfficerID OfficerName Officer Rank TICKETREVENUE Fact table Calendarkey (FK) Officer Key (FK) PayerKey (FK) VehicleKey (FK) TicketTypeKey (FK) TicketID Amount TICKETTYPE Dimension TicketTypeKey TicketCategory TicketViolation TicketFee VEHICLE Dimension Vehiclekey VehicleLPN VehicleMake Vehicle Model Vehicle Year VehicleOwnerDLN Vehicle OwnerName VehicleOwnerGender VehicleOwnerBirth Year CALENDAR Dimension Calendarkey FullDate PAYER Dimension PayerKey PayerDLN Month Qtr Dayof Dayof Week Month Year Payer Payer Name Gender Payer Birth Year OFFICER Dimension officerkey officerID officer officer Name Rank TICKETREVENUE Fact table Calendar Officer Payer Vehicle Key Key Key Key TicketType Key TicketID Amount TICKETTYPE Dimension TicketTypeKey Ticket Ticket Ticket Category Violation Fee VEHICLE Dimension Vehiclekey Vehicle Vehicle LPN Make Vehicle Model Vehicle Year Vehicle Vehicle Owner Owner DLN Name Vehicle Owner Gender Vehicle Owner Birth Year