INFS 247 Flashcards (Lectures 6-9 + Access)

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/90

encourage image

There's no tags or description

Looks like no tags are added yet.

91 Terms

1
New cards

Operational information

Collected and used for day-to-day operations (e.g., ATM withdrawal, booking a flight); lives in an operational database

2
New cards

Analytical information

Used for analysis like trends, sales, product statistics, and growth projections; based on operational data and lives in a data warehouse

3
New cards

Why keep a data warehouse separate? (3 reasons)

1) Time horizon: operational DBs keep 60-90 days, warehouses keep years. 2) Performance: analytical queries slow operations. 3) Structure: one database cannot be efficient for both uses

4
New cards

Source systems

Operational databases that feed the warehouse; each has its original operational purpose plus feeding the warehouse. Can include external sources like census, market research, weather

5
New cards

ETL

Extract - Transform - Load: extract useful data from sources, transform it to fit the warehouse model and ensure quality, load cleaned data into the warehouse

6
New cards

Data warehouse (target system)

The destination / target system that periodically pulls selected, analytically useful data from source systems

7
New cards

Front-end (BI) applications

Applications that give most users indirect access to the warehouse; S-E-W-F = Sources, ETL, Warehouse, Front-end

8
New cards

OLTP

Online Transaction Processing: inserting, modifying, deleting, and retrieving data for operational purposes

9
New cards

OLAP

Online Analytical Processing: retrieving data from analytical databases / warehouses for analytical purposes

10
New cards

Slice and dice (OLAP)

Adds, replaces, or eliminates specified attributes or values in the displayed result (e.g., limit result to only Stores 1 and 2)

11
New cards

Pivot / Rotate (OLAP)

Moves a dimension's values from one axis to another; reorganizes layout, same data, same detail level

12
New cards

Drill down (OLAP)

Makes granularity finer / more detail (e.g., product category -> individual products)

13
New cards

Drill up / Roll up (OLAP)

Makes granularity coarser / less detail (e.g., individual products -> product category)

14
New cards

Data visualization

Graphical display (scatterplots, area charts, heat maps) using points, lines, color, shading, symbols to help comparison, pattern recognition, and change detection

15
New cards

Direct access to warehouse

Technical users write SQL directly or use OLAP/BI tools that generate the SQL for them

16
New cards

Indirect access to warehouse

Most users use front-end / BI applications (pick parameters like date range and stores and get results) because they cannot write SQL

17
New cards

Executive dashboard

Organized, easy-to-read display of a few critically important performance queries for higher-level decision makers; needs little or no training

18
New cards

Big Data

Massive volumes of diverse, rapidly growing data that is NOT formally modeled for efficient retrieval; heterogeneous, semi-structured and unstructured, can be 80-90%+ of stored data

19
New cards

Big Data vs warehouse relationship

Big Data methods do NOT replace databases and warehouses; they complement them

20
New cards

Data warehouse vs data lake: process

Warehouse: ETL (transform BEFORE loading). Data lake: E-L (load first, transform later when needed)

21
New cards

Data warehouse vs data lake: schema

Warehouse: schema defined up front, predefined structure, heavy/costly prep, predictable yield. Lake: raw data in native format, schema not defined until queried, simpler/cheaper prep, less predictable yield

22
New cards

Data lake analogy

Warehouse = agriculture (clear, plow, tend, reliable crop). Data lake = hunting and gathering in a fenced wild area (cheap prep, unpredictable yield)

23
New cards

Insurance example - Big Data

Telemetry data from tracking devices in insured cars; used to price policies and segment by distance driven, speed, hard acceleration/deceleration

24
New cards

Data mining

Discovering novel and interesting patterns in large amounts of data; good patterns are accurate, meaningful, understandable, actionable

25
New cards

Predictive analytics

Using past data to predict future events

26
New cards

Market basket / Association rule mining

Finds groups of items that tend to appear together in transactions; rule form X -> Y means transactions containing X are likely to also contain Y

27
New cards

Support (formula)

