Problem #3

Business Scenario: To keep track of office furniture, computers, printers, and other office equipment, the FOUNDIT company uses the table structures that meet 3NF. The business rubles can be written: A building may have many rooms, and a room may have many items. An employee may manage many buildings, and one building can be managed by one employee.

The relational schemas are written as follows:

EMPLOYEE (EMP_NAME, EMIAL)

BUILDING (BLDG_NAME, EMP_NAME (FK))

ROOM (ROOM_NUMBER, ROOM_DESCRIPTION, BLDG_NAME(FK))

ITEM (ITEM_DESCRIPTION, ROOM_NUMBER (FK))

Based on the above scenario, accomplish the following tasks.

Task 01 Minimize data entry errors by adding appropriate surrogate keys.

Solution:

Task 02 Improve design by evaluating name conventions.

Solution:

Task 03 Improve design by refining attribute atomicity

Solution:

Task 04 Improve design by identifying new attributes (Hints: add two or more attributes in total)

Solution:

\begin{tabular}{|c|c|c|c|} \hline Attribute Name & Sample Value & Sample Value & Sample Value \\ \hline MEMBER_NUM & 214 & 235 & 214 \\ \hline MEMBER_NAME & \begin{tabular}{l} Alice \\ YanderVoort \end{tabular} & Gerald M. Gallega & \begin{tabular}{l} Alice \\ YanderVocrt \end{tabular} \\ \hline MEMBER_ADORESS & 325 Meadow Park & 123 Rose Court & 325 Meadow Park \\ \hline MEMBER_CITY & Murkywater & Highlight & Murkowater \\ \hline MEMBER ZIPCOOE & 12345 & 12349 & 12345 \\ \hline INVITE_DATE & 23.Feb-2022 & 12-Mar-2022 & 23-Feb-2022 \\ \hline ACCEPT_DATE & 27-Feb-2022 & 15Mar2022 & 27-Feb-2022 \\ \hline DINNER_DATE & 15-Mar-2022 & 17-Mar-2022 & 15-Mar-2022 \\ \hline DINNER_ATTENDED & Yes & Yes & No \\ \hline DINNER_CODE & D15 & D15 & D12 \\ \hline DINNER_DESCRIPTION & Glowing sea delight & \begin{tabular}{l} Glowing sea \\ delight \end{tabular} & Ranch Superb \\ \hline ENTREE_COOE & EN3 & EN3 & EN5 \\ \hline ENTREE_DESCRIPTION & Stuffed crab & Stulled crab & Marinated steak \\ \hline DESSERT_CODE & DE8 & DE5 & DE2 \\ \hline DESSERT_DESCRIPTION & \begin{tabular}{l} Chocolate mousse \\ with raspberry \\ sauce \end{tabular} & Cherries jublee & \begin{tabular}{l} Apple pie with \\ honey crust \end{tabular} \\ \hline \end{tabular}