For the following description of a database to support physical plant operations
ID: 3821866 • Letter: F
Question
For the following description of a database to support physical plant operations, identify functional dependencies and construct normalized tables in BCNF. You are to assist physical plant personnel in managing key cards for access to buildings and rooms. The primary purpose of the database is to ensure proper accounting for all key cards:
a. A building has a unique building number, a unique name, and a location within the campus.
b. A room has a unique room number, a size (physical dimensions), a capacity, a number of entrances, and a description of equipment in the room.
c. Each room is located in exactly one building. The room number includes a building identification and followed by an integer number. For example, room GW123 identifies room 123 in the George Warren (GW) building.
d. An employee has a unique employee number, a name, a position, a unique e-mail address, a phone, and an optional room number in which the employee works.
e. Magnetically encoded key cards are designed to open one or more rooms. A key card has a unique card number, a date encoded, a list of room numbers that the key card opens, and the number of employee authorizing the key card. A room may have one or more key cards that open it. A key type must be authorized before it is created
Explanation / Answer
Given Tables are as follows:
Building table
Building No, Name, location
Room Table
Room No, Size, Capacity, No of Entrances, Equipment Description
Employee
Emp No, name, position, e-mail, address, phone, room no
Card Table
Card No, date, list of room, no no of employee authorizing
Converting Tables to BCNF:-
Converting Building table to BCNF (Every determinant should be a candidate key)
1.Building No, Name
2.Location, list of building No
Converting Room table to BCNF (Every determinant should be a candidate key)
1.Size, list of room no
2.Capacity, list of room no
3.No of Entrances, list of room no
4.Equipment description, list of room no
Converting Employee table to BCNF (Every determinant should be a candidate key)
1.Emp No, Name, e-mail
2.Position, list of emp no
3.Room no, list of emp no
Converting Card table to BCNF (Every determinant should be a candidate key)
1.Card No, list of room no (the key can open), list of emp no (who can authorize the key card)
2.Date, list of card no