using this script

CREATE TABLE PROBLEM$3$

$($ LASTNAME VARCHAR$(75),$

CASENUMBER VARCHAR$(30),$

FIRSTNAME VARCHAR$(75),$

CITIZENID numeric$(5,0),$

OFFICERLASTNAME VARCHAR$(75),$

INVOLVEMENT VARCHAR$(20),$

AGENCYNAME VARCHAR$(75),$

OFFICERFIRSTNAME VARCHAR$(75),$

SEIZUREDATE DATE,

ACTIVITY VARCHAR$(50),$

OFFICERID numeric$(5,0),$

AGENCYTYPE VARCHAR$(100),$

DATEOFBIRTH DATE

$)$ ;

Problem $3$

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

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

business rules apply:

$1.$ A seizure is identified by a case number. In reporting a seizure, law

enforcement officers record the date of the seizure and the activity they

were involved with when making the seizure. They also record the

citizens who were involved in some fashion in the seizure, the nature of

each citizen$$s involvement, and the officers who carried out the seizure.

$2.$ For each citizen, the name and date of birth of the individual are recorded.

$3.$ Multiple citizens may be involved with a single seizure. An individual

citizen may be involved with different seizures.

$4.$ Similarly, multiple officers may be involved with a single seizure.

However, there is no stored connection between an officer and a citizen

other than that they were both involved in the same seizure. That is$,$ there

is no need to record which officer arrested which citizen, for example.

$5.$ For each seizure, the agency responsible for overseeing the seizure is

recorded. Each agency has a unique name and a particular type. The

affiliation of an officer with an agency is not recorded.

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

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

appropriate name for the table..