Management Information Systems, Sixth Edition

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

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

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 Management Information Systems, Sixth Edition

4. Shine in 10 (Activity 7) • Define IYOW any of the following that is assigned to you: • Database approach vs Traditional file approach • Hierarchy of Data (Show the hierarchy and examples) • Primary, Composite and Foreign keys • SQL (what it does and how it works) • ERD (how it is used – be specific) • Data Warehouse • This is worth 20pts Management Information Systems, Sixth Edition

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

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

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

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

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

10. Management Information Systems, Sixth Edition

11. Management Information Systems, Sixth Edition

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

13. The Database Approach (continued) Visual Query from MS Access Management Information Systems, Sixth Edition

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

15. Management Information Systems, Sixth Edition

16. Management Information Systems, Sixth Edition

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

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

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

20. Management Information Systems, Sixth Edition

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

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

23. The Relational Model (continued) Primary Key Foreign Key Related One-to-Many Table in MS Access Management Information Systems, Sixth Edition

24. The Relational Model (continued) Demo Management Information Systems, Sixth Edition

25. The Object-Oriented Model • Object-oriented database model: uses object-oriented 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

26. Management Information Systems, Sixth Edition

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

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

29. Structured Query Language SQL in MS Access Management Information Systems, Sixth Edition

30. Structured Query Language Demo Management Information Systems, Sixth Edition

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

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

33. Management Information Systems, Sixth Edition

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

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

36. Data Modeling (continued) Management Information Systems, Sixth Edition

37. Data Modeling (continued) Management Information Systems, Sixth Edition

38. Mini-Project 2 – ERD • Create an ERD for a small Store or Mini-store (like the stores at the back of COM). • The store of course store Information about their products, supplier, the sale that they made • each day but not of their Customer. • This is worth 100 points but the scoring is based on Group Output (50 points) and Individual • participation (50 points) • Group Criteria : • ERD reflects real world entities - 40% • Relationships are well defined - 30% • Attributes are complete and well defined - 30% • ------------------------------------------------- ------------- • TOTAL 100% • Individual Criteria : • Joins meaningful discussion - 20% • Has a substantive and meaningful assignment - 30% • Execute well his/her given assignment - 30% • Does not do unrelated stuff - 20% • ------------------------------------------------ ------------- • TOTAL 100% Management Information Systems, Sixth Edition

39. Mini-Project No. 3 - Database • Creating Mini-Store Database • Create a Database for the Mini-store based on your ERD that you have just created • Create tables that reflects an entity, namely, People, Event or Products • Create a relationship among tables • You could use MS Access or MySQL Database Management Systems on this one. • This is worth 100 points but the scoring is based on Group Output (50 points) and Individual • participation (50 points) • Group Criteria : • Tables reflects real world entities - 40% • Table Relationships are right defined - 40% • Complete sets of tables are defined - 20% • ------------------------------------------------- ------------- • TOTAL 100% • Individual Criteria : • Joins meaningful discussion - 20% • Has a substantive and meaningful assignment - 30% • Execute well his/her given assignment - 30% • Does not do unrelated stuff - 20% • ------------------------------------------------ ------------- • TOTAL 100% Management Information Systems, Sixth Edition

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

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

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

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

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

45. Management Information Systems, Sixth Edition

46. VB.net Database Demo Demo Management Information Systems, Sixth Edition

47. MS Access Demo Demo Management Information Systems, Sixth Edition

48. Mini-Project No. 4 - Project • Making a Program for a mini-store information system • The program should have the following feature: • Add/Edit/View Products • Add/Edit/Delete Suppliers • Could update inventory • Make a sales transaction • Because of a little complexity of this system I will allow group to merge into one group. • This is worth 100 points but the scoring is based on Group Output (50 points) and Individual • participation (50 points) • Group Criteria : • Program reflects the requirements - 40% • Program has no or has only minor bug - 30% • User interface and ingenuity of the program - 30% • ------------------------------------------------- ------------- • TOTAL 100% • Individual Criteria : • Joins meaningful discussion - 20% • Has a substantive and meaningful assignment - 30% • Execute well his/her given assignment - 30% • Does not do unrelated stuff - 20% • ------------------------------------------------ ------------- • TOTAL 100% Management Information Systems, Sixth Edition

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

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