Changes between Version 5 and Version 6 of Normalization


Ignore:
Timestamp:
09/19/25 01:59:12 (3 hours ago)
Author:
226030
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Normalization

    v5 v6  
    99
    1010== Reservation Management
    11 ||= fs_manage_reservation_id =||= employee_id =||= e_email =||= e_city =||= e_street =||= e_phone_number =||= e_password =||= net_salary =||= gross_salary =||= staffRole_id =||= name =||= stay_length =||= creation_timestamp =||= datetime =||= number_of_people =||= reservation_id =||= customer_id =||= c_email =||= c_street =||= c_city =||= c_phone_num =||= c_password =||= table_number =||= seat_capacity
    12 || 1 || 1 || test@hotmail.com || Strumica || Mladinska 3 || 70003005 || password1 || 30000 || 40000 || 1 || Server || NULL || 15.7.2025 10:33 || 15.7.2025 20:00 || 4 || 1 || 2 || test2@hotmail.com || Marsal Tito 10 || Strumica || 70001002 || password2 || 1 || 4
    13 || 2 || 1 || test@hotmail.com || Strumica || Mladinska 3 || 70003005 || password1 || 30000 || 40000 || 1 || Server || NULL || 16.7.2025 10:33 || 17.7.2025 20:00 || 6 || 2 || 2 || test2@hotmail.com || Marsal Tito 10 || Strumica || 70001002 || password2 || 2 || 8
    14 || 3 || 5 || test5@hotmail.com || Strumica || Mladinska 12 3 || 70005005 || password1 || 20000 || 37000 || 4 || Hostess || NULL || 18.7.2025 10:33 || 18.7.2025 20:00 || 6 || 3 || 2 || test2@hotmail.com || Marsal Tito 10 || Strumica || 70001002 || password2 || 2 || 8
    15 || 4 || 5 || test5@hotmail.com || Strumica || Mladinska 12 3 || 70005005 || password1 || 20000 || 37000 || 4 || Hostess || NULL || 18.7.2025 10:33 || 18.7.2025 21:00 || 6 || 4 || 3 || test3@hotmail.com || Mladinska 5 || Strumica || 70003003 || password2 || 3 || 8
    16 
    17 R = {fs_manage_reservation_id, employee_id, e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, staffRole_id, name, stay_length, creation_timestamp, datetime, number_of_people, reservation_id, customer_id, c_email, c_street, c_city, c_phone_number, c_password, table_number, seat_capacity}
     11||= employee_id =||= e_email =||= e_city =||= e_street =||= e_phone_number =||= e_password =||= net_salary =||= gross_salary =||= staffRole_id =||= name =||= stay_length =||= creation_timestamp =||= datetime =||= number_of_people =||= reservation_id =||= customer_id =||= c_email =||= c_street =||= c_city =||= c_phone_num =||= c_password =||= table_number =||= seat_capacity
     12|| 1 || test@hotmail.com || Strumica || Mladinska 3 || 70003005 || password1 || 30000 || 40000 || 1 || Server || NULL || 15.7.2025 10:33 || 15.7.2025 20:00 || 4 || 1 || 2 || test2@hotmail.com || Marsal Tito 10 || Strumica || 70001002 || password2 || 1 || 4
     13|| 1 || test@hotmail.com || Strumica || Mladinska 3 || 70003005 || password1 || 30000 || 40000 || 1 || Server || NULL || 16.7.2025 10:33 || 17.7.2025 20:00 || 6 || 2 || 2 || test2@hotmail.com || Marsal Tito 10 || Strumica || 70001002 || password2 || 2 || 8
     14|| 5 || test5@hotmail.com || Strumica || Mladinska 12 3 || 70005005 || password1 || 20000 || 37000 || 4 || Hostess || NULL || 18.7.2025 10:33 || 18.7.2025 20:00 || 6 || 3 || 2 || test2@hotmail.com || Marsal Tito 10 || Strumica || 70001002 || password2 || 2 || 8
     15|| 5 || test5@hotmail.com || Strumica || Mladinska 12 3 || 70005005 || password1 || 20000 || 37000 || 4 || Hostess || NULL || 18.7.2025 10:33 || 18.7.2025 21:00 || 6 || 4 || 3 || test3@hotmail.com || Mladinska 5 || Strumica || 70003003 || password2 || 3 || 8
     16
     17R = {employee_id, e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, staffRole_id, name, stay_length, creation_timestamp, datetime, number_of_people, reservation_id, customer_id, c_email, c_street, c_city, c_phone_number, c_password, table_number, seat_capacity}
    1818
    1919Functional Dependencies:
    2020
    21 fs_manage_reservation_id → reservation_id, employee_id, table_number
    22 
    2321employee_id → e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, staffRole_id
    2422
    … …  
    3129table_number → seat_capacity
    3230
    33 Left only: fs_manage_reservation_id
    34 
    35 Left and right: employee_id, staffRole_id, customer_id, table_number, reservation_id
     31Left only: employee_id, reservation_id, table_number
     32
     33Left and right: staffRole_id, customer_id
    3634
    3735Right only: e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, name, stay_length, creation_timestamp, datetime, number_of_people, c_email, c_street, c_city, c_phone_number, c_password
    3836
    39 {fs_manage_reservation_id}* = R => fs_manage_reservation_id is the primary key
    40 
    41 We can see that 1NF and 2NF are satisfied due to having atomic attributes and a single primary key, but BNCF is not satisfied due to transitive dependencies.
     37{reservation_id, employee_id, table_number}* = R => {reservation_id, employee_id, table_number} is the only candidate key and primary key.
     38
     39There are no multi-valued dependencies in our relation, and therefore it satisfies the first normal form.
     40
     41But our previous defined relation isn’t in 2NF because there is a partial dependency:
     42
     43employee_id → e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, staffRole_id
     44
     45reservation_id → stay_length, creation_timestamp, datetime, number_of_people, customer_id, table_number
     46
     47table_number → seat_capacity
    4248
    4349Decomposition: Step 1: Decompose R by employee_id
    4450
    45 {e_id}* = {employee_id, e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, staffRole_id, name}
     51{employee_id}* = {employee_id, e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, staffRole_id, name}
    4652
    4753R1 = {employee_id, e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, staffRole_id, name}
    4854
    49 R2 = {employee_id, fs_manage_reservation_id, stay_length, creation_timestamp, datetime, number_of_people, reservation_id, customer_id, c_email, c_street, c_city, c_phone_number,c_password, table_number, seat_capacity}
    50 
    51 Step 2: Decompose R1 by staffRole_id
     55R2 = {employee_id, stay_length, creation_timestamp, datetime, number_of_people, reservation_id, customer_id, c_email, c_street, c_city, c_phone_number,c_password, table_number, seat_capacity}
     56
     57Step 2: Decompose R2 by table_number
     58
     59{table_number}* = {table_number, seat_capacity}
     60
     61R3 = {table_number, seat_capacity}
     62
     63R4 = {employee_id, stay_length, creation_timestamp, datetime, number_of_people, reservation_id, customer_id, c_email, c_street, c_city, c_phone_number,c_password, table_number}
     64
     65Step 3: Decompose R4 by reservation_id
     66
     67{reservation_id}* = {reservation_id, stay_length, creation_timestamp, datetime, number_of_people, customer_id, c_email, c_street, c_city, c_phone_number,c_password}
     68
     69
     70R5 = {reservation_id, stay_length, creation_timestamp, datetime, number_of_people, customer_id, c_email, c_street, c_city, c_phone_number,c_password}
     71
     72R6 = {employee_id, table_number, reservation_id}
     73
     74We removed all the partial dependencies, but there are still transitive dependencies so the relation is not in BCNF.
     75
     76Step 4: Decompose R1 by staffRole_id
    5277
    5378{staffRole_id}*= {staffRole_id, name}
    5479
    55 R3 = {staffRole_id, name}
    56 
    57 R4 = {employee_id, e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, staffRole_id}
    58 
    59 Step 3: Decompose R2 by customer_id
     80R7 = {staffRole_id, name}
     81
     82R8 = {employee_id, e_email, e_city, e_street, e_phone_number, e_password, net_salary, gross_salary, tip_precent, staffRole_id}
     83
     84Step 3: Decompose R5 by customer_id
    6085
    6186{customer_id}* = {customer_id, c_email, c_street, c_city, c_phone_number, c_password}
    6287
    63 R5 = {customer_id, c_email, c_street, c_city, c_phone_number, c_password}
    64 
    65 R6 = {fs_manage_reservation_id, reservation_id, employee_id, stay_length, creation_timestamp, datetime, number_of_people, customer_id, table_number, seat_capacity}
    66 
    67 Step 4: Decompose R6 by table_number
    68 
    69 {table_number}* = {table_number, seat_capacity}
    70 
    71 R7 = {fs_manage_reservation_id, reservation_id, employee_id, stay_length, creation_timestamp, datetime, number_of_people, customer_id, table_number}
    72 
    73 R8 = {table_number, seat_capacity}
    74 
    75 Step 5: Decompose R7 by reservation_id
    76 
    77 {reservation_id}* = {reservation_id, stay_length, creation_timestamp, datetime, number_of_people, customer_id}
    78 
    79 R9 = {fs_manage_reservation_id, reservation_id, employee_id, table_number}
     88R9 = {customer_id, c_email, c_street, c_city, c_phone_number, c_password}
    8089
    8190R10 = {reservation_id, stay_length, creation_timestamp, datetime, number_of_people, customer_id}
    8291
     92
    8393Final BCNF Relations:
    8494
    85 R3(StaffRole), R4(FrontStaff), R5(Customer), R10(Reservation), R8(Table) R9 (FrontStaff_Managed_reservation)
     95R7(StaffRole), R8(FrontStaff), R9(Customer), R10(Reservation), R3(Table) R6 (FrontStaff_Managed_reservation)
    8696
    8797== Shift Management