Logical Design Flashcards
Goal of logical design in relational databases
Convert E/R schema into a relational schema that is efficient and maintains the original data integrity and relationships
What are the two main phases of logical design?
- Restructuring E/R schema
- Translating to relational
How are genrealization hierarchies handled in logical design?
Collapsing hierarchies upward, downward, or reifying them as relationships
What happens to multivalued attributes during restructuring?
They are either replaced with single-valued attributes or converted into a new table
How are 1:N relationships translated into relational tables?
Add primary key of one side as foreign key in many side
How are N:M relationships represented in the relational model?
Using a bridge table containing the primary keys of the related entities
What is a recursive relationship?
Entity references itself, implemented bby adding a foreign key referencing the same table
What criteria are used to select a primary identifier?
- Simplicity
- Minmal null values
- frequent usage in operations
How would you handle a multivalued attribute like “PhoneNumbers”?
Replace it with a separate table containing PhoneNumber and a foreign key referencing the original entity
How is generalization with overlapping coverage handled?
Reifying it as a relationship to avoid redundancy and manage specific attributes for subtypes
Principle of “Keep togetherwhat is used together”
Design tbales to optimize for frequent operations, minimizing the need for complex joins
Why is documentation important for logical design?
Ensure all constraints and relationships are clear, aiding in maintainability and schema evolution
