Chapter 7 Databases and Data Warehouses Management Information

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

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, Fifth 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, Fifth Edition 3

Managing Digital Data • Businesses collect and dissect data • Data can be stored in powerful database format – Easy access and manipulation • Databases have profound impact on business • Database technology integrated with Internet Management Information Systems, Fifth Edition 4

The Traditional File Approach • Traditional file approach: no mechanism for manipulating data • Database approach: has mechanism for manipulating data • Traditional approach inconvenient – High data redundancy – Low data integrity • Data redundancy: duplication of data • Data integrity: accuracy of data Management Information Systems, Fifth Edition 5

The Traditional File Approach (continued) Figure 7. 1: The layout of a human resource file in traditional file organization Management Information Systems, Fifth Edition 6

The Database Approach • Database approach: data organized as entities • Entity: object that has data – People – Events – Products • Character: smallest piece of data • Field: single piece of information about entity • Record: collection of fields Management Information Systems, Fifth Edition 7

The Database Approach (continued) • File: collection of related records • Database management system (DBMS): program used to build databases – Populates with data – Manipulates data – Query: message requesting access to data Management Information Systems, Fifth Edition 8

The Database Approach (continued) • Database has security issues • Database administrator (DBA): limits user access to database – Requires users to enter codes • DBMS bundled with fourth-generation languages Management Information Systems, Fifth Edition 9

The Database Approach (continued) Figure 7. 2: Data hierarchy Management Information Systems, Fifth Edition 10

The Database Approach (continued) Databases include more than just text and numbers: for instance, a database used by real Estate agents may show property pictures in addition to addresses, prices, and sale status Management Information Systems, Fifth Edition 11

The Database Approach (continued) Figure 7. 3: Different database views reveal different combinations of data Management Information Systems, Fifth Edition 12

The Database Approach (continued) Figure 7. 4: Different views from the same database Management Information Systems, Fifth Edition 13

Database Models • Database model: general logical structure – How records stored in database – Records linked differently in different models – Models constantly changing Management Information Systems, Fifth Edition 14

The Relational Model • Relational Model: consists of tables • Based on relational algebra – Tuple: record – Attribute: field – Relation: table – Key: identifier field • Used to retrieve records Management Information Systems, Fifth Edition 15

Relational Model (continued) Figure 7. 5: A relational database Management Information Systems, Fifth Edition 16

The Relational Model (continued) • Primary key: unique key – Uniquely identifies record – Required in table • Composite key: combination of fields – Serves as primary key • Foreign key: shared field – Links tables • Join table: composite of tables Management Information Systems, Fifth Edition 17

The Relational Model (continued) Figure 7. 6: A join table Management Information Systems, Fifth Edition 18

The Relational Model (continued) • Table relationships with other tables • One-to-many relationship: one item in table linked to many items in other table • Many-to-many relationship: many items in table linked to many items of other table Management Information Systems, Fifth Edition 19

The Object-Oriented Model • Object-Oriented model: uses object-oriented approach • Encapsulation: combined storage of data and relevant procedures – Allows object to be planted in different data sets • Inheritance: creates new object by replicating characteristics of existing (parent) object Management Information Systems, Fifth Edition 20

The Object-Oriented Model (continued) Figure 7. 7: An object-oriented database Management Information Systems, Fifth Edition 21

Relational Operations • Relational operation: create temporary subset of table • Create limited list or joined table list – Select records based on conditions – Project columns – Join tables to create temporary table Management Information Systems, Fifth Edition 22

Structured Query Language • Structured query language: language of choice for DBMSs • Advantages – Standardized language – Used in many host languages • Portable Management Information Systems, Fifth Edition 23

The Schema and Metadata • Schema: plan – Describes structure of database – Names and sizes of fields – Identifies primary keys • Data dictionary: repository of information about data Management Information Systems, Fifth Edition 24

The Schema and Metadata (continued) • Metadata: data about data – Source of data – Tables related to data – Field information – Usage of data – Population rules Management Information Systems, Fifth Edition 25

The Schema and Metadata (continued) Figure 7. 8: Schema of the Employee table in an Access database Management Information Systems, Fifth Edition 26

Data Modeling • Databases must be carefully planned • Data modeling: analysis and organization of data – Proactive process – Develop conceptual blueprint • Entity relationship diagram: graphical representation of relationships Management Information Systems, Fifth Edition 27

Data Modeling (continued) • Entity relationship diagram – Boxes identify entities – Lines indicate relationship – Crossbars indicate mandatory fields – Circles indicate optional – Crows feet identify “many” Management Information Systems, Fifth Edition 28

Data Modeling (continued) Figure 7. 9: An entity relationship diagram (ERD) Management Information Systems, Fifth Edition 29

Data Modeling (continued) Figure 7. 10: Fields of the Professor entity Management Information Systems, Fifth Edition 30

Databases on the Web • Web dependent on databases • Interface between Web and database required – CGI – ASP – API Management Information Systems, Fifth Edition 31

Databases on the Web (continued) Figure 7. 11: Active server pages and similar software enable data queries and entry via the Web Management Information Systems, Fifth Edition 32

Data Warehousing • Data collections used for transactions • Accumulation of transaction data useful • Data warehouse: large database – Typically relational – Supports decision making – Data copied from transactional database • Data mart: collection of data focusing on particular subject Management Information Systems, Fifth Edition 33

From Database to Data Warehouse • Transactional database not suitable for business analysis – Only current data – Not historic • Data warehouse requires large storage capacity – Mainframe computers used – Scalability issue Management Information Systems, Fifth Edition 34

Phases in Building a Data Warehouse • Begin building data warehouse after equipment secured – Extraction phase • Create files from transactional database – Transformation phase • Cleanse and modify data • Loading phase • Transfer files to data warehouse Management Information Systems, Fifth Edition 35

Phases in Building a Data Warehouse (continued) Figure 7. 12: Phases in preparing and using a data warehouse Management Information Systems, Fifth Edition 36

Summary • Organizations collect vast amounts of data • Database approach has advantages over traditional approach • Character: smallest piece of data • File: collection of records • Designer must construct schema to construct database Management Information Systems, Fifth Edition 37

Summary (continued) • Database management system enables database construction and manipulation • Relational and object-oriented database models have different advantages • Keys used to form links among entities • Object-oriented database maintains links differently • SQL adopted as international standard Management Information Systems, Fifth Edition 38

Summary (continued) • Designers conduct data modeling to show required tables • Databases often linked to Web • Data warehouses contain huge collections of historical data • Data warehouse allows data extraction, transformation, and loading • Invasion of privacy is exacerbated by database technology Management Information Systems, Fifth Edition 39