This is just a made up table that i made solely for this question only, suppose I have 2 tables mechanic and client from my conceptual design
| mech_id | mech_name |
|---|---|
| 1 | a |
| 2 | b |
| 3 | c |
| client_id | car_reg_no |
|---|---|
| 1 | abc123 |
| 2 | sdf321 |
| 3 | jhg123 |
and i have this car repair table that i should normalize, where i have a client's car and a mechanic who repaired the client's car
| job_id | car_reg_no | Mech_id1 | Mech_name1 | Mech_id2 | Mec_name2 |
|---|---|---|---|---|---|
| 501 | abc123 | 1 | a | 2 | b |
| 102 | sdf321 | 1 | a | 3 | c |
| 302 | jhg123 | 2 | b | 3 | c |
i want to find the 3NF so the steps would be:
UNF
CAR_REPAIR (job_id, car_reg_no, mech_id1, mech_name1, mech_id2, mech_name2)
1NF (finding primary key and partial dependencies)
CAR_REPAIR (**job_id**, car_reg_no, mech_id1, mech_name1, mech_id2, mech_name2)- no partial dependencies
2NF (finding transitive dependencies)
CAR_REPAIR (**job_id**, car_reg_no, mech_id1, mech_name1, mech_id2, mech_name2)- transitive dependencies
- mech_id1 -> mech_name1
- mech_id2 -> mech_name2
3NF
CAR_REPAIR (**job_id**, car_reg_no, mech_id1,mech_id2)MECHANIC1 (**mech_id1**, mech_name1)MECHANIC2 (**mech_id2**, mech_name2)
My question
in 3NF, i have 2 mechanic (Mechanic1 and Mechanic2), where as in my table i only have 1 for Mechanic. how do i change it into Mechanic only like what words should i use or just use this as my 3NF? without specify anything
CAR_REPAIR (**job_id**, car_reg_no, mech_id1,mech_id2)MECHANIC (**mech_id**, mech_name)
what would happen for mech_id1 and mech_id2 in CAR_REPAIR if i just specify the 3NF from the above?