2. Consider the following task report for Speedy Lawn Care Service. You are not allowed to add any new column to the relation. You should identify the primary key and list all functional dependencies. Specify the relation(s) schema with the primary key attribute(s) underlined for 1NF, 2NF & 3NF.

Here are the three tasks for this exercise:

a. Convert the original report to 1NF relation(s).

i. State any assumptions you may make

ii. Explain your rationale

iii. Specify the relation(s) schema with the primary key attribute(s) underlined

b. Normalize your 1NF schema to 2nd normal form (2NF).

i. Indicate which FD is eliminated when you decompose a relation

ii. State any assumptions you may make

iii. Specify the relation schema of each relation with the primary key attribute(s) underlined.

c. Normalize your 2NF schema to 3rd normal form (3NF).

i. Indicate which FD is eliminated when you decompose a relation

ii. State any assumptions you may make

iii. Specify the relation schema of each relation with the primary key attribute(s) underlined.i. State assumptions: Lawn care professional. Client Services. ii. Remove Repeating Groups (multivalued attributes) (Attached) iii. Specify the relation(s) schema with the primary key attribute(s) underlined ClientService(Client_Name, Client_Add, Service_Ty, Cost, Tech_Name, Vehicle_Ident.#) 1NF b. Normalize your 1NF schema to 2nd normal form (2NF). i. Indicate which FD is eliminated when you decompose a relation - Identifying Partial Dependencies: a FD on part of the primary key. - Client_Name -> Client_Add ii. State any assumptions you may make Landscaper(Client_Name, Client_Add) iii. Specify the relation schema of each relation with the primary key attribute(s) underlined. ClientService(Client_Name, Service_Ty, Cost, Tech_Name, Vehicle_Ident.#) 2NF c. Normalize your 2NF schema to 3rd normal form (3NF). i. Indicate which FD is eliminated when you decompose a relation Identify all Transitive Functional Dependencies: FD between two non-key attributes ii. State any assumptions you may make Service_Ty -> Cost Tech_Name -> Vehicle_Ident.# iii. Specify the relation schema of each relation with the primary key attribute(s) underlined. Remove TFDs by splitting relation Landscaper(Client_Name, Client_Add) - 3NF Services(Service_Ty -> Cost) - 3NF Technician(Tech_Name -> Vehicle_Ident.#) - 3NF ClientService(Client_Name, Tech_Name, Service_Ty) - 3NF

CORRECTION: the service type is not the only factor to determine the cost, for example the mowing cost is different based on the different client if the lot size is different.

\begin{tabular}{|l|l|l|r|l|l|} \hline \multicolumn{1}{|c|}{ Client Name } & ClientAddress & \multicolumn{1}{|c|}{ServiceType} & \multicolumn{1}{c|}{ Cost (\$) } & \multicolumn{1}{|c|}{TechnicianName} & VehicleIdent.# \\ \hline Kim Jones & Worthington & Mowing & 25 & Carlos & L2 \\ \hline John Smith & Dublin & Mowing & 75 & Fred & L1 \\ \hline & & Aeration & 120 & Louie & LT1 \\ \hline Barbara Wentz & Worthington & Mowing & 50 & Carlos & L2 \\ \hline Charles Lee & Hilliard & Mowing & 60 & Louie & LT1 \\ \hline Heinz Schmidt & Powell & Aeration & 255 & Louie & LT1 \\ \hline Carrie Fisher & Columbus & Fertilizing & 45 & Gina & T25 \\ \hline \end{tabular}