1
Q
what 3 things make up the core of IT infrastructure?
A
on premises and/or outsourced hardware
software
specialized personnel
2
Q
organizations relying on third-party providers to support their IT operations has caused an increase focus on what report?
A
System and Organization Controls (SOC) 2 reports
3
Q
what are the AICPA’s 5 trust services criteria?
A
security
availability
processing integrity
confidentiality
privacy
4
Q
what do SOC 2 reports provide users?
A
reasonable assurance that the service organization’s controls listed in the system description are accurately depicted and effective
5
Q
SOC 2 engagements require auditors to have what 2 things?
A
1) advanced understanding of IT terminology
2) technical expertise in the way in which key components of the modern IT landscape function
6
Q
what 4 things are included in computer hardware?
A
computers
physical components that compromise computers
computer related equipment
external peripheral devices
7
Q
what are end-user devices (EUDs)?
A
electronic machines, typically computers, that directly interact with employees or consumers at the “edge” of a network
8
Q
what 4 things could an intermediary individual work with that would be considered a non-EUDs?
A
switches
servers
routers
other network support devices
9
Q
what are 4 examples of EUDs?
A
company issued laptops
desktops
tablets
wearables that are used by an employee who is the final consumer of the device
10
Q
what are 6 key components of the internal computer hardware?
A
microprocessors
graphics and sound cards
hard drives
random access memory (RAM)
power supply
motherboard
11
Q
what holds permanent storage? temporary?
A
permanent: hard drives
temporary: RAM
12
Q
what is infrastructure housing?
A
the facilities and safeguards on those facilities that contain hardware such as data centers or offices
13
Q
what does network infrastructure refer to?
A
the hardware, software, layout, and topology of network resources that enables connectivity and communication between devices on a computer network
14
Q
what are the 7 traditional hardware found in most network infrastructures?
A
modems
routers
switches
gateways
edge enabled devices
servers
firewalls
15
Q
what is difference between modem and router?
A
modem: connects network to an internet service provider’s network
router: connects devices to network
16
Q
a router acts as a link between what 2 things?
A
modem
the organization’s switches (if none then directly to EUDs)
17
Q
what is a switch?
A
connect and divide devices within a computer network but do not perform as many advanced functions as routers like assigning IP addresses
18
Q
what is a gateway?
A
a computer or device that acts as an intermediary between different networks by transforming data from one protocol into another so information can flow between networks
19
Q
what is a protocol?
A
a rule, or set or rules, that governs the way in which information is transmitted
20
Q
what do edge enabled devices allow?
A
computing, storage, and networking functions to be closer to the devices where the data or system request originates to allow for faster response times
21
Q
what 2 things can a server be?
A
physical
virtual
22
Q
services coordinate what 3 things?
A
computers
programs
data that are part of the network
23
Q
what 2 things are firewalls intended to do?
A
1) prevent unauthorized access to the org.
2) prevent employees from downloading malicious programs or accessing restricted sites
24
Q
what are basic packet-filtering firewalls?
A
they analyze network traffic that is transmitted in packets and determine whether that firewall software is configured to accept the data
25
what are 5 types of firewalls?
basic packet filtering firewalls
circuit level gateways
application level gateways
stateful multilayer inspection firewalls
next gen firewalls
26
what is topology?
the physical layout equipment, or nodes, in a network which helps understand how to properly engineer the network for optimal performance
27
what are the 4 common topologies? what are their layouts?
bus (linear or tree each with a single connection)
mesh (numerous connections between nodes)
ring (circular path of connection)
star (central hub acts as a switch or server)
28
what are the two types of mesh topology?
full mesh
partial mesh
29
what are the two types of paths in ring typology?
unidirectional ring (one direction)
multi directional (two way data transmission)
30
can there be multiple hubs in a star topology?
yes
31
what is an advantage of star topology?
it is easy to identify the damaged cables if a hub fails
32
what 2 things does the open systems interconnection (OSI) model?
how protocols work
how network devices communicate with each other
33
what are the 7 layers of the OSI model?
application
presentation
session
transport
network
data link
physical
34
what are the 2 terms to label the way data moves across the 7 layers of the OSI model? which direction?
encapsulation (from 7 to 1)
decapsulation (from 1 to 7)
35
what is the purpose of application layer (layer 7)?
serves as the interface between applications that a person uses and the network protocol needed to transmit a message
36
what are 4 common protocols used in application layer?
hypertext transfer protocol (HTTP)
file transfer protocol (FTP)
simple mail transfer protocol (SMTP)
electronic data interchange (EDI)
37
what is the purpose of presentation layer (layer 6)?
transforms data received from application layer into a format that other devices using OSI model can interpret like videos, images, and web pages
38
what are 3 common formats used in presentation layer?
american standard code for information interchange (ASCII)
joint photographic experts group (JPEG)
moving picture experts group (MPEG)
39
what is purpose of session layer (layer 5)?
allows sessions between communicating devices to be established and maintained
40
what are 3 common protocols of session layer?
structured query language (SQL)
remote procedure cell (RPC)
network file system (NFS)
41
what is purpose of transport layer (layer 4)?
supports and controls the communication connections between devices
42
what are 4 common protocols of transport layer?
transmission control protocol (TCP)
user datagram protocol (UDP)
secure sockets layer (SSL)
transport layer security (TLS)
43
what is the purpose of network layer (layer 3)?
adds routing and address headers or footers to the data, such as source and destination IP addresses so the message reaches the correct devices and it can detect errors
44
what are 4 common protocols of network layer?
internet protocol (IP)
internet protocol security (IPSec)
network address translation (NAT)
internet group management protocol (IGMP)
45
what is the purpose of data link layer (layer 2)?
format for transmitted link by adding a MAC address to route messages to the correct devices
46
what are 4 common protocols used in data link layer?
integrated services digital network (ISDN)
point to point tunneling protocol (PPTP)
layer 2 tunneling protocol (L2TP)
address resolution protocol (ARP)
47
what is purpose of physical layer (layer 1)?
converts message sent from data link layer into bits (0s or 1s) so it can be transmitted to other physical devices
48
what are 3 common protocols of physical layer?
high speed serial interface (HSSI)
synchronous optical networking (SONET)
V.35 and X.21
49
what does network infrastructure architecture refer to?
way an organization structures its network from a holistic design standpoint considering factors like geographical layout, physical and logical layout, and network protocols used
50
what 4 things can networks be?
wired
wireless
on premises
virtual
51
what are 4 common types of network architecture designs?
local area networks (LAN)
wide area networks (WAN)
software defined WAN (SD-WAN)
virtual private networks (VPNs)
52
what is difference between LAN and WAN?
- LAN provides network access to a limited geographical area (home, single office)
- WAN provides network access to large geographic area (cities, regions, countries)
53
what is the SD-WAN designed for?
monitors the performance of WAN connections and manages traffic to optimize connectivity
54
what are VPNs?
virtual connections through a secure channel or tunnel that provides remote and secure access to an existing network
55
what does software consist of?
the applications, procedure, or programs, that provide instructions for a computer to execute
56
what are 3 common types of software?
operating system (OS)
firmware
mobile technology
57
what is an operating system?
software that orchestrates the global functioning of a group of applications, hardware, and their performance by acting as an intermediary
58
what is firmware?
software that is locally embedded in hardware that instructs the hardware how to operate
59
what are internet of things (IoT)? what are some examples?
an extension of mobile technology that usually requires bluetooth or an internet connection to access a larger network
ex. Siri, Alexa, bluetooth headphones, etc.
60
what is cloud computing?
a computing model that uses shared resources over the internet
61
what is infrastructure elasticity in terms of cloud computing?
allows customers to rent only as much service/storage/processing as needed on a minute to minute basis
62
what are the 3 primary cloud computing models?
infrastructure as a service (IaaS)
platform as a service (PaaS)
software as a service (SaaS)
63
what is IaaS?
the CSP provides an entire virtual data center of resources allowing organizations to outsource just about everything to third party providers
64
what 2 things are the company responsible for if they use IaaS?
1) keeping the environment in which it operates consistently up and running for users
2) virtually managing the performance of the physical infrastructure
65
what is PaaS?
CSP provides proprietary tools or solutions remotely that are used to fulfill a specific business purpose
66
what is SaaS?
the CSP provides a business application or software that organizations use to perform specific functions or processes
67
what are the 4 common types of cloud computing deployment models?
public
private
hybrid
community (shared by multiple orgs.)
68
what is a cloud service provider (CSP)?
a third party that provides cloud computing services such as application delivery, hosting, or monitoring to customers
69
the Committee of Sponsoring Organizations (COSO) developed guidance and best practices for what 4 things?
internal control
enterprise risk management
governance
fraud deterrence
70
what are the 4 elements of the COSO enterprise risk management framework?
mission, vision, and core values
strategy development
business objective formulation
implementation and performance
71
what are the 5 components of the COSO enterprise risk management framework?
governance and culture
strategy and objective setting
performance
review and revision
information communication and reporting
72
what are the 5 supporting principles of the governance and culture component of COSO?
1) exercises board risk oversight
2) establishes operating structures
3) defines desired culture
4) demonstrates commitment to core values
5) attracts, develops, and retains capable individuals
73
what ae the 4 supporting principles of the strategy & objective setting component of COSO?
6) analyzes business context
7) defines risk appetite
8) evaluates alternative strategies
9) formulates business objectives
74
what are the 5 supporting principles of the performance component of COSO?
10) identifies risk
11) assesses severity of risk
12) prioritizes risk
13) implements risk responses
14) develops portfolio view
75
what are the 3 supporting principles of the review & revision component of COSO?
15) assesses substantial change
16) reviews risk and performance
17) pursues improvement in enterprise risk management
76
what are the 3 supporting principles of the information, communication, & reporting component of COSO?
18) leverages information and technology
19) communicates risk information
20) reports on risk, culture, and performance
77
the set of 20 COSO principles were designed to do what 2 things?
1) be practical and customizable so organizations of any industry, size, or type can implement them
2) designed with the thought that mgmt and a company's BOD could use them as a standard for reasonable expectations when managing risks
78
when outsourcing to a CSP, who does the risk fall on... the organization or CSP?
risk remains with the organization
79
what 4 things must an organization consider when deciding a CSP?
1) how a CSP affects the organization's risk profile
2) how a CSP's risks can impact performance
3) what responsibilities belong to a CSP
4) how a CSP's internal controls address risk
80
what are the 8 components the COSO frameworks suggests to establish ideal configurations with CSPs?
internal environment
objective setting
event identification
risk assessment
risk response
control activities
information and communication
monitoring
81
what are 6 risks you should consider when evaluating CSPs and their services?
1) the rate of competitor adoption
2) being in the same risk ecosystem as the CSP and other tenants
3) transparency
4) reliability and performance
5) lack of application portability (vendor lock-in)
6) security compliance
82
what are 4 additional risks to consider when evaluating CSPs?
cyberattacks
data leakage
IT organizational change
CSP long-term viability
83
what type of firewall assigns IP addresses?
network address translation (NAT) firewalls
84
Which type of firewall inspects the contents of data packets?
application level gateway
85
what type of firewall verifies the source of packets?
circuit level gateway
86
what type of firewall combines packet filtering and NAT firewalls?
stateful multilayer inspection firewalls
87
what are enterprise resource planning (ERP) systems?
cross functional systems that support different business functions and facilitate integration of information across departments
88
an ERP solution does what 2 things?
1) facilitates real time communication between systems
2) operates under a centralized database and user interface
89
what 2 things does an Accounting Information System (AIS) do?
1) collect, record, and store accounting information
2) complies information using accounting rules to report both financial and nonfinancial information to decision makers
90
what are the 3 main subsystems/modules of AIS?
transaction processing system (TPS)
financial reporting system (FRS)
management reporting system (MRS)
91
what does a transaction processing system (TPS) do?
convert economic events into financial transactions (JEs) and distribute information to support daily operations
92
what does a financial reporting system (FRS) do?
aggregates daily financial information from TPS and other sources for infrequent events like mergers, settlements, or natural disasters to enable timely reporting
93
what does a management reporting system (MRS) do?
provides internal financial information to solve day to day business problems such as budgeting, variance analysis, or cost-volume-profit analysis
94
the 3 AIS subsystems achieve what 5 objectives?
1) record valid transactions
2) properly classify those transactions
3) record the transactions at their correct value
4) record the transactions in the correct accounting period
5) properly present the transactions and related information in the FS of the org.
95
what are 3 key transaction cycles
revenue cycle
purchasing and disbursement cycle
other processes that involve the recognition and/or facilitation of transactions
96
what are 4 broad areas of process improvements that can enhance AIS performance?
automation
shared services
outsourcing
offshore operations
97
what are 3 forms of technology that are gaining mass adoption in process improvement?
robotic process automation (RPA)
natural language processing (NLP) software
neural networks
98
what are 2 distinguishing features of a shared service?
1) they are shared within an org. or group of affiliates
2) almost always involve software that is designed to process large batches of data
99
what are 8 risks associated with outsourcing?
quality of risk
quality of service
reduced productivity
staff turnover
language barriers
security
qualification of outsourcers
labor insecurity
100
what are the 4 most common types of offshore outsourcing?
IT
business processes (call centers, accounting operations, tax compliance)
software research and development
knowledge processes (processes requiring advanced knowledge & skills)
101
what is robotic process automation (RPA)?
refers to programs capable of extracting information from a specific user interface that can then initiate further processes based on the data extracted
102
what is natural language processing (NLP) software? what are some examples?
involves the technology developed and used to encode, decode, and interpret human languages so the tech can perform tasks, interact with other humans, and/or carry out commands on other technology devices
ex. tech made to build Siri, Echo, Alexa
103
what is difference between AI and ML?
- AI: umbrella term used to describe systems that perform tasks that usually require human intelligence
- ML: subset of AI using algorithms and data sets for computers to learn and make decisions
104
what are neural networks?
a form of technology that is modeled after neurons that facilitate the function of human or animal memory
105
what are the 3 basic layers of a neural network?
input layer
output layer
hidden layer
106
what is deep learning in terms of neural network?
a specialized subset of neural networks that is used to capture patterns in large volumes of data
- the "engine" of the hidden layers
107
processing integrity refers to a system's ability to initiate and complete transactions so they are what 4 things?
valid
accurate
completed timely
authorized to meet a company's objective
108
the AICPA defines deficiencies in the design of a control in a SOC 2 engagement as either what?
necessary controls that are missing... or
existing controls that are not designed properly
109
what are the 5 trust services criteria?
security
availability (search for bottlenecks)
processing integrity (look for red flags)
confidentiality (evaluate employees and processes)
privacy (look for potential data breaches)
110
what 2 items does the AICPA's Description Criteria for a Description of a Service Organization's System in a SOC 2 Report recommend to review?
principle service commitment
principle system requirements
111
how does the AICPA define an operation deficiency?
a properly designed control that either:
- does not operate as designed; or
- is performed by a person who lacks authority or competence to perform the control effectively
112
what are the 3 evidence needed in order to test operating effectiveness of controls?
1) how the controls were applied
2) the consistency of application
3) the personnel responsible for applying these controls
113
if a deficiency is already identified, does the service auditor have to test the effectiveness of this control?
no
114
what are 4 possible duties of the service auditor in designing and performing the tests of controls?
1) make inquiries
2) reperform controls
3) observe service organization personnel performing the controls
4) review documentation
115
what are 4 variables that influence the size and frequency of sampling when testing controls?
how often a control is performed
the expectation that a control will actually deviate
the testing period length
the reliability of the evidence
116
the COSO framework has developed guidance and frameworks covering what 3 areas?
internal controls
risk management
fraud deterrence
117
within the Control Activities category of COSO, what does principle 11 state?
there should be general controls over technology in order to achieve organizational objectives
118
within the Information and Communication category of COSO, what does principle 13 state?
that organizations should acquire, create, and use quality information in order to support internal controls
119
within the Information and Communication category of COSO, what does principle 14 state?
effective communication of information is necessary to support internal controls
120
what is blockchain?
a control system originally designed to govern the creation and distribution of Bitcoin
121
what are 2 reasons why blockchain was created?
to prevent bitcoin from being replicated
to limit its initial creation so there is only a finite number of bitcoins
122
what are 3 things blockchain is resistant to that make it valuable?
alteration
multiparty transaction validation
decentralized nature
123
what are 2 ways blockchain helps financial reporting?
visibility of transactions
the availability of data
124
what are 2 benefits of blockchain for management and auditors?
allows management to support its financial records
make audits of blockchain transactions easier because of automatic audit trails
125
what are 5 things organizations should consider when implementing COSO's controls to a blockchain setting?
1) focus on preventative controls
2) increase frequency of detective controls
3) develop controls that use analytic technology
4) develop a code of conduct
5) create cross disciplinary teams with segregation of duties
126
Due to the volume of transactions being processed on a blockchain, organizations should focus on what 2 type of controls when applying the COSO internal control framework?
preventative
detective
127
what is system availability?
when business data is accessible and IT systems are operating normally
128
what are 2 facets of availability?
system availability
availability of human capital
129
what are 9 considerations to help maintain availability?
business resiliency
business continuity
system availability controls
crisis management
disaster recovery
physical and IT infrastructure controls
uninterrupted power supply
redundancy and backup
incident response plan
130
what is business resiliency?
the integration of system availability controls, disaster recovery plans, business continuity plans, and crisis management plans into a central set of procedures
131
to build out business resiliency what 2 things must organizations do?
1) identify what activities are necessary to the core operations
2) assess existing threats to those activities
132
what are the 5 steps in a disaster recovery plan?
1) assess the risks
2) identify mission critical applications and data
3) develop a plan for handling the mission critical applications
4) determine the responsibilities of the personnel involved in disaster recovery
5) test the disaster recovery plan
133
what are the 3 main options an organization has to maintain IT operations through the use of alternative processing facilities?
cold site
hot site
warm site
134
what is a cold site?
off-site location with the wiring and electrical connections but no actual equipment so the space is empty
135
what is a hot site?
off-site location with everything ready to go with hardware in place to perform the functions of organization
136
what is a warm site?
off-site location that falls between a cold site and hot site
137
what are business continuity plans?
more comprehensive than disaster recovery plans focused on keeping the business operational
138
what 4 things must business continuity plans consider?
identify key business processes
identify the risks that exist in these processes
determine the acceptable downtime
implement mitigation and contingency plans to address risks and downtimes
139
what is business impact analysis?
identifies business units, departments, and processes that are essential to the survival of an entity and the impact it would have during a disruption
140
what 2 things will BIA identify?
1) how quickly essential business units and/or processes can return to full operation following a disaster
2) the resources required to resume business operations
141
what are the 7 steps of a BIA?
1) establish the BIA approach
2) identify critical resources
3) define disruption impacts
4) estimate losses
5) establish recovery priorities
6) create BIA report
7) implement BIA recommendations
142
what is annualized rate of occurrence (ARO)?
involves assigning a probability of likelihood to each of the potential risks and threats in a BIA to determine the expected frequency of occurrences in a year
143
what is exposure factor (EF) in terms of BIA?
the damage in terms of dollars, expressed as a % of an asset's value
144
what is a single loss expectancy (SLE) in terms of BIA?
the cost of an individual loss
145
what is annualized loss expectancy (ALE) in terms of BIA?
the cost of a specific loss in a given year
146
what are the 4 goals of crisis management plans?
lessen the impact
protect people
protect organizational reputation
return to normal operations ASAP
147
what are 8 metrics to measure for system availability?
agreed service time (AST)
minimal amount of downtime (DT)
maximum tolerable downtime (MTD)
recovery point objective (RPO)
recovery time objective (RTO)
mean time to repair (MTTR)
recovery time actual (RTA)
recovery point actual (RPA)
148
what are 6 system availability controls that should be put in place?
physical controls
IT infrastructure controls
uninterrupted power supply
redundancy
system backup
detecting deficiencies in control design
149
what are the types of system backups?
full (exact copy)
incremental (only items that have changed since last backup)
differential (copies all changes since last full backup)
150
what is change management?
a term used to describe policies, procedures, and resources employed to govern change in an organization
151
what are the 11 steps that would help a company go from change inception to implementation?
1) identify and define the need for system changes
2) design a high level plan with goals
3) obtain approval from management
4) develop a budget and timeline
5) assign personnel responsible for managing the change
6) identify and address potential risks
7) provide an implementation road map
8) procure necessary resources and train personnel
9) test the system change
10) execute the implementation plan
11) review and monitor change implementation
152
what are the 5 types of segregated environments where change could be implemented to not disrupt normal operations?
development
test (could be same as development depending on org.)
staging (testing in final phases)
production (application is deployed)
disaster recovery
153
what are 3 examples of selection and acquisition risks in change implementation?
- lack of expertise from purchasing agent or lack perspective to meet org. needs
- lack of a formal selection and acquisition process
- software/hardware vulnerability and incompatibility
154
what are the 3 major change management risks?
selection and acquisition risks
integration risks
outsourcing risks
155
what are 6 examples of integration risks in change implementation?
user/employee resistance
lack of management support
lack of shareholder support
resource concerns
business disruption
lack of system integration
156
what are 3 outsourcing risks in change implementation?
lack of organizational knowledge
uncertainty of 3rd party's knowledge and mgmt
lack of security
157
what are the 11 change management controls?
policies and procedures
emergency change policies
standardized change requests
impact assessment
authorization
separation of duties
conversion controls
reversion access
pre-implementation testing
post-implementation testing
ongoing monitoring
158
what are 3 reasons documenting system of controls can be useful?
troubleshooting
staff training and education
improving system performance
159
baseline configuration metrics may include what 3 metrics?
system uptime
resource utilization
failover time
160
what are 6 frequently used log types?
application logs
change logs
event logs
firewall logs
network/perimeter logs
proxy logs
161
what is an application log?
records application data such as when an employee accesses or views a table, or when an error occurs in a program
162
what is a change log?
track changes that were requested, approved, and implemented
163
what is an event log? and some of the logs it reports events on?
record various events that occur on a system such as directory logs, DNS server logs, endpoint logs, security event logs, and basis system logs
164
what are the 2 most common IT change management methodologies?
waterfall model
agile method
165
what is the waterfall method?
the steps flows in one direction with each step occurring in a sequence
166
what are 5 challenges of the waterfall method?
time consuming
benefits of new system not realized until the end
there is no customer input
change can be difficult to manage
some employees may be idle during the process
167
what are the 7 steps in the waterfall method?
1) plan
2) analyze
3) design
4) develop
5) test
6) deploy
7) maintain
168
what is the agile method?
has cross-functional teams working simultaneously each dedicated to a particular function of a system from a prioritized list of the customer's remaining needs for the system
169
what is patch management?
the systematic process of identifying specific vulnerabilities or software bugs in operating systems or applications and addressing them with patches/fixes
170
an effective patch management process includes what 5 things?
evaluating new patch releases (reactive)
using a vulnerability tool (proactive)
testing patches in a test environment
approving and deploying patches
verifying patches deployed
171
what are the 5 system conversion methods and what do they entail?
- direct (immediate change)
- parallel (new one implemented while old one still in use for some time)
- pilot (new system tested on small scale while still using old system)
- phased (new system gradually implemented over time)
- hybrid
172
what are the 6 steps of the software testing process for change management?
1) establish a testing plan
2) identify and prioritize key areas of software to test
3) determine which type of test to run and the objectives
4) execute the tests
5) log the results and identify defects
6) report the findings and fix the defects
173
what are the 4 things a Change Advisory Board (CAB) do?
approve changes
document changes
notify users of upcoming or past changes
deploy resources for testing and responding to change
174
what are the 4 most common types of testing performed to see if a system is performing as intended? and what do they do?
- unit testing (examining the smallest increment of an application)
- integration testing (looking at components/units working together)
- system testing (focuses on overall functionality of the program)
- acceptance testing (determines if program meets end-user needs)
175
The process of modifying the default system parameters to meet a company's needs is known as what?
configuration
176
what is closed loop verification?
step in change management testing that ensures the system meets predefined standards by continuously comparing actual outcomes against expected results
177
what system conversion method is viewed as the safest?
parallel
178
what are the 8 steps of the data life cycle?
definition
capture/creation
preparation
synthesis
analytics and usage
publication
archival
purging
179
what is definition in the data life cycle?
defining what data a business needs and where to capture or retrieve such data
180
what are 6 input checks employed to maintain the integrity and accuracy of internally generated data? what is entailed in each of these checks?
field check (ensure data matches specific format)
reasonableness check (is it within expected boundaries)
completeness check (check for missing fields)
validity check (verifies data against predefined rules)
limit check (checks data values against certain thresholds)
size check (checks if # of characters exceed allowed amount)
181
what is the purpose of the preparation step?
determine whether the data is complete, clean, current, encrypted, and user friendly
182
what is the synthesis step of data life cycle?
bridge between preparation and usage in which calculated fields are created to prepare data for quicker usage and analysis
183
what are 3 reasons to archive data?
free up storage for active systems
enhance active system performance
reduce security risks
184
what are 3 methods of creating or capturing data?
extract, transform, and load (ETL)
active data collection
passive data collection
185
what are 3 complexities when obtained data from an external source?
integrity
safety
copyrights
186
what are 4 common types of data storage?
operational data store (ODS)
data warehouse
data mart
data lake
187
what is an operational data store (ODS)?
repository of transactional data from multiple sources and is often an interim area between data source and data warehouses
188
what is a data warehouse?
very large data repositories that are centralized and used for reporting and analysis rather than for transactional purposes
189
what 2 areas does a data warehouse pull data from?
directly from enterprise systems with transactional data
ODS
190
what is a data mart?
like a data warehouse but more focused on a specific purpose
- often a subset of a data warehouse
191
what is a data lake?
repository similar to a data warehouse but contains both structured and unstructured data with data mostly being in its natural or raw format
192
how is a data lake different from a data warehouse?
there is no predefined data structure
193
what 3 things does storing data in a relational database help with?
1) reasonably assure that data is complete and not redundant
2) data follows business rules and internal controls are enforced
3) aids communication and integration across business processes
194
what are the 3 types of columns in a relational database table?
primary key
foreign key
descriptive attributes
195
tables in relational databases are also known as what?
entities
196
in terms of a table what is an attribute? a record? a field?
attribute: column
record: row
field: data value/cell
197
what is the purpose of the primary key?
help solidify that each row in a table is unique
- required in every table
198
when is a composite primary key needed?
when more than one attribute is necessary to function as a unique identifier
199
what is a foreign key?
attributes/columns in one table that are also primary keys in another table to create a relationship between tables
## Footnote
think of XLOOKUP
200
data dictionaries provide and summarize information about the data in a database to do what 2 things?
1) make it easier to work with the data
2) understand how it can be used to inform decision and build meaningful reports
201
what is normalization in terms of database redundancy?
database design technique that reduces data redundancy and eliminates undesirable characteristics
202
what is first normal form (1NF) in normalization? what are the 2 criteria of 1NF?
determining whether the data conforms to the first normal form
1) each cell has only one piece of information
2) each record must have a primary key/be uniquely identifiable
203
what does second normal form (2NF) require in normalization?
requires all non-key attributes in a table to depend on the entire primary key
204
what does third normal form (3NF) require in normalization?
requires each column in a table depend on ONLY the primary key
205
attributes that violate 3NF are described as what?
transitively dependent columns
206
what are data models?
conceptual, high-level representations of the data structures in an information system
207
what are data schemas?
set of instructions to tell the database engine how to organize data to be compliant with the data models
208
what are the 3 different aspects of data models? and how do they help you understand data models?
conceptual (understand overall structure)
logical (more detailed at level of data itself)
physical (specify how data will be stored in database)
209
what are 2 popular data schemas?
star schema
snowflake schema
210
what are the 2 tables each data schema has?
fact table
dimension table
211
what is a fact table?
contains measures or metrics which are referred to as facts but do not contain descriptive elements about the business
212
what is a dimension table?
contains descriptive or contextual data for measures like dates, product names, and customer names
213
what is a star schema?
data is organized into a central fact table with associated dimension tables surrounding it
214
what is snowflake schema?
dimension tables are broken down into multiple related tables rather than a singular table like a star schema, thus it requires more tables and more foreign keys to link the tables together
- more complex and flexible than star schema
- strikes balance between benefits of normalized and star schema
215
what is structured query language (SQL)?
a computer language to interact with data in a relational database
216
why are SQL queries written?
to indicate which subset of data is intended for extraction
217
SQL queries are made up of what 2 things and what is their definition?
- SQL commands: language specific words
- database elements: references to table names, attribute names, or criteria
218
the SQL clause SELECT indicates what?
indicates which attribute the user wishes to view
219
the SQL clause FROM does what?
lets the database management system know which table(s) contain(s) the attributes that the user is selecting
220
the SQL clause WHERE does what?
acts as a filter like in excel, which ultimately narrows the focus of the query
221
what are 5 common SQL aggregate functions used in SELECT clause?
SUM
COUNT
AVG
MIN
MAX
222
what is the HAVING clause?
similar to WHERE clause but it allows for filtering of aggregated data (sum, avg, count, min, max)
- allows for =, <, >, =<, =>, <>
223
what does JOIN clause do? what are the 2 most common types?
JOIN links tables to retrieve data from more than one table
1) INNER JOIN (only pulls data for which there is a match in both tables)
2) LEFT JOIN (provides data for which there is not a match)
224
does the order of tables matter in an INNER JOIN clause? does it for a LEFT JOIN?
INNER: no
LEFT: yes
225
what are 2 common templates for flowcharts?
business process modeling notation (BPMN)
data flow diagrams (DFD)
226
what are business process modeling notation (BPMN) activity models?
standardized tool for creating diagrams with symbols and rules to depict business processes
- can help with efficiency and effectiveness of processes
227
what are the 7 most common symbols and rules in BPMN models? what do they entail?
- flow activities: shows flow of steps in a process
- pools: key participants/groups in process
- swim lanes: reflects a group of similar activities within a pool
- events: describe how process begins and ends
- tasks: a function or action
- message flows: when a BPMN models requires more than one pool
- gateway: a question/decision point
228
what are the 3 events in a BPMN model?
start events
end events
intermediate events
229
on an activity model diagram what signifies difference between sequence flow and message flow?
sequence flow: solid line arrows
message flow: dashed/dotted line arrows
230
what is a data flow diagram (DFD)?
standardized tool for creating diagrams that describe the way data moves through an organization
231
what are the 4 objects in a data flow diagram and their respective shape?
process (circle or rounded rectangle)
data flow object (curved or straight arrow)
data store or warehouse (open ended rectangle)
external entity or terminator (square)
