Chapter 5 Lecture 2 Objectives Understand

Chapter 5 Lecture 2

Objectives • Understand Data definition language (DDL) and data dictionary • Learn about popular DBMSs • Understand types of Data Warehouses • Conceptually understand ODBC Principles of Information Systems 2

Creating and Modifying the Database • Data definition language (DDL) • A collection of instructions and commands used to define and describe data and data relationships in a specific database • Allows the database’s creator to describe the data and the data relationships that are to be contained in the schema and subschemas • Data dictionary: a detailed description of all the data used in the database Principles of Information Systems 3

Figure 5. 11: Using a Data Definition Language to Define a Schema Principles of Information Systems 4

Figure 5. 12: A Typical Data Dictionary Entry Principles of Information Systems 5

Storing and Retrieving Data • When an application program request data from DBMS, the application program follows a logical access path • When the DBMS goes to a storage device to retrieve the requested data, it follows a path to the physical location (physical access path) where the data is stored Principles of Information Systems 6

Figure 5. 13: Logical and Physical Access Paths Principles of Information Systems 7

Manipulating Data and Generating Reports • Data manipulation language (DML): the commands that are used to manipulate the data in a database • Structured Query Language (SQL): adopted by the American National Standards Institute (ANSI) as the standard query language for relational databases • Once a database has been set up and loaded with data, it can produce reports, documents, and other outputs Principles of Information Systems 8

Table 5. 6: Examples of SQL Commands Principles of Information Systems 9

Popular Database Management Systems • Popular DBMSs for end users include Microsoft’s Access and Corel’s Paradox • The complete database management software market includes databases by IBM, Oracle, and Microsoft • Examples of open-source database systems: Postgre. SQL and My. SQL • Many traditional database programs are now available on open-source operating systems Principles of Information Systems 10

Selecting a Database Management System • Important characteristics of databases to consider: • Size of the database • Number of concurrent users • Performance • The ability of the DBMS to be integrated with other systems Principles of Information Systems 11

Selecting a Database Management System (continued) • Important characteristics of databases to consider (continued): • Features of the DBMS • Vendor considerations • Cost of the system Principles of Information Systems 12

Database Applications: Linking the Company Database to the Internet • Corporate databases can be accessed by customers, suppliers, and company employees through: • The Internet • Intranets • Extranets • Semantic Web: a seamless integration of traditional databases with the Internet Principles of Information Systems 13

Data Warehouses, Data Marts, and Data Mining • Data warehouse: a database that collects business information from many sources in the enterprise, covering all aspects of the company’s processes, products, and customers • Data mart: a subset of a data warehouse • Data mining: an information-analysis tool that involves the automated discovery of patterns and relationships in a data warehouse Principles of Information Systems 14

Figure 5. 17: Elements of a Data Warehouse Principles of Information Systems 15

Table 5. 8: Common Data-Mining Applications Principles of Information Systems 16

Business Intelligence • Business intelligence (BI): the process of gathering enough of the right information in a timely manner and usable form and analyzing it to have a positive impact on business strategy, tactics, or operations • Knowledge management: the process of capturing a company’s collective expertise wherever it resides and distributing it wherever it can help produce the biggest payoff Principles of Information Systems 17

Distributed Databases • Distributed database • A database in which the data may be spread across several smaller databases connected via telecommunications devices • Corporations get more flexibility in how databases are organized and used • Replicated database: a database that holds a duplicate set of frequently used data Principles of Information Systems 18

Online Analytical Processing (OLAP) • Software that allows users to explore data from a number of different perspectives Table 5. 9: Comparison of OLAP and Data Mining Principles of Information Systems 19

Open Database Connectivity (ODBC) • Standards that ensure that software can be used with any ODBC-compliant database • Can be used to export, import, or link tables between different applications Principles of Information Systems 20

Figure 5. 19: Advantages of ODBC Principles of Information Systems 21

Object-Oriented and Object-Relational Database Management Systems • Object-oriented database • Stores both data and its processing instructions • Method: a procedure or action • Message: a request to execute or run a method Principles of Information Systems 22

Object-Oriented and Object-Relational Database Management Systems (continued) • Object-oriented database management system (OODBMS): group of programs that manipulate an object-oriented database and provide a user interface and connections to other application programs • Object-relational database management system (ORDBMS): DBMS capable of manipulating audio, video, and graphical data Principles of Information Systems 23

Summary • Hierarchy of data: bits, characters, fields, records, files, and databases • Entity: a generalized class of things (objects) for which data is collected, stored, and maintained • Attribute: characteristic of an entity • Data model: diagram of entities and relationships • Relational model: describes data in which all elements are placed in two-dimensional tables called relations Principles of Information Systems 24

Summary (continued) • Selecting: eliminates rows according to certain criteria • Projecting: eliminates columns in a table • Database management system (DBMS): group of programs used as an interface • Between a database and application programs • Database and the user • Data dictionary: detailed description of all the data used in the database Principles of Information Systems 25

Summary (continued) • Data warehouse: database that collects business information from all aspects of a company’s processes, products, and customers • Data mining: an information-analysis tool for the automated discovery of patterns and relationships in a data warehouse • Open database connectivity (ODBC) standards: ensure that software can be used with any ODBC-compliant database Principles of Information Systems 26