Consider the following database schema used to track vehicular incidents. The database has been in use for several years and contains millions of entries. Primary keys are underlined and foreign keys carry the same names as the primary keys they reference.

Individuals(IndID, Name, Address, City, State, Zip, DOB) Vehicles (Vin, Lic#, LicState, IndID) RoleCodes(RoleCode, Description) Officers(Badge#,Jurisdiction, Name, Rank, Precinct, Status, RoleCode) Events(EventID, EventDate, EventTime, EventLocation, EventDescription, WeatherCond) IndividualsAtEvent(IndID, EventID, RoleCode) OfficersAtEvent (Badge#, Jurisdiction, EventID) VehiclesAtEvent (Vin, EventID)

Implement the database change request shown at the end of this prompt by using a sequence of appropriate refactoring pattern(s) (from the book) and describing the process for each used refactoring as follows:

1) name of the refactoring chosen 2) motivation: why did you chose it for this database? 3) schema update & data migration mechanics: no need to include triggers but do include SQL commands needed to achieve the desired objective and 4) the resulting schema

Follow the format used in the example we did in class for each refactoring pattern you use but tailor your description to the specific database schema given in this assignment. Please note that the refactoring request will require the application of a sequence of refactoring pattern(s). You are expected to discuss the refactoring pattern(s) required to achieve the request in the proper order. Show the affected relations in the schema after each refactoring.

Here is the change request:

The EventID primary key (of relation Events) is determined to be too difficult to remember and search by, so a composite key consisting of the date and time of the event in addition to the location is to replace it. The EventID should then be dropped from the database altogether (i.e. anywhere it occurs not just from relation Events).

Hint #1: The following Website provides an excellent resource for data refactorings: http://www.agiledata.org/essays/databaseRefactoringCatalog.html

Hint #2: Before the EventID can be dropped you need to make sure that all relations containing EventID also contain the proposed composite key. Relation Events already contains the proposed composite key but not relations IndividualsAtEvent, OfficersAtEvent, and VehiclesAtEvent. Thus, considering starting out with the Add Column refactoring to add each column in the proposed into relations IndividualsAtEvent,OfficersAtEvent,andVehiclesAtEvent afterwhich the Insert Data refactoring may be utilized to populate data into the newly added columns from columns {EventDate, EventTime, EventLocation} in relation Events. These two refactorings are just the start; youll need to use plenty more refactorings to fulfil the above request.