Data Analytics - CH 4

What are characteristics of a Database

Structured data se that can be accessed by many potential authorized users via a computer system or network/ Most Secure Method of storing data

Characteristics of Excel

Spreadsheet application that allows the creation and storing of data and is a powerful tool for performing data analytics and exploring data Examples: PivotTables, Charts, Graphs, Statistical Analysis

Characteristics of Tableau and Power BI

Tool for visualization of data; Good for exploring data and identifying trends and outliers

Define: Rational Database

Stores data in separate tables, each containing a unique list of the items stored

What are the 3 components of Rational Databases

Tables Fields and Records

Define: Tables

Data organized into sets of columns(*called fields*) and rows(*called records*)

Define: Fields

Columns that contain descriptive characteristics about the items in a table

Define: Records

the instances of what is being described in the table

Define: Primary Key

any field that functions as a unique identifier in a table(every tables must have this and it cannot be null)

Define: Foreign Key

primary key of one table that is embedded as a field (characteristics) in another table(**create relationships by physically linking two tables**)

Define: Relational Database Data Dictionary

Contains details of the fields included in the database tables

Define: Relational Database Diagrams

Communicates the same information as a data dictionary but in a visual form

Define: Entity-Relationship Diagrams

Graphical representations of an information system (database) that illustrates relationships among tables(resources, events, people) within the system

Define: Data integrity

Truthfulness of the data

Whare are the 3 benefits of Data Integrity?

Free from error—> no mistakes/inaccuracies Complete—> includes all monetary transactions Neutrality —> data is not biased

◦If everyone had a different version of the data, how would we know which version is correct?

◦In a relational database, there is only ONE version of the data that all authorized users can access.

Name the 4 Main Internal Control Benefits

1.)Preventive controls easier to enforce

2.) Security around the data entry and table access can aid in creating and enforcing data entry internal controls

3.) Reduced Redundancy cuts down on errors

4.) Version control reduces the possibility of having more than one version of the data

What does ETL stand for

Extract, Transform, and Load

Define: Extract

Getting the data that is needed; accessing or connecting to data

Define: Transform

Making the data ready to use; Necessary when the data is not formatted the way it needs to be Sometimes requires cleaning the data

What are examples of Transforming/ Cleaning the data

Removing rows or columns that are blank or that have erroneous data; Sorting or filtering data so it is easier to analyze

Define: Load

Getting the data into the tool used for analysis

What is the easiest way to extract data

By using a query

Can Excel Change data in the connected Database?

No but as the database changes the excel file will be refreshed(can be done in settings/automatically)

Can data be created in Tableau or PowerBI

No it cannot so you must connect existing outside data source

What can —> Structured Query Language(SQL)

can be used to select only the data needed

What does Universal Data lanugage do

used to create and delete records and tables in relational databases

What are some other uses of SQL

Can combine data from multiple tables and organize that data in a way that is more intuitive

Name three Common Functions that SQL uses

Select, FROM, WHERE

Define: SELECT

identifies columns to be extracted

Define: FROM

identifies the table(s) those columns are found in

Define: WHERE

allows filtering or items in the columns

Define: Publicly available

download directly from the internet or other source

Define: Not Publicly available

contact the company directly or subscribe to syndicated data services

How to Obtain Data from the Wed Through Excel

From the “Data” tab in the menu ribbon —> click “New Query” or maybe “Get Data”, Refresh settings, Some data may have to be transformed