Class 6: Master the Data, Preparing the Data

What is a Database?

Structured dataset that can be accessed by many potential authorized users via a computer system or network

How do relational databases break the data into separate tables?

With unique lists

What are the parts of a relational databse?

Tables:

• Fields (variables)

  • Primary Key (unique identifier, all tables must have this)

  • Foreign Key (create a relationship between two tables)

• Records (rows)

What is an Entity Relationship Diagram?

Helps you become familiar with what data is in the database and how to query it

What are the Advantages of Storing Data in Relational Databases?

• Data Integrity

  • Fields can have data requirements

  • Complete

  • Neutral

• Internal Control Benefits

  • Internal Controls

  • Data entry and table access can be controlled

  • Reduce redundancy and errors

  • Version control

What are common issues with data?

• Missing data

• Duplicate rows

• Blank rows/columns

• Numbers formatted as text

What is SQL?

A programming language used for managing and querying relational databases, allowing users to retrieve, update, and manipulate data

What can you do with SQL?

• Execute queries

• Retrieve data

• Insert records

• Update records

• Delete records

• Create new databases

• Create new tables

• Create stored procedures

• Create views

• Set permissions on tables, procedures, and views

What is does it mean to Merge data sets?

Combine data from different sources into a single dataset to use for analysis

What is a Left Join?

Start with the first dataset, and add only the column that match the rows in the first dataset

What is a Right Join?

Start with the second dataset, and add only columns that match the rows in the second dataset

What is Inner Join?

Keep only the rows that match in BOTH datasets

What is Outer Join?

Maximum information keep all info