What is Logical Database Design
- 378
Logical Database Design
The logical database design contains the definition of the data to be stored in a database. It also contains the rules and information about the structure and type of data. All entities, their attributes, and their relationships are described in a logical model. It is the complete description of data stored in a database.
Represent Entities
Each entity in an E-R diagram is represented as a relation in the relational model. In this process, the name of the entity becomes the name of the relation. The identifier of entity type becomes the primary key of the relation. The remaining attributes of the entity type become non-key attributes of the relation.
Represent Relationships
Each relationship in an E-R diagram must also be represented in the relational model. The representation depends on the nature of the relationship. In some cases, a relationship is represented by making the primary key of one relation a foreign key of another relation. In some cases, a separate relation is created to represent a relationship.
Merge the Relations
In some cases, there may be redundant relations. It means that two or more relations may describe the same entity type. The redundant relations must be merged to remove the redundancy. This process is also known as view integration. Suppose there are two relations as follows:
EMP1 (EMPNO, NAME, ADDRESS, PHONE)
EMP2 (EMPNO, ENAME, EMP-ADDR, EMP_JOB CODE, EMP_DOB)
The above tables EMP1 and EMP2 describe the same entity EMPLOYEE. They can be merged into one relation. The result of merging the above relations is as follows:
EMP (EMPNO, NAME, ADDRESS, PHONE, EMP_JOB CODE, EMP_DOB)
The new relation contains attributes of both relations without any repeating attributes.
Normalize the Relations
The relations created in step 1 and step 2 may have some unnecessary redundancy. Some certain anomalies or errors may arise while updating these relations. The process of normalization refines these relations to avoid these problems.
