Q12) What is a surrogate key?
In normal practice, a numerical attribute is enforced as a primary key which is called a surrogate key. A surrogate key is a substitute for natural keys.
Instead of having a 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.
4) List out various design schema in data modelling
There are two different types of data modelling schemes schemas: 1) Star Schema, and 2) Snowflake Schema
7 Define materialized views in Redshift.
An SQL query on one or more base tables is the foundation of a materialized view, which stores a precomputed result set. The same SELECT queries you use to query other tables and views in the database can query a materialized view.
Q39) What is the difference between star flake and snowflake schema?
Star Schema: Well in star schema you just enter your desired facts and all the primary keys of your dimensional tables in the Fact table. And fact tables primary is the union of its all dimension table key. In a star schema, dimensional tables are usually not in BCNF form.
Snow Flake: Its almost like star-schema but in this, our dimension tables are in 3rd NF, so more dimensions tables. And these dimension tables are linked by primary, foreign key relations.
ERwin Data Modeler Interview Questions
The Name Hardening Wizard offers a technique to prevent name inheritance and avoid name changes or resets inside a model. The object types and instances you need to harden logical names, physical names, or both within the model, as opposed to using the inherited name values, can be determined using the wizard.
2 What are some of the benefits of a data model?
The following are some of the benefits of a data model-
43) What is predictive modelling analytics?
The process of validating or testing a model which would used to predict testing and validating outcomes. It can be used for machine learning, artificial intelligence, as well as statistics.
4 What is the difference between Amazon DynamoDB and other NoSQL databases?
Amazon DynamoDB is a cloud-based database with no physical location. It is scalable enough to suit the needs of high-performance applications. You can set up security, back up all in case of a crash or issues, save data in various locations, and even export all of your data if you want to.
Amazon DynamoDB is a hosted database for applications that require regular updates without the assistance of a database administrator. DynamoDB takes care of all database administration on their servers, minimizing the need for manual tasks like backups and replication.
2 Differentiate between OLAP and OLTP databases.
Database SQL workloads can be divided into two categories:
Online-transactional processing (OLTP): These are simple queries with high concurrency and low latency that read or change a few records simultaneously while ensuring data integrity, such as bank account transactions.
Online Analytical Processing (OLAP): These workloads, similar to periodic reporting, are often complicated queries (including aggregates and joins) that necessitate scanning a large number of records.
|
Online-transactional processing (OLTP) |
Online Analytical Processing (OLAP) |
|
OLAP databases do not allow normalization in tables. |
|
OLAP supports the data warehouse approach. |
|
OLAP has a response time of seconds to minutes. |
|
OLAP databases comprise large data volumes. |
|
OLAP is a database that allows you to analyze business metrics by category and attribute. |