Management Information Systems Sixth Edition Chapter 7 Databases

Management Information Systems, Sixth Edition Chapter 7: Databases and Data Warehouses

Objectives • Explain the difference between traditional file organization and the database approach to managing digital data • Explain how relational and object-oriented database management systems are used to construct databases, populate them with data, and manipulate the data to produce information • Enumerate the most important features and operations of a relational database, the most popular database model Management Information Systems, Sixth Edition 2

Objectives (continued) • Understand how data modeling and design creates a conceptual blueprint of a database • Discuss how databases are used on the Web • List the operations involved in transferring data from transactional databases to data warehouses Management Information Systems, Sixth Edition 3

Managing Digital Data • Businesses collect and dissect data for many purposes • Data can be stored in database format – Easy access and manipulation • Databases have had a profound impact on business – An information industry has been created • Database technology integrated with the Internet has contributed to commerce significantly Management Information Systems, Sixth Edition 4

The Traditional File Approach • Traditional file approach: no mechanism for tagging, retrieving, or manipulating data • Database approach: provides powerful mechanism for managing and manipulating data • Traditional approach is inconvenient: – Program-data dependency – High data redundancy – Low data integrity • Data redundancy: duplication of data • Data integrity: accuracy of data Management Information Systems, Sixth Edition 5

The Traditional File Approach (continued) Management Information Systems, Sixth Edition 6

The Database Approach • Database approach: data organized as entities • Entity: an object about which an organization chooses to collect data, such as: – People – Events – Products • Character: smallest piece of data – A single letter or a digit • Field: single piece of information about entity Management Information Systems, Sixth Edition 7

The Database Approach (continued) • Record: collection of related fields • File: collection of related records • Database fields can hold images, sounds, video clips, etc. • Field name allows easy access to the data • Database management system (DBMS): program used to: – Build databases – Populate a database with data – Manipulate data in a database Management Information Systems, Sixth Edition 8

Management Information Systems, Sixth Edition 9

Management Information Systems, Sixth Edition 10

The Database Approach (continued) • Query: a message to the database requesting data from specific records and/or fields • Database must be properly secured – Not everyone should have access to all data – Users will have different views of the database, based on the data they are allowed to see Management Information Systems, Sixth Edition 11

The Database Approach (continued) • Database administrator (DBA): the person responsible for managing the database – Sets user limits for access to data in the database • DBMS is usually bundled with a programming language Management Information Systems, Sixth Edition 12

Management Information Systems, Sixth Edition 13

Management Information Systems, Sixth Edition 14

Database Models • Database model: general logical structure – How records stored in the database – How relationships between records are established • Database models differ in: – How records are linked to each other – How users can navigate the database, retrieve records, and create records Management Information Systems, Sixth Edition 15

The Relational Model • Relational Model: consists of tables • Based on relational algebra – Tuple: record (or row) – Attribute: field (or column) – Relation: table of records • To design a relational database, you must understand the entities to be stored in the database and how they relate • Tables are independent of each other, but can be related to each other Management Information Systems, Sixth Edition 16

The Relational Model (continued) • Key: a field whose values identify records – Used to retrieve records • Primary key: a field by which records are uniquely identified – Each record in the table must have a unique key value • Composite key: combination of fields that serve as a primary key Management Information Systems, Sixth Edition 17

Management Information Systems, Sixth Edition 18

The Relational Model (continued) Management Information Systems, Sixth Edition 19

The Relational Model (continued) • Foreign key: a field that is common to two tables – Used to link the tables – This field is a primary key in one table and a foreign key in the other • Join table: composite of tables • Two types of table relationships: – One-to-many relationship: one item in a table is linked to many items in the other table – Many-to-many relationship: many items in a table are linked to many items of the other table Management Information Systems, Sixth Edition 20

The Object-Oriented Model • Object-oriented database model: uses objectoriented approach for the database structure • Encapsulation: combined storage of data and relevant procedures to process it – Allows object to be “planted” in different data sets • Inheritance: the ability to create a new object by replicating the characteristics of an existing (parent) object • Object-oriented databases (ODBs) store data objects, not records Management Information Systems, Sixth Edition 21