Support = (# transactions containing X AND Y) / (# of ALL transactions). Measures SIGNIFICANCE of the rule

28
New cards

Confidence (formula)

Confidence = (# transactions containing X AND Y) / (# transactions containing X). Measures STRENGTH of the correlation

29
New cards

Support vs Confidence trap

Support denominator = ALL transactions. Confidence denominator = transactions containing X only. A rule is kept only if it meets BOTH thresholds

30
New cards

Artificial Intelligence (AI) - IBM definition

Technology that lets computers and devices learn, read, write, create, and analyze

31
New cards

Pattern recognition

Machines identifying patterns in data and using them to decide or predict; a vital component of AI

32
New cards

Descriptive AI

Discriminative AI that describes patterns in data. Example: CLUSTERING for customer segmentation

33
New cards

Predictive AI

Discriminative AI that uses data and patterns to forecast outcomes. Example: CLASSIFICATION for credit card applications

34
New cards

Generative AI

Uses data, patterns, and content to generate NEW content (e.g., ChatGPT, Copilot, Gemini; multimodal: Sora, Udio)

35
New cards

Large Language Model (LLM)

Neural network with billions of parameters, fed enough examples to recognize patterns and synthesize content from patterns + user prompt. Foundation came from Google in 2017

36
New cards

How an LLM works

Predicts the next word probabilistically, chains predictions to build phrases/sentences; trained on petabytes of text for months on high-end hardware

37
New cards

Hallucination

AI produces a confident but totally wrong / made-up answer. Happens because LLM output is probabilistic, not deterministic - so verify

38
New cards

Prompt engineering

The content and context of your question / prompt determine the answer you get

39
New cards

AI supply chain (in order)

Lithography makers (ASML) -> Chip manufacturers (TSMC) -> Chip developers (NVIDIA) -> Cloud/data centers (Microsoft Azure) -> Generative AI companies (OpenAI) -> Copilot/ChatGPT

40
New cards

AI model vs AI chatbot

Model = underlying software / LLM, BACK END (GPT, Claude, Gemini models). Chatbot = user-facing app/interface that uses a model, FRONT END (ChatGPT, Claude, Gemini app, Copilot)

41
New cards

Model weights

Numerical floating-point parameters inside a model that store what it learned

42
New cards

Closed model

Inner workings, architecture, training data, and weights kept internal; you rent access via chatbot or API (GPT, Claude, Gemini)

43
New cards

Open-weight model

Weights are shared so you can download and run locally, but training data and code are often hidden (Qwen, DeepSeek, Meta open models)

44
New cards

True open-source model

Weights, source code, AND training data released for full reproducibility. Example: OLMo

45
New cards

Token

A small chunk of text, about 3/4 of an English word; about 1,000 tokens per 750 words. Pricing can be by tokens processed

46
New cards

AI chatbot/assistant vs AI agent

Chatbot: prompt-driven, collaborative, human reviews/decides, moderate autonomy. Agent: goal-driven, multi-step, perceives-reasons-acts-learns, uses tools, high autonomy, human sets goals and monitors exceptions

47
New cards

Supply chain

A network between a company, its suppliers, and its customers to produce and distribute products/services; includes all steps to get a product/service to the customer

48
New cards

Upstream (supply chain)

Supplier side: raw material suppliers -> Tier suppliers -> company. Here company is the CUSTOMER, wants its customer power high

49
New cards

Downstream (supply chain)

Customer side: company -> direct customers -> end customers. Here company is the SUPPLIER, wants its supplier power high

50
New cards

SCOR model - 6 processes

Plan, Source, Make, Deliver, Return, Enable (P-S-M-D-R-E)

51
New cards

SCOR: Enable

Jointly manage all SCM processes and elements (rules, contracts, regulations, risks, HR) and measure/monitor performance using data and information systems

52
New cards

Logistics

Part of supply chain that plans, implements, and controls efficient forward AND reverse flow and storage of goods, services, and related information from origin to consumption - more than just transportation/shipping

53
New cards

Back order rate (formula)

Back order rate = # back orders / # of all orders. Goal: DECREASE it (higher rate = more cancellations, costs, lost goodwill)

54
New cards

Inventory turnover rate (formula)

Inventory turnover = Cost of Goods Sold (COGS) / value of average (or end-of-period) inventory. Tells how many times a year company sells its entire inventory. Goal: generally INCREASE it

55
New cards

Days inventory (formula)

Days inventory = 365 / inventory turnover rate. Example: turnover 9.16 = about 40 days

56
New cards

Rate of return (formula)

Rate of return = # returned items / # of all shipped (bought) items. Goal: DECREASE it

57
New cards

SCM decision levels

Operational = day-to-day (how much inventory to order today). Tactical = medium-term (annual supplier contract terms). Strategic = longer-term (use overseas suppliers in next 3-5 years). Based on TIME FRAME, not job title

58
New cards

Bullwhip effect

Demand information gets distorted (delayed) as it passes through the supply chain, causing excessive or insufficient inventory

59
New cards

RFID

Radio Frequency Identification microchip; costs more than a barcode but tracks any item automatically at each stage, holds more information, accessed more quickly

60
New cards

CRM

Customer Relationship Management: use existing customer data to drive loyalty and engagement and maximize profitability from lapsed, existing, and potential customers; integrates sales, marketing, and customer service

61
New cards

RFM analysis

Recency (when was most recent purchase?), Frequency (how often do they buy?), Monetary value (how much did they spend?). Each scored 1-5 in quintiles, 5 = BEST, forming a 3-digit cell like 5-5-4

62
New cards

Starbucks loyalty - data collected

Behavioral (time of day, weekday/weekend, frequency, location, length of visit via wifi) and transactional (product type, payment method)

63
New cards

Business process

A set of activities that, once completed, accomplish a company's task or goal (e.g., making a sale, credit check, resolving fraud)

64
New cards

BPR

Business Process Reengineering: analysis and redesign of workflows within and between enterprises to optimize processes. Hammer 1990: Don't Automate, Obliterate

65
New cards

BPR steps (cycle)

1) Identify process to improve, 2) Diagram/review/analyze the AS-IS process (how it works now), 3) Design the TO-BE process (how it could be better), 4) Test and implement the To-Be

