Question

Scenario: A builder needs a database to keep track of contractors he hires for various projects. So far, we have this 2NF relation, with sample data shown. Normalize to 3NF.

CONTRACTOR: # ConID, Lname, Fname, JobTitle, Company, Street, City, State, Zip, CompanyPhone, CellPhone

ConID	Lname	Fname	JobTitle	Company	Street	City	State	Zip	Phone	CellPhone
2	Garcia	Mary	Carpenter	Construct Co	123 Main	Portland	OR	97204	823-1234	645-5423
14	Jones	Tomas	Welder	Construct Co	123 Main	Portland	OR	97204	823-1234	344-3475
21	Smith	Joey	Welder	Construct Co	123 Main	Portland	OR	97204	823-1234	676-0495
8	Tanaka	Bill	Plumber	Water Inc	456 21st St	Portland	OR	97219	722-0045	805-7602
9	Alredd	Pete	PipeFitter	Water Inc	456 21st St	Portland	OR	97219	722-0045	341-1243

Explain your reasoning here (not what you did but why you did it). What does 3NF mean? How did you apply it?

Your 3NF relation or relations go here.

Answer 1

Solution :-

A Relational Schema, R, is in 3NF if it is in 2NF and no non-prime attribute of r is transitively dependent on the primary key. A Relational Schema , R , is 3NF if whenever a functional dependency X\rightarrow A holds in R, either:

X is the super key of R,or
A is a prime attribue of R.
A functional dependency X\rightarrow Y in a relational schema, R , is a transitive dependency if there is a set of attributes Z that is not a subset of any key of R and both X\rightarrowZ and Z\rightarrow Y holds.

In the above CONTRACTOR relation,

ConID is the primary key of the relation
ConID \rightarrow Lname,Fname,JobTitle , Company ,CellPhone
Company \rightarrow Street,City,State,Zip,Phone
Street,City,State,Zip,Phone are transitively dependent on ConID.

To remove the transitive dependency and convert the Schema into 3NF, we remove the attributes Company,Street,City,State,Zip,Phone from Contractor table and create a new table say CompanyDetails with attributes Company, Street,City,State,Zip,Phone where Company is the primary key of CompanyDetails and it is the foreign key in Contractor table.

After 3NF:

Contractor : ConID,Lname,Fname,JobTitle , Company ,CellPhone ; where ConID is the primary key of Contractor. Company is the foreign key of CompanyDetails

CompanyDetails : Company, Street,City,State,Zip,Phone ; where Company is the primary key of CompanyDetails

Thank you...!