Logical Design: Database Design Stages
Logical design is the second stage in the database design process. The logical design goal is to design an enterprise-wide database based on a specific data model but independent of physical-level details. Logical design requires that all objects in the conceptual model be mapped to the specific constructs used by the selected database model. For example, the logical design for a relational DBMS includes the specifications for the relations (tables), relationships, and constraints (i.e., domain definitions, data validations, and security views).
The logical design is generally performed in four steps, which are as follows.
1. Map conceptual model to logical model components
2. Validate logical model using normalization
3. Validate logical model integrity constraints
4. Validate logical model against user requirements
1. Map the Conceptual Model to the Logical Model:
The first step in creating the logical design is to map the conceptual model to the chosen database constructs. Logical design generally involves translating the ER model into a set of relations (tables), columns, and constraints definitions. The process of translating the conceptual model into a set of relations is depicted as follows.
1. Map strong entities
2. Map supertype/subtype relationships
3. Map weak entities
4. Map binary relationships
5. Map higher degree relationships
2. Validate the Logical Model Using Normalization:
The logical design should contain only properly normalized tables. The process of mapping the conceptual model to the logical model may unveil some new attributes or the discovery of new multivalued or composite attributes. Therefore, it’s very likely that new attributes may be added to tables or entire new tables added to the logical model. For each identified table (old and new), you must ensure that all attributes are fully dependent on the identified primary key and that the tables are in at least third normal form (3NF).
3. Validate Logical Model Integrity Constraints:
The translation of the conceptual model into a logical model also requires the definition of the attribute domains and appropriate constraints. For example, the domain definitions for the CLASS_CODE, CLASS_DAYS, and CLASS_TIME attributes of the CLASS entity are written this way:
CLASS_CODE is a valid class code.
Type: numeric
Range: low value = 1000 high value = 9999
Display format: 9999
Length: 4
CLASS_DAYS is a valid day code.
Type: character
Display format: XXX
Valid entries: MWF, TTh, M, T, W, Th, F, S
Length: 3
CLASS_TIME is a valid time.
Type: character
Display format: 99:99 (24-hour clock)
Display range: 06:00 to 22:00
Length: 5
4. Validate the Logical Model against User Requirements:
The logical design translates the software-independent conceptual model into a software-dependent model. The final step in the logical design process is to validate all logical model definitions against all end-user data, transaction, and security requirements. The stage is now set to define the physical requirements that allow the system to function within the selected DBMS/hardware environment.
You May Also Like:
Database Development Life Cycle
Conceptual Design in Database Design Process
