Q: What is ‘Data Modeling’?
A : process of conceptualizing and visualising how data will be gathered, stored, and used within business operations
Q: Different types of data models?
A: Conceptual data model: entity and entity relationships, Logical data model: attributes, entity names, primary keys, entity relationships, and foreign keys, Physical data model: actually implemented in the database.
Q: Types of relationships?
A:
Recursive relationship - primary key points to another column in same table. e.g. one employee manages another employee.
Q: What is a Surrogate key?
A: A key to uniquely identify a row but it's not part of business data.
Q: Forward Engineering and Reverse Engineering ?
A: Forward:use data modelling tools to generate DDL.
Backward: use DB tools to generate DDL using it's existing table and structures.
Q: Snow Flake Schema and Star Flake Schema?
A: star flak has more redundancy than snow flake
star schema is a data model that stores information in multiple table types: a single fact table and multiple dimensional tables
snowflake schemas split dimensional tables into further dimensional tables (also called lookup tables), in 3rd normal form
Q. Data Sparsity and aggregation?
A: The amount of empty or null data in data set. Problem is what should we do with empty data? treat it as 0 ? is it useful for aggregation?? Should fill forward?
Q. Describe the subtype entity and supertype entity?
A. Subtype entity is a child entity of supertype entity. It inherits attributes of supertype plus its own attributes. e.g employee is a supertype, fulltime employee or contract employee can be a subtype.
Q: What is ‘Data Modeling’? A : process of conceptualizing and visualising how data will be gathered, stored, and used within business operations
Q: Different types of data models? A: Conceptual data model: entity and entity relationships, Logical data model: attributes, entity names, primary keys, entity relationships, and foreign keys, Physical data model: actually implemented in the database.
Q: Types of relationships? A:
Recursive relationship - primary key points to another column in same table. e.g. one employee manages another employee.
Q: What is a Surrogate key? A: A key to uniquely identify a row but it's not part of business data.
Q: Forward Engineering and Reverse Engineering ? A: Forward:use data modelling tools to generate DDL. Backward: use DB tools to generate DDL using it's existing table and structures.
Q: Snow Flake Schema and Star Flake Schema? A: star flak has more redundancy than snow flake star schema is a data model that stores information in multiple table types: a single fact table and multiple dimensional tables
snowflake schemas split dimensional tables into further dimensional tables (also called lookup tables), in 3rd normal form
Q. Data Sparsity and aggregation? A: The amount of empty or null data in data set. Problem is what should we do with empty data? treat it as 0 ? is it useful for aggregation?? Should fill forward?
Q. Describe the subtype entity and supertype entity? A. Subtype entity is a child entity of supertype entity. It inherits attributes of supertype plus its own attributes. e.g employee is a supertype, fulltime employee or contract employee can be a subtype.
https://www.keboola.com/blog/star-schema-vs-snowflake-schema