66
New cards

Automating (change type)

Use technology to perform the process (e.g., computerized phone menu instead of human rep)

67
New cards

Streamlining (change type)

Remove unnecessary, outmoded, or inefficient steps (e.g., same phone menu with fewer levels and shorter wording)

68
New cards

BPR / Reengineering (change type)

Completely redesign the process instead of doing the same thing faster/cheaper (e.g., company detects fraud first with data mining, calls customer, cancels card immediately)

69
New cards

Paradigm shift (change type)

One company's BPR redefines the whole industry (e.g., other card companies adopt proactive fraud detection too)

70
New cards

MS Access - Primary key

One or more fields that uniquely identify each record; set via Table Tools Design tab, Tools group, Primary Key button

71
New cards

MS Access - Foreign key

A field that matches the primary key of another table; this is how tables link. Should have same data type and size as the PK it points to

72
New cards

Composite primary key

A key made of two or more fields together. WORKS_ON key = PID + Essn, so same employee-project pair cannot repeat

73
New cards

WORKS_ON table

Bridge table for the many-to-many Employee-Project (WorksOn) relationship; fields PID + Essn, composite PK, FKs to PROJECT and EMPLOYEE

74
New cards

ExampleCompany relationships

Department 1-to-many Employee (WorksFor), Department 1-to-many Project (Controls), Employee many-to-many Project (WorksOn, stored as WORKS_ON table)

75
New cards

PROJECT foreign key in Access

Called ControllingDeptNo in the Access table (ER diagram calls it Dnumber); FK to DEPARTMENT.Dnumber

76
New cards

Referential integrity

Every non-null foreign key value must match an existing primary key value; you cannot add a record to the FK table unless a matching record exists in the linked table

77
New cards

Creating relationships in Access

Database Tools tab -> Relationships -> Show Table (add all 4 tables) -> drag PK from one side to FK on many side -> check Enforce Referential Integrity -> Create. Shown as 1 to infinity

78
New cards

MS Access - Default data type

Default data type for a new field = SHORT TEXT

79
New cards

Short Text Field Size

Maximum characters 1 to 255, default 255. Field Size is only available for Short Text and Number fields

80
New cards

Datasheet view vs Design view

Datasheet = rows and columns of data. Design = defining structure: add/remove/rearrange fields, set names, data types, properties, primary key

81
New cards

Navigation Pane

Central place to work with database objects (tables, queries, forms, reports); open/close with Shutter Bar button

82
New cards

Access saving

Access AUTO-SAVES data when added/changed/deleted, so a new database is saved to a file before you start. You still save design changes when closing

83
New cards

Data entry order in Access

Parent before child, due to referential integrity: 1) DEPARTMENT first, 2) EMPLOYEE and PROJECT, 3) WORKS_ON last

84
New cards

Query (Access object)

Extracts, combines, and displays data from one or more tables based on criteria; can sort and summarize. Create tab -> Query Design, run with ! Run icon

85
New cards

Form (Access object)

Makes inserting, modifying, and deleting records easier and more visually appealing; data from tables or a query. Create tab -> Form Wizard, Columnar layout in tutorial

86
New cards

Report (Access object)

Used primarily for PRINTING; labels, groups, sorts, and summarizes data. Create tab -> Report Wizard, in tutorial: by DEPARTMENT, Stepped layout, Portrait

87
New cards

Access objects summary

Table = stores data, Query = extracts/combines/summarizes data, Form = easy data entry/editing, Report = formatted output mainly for printing

88
New cards

Practice: Milk -> Cereal

200 transactions, 40 contain milk, 10 contain both. Support = 10/200 = 5%, Confidence = 10/40 = 25%

89
New cards

Practice: Inventory turnover

COGS $600M, average inventory $75M. Turnover = 600/75 = 8 turns, Days inventory = 365/8 = about 45.6 days

90
New cards

Practice: Tokens in 1,500 words

About 2,000 tokens (1,000 tokens per 750 words)

91
New cards

Practice: RFM code 5-1-2

Very recent purchase (5), low frequency (1), low-to-mid monetary value (2)