BUAD 342 EXAM #1

make table queries

(to create a new table using data from one or more existing tables).

Key Information Processing Steps

-input recording (capturing) data -processing maintaining reference data -output reporting data (generating output)

Making "Things" Event (Conversion Process)

-varies on what is being converted Examples: Assembly. Growing. Excavating. Harvesting. Preparing a meal. Transforming knowledge.

source document

A business paper from which information is obtained for a journal entry

query

It is a request for data or information from a database table or combination of tables.

volume

More data- The amount of data being created is vast compared to traditional data sources. More of our own data (archive, junk, log files), added free or public data, premium service data.

velocity

Speed at which new data arrives. Data is being generated extremely fast —now more streaming data projects which allows the potential of near real time analysis.

purchase order

Supports the Negotiate Purchase Order business event by summarizing the details (the who, what, where, when, and why) of each purchase order transaction -paper trail

Sales Invoice

Supports the business event Deliver Goods or Services (the sale) by summarizing the details (the who, what, where, when, and why) of each sale transaction -have the legal right to payment -request of payment

variability

different flows in data

NOT

selects all records that do not match this criteria

shipper

shipper id, shipper name, shipper phone

totals query

(a variation of a Select query) - You can use the Totals row in a basic Select query, but a Totals query allows you to ask more complex questions by grouping and summarizing data via the Sum function (an aggregate function).

delete queries

(to delete records from one or more tables, including using a criteria to specify which rows should be deleted)

update queries

(to make changes to records from one or more tables, including using a criteria to specify which rows should be updated)

NoSQL

- Not only SQL or Not ordinary SQL - databases. -uses schema on read

Batch Processing

- Transaction data is collected and stored for processing at a scheduled time or when a specified amount of data has been collected. (So master files that updated with transaction totals are not always up to date.) Examples: Payroll Credit Card Transaction Customer Billing Customer Statements

select query

- Use to view data from only certain fields in a table, review data from multiple tables simultaneously or pull data based on certain criteria.

parameter query

- prompts the query user for information to be used in executing the query. For example, use this type if you want a query that displays only the order ids for the customer id that the user inputs.

Buying "Things"

-Acquire and pay for resources (e.g. financing, human skills, materials and supplies, and plant and equipment), -In order to function/operate

Master File

-Balance data or status of entity a point in time E.g. -Inventory, Customer, Employee, Vendor, Cash Accounts

Transaction File

-Business activity data (used to update master files) E.g. - Order files, billing files, shipping files, cash receipt files

Customer Relationship Management Systems (CRM)

-Internal and external to customers -Provides information to coordinate all of the customer related business processes -Sales -Marketing -Customer service -Helps firms identify, attract, and retain the most profitable customers

Enterprise Resource Planning Systems (ERP)

-Internal system -Collect data from different firm functions and store data in single central data repository -Resolves problems of fragmented data Enables: -Coordination of daily activities -Efficient response to customer orders (production, inventory) -Effective decision making by managers about daily operations and long-term planning

Supply Chain Management Systems (SCM)

-Interorganizational system -Manages firm's relationships with suppliers and customers -Shares information about: Orders, production, inventory levels, delivery of products and services -Goal: Right amount of products to destination with least amount of time and lowest cost

Making "Things"

-Make new "things" to sell -Using "things" they buy, or customize "things" they buy to get them ready for resale -Essentially, converting resources acquired into goods and services for customers

Real-time Processing

-Transaction data is immediately processed or close after the transaction occurs. (So master files that updated with transaction totals are kept current.) Examples: Airline or Ticket Reservation Systems Point of Sale (POS) Terminals

Source Data Automation (SDA)

-collecting data at its point of origin in digital form. Examples:barcode holds the info for that product -not supposed to scan barcode more than once -Biometric device- finger print -Magnetic Ink Character Recognition (MICR) (such as those on checks) -Bar Code Reader/Scanner -Optical Character Recognition (OCR) -Magnetic Strip on credit cards -Credit card chips -RFID - radio frequency identification devices -Touch screens -Voice recognition & other biometrics devices

data warehouse

-is a central repository of information created with data from transactional systems, relational databases, and other sources. -A solution to enable a large number of users to concurrently monitor business performance and extract insights useful for decision making.

SQL (structured query language)

-is actually a standardized query language that can be used to accomplish many tasks (not just ask questions). -creating table structures, -updating table structures, -joining tables, adding or updating data, requesting information from a database.

big data

-refers to the dynamic, large and disparate volumes of data being created by people, tools and machines; it requires new, innovative and scalable technology to collect, host and analytically process the vast amount of data gathered in order to derive real-time business insights. -Structured - traditional database which assumes data that will go into tables, so data is structured to fit that model No structure - can be text or non text. Snap chats, tweets, mail messages, PowerPoint presentations, Word documents, text messages, JPEG images, MP3 audio files video files. Semi structured - has information associated with it, such as metadata and tags. e.g. JSON (JavaScript Object Notation), data represented as graphs

Buying "Things" Event AKA Procurement/Expenditure Process

1)Identify need and possibly internally request the purchase of goods or services by an authorized purchasing agent. 2)Negotiate a purchase order with a vendor. 3)Receive and inspect goods or services from a vendor. 4)Store and/or maintain goods. 5)Pay a vendor for goods or services. 6)Return goods to a vendor. Example: Walmart identifies that they need milk Find a vendor Purchase milk Make sure they received the actual milk (not strawberry) Make sure you have a place to put it (shelf or storage) Pay the vendor Return goods if needed

how to create data base schema on write

1)Map Reality (Understanding of Business Process Needs) into Conceptual Model (e.g., E-R diagram or Object Oriented graphic) 2)Map Conceptual Model into Logical Model 3)Create Physical Database using DBMS software

Oder Cash Process (Selling "Things" Event) AKA Order to Payment, Revenue, Sales

1)Market goods or services to potential or returning customers (and/or perform support activities that help customers choose the organization as a vendor). 2)Negotiate (with a potential or returning customer) the terms of a sales order for goods or services. 3)Select and inspect goods or services to be delivered to the customer. 4)Prepare goods or services for delivery to the customer. 5)Deliver goods or services to the customer. 6)Receive payment for goods or services from the customer. (have a sale!) 7)Negotiate the terms of, and possibly accept, a customer return of goods

3 interdependent processes

1)buying things 2)making things (crating/buying things for resale) -3)selling things (can be services or goods) -All three processes are related and have to flow through each other -One creates the need for the other -Goods/services and "cash" (wealth generation) flows between the three

traditional databases disadvantages

1-are expensive to scale up or out, 2- need specially trained professionals to oversee them, and 3- can't process large volumes of data in near real time.

Business first, technology....

2nd

EDI (electronic data interchange)

A set of standards for exchanging messages containing formatted data between computer applications. -paperless

schema on write approach

A traditional approach to creating databases. A schema is a fancy word for a model of the data. A model is defined up front, Data is captured and recorded per the specified model. When organizations want to read the data, they access it using the schema (model) defined up-front.

AND

BOTH conditions in the criteria must be met

schema on read approach

Data is collected as-is, without making it conform to a particular model or predefined structure. -Then organizations decide later how to process the data once they decide how they want to read and use it. -A schema (Model) might not be determined until accessing data, based what is most relevant to the task at hand. -allows for more flexibility -helpful when you try to consolidate multiple data sets. -eliminates the need for upfront modeling.

Selling "Things"

Delivering goods and/or services to customers and collect payment.

veracity

Differences in data accuracy/quality. Big data is sourced from many different places; as a result, you need to test the veracity and quality of the data the truth and the quality of the data, the usefulness of the data

variety

Differences in types/sources of data - (structured, unstructured, and semi-structured).

ETL

E- extract/capture T- transform data scrubbing/cleansing L- load and index

OR

EITHER condition in the criteria must be met

receiving report

a document that records details about each delivery, including the date received, shipper, supplier, quantity received

bill of lading

a legal contract that defines responsibility for goods while they are in transit -Supports the Deliver goods or services business event by summarizing the who, what, where, when, and why of the fulfillment of each sales order. -document between seller and shipper Ex. Amazon and Fedex Fedex is responsible until it gets to buyer -who what when where and why

packing slip document

a shipping document that accompanies deliveries, processed by the shipping department -Supports the Prepare goods or services for delivery business event by summarizing the who, what, where, when, and why of the inventory selected to fulfill each sales order

Porter's Value Chain

analysis recognizes that organizations are elements of a value system (some call it a supply chain). -upstream and downstream management

left join

asking for all the rows from the table displayed or listed on the left and, if there's a match in the table on the right match it up. When a value in the left table doesn't have a corresponding match in the right table, you see a null value in the output.

category

category_id, category_name, category_description

Suspense files

contain data awaiting some action to complete their processing. Examples include customer payments waiting to be deposited or records identified as incomplete or erroneous that need correction and reentry to complete processing. Files that contain errors

History files (which may be called archive files)

contain inactive past or historical data. -Examples could include tax return files from previous years or human resource files of employees who are no longer with the organization.

Reference File

contain referential data such as a tax rate schedule, a volume pricing list, or a listing of the general ledger chart of accounts.

customer

customer_id, cust_first_name, cust_last_name, cust_birthdate, cust_income_bracket, cust_address, cust_city, cust_region, cust_zip, cust_country, cust_phone, cust_fax

employee

employee_id, emp_last_name, emp_first_name, emp_title, emp_birthdate, emp_hire_date, emp_address, emp_city, emp_region, emp_zip, emp_country, emp_phone, emp_notes, emp_reports_to

union

is another method for combining two or more tables by appending rows of data from one table to another. Ideally, the tables that you union have the same number of fields, and those fields have matching names and data types.

right join

is asking for all the rows from the table displayed or listed on the right and, if there's a match in the table on the left , match it up. When a value in the right table doesn't have a corresponding match in the left table, you see a null value in the data grid.

full outer join

is asking for every row from the table on the left and every row from the table on the right. If they have matching values, the computer matches them up. If they don't have matching values, the computer will still display each row, but show nulls where they don't match.

inner/equi join

matches up records between two tables based on designated values. The result set that you get contains the rows from the table on the left that match the table on the right. If there are rows in either table that don't match, they aren't returned in the result set.

BETWEEN

often used for dates in a criteria

order

order id, order_date, required_date, order_price_per, quantity_ordered

porduct

product id, product_name, unit list price, units in stock, units on order, reorder level, discontinued code

point of purchase

receiving good/service

sale

sales invoice id, shipped_date, freight_charges, ship to address, ship to city, ship to region, ship to postal code, ship to country, sales_price_per, quantity_sold, item discount

big data =

schema on read

traditional data =

schema on write

supplier

supplier id, supplier name, contact name, contact title, supplier address, supplier city, supplier region, supplier zip, supplier country, supplier phone, supplier fax

once goods have been shipped =...

the point of sale! delivery of good/service

append queries

to add records from one or more tables to the end of one or more other tables),

crosstab query

used to calculate (sum, average, count, etc.) data that is grouped by two types of information - one down the left side of the datasheet and one across the top. The cell at the junction of each row and column displays the results of the query's calculation. For example, if you wanted to review product subtotals, but you also want to aggregate by month, so that each row shows subtotals for a product, and each column shows product subtotals for a month.

LIKE

used to test whether or not text matches a pattern

relational model

uses a two-dimensional table of rows and columns of data. Rows are records and columns are fields