- Databases and Data Warehouses How Do You Organize Large Amounts of Information? Chapter 10.
Databases and Data Warehouses How Do You Organize Large Amounts of Information? Chapter 10.
Slide 1 Databases and Data Warehouses How Do You Organize Large Amounts of Information? Chapter 10 Slide 2 2003 The McGraw-Hill Companies Student Learning Outcomes 1.Describe the difference between data and information and the logical structure people use to organize data and information 2.Define key relational database concepts including field properties, primary keys, compound primary keys, foreign keys, and integrity constraints 3.Describe the role of an entity-relationship diagram in defining the structure of a database and the relationships among the tables Slide 3 2003 The McGraw-Hill Companies Student Learning Outcomes 4.List the major components of a database management system and describe their roles 5.Discuss how Web databases support various e-commerce functions 6.Describe how data warehouses and data- mining tools help create business intelligence Slide 4 2003 The McGraw-Hill Companies Introduction Businesses and individuals alike need technology tools to help them effectively organize their information so they can access and use it for a variety of purposes. Slide 5 2003 The McGraw-Hill Companies 10.1 Data, Information, and Their Structures Data are distinct items providing descriptions of people, places, and/or things that may not have much meaning to you in a given context Information is organized data whose meaning is clear and useful to you in a given context Slide 6 2003 The McGraw-Hill Companies Logical Structure of Data and Information Field Record Data File Database Data Warehouse SimNet Concepts Support CD: Database Applications, Managing Data, and Database Management Systems Slide 7 2003 The McGraw-Hill Companies 10.2 Relational Databases Most popular method for organizing and storing information is the relational database model which stores information in files or tables that have rows and columns Popular software applications include: Microsoft Access Oracle Sybase DB2 FileMaker Slide 8 2003 The McGraw-Hill Companies Commercial Relational Database Models Slide 9 2003 The McGraw-Hill Companies Database Example p.10.294 Fig. 10.3 Slide 10 2003 The McGraw-Hill Companies Relational Database Concepts Field Properties Field Properties Foreign Keys Foreign Keys Primary Keys Primary Keys Integrity Constraints Integrity Constraints SimNet Concepts Support CD: Designing Relational Databases Slide 11 2003 The McGraw-Hill Companies Entity-Relationship Diagrams Entity relationship diagram is a graphical representation of tables in a database and the relationships among the tables p.10.296 Fig. 10.4 Slide 12 2003 The McGraw-Hill Companies Working with a Relational Database Relational database relies on associations or relationships between tables Three types of relationships One-to-one One-to-many Many-to-many Slide 13 2003 The McGraw-Hill Companies Types of Database Relationships One-to-one relationship means that one record in a database table can only be related to at most one record in another database table One-to-many relationship means that one record in a database table can be related to many records in another database table Many-to-many relationship means that many records in a database table can be related to many records in another database table Slide 14 2003 The McGraw-Hill Companies Database Models Hierarchical Network Relational Object-oriented SimNet Concepts Support CD: Types of Database Organizations Slide 15 2003 The McGraw-Hill Companies Hierarchical Databases Uses an inverted directory tree structure Organizes data under different directories Parent directories are the main directories - similar to a folder Children subdirectories can have only one parent Records belonging to a directory are children Slide 16 2003 The McGraw-Hill Companies Network Databases Similar to hierarchical databases Uses a tree structure Children can have more than one parent Slide 17 2003 The McGraw-Hill Companies Object-Oriented Databases Becoming popular Uses objects to represent entities rather than fields in a table Object is one item that contains distinct information Each object has its own properties or attributes Similar objects belong to the same class Slide 18 2003 The McGraw-Hill Companies 10.3 Database Management Systems Database management system (DBMS) Application software Allows you to arrange, modify, and extract information from a database DBMS works on everything from PDAs to large mainframes SimNet Concepts Support CD: Database Management Systems Slide 19 2003 The McGraw-Hill Companies DBMS All DBMS include: Data Manipulation Subsystem Application Generation Subsystem Data Definition Subsystem Data Administration Subsystem Slide 20 2003 The McGraw-Hill Companies Data Definition Subsystem p.10.299 Fig. 10.7 Slide 21 2003 The McGraw-Hill Companies Defining Relationships Among Tables p.10.300 Fig. 10.8 Slide 22 2003 The McGraw-Hill Companies Data Manipulation Subsystem: Database Form Database form is a graphical interface that makes it easy to add, change and delete information p.10.301 Fig. 10.9 Slide 23 2003 The McGraw-Hill Companies Queries Asks questions of a database Query language uses English statements to extract data Query-by-example tool allows you to graphically represent what information youd like to see from a database SQL (Structured Query Language) is a standardized query language for most databases Slide 24 2003 The McGraw-Hill Companies Access Query-By-Example Fields Tables Visible Fields in Query Relationship Tables & Primary Keys p.10.302 Fig. 10.10 Slide 25 2003 The McGraw-Hill Companies Generating a Report Typical report generators will allow you to: Specify what information you want in a report The order in which it will appear Various reporting options such as a title and subtotal p.10.303 Fig. 10.11 Slide 26 2003 The McGraw-Hill Companies OTHER DBMS SUBSYSTEMS Application generation subsystem For developing transaction-intensive software Data administration subsystem Managing the overall database environment Security management Concurrency control Used by database administrators Slide 27 2003 The McGraw-Hill Companies Design Database Administrators How do database administrators come up with solutions to businesses challenges using databases? ImplementMaintain Slide 28 2003 The McGraw-Hill Companies 10.4 Databases In Electronic Commerce Databases play a key role in helping businesses conduct e-commerce by making their business tasks more effective and efficient Businesses are using the Web to provide employees with access to information Businesses are allowing their partner businesses to gain access to vitally important information in support of functions such as supply chain management through the Web Slide 29 2003 The McGraw-Hill Companies Productive Uses of Databases in e-Commerce Electronic Catalogs Web Search Engines Web Databases Personal Portals Slide 30 2003 The McGraw-Hill Companies Web Database Web databases require the use of: An appropriate DBMS An organization and presentation facility such as XML Middleware (software that allows the communications to happen between different software applications) p.10.305 Fig. 10.12 Slide 31 2003 The McGraw-Hill Companies Web Search Engine Web search engines use Web databases to: Store Sort Organize Categorize Web sites, their addresses, and their content p.10.306 Fig. 10.13 Slide 32 2003 The McGraw-Hill Companies Web Catalogs Electronic catalogs are Web databases that contain product information, including: Descriptions Prices and perhaps: Images, sound, video, 3-D graphics, and animation p.10.307 Fig. 10.14 Slide 33 2003 The McGraw-Hill Companies Personal Portal A personal portal is a Web page for which you define the content you want to see. Examples to include: List of Web site links Stock ticker Local weather forecast Favorite news site p.10.307 Fig. 10.15 Slide 34 2003 The McGraw-Hill Companies Security & Privacy Security: Hackers can break into Web databases containing credit card numbers from e- commerce sites, Internet banking sites, and online medical records SimNet Concepts Support CD: Privacy Issues and Security Issues Slide 35 2003 The McGraw-Hill Companies Security & Privacy Privacy Businesses are compiling information about you in their databases. As businesses merge, so do their databases. Information about you can be easily sold from one business to another SimNet Concepts Support CD: Privacy Issues and Security Issues Slide 36 2003 The McGraw-Hill Companies 10.5 Data Warehouses and Business Intelligence DBMS support: Online Transaction Processing (OLTP) - is the processing of information to support some sort of transaction such as the purchasing of a product. Online Analytical Processing (OLAP) - is the manipulation of information to generate business intelligence and support decision- making tasks. Business Intelligence - is knowledge about your customers, competitors,and internal operations that can help you make more informed and effective decisions. Business Intelligence - is knowledge about your customers, competitors,and internal operations that can help you make more informed and effective decisions. Slide 37 2003 The McGraw-Hill Companies Tools used to Work with Multiple Databases Data Warehouse Data-mining tools Slide 38 2003 The McGraw-Hill Companies Data Warehouse Slide 39 2003 The McGraw-Hill Companies A Data Warehouse is Multidimensional p.10.310 Fig. 10.16 Slide 40 2003 The McGraw-Hill Companies Data Warehouse Features Data warehouse is a collection of information from internal and/or external sources organized specifically for generating business intelligence to support decision making Data warehouse is different from a database in that it is multidimensional with layers of columns and rows Data warehouses directly support OLAP and not OLTP Slide 41 2003 The McGraw-Hill Companies Data-Mining Tools Tools you use to perform data mining p.10.310 Fig. 10.17 Slide 42 2003 The McGraw-Hill Companies Data-Mining Tools cont. Query-and-reporting tools are similar to QBE tools, SQL, and report generators in the typical database environment Multidimensional analysis (MDA) tools are slice and dice techniques that allow you to view multidimensional information from different perspectives Statistical tools help you apply various mathematical models to the information stored in a data warehouse to discover new information Slide 43 2003 The McGraw-Hill Companies Data-Mining Tools cont. Data-mining agents help you discover new information, trends, and relationships within a data warehouse without necessarily applying a specific mathematical model Slide 44 2003 The McGraw-Hill Companies Data Marts Miniature data warehouse Has a special focus Subset of a data warehouse Aids decision making in a specific focus area Slide 45 2003 The McGraw-Hill Companies Slide 46 10.6 Key Terms Application generation subsystem Business intelligence Data Data administration subsystem Data definition subsystem Data dictionary Data file Data manipulation subsystem Data mart Data mining Data-mining agent Data-mining tool Data warehouse Database Slide 47 2003 The McGraw-Hill Companies 10.6 Key Terms Database form Database management system (DBMS) Entity-relationship diagram (E-R) Field Field property Foreign key Hypertext database Information Integrity constraint Many-to-many relationship Middleware Multidimensional analysis tool (MDA) One-to-many relationship Slide 48 2003 The McGraw-Hill Companies 10.6 Key Terms One-to-one relationship Personal portal Primary key Query-and-reporting tool Query-by-example tool Record Relational database model Report generator Statistical tool Structured Query Language (SQL) Web database Slide 49 2003 The McGraw-Hill Companies Review of Concepts 1.Designing a Database How do instructors relate to courses? 2.Defining the Timeliness of Data Warehouse Information How often do you need the right information to predict the scores of football games? Slide 50 2003 The McGraw-Hill Companies Hands On Projects E-Commerce 1.Obtaining a Free Credit Report Maybe the first time, but not always 2.Getting Tutored on the Web 3.Using Interlibrary Loan No need to ever visit the library again Slide 51 2003 The McGraw-Hill Companies Hands On Projects Ethics, Security & Privacy 1.How Secure Is Your Personal Information Is a business responsible for information about you that it loses to a hacker? 2.CRUD Defining Who Can Do What with Database Information Who can CRUD your information? Slide 52 2003 The McGraw-Hill Companies Hands On Projects on the Web 1.Using Webopedia to Learn More about Technology 2.Researching Data Warehouses and Data-Mining Tools 3.Finding a DBMS for Your PDA 4.Researching Database Security 5.Finding a Free DBMS Slide 53 2003 The McGraw-Hill Companies Hands On Projects Group Activities 1.Evaluating Popular Personal DBMSs 2.Digging for Databases What databases are at your school? 3.Defining the Structure of a Data Warehouse