Management Information Systems, Sixth Edition 22

Relational Operations • Relational operation: creates a temporary subset of a table or tables • Used to create a limited list or a joined table list • Three important relational operations: – Select: a selection of records based on conditions – Project: a selection of certain columns from a table – Join: join data from multiple tables to create a temporary table Management Information Systems, Sixth Edition 23

Structured Query Language • Structured Query Language (SQL): query language of choice for DBMSs • Advantages of SQL: – It is an international standard – It is provided with most relational DBMSs – It has easy-to-remember, intuitive commands Management Information Systems, Sixth Edition 24

The Schema and Metadata • Schema: a plan that describes the structure of the database, including: – Names and sizes of fields – Identification of primary keys – Relationships • Data dictionary: a repository of information about the data and its organization – Also called metadata: the data about the data Management Information Systems, Sixth Edition 25

The Schema and Metadata (continued) • Metadata includes: – Source of the data – Tables related to the data – Field and index information – Programs and processes that use the data – Population rules: what is inserted, or updated, and how often Management Information Systems, Sixth Edition 26

Management Information Systems, Sixth Edition 27

Data Modeling • Databases must be carefully planned and designed to meet business goals • Data modeling: analysis of an organization’s data and identification of the data relationships – A proactive process – Develops a conceptual blueprint of the database • Entity relationship diagram: a graphical representation of all entity relationships Management Information Systems, Sixth Edition 28

Data Modeling (continued) • Entity relationship diagram is composed of: – Boxes: identify entities – Lines: indicate relationship between entities – Crossbars: indicate mandatory fields – Circles: indicate optional – Crow’s feet: identify “many” Management Information Systems, Sixth Edition 29

Data Modeling (continued) Management Information Systems, Sixth Edition 30

Data Modeling (continued) Management Information Systems, Sixth Edition 31

Databases on the Web • Web is dependent on databases – Organizations must link their databases to the Web • Interface between Web and database required • Interface may be programmed in one of several Web programming languages, including: – Java servlets – Active server pages (ASP) – PHP (Hypertext Preprocessor) – Web application program interfaces (APIs) Management Information Systems, Sixth Edition 32

Databases on the Web (continued) Management Information Systems, Sixth Edition 33

Data Warehousing • Most data collections are used for transactions • Accumulation of transaction data is useful • Data warehouse: a large repository database that supports management decision making – Typically relational – Data is collected from transactional databases • Data mart: a smaller collection of data focusing on a particular subject or department Management Information Systems, Sixth Edition 34

From Database to Data Warehouse • Transactional databases are not suitable for business analysis – Contain only current, not historical data • Data warehouse requires large storage capacity: – Mainframe computers are often used – Scalability is an issue – Data warehouses grow continually Management Information Systems, Sixth Edition 35

Phases in Data Warehousing • Three phases in transferring data from a transactional database to a data warehouse: – Extraction phase: create files from transactional database – Transformation phase: cleanse and modify the data format – Loading phase: transfer files to data warehouse • A properly built data warehouse becomes a single source for all data required for analysis • Data mining and online analytical processing (OLAP) use data in data warehouses Management Information Systems, Sixth Edition 36

Management Information Systems, Sixth Edition 37

Summary • Organizations collect vast amounts of data • Database approach has several advantages over traditional file approach • Character: smallest piece of data • Field: made up of multiple characters • Record: collection of related fields • File: collection of related records • Database management system (DBMS): tool to construct databases Management Information Systems, Sixth Edition 38

Summary (continued) • Relational and object-oriented database models have different advantages • Keys are used to form links among entities • Primary keys are unique identifiers • Object-oriented database maintains objects that contain data and procedures that process it • Structured Query Language (SQL) is an international standard for querying databases • Database designer must construct a schema to construct a database Management Information Systems, Sixth Edition 39

Summary (continued) • Database designers conduct data modeling and create entity relationship diagrams to plan databases • Many databases are linked to Web • Data warehouses contain huge collections of historical transaction data • Data warehouse requires data extraction, transformation, and loading of transactional data • Invasion of privacy is exacerbated by database technology Management Information Systems, Sixth Edition 40