M6 S2 ISC CPA

Data Storage

Type of technology specifically designed for retention of information and helps with accessibility for authorized users to perform business activities effectively and efficiently

Types:

1. Operational Data Store (ODS)

2. Data Warehouse

3. Data Mart

4. Data Lake

Operational Data Store (ODS)

A repository of transactional data from multiple sources and is often an interim area between a data source and data warehouses

• Captured transactional data related to operational activities or system-related

• Data sets are smaller and are frequently overwritten as transactions are modified, processed, and reported

Data Warehouse

A very large data repository that is centralized and used for reporting and analysis rather than for transactional purposes

• Pulls data either directly from enterprise systems with transactional data or from an ODS

• Data is combined into a single repository that can be used for reporting or to create data marts

Data Lake

Repository similar to a data warehouse, but it contains both structured and unstructured data, with data mostly being in its natural or raw format

• Does not have a predefined data structure or schema

• Data stored in the data lake is not indexed or prepped and can be accessed by a user in its original form

Data Mart

Similar to a data warehouse but is more focused on a specific purpose, such as marketing or logistics

• A subset of a data warehouse

• Different departments tailor data to operate more effectively, so they select highly relevant data points from a data warehouse

Relational Database

Most common method for storing structured date that reasonably assures that data is complete, not redundant, that business rules and internal controls are enforced, and

aids communication and integration across business processes

• Made up of at least two tables that are related

Stores data across a series of related tables that contains columns (attributes) and rows (records) that are made of data

Tables

Organizational structures that establish columns and rows to store specific types of data records

• Referred to as entities

• Represents an object in the database

Attributes

Columns in relational database tables that describe the characteristics or properties desired to be known about each entity

• Must be unique to table and relevant to the purpose of the table

• “Last Name”

3 Types

1. Primary Key

2. Foreign key

3. Descriptive attributes

Record

Rows in relational database tables that contains information about one entity within the table

• Provide information about a single customer

Field

Space created at the intersection of a column and row in a table in which data is entered

• The information = "Data value"

Data Type

Specify how the data is stored and indicates how the data in a given attribute can be analyzed

• Numerical, text, or date/time

1. Int ←- Whole number Values (Mathematical calculation)

2. Char(#) ←- String/ number values

3. Date/time

4. Decimal

5. Text ←- “String” values that are non-numeric

Database Keys

Help to uniquely identify each record in a table (and thus, uniquely identify each field at a cross section of attribute and record) and facilitate the relationships between related tables

1. Primary Key

2. Composite Key

3. Foreign Key

Primary Key

Type of database key that helps solidify that each row in the table is unique

• Referred as a "unique identifier"

• Required in every table

• Student ID, Invoice number

Composite Primary Key

Database key where more than one attribute is necessary to function as a unique identifier

• Table does not have one single atttribute that can uniquely identify each record in a table

Foreign Key

Database key where attributes in one table are also primary keys in another table

• "Customer ID" may be the primary key in the customers table; however, tt is a foreign key in the sales orders table

• Creates relationship by linking tables together

Data Dictionary

Type of metadata that s provides and summarizes information about the data in a database to make it easier to work with the data and understand how it can be used to inform decisions and build meaningful reports

Normalization

A database design technique that reduces data redundancy and eliminates undesirable characteristics like insertion, update, and deletion anomalies

• Rules divide larger tables into smaller tables and link them using relationships

• Purpose = Eliminate redundant (repetitive) data and reasonably assure data is stored logically

Steps:

1. 1NF

2. 2NF

3. 3NF

First Normal Form (1NF)

The first step to normalizing data is to determine whether the data conforms to the first normal form, which makes sorting and filtering data easier

• 2 criteria:

  1. Each cell (field) in a table must contain only one piece of information

  2. Each record in every table must be uniquely identified by using a Primary Key/ Composite Primary Key

Second Normal Form (2NF)

Normalization step that requires all non-key attributes in a table to depend on the entire primary key

• If a table has a composite primary key, every non-key attribute needs to describe each component of the PK

  • In a Sales_Order_Detail table that has a composite primary key of SO_ID and Inventory_ID, every additional (non-key) attribute must depend on both the sales order and the inventory item.

Third Normal Form (3NF)

Normalization step where you ascertain that each column in a table describes only the PK

• Established that none of the non-key attributes depend on other non-key attributes (transitive attributes)

Transitively Dependent Columns

The attribute depends on not just the PK but on another non-key attribute

• Violates 3NF

Data Model

Conceptual representations/ high-level designs of the data structures in an information system

1. Conceptual

2. Logical

3. Physical

Schema

A set of instructions to tell the database engine how to organize data to be in compliance with the data models; the actual implementation and execution of the data model design in a specific relational database

• Defines the actual structure of the database, including the tables, columns, and relationships between the data entities

• Specifies how the data will be stored and ultimately accessed in the database

Conceptual Data Model

A high-level, big-picture representation of the data structures in an information system that defines the main entities and relationships of the data, without going into

the details of the attributes or the physical implementation of the database

• Used to understand the overall structure and meaning of the data

• Useful for communicating to stakeholders as they are kept to a minimal level of detail

Logical Data Model

A more detailed representation of the data structures in an information system at the level of the data itself

• Defines entities, relationships, and the attributes (PK and FK) of each entity

• Useful for data-oriented projects ←- designing a data warehouse or system development

• Adjusts any entity-relationship issues related to first normal form or second normal form

Physical Data Model

The most detailed representation of data structures specifying how the data will be stored in database

• Entities = tables

• Attributes = columns

• Includes entities, relationships, attributes, character limits, and data types

• Guide for database implementation and system performance improvements once the database

is implemented

Fact Table

Contain measures or metrics = facts that measure the business

• Sales, cost of goods, or profit

• Does not contain descriptive elements about the business, but they do contain foreign keys to the dimension tables

Dimension Table

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

• Descriptive attributes describe a dimension

• Time table stores the various aspects of time such as year, quarter, month, and day.

Star Schema

Most common schema for dimensional modeling, and is the simplest

• Data is organized into a central fact table with associated dimension tables surrounding it

Downside = Data is redundant, so whenever a change is necessary in the system, that change will need to be updated in every area where it has been duplicated

Snowflake Schema

A complex schema where the dimension tables are broken down into multiple related tables, rather than a single table

• More flexible ←- Allows for more detailed information to be stored about the dimensions

• Strikes a balance between the benefits of a normalized schema and a star schema

Downside = Data is redundant, so whenever a change is necessary in the system, that change will need to be updated in every area where it has been duplicated

Structured Query Language (SQL)

A computer language that interacts with data (tables, records, and attributes) in a relational database that can create, update, delete, and view records and entire tables

• Data can be retrieved from tables and organized in a way that is more intuitive and useful for data analysis and reporting

• Written to indicate which subset of data is intended for extraction—including the intention to filter results based on any criteria or aggregate existing data

SQL Queries

SQL statements that can be thought of as asking the database a question and receiving an answer based on the criteria placed in the SQL query

Mirroring

Method that supports database redundancy that involves copying a database onto a different machine at the same site

Replication

Method that supports database redundancy that involves transferring data to a different database at a secondary site