- 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.
- 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
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.
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’.
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.