Here is the definition of Office First Occupied Date:

Office First Occupied Date is the official date, as recorded in the Human Resources database, when a person first took up residence in an office. It must be a business day (that is, not a weekend day or holiday).

The business expert in this area provided you with the following three rules that need to be captured on this model:

1.      Office First Occupied Date is a mandatory field for each employee who has an office.

2.      Only managers have an office and all managers have an office.

3.      A manager has one and only one office.

Modify this model to accommodate this new data element and the three business rules. Add the rules as Business Rules in PowerDesigner, and link them to the entities and attributes that document them.

Extract the Entity and Attribute Names and Descriptions for review by your experts.

See the Appendix for my answers.

EXERCISE 18: Modifying and Normalizing a Logical Data Model in PowerDesigner

In this exercise, you will work with the Contact Logical Data Model and work through the normalization steps we described.

Whether or not you create domains in this LDM is your choice. We do not specify data types in this exercise, and only one attribute requires a list of values, so they are not strictly necessary.

Create a new LDM called “Exercise 18”, containing the entity shown in Figure 16.28.

Figure 16.28 The initial model

First Normal Form

Rename Business Card as Contact, and create two new entities, called Contact Phone and Contact Specialty.