Problem $1$

a$.$ In MySQL$,$ you will find a table called PROBLEM$1.$

b$.$ Draw a functional dependency diagram for the table shown below. The following

business rules apply:

$1.$ Law enforcement authorities periodically seize material suspected of being

drugs during traffic stops, or arrests at places of resident. When they do$,$

the seizure is given a unique case number. The date on which the seizure

was made is recorded.

$2.$ The seized material may be sent to laboratories for analysis. Material from

one seizure may be sent to multiple labs and a given lab can receive

material from multiple seizures. The date on which a lab receives a

portion of the seized material is recorded, as is the individual who received

the material at the lab.

$3.$ The city in which a seizure was made is also stored. Each city is given a

unique identifier and is located in a particular county.

$-2-$

$4.$ Each lab is located in a city.

$5.$ During a seizure, law enforcement also records the activity engaged in at

the time of the seizure and the number of firearms seized.

c$.$ State the highest normal form this table could be in in its present form $($i$.$e$.$ if the

table shown here satisfies $1$NF but does not satisfy $2$NF$,$ then the answer would

be $1$NF$).$

d$.$ Normalize this table through BCNF$.$ Decompose the Problem$1$ table

appropriately. Name each of the resulting tables P$1\_$X$,$ where X $=$ some

appropriate name for the table.