resturant ID : 1 1 2 2 3
Resturant Name: Bears of brooklyn Bears of brooklyn Cats of Cape Town Cats of Cape Town Doves of durban
Waiter ID: 2 3 3 4 5
Waiter Name: John Sarah Sarah Themba Pete
waiter Surname: Smith Ndambi Ndambi Dlaminin Nair
Employee Type ID: 1 2 2 1 2
Employee Type: permanent temporary temporary permanent temporary
start Date: 2020-01-19 2021-09-09 2021-11-02 2022-02-02 2022-04-03
end date : 2020-09-15 2021-10-30 - 2022-03-15 -
normalise the above data to the third normal form (3NF). show all the steps and the final answer in the form of a dependency diagrams.
First, we need to identify the functional dependencies in the data:
1. Resturant ID -> Resturant Name
2. Waiter ID -> Waiter Name, Waiter Surname
3. Employee Type ID -> Employee Type
4. Resturant ID -> Waiter ID
5. Waiter ID -> Employee Type ID
6. Waiter ID -> Start Date, End Date
Next, we will create separate tables for each entity:
Resturant Table:
Resturant ID | Resturant Name
1 | Bears of brooklyn
2 | Cats of Cape Town
3 | Doves of durban
Waiter Table:
Waiter ID | Waiter Name | Waiter Surname
2 | John | Smith
3 | Sarah | Ndambi
4 | Themba | Dlaminin
5 | Pete | Nair
Employee Type Table:
Employee Type ID | Employee Type
1 | permanent
2 | temporary
Resturant Waiter Table:
Resturant ID | Waiter ID
1 | 2
1 | 3
2 | 3
2 | 4
3 | 5
Waiter Employee Type Table:
Waiter ID | Employee Type ID
2 | 1
3 | 2
4 | 1
5 | 2
Waiter Employment Table:
Waiter ID | Start Date | End Date
2 | 2020-01-19 | 2020-09-15
3 | 2021-09-09 | 2021-10-30
3 | 2021-11-02 | null
4 | 2022-02-02 | 2022-03-15
5 | 2022-04-03 | null
Finally, we can create a dependency diagram to represent the relationships between the tables:
Resturant Table -> Resturant ID
Waiter Table -> Waiter ID
Employee Type Table -> Employee Type ID
Resturant Waiter Table -> (Resturant ID, Waiter ID)
Waiter Employee Type Table -> (Waiter ID, Employee Type ID)
Waiter Employment Table -> (Waiter ID, Start Date, End Date)
This represents the data in 3NF.