Interview Questions.

Top 100+ Data Modeling Interview Questions And Answers

fluid

Top 100+ Data Modeling Interview Questions And Answers

Question 1. What Is Data Modeling?

Answer :

A records model is a conceptual illustration of commercial enterprise requirement (logical information version) or database items (bodily) required for a database and are very effective in expressing and communicating the commercial enterprise requirements and database objects. The approach by way of which data fashions are created is called as data modeling.

Question 2. What Does Data Model Contain?

Answer :

Logical Data Model: Entity, Attributes, Super Type, Sub Type, Primary Key, Alternate Key, Inversion Key Entry, Rule, Relationship, Definition, business rule, and many others
Physical Data Model: Table, Column, Primary key Constraint, Unique Constraint or Unique Index, Non Unique Index, Check Constraint, Default Value, Foreign Key, remark and so on.
Data Warehouse ETL Toolkit Interview Questions
Question 3. What Is A Logical Data Model And Logical Data Modeling?

Answer :

A logical data model is the version of a statistics version that represents the enterprise necessities (entire or part of an company). This is the real implementation and extension of a conceptual information model. Logical Data Models include Entity, Attributes, Super Type, Sub Type, Primary Key, Alternate Key, Inversion Key Entry, Rule, Relationship, Definition etc. The technique by way of which logical statistics models are created is referred to as as logical information modeling.

Question four. What Is A Physical Data Model And Physical Data Modeling?

Answer :

Physical data version consists of all required tables, columns, relationship, database residences for the bodily implementation of databases. Database performance, indexing strategy, and bodily garage are vital parameters of a bodily version. The critical or fundamental item in a database is a desk which is composed or rows and columns. The technique via which bodily data fashions are created is known as as bodily records modeling.

Data Warehouse ETL Toolkit Tutorial
Question five. What Is The Difference Between A Logical And Physical Data Model?

Answer :

Logical statistics modeler works with the client, his identify can be a logical data modeler or a physical information modeler or combination of both. A logical data modeler  designs the statistics version to match commercial enterprise necessities, creates and keeps the research statistics, compares the variations of statistics version, continues trade log, generate reviews from facts  model and while a bodily data modeler has to know approximately the source and goal databases properties.

A physical data modeler have to recognise the technical-knowledge  to create data fashions from current databases and to song the facts fashions with referential integrity, alternate keys, indexes and a way to suit indexes to SQL code. It  might be right if the physical information modeler is aware of approximately replication, clustering and so on.

Teradata Interview Questions
Question 6. What Is A Table (entity)?

Answer :

Data stored in shape of rows and columns is known as as desk. Each column has datatype and based totally at the scenario, integrity constraints are enforced on columns.

Question 7. What Is A Column (characteristic)?

Answer :

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

Teradata Tutorial SQL Server 2008 Interview Questions
Question 8. What Is A Row?

Answer :

Row also called tuple or report is the horizontal alignment of the statistics.

Question 9. What Is Er (entity Relationship) Diagram Or Erd?

Answer :

ER diagram is a visual representation of entities and the relationships among them. In a information version, entities (tables) seem like square boxes or square packing containers, which contain attributes and those entities, are linked through traces (relationship).

Data Warehousing Interview Questions
Question 10. What Is A Primary Key Constraint?

Answer :

Primary key constraint is imposed on the column facts to avoid null values and replica values. Primary Key=Unique + Not Null. Example: social protection variety, financial institution account range, bank routing variety

SQL Server 2008 Tutorial
Question 11. What Is A Composite Primary Key Constraint?

Answer :

When multiple column is part of the number one key, it is called as composite number one key constraint.

Oracle Data Integrator (ODI) Interview Questions
Question 12. What Is A Surrogate Key?

Answer :

In ordinary practice, a numerical characteristic is enforced a primary key which is referred to as as surrogate key.  Surrogate secret is an alternative to natural keys. Instead of having primary key or composite primary keys, the information modelers create a surrogate key; that is very beneficial for creating SQL queries, uniquely become aware of a file and top performance.

Data Warehouse ETL Toolkit Interview Questions
Question thirteen. What Is A Foreign Key Constraint?

Answer :

Parent desk has number one key and a overseas key constraint is imposed on a column in the child table.  The overseas key column price within the toddler table will always discuss with number one key values inside the determine table.

Data Warehousing Tutorial
Question 14. What Is A Composite Foreign Key Constraint?

Answer :

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

Question 15. What Are The Important Types Of Relationships In A Data Model?

Answer :

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

Database Design Interview Questions
Question 16. What Is Identifying Relationship?

Answer :

Usually, in a facts version, determine tables and infant tables are gift. Parent desk and toddler desk are related by means of a relationship line. If the referenced column inside the toddler desk is part of the primary key in the toddler desk, relationship is drawn via thick lines by way of connecting those two tables, that is called as identifying dating.

Performance Management Tutorial
Question 17. What Is Non-figuring out Relationship?

Answer :

Usually, in a information version, discern tables and baby tables are gift. Parent desk and baby desk are linked by means of a dating line. If the referenced column within the toddler table is a now not part of the number one key and standalone column inside the baby table, relationship is drawn via dotted strains through connecting those  tables, which is known as as non-identifying courting.

