$11\mathrm{.}7$ LAB $-$ Create Vehicle and EventVehicle tables $($Case study$)$

LAB ACTIVITY

$11\mathrm{.}7\mathrm{.}1$: LAB $-$ Create Vehicle and EventVehicle tables $($Case study$)$

$4/10$

Refer to the completed table diagram in the zyBook case study. This lab implements four tables of the diagram:

A table diagram has tables Vehicle, Event, Officer, and VehicleEvent with columns as follows: Columns in the Vehicle table: VehicleID $($primary key$),$ TypeCode R$,$ YearNumber R$,$ MakeCode R$,$ ModelName R$,$ PlateID R$,$ StateCode R$,$ and RegistrationDate The composite column $($PlateID$,$ StateCode$)$ is unique. Columns in the Event table: EventNumber $($primary key$),$ EventDateTime R$,$ LocationDesc R$,$ ReportDesc, EventCode R$,$ CauseEventNumber, and ReportOfficerNumber R$.$ Columns in the Officer table: OfficerNumber $($primary key$),$ BadgeNumber R$,$ DepartmentCode R$,$ FullName R$,$ and RankCode. Columns in the VehicleEvent table: VehicleID and EventNumber. The primary key is $($Vehicle$,$ EventNumber$).$ The diagram has four arrows representing foreign keys: In the Event table, CauseEventNumber references the Event table and ReportOfficerNumber references the Officer table. In the VehicleEvent table, VehicleID references the Vehicle table and EventNumber references the Event table. All foreign key rules are restrict.

Do not create the Officer and Event tables. These tables are created, as examples, in Initialize.sql$.$

Create the Vehicle and VehicleEvent tables. Implement all diagram constraints, including keys and cardinality, in SQL$.$ Specify data types based on the column name suffix:

ID $-$ fixed length, $8$ characters

Number $-$ unsigned integer

Code $-$ fixed length, $3$ characters

Name $-$ variable length, max $30$ characters

Date $-$ date

To confirm that all four tables have been created, add the SHOW TABLES; statement.