techsupport Data Design
This chapter is devoted to designing a relational database, and it usually takes place in a multi-step design process. This chapter focuses on modelization and the representation of this modelization with a very widespread notation entity/association. Beforehand, we will discuss the final objective, which is to obtain a database schema that conforms to requirements and does not present any anomalies.
Standardization
The design of a relational diagram does not present any technical difficulty. We note in practice that it requires a specific experience, in particular, because the advice of use, somewhat abstract, becomes much clearer when one makes a mistake once or twice. That one noted the consequences of his errors. The data schema constitutes the foundations of an application. The schema is never fixed: it will evolve with the application. But the existing part of a diagram is always challenging to question without leading to a significant rewrite.
Given a schema and its functional dependencies, we know how to determine if it is normal. Can we automatically produce a normalized diagram from all the attributes and their constraints (the DFs)? In principle, yes. In practice, not really.
Advanced Concepts
So far, we have considered the case of entities independent of each other. Each entity, with its identifier, could be viewed in isolation, and one entity can only exist in close association with another and is identified relative to that other entity. We then speak of a weak entity.
Take the example of a cinema and its rooms. We can consider each room as an entity endowed with attributes such as capacity, Dolby sound equipment, or others. It isn’t easy to imagine representing a room without it attached to its cinema. At the cinema level, we will find some general information such as the room’s address.
It is possible to represent the link between a cinema and its rooms by a classic association, as shown in the figure. Possible models of the Cinema-Room link .a. The cardinality 1..1 force the participation of a theatre to a link of association with one and only one cinema. This representation is correct but has a drawback: you have to create an artificial identifier for the Room entity type and number of all the rooms, regardless of the cinema to which they are attached.
We can consider it much more natural to number the rooms by a number internal to each cinema. The identification key of a room is then made up of two parts:
Types
- the Cinema key, which indicates in which cinema the room is located;
- the room number within the cinema.
In other words, the Room entity does not have an absolute identification but an identification relative to another entity. Of course, this forces the room always associates with one and only one cinema.
The graphical representation of weak entities with UML has illustrated in the figure Possible models of the Cinema-Room link .b. The room associated with the cinema with an association qualified by the attribute, which serves as a discriminant to distinguish the rooms within the same cinema.
Specialization
The Entity / Association model is simple and practical.
- There are only three concepts: entities, associations, and attributes.
- It is suitable for intuitive graphical representation, although there are many conventions.
- It allows us to quickly model structures that are not too complex.
Unfortunately, it has several limitations, which arise because many more or less equivalent design choices can derive from a single specification. The specification itself is, in most cases, informal and subject to interpretation.
Another drawback of the E / A model remains poverty: it is challenging to express integrity constraints and complex structures. Lots of extensions suggest, but the schematic design remains a matter of common sense and experience. We usually try:
- To come down to associations between 2 entities: beyond that, it is probably in the interest of transforming the association into a commodity;
- Avoid any redundancy: information must be in one place;
- Lastly – and above all – to favour simplicity and readability by representing only what is strictly necessary.
The development of a model strongly involves continuing a database development project. It must be based on experienced people, listening to prescribers, and on an iterative process that identifies ambiguities and seeks to resolve them by specifying the corresponding need. Moreover, in the context of databases, the E / A model uses in the design phase. It makes it possible to determine the structure of the information in the database and offer an abstract representation independent of the logical model chosen next.
