I am creating a table as follows:
Building (PK: buildingID) Area (PK: areaID, FK: buildingID) Room (PK: roomID, FK: buildingID, FK: areaID)
All rooms are in a building. Some rooms can be in an area of a building. Rooms that are not in an area of a building have the FK: areaID set to NULL.
For example, areas of a building include: blue zone, green zone, red zone. Rooms can be in one of those areas, but may not be in an area and just part of the building itself.
Is this properly normalized? I feel that when the FK: areaID is set to a value then it is not normalized as the buildingID can be determined from the areaID.
If it is not normalized, how do I go about normalizing it so that the FK: areaID can still use NULL values?