Data warehouse

E-R Modeling in Data Warehouse

Data Modeling is process of designing tables and defining relationships. E-R modeling is a design technique of creating tables in highly normalized form inside a relational database. It represents relationship among entities.

Entity

An entity may be defined as a thing capable of an independent existence that can be uniquely identified. An entity is an abstraction from the complexities of a domain. When we speak of an entity, we normally speak of some aspect of the real world that can be distinguished from other aspects of the real world (or) simply an entity is category of an object which business is interested.
For example in a bank database we have customer, employees, branches and services offered can be considered as entities.
Entity set is a collection similar type of entities. For example customer set contain all customers in a bank.

Attributes

Entities are represented by their properties is called as attributes. An attribute is  characteristic of an entity relationship. For example customer entity may contain name, amount and age considered as attributes.
Below figure shows how one entity is related with other entity.
All the activities which are performed in OLTP, which is E-R Model. It is the best modeling technique for OLTP. Since it represents data flow in entity and relationships hence it is called Entity Relationship(E-R) Modeling.  

Keys

Key is an attribute or collection of attributes that uniquely identifies an entity among entity set.
Super keyA super key of an entity set i a set of one or more attributes.
Candidate key: A candidate key of an entity set is a set of one or more attributes.
Primary key: Primary key is one of the candidate key, choosen by database designer for uniquely identify the entity set. 

Degree of  Relation ship

  • A relationship set is a mathematical relation among two entities, each taken from entity sets.
  • An attribute can also be a property of a relationship set.
  • Degree of relation ship refers to number of entity sets that participate in a relationship set. 
  • Relationship sets that involve two entity sets are binary(or degree two). Generally most relationship sets in a database system are binary.
  • The entity sets may be linked by the ternary(degree three) relationship set.

Cardinality Mapping 

  • Express the number of entities to which another entity can be associated via a relationship set.
  • Mostly we are using binary relationship sets.
  • For binary relationship set the mapping cardinality must be one of the following types
                                         - One to one
                                         - One to many
                                         - Many to one
                                         - Many to many
Powered by Blogger.