Friday, September 28, 2012

Part 4.2 Relational Data Modeling



4.2 Relational Data Modeling

     4.2.1     Relational Data Model Concepts
     4.2.2     ER to Relational Mapping Algorithm
     4.2.3     Relational Database Design
     4.2.3.1 Relational Database Design Approach
     4.2.3.2 Normalization
    
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
          - minimize redundancy
          - minimize insertion, deletion, and modification anomalies

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:
          -  The lossless join or nonadditive join property (Mandatory)

          -  The dependency preservation property (Desirable)