S2-m6

Question 1

Operational Data Storage (ODS)

Answer 1

A repository of transactional data from multiple sources and is often an interim area between a data source and data warehouses. Ex) captured transactional data an be related to operational activities such as customer order, sales, or vendor payments
-smaller and are frequently overwritten as transactions are modified

Question 2

Data Warehouse

Answer 2

Very large data repositories that are centralized and used for reporting and analysis rather than for transactional purposes
-pulls data directly from enterprise systems or from a ODS

Question 3

Data Mart

Answer 3

Like a warehouse but is more focused on a specific purpose such as a marketing or logistics and is often a subset of a warehouse

Question 4

Data Lake

Answer 4

A repository similar to a warehouse, but it contains both structured and unstructured data, with data being intis natural or raw format
-does not have a predefined structure or schema

Question 5

Relational Database

Answer 5

The most efficient and effective method to store data

Question 6

Benefits of a Relational database

Answer 6

storing data in a normalized, relational database helps to reasonably assure:
-Completeness
-No redundancy
-Business rules enforcement
-Communication and integration of Business Processes

Question 7

Tables

Answer 7

Tables are organized structures that establish columns and rows to store specific types of data records. Each represents and object

The existence of more than one table in a relational database is the first differentiator between flat files and relational databases; relational databases are made up of at least two tables that are related.

Question 8

Attributes

Answer 8

Columns in a relational database Describe the characteristics or properties desired to be known about each entity. Must be unique to that table. Three types of columns: primary keys, foreign keys, and descriptive attributes

Question 9

Records

Answer 9

Rows contains info about one entity within the table

Question 10

Fields

Answer 10

A space created at the intersection of a column and row in a table in which data is entered.

Question 11

Data types

Answer 11

Each attribute in a table has a designated data type. Specify how the data is stored and indicate how the data in a given attribute can be analyzed

Question 12

Primary Key

Answer 12

A primary key attribute is required in every table. It typically made up of one column. The purpose of the primary key is to help solidify that each row in the table is unique
-rarely descriptive; instead, a collection of letters or sequential numbers
-like id numbers

Question 13

Composite Primary Key

Answer 13

When more than one attribute is necessary to function as a unique identifier

Question 14

Foreign Key

Answer 14

Are attributes in one table that are also primary keys in another table
-same customer ID may appear multiple times in the sales orders table because a single customer can place more than one order
- link between a primary key in one table and a foreign key in another table is what creates a relationship between tables

Question 15

Data Dictionaries

Answer 15

A type of metadata - data about data
Summaries info about the data in a database to make it easier to work with the data and understand how it can be used

Question 16

Normalization

Answer 16

a database design technique that reduces data redundancy and eliminated undesirable characteristics. Normalization rules divide larger tables into smaller tables and link them using relationships. Purpose is to eliminate redundancy

Question 17

First Normal Form 1NF Criteria

Answer 17

1. Each cell in a table must contain only once piece of information. One value may be in a column
2. Each record in every table must be uniquely identified. Accomplished with a primary key,

Question 18

Second Normal Form 2NF

Answer 18

Follows once a table is in 1NF. Requires all non-key attributes in a table to depend on the entire primary key. 2nF is particularly meaningful for tables that have composite primary keys.

Question 19

Third Normal Form 3NF

Answer 19

Once the table is in 1NF and 2NF the next step is to ascertain that each column n a table describes only the PK. 3NF wants to establish that non of the none key attributes depend on other non key attributes

Question 20

What is a database schema?

Answer 20

a set of instructions to tell the database engine how to organize data to be in compliance with the data models. defines the actual structure of the database, including the tables, columns, and relationships between the data entities. Actual implementation and execution of the design

Question 21

What is a data model?

Answer 21

Conceptual representations of the data structures in an information system and are not restricted to relational databases only. A high level of design of the data structures in an info system

Question 22

A conceptual data model

Answer 22

a high level big picture representation of the data structure in an info system

Question 23

A logical data model

Answer 23

more detailed representation of the data structures in an info system at the level of the data itself, providing more detail than a conceptual model.

Question 24

A physical data model

Answer 24

most detailed representation. Specify how the data will be stored in the database.

Question 25

Fact Table

Answer 25

contain measures or metrics, which are referred to as facts. these facts measure the business, such as sales, cost of goods, or profit. Do not contain descriptive elements about the business but do contain foreign keys

Question 26

Dimension Table

Answer 26

Contain descriptive or contextual data for measures such as dates, product names and customer names

Question 27

Star Schema

Answer 27

Most common schema for dimensional modeling and the simplest. Fact table in the center and the dimension tables arranged around it

Question 28

Snowflake Schema

Answer 28

Dimension tables further normalized. The dimension tables are broken down to related tables, rather than a single table. More complex and flexible. A balance between a normalized schema and a star schema