CREATE TABLE Officer $($

OfficerNumber INT UNSIGNED AUTO$\_$INCREMENT,

BadgeNumber INT NOT NULL,

DepartmentName VARCHAR$(30)$ NOT NULL,

FullName VARCHAR$(30)$ NOT NULL,

RankCode CHAR$(3),$

PRIMARY KEY $($OfficerNumber$),$

UNIQUE $($BadgeNumber$,$ DepartmentName$)$

$)$;

CREATE TABLE Event$1($

EventNumber INT UNSIGNED AUTO$\_$INCREMENT,

EventDateTime DATETIME NOT NULL,

LocationDesc TEXT$(1000)$ NOT NULL,

ReportDesc TEXT$(5000),$

EventCode CHAR$(2)$ NOT NULL,

CauseEventNumber INT UNSIGNED,

ReportOfficerNumber INT UNSIGNED NOT NULL,

PRIMARY KEY $($EventNumber$),$

FOREIGN KEY $($CauseEventNumber$)$

REFERENCES Event$1($EventNumber$)$

ON UPDATE SET NULL

ON DELETE SET NULL,

FOREIGN KEY $($ReportOfficerNumber$)$

REFERENCES Officer $($OfficerNumber$)$

ON UPDATE RESTRICT

ON DELETE RESTRICT

$)$;

CREATE TABLE Vehicle $($

VehicleID INT UNSIGNED AUTO$\_$INCREMENT,

TypeCode INT UNSIGNED NOT NULL,

YearNumber INT UNSIGNED NOT NULL,

MakeCode INT UNSIGNED NOT NULL,

ModelName VARCHAR$(20)$ NOT NULL,

PlateID INT UNSIGNED NOT NULL,

StateCode INT UNSIGNED NOT NULL,

RegistrationDate DATE,

PRIMARY KEY $($VehicleID$),$

UNIQUE $($PlateID$,$ StateCode$)$

$)$;

CREATE TABLE VehicleEvent $($

VehicleID INT UNSIGNED NOT NULL,

EventNumber INT UNSIGNED AUTO$\_$INCREMENT,

EventCode INT UNSIGNED NOT NULL,

EventDateTime DATETIME NOT NULL,

LocationDesc VARCHAR$(255)$ NOT NULL,

ReportDesc TEXT,

OfficerNumber INT UNSIGNED NOT NULL,

CauseEventNumber INT UNSIGNED,

PRIMARY KEY $($EventNumber$),$

FOREIGN KEY $($VehicleID$)$ REFERENCES Vehicle$($VehicleID$)$ ON DELETE RESTRICT ON UPDATE RESTRICT,

FOREIGN KEY $($OfficerNumber$)$ REFERENCES Officer$($OfficerNumber$)$ ON DELETE RESTRICT ON UPDATE RESTRICT,

FOREIGN KEY $($CauseEventNumber$)$ REFERENCES VehicleEvent$($EventNumber$)$ ON DELETE SET NULL ON UPDATE RESTRICT

$)$;