Is this database relation in 3NF and BCNF and why?
Staff (SID, Fname, MI, Lname, Position, Shift, Phone_number, DoB, DoE, Gender, Apt_Number, City, Zip_code)
SID is the primary key.
FD1: (SID) --> (Fname, MI, Lname, Position, Shift, Phone_number, DoB, DoE, Gender, Apt_Number, City, Zip_code);
FD2: (Fname, MI, Lname, DoB) --> (SID, Position, Shift, Phone_number, DoE, Gender, Apt_Number, City, Zip_code)
FD3: (Zip_code) --> (City)
Since in FD3, Zip_code is not a primary key and cities can have more than one Zip_code, it is not in 3NF and must be decomposed. Make another table Zip_Locations (Zip_code(Primary Key), City). Zip_Locations only has one FD and its a non-primary that depends on a candidate or primary key, so it satisfies 3NF automatically. Staff gets Staff (SID, Fname, MI, Lname, Position, Shift, Phone_number, DoB, DoE, Gender, Apt_Number, Zip_code) and afterwards the relations are in BCNF because all non-prime keys depend strictly on candidate keys. Plus if there is only one primary key in a relation and it already satisfies 3NF then it automatically satisfies BCNF.
Helpful website: https://web.archive.org/web/20130921232322/http://www.tomjewett.com/dbdesign/dbdesign.php?page=subkeys.php