The Approach

At this point the teams begin working in their expert domains. The database designer will start to normalize the logical design and prepare it for the database design. Unlike the previous chapters, we now focus, for the most part, fully on the database designer's activities. (Thus there is no longer the need to use the special sections to highlight key areas for the database designer.)

The first step is to work through the logical class diagram to normalize it so that it can be used to create the database design model. Depending on the process, you may create separate entities that are mapped to the current class diagram, but for our example, we will work with the current class diagram and normalize it. While this process is ongoing, the design aspects can begin for both the application designs and database implementation designs. These de signs will be built from the logical analysis model while maintaining a link back (see Figure 6-4).

Figure 6-4. Linkage between the logical analysis, database design, and application design models

There is a need to understand the classes within the model and to make sure that everything needed exists. As a logical data model, there may be a need to define some of the classes that were created as not needed in the database. We will assign a UML tagged value of persistent to all classes that we want to transform to the database. At the same time, there may be attributes that will exist only in the application. Therefore we want to keep them in the logical analysis model but not have them exist anywhere in the database. For example, a database may keep track of individual billing information or may even break it down to specifics within a resident's stay, but the accounting staff wants a quick way to see what Medicare is going to pay. So we create a derived at tribute called TotalMedicarePaymentDue, an attribute that, along with a method, calculates the total amount due from Medicare and exists only in the application. There may be a very good business reason for creating this attribute and it may satisfy a re quirement, but it is not captured in the database.

Inheritance or generalizations become a factor at this stage as well. When in the logical analysis model, we need to make sure that every instance can be modeled and understood . In the MDS, there are some attributes that are collected at different times for all residents. Therefore, we have a class as a supertype of MDS and two classes as subtypes: BasicAssessment and FullAssessment. This allows us to be sure, in an analysis phase, that we are capturing the information needed. When we begin to transform this to the database design models, we have decisions to make. Do we roll up the tables all into one, roll them down into the subtypes , or just leave them as is in a one-to-one relationship?

Discussions can become quite controversial depending on individual style or corporate methodologies. Other considerations are when to assign data types and unique identifiers or primary keys. Some camps say that the unique identifier is important only during implementation of the database, so they wait for the database design model. Others want it earlier to make sure the database is fully normalized prior to denormalization. At EAB, the data analysis team in the logical analysis model will define the primary keys. Also, the datatypes will be non-database -specific types, defined at the logical level, which will be further defined and mapped during database implementation.

The last step during the analysis phase, so that we can transform properly to the database design model, is to ensure that each class defines only one thing. The concept of normalization includes the requirement that each class or entity must define only one subject and should not cross subjects. If the class defines more than one thing, it should be split into multiple classes. During the process of working on the database design diagram, tables may be combined for reasons of optimization. But to make sure that the model is normalized first and that we are capturing all data needed, we have to analyze the model to make sure that each class is unique in the type of data it is capturing.