Question
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
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}Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started