Chapter 3 - Databases and Data Acquisition

Question 1

What does an entity contain? What kind of database does it relate to?

Answer 1

An entity contains data about a single subject. Think of it as a noun that describes a person, place or thing. Used in relational databases.

Question 2

What’s an entity instance?

Answer 2

It’s like a single record or instances of an entity subject. Like picking out one record in a table about pets.

Question 3

What is the name given to describe the relationship between two entities?

Answer 3

Cardinality

Question 4

Which types of entity relationships are comparatively complex and rare?

Answer 4

Unary and Ternary.

Question 5

What is the difference between an entity instance and an entity attribute?

Answer 5

An entity attribute is akin to the column heading in a table. An entity instance is akin to a single record in a table, for example, the record about one particular customer. The record contains multiple entity attributes.

Question 6

Entities become a separate _____ in a database

Answer 6

table

Question 7

What two things together link data between two tables?

Answer 7

An associative table and a foreign keys

the F-keys link to the primary keys in the two related tables

Question 8

A foreign key helps to enforce what?

Answer 8

foreign key enforces referential integriy, or how consistent the data is in related tables

Question 9

What describes the modifications necessary to to create tables for storing data?

Answer 9

A schema

Question 10

A table that contains a primary key in more than one column has a primary key called what?

Answer 10

A composite primary key

Question 11

You need to store and retrieve simple data quickly that doesn’t have a lot of structure, which of the two non-relational databases would you choose?
Key-value or Column-family

Answer 11

Key-value

Question 12

Name 2 high level key benefits of a column-family database?

Answer 12

1) DYNAMIC/FLEXIBLE - Column data can vary dynamically between key values
2) ABILITY TO SCALE - can process vast amounts of data

Question 13

What kind of database is best at exploring relationships between pieces of data?

Answer 13

Graph databases

Question 14

What database type do the below relate to and what are they?

1) NODES -
2) PROPERTIES -
3) RELATIONSHIPS -

Answer 14

GRAPH DATABASES

2) 1) NODES - these are the equivalent of an entity instance; a person or a thing.
2) PROPERTIES - these store attributes about a node
3) RELATIONSHIPS - these are arrows connecting nodes denoting their relationship to each other

Question 15

What kind of database is often behind website features like related or RECOMMENDED items?

Answer 15

Graph database.

Question 16

What database process is employed to reduce data redundancy?

Answer 16

Normalization

Question 17

List the 5 rules of 1NF

Answer 17

1) KEY - a table without a primary key is not permitted
2) GROUPS - Repeating groups are not permitted
3) ORDER - Row order is not permitted
4) DATATYPES - Mixing of datatypes in a column is not permitted
5) VALUES - non-atomic values are not permitted

Question 18

What level of normalization does this statement describe? “every attribute in a table should depend on the key, the whole key. and nothing but the key”?

Answer 18

3NF - third normal form

Question 19

it doesn’t use a schema
it stores data in raw format
it requires specialist knowledge (data scientists) about the data to operate

Answer 19

a datalake

Question 20

a subset of a datawarehouse that is data that serves a particular part or subject within the organization as opposed to data that serves the entire organziation

Answer 20

A Datamart

Question 21

List 2 key features of the Star Schema design pattern

Answer 21

1) Denormalized to improve read performance over large datasets
2) Uses a central fact table with dimension tables as spokes.

Question 22

Regarding OLAP databases, which schema design pattern would you pick if storage was a concern?

Answer 22

Snowflake schema

Question 23

Regarding design patterns, which design pattern does a datawarehouse usually use and why and which design pattern does a datamart usually use and why?

Answer 23

1) Datawarehouses usually use snowflake schema design patterns because they’re supplied from many differences sources.
2) Datamarts usually use the Star schema because they represent a single data subject area.

Question 24

When designing a dimension table, it is crucial to understand what to help you build it?

Answer 24

The questions that analysts will be asking about the the business or subject?

Question 25

A dimension table provides additional context to a fact table. These could be information about people, or the price of a product. The data may change over time and you'll need to add additional attributes to the table. Regardless of how quickly the data changes, what must you consider about the data when designing dimension tables?

Answer 25

whether you need to store the history/indicate whether the attribute is current or not

Question 26

what are 3 approaches mentioned by the author to handle the time dimension in tables

Answer 26

1) using a start date / end date for the attribute 2) indicator flag (shows what data is current) 3) effective date - date the attribute became effective ## Footnote you must consider the historic requirements of the data

Question 27

Name the main disadvantage of using effective date approach in creation of dimension tables

Answer 27

Itcomplicates queries because you need to calculate date math ## Footnote when you ask the question "what was the price on x date" it needs to calculate between the effective dates

Question 28

What's the key difference between ETL and ELT and what technology do they use to do it?

Answer 28

1) ETL transforms data before it enters the warehouse and often uses Python 2) ELT transforms data after it enters the warehouse and typically uses SQL

Question 29

Describe initial load, delta load and incremental load and batch window

Answer 29

1) initial load is the first load of data into the DWH 2) the delta loads are subsequent loads after the initial load 3) incremental loads are the same as delta loads 4) batch window is the time period in which you have to extract data.

Question 30

ETL and ELT are associated with what data acquisition concept?

Answer 30

Integration

Question 31

Regarding data-collection, Qualtrics is an application you can use in what type of data collection method?

Answer 31

conducting Surveys

Question 32

when acquiring qualitative data using observation, what should you be mindful of?

Answer 32

1) accounting for subconscious action 2) bias - both in the observer and observee | you must develop methods for observation to mitigate the two

Question 33

What lets you actually create a database ready to accept data from a database design?

Answer 33

SQL's Data Definition Language

Question 34

What's the difference between DDL and DML?

Answer 34

1) DDL is about creating database tables and objects 2) DML is about querying, altering and retrieving data from the database table

Question 35

What are the four CRUD data manipulation actions and their association SQL Keywords?

Answer 35

1) Create - INSERT 2) Read - SELECT 3) Update - UPDATE 4) Delete DELETE

Question 36

Which logical function could you use to convert a given value into a different name of your choice to help divide data into categories? ## Footnote For example, you want to change the M and F letters in the Sex column to Boys and Girls.

Answer 36

The IFF function | IFF(boolean_expression, true_value, false_value)

Question 37

List the 6 aggregate functions

Answer 37

1) Max 2) Min 3) Count 4) Sum 5) AVG 6) STDD

Question 38

Preventing injection attacks and reducing several queries into a single query are benefits of what?

Answer 38

Parameterization

Question 39

Name the key benefit of database indexes and one drawback

Answer 39

1) BENEFIT - they improve query speed 2) DRAWBACK - it slows down create, update and delete activity

Question 40

You want to explore historic sales data. The sales history table is huge. What's the issue with this and what solution could you emply to mitigate it?

Answer 40

Querying a very large table is inefficient. It's more efficient to create a temporary table using a SUBSET of data from the main table

Question 41

What would you look at to troubleshoot query performance?

Answer 41

The execution plan

Question 42

What should you look at to ensure you're developing an efficient query?

Answer 42

the execution plan

Question 43

Snowflake and Star schemas are also known as what label?

Answer 43

Multidimensional Schemas

Question 44

Generally speaking, Fact tables contain ______ data and dimension tables contain _____________ data?

Answer 44

1) fact tables = quantitative data 2) dimension tables = qualitative data

Question 45

Regarding data acquisition. ETL or ELT is associated with sourcing data from where?

Answer 45

a transactional system

Question 46

What are the key differences between ETL and ELT when it comes to how they transform the data?

Answer 46

1) ETL uses external technology to transform the data 2) ELT uses the database itself to transform the data

Question 47

What's the name given to the concept of moving and combining data from different sources into a a data warehouse or datalake?

Answer 47

Data Integration

Question 48

In data integration, API's return transaction data as what? And return bulk data as what?

Answer 48

1) JSON files 2) CSVs

Question 49

What were the 4 approaches mentioned in the book that mitigate performance issues for query optimization

Answer 49

1) Parameterization 2) Using data subsets and temp tables 3) Review execution plan with DB admin 4) Applying a database index

Question 50

Why is adding a time dimension to star of snowflake tables a good idea?

Answer 50

It allows handling of current and historical data

Question 51

which data collection method is good for validating business processes?

Answer 51

Observation

Question 52

if you're doing just ad-hoc analysis, what would you utilize?

Answer 52

data subsets and use temporary tables

Question 53

Star and Snowflake Schemas are designed for what kind of database?

Answer 53

OLAP databases

Question 54

Why is the the denormalized nature of OLAP databases result in quicker read performance?

Answer 54

Queries are simpler and data reads faster due to fewer table joins required due to the hub and spoke nature of the schema.

Question 55

what determines the frequency of ETL data loads?

Answer 55

Business requirements

Question 56

What defines the batch window for loading data into the datawarehouse?

Answer 56

Between the last transaction of the day and the first transaction of the following day

Question 57

__________ translates the human-readable SQL into code the database understands

Answer 57

parsing

Question 58

____________ reduces the number of times the database has to parse individual queries

Answer 58

parameterization

Question 59

Large tables without indexes are inefficient. Applying a database index helps speed queries up. Ideally you want to include all columns in the index, but if you can't, what should should you ensure when writing your select statement?

Answer 59

That the column you're selecting is covered by an index

Question 60

while indexing improves read speed of a query, it slows down what 3 activites?

Answer 60

create, update and delete ## Footnote the indexing strategy needs to match the type of database, i.e. transactional or reporting

Question 61

You find that a table where a non-key attribute is dependent on another non-key attribute. What form does this violate?

Answer 61

3NF

Question 62

When the primary key of a table is indirectly related to a non-key attribute through a key-attribute, what violation is this called?

Answer 62

transitive violation | because the key is related to a non-key attribute indirectly. ## Footnote this violates 3NF

Question 63

Match the type of normalization to the statements below: 1) All non-key attributes must depend on the key 2) All non-key attributes must depend on the whole key and nothing else!

Answer 63

1) All non-key attributes must depend on the key - 2NF 2) All non-key attributes must depend on the whole key and nothing else! - 3NF

Question 64

You may have multiple applications in your business that need to request the same data, each using the same SQL calls to the database. What issue arises from this and what method can make this more efficient?

Answer 64

**Issue** - SQL code has to be maintained in multiple places. **Solution** - implement a single API call for the specific data needed from the database. All applications can call the API instead of maintaining their own SQL.

Question 65

Frosty wants to use an web service enabled API. What must they obtain/purchase before they can use it?

Answer 65

an API key

Question 66

Webscrapers use Python / R or webscrapers use API's?

Answer 66

Webscrapers use Python / R

Question 67

re: data collection/acquisition - the act of collecting primary source data from people or machines

Answer 67

is known as Observation

Question 68

Why is the snowflake schema more efficient with data storage than star schema?

Answer 68

because it is more normalized, reducing data duplication

Question 69

Fred is looking at where data comes from, how frequently it changes, and how long it needs to persist. What is he doing?

Answer 69

Considering the life-cycle of data ## Footnote for managing data in a datawarehouse or datamart.

Question 70

Which method of handling time dimensionality keeps all pricing data in a single place (as opposed to splitting it out into a different table) and avoids using date math to get the current data?

Answer 70

Indicator flag method

Question 71

Which is the best choice if you need to get massive amounts of data from a transactional system into an OLAP system? ETL or ELT?

Answer 71

ELT

Question 72

you extract data from the source system and place it into a staging area as flat file. What Integration approach is this?

Answer 72

ETL

Question 73

what should take into account when designing the delta load strategy?

Answer 73

the batch window

Question 74

slowly changing dimension - keeps current and historic adds column to show previous value

Answer 74

Type 3

Question 75

Slowly changing dimension - keep current state

Answer 75

Type 1

Question 76

slowly changing dimension - keeps current and hisotrical changes tracks change on new row

Answer 76

Type 2

Question 77

Which symbol denotes the end of an SQL statement?

Answer 77

Semi-colon ";"

Question 78

which key enforced referencial integrity?

Answer 78

Foreign key

Question 79

s a set of protocols within a computer system that allows two unrelated systems to communicate. | cm

Answer 79

API

Question 80

is an API that allows a hosted computer on a network to share data with a computer in the same hosted environment. | cm

Answer 80

web service API ## Footnote DO NOT AGREE!