300+ [REAL TIME] ODS Interview Questions

1. Explain How Does Tuple-oriented Relational Calculus Differ From Domain-oriented Relational Calculus?

The tuple-oriented calculus uses a tuple variables i.e., variable whose only permitted values are tuples of that relation.

Example: QUEL The domain-oriented calculus has domain variables i.e., variables that range over the underlying domains instead of over relation. Example: ILL, DEDUCE.

2. What Is Vdl (view Definition Language) Explain?

It specifies user views and their mappings to the conceptual schema.

3. Explain Multivalued Dependency?

Multivalued dependency denoted by X–>Y specified on relation schema R, where X and Y are both subsets of R, specifies the following constraint on any relation r of R: if two tuples t1 and t2 exist in r such that t1[X] = t2[X] then t3 and t4 should also exist in r with the following properties

• t3[x] = t4[X] = t1[X] = t2[X]
• t3[Y] = t1[Y] and t4[Y] = t2[Y]
• t3[Z] = t2[Z] and t4[Z] = t1[Z]

where [Z = (R-(X U Y)) ]

4. Tell Me What Is Degree Of A Relation?

It is the number of attribute of its relation schema.

5. What Is A Relationship?

It is an association among two or more entities.

Relationship Set:
 The collection (or set) of similar relationships.

Relationship Type:
 Relationship type defines a set of associations or a relationship set among a given set of entity types.

Degree of Relationship Type:
 It is the number of entity type participating.

6. What Is Operational Data Store?

An operational data store (or “ODS”) is a database designed to integrate data from multiple sources to make analysis and reporting easier.

7. Explain A Relation Schema And A Relation?

A relation Schema denoted by R(A1, A2, ?, An) is made up of the relation name R and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let ‘r’ be the relation which contains set tuples (t1, t2, t3, …, tn). Each tuple is an ordered list of n-values t=(v1,v2, …, vn).

8. What Is A Functional Dependency F Said To Be Minimal?

• Every dependency in F has a single attribute for its right hand side.
• It cannot replace any dependency X –>A in F with a dependency Y–> A where Y is a proper subset of X and still have a set of dependency that is equivalent to F.
• We cannot remove any dependency from F and still have set of dependency that is equivalent to F.

9. Explain Functional Dependency?

Functional dependency is denoted by X –> Y between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuple that can form a relation state r of R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] = t2[Y]. This means the value of X component of a tuple uniquely determines the value of component Y.

10. Explain Ddl (data Definition Language)?

A data base schema is specified by a set of definitions expressed by a special language called DDL.

11. Explain Normalization?

It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties

• Minimizing redundancy
• Minimizing insertion, deletion and update anomalies.

12. What Are The Basic Stages Of A Data Warehouse?

The first stage to build a data warehouse is the initial data introduction, typically this can be achieved by copying some operational database. This is called and offline operational database. Then, we have to feed new sets of data to the newest created data warehouse. Therefore, this database is updated with large sets of data in a regular time basis (week, month). With this step, we’ve successfully built a offline data warehouse.
To achieve a Real-time data warehouse you have to insert the operational data in real time. When this is integrated with the application, reporting on the data, it’s called a Integrated data warehouse.

13. Explain The Slicing Operation.

The slicing operation on a OLAP Cube establishes a single value for one of the dimensions of the cube, selecting all the data that corresponds to the selected value.
So, by executing a slice on the cube we get all the selected dimension and fact information for the specific value assigned.

14. Explain The Dicing Operation

Dicing on OLAP Cubes consists on choosing an interval of values for some of the dimensions representing in the cube, and selecting the data that corresponds to those intervals.
This operation creates a subset of the cube which contains the data between the intervals.

15. Explain The Roll Up Operation.

The roll-up operation performs some computing rules on the data of a OLAP cube specific dimension, returning the computed information to the end user.
These applied rules can be defined and summarize the information on that specific dimension.

16. Explain The Drill-up/drill-down Operation

These operations allow the exploration of information between the levels of data presented on dimensions and facts on the data warehouse.
It can select summarized information or the details that compose that data aggregation.

17. Explain The Pivoting Operation

Pivoting allows the rotation of the cube on its dimensions providing the user a different point of view of the explored data.
The cube can be rotated on every face.

18. Explain The Concept Of Data Mart.

Data mart is a specific group of data linked to a subject, which is part of a specific data warehouse. Therefore, a data warehouse have multiple data marts.
Basically a data mart is a small data warehouse with condensed information about a specific subject and it’s relationships. Usually each data mart is related to a department, business unit or something that can function individually within a data warehouse.

19. Which Are The Reasons To Create A Data Mart?

There are various reasons that lead to a the creation of a data mart. The most important ones are:

• Create a data specific environment, providing easy access to it
• Easy to create
• Data is more relevant to users having only the essential information
• Lower cost than creating a whole data warehouse

20. What Is The Difference Between Operational Data Store And Staging Area?

Operation Data Store or ODS means the current data that is required to do quick analysis or near realtime reporting.

On the other side Staging is a dump of all data that you gather form multiple and heterogeneous Sources, you cleanse this data, apply multiple business rules, filter it and then push it to your Data Warehouse or ODS.

An ODS sits between your Staging/Factory and Data Warehouse. It gets a snapshot of most recent data, like if its a:

– Telecom provider it would store data of around 1 month to give you quick and detailed analysis on multiple calls/sms’s done on a daily basis.
– Bank would store data for 3-6 months to 1 year for your day today transactions. That’s why if you require 1-2 years of transaction details it takes bank 1-2 days to provide you that list separately.
If both will not manage an ODS it will become very difficult to provide quick details to their customers, and it will be a very slow process and also an overhead on their servers also.