ISM QUIZ 2 CHATPER 3 DATABASES AND DATA WAREHOUSES 1 Data is everywhere Stored in databases What are the risks Risk of theft Hackers can get information and sell it to someone else Wardriving driving around looking for unsecured wireless networks being used by retailers sniffer programs captured credit and debit card numbers as they moved through a retailer s processing computers then tried to sell the data overseas 2 A database is a logical structure structured determined by the information stored in the database Database a collection of information organized in a logical structure 3 Most popular type of database Product information stored by item number Customer information stored by customer ID Sales information stored by sales ID Relational database most popular consists of 2 or more tables tables have a relationship with one another 4 Understand the parts of a relational database Tables customer product type employee order driver Rows and records Columns and fields Primary keys uniquely describes a row in a table Foreign keys primary key in another table that links two tables exists as a primary key in a different table Data type number character date time currency something you can do a mathematical calculation on a number 5 What is the purpose of the data dictionary Data dictionary defines the structure of a relational database structure of the tables 6 What are the parts of a DBMS Database Management system 1 DBMS engine accepts logical requests from the various other DBMS subsystems converts them into their physical equivalent and actually accesses the database and data dictionary as they exist on a storage device 2 Data definition subsystem helps you create and maintain the data dictionary and define the structure of the files in a database 3 Data manipulation subsystem helps you add change and delete information in a database and query it for valuable information allows you to specify your information requirements logical detailed interaction 4 Application generation subsystem contains facilities to help you develop transaction intensive applications These applications usually require that you perform a series of tasks to process a transaction menus reports user 5 Data administration subsystem helps you manage the overall database environment by providing facilities for backup and recovery security management query optimization concurrency control and change management 7 How is a data warehouse different from a database database a collection of information that you organize and access according to the logical structure of that information used for transactional processes data warehouse a logical collection of information gathered from many different operational databases used to create business intelligence that supports business analysis activities and decision making tasks pulls together data from multiple database internal and external multi dimensional rows columns layers want to understand patters and trends 8 What are data warehouses used for 9 What is data mining They exist to support decision making tasks in your organization support only online analytical processing data mining the process of querying a data warehouse to extract meaningful information to get information to use in business decision making to get insights from data that are not obvious business analytics otherwise Data warehouse data warehouse engine software that runs it query and reporting tools intelligent agents picks out info that people wouldn t normally pick out multi dimensional analysis tools techniques that allow you to view multi dimensional information from different perspectives statistical tools help you apply various mathematical models to the information stored in a data warehouse to discover new information 10 Why do organizations keep data Business intelligence The more data the more time the richer the information It s the collective information about customers competitors business partners competitive environment your internal operations It is used to make strategic business decisions 11 Why is the quality of data in a database important 1 Who owns the information sometimes people must pay for the information 2 Who is responsible for its quality and accuracy garbage in garbage out some organizations have a person who is responsible for the data don t duplicate same piece of stored in different places can give it a different meaning information 3 Who is responsible for keeping it secure biggest threat is an internal threat people inside the company messing up the data employees have access to a lot of data someone who gets fired or not logging out of the computer Extra Vocabulary Business Intelligence collective information about your customers your competitors your business partners your competitive environment and your own internal operations that gives you the ability to make effective important and often strategic business decisions Online Transaction Processing OLTP gathering of input information processing that information and updating existing information to reflect the gathered and processed information Online Analytical Processing OLAP the manipulation of information to support decision making Integrity constraints rules that help ensure that quality of the information Physical view deals with how information is physically arranged stored and accessed on some type of storage device Logical view focuses on how you as a knowledge worker need to arrange and access information to meet your particular business needs View allows you to see the contents of a database file make whatever changes you want perform simple sorting and query to find the location of specific information Report generators help you quickly define formats of reports and what information you want to see in a report once you define a report you can view it on a screen or print it Query by example QBE helps you graphically design the answer to a question You identify the files in which the needed information is located drag any necessary fields from the identified files to the QBE grid and specify selection criteria Structured query language SQL standardized fourth generation query language found in most DBMSs how the database manages its information Concurrency control how the database manages changes to a record if multiple users attempt to make changes at the same time 2 people 2 different places updating same information who s characteristics wins if one person is accessing record only one person on