Chapter 4 - Preparing data for Analysis

Question 1

What are relational databases?

What are the parts of a relational database?

Answer 1

Best means of storing data in one place:

• Tables: Data organzied into fields and rows
• Field: Columns that contain descriptive characteristics about each observation
• Records: The rows that contain each observation

Question 2

What are primary keys of relational databases?

Why are they important?

Answer 2

Unique identifier of each record that allows connection to other tables (think customer ID)

• Every table must have a primary key to ensure unique listings.

Question 3

What are foreign keys of relational databases?

Answer 3

Primary key used in a separate table to create relationships between the tables.

• Not required for every table

Question 4

What are relational database data dictionaries?

How do they differ from data dictionaries of chapter 3?

Answer 4

Table that

• relates each attribute (transaction ID, date, etc..) to their respective tables and
• explains useful info about each attribute

Contain more fields and desriptive elements of each attribute.

Question 5

Use this slide to understand relational database data dictionaries.

Answer 5

Note the table field and primary/foreign key field.

Question 6

What are entity relationship diagrams?

Answer 6

Graphical representation of each table in a database that shows the fields contained within,

• Connects tables through separate diagrams:

Question 7

What are the advantages of using relational databases for storing data.

Answer 7

Puts all data in one place and ensures:

• Preventive internal controls
• Security of data entry
• Reduced redundancy costs
• Version control

Question 8

What are the three characteristics that ensure data integrity (faithful representation)?

Answer 8

Data that is:

• free from error
• Complete (no missing info).
• Neutral (not biased)

Question 9

Why is it better to connect files to a relational data base rather than extracting and creating different version?

Answer 9

Ensures info is up to date.

Extracting data makes data out of date as soon as new transactions are posted and damages the representation.

Question 10

How do relational databases improve internal controls?

Answer 10

Relational databases ensures that parent data must exist before creating new data:

a foreign key cannot be made without first creating the parent key.

Question 11

How do relational databases improve security?

Answer 11

Data entry can be verified and show who has entered and viewed new data.

Question 12

How do relational databases reduce redundancy costs?

Answer 12

Ensures that there is a unique listing of each observation in only one location.

• maintains one version of the truth.

Question 13

Use this slide to show how to use excel to connect to a database:

Answer 13

Question 14

Use this slide to show how to refresh connections to databases in excel:

Answer 14

From data tab:

• Select “refresh all” dropdown
• “connection properties…”

Question 15

What is Structured Query Language (SQL) and why is it useful for extracting data?

Answer 15

Universale database language.

Used to create, update, and delete records in relational databases.

Necessary for connecting to databases too large for excel to handle.

Question 16

How do you use SQL to extract data?

Answer 16

Need two lines of code:

• SELECT: identifies the fields (columns) you would like to extract
• FROM: identifies the table(s) that store those columns

May need third line to restrict rows:

WHERE: acts as a filter to specify rows associated with observation

Question 17

Write SQL code to retrieve the date, transaction_type, and amount columns from the Transaction table of Returned items

Answer 17

Query:

• SELECT Date, Transaction_Type, Amount*
• FROM Transaction*
• WHERE Transaction_Type = ‘Return’*

Question 18

If you want to select ALL columns/fields when writing SQL, what do you place in the SELECT code?

Answer 18

SELECT *

note: asterisks will return all fields

Question 19

If you want to connect to multiple tables when writing SQL code, what lines of code must you include?

Answer 19

• Place the first table in the FROM line as normal:
• Place the second table in third row: INNER JOIN
• Denote the primary and foreign keys in a fourth row: ON

• FROM Transactions*
• INNER JOIN Customers*
• ON Transaction.CustomerID = Customers.CustomerID*