ISC S2 M1 - IT Infrastructure PDF

**S2 M1 - IT Infrastructure** **Computer Hardware** -the physical components that comprise computers\ -external peripheral devices\ -back end devices: switches, servers, routers\ -can include end-user devices **End-User Devices** -directly interact with person\ -laptops and desktops\ -tablets\ -...

**S2 M1 - IT Infrastructure** **Computer Hardware** -the physical components that comprise computers\ -external peripheral devices\ -back end devices: switches, servers, routers\ -can include end-user devices **End-User Devices** -directly interact with person\ -laptops and desktops\ -tablets\ -wearables **Internal Computer Hardware** -microprocessor - the brain\ -graphics and sound cards\ -hard drives - permanent storage\ -random access memory (RAM) - temporary storage\ -the power supply\ -the motherboard - connects critical pieces **External Peripheral Devices** -not integrated into the machine itself\ -mouse, keyboard, speaker, microphone, disk drives, memory devices, network cards, monitors **Infrastructure Housing** -data center or offices\ -advanced security systems\ -ventilation and climate control **Network Infrastructure Hardware** -hardware, software, layout, and topology of network resource that enables connectivity and communication\ \ -modems\ -routers\ -switches\ -gateways\ -edge-enabled devices\ -servers\ -firewalls **Modem** -connects computer to internet\ -receives analog signals from internet service and translates those into digital signals\ -brings internet to a home or office\ -modem has a public IP address **Routers** -manage a network traffic by connecting devices to form a network\ -reads source packets and routes them\ -assign IP adresses\ -link between a modem and switches **Hubs** -primitive device\ -connection point that link multiple systems and devices\ -receive data packets and forward them to other devices Switches -can divide one network connection into multiple connections (printers, servers, end user machine)\ -not as advanced as a router, can\'t assign IP address\ -like a hub but can only route traffic to target destination **Gateways** -acts as an intermediary between networks\ -reads and converts protocol **Proxies** -form of gateway that does not translate protocols but acts as a mediator\ -blocks hackers **Protocol** -rule or set of rules that govern the way in which information is transmitted\ -TCP/IP - common internet protocol **Edge-Enabled Devices** -allows computing, storage, and networking functions to be close to the devices where the data or system request originates\ -decentralizes computing power\ -faster network response times **Servers** -master coordination and communication of core functions in a network\ -coordinate the computers, programs, and data\ -client requests data from server - server sends data back **Signal Modifiers** -receive signals, modify them by increasing signal strength\ -types of signals are electrical, radio frequency, audio, optical **Firewalls** -user identification and authentication that prevents unauthorized access to network\ -physical, software, or both\ -improves traffic\ -prevent traffic from crossing networks **Basic Packet-Filtering Firewalls** -work to analyze network traffic that is transmitted in packets\ -determine if firewall storage can accept the data\ -can be set to only allow trusted sources (IP) **Circuit-Level Gateway Firewalls** -verify the source of the packet and meet rules and policies set by security team **Application-Level Gateway Firewall** -inspect the packet itself\ -resource-intensive and may have slow performance **Network Address Translation Firewalls** -assign an IP address to specific approved sources\ -those source are approved to be in firewall\ \ -can allow machines on a private network to share a single public address to mask their true identity **Stateful Multilayer Inspection Firewalls** -combine packet-filtering and network address translation **Next-Gen Firewalls** -assign different firewall rules to different applications as well as users **Topology** -physical layout or nodes in a network **Bus Topology** -layout is either in a linear or tree form, with each node connected to a single line or cable\ -disadvantage: if the central line is compromised the entire network goes offline **Mesh Topology** -numerous connections between nodes\ -commonly used in wireless networks\ -disadvantage: high traffic, costly **Ring Topology** -nodes are connected in a circular path in a ring\ -advantage: collision is minimized or eliminated\ -disadvantage: slow network performance **Star Topology** -data passes through a central hub\ -advantage: easy to identify damaged cables **Open Systems Interconnection Model (OSI)** -OSI model was developed by the International Organization for Standardization (IOS)\ -segregates network functions into seven different layers, each responsible for a specific data exchange **OSI Model - 7 Layers** APSTNDP\ \ 7. Application\ 6. Presentation\ 5. Session\ 4. Transport\ 3. Network\ 2. Data Link\ 1. Physical **Application Layer - Layer 7** \*serves as the interface between applications.\ \ -Hypertext Transfer Protocol (HTTP)\ -File Transfer Protocol (FTP)\ -Simple Mail Transfer Protocol (SMTP)\ -Electronic Data Interchange (EDI) **Presentation Layer - Layer 6** \*transforms data received from the application layer into a format that other devices using the OSI model can interpret, such as standard formats for videos, images, and web pages\ \*encryption occurs at this layer\ \ -American Standard Code for Information Interchange (ASCII)\ -Joint Photographic Experts Group (JPEG)\ -Moving Pictures Expert Group (MPEG) **Session Layer - Layer 5** \*session between communicating devices to be established and maintained\ \ -Structured Query Language (SQL)\ -Remote Procedure Call (RPC)\ -Network File System (NFS) **Transport Layer - Layer 4** \*supports and controls communication connections between devices\ \*sets rules for how devices are referenced\ \ -Transmission Control Protocol (TCP)\ -User Datagram Protocol (UDP)\ -Secure Sockets Layer (SSL)\ -Transport Layer Security (TLS) **Network Layer - Layer 3** \*adds routing address headers or footers\ \ -Internet Protocol (IP)\ -Internet Protocol Security (IPSec)\ -Network Address Translation (NAT)\ -Internet Group Management Protocol (IGMP) **Data Link - Layer 2** \*data packets are formatted for transmission\ \*adds Media Access Control (MAC) addresses\ \ -Integrated Services Digital Network (ISDN)\ -Point-to-Point Tunneling Protocol (PPTP)\ -Layer 2 Tunneling Protocol (L2TP)\ -Address Resolution Protocol (ARP) **Physical Layer - Layer 1** \*converts the message into bits (0 and 1) so it can be transmitted\ \ -Hight Speed Serial Interface (HSSI)\ -Synchronous Optical Networking (SONET) **Common Types of Network Architecture** -Local Area Networks (LAN): limited geographical area (house)\ -Wide-Area Networks (WAN): larger area (multiple offices, the internet)\ -Software-Defined WAN: manages traffic to optimize connectivity\ -Virtual Private Network (VPN): remote and secure access to an existing network **Operating Systems (OS)** -the software that supports a computer\'s basic functions, such as scheduling tasks, executing applications, and controlling peripherals.\ -example: Windows, MacOS, iOS **Firmware** -software that is local\ -directs functions of motherboard or microprocessor\ -not updated frequently or at all **Mobile Technology** -wireless enabled devices that connect to internet\ -combines hardware such a laptops, tablets, hotspots, mobile phones, mobile applications, operating systems\ -connectivity done with Wi-Fi, Bluetooth, 4G or 5G LTE cellular technology\ -can be user end devices (EUDs) or non EUDs **Internet of Things Devices** -Siri, Alexa, TV, iHome\ -an extension of mobile technology\ -typically require Bluetooth or internet connection **Cloud Computing** -computing model that uses shared resources over the internet\ -rent storage space, processing power, proprietary software, or a combination of the three\ -low up front costs **Cloud Computing Models - Infrastructure as a Services (IaaS)** CORE IT HARDWARE\ \ -outsource servers, storage, hardware, networking services, and networking components to third-party\ -CPS is responsible for physical management of that infrastructure\ -degree of control will vary **Cloud Computing Models - Platform as a Service (PaaS)** CORE IT HARDWARE, SOME MANAGEMENT & ABILITY TO DEVELOP APPLICATIONS\ \ -tools or solutions remotely provided\ -building an e-commerce platform, advertising products, building a website **Cloud Computing Models - Software as a Service (SaaS)** ALL IT, MANAGEMENT, AND APPLICATION DESIGN\ \ -company is selling applications\ -commonly offered with Business Processes as a Services (BPaaS): outsources payroll, billing, logistics Cloud computing Deployment Models -Public: available to all people\ -Private: created for a single organization\ -Hybrid: one piece for specific organization, one for public use\ -Community: shared by multiple organizations to support a common interest **Cloud Service Provider (CSP)** -the third party who provides cloud computing services\ -often have advanced skills and experience\ -Cloud Controls Matrix - framework designed for best practices regarding cloud security **Committee of Sponsoring Organizations (COSO)** -developed guidance and best practices for internal control, enterprise risk management, governance and fraud deterrence **COSO Enterprise Risk Management** \*specific guidance on how to apply enterprise risk management\ \ SPRIG\ -Strategy & Objective Setting\ -Performance\ -Review & Revision\ -Information, Communication & Reporting\ -Governance and Culture **COSO Enterprise Risk Management - Strategy & Objective Setting** \*define risk appetite COSO Enterprise Risk Management - Performance \*prioritize their risks based on risk appetite COSO Enterprise Risk Management - Review and Revision \*reviewing a company\'s performance over time COSO Enterprise Risk Management - Information, Communication, and Reporting \*continual process in place that supports sharing internal and external information COSO Enterprise Risk Management - Governance and Culture \*sets the company\'s tone\ \*board oversight COSO Enterprise Risk Management Framework Components \*helps organizations establish ideal configurations for cloud options\ \ **CRRIME OIE**\ \ C - Control Activities\ R - Risk Assessment\ R - Risk Response: avoid, reduce, accept, share risk\ I - Information: timeliness and availability of info\ M - Monitoring\ E\ \ O - Objective Setting: will it help or hinder goals\ I - Internal Environment\ E - Event ID: are risks easier or harder to detect **What to do when implementing CSP?** 1\) create steering committee\ 2) assess risk of adding CSP\ 3) define systems and structure provided by CSP\ 4) integrate governance of CSP with existing risk management policies **Cloud Computing Risks** -competitor adoption\ -risk ecosystem - diversifying risk or concentrating risk\ -transparency\ -reliability and performance\ -lack of application\ -security and compliance\ -application portability (vendor lock-in) **Cloud Risks - High or Low** risk increases when:\ -moving from a private to public model\ -moving from a SaaS to IaaS model **S2 M2 - Enterprise and Accounting Information Systems** **Types of Processing Controls** -input: integrity of data meets parameters as it is entered\ -output: additional control over processing integrity (reconciliation, user review)\ -processing: ensure processing is accurate and complete\ -access: restrict user admission **General Controls in an information system** -software acquisition\ -IT infrastructure\ -security management\ -development, operations, and maintenance controls **Enterprise Resource Planning** -cross functional systems that support different business functions\ -data input is easier because a central repository is used **Accounting Information System** -a system that accountants and financial managers interact with the most\ -could be a component of an ERP **Reasonableness Test** -will likely prompt an error message when the offset transaction total exceeds the original transaction. **AIS - Three Subsystems** Transaction Processing Systems (TPS)\ -converts economic events into financial transactions\ -sales cycle, conversion cycle, expenditure cycle, payroll cycle\ \ **Financial Reporting System (FRS)\ **-aggregates daily financial information from the TPS and other sources for infrequent events (mergers, lawsuits)\ \ **Management Reporting System (MRS)\ **-internal financial information to solve day-to-day business problems, such as budgeting, variance analysis, cost-volume profit analysis **Goals of AIS Subsystems** -record valid transactions\ -classify those transactions\ -record transactions at correct value\ -record transactions in correct accounting period **AIS Process** 1\) Input by end user\ 2) source documents are filed\ 3) transactions are recorded in journal\ 4) transactions are posted to the GL and subsidiary ledgers\ 5) trial balances prepared\ 6) adjustment, accruals, and corrections are entered\ 7) financial reports are generated **Purchasing and Disbursements Cycle** 1\) vendor supplies product\ 2) company submits PO and invoices\ 3) purchasing department pays vendor\ 4) purchasing department records transaction, record fixed asset or inventory (in GL)\ 5) reports sent to managers **Treasury Cycle** 1\) decisions made by management on cash and working capital\ 2) bank executes transactions\ 3) record cash, interest, investment activity\ 4) report sent to managers **Payroll Cycle** 1\) Pay employees\ 2) Record payment\ 3) Report sent to managers **Revenue and Collections Cycle** 1\) Customer orders goods\ 2) Company sends goods and bills for payment\ 3) Customer pays\ 4) Company collects payment and sends it to bank\ 5) Company records sales transactions\ 6) Report sent to managers **Revenue and Cash Collection Cycle - Key AIS Functions** -approves or denies credit\ -records sales invoices\ -transmits inventory release to warehouse\ -digitally sends packing slip to shipping department\ -automatically updates with sales info\ -when paid - closes invoice, posts to ledger, updates customer payment record **Purchasing and Disbursement Cycle - Key AIS Functions** -determine if vendor is approved\ -prepare purchase order\ -terminal for receiving department to enters quantity received into RR\ -updates RR file, reconciles quantity received, closes PO\ -updates inventory sub ledger and GL\ -terminal for AP to enter invoice\ -approves payment, prints and distributes signed check to mail room for mailing **HR and Payroll Cycle - Key AIS Functions** -changes of employment data such as benefits, pay rates, deductions, employment status, new hires, and terminations\ -allocates labor costs, direct labor, indirect labor\ -calculates payroll, produces payroll register **Production Cycle - Key AIS Functions** -tracks production costs such as labor, materials, and MOH\ -tracks production run **Fixed Asset Cycle - Key AIS Functions** -create a record of PPE (useful life, SV, depreciation, location)\ -updates GL, prepares JE, creates depreciation schedule\ -calculates depreciation\ -when disposed - calculates G/L **Treasury Cycle - Key AIS Functions** -calculate changes in investments\ -estimates on dividends and interest **General Ledger and Reporting - Key AIS Functions** -updates GL continuously\ -at end of period AIS automatically produces a TB\ -accounting posts AJE\'s\ -AIS produces final financial statements\ -AIS automatically closes temporary accounts **Benefits of an Application Software Provider (ASP) for ERP** -lower costs\ -flexibility\ -great for smaller companies to only pay for what is used **Processes Driven by IT Systems** -automation: computer programs performing repetitive tasks\ -shared services: seeking out redundant services\ -outsourcing: contracting services to external provider\ -offshore operations **Offshore Operations** -information technology: infrastructure, networks\ -knowledge: specialized skills such as reading x-rays\ -business processes: call centers, tax, payroll, bookkeeping\ -software r&d: support in creating new technology **Risks in Outsourcing** -quality risk (defective product)\ -quality of service (poorly designed service agreement)\ -productivity\ -staff turnover\ -language skills\ -security\ -qualifications of outsourcers\ -labor insecurity **Forms of Technology Driven by IT Systems** -robotic process automation (RPA)\ \ -natural language processing (NLP) software\ \ -neural networks **Robotic Process Automation** -the use of software with artificial intelligence and machine learning capabilities\ -high-volume, repeatable tasks\ -lidar (self driving vehicles) **Natural Language Processing Software** -technology developed and used to encode, decode, and interpret human language\ -siri, alexa **Neural Network** -modeled after neurons that facilitate the function of human or animal memory\ -input layer, output layer, hidden layer **COSO - Principle 11** there should be general control over technology in order to achieve organizational goals **COSO - Principle 13** organizations should acquire, create, and use quality information in order to support internal controls **COSO - Principle 14** effective communication of information is necessary to support internal controls **Blockchain** -control system originally designed to govern creation of bitcoin\ -decentralized - decreased accountability\ -resistant to alteration\ -multiple transaction validation **Applying COSO to Blockchain** -focus on PREVENTATIVE and DETECTIVE controls\ -COSO Internal Control - Integrated Framework can still be applied **S2 M3 - Availability, Resiliency, and Disaster Recovery** **Business Resiliency** \*continuous operations or the ability to quickly return to operations after an event\ \ -business continuity plans\ -systems availability controls\ -crisis management\ -disaster recovery **Business Continuity Plans** \*more comprehensive than disaster recovery (involves non IT)\ 1) ID organization\'s key business processes (business impact analysis)\ 2) ID risks\ 3) Determine acceptable downtime\ 4) Implement mitigation and contingency plans **Business Impact Analysis** \*identifies how quickly business units can recover after a disaster\ ARIL RRR\ 1) establish the BIA approach\ 2) identify critical resources - ID org processes that are most vulnerable through interviews and documentation review\ 3) define disruption impacts\ 4) estimate losses\ 5) establish recovery priorities\ 6) create the BIA report\ 7) implement BIA recommendations **Define Disruption Impacts - BIA Step 3)** -classified as low, medium, high\ -low: can operate for an extended time if damaged\ -medium: can partially function for a limited time if damaged\ -high: significant recovery costs **Estimation of Losses - BIA Step 4)** 1\) Annualized Rate of Occurrence (ARO):\ number of occurrences/relevant years\ \ 2) Exposure Factor (EF)\ damage % of assets value\ \ 3) Single Loss Expectancy (SLE)\ exposure factor \* value of asset\ \ 4) Annualized Loss Expectancy (ALE)\ SLE \* ARO **BIA Step 5) - Establish Recovery Priorities** -system availability metrics - specific metrics that assess system availability and risk\ \ Most Common:\ -Agreed Service Time (AST): amount of time until services are operational\ -Minimal Downtime (DT): amount of time service is not working **Maximum Tolerable Downtime (MTD)** -amount of time business can tolerate an outage without causing long term consequences **Recovery Point Objective (RPO)** -max threshold for data lost, dollars lost, or inoperability **Recovery Time Objective (RTO)** -max time it should take to restore business operations **Mean Time to Repair (MTTR)** -average time it takes to restore business operations **Recovery Time Actual (RTA)** -actual time it takes to restore business operation **Recovery Point Actual (RPA)** -actual time it takes to recover to a pre-event state **System Availability Controls** -redundancy\ -system backups\ -uninterrupted power supply\ -IT infrastructure controls (malware)\ -physical controls **Crisis Management Plans** -unexpected, large scale incidents **Disaster Recovery** \*long term outages related to destruction of resources\ \*specify the steps required to resume operations (IT loss)\ \*ex. store duplicate files offsite\ \ AMMWT\ 1) Assess the risks\ 2) Identify mission-critical applications and data\ 3) Develop a plan for handling the mission-critical applications\ 4) Determine who\'s in charge\ 5) Test Plan \*most important **3 Types of Disaster Recovery Sites** -Cold Site: NO equipment in place, 1-3 days, cheapest\ \ -Warm Site: equipment on site but not plugged in, 0-3 days, moderately expensive\ \ -Hot Site: equipment and operations in place. immediately operational, expensive **Full Backup** -exact copy of entire database\ -very time consuming\ -quickest restoration to functionality\ -most organizations do this weekly and supplement with partial backups **Incremental Backup** -copying data items that have changed since the LAST backup\ -one day of transactions **Differential Backup** -copies all changes since the LAST FULL backup\ -take longer than incremental, restoration is simpler\ -stores 2 copies of backup **Replication vs. Mirroring** -replication: transferring data to a database at a secondary site\ \ -mirroring: copying a database onto a machine at the same site **S2 M4 - Change Management** **Change Management** -the policies, procedure, and resources employed to govern change in an organization\ -can be routine or complex changes **The Change Management Process** 1\) Identify and Define need for change\ 2) Design a high-level plan\ 3) Gain approval from management\ 4) Develop budget and timeline\ 5) Assign Personnel\ 6) Identify and Address Potential Risks\ 7) Implementation Map\ 8) Necessary Resources and Training\ 9) Test the System Change\ 10) Execute Plan\ 11) Review and Monitor Change **Documenting Systems Controls** -baseline configuration: establish a starting point before change is implemented\ \ -system component inventory: document that catalogs IT assets\ \ -acceptance criteria: qualitative or quantitative, measure categories such as performance, functionality, scalability, and compliance **Change Management Controls** -adopting basic policies and procedures\ -standardizing requests\ -separating certain job duties\ -pre- and post-implementation testing\ -reversion access (ability to revert to original) **Types of Change Enviornemnts** DTSPD\ -Development: write code to create application prototypes\ -Testing: developers test and debug code to id errors\ -Staging: testing programs that in are their final phases\ -Production: environment in which application is deployed\ -Disaster Recovery **Integration Risks** -user resistance\ -lack of management support\ -lack of stakeholder support\ -resource concerns\ -business disruptions\ -lack of system integration **Outsourcing Risks** -lack of organizational knowledge\ -uncertainty of third party\'s knowledge and management\ -lack of security **Logging** \*critical part of testing and implementing change policies\ -application logs\ -change logs\ -event logs (access to files)\ -firewall logs (flow of network traffic)\ -network logs\ -proxy logs (access to internet) **Waterfall Model** -focus on TESTING and CHANGE REVIEW\ -different teams of employees performing separate tasks in sequence\ -requires more time\ -benefits not realized until completion\ -no customer input **Agile Method** -different teams work on different phases or tasks simultaneously\ -flexible approach\ -shorter deadlines\ -more communication **Patch Management** -identifying specific VULNERABILITIES or software bugs and addressing them with patches or fixes\ -SOC2 requires rules on patch management\ \ -Reactive: release as vendor discovers new vulnerabilities\ -Proactive: identify weaknesses as we go **Patch Management Program** EVTAV\ \ 1) Evaluate New Patch Releases\ 2) Use Vulnerability Tool\ 3) Test Patches in Test Environment\ 4) Approve and Deploy Patches\ 5) Verify Patches Deployed **1) Evaluate New Patch Releases** -IT evaluates patches released by vendors\ -devises plans to implement them **2) Use Vulnerability Tool** -helps organizations track security controls and ID weaknesses on their own so they can ID patches as needed **3) Testing Patches in a Test Enviornment** -implement patches in a non production environment **4) Approving and Deploying Patches** -patches must be successfully reviewed and tested by IT\ -deployed in a scheduled downtime **5) Verifying Patches Deployed** -performed after testing and deployment, followed by monitoring **Conversion Methods** -Direct: ceasing use of the old system and starting a new one immediately\ \ -Parallel: new system is implemented while the old system is still in use (safest option)\ \ -Pilot: conversion on a small scale while continuing to use older system\ \ -Phased: transition gradually adds volume to the new system while still operating old system\ \ -Hybrid: any combo **Types of Testing Methods** -Unit Testing: considers the smallest increment, or unit, of application\ \ -Integration Testing: performed after unit testing to ensure all components can work together\ \ -System Testing: combined modules of application work as designed\ \ -Acceptance Testing: may involve beta testing, assess if application is meeting users goals **S2 M5 - Introduction to Data Collection and Data Lifecycle** **Data Lifecycle Process** DCP SAPAP\ \ 1) Definition\ 2) Capture (or creation)\ 3) Preparation\ 4) Synthesis\ 5) Analytics and Usage\ 6) Publication\ 7) Archival\ 8) Purging **Data Lifecycle - 1) Definition** -defining what type of data a business needs **Data Lifecycle - 2) Capture/Creation** -creating data internally or capturing data from external sources **Data Lifecycle - 3) Preparation** -enhancing completeness and integrity of data (moving data adds risk)\ -data integration\ -cleaning data\ -data encryption **Data Lifecycle - 4) Synthesis** -create calculated fields to prepare data for quicker use\ \ -OPTIONAL **Data Lifecycle - 5) Analytics and Usage** -data being useful to the company internally **Data Lifecycle - 6) Publication** -data may also be shared with external users **Data Lifecycle - 7) Archival** -data is moved from active systems to passive systems\ -free up storage and resources **Data Lifecycle - 8) Purging** -data becomes useless\ -remove data from system\ -there is no requirement (legal or otherwise) **Data Collection Types** Extract, Transform, Load (ETL)\ -Extract: data is gathered and retrieved from existing source\ -Transform: transformed into useful info\ -Load: tools such as report generation or analysis\ \ Active Data Collection**\ **-directly ask for data from employees, customers, users.\ \ Passive Data Collection**\ **-gathering info without direct permission ex. tracking web cookies, AI Algorithms **Complexities when obtaining data from a External Source** -copyrights\ -safety\ -integrity **S2 M6 - Data Storage and Database Design** **Operational Data Store (ODS)** -A type of database often used as an interim area for a data warehouse\ -holds info on operational activities such as: customer orders, sales, vendor payments **Data Warehouse** -large data repositories that are centralized\ -used for reporting and analysis\ -must be continuously updated to remain relevant **Data Mart** -like a data warehouse but focused on a specific purpose such as marketing\ -different departments need tailored data marts **Data Lake** -contains both structured and unstructured data\ -data in its raw format **Relational Databases** \*most common method for storing structured data\ \*creates a data model\ \*ALL data required for business processes is stored here\ \ -completeness\ -no redundancy\ -business rules enforcement (IC and rules)\ -communication and integration of business processes **Data Elements in a Relational Database** -tables\ -attributes: columns, unique to each table\ -records: rows\ -fields: intersection of column and row\ -data type: numerical, text, data\ -database keys **Types of Database Keys** -primary key: unique identifier\ -composite primary key: no primary key so more than one attribute create unique identifier\ -foreign key: attributes in one table than are primary keys in another **Data Dictionary** -information about the structure of the database\ -includes a description of each data element **Normalization of Data** -First Normal Form (1NF): each cell contains only one piece of info, each table has a primary key\ -Second Normal Form (2NF): all non-key attributes in a table depend on the primary key\ -Third Normal Form (3NF): no non-key attributes depend on other non-key attributes **Data Model versus Database Schema** -Model: high-level design of the data structures\ \ -Schema: actual implementation and execution of the design in a specific relational database. Set of instructions that tell database engine how to organize data **Types of Data Models** CLP\ \ -Conceptual Model: table name and relationships\ -Logical: add primary keys and foreign keys\ -Physical: add column data types **Types of Database Schemas** -Fact Table: contacts measures or metrics but no foreign keys\ -Dimension Table: descriptive or contextual data for measures\ -Star Schema: organized into a central fact table with associated dimension tables around it\ -Snowflake Schema: more complex, similar to star schema but dimension tables are further normalized **M2 M7 - Data Extraction, Integration, and Process Documentation** **Sructured Query Language (SQL)** -computer language to interact with data (tables, records, and attributes) in a relational database\ \ -Sql Commands: SELECT, FROM, JOIN, GROUP BY, HAVING, WHERE and ORDER BY\ -Database Elements: references to table names, attribute names, or critera **SELECT** -required 1st clause\ -indicates which attribute the user wishes to view\ - \* indicates select all **FROM** -required 2nd clause\ -which table contains the attributes that the user is selecting\ ex. FROM Products **WHERE or HAVING** -WHERE: filter results\ ex. WHERE Sales\_Price \> 10\ \ -HAVING: filter aggregated date\ ex. HAVING SUM(Qualtity\_Sold) \> 200 **GROUP BY** -when needed to aggregate data into SUBTOTALS based on categories\ ex. GROUP BY Customer\_ID **JOIN functions** INNER JOIN - retrieve data from more than one table, only retrieves data that is a match\ \ LEFT JOIN - retrieve data from more than one table, will retrieve data that has no match **BPMN Activity Models** -standardized tool for creating diagrams with symbols and rules to depict business processes\ \ -FLOW ACTIVITIES: are the processes an organization is involved in\ -POOLS: how many organizations are involved in a process (ex. Sales, Customer\ -SWIM LANES: more granular, indicate segregation of duties (ex. within Sales = Sales Rep, Cashier) **BPMN Activity Models - Events** Start Events\ -One start event based on pools.\ -Thin Circle\ \ **End Events\ **-One end event, potentially two if the process can be cut short early.\ -Thicken Circle\ \ **Intermediate Events**\ -Indicate when something changes in the course of a process.\ -Double-Lined Circle **BPMN Activity Models - Tasks** -every action in a process is documented as a task\ -rectangle with rounded edges **BPMN Activity Models - Flows** Sequence Flows\ -connect objects within the pool\ -smooth-lined arrows.\ \ Message Flows\ -when information is communicated between two seperate pools\ -dotted lines **BPMN Activity Models - Gateways** -a question not a task\ -ex. sufficient quantity?\ -diamond shaped **Data Flow Diagrams** -standardized tool for creating diagrams\ -granular level of detail\ -LOGICAL FLOW\ \ -Process: (action that results in data changing) circle or rectangle with rounded edges\ -Data Flow: arrow\ -Data Store Or Warehouse: open-ended rectangle\ -External Entity: square **Flowcharts** -LOGICAL and PHYSICAL flow\ -visual representation of how documents and info flow through a process **System Interface Diagram** -interaction of LOGICAL AND PHYSICAL flow\ -demonstrate how users and functions, both internal and external, interface with systems