BUAD 342
P2P ‐ Data analysis associated with Negotiating Orders of Goods or Services from Vendors- data recorded about purchase ordering can be used to generate a variety of analyses, including:
A listing of open purchase orders (approved orders not yet received and not yet recognized as liabilities). Purchase orders approved and executed during the period. An aging of outstanding purchase orders. An estimate of anticipated purchasing expenditures (forthcoming cash disbursements based on outstanding open purchase orders and service level agreements), which is useful for cash planning. Various summary reports for the purchasing manager and the inventory manager. Analyses useful in tracking and periodically evaluating supplier performance. Analyses useful in evaluating how well the purchasing function minimizes total processing costs, not just the actual costs of ordering and purchasing the goods.
For example, following are just a few of the tasks that programmers might write code to accomplish:
Create or update database structures (create a new database, add or update table/field structures, etc.) Retrieve data for further processing Add or delete database users Maintain database security Add new data records, update existing records and/or delete records Generate printed or electronic outputs (source document data flows, reports, query results, etc.) Design user interfaces (UI)
Examples of source data automation technologies include
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 device1 Touch screens Voice recognition and other biometrics devices
order to cash process typically includes the following business events:
Market goods or services to potential or returning customers (and/or perform support activities that help customers choose you as a vendor). Negotiate (with a potential or returning customer) the terms of an order for goods or services. Select and inspect goods or services to be delivered to the customer. Prepare goods or services for delivery to the customer. Deliver goods or services to the customer. Receive payment for goods or services from the customer. Negotiate the terms of, and possibly accept, a customer return of goods.
P2P ‐ Data analysis associated with Receiving Goods or Services from Vendors--Data recorded by the Receipt of Goods or Services (and corresponding recognition of a liability) are used to produce several data flows and reports. These include:
Summaries and analysis of goods and services rendered. A listing of receipts during a period of time. A listing of purchases for a specified time period. A listing of receipts per vendor. An analysis of vendor performance (e.g., the time between order and delivery, frequency of back ordered items, and the condition or quality of the goods or services received). A listing of aged payables. An inventory listing or status report
database management systems that differ from relational database management system in the following ways:
They are an approach to data management and database design that's useful for very large sets of distributed data. They do not have fixed schema determined (up front) at design. They do not use SQL as their primary query language. While it's true that some NoSQL systems are entirely non‐relational, others simply avoid selected relational functionality such as fixed table schemas and join operations. For example, instead of using tables, a NoSQL database might organize data into objects, key/value pairs or a series of individual tuples (rows).
business event
" we will differentiate between an overall business process and the individual activities that comprise that process by using the term________ business event to denote each single activity within a business process
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).
application programs
(programs written to accomplish certain tasks) and code to perform tasks in a database environment. To create programmed computer instructions, the developer uses macros or application languages included in the DBMS or some other language that is able to interface with the database
Source Data Automation
(sometimes referred to as automated data collection) describes a process of collecting data at its point of origin in digital form. This eliminates the duplicated effort, potential for errors, and delays associated with manually collecting source data then reentering it into a computer.
append queries
(to add records from one or more tables to the end of one or more other tables),
make-table queries
(to create a new table using data from one or more existing tables).
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)
A third typical business process, the conversion process
, is the most diverse of the business processes. -The diversity of conversion processes makes it difficult to propose a single generalized set of events that make up the process. The business events ____ differ from company to company and across industries. The activities involved in assembling a toy car are vastly different from those involved in making a pizza, defending a client in court or discovering a cure for a crippling disease.
With automated batch processing,
, transaction data may not be entered in the computer system until some time after a business activity occurs, and master files may be updated even later. The only time the master file is accurate and up‐to‐date is immediately after a batch of transaction data has been processed.
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
DBMS SOFTWARE - The Relational Logical Structure Option‐ Schema on Write
- differs in the features offered to users and developers
There are two standards for EDI
-ANSI X12 was developed by the American National Standards Institute and is used for North America transaction sets. - EDIFACT4 was developed by the United Nations Economic commission. Outside of North America, it is the most widely used standard.
. What are the technology trends/issues transforming the way we conduct business (and as a result, the way we manage and leverage information)? By the way, come back in few years and these will likely be different
-Analytics and Big Data -Migration to Cloud Computing -The Internet of Things -AI/Machine Learning/Rise of Business Bots/Augmented Reality and Virtual Reality -RPA -Blockchain -Cryptocurrencies -Social Media/BYOD/BYOD MESSAGING (e.g., Twitter, Slack, Facebook Messenger, GroupMe, Whatsapp) -Compliance with Information Management Regulations and Standards (e.g. PCI-DSS, EMV technology, GDPR) -Cyber Security
What are some typical substeps? (i.e., How they Make new "things" to sell using "things" they buy, or customize "things" they buy to get them ready for resale) This is tough because this is the most diverse of the three processes we will discuss.
-Assembly. -Growing. -Excavating. -Harvesting. -Steps that define a manufacturing process. -Steps that result in a deliverable service (e.g., mowing a lawn, raking leaves, trimming trees). -Steps involved in preparing a meal to sell in a restaurant. Transforming knowledge into valued intellectual property. Steps in educating or training. -Discovering (e.g., research and development activities).
DBMS FEATURES
-Data Query Commands -Forms Generators -Report Generators
A note about buying "things" Organizations can acquire a wide variety of goods and services categories including, but not limited to:
-Human resources / labor (e.g., people's time and skills). -Financial resources or financial instruments. Supplies. -Inventories (e.g., raw materials or finished goods). -Property, plant, and equipment. -Fixed Assets -New ideas (e.g., research and development). -Miscellaneous services (e.g., legal services, utilities such as power, telephone, internet, or custodial protection services, research and development, medical services, financial expertise, insurance, repair services, etc.)
Buy "things" - Procure to Pay Business Process
-Identify need and possibly internally request the purchase of goods or services by an authorized purchasing agent. -Negotiate a purchase order with a vendor. -Receive and inspect goods or services from a vendor. -Store and/or maintain goods. -Pay a vendor for goods or services. -Return goods to a vendor.
Organizations have cited other EDI benefits including:
-Improved customer relations and service. -Improved production scheduling. -Better cash management. -Improved inventory management. -Lower inventory costs. -Improved data access. -Reduced order lead times. -Reduced occurrences of out of stock items. -Improved information on price changes and product availability. -Reduced labor costs. -Better accuracy in ordering, shipping, and receiving.
Examples of SDA technologies include
-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
Sell "things" - Order to Cash Business Process
-Market goods or services to potential or returning customers (and/or perform support activities that help customers choose the organization as a vendor). -Negotiate (with a potential or returning customer) the terms of a sales order for goods or services. -Select and inspect goods or services to be delivered to the customer. -Prepare goods or services for delivery to the customer. -Deliver goods or services to the customer. -Receive payment for goods or services from the customer. -Negotiate the terms of, and possibly accept, a customer return of goods.
. How do I access the data that organizations collect and learn to produce useful information that answers questions and helps solve business problems?
-The need for an analytical mindset -Query logic (SQL to guide design, test drive via Access QBE) -Visualization Tool use
AN ANALYSIS APPROACH - ASKING THE SIMPLE QUESTIONS CAN PAY OFF
-What happened and why? -What roles are performed and who/what agents perform the roles in executing each event? -What kinds of resources were involved and how much was used? -When did each event occur? -Where did the event occur? - What can go wrong when executing the event?
Think of business processes as a cookbook for running a business and reaching business goals defined in an organization's business strategy. Business process groupings used by disciplines vary and examples can include:
-acquiring and paying for resources (e.g. financing, human skills, materials and supplies, and plant and equipment), -converting resources acquired into goods and services for customers, -delivering goods and services to customers and collecting payment.
Batch processing
-can occur manual or automated systems -accumulates transaction data for a period of time to collect a group (called a batch) of transaction data before proceeding with processing.
two design steps
-logic -query design mechanics
Some industries group key management activities and refer to them as business processes, such as
-managing the supply chain, -managing the customer relationship, -managing the product lifecycle management, -managing human resources, -managing financial resources.
When an organization performs business activities, it triggers the need for information processing. So you need to know the basics of information processing
-procure to pay (buyer side) -order to cash(seller side)
To analyze an organization, you need some way to handle the complexity by organizing your analysis into manageable units. We can do this drilling down to talk about
-the series of interrelated business processes that make up the value chain, then -the business events that make up each of these processes. -Analyze: Who? What? When? Where? Why? How?
Let develop two skills sets that help differentiate you in the marketplace:
1- How to query schema on write databases to answer business questions, and 2- How to create visualizations when working with large data sets.
What Are Some Key Information Processing Steps?
1. (Capturing) and Recording Source Data 2. Maintaining Reference Data 3. Generating Outputs/ Reports
Relational DBMS- each table contains
1. A data item (or items) that uniquely identify each table record. 2. Data items about a particular group of people, resources, locations, or business activity type. 3. Data items (usually key attributes) that are used to combine or link tables.
Database design involves three key steps
1. Mapping Reality (aka Understanding of Business Process Needs) into a Conceptual Model 2. Mapping the Conceptual Model into a Logical Model, and 3. Creating a Physical Database using DBMS software.
Data used by program instructions are often accessed in either of two ways:
A user can supply input data directly (via typing, voice, scanning, screen touch, etc.) 2. A computer program can access data stored on a storage medium (e.g., a cloud server, a hard drive, network file server, USB flash drive, or DVD
object oriented
Although we will use relational databases in this course, more and more software is incorporating a technologically sophisticated model called
Extract‐Transform‐ Load (ETL)
Building a data warehouse is actually a multi‐step process referred to as
Recording data
Collecting data that describes business events, the agents who execute those events, the locations where the events take place, and the resources affected by the events. In other words, recording data involves collecting and storing data that describes the who, what, when, where, why, and how much about business activities. The volume and nature of data recorded continues to expand. Additionally, more and more data recording is performed using automation at the time and point of the business activity.
OTC ‐‐Data analysis associated with Sales Order Negotiations (sometimes referred to as Sales order entry)
Data collected about sales order negotiations triggers the next step in this process - the order fulfillment. You can also use sales order negotiation data to learn more about your customers, your products, and your employees. Sales order data can be used to generate different analyses such as forecasting sales, forecasting salesperson commissions, and measuring sales order clerk and salesperson productivity.
EDI (Electronic Data Interchange).
Data does not just flow within an organization, but needs to flow between organizations. To enhance the efficiency of the purchaser/supplier relationship, many companies require or encourage the use of
data dictionary
Database documentation at the data item (field) level is -include information about the database and data items in the database (e.g., how it is structured, names for items, information about table structures, etc.).
fields
Each record is made up of a collection of columns referred to as
The following illustrates some business events for a sample order to cash business process. The exhibit shows one business process (Order to Cash) and sample business events that may comprise the process (marketing to customers, negotiating an order, delivering the goods, and collecting the customer payment).
Event 1. market/support customers Event 2. negotiate order with customers Event 3. deliver ordered goods Event 4. receive customer payment
THE NATURE OF BASIC INFORMATION PROCESSING-
Files and the timing of processing - organizations refer to groups of data as files and they are often files classified by their type or content. Traditionally, types of data files used in business transaction processing systems include master files, transaction files, history files, reference files, and suspense files.
transaction files
Files that contain business activity data (i.e., transaction data used to update balances in master files) are referred to as -Examples include order files, billing files, shipping files, and cash receipt files. In automated systems, these transaction files are electronic. In manual systems, batches of paper source documents that document transaction data are examples of transaction files
OTC ‐‐Data analysis associated with Marketing Campaigns and Generating Customer Quotes
For example, information about customer preferences and the effectiveness of marketing campaigns can be collected during normal sales calls. Data collected could be used to provide details for analysis of expense logs and salesperson productivity. Data about marketing campaigns and other marketing efforts could be used to link marketing expenses to the revenue generated by the marketing events, allowing an organization to measure the return on funds invested in marketing efforts.
BUSINESS PROCESSES ARE NOT DISCRETE, BUT ARE INTERRELATED
For example, the ability to deliver a product to a customer is dependent on the processes of acquiring and converting the resources used to create that product. It is important to remember that business processes are interrelated, but we differentiate them in an attempt to simplify our analysis.
Reporting
Generating relevant, timely and accurate outputs for information customers both inside the organization (e.g., management and other decision makers) and outside the organization (e.g., regulatory agencies, stockholders, creditors, etc.). Reporting, arguably the most demanding of these information processes, provides information and measurements to support organization critical activities such as planning, controlling, and evaluating.
Regardless of the type of good or service being acquired, the following are typical business events in the procure to pay business process:
Identify need and possibly internally request the purchase of goods or services by an authorized purchasing agent. Negotiate a purchase order with a vendor. Receive and inspect goods or services from a vendor. Store and/or maintain goods. Pay a vendor for goods or services. Return goods to a vendor.
Maintaining reference data
Keeping current and accurate reference data about resources owned or used, external parties with whom an organization does business, employees who work for an organization, and locations where activities take place. In business, things are ever changing. Customers move, vendors change their product lines and prices, etc. That requires an organization to monitor stored data and keep it current.
Conversion Business Process
Make new "things" - to sell using "things" they buy, or customize "things" they buy to get them ready for resale
NoSQL
Not only SQL or Not ordinary SQL - databases
Using object technology to supplement relational database models is referred to as
ORDMS (object relational database management systems).
(called the primary key field)
One of the table fields is a unique identifier that enables users to differentiate between the various records
DBMS (AND FILE) DOCUMENTATION- Record layout
One way of documenting the contents of a database is to look at the table structure,
THE NATURE OF BASIC INFORMATION PROCESSING ‐ How is data captured and how does it flow from place to place?
Organizations get data from a large variety of sources these days
What type of data do organizations collect to generate information and how is that data stored?
Our study begins with a focus on BUSINESS FIRST, TECHNOLOGY SECOND.
QBE (query by example) and SQL (structured query language)
Popular data query tools include
OTC ‐‐Data analysis associated with Order Fulfillment ‐ Warehousing/Shipping
Queries or requested reports about fulfillment can include inventory status reports; back orders requested by warehousing; analysis of inventory loss, damage, or shrinkage during a period of time; production process inputs; data to plan inventory purchases; warehousing and inventory holding costs; warehousing efficiency measures (such as the time between the order and the goods delivered to shipping for delivery); and analysis of warehousing personnel efficiency.
OTC ‐‐Data analysis associated with Receiving Payments from Customers.
Regardless of the methods used to receive and process remittances, data about cash receipts can be used to generate a variety of analyses and reports. Examples include unapplied cash report, accounts receivable aging schedule, sales discounts not taken, customer payment performance measures, timing of payments, and mode of payment (e.g., cash, credit card, or electronic funds transfer).
big data
Several years ago, technologies began enabling high volumes of semi structured and unstructured data to arrive at a high velocity (speed). This data is often referred to as
OTC ‐‐Data analysis associated with Shipping and Billing.
Shipping/billing data can be used to generate a variety of analyses and reports. Example analyses available after billing include cash flow projections, a listing of the items invoiced, invoice batch totals, a listing of open invoices, an accounts receivable aging report (i.e. calculating the length of time that has passed on unpaid bills) or a listing of the activity in accounts receivables. At this point, the total in the general ledger accounts receivable account, the accounts receivable breakdown by customer (often referred to as a subsidiary accounts receivable account) and the open sales invoice balances should equal.
Entity Relationship diagrams (ERDs) or Object Oriented diagrams
The resulting conceptual model graphics are called
records
The table rows are referred to as ____ and each table can contain many records
batch processing or real time processing (using online devices).
The timing of processing reveals when data is recorded in the system and when transaction data is posted to update master file balances. Thus the processing method affects the timeliness and accuracy of data stored in files. Processing is often identified by type:
EDI (Electronic Data Interchange)
To enhance the efficiency of the purchaser/supplier relationship, many companies require or encourage the use of
buyers (purchasing personnel)
To make wise purchase decisions, _____________ must know which items need to be purchased and information about vendors and their products and services. Selecting the vendor is perhaps the most critical aspect of the buying decision. To make a good decision, the buyer needs information on the quality of vendor services, price, and data about vendor performance.
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
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.
The HOW question. Let's drill down further, still focusing on what the organizations are doing, but touching on how organizations do what they do, by looking at the series of sub-steps ("events") they perform.
What are some typical business events that make up these business processes?
Buy "things" Events the Procure to Pay business process (but is sometimes called Procurement or Expenditure process).
What are some typical substeps (i.e., How they Buy "things")? -Identify need and possibly internally request the purchase of goods or services by an authorized purchasing agent. -Negotiate a purchase order with a vendor. -Receive and inspect goods or services from a vendor. -Store and/or maintain goods. -Pay a vendor for goods or services. -Return goods to a vendor.
Sell "things" Events the Order to Cash business process (but it is sometimes called the Order to Payment, Revenue, or Sales process).
What are some typical substeps (i.e., How they Sell "things") ? -Market goods or services to potential or returning customers (and/or perform support activities that help customers choose the organization as a vendor). -Negotiate (with a potential or returning customer) the terms of a sales order for goods or services. -Select and inspect goods or services to be delivered to the customer. -Prepare goods or services for delivery to the customer. -Deliver goods or services to the customer. -Receive payment for goods or services from the customer. -Negotiate the terms of, and possibly accept, a customer return of goods.
The WHAT question. WHAT business processes do most businesses typically perform?
What is a business process? What are some typical business processes and how are they related?
OTC ‐‐Data analysis associated with Sales Order Negotiations (sometimes referred to as Sales Order Entry)- What other insights can this data provide? You can learn
Which categories of customers order which products or services, What time of day or which day of the week your customers typically order goods or services What types of customers typically place orders then cancel them later, Feedback about the customer ordering experience, Whether customers tend to order online or via phone, How many sales order each employee executes and how long each sales order clerk spends with each customer, etc.
QBE
a grid or replica of an empty record is displayed and the user types search criteria in the applicable columns. Then the computer takes the information entered in the QBE grid and generates SQL commands
EDI (Electronic Data Interchange)
a method for exchanging business data between or within organizations in a structured, machine retrievable format. The communications are based on using a standard format understood by both the sending computer and the receiving computer.
the heart of any conversion process is
a sequence of activities that takes goods and services acquired via the procure to pay process and transforms them into goods and services suitable for the end customers (and sold via the order to cash process).
Buy "things"
acquire and pay for resources (e.g. financing, human skills, materials and supplies, and plant and equipment),
Well‐designed databases start with
an understanding of an organization and its business needs and processes.
Porter's Value Chain
analysis recognizes that organizations are elements of a value system
Relational databases,
are based on the theory of relational math (set theory) and they use a method of data recording and access called the schema‐on‐write approach
Online devices
are devices connected to a CPU (a computer's processor or 'brain') -facilitate real time processing
Source document data flows
are used to capture detail data about business events then move data from one point to another. Source documents often serve as supporting documentation for any subsequent information processing. Examples of source documents include sales orders, purchase orders, customer order sales invoices, vendor invoices, deposit slips, shipping documents, receiving reports, remittance advices, purchase requisitions, checks, bills of lading, etc
History files (which may be called archive files),
as the name suggests, 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
, objects combine
both data and methods (i.e. instructions for processing the data) into a single processing element.
NoSQL database systems are
broad class of database management systems that differ from relational database management
Source Data Automation (SDA
collecting data at its point of origin in digital form.
Master files
contain balance data or the status of an entity at a point in time. Examples include inventory files, customer files, employee files, vendor files, or cash account files.
Transaction files
contain business activity data (i.e., transaction data used to update balances in master files). Examples include order files, billing files, shipping files, and cash receipt files.
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.
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 files
contain referential data such as a tax rate schedule, a volume pricing list, or a listing of the general ledger chart of accounts.
Reference files
contain referential data such as a tax rate schedule, a volume pricing list, or a listing of the general ledger chart of accounts
the Product‐Sale table
contains data items that enable linking back to the Sale and Product tables (Invoice Number and Item Number respectively), and also includes fields that describe the items sold in each sale (e.g., Quantity, and Negotiated Price Each fields).
The Sale table
contains only data items about a sale (e.g., Invoice Number, and Date fields) plus it also includes the Customer field as a foreign key field to enable table linking between the Customer and Sale tables
Computer processing involves two components:
data and instructions (application software also called computer programs)
Extract refers to
data capture
big data typically includes
data not previously captured, stored, or analyzed. -It describes the dynamic, large and disparate volumes of data being created by people, tools and machines (such as information garnered from social media, data from internet‐enabled devices (including smartphones and tablets), machine data, video and voice recordings, and the continued preservation and logging of structured and unstructured data).
Using object technology
database programmers can model and implement complex data types such as voice, video, audio, etc
Sell "things
deliver goods and/or services to customers and collect payment.
business events are
economic in nature, meaning that they increase or decrease the quantity of a firm's economic (financial) resources
Suspense files
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.
Marketing
has a significant impact on the order to cash process. _____ personnel are among the most dominant users of order to cash process information - has primary responsibility for establishing pricing policy, product offerings, price discounts, and possibly, credit terms. Most decisions concerning product design, styling, packaging, and product deletion fall under the jurisdiction of marketing.
Treasury and finance
have the responsibility to see that money is available to pay for items purchased, ensure that debts are paid, and correctly account for purchased items in financial reporting. Budgeting is used to plan cash flows for goods and services required in normal business operations and capital budgeting is used to help identify the amount and timing of cash needed to acquire long term assets.
Once the designer creates a conceptual model of the databases
he or she creates a blueprint that will be used to build an actual physical database. This blueprint is referred to as a logical model and the database specifications will depend on the type of database the design wants to build. In this course, we will work with one of the more popular types of databases today, relational databases.
Procure to Pay business process
includes the activities involved in acquiring, paying for, and maintaining the goods and services needed by an organization. - provide needed resources for the organization's order to cash and conversion processes at an optimal time (at the point when they are needed in an effort to reduce process cycle times, waste and costs)
decision/management events
including decision about managing resources (e.g., human, financial, and physical), corporate governance, strategic management, managing relationships with trading partners, developing a five year business plan, deciding to design a new product or service, making a decision to close a branch office or open a new store, and making a decision to change vendors.
rather than trying to analyze and model everything, we will concentrate on three basic,
interdependent business processes common to all organizations.
The Product table contains data about
inventory items maintained by the company (e.g., Item Number, Description, and MSRP - manufacturer suggested retail price‐ fields). The Item # primary key differentiates between the various products.
conversion business process
involves the activities necessary to convert raw inputs into finished products or services. The inputs to the process include materials, equipment, services, labor, intellectual property, etc. that are procured via the procure to pay process. Finished products and services are the outputs of the conversion process, and they are sold to customers through the order to cash process • Meeting conversion goals such as deadlines, output levels, capacity use, etc.
A data warehouse
is a central repository of information created with data from transactional systems, relational databases, and other sources. Business analysts, data scientists, and decision makers access the data through business intelligence (BI) tools, SQL clients, reports, dashboards and other analytics applications and tools.
business process
is a collection of related activities that combine different resources and inputs to create an output that is of value to the organization, its stake holders and/or its customers. These outputs may be products or services.
A schema
is a fancy word for a model of the data.
The Sales Invoice number
is a most logical primary key because it enables the organization to differentiate between invoices
SQL
is actually a standardized query language that can be used to accomplish many tasks (not just ask questions).
SQL
is an actual programming language that you can use to directly interact with data, or you can embed SQL commands in programs that access and process data
database
is based on the idea that the same pool of data can be shared between various application programs to create various views of the same data. Database designers and programmers develop an integrated data store to hold recorded data, then use application code to select applicable data items from the common data file to create a variety of logical outputs (views)
batch
is not bad and real time good. _____ data processing is an efficient way of processing high volumes of data is where a group of transactions is collected over a period of time.
A current popular trend in database development
is the attempt to overcome the limitations of relational database methods by combining the relational approach with object technology concepts
DBMS
is the resource available to build and manage data structures in an information system. - software was originally developed to simplify and enhance the value of an organization's data by reducing data and programming redundancy, inconsistent data formats, and data inconsistencies.
SQL3
is the structured query language standard for object relational databases
One of the critical differences between DBMS software packages
is their ability to create and manage logical data structures (i.e. the way the data are conceptually organized rather than how data are actually physically stored).
Relational DBMS
is used to create and store a database comprised of many tables, each organized as a collection of columns and rows
(OO) structures object oriented
it is much more computationally demanding (meaning it requires heavy use of computing resources) than even the pure relational structure
Additional DBMS features
many packages include a user interface designed to aid programmers and users with the processes of data definition, data queries, and application design. They can also house containers for storing computer code used to automate processes.
Receiving clerks
need information about goods and services on order. Enough information needs to be provided so that the receiving clerks do not accept items that were not ordered by the company. Providing too much information, however, has been known to encourage these people to steal excess items shipped by a supplier. Organizations should use care to provide adequate information and identify individual responsibility, as this is a point in the process where significant losses traditionally occur.
4 Outsourcing
occurs when one organization finds another organization (or individuals) to perform some work. This practice is useful when the outsourcing organization cannot complete the work (e.g. they do not have the capacity or the expertise) or when they identify another organization that can complete the work in a more cost‐ effective manner.
Then the batch of transactions is
posted to the master file in one processing run
Processing involves
posting the totals from the newly posted transactions to the current master file data to create a new, updated master file. The pre‐update master file, the batch of transactions, and the post update master file are all stored for when backups are needed.
Logistics
professionals need analyses to help them streamline logistics operations. For example, they may want transportation data such as product volumes, lanes, cost per shipment and much more. They are interested in consolidated shipping and expense data possibly broken down by domestic and international, inbound and outbound, by mode of transportation or route, or spend by product category, by location and/or mode.
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.
Data Dictionaries
provide a catalog of data item information such as name, description, data type, date created, name of person who created it, data representation, data range, list of applications it is used in, the person(s) authorized to view or change the data item, etc.
key business events include
recording data, maintaining reference data, reporting
Human Resource
requires information about the order to cash process to effectively deploy an organization's work force. The human resource function requires labor information so that they can determine work force needs, oversee employee scheduling, initiate employee compensation, and conduct employee performance evaluations.
Production
responsibility to maintain adequate stores of inventory on hand and produce a quality product in a timely manner. Projected and actual sales information is used extensively by production personnel to plan and adjust their schedules. Feedback from customers about product quality, defects, and warranty problems is valuable in adjusting production processes and product quality
ERP (enterprise resource planning) software, CRM (customer relationship management) software, and SCM (supply chain management) software
s are internally focused and support operational transaction processing activities. - application modules include order to cash transaction processing, procure to pay transaction processing, human resources support, payroll processing, financial applications, cash management, general ledger, and inventory / asset management.
Schema on Read
simply records the data 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 might not be determined until accessing data, based what is most relevant to the task at hand.
For Profit organizations
strive to generate a profit (revenue in excess of expenses). These profits can be reinvested into the company or distributed to owners and/or shareholders. For Profit organizations take several legal forms such as unincorporated (sole proprietorships or partnerships), corporations (C Corporations and S Corporations) and limited liability companies (LLC).
. Not for Profit organizations
strive to provide particular services to constituents (e.g., governments, educational, or charitable organizations.) In contrast to For Profit organizations, Not for Profit 'owners,' shareholders or trustees are not intended to benefit financially from the organization.
Real‐time denotes
that rather than waiting a predetermined amount of time as you would with batch processing, transaction data are entered and processed to update the relevant master files as the business event occurs
The power of relational DBMS software is
the ability to maintain several tables of related information that can be accessed by several different information customers in many different ways. As well, the tables could be used to summarize what each customer purchased during a month, to calculate the average time between a sale and a related cash receipt, and to calculate total sales for the income statement.
A DBMS environment allows
the development and maintenance of a data repository that is independent from application programs. Such flexibility enables database users to create and maintain IT applications that capture, maintain, and report data while shielding application developers from the physical structure and maintenance of the data repository
user interfaces (UI)
the input/output/help screens or menus through which a user communicates with a program.
In the order to cash process,
the organization plays the role of the seller - viewed as the heart of a company's value chain and is typically the only time the customer directly interacts with the organization
A record layout details
the record structure, including record field names, the width of each field, and the type of data stored in each field (e.g., numeric, alphanumeric, date).
The major advantage of object oriented (OO) structures is
their increased flexibility to represent very complex data structures that capture the essence of complex objects that exist in reality. You may not know it, but you already use object technology on a daily basis. For example, Excel is actually an application that is written to be library of objects (a worksheet object, a chart object, a pivot table object, a range object, etc.)
Make new "things" Events- to sell using "things" they buy, or customize "things"
they buy to get them ready for resale -will refer to this overall process as the Conversion business process (also called 'Production' process).
When organizations use schema‐ on‐write,
this means that they define a schema (model) of the data up front (during the logical design phase), to plan the structure that will be built to house the data.
schema on read.
to define how data is captured, stored and queried, NoSQL databases use
You can use SQL commands
to do everything from creating databases, creating schemas, creating table structures, updating table structures, deleting tables, joining tables, adding or updating data, adding database users, setting database controls, and requesting information from a database.
DBMS FEATURES
to help you store, maintain, and report information
a central objective in analyzing and designing the database is
to identify the set of data needed to document business activities (e.g., financing, selling, purchasing, or marketing) and provide information about parties (e.g., customers, vendors, or employees) and resources (e.g., cash, services, or inventory) involved in the business activities
Make new "things"
to sell using "things" they buy, or customize "things" they buy to get them ready for resale - convert resources acquired into goods and services for customers, and
The key to designing queries that generate outputs is
understanding the proper business logic, then translating that business logic into the DBMS using the proper physical design steps.
accounting and finance
use procure to pay process information to prepare various management reports, financial analyses and financial statements. Procure to pay data such as inventory purchases; purchase returns, allowances, and discounts; property, plant, and equipment; selling expenses; administrative expenses; procure to pay cash flows and accounts payable balances should be accurate, complete and timely
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.
Report Generators
which are used to define the format and look of desired output
Forms Generators
which are used to design the format and look of desired screens and forms;
Data Query Commands
which help users ask questions about the data;
Logistics professionals
who assist with supply chain management use a large amount of data from the procure to pay process. They need analysis to support key tasks such as demand planning (identifying when it is time to procure goods or services), inbound logistics (receiving, storing, and disseminating incoming goods) and managing supplier relationships. Treasury and finance have the responsibility to see that money is available to pay for
P2P ‐ Data analysis associated with Identifying Need and Requesting the Purchase of Goods or Services. Information users want the ability to produce various outputs from the information captured during purchase requests including:
• A list of requests made during the period. • A list of requests outstanding (those not yet acted on by the buyer). • An analysis of the timeliness of identifying need and generating requests. • A stock status report of goods or services (as support for monitoring need), including information about items out of stock, items below a minimum stock level, and item turnover analysis.
Procure to Pay business process
• Acquire only those goods or services that an organization needs and can afford. • Purchase items from reliable vendors. • Purchase high quality items, or at least items of the desired quality. • Obtain the desired items at the best possible price. • Purchase only those items that are properly authorized and are for legitimate company purposes. • Have resources available and in useful condition when they are needed by the company. • Receive only those items ordered, and receive all the items ordered. • Control and safeguard items received so they are not lost, stolen, or damaged. • Pay only for items actually received and pay in a timely manner to the appropriate party.
Information relevant to marketing includes:
• Amount and location of inventory in stock. • Customer preferences and changes in those preferences. • Cost to produce each product and to provide each service. • Activity and success of each salesperson. • Quantity and price of each product sold by sales person, by customer type, and by geographic area. • Detailed information on competitor's products, services, pricing strategy, and advertising campaigns. • Comparison of marketing efforts and return on marketing investments compared to competitors and industry benchmarks.
Top managers are primarily interested in summary information for the sales efforts of the entire enterprise:
• Market share of company products compared to the market share of competitors. • Trends in sales this year compared to prior years. • Changes over time in key economic indicators compared to changes in the organization's sales, sales returns and allowances, and uncollectible accounts.
Conversion Business Process
• Using efficient conversion methods that optimize capacity. • Enhancing safety during conversion. • Generating products or services in a timely manner. • Reducing the number of errors, repairs or rejects during conversion. • Using less organizational resources (such as inventory or space), resulting in lower total operating costs. • Minimizing the time necessary to convert raw materials into finished products or services.