Oracle Performance Tuning Interview Questions
Question 18. What Is Self-recursive Relationship?

Answer :

A standalone column in a desk may be related to the primary key of the equal table, that's known as as recursive courting.

Teradata Interview Questions
Question 19. What Is Cardinality?

Answer :

One to One, One to many, and many to many are one-of-a-kind styles of cardinalities. In a database, excessive cardinality way greater particular values are saved in a column and vice versa.

Question 20. What Is A Conceptual Data Model And Conceptual Data Modeling?

Answer :

Conceptual information model includes all foremost entities and relationships and does now not contain an awful lot specific level of data approximately attributes and is frequently used inside the preliminary planning section. Data Modelers create conceptual records version and ahead that model to functional crew for his or her evaluate. The approach via which conceptual information fashions are created is called as conceptual information modeling.

Data analyst Interview Questions
Question 21. What Is An Enterprise Data Model?

Answer :

Enterprise information version accommodates of all entities required via an organization. The improvement of a common regular view and knowledge of facts elements and their relationships throughout the corporation is known as Enterprise Data Modeling. For better information purpose, these facts fashions are split up into situation areas.

Question 22. What Is Relational Data Modeling?

Answer :

The visible illustration of items in a relational database (usually a normalized) is called as relational information modeling. Table includes rows and columns.

Question 23. What Is Oltp Data Modeling?

Answer :

OLTP acronym stands for ONLINE TRANSACTIONAL PROCESSING. The approach by way of which data models are built for transactions is known as as OLTP records modeling. Example: all on line transactions, bank transactions, buying and selling transactions.

Performance Management Interview Questions
Question 24. What Is A Constraint? What Are The Different Types Of Constraint?

Answer :

Constraint is a rule imposed on the information.  The distinctive varieties of constraints are primary key, specific, now not null, overseas key, composite overseas key, take a look at constraint and so on.

SQL Server 2008 Interview Questions
Question 25. What Is A Unique Constraint?

Answer :

Unique constraint is imposed on the column statistics to avoid duplicate values, but it's going to comprise NULL values.

Question 26. How Many Null Values Can Be Inserted In A Column That Has Unique Constraint?

Answer :

Many null values can be inserted in an specific constraint column because one null cost is not same to some other null value.

Question 27. What Is A Check Constraint?

Answer :

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

Data Warehousing Interview Questions
Question 28. What Is Index?

Answer :

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

Question 29. What Is A Sequence?

Answer :

Sequence is a database object to generate unique wide variety.

Question 30. What Is Normalization?

Answer :

E.F. Codd gave some regulations to layout relational databases and the regulations have been centered on doing away with facts redundancy which facilitates to overcome normal statistics modeling issues. The method of eliminating records redundancy is referred to as normalization.

Question 31. What Are The Types Of Normalization?

Answer :

First ordinary shape, Second regular form, third everyday bureaucracy are three forms of normalization utilized in exercise. Beyond these ordinary paperwork, Boyce-Codd fourth and 5th ordinary forms also are to be had.

Question 32. What Is De-normalization?

Answer :

De-Normalization is a manner of adding redundancy to the records. This helps to fast retrieve the information from the database.

Question 33. What Is Data Model Meta Data?

Answer :

You can take a document of the whole statistics version, or challenge or part of the data version. The facts about various items inside the facts model is called as records version Metadata. .Data Modeling Tools have alternatives to create reports through checking the diverse alternatives. Either you can create logical records model Meta records of physical model Meta records.

Oracle Data Integrator (ODI) Interview Questions
Question 34. What Is Data Model Repository?

Answer :

Data Model and its relevant facts like entity definition, attribute definition, columns, information types and so forth. Are saved in a repository, which may be accessed by statistics modelers and the complete group.

Question 35. What Is Forward Engineering In A Data Model?

Answer :

Forward Engineering is a process through which DDL scripts are generated from the facts model. Data modeling tools have options to create DDL scripts by way of connecting with numerous databases. With those scripts, databases can be created.

Question 36. What Is Reverse Engineering In A Data Model?

Answer :

Reverse Engineering is a procedure useful for creating the facts fashions from database or scripts. Data modeling tools have alternatives to connect to the database by using which we can reverse engineer a database into a information version.

Database Design Interview Questions
Question 37. What Is A Subtype And Super Type Entity?

Answer :

An entity can be break up into many entities (sub-entities) and grouped based totally on some characteristics and each sub entity will have attributes applicable to that entity. These entities are called subtype entities. The attributes which can be not unusual to these entities are moved to a tremendous (better) degree entity, that's known as as supertype entity.

Question 38. Give Us A Non-laptop Example Of Preemptive And Non-preemptive Scheduling?

Answer :

Consider any device where human beings use some sort of resources and compete for them. The non-pc examples for preemptive scheduling the visitors at the unmarried lane street if there's emergency or there is an ambulance on the street the other vehicles deliver direction to the vehicles which are in want. The instance for preemptive scheduling is humans status in queue for tickets.

Question 39. What Is The Difference Between Star Flake And Snow Flake Schema?

Answer :

Star Schema: Well in celebrity schema you just enter your favored facts and all the number one keys of your dimensional tables in Fact desk. And reality tables number one is the union of its all dimension table key. In megastar schema dimensional tables are normally no longer in BCNF form.

Snow Flake: Its nearly like starschema however in this our measurement tables are in 3rd NF, so greater dimensions tables. And those measurement tables are connected by way of primary, overseas key relation.

Question 40. What Is Data Sparsity And How It Effect On Aggregation?

Answer :

Data sparsity is term used for the way a good deal data we have for a specific measurement/entity of the version.

It influences aggregation depending on how deep the combination of contributors of the sparse dimension make up. If the mixture is lots and people mixture do not have any genuine information then growing area to shop those aggregations might be a waste as a end result, the database will become huge.

Oracle Performance Tuning Interview Questions
Question 41. What Is The Difference Between Hashed File Stage And Sequential File Stage In Relates To Datastage Server?

Answer :

In datastage server jobs, can we use sequential filestage for a research as opposed to hashed filestage. If yes ,then what’s the advantage of a Hashed File stage over sequential filestage

search is quicker in hash documents as you could without delay get the deal with of file directly by means of hash algorithm as statistics are saved like that but in case of sequential document u have to compare all of the statistics.

Question forty two. When Should You Consider Denormalization?

Answer :

Denormalization is used while there are quite a few tables worried in retrieving facts. Denormalization is done in dimensional modelling used to construct a records ware residence. This isn't always normally carried out for statistics bases of transactional structures.

Data analyst Interview Questions
Question forty three. What Is Erd?

Answer :

Data fashions are tools used in evaluation to describe the information requirements and assumptions inside the machine from a pinnacle-down attitude. They also set the degree for the layout of databases afterward within the SDLC.

There are three primary elements in ER fashions:
Entities are the “matters” approximately which we searching for records.
Attributes are the information we gather approximately the entities.
Relationships provide the shape had to draw information from more than one entities
Question 44. What Is Third Normal Form?

Answer :

An entity is in the third regular form if it's miles in the 2d everyday form and all of its attributes aren't transitively depending on the primary key. Transitive dependence way that descriptor key attributes depend no longer most effective on the entire primary key, however additionally on different descriptor key attributes that, in turn, rely upon the number one key. In SQL phrases, the 0.33 ordinary shape means that no column within a table is depending on a descriptor column that, in flip, depends at the number one key.

For 3NF, first, the desk ought to be in 2NF, plus, we need to make certain that the non-key fields are dependent upon ONLY the PK, and no longer different non-key fields for its existence. This is very similar to to 2NF, except that now you're evaluating the non-key fields to OTHER non-key fields. After all, we recognise that the relationship to the PK is right, due to the fact we set up that in 2NF.

Question forty five. Why Are Recursive Relationships Are Bad? How Do You Resolve Them?

Answer :

Recursive relationships are an exciting and greater complicated concept than the relationships you've got visible within the previous chapters, together with a one-to-one, one-to-many, and lots of-to-many. A recursive courting takes place whilst there's a relationship among an entity and itself. For instance, a one-to-many recursive dating occurs when an worker is the manager of other employeess. The employee entity is associated with itself, and there may be a one-to-many dating between one worker (the manager) and lots of other employees (the people who report to the supervisor). Because of the more complex nature of these relationships, we are able to need slightly more complex techniques of mapping them to a schema and showing them in a fashion sheet.

Question forty six. Is This Statement True Or False? All Databases Must Be In Third Normal Form?

Answer :

In popular all agency records bases are normalized to 3nf as a way to put off redundancy and green get admission to. A information base can also be created without normalization. Hence it isn't always a mandatory that a database should be in 3nf.

Question forty seven. What Is An Artificial (derived) Primary Key? When Should It Be Used?

Answer :

Using a name as the primary key violates the principle of balance. The social safety variety is probably a valid choice, however a overseas worker might not have a social security quantity. This is a case in which a derived, instead of a herbal, primary key's suitable. A derived secret's an synthetic key which you create. A natural secret is one this is already part of the database.

Question forty eight. What Is Second Normal Form?

Answer :

An entity is within the second ordinary form if all of its attributes rely upon the complete (number one) key. In relational terms, each column in a desk must be functionally depending on the entire number one key of that desk. Functional dependency indicates that a hyperlink exists between the values in  exclusive columns.

If the value of an attribute relies upon on a column, the value of the attribute have to alternate if the fee inside the column adjustments. The attribute is a function of the column. The following causes make this more specific:

If the desk has a one-column number one key, the characteristic ought to depend on that key.

If the table has a composite primary key, the attribute ought to rely on the values in all its columns taken as a whole, now not on one or a number of them.

If the characteristic also depends on other columns, they ought to be columns of a candidate key; that is, columns that are unique in every row.

If you do not convert your version to the second one normal shape, you threat records redundancy and trouble in changing records. To convert first-ordinary-shape tables to second-everyday-form tables, eliminate columns that are not dependent on the number one key.




CFG