Problem $2$

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

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 material collected is known as a collection. Each collection is given a

unique case number. The date and location on which the collection was

made is recorded. Each collection is also seized by a single law

enforcement agency.

$2.$ A law enforcement agency has a unique name and is one of several

types: policy department $($PD$),$ sheriff$$s office $($SO$),$ etc.

$3.$ From the collection, one or more exhibits are taken. An exhibit is a portion

of a collection. It is given a unique number. Law enforcement records an

estimate of the amount of material that makes up an exhibit. A description

of each exhibit is also recorded.

$4.$ An exhibit is sent to a laboratory for analysis. The laboratory divides the

exhibit up into samples for further testing. The lab gives each sample a

unique identifier $($a lab number$),$ runs the tests, and records the analysis

date and analysis results for the sample.

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$2$ table

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

appropriate name for the table.

CREATE TABLE PROBLEM$2$

$($ LABNUMBER VARCHAR$(10),$

TYPE VARCHAR$(100),$

DESCRIPTION VARCHAR$(255),$

LOCATION VARCHAR$(100),$

ESTIMATEDAMOUNT numeric$(5,1),$

EXHIBITUNITS VARCHAR$(50),$

ANALYSISDATE DATE,

RESULTS VARCHAR$(255),$

EXHIBITNO VARCHAR$(10),$

COLLECTIONDATE DATE,

CASENUMBER VARCHAR$(30),$

AGENCY VARCHAR$(75)$

$)$ ;