4.2 Relational Data Modeling
4.2.1 Relational Data Model Concepts
Relation Data Model
- A relational data model is a representational or implementation or record-based data model
- The model uses the concept of a mathematical relation – which looks somewhat like a table of values
- The model has its theoretical basis in set theory and first order predicate logic
4.2.2 ER to Relational Mapping Algorithm
Logical Design
Based upon the conceptual data model
Four key steps
1. Develop a logical data model for each known user interface for the application using normalization principles.
2. Combine normalized data requirements from all user interfaces into one consolidated logical database model
3. Translate the conceptual E-R data model for the application into normalized data requirements
4. Compare the consolidated logical database design with the translated E-R model and produce one final logical database model for the application
Step 1 – (Identify Strong Entities)
Step 2 — (Identify Weak Entities)
Step 3 — (Identify Relationships)
Step 4 — (Identify dependent relationships)
Step 5 — (Identify dependent relationships if required)
Step 6 — (Identify multi dependent relationships)
Step 7 — (Identify for any new sub tables)
Step 8 – (Specialization)
4.2.3 Relational Database Design
4.2.3.1: Relational Database Design Approaches
Database Design Approaches
Bottom-up (Design by synthesis)
- The basic relationship among individual attributes is used as starting point to build up relations
- Not very popular in practice
- Suffers from the problem of collecting a large number of binary attribute relationships as the starting point
Top-down (Design by Analysis)
- Starts with a number of groupings of attributes into relations that have already been obtained from conceptual design and mapping activities
- Design by Analysis is then applied to the relations individually and collectively, leading to further decomposition until all desirable properties are met
4.2.3.2 Normalization
Normalization
- Normalization of data can be looked upon as a process of analyzing the given relational schemas based on their functional dependencies and primary keys to
Normal Form
- Normal form of a relation refers to the highest normal form condition that it meets
- Normal forms, when considered in isolation from other factors, do not guarantee a good design. The normalization through decomposition must confirm the following two properties for a good database design:
