15 - Logical Design

Logical Design Overview

• Course: University of Liverpool COMP 107 Logical Design

Aims of Logical Design

• Purpose: To construct a logical schema representing all information from the conceptual schema (e.g., ER schema).

• Complexity: Establishing a direct translation from conceptual to logical schema is not always straightforward. Examples:

  • Extended Entity-Relationship (EER) schemas cannot be directly translated to relational databases (classes do not have a one-to-one match).

  • Logical design is closer to the final computer-based product, while conceptual design represents data at a higher, computer-independent level.

Decision-Making in Logical Design

Key Mapping Decisions

1. Analysis of Redundancies

   • Evaluate potential redundancies and decide to retain or eliminate them.

2. Handling Unsupported Concepts

   • Identify how to map generalization concepts to other constructs in a relational model.

3. Partitioning and Merging

   • Example: Separate personal data (Name, Year of Birth) from professional data (Level, Salary) based on access/modification frequency.

4. Selecting Primary Identifiers

   • Add new attributes to entities lacking a natural primary key.

5. Addressing Derived Attributes

   • Determine how to represent derived attributes, either as virtual fields, user views, queries, or to be ignored altogether.

6. Cycles and Derived Relationships

   • Assess if a derived relationship can safely be ignored or must be defined as its own relationship.

Reading Reference

• Reading: Elmasri & Navathe, Fundamentals of Database Systems, Chapter 9.

Process of Mapping ER to Relational Relations

Step-by-Step Approach

1. Regular Entities

   • Create a relation for every regular entity, including all simple attributes and primary keys.

2. Weak Entities

   • Create relations for weak entities with primary keys taken from the owning entity.

3. Binary Relationships (1:1)

   • Identify relations, include foreign keys from the related entities, and primary key attributes.

4. Binary Relationships (1:N)

   • Similar to 1:1, but foreign keys are included in the many-side relation.

5. Binary Relationships (N:M)

   • Create a new relation that includes foreign keys from both participating entities and their attributes.

6. Multi-Valued Attributes

   • Create a separate relation to handle multi-valued attributes along with primary keys from the relevant entity.

7. N-ary Relationships

   • Identify all related entities and create a new relation that includes foreign keys from each.

Further Steps

• Steps include addressing special cases like generalizations/specializations with multiple subclasses into relation schemas and introducing flags for handling complex classifications rather than creating distinct relations.

Additional Concepts

• Total Participation: Must be enforced programmatically if relationships cannot guarantee through constraints.

• Relational vs. ER Model Differences:

  • Relationships handled via primary and foreign keys in the relational model, joining records to reflect relationships.

  • Multivalued attributes require additional separate relations in relational models, whereas ER supports them inherently.

Conclusion

• Understanding the intricacies of logical design decisions and their implications on the relational model is key for successful database development, ensuring correct representation of all data while optimizing efficiency.