1. Database design process
Let’s first start by zooming out and see where we are in the database design process … We started by collecting and analyzing the data requirements which we referred to as the universe of discourse. These requirements where then formalized into a data model. As our conceptual data model, we can make use of either EER model or OO model since both have a user friendly graphical representation. As the next step, we can map our conceptual data model to a logical date model. This data model is based upon the data model of the DBMS that will be used for the actual implementation. This chapter does explain how to map EER or OO models to CODASYL, relation or OO models. Finally, a physical data model is the actual implementation of the logical data model for example in Microsoft or in Oracle.
2. Conceptual database design
The conceptual data model needs to satisfy various requirements.
· It needs to be able to accurately and unambiguously model the universe of discourse.
· It must be flexible such that new or changing requirements can be easily added to the model.
· The model must be user friendly and easy to understand the graphic representation so that it can be used as a communication tool between the database developers and the business users.
· DBMS or implementation independent since its only goal is to collect and analyze data requirements.
· When considering these requirements, it becomes logical that only two models are illegible for a conceptual data model: EER model and OO model. Since both of these models also have their shortcomings, it is of key importance to document.
As a reminder, EER models are temporary snapshot. Hence, temporal aspects cannot be included and need to be written down as business rules. Ex: an employee cannot return to a department.
· To sum up, the conceptual data model needs to be complemented with some business rules which can then be reconsidered during the actual implementation.
Key steps when developing a conceptual data model:
· Identify and name entity types
· Identify and name relationship types
· Assign role names and cardinalities to relationship types
· Identify and assign attribute types and assigned to either the entity or relationship type.
· Define key attribute types
· Identify weak entity types
· Apply EER constructs such as specialization, categorization and aggregation
It is important that these activities are executed interactively in close collaboration with the business user. When naming the modeling concepts, it’s important the use conventional naming concepts to facilitate the model understanding and the future maintenance.
The steps to design an OO model are quite similar to the ones listed before for the conceptual model.
- · Identify and name (persistent) classes
- · Identify and name association types
- · Assign role names, cardinalities and direction reading arrows to association types
- · Identify and assign attribute types
- · Define key attribute types
- · Identify, name, design and assign methods
- · Identify weak entity types
- · Identify specialization (is-a) and aggregation (part of) relationships
- · Also here, additional semantics can be written down as business rules.