Introduction to Data Modeling Interview Questions And Answers
So if you have finally found your dream job in Data Modeling but are wondering how to crack the 2020 Data Modeling Interview and what could be the probable Data Modeling Interview Questions. Every interview is different and the scope of a job is different too. Keeping this in mind we have designed the most common Data Modeling Interview Questions and answers to help you get success in your interview.
Top 10 Data Modeling Interview Questions and answers
Below are the important set of Data Modeling Interview Questions that are asked in an interview
1. What do you understand by the term ‘Data Modeling’?
A data model is a representation of logical data model tools and physical data model that is, business requirements and database objects respectively required for a database and are very important in communicating and expressing the database objects and business requirements. The proposal through which data models are created is called as data modeling.
2. What are the different types of data models?
We have three different types of data models. They are
- Conceptual data model: It will be just depicting entity names and entity relationships.
- Logical data model: It will be depicting attributes, entity names, primary keys, entity relationships, and foreign keys in each entity.
- Physical data model: It will be showing us primary keys, foreign keys, column names, column data types, table names. This generally helps us to understand how the model will be actually implemented in the database.
The level of complexity or difficulty and details increases from the conceptual data model to the physical data model. On the other hand, the conceptual data model shows a very basic high level of design while the physical data model gives a very detailed view of design.
3. What are the important types of relationships in a data model and explain them?
There are three types of relationships in a data model. They are
Generally, in a data model, we have parent tables and child tables. And they both are connected by a relationship line.
If the presence of an entity or row in a child table depends on a row or entity in a parent table, then the relationship is represented by a thick line by connecting these two tables. This is called Identifying relationships.
If the primary key attributes of the parent should not become the primary key attributes of the child, then the relationship is represented by dotted lines by connecting these two tables. This is called a Non-identifying relationship.
The primary key of the table is connected with the standalone column in the same table, which is called as a Recursive relationship.
4. What is a Surrogate key?
In practice, a numerical attribute is enforced by a primary key which is called a surrogate key. This key is a replacement for natural keys, instead of having a primary key or composite primary keys, the data modelers will create this key, which is very useful in identifying a record, creating SQL queries software and good performance.
5. What are Forward Engineering and Reverse Engineering in a data model?
Forward Engineering is a process by which Data Definition Language(DDL) scripts are generated from the data model. Data modeling tools have some options to create DDL scripts by coupling or connecting with several databases. By using these scripts, databases can be created.
Reverse Engineering is a process used for creating the data models from a database or scripts. Data modeling tools have some options to connect with the database through which we can reverse engineer a database into a data model.
6. What is the Logical data model, Logical data modeling, Physical data model and Physical data modeling?
A Logical data model is the type of data model that shows the business requirements that are entire or part of an organization. This is the actual implementation and extension of a conceptual data model. They contain Attributes, Entity, Super Type, Alternate key, Primary key, Rule, Relationship, Inversion key entry, etc. The approach through which logical data models are created is called logical data modeling.
The physical data model includes all essential tables, relationships, database properties for the implementation of databases. Physical storage, Indexing strategy, Performance are essential parameters of a physical model. The important object in a database is a table that contains rows and columns. The approach through which physical data models are created is called as physical data modeling.
7. What is the main difference between Snow Flake Schema and Star Flake Schema?
- Snowflake Schema: It is very similar to Star schema but in this dimension tables are in third Normal form(NF), so we can split into multiple dimension tables. And these tables are linked by a foreign key, primary key relation.
- Star Flake Schema: In star Flake schema, we just give the required or useful facts and also we give all the primary keys of dimensional tables and the fact table. The primary function of the fact table is the integration of its all dimensional table key. In this schema, dimensional tables are not usually in BCNF(Boyce-Codd normal form) form.
8. Describe Data Sparsity and how does it impact on aggregation?
It is a term that is used for how much amount of data we have for a specified dimension or entity of the model. It impacts aggregation that depends upon how deep does the joining of members of the sparse dimension was formed. If the merging is a lot, and these combinations are empty or do not have any type of data, then to generate space to store those aggregations would be useless and the database also will become huge.
9. Describe the subtype entity and supertype entity?
An entity can be broken down or split into many sub-entities and they can be grouped by some features. Each sub-entity will be having attributes, which are relevant to that entity. These entities are called as subtype entities. The attributes which are common to every entity are placed to a higher or super level entity, which is called as a supertype entity.
10. What are Relational data modeling, conceptual Data model, and conceptual data modeling?
Relational data modeling is the visual representation of objects in a relational database which is usually normalized. And the table contains rows and columns.
The conceptual data model has all major relationships and entities, which does not have any detailed level of information about attributes and is very used in the beginning of the planning phase. Data modelers create this data model and pass that model to the functional team for the review. The approach through which conceptual data models are created is termed as conceptual data modeling.
This has been a guide to Data Modeling Interview Questions. Here we have listed down the top 10 interview questions and answer that are frequently asked in Interview. You may also look at the following articles to learn more –