Data Modeling Interview Question and Answer Videos

Video Tutorial 1:

What is a Data Model and Data Modeler Responsibilities?

What’s in it: Data Model, High Level Data Modeler Responsibilities along with images of a conceptual data model, logical data model and physical Data Model is explained using Erwin Data Modeling Tool.

Video Tutorial 2:

Explain Data Modeling Development Life Cycle?

What’s in it: This question is asked by the Interviewers. Our video explains step by step process in Data Modeling Development Life Cycle. This life cycle is common for RDMS Data Modeling and Dimensional Data Modeling.

Video Tutorial 3:

What is an ER Diagram?

What’s in it: ER Diagram also know as Erwin Relationship Diagram is explained with a data model created by using Erwin Data Modeling Tool. Primary Key Constraint, Composite Primary Key Constraint, Foreign Key Constraint, Composite Foreign Key Constraint are explained here in this Data Modeling Video Tutorial.

Video Tutorial 4:

What is a Conceptual Data Model?

What’s in it: This is the first phase in designing the data model in the Data Modeling Development Life and is explained in Detail with image of Erwin Data Model.

Video Tutorial 5:

What is a Logical Data Model?

What’s in it: The is second phase in designing the data model in the Data Modeling Development Life Cycle and is explained in details

Video Tutorial 6:

What is the difference between a Logical Data Model and Physical Data Model?

What’s in it: The differences between Logical Data Model and Physical Data Model is tabulated and explained with the help of a data model created from the Erwin Tool.

Video Tutorial 7:

What is an Entity and Attribute?

What’s in it: Entity and Attribute are explained with a Diagram.

Video Tutorial 8:

What is Identifying and Non-Identifying Relationship?

What’s in it: Identifying and Non-Identifying Relationship are explained with the help of a small data model drawn from Erwin Data Modeling Tool. Difference between those two are also explained.

Video Tutorial 9:

What is Forward Engineering & What is Reverse Engineering?

What’s in it: Forward Engineering, Reverse Engineering, SuperType and SubType are explained.

Video Tutorial 10:

What is Data Model Meta Data & Data Model Repository

What’s in it: Data Model Meta Data & Data Model Repository are explained in this video tutorial.

Video Tutorial 11:

What is the process to create a Data WareHouse Schema and What are the steps involved in designing the data mart?

What’s in it: This video explains important points in designing the Data Mart and Data Warehouse Schema.

Video Tutorial 12:

What is Dimensional Data Model and a Star Schema?

What’s in it: This video explains the basics of Dimensional Data Model and Schema with OLA use case as the example.

Video Tutorial 13:

What is ETL? / What is Datawarehouse? / What is DataMart?

What’s in it: Basics of ETL, Datawarehouse and Datamart are explained in this Data Modeling Video Tutorial.

Video Tutorial 14:

What is Check Constraint? / What is a Sequence? / What is an Index?

What’s in it: Brief explanation of Check Constraint, Sequence, and Index is explained.  Also NULL values in Unique constraint are explained.  The above topic is an important question in the Data Modeling Interview..

Video Tutorial 15:

What is OLTP Data Modeling? / What are the different types of constraints? / What is an Unique Constraint?

What’s in it: Brief explanation about OLTP data modeling and constraint. This is an important question in Data Modeling Interview.

Video Tutorial 16:

What is a Foreign Key Constraint? / What is a composite Foreign Key constraint?

What’s in it: Foreign Key Constraint is explained in a detailed manner by taking an example from IBM.

Video Tutorial 17:

Erwin Data Modeling Interview Questions – Part 1

What’s in it:

This video helps with the list of interview questions that can be asked in creating :
Conceptual data model by Erwin.
Logical data model by Erwin.
Physical data model by Erwin.

Video Tutorial 18:

Dimensional Data Modeling Interview Questions – Part 1

What’s in it:

These Dimensional Data Modeling Questions are related to Data Warehouse and Data Mart.
When you work as an ETL developer or BI developer, these questions will be asked in the interview.

Data Modeling Interview Questions

General Questions:

  • Have you exported objects from the data model to different database? What is the use of it?
  • What is aggregated data and why do you need aggregated data?
  • What is the role of a logical data modeler and physical data modeler?
  • How do you gather requirements for creating data model?
  • What is the naming convention you follow?
  • What are the different notations that can be used?
  • What is Cardinality?
  • What is identifying and non identifying relationship?
  • Why do you need phrases in a data model?
  • Have you generated reports for data models? Why do you need reports from data model?
  • How you will recover if data modeling file is corrupted?
  • What is Meta data? What kind of Meta data do you capture in a data model?
  • Explain a difficult situation in data modeling and how did you resolve it?

Logical Data Modeling Questions:

  • What is logical data modeling?
  • What is conceptual data modeling? Why do you need that?
  • What is enterprise data modeling? Why do you need that?
  • What is relational data modeling? When you will use relational data modeling?
  • When you will go for relational data modeling?
  • What is OLTP?
  • What is OLAP data modeling?
  • What is dimensional data modeling?
  • What is a dimension?
  • What is a slowly changing dimension? What are the types of slowly changing dimension? Which one you will use it?
  • What is super type and sub type? Where you will use in logical or physical data model? If you generate the super type and sub type structure it in a database, what are the consequences?
  • What is Star Schema? When you use Star Schema?
  • What is Snow Flake? When you will use Snow Flake Schema?
  • What is the difference between Snow Flake and Star Schema?
  • Can you compare logical data model with data base? How?
  • How you will find out the difference between the logical data models created previous month and current month?
  • What is Data Ware House?
  • What is Data Mart?
  • What is the difference between data ware house and data mart?

Physical Data Modeling Questions:

  • What is physical data modeling?
  • What is the difference between logical and physical data modeling?
  • Can you compare physical data model with data base? How?
  • How version controlling is done in a data model?

 

Data Modeling Frequently Asked Interview Questions And Answers – Part 1

The following data modeling questions and answers are conceptual questions that are asked during the data modeler interview.

 1. What is data modeling?

A data model is a conceptual representation of business requirement (logical data model) or database objects (physical) required for a database and are very powerful in expressing and communicating the business requirements and database objects. The approach by which data models are created is called as data modeling.

2. What does data model contain?

Logical Data Model: Entity, Attributes, Super Type, Sub Type, Primary Key, Alternate Key, Inversion Key Entry, Rule, Relationship, Definition, business rule, etc

Physical Data Model: Table, Column, Primary key Constraint, Unique Constraint or Unique Index, Non Unique Index, Check Constraint, Default Value, Foreign Key, comment etc.

Please refer http://www.learndatamodeling.com/diff_lpdm.php

3. What is a logical data model and logical data modeling?

A logical data model is the version of a data model that represents the business requirements (entire or part of an organization). This is the actual implementation and extension of a conceptual data model. Logical Data Models contain Entity, Attributes, Super Type, Sub Type, Primary Key, Alternate Key, Inversion Key Entry, Rule, Relationship, Definition etc. The approach by which logical data models are created is called as logical data modeling.

4. What is a physical data model and physical data modeling?

Physical data model includes all required tables, columns, relationship, database properties for the physical implementation of databases. Database performance, indexing strategy, and physical storage are important parameters of a physical model. The important or main object in a database is a table which consists or rows and columns. The approach by which physical data models are created is called as physical data modeling.

5. What is the difference between a logical and physical data model?

Please refer http://www.learndatamodeling.com/diff_lpdm.php

 6. What is a table (entity)?

Data stored in form of rows and columns is called as table. Each column has datatype and based on the situation, integrity constraints are enforced on columns.

7. What is a column (attribute)?

Column also known as field is a vertical alignment of the data and contains related information to that column.

8. What is a row?

Row also known as tuple or record is the horizontal alignment of the data.

9. What is ER (entity relationship) diagram or ERD?

ER diagram is a visual representation of entities and the relationships between them. In a data model, entities (tables) look like square boxes or rectangular boxes, which contain attributes and these entities, are connected by lines (relationship).

10. What is a primary key constraint?

Primary key constraint is imposed on the column data to avoid null values and duplicate values. Primary Key=Unique + Not Null. Example: social security number, bank account number, bank routing number

11. What is a composite primary key constraint?

When more than one column is a part of the primary key, it is called as composite primary key constraint.

12. What is a surrogate key?

In normal practice, a numerical attribute is enforced a primary key which is called as surrogate key.  Surrogate key is a substitute for natural keys. Instead of having primary key or composite primary keys, the data modelers create a surrogate key; this is very useful for creating SQL queries, uniquely identify a record and good performance.

13. What is a foreign key constraint?

Parent table has primary key and a foreign key constraint is imposed on a column in the child table.  The foreign key column value in the child table will always refer to primary key values in the parent table.

14. What is a composite foreign key constraint?

When group of columns are in a foreign key, it is called as composite foreign key constraint.

15. What are the important types of Relationships in a data model?

Identifying, Non-Identifying Relationship, Self-Recursive relationship are the types of relationship.

16. What is identifying relationship?

Usually, in a data model, parent tables and child tables are present. Parent table and child table are connected by a relationship line. If the referenced column in the child table is a part of the primary key in the child table, relationship is drawn by thick lines by connecting these two tables, which is called as identifying relationship.

17. What is non-identifying relationship?

Usually, in a data model, parent tables and child tables are present. Parent table and child table are connected by a relationship line. If the referenced column in the child table is a not a part of the primary key and standalone column in the child table, relationship is drawn by dotted lines by connecting these two tables, which is called as non-identifying relationship.

18. What is self-recursive relationship?

A standalone column in a table will be connected to the primary key of the same table, which is called as recursive relationship.

19. What is cardinality?

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 conceptual data model and conceptual data modeling?

Conceptual data model includes all major entities and relationships and does not contain much detailed level of information about attributes and is often used in the initial planning phase. Data Modelers create conceptual data model and forward that model to functional team for their review. The approach by which conceptual data models are created is called as conceptual data modeling.

21. What is an enterprise data model?

Enterprise data model comprises of all entities required by an enterprise. The development of a common consistent view and understanding of data elements and their relationships across the enterprise is referred to as Enterprise Data Modeling. For better understanding purpose, these data models are split up into subject areas.

22. What is relational data modeling?

The visual representation of objects in a relational database (usually a normalized) is called as relational data modeling. Table contains rows and columns.

23. What is OLTP data modeling?

OLTP acronym stands for ONLINE TRANSACTIONAL PROCESSING. The approach by which data models are constructed for transactions is called as OLTP data modeling. Example: all online transactions, bank transactions, trading transactions.

24. What is a constraint? What are the different types of constraint?

Constraint is a rule imposed on the data.  The different types of constraints are primary key, unique, not null, foreign key, composite foreign key, check constraint etc.

25. What is a unique constraint?

Unique constraint is imposed on the column data to avoid duplicate values, but it will contain NULL values.

 26. How many null values can be inserted in a column that has unique constraint?

Many null values can be inserted in an unique constraint column because one null value is not equal to another null value.

27. What is a check constraint?

Check constraint is used to check range of values in a column.

28. What is index?

Index is imposed on a column or set of columns for fastest retrieval of data.

29. What is a sequence?

Sequence is a database object to generate unique number.

Top Data Modeling Interview Questions asked in 2018:

  • what is Canonical Data Model?
  • What is Optionality?
  • What is Cardinality?
  • How columnar databases are different from the RDBMS Database?
  • What are the deliverables of a Data Modeler?
  • How do you present the data model to the business team and the technical team?
  • How do you maintain the data model after the project implementation?
  • How do you define the business rules in the data model?
  • Can you do shared role?
  • What is Enterprise Data Architecture?
  • What is Big Data Data Modeling?
  • What kind of knowledge you have in NO SQL Databases?
  • What is the difficult scenario you faced in Data Modeling?
  • What is the document equivalent to RTM (Requirement Traceability Matrix) in Data Modeling?
  • Which approach you followed: Inmon’s approach or Ralph Kimball’s approach? & Why?
  • As a Data Modeler, how can you ensure that the Data is available to customer 24/7, 365 days and the Data is most reliable?
  • What is the data modeling tool that you have worked?
  • How will you reverse engineer using a data dictionary in excel format?
  • Can we reverse engineer, upload an Excel file with entity name, attribute name and datatype to create a data model?
  • Why do you have a flat file in Data Warehouse environment without primary keys?
  • What is the difference between a domain and a datatype?
  • Under which scenario you would use recursive relationship, why and why not?for example: Hierarchical data set or Master-Detail data set?