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 represents logical data model tools and physical data model: business requirements and database objects, respectively, required for a database and are essential in communicating and expressing the database objects and business requirements. The proposal through which data models are created is called as data modelling.
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 complexity or difficulty and details increase from the conceptual data model to the physical data model. On the other hand, the conceptual data model shows a fundamental 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 table’s primary key is connected with the standalone column in the same table, which is called 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 modellers will create this key, identifying a record, creating SQL queries software, and performing well.
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 modelling 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 data models from a database or scripts. Data modelling 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 modelling, Physical data model and Physical data modelling?
A Logical data model is the type of data model that shows the entire business requirements 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 modelling.
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 modelling.
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 on how deep 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 some features can group them. Each sub-entity will be having attributes, which are relevant to that entity. These entities are called subtype entities. The common attributes to every entity are placed to a higher or super level entity, which is called a supertype entity.
10. What are Relational data modelling, conceptual Data model, and conceptual data modelling?
Relational data modelling 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 do not have any detailed information about attributes and is very used at the beginning of the planning phase. Data modellers create this data model and pass that model to the functional team for review. The approach through which conceptual data models are created is termed as conceptual data modelling.
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 the Interview. You may also look at the following articles to learn more –