ERD

ERD

65808331

Capture222

16.What is ER Diagram ?
The Entity-Relationship (ER) model was as a way to unify the network and relational database views.
Simply stated the ER model is a conceptual data model that views the real world as entities and relationships.
A basic component of the model is the Entity-Relationship diagram which is used to visually represents data objects.
The model has been extended and today it is commonly used for database design For the database designer, the utility of the ER model is:it maps well to the relational model.
The constructs used in the ER model can easily be transformed into relational tables.
  • ER model or entity-relationship model is a particular methodology of data modeling wherein the goal of modeling is to normalize the data by reducing redundancy.
  • This is different than dimensional modeling where the main goal is to improve the data retrieval mechanism.

.

.

26Capture

  • Data models are tools used in analysis to describe the data requirements and assumptions in the system from a top-down perspective.
  • They also set the stage for the design of databases later on in the SDLC.
  • ER diagram is abbreviated as Entity-Relationship diagram which illustrates the interrelationships between the entities in the database.
  • This diagram shows the structure of each tables and the links between the tables.

There are three basic elements in ER models:

  • Entities are the “things” about which we seek information.
  • Attributes are the data we collect about the entities.
  • Relationships provide the structure needed to draw information from multiple entities
  • ER model or entity-relationship model is a methodology for data modeling wherein the goal of modeling is to normalize the data by reducing redundancy.

17.What are Aggregate tables?

  • Aggregate tables are the tables which contain the existing warehouse data which has been grouped to certain level of dimensions.
  • It is easy to retrieve data from the aggregated tables than the original table which has more number of records
  • This table reduces the load in the database server and increases the performance of the query.

18.What is the difference between ER Modeling and Dimensional Modeling?

  • ER modeling will have logical and physical model but Dimensional modeling will have only Physical model.
  • ER Modeling is used for normalizing the OLTP database design whereas Dimensional Modeling is used for de-normalizing the ROLAP and MOLAP design
19.What is Data Cardinality?
Cardinality is the term used in database relations to denote the occurrences of data on either side of the relation.
There are 3 basic types of Cardinality :
High data cardinality: Values of a data column are very uncommon.e.g.: email ids and the user names 
Normal data cardinality: Values of a data column are somewhat uncommon but never unique.e.g.: A data column containing LAST_NAME (there may be several entries of the same last name)
Low data cardinality: Values of a data column are very usual.e.g.: flag statuses: 0/1 
Determining data cardinality is a substantial aspect used in data modeling. This is used to determine the relationships
Types of cardinalities:
The Link Cardinality – 0:0 relationships
The Sub-type Cardinality – 1:0 relationships
The Physical Segment Cardinality – 1:1 relationship
The Possession Cardinality – 0: M relation
The Child Cardinality – 1: M mandatory relationship
The Characteristic Cardinality – 0: M relationship
The Paradox Cardinality – 1: M relationship

One to One, One to many, and many to many are different types of cardinalities. In a database, high cardinality means more unique values are stored in a column and vice versa.

 20.What is a subtype and super type entity?

  • An entity can be split into many entities (sub-entities) and grouped based on some characteristics and each sub entity will have attributes relevant to that entity.
  • These entities are called subtype entities.
  • The attributes which are common to these entities are moved to a super (higher) level entity, which is called as supertype entity.

18Capture

16Capture19Capture

let’s say we have these different entities – vehicle, car, bike, Economy car, family car, sports car. Here, a vehicle is a super type entity. Car and bike are its sub-type entities. Furthermore, economy car, sports car, and family car are sub-type entities of its super-type entity- car. A super-type entity is the one which is at a higher level. Sub-type entities are ones which are grouped together on the basis of certain characteristics. For example, all bikes are two-wheelers and all cars are four wheelers. And since both are vehicles, so their super-type entity is ‘vehicle’.

A super-type entity is the one which is at a higher level. Sub-type entities are ones which are grouped together on the basis of certain characteristics. For example, all bikes are two-wheelers and all cars are four wheelers. And since both are vehicles, so their super-type entity is ‘vehicle’.

29Capture

21.What is data sparsity and how it effect on aggregation?

  • Data sparsity is term used for how much data we have for a particular dimension/entity of the model.
  • It affects aggregation depending on how deep the combination of members of the sparse dimension make up.
  • If the combination is a lot and those combination do not have any factual data then creating space to store those aggregations will be a waste as a result, the database will become huge.