IDA Chapter 4

What are the internal control benefits of storing data in relational databases?

- Preventative internal controls are easier to enforce - Security around data entry and table access can aid in creating and enforcing data entry internal controls - Reduced redundancy cuts down on errors - Version control reduces the possibility of having more than one version of the data

What is the purpose of a foreign key?

A foreign key creates the relationship between two tables in a relational database.

What is the purpose of a primary key?

A primary key uniquely identifies each record in a table.

Preventative internal controls are easier to enforce. (Internal controls benefits)

Controls are implemented in the database to ensure the suppliers receiving checks are verified in a company's system. Because a foreign key cannot contain data that doesn't first exist in the related table's primary key, one could not write a check to a supplier that is not already in the Suppliers table.

True or False: You can create raw data in Tableau.

False

Why are foreign keys allowed to repeat values?

Foreign keys are allowed to repeat or even to be null, as long as the data in the field matches the related table.

What are the three characteristics of accounting information having representational faithfulness?

Free from error, complete, and neutrality

Why is it important to connect to data in Excel instead of storing copies of data on your personal computer when using Tableau?

In a business, data will be updated constantly. If you store data on your computer, then your data is out-of-date almost immediately.

Security around data entry and table access can aid in creating and enforcing data entry internal controls. (Internal controls benefits)

In a relational database, you (or the Information Systems Database Administration team) can set up table-level security to indicate which employees have read/edit/write permission. This will ensure that only employees with the appropriate security clearance can create new data entries, such as new suppliers in the Suppliers table.

When would you prefer to view the ER diagram instead of reading the data dictionary?

It is up to individual preference, but if you are interested in extracting data from the database, to understand which tables are related and which fields form primary key/foreign key relationships, an ER diagram would likely be more helpful.

When would you prefer to read the data dictionary instead of viewing the ER diagram?

It is up to individual preference, but particularly if you are interested in understanding what each field represents and how the tables are used, a data dictionary may be more helpful than an ER diagram.

Version control reduces the possibility of having more than one version of the data. (Internal controls benefits)

It maintains data integrity when data is stored in one centralized database that business users can connect directly to via Excel. Tableau, or other tools, rather than having multiple desktop databases or Excel files where data is stored.

What are the three of the internal control benefits that can be achieved by storing data in a relational database?

Preventative internal controls are easier to enforce, security around data entry and table access can aid in creating and enforcing data entry internal controls, reduced data redundancy cuts down on errors and improves version control.

Which two SQL words will be in every query written to extract data?

SELECT and FROM

When connecting to data in Excel, when is it necessary to use SQL instead of connecting directly to an entire table in a database?

SQL is absolutely necessary if the data you are connecting to is larger than the Excel row limit. Beyond that requirement, SQL might be preferred if you want to work with a smaller extract of data.

What are the benefits of storing data in a relational database over Excel?

The benefits of a relational database, in general, cannot be done in Excel. The internal control benefits, reduced redundancy, and reduction of errors that relational databases provide make it a superior method of data storage.

Why is it critical that the data in a foreign key field match the data in its related primary key field?

The data in the foreign key field has to match data in the related primary key to preserve data integrity.

Reduced redundancy cuts down on errors. (Internal controls benefits)

The nature of relational database table structure confirms that there is a unique listing of each observation stored in only one place, rather than repeating customer details every time they make a purchase if using only one massive table for all of the data. This maintains one version of the truth across all reporting mechanisms, which is critical for ensuring that decisions are based on reliable information.

Why is it beneficial to perform transformation steps in PowerQuery instead of making changes manually in the Excel table?

The transformation made in PowerQuery can be refreshed every time to reconnect to data. This is a big time-saving tool so that you don't have to transform the data manually each time you need updated data.

Which SQL word do you use to filter the data that is returned?

WHERE

What is the difference between data dictionaries and ER diagrams?

While both tools have the same general purpose, a data dictionary is a text description of the attributes in a database or dataset, and an ER diagram is a visual depiction of the database. The data dictionary has more detail about each individual field across the dataset. The ER diagram, on the other hand, has a clearer depiction of how tables are related.

foreign key

a column (or group of columns) that exist to create relationships between two tables, linking to the primary key in another table.

primary key

a column in a relational table/database that serves as a unique identifier.

relational database data dictionary

a data dictionary describing each data field for each table in the relational database.

relational database

a means of storing data in order to ensure that the data are complete, not redundant, and to help enforce business rules. Relational databases are made up of tables that are related to one another, and each table contains fields and records. Relational databases also aid in communication and integration of business processes across an organization.

Structured Query Language (SQL)

a universal database language that can be used to create, update, and delete records and tables in relational databases.

tables

data organized into sets of columns (fields) and rows (records).

relational database diagrams

diagrams that communicate which tables are in the database, which fields are in each table, and how each table is related through primary key/foreign key placement.

records

each row (or observation) corresponds to a unique instance of what is being described in the table (generally shown as rows in a table).

entity-relationship (ER) diagram

graphical representation of an information system, illustrating relationships among people, objects, places, and events within that system.

database

structured set of data generally held in a computer allowing access by many potential users.

fields

these are the columns that contain descriptive information about the observations in the table. Specific types of fields are primary keys and foreign keys.

data integrity

truth in data, or how the data presents the truth of the underlying transactions or events that occurred or events that occurred.