Management Information Systems By Effy Oz & Andy Jones

1. Management Information SystemsByEffy Oz & Andy Jones Chapter 6: Databases and Data Warehouses www.cengage.co.uk/oz

2. Objectives • Explain the difference between traditional file organisation 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

3. 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

4. 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

5. 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

6. The Traditional File Approach (continued)

7. The Database Approach • Database approach: data organised 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

8. 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

9. 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

10. The Database Approach (continued)

11. The Database Approach (continued)

12. The Database Approach (continued)

13. Database Models • Database model: general logical structure • How records stored in database • Records linked differently in different models • Models constantly changing

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

15. Relational Model (continued)

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

17. The Relational Model (continued)

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

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

20. The Object-Oriented Model (continued)

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

22. Structured Query Language • Structured query language: language of choice for DBMSs • Advantages • Standardised language • Used in many host languages • Portable

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

24. The Schema and Metadata (continued) • Metadata: data about data • Source of data • Tables related to data • Field information • Usage of data • Population rules

25. The Schema and Metadata (continued)

26. Data Modeling • Databases must be carefully planned • Data modeling: analysis and organisation of data • Proactive process • Develop conceptual blueprint • Entity relationship diagram: graphical representation of relationships

27. Data Modeling (continued) • Entity relationship diagram • Boxes identify entities • Lines indicate relationship • Crossbars indicate mandatory fields • Circles indicate optional • Crows feet identify “many”

28. Data Modeling (continued)

29. Data Modeling (continued)

30. Databases on the Web • Web dependent on databases • Interface between Web and database required • CGI • ASP • API

31. Databases on the Web (continued)

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

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

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

35. Phases in Building a Data Warehouse (continued)

36. Summary • organisations 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

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

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