Convert this building permits table to third normal form. (20 pts). Use the foll
ID: 3860247 • Letter: C
Question
Convert this building permits table to third normal form. (20 pts). Use the following functional dependencies:
BuildingNum, PermitNum --> BuildingAddress, PermitDescription, SuperID, SuperFirstName, SuperLastName, DateIssued,County
BuildingNum --> BuildingAddress PermitNum --> PermitDescription SuperID --> SuperFirstName, SuperLastName
BuildingPermits ( BuildingNum, BuildingAddress, (PermitNum, PermitDescription), SuperID, SuperFirstName, SuperLastName, DateIssued,County)
Explanation / Answer
Given,building permits table is as follows
BuildingPermits(BuildingNum, BuildingAddress, (PermitNum, PermitDescription), SuperID, SuperFirstName, SuperLastName, DateIssued,County)
* In this table we have "BuildingNum, BuildingAddress,PermitNum, PermitDescription, SuperID, SuperFirstName, SuperLastName, DateIssued,County" are attributes of the table.Here, "BuildingNum" determines "permitNum" and "permitNum" determines "PermitDescription".Thus "BuildingNum" determines "PermitDescription" via "permitNum" and it is transtive dependancy.
* In this table PermitDescription,SuperFirstName,SuperLastName are partially dependent on primary key i.e,BuildingNum and this structure does not satisfy 3NF.
* As per third normal form every non-prime attribute of table must be dependent on primary key.In order to make the table in form of 3NF there must be no transtive functional dependency,based on that we have to split the table.
Using the given functional dependencies these are the tables in 3NF.
Table 1 ---> BuildingTable(BuildingNum(PrimaryKey),BuildingAddress,PermitNum(FK),SuperID(FK),DateIssued,County)
Table 2 ---> PermitTable(PermitNum(PK),PermitDescription)
Table 3 ---> SuperTable(SuperID(PK),SuperFirstName,SuperLastName)