Data Storage & Database Design

Question 1

• a complex database schema that normalizes dimension tables into multiple related tables
• greatest number of foreign keys
• added flexibility & more detailed info about dimensions

Answer 1

snowflake schema

Question 2

• a simple & common dimensional modeling schema with a central fact table surrounded by dimension tables
• fewer foreign keys
• easyer to understand & query

Answer 2

star schema

Question 3

• simplest type of database schema where all data is stored in a single table
• lacks the complexity of relationships between multiple tables
• not suitable for storing detailed or structured data

Answer 3

flat model

Question 4

database schema that organizes data in a tree-like structure where each parent has multiple child records
- one-to-many relationships
- not used for complex relational databases

Answer 4

hierarchical model

Question 5

a very large data repository that is centralized & used for reporting & analysis rather than transactional purposes; must be continuously updated

Answer 5

data warehouse

Question 5

a repository of transactional data from multiple sources, often an interim area between data sources & data warehouses

Answer 5

ODS operational data storage

Question 6

a large data repository that is more focused on a specific purpose like marketing or logistics (subset of data warehouse)

Answer 6

data mart

Question 7

a large repository that contains both structured & unstructured data, with data mostly being in its natural or raw form

Answer 7

data lake

Question 8

a database design technique that reduces data redundancy & eliminates undesirable characteristics

Answer 8

normalization

Question 9

List the 4 benefits associated with relational databases.

Answer 9

completeness
no redundancy
business rules enforcement
communication & integration of business processes

Question 10

files that contain plain text with no structural interrelationships within that file (excel or CSV)

Answer 10

flat file

Question 11

a column in a relational database table (must be unique)

Answer 11

attribute

Question 12

a row in a relational database table

Answer 12

record

Question 13

space created at the intersection of a column & row in a table in which data is entered

Answer 13

field

Question 14

1. each cell (field) in a table must contain only 1 piece of info
2. each record in every table must be uniquely identified (primary or composite key)

Answer 14

1NF first normal form

Question 15

all non-key attributes are required to depend on the entire primary key

Answer 15

2NF second normal form

Question 16

ascertain that each column in a table describes only the primary key

Answer 16

3NF third normal form

Question 17

a set of instructions to tell the database engine how to organize data to be in compliance with data models

Answer 17

database schema

Question 18

conceptual representations of the data structures in an information system & are not restricted to relational databases only

Answer 18

data model

Question 19

a high-level, big-picture representation of the data structures in an information system

Answer 19

conceptual data model

Question 20

a more detailed representation of the data structures in an information system at the level of the data itself, giving more detail than a conceptual data model

Answer 20

logical data model

Question 21

the most detailed representation of data structures that specifies how the data will be stored in the database

Answer 21

physical data model

Question 22

table that contains measures or metrics (facts) that measure the business & have no descriptive elements

Answer 22

fact table