Using Management Information Systems

1. Using Management Information Systems David Kroenke Database Processing Chapter 4 © 2007 Prentice Hall, Inc.

2. Learning Objectives • Know the purpose of database processing. • List the components of a database system. • Understand important database terms. • Know the elements of the entity-relationship model. © 2007 Prentice Hall, Inc.

3. Learning Objectives (Continued) • Understand the general nature of database design. • Recognize the need for and know the basic tasks of database administration . © 2007 Prentice Hall, Inc.

4. Purpose of a Database • The purpose of a database is to keep track of things that involve more than one theme. © 2007 Prentice Hall, Inc.

5. Figure 4-1 A List of Student Grades © 2007 Prentice Hall, Inc.

6. Figure 4-2 Student Data Shown in Form from Database © 2007 Prentice Hall, Inc.

7. What Is a Database? • A database is a self-describing collection of integrated records. • A byte is a character of data. • Bytes are grouped into columns, such as Student Number and Student Name. • Columns are also called fields. © 2007 Prentice Hall, Inc.

8. What Is a Database? (Continued) • Columns or fields, in turn, are grouped into rows, which are also called records. • There is a hierarchy of data elements. • A database is a collection of tables plus relationships among the rows in those tables, plus special data, called metadata. • Metadata describes the structure of the database. © 2007 Prentice Hall, Inc.

9. Figure 4-3 Student Table (also called File) © 2007 Prentice Hall, Inc.

10. Figure 4-4 Hierarchy of Data Elements © 2007 Prentice Hall, Inc.

11. Relationships Among Records • A key is a column or group of columns that identifies a unique row in a table. • Student Number is the key of the Student table. • A foreign key is a non-key column or field in one table that links to a primary key in another table. • Student Number in the Email and Office_Visit tables • Relational databases store their data in the form of tables that represent relationships using foreign keys. © 2007 Prentice Hall, Inc.

12. Figure 4-5 Components of a Database © 2007 Prentice Hall, Inc.

13. Figure 4-6 Examples of Relationships Among Rows © 2007 Prentice Hall, Inc.

14. Metadata • Databases are self-describing because they contain not only data, but also data about the data in the database • . • Metadata are data that describe data. • The format of metadata depends on the software product that is processing the database. • Field properties describe formats, a default value for Microsoft Access to supply when a new row is created, and the constraint that a value is required for the column. © 2007 Prentice Hall, Inc.

15. Metadata (Continued) • The presence of metadata makes databases much more useful. • Because of metadata, no one needs to guess, remember, or even record what is in the database. • Metadata make databases easy to use for both authorized and unauthorized purposes. © 2007 Prentice Hall, Inc.

16. Figure 4-7 Example Metadata (in Access) © 2007 Prentice Hall, Inc.

17. Components of a Database Application System • By itself database, is not very useful. • Pure database data are correct, but in raw form they are not pertinent or useful. • Database applications make database data more accessible and useful. © 2007 Prentice Hall, Inc.

18. Components of a Database Application System (Continued) • Users employ a database application that consists of forms, formatted reports, queries, and application programs. • Each of these, in turn, calls on the database management system (DBMS) to process the database tables. © 2007 Prentice Hall, Inc.

19. Figure 4-8 Components of a Database Application System © 2007 Prentice Hall, Inc.

20. Database Management System • A database management system (DBMS) is a program used to create, process, and administer a database. • Almost no organization develops its own DBMS. • Companies license DBMS products from vendors like IBM, Microsoft, Oracle, and others. © 2007 Prentice Hall, Inc.

21. Database Management System (Continued) • Popular DBMS products are: • DB2 from IBM • Access and SQL Server from Microsoft • MySQL, an open-source DBMS product that is free for most applications • The DBMS and the database are two different things: • A DBMS is a software program. • A database is a collection of tables, relationships, and metadata. © 2007 Prentice Hall, Inc.

22. Creating the Database and Its Structures • Database developers use the DBMS to create tables, relationships, and other structures in the database. • A form can be used to define a new table or to modify an existing one. • To create a new table, the developer just fills out a new form. • To modify an existing table say, to add a new column, the developer opens the metadata form for that table and adds a new row of metadata. © 2007 Prentice Hall, Inc.

23. Figure 4-9 Adding a New Column to a Table (in Access) © 2007 Prentice Hall, Inc.

24. Processing the Database • The second function of the DBMS is to process the database. • Applications use the DBMS for four operations: read, insert, modify, or delete data. • The applications call upon the DBMS in different ways: • Via a form, when the user enters new or changed data • Via a computer program behind the form calls the DBMS to make the necessary database changes • Via an application program, the program calls the DBMS directly to make the change © 2007 Prentice Hall, Inc.

25. Processing the Database (Continued) • Structured Query Language (SQL) is an international standard language for processing a database. • All five of the DBMS products mentioned earlier accept and process SQL statements. • SQL can be used to create databases and database structures. © 2007 Prentice Hall, Inc.

26. Administering the Databases • A third DBMS function is to provide tools in the administration of the database. • Database administration involves a wide variety of activities. • For example, the DBMS can be used to set up a security system involving user accounts, passwords, permissions, and limits for processing the database • DBMS administrative functions also include: • Backing up database data • Adding structures to improve the performance of database applications • Removing data that are no longer wanted or needed, and similar tasks © 2007 Prentice Hall, Inc.

27. Database Applications • A database application is a collection of forms, reports, queries, and application programs that process a database. • A database may have one or more applications, and each application may have one or more users. • Applications have different purposes, features, and functions, but they all process the same inventory data stored in a common database. © 2007 Prentice Hall, Inc.

28. Figure 4-10 Use of Multiple Database Applications © 2007 Prentice Hall, Inc.

29. Forms, Reports, and Queries • Data entry forms are used to read, insert, modify, and delete data. • Reports show data in a structured content. • Some reports also compute values as they present the data. • DBMS programs provide comprehensive and robust features for querying database data. © 2007 Prentice Hall, Inc.

30. Figure 4-11 Example Student Report © 2007 Prentice Hall, Inc.

31. Figure 4-12 Example Query © 2007 Prentice Hall, Inc.

32. Database Application Programs • Application programs process logic that is specific to a given business need. • Application programs enable database processing over the Internet. • For this use, the application program serves as an intermediary between the Web server and the database. • The application program responds to events, such as when a user presses a submit button; it also reads; inserts; modifies; and deletes database data. © 2007 Prentice Hall, Inc.

33. Figure 4-13 Four Application Programs on Web Server Computer © 2007 Prentice Hall, Inc.

34. Multi-User Processing • Multi-user processing is common, but it does pose unique problems that you, as a future manager, should know about . © 2007 Prentice Hall, Inc.

35. Multi-User Processing (Continued) • When more that one user is trying to access a particular database table at same time, the first user to gain assess to the database table has the the correct content value, the other users may not have the correct content value because the first user may modify the value without the other users knowing. • This problem is known as the lost-update problem, exemplifies one of the special characteristics of multi-user database processing. • To prevent this problem, some type of locking must be used to coordinate the activities of users who know nothing about one another. © 2007 Prentice Hall, Inc.

36. Multi-User Processing (Continued) • Converting to a single-user database to a multi-user database requires more than simply connecting another computer. • The logic of the underlying application processing needs to be adjusted as well. • Be aware of possible data conflicts when you manage business activities that involve multi-user processing. © 2007 Prentice Hall, Inc.

37. Enterprise DBMS Versus Personal DBMS • DBMS products fall into two broad categories: Enterprise DBMS and Personal DBMS. Enterprise DBMS • These products process large organizational and workgroup databases. • These products support many users, perhaps thousands, of users and many different database applications. • Such DBMS products support 24/7 operations and can manage dozens of different magnetic disks with hundreds of gigabytes or more data. • IBM’s DB2, Microsoft’s SQL Server, and Oracle are examples of enterprise DBMS products. © 2007 Prentice Hall, Inc.

38. Enterprise DBMS Versus Personal DBMS (Continued) Personal DBMS • These products are designed for smaller, simpler database applications. • Such products are used for personal or small workgroup applications that involve fewer than 100 users, and normally fewer than 15. • The great bulk of databases in this category have only a single user. • Microsoft Access is the only available personal DBMS. © 2007 Prentice Hall, Inc.

39. Figure 4-14 Personal Database System © 2007 Prentice Hall, Inc.

40. Developing a Database Application • The reason that user involvement is so important for database development is that the database design depends entirely on how users view their business environment. • Database structures can be complex, in some cases, very complex. • Before building, the database, the developers construct a logical representation of database data called a data model. © 2007 Prentice Hall, Inc.

41. Developing a Database Application (Continued) • The data model describes the data and relationships that will be stored in the database. • The data model is referred to as a blueprint. © 2007 Prentice Hall, Inc.

42. Figure 4-15 Database Development Process © 2007 Prentice Hall, Inc.

43. Entities • An entity is something that the users want to track. • Examples of entities are Order, Customer, Salesperson, and Item • Some entities represent a physical object, such as an Item or Salesperson; others represent a logical construct of transaction, such as Order or Contact. • Entities have attributes that describe characteristics of the entity. • Example attributes of Salesperson are SalespersonName, Email, Phone, and so forth © 2007 Prentice Hall, Inc.

44. Entities (Continued) • Entities have an identifier, which is an attribute (or group of attributes) whose value is associated with one and only one entity instance. • For example, OrderNumber is an identifier of Order,because only one Order instance has a given value of OrderNumber. • CustomerNumber is an identifier of Customer. • If each member of the sales staff has a unique name, then SalespersonName is an identifier of Salesperson. © 2007 Prentice Hall, Inc.

45. Figure 4-16 Student Data Model Entities © 2007 Prentice Hall, Inc.

46. Relationships • Entities have relationships to each other. • An Order, for example, has an relationship to Customer entity and also to a Salesperson entity • Database designers use diagrams called entity-relationship (E-R) diagrams. • All of the entities of one type are represented by a single rectangle. • A line is used to represent a relationship between two entities. © 2007 Prentice Hall, Inc.

47. Relationships (Continued) • If two entities have a plain straight line between them, then this type of relationship is called one-to-one. • If two entities have a line between them, but at the end of one line in one of the two directions (left or right) exists an arrow (crow’s foot), then this type of relationship is called one-to-many. © 2007 Prentice Hall, Inc.

48. Relationships (Continued) • If two entities have a line between them, but at the end of the line in both directions (left and right) exists an arrow (crow’s foot), then this type of relationship is called many-to-many. • The crow’s-foot notation shows the maximum number of entities that can be involved in a relationship. • This is called the relationship’s maximum cardinality. • Common examples of maximum cardinality are 1:N, N:M, and 1:1. • Constraints on minimum requirements are called minimum cardinalities. © 2007 Prentice Hall, Inc.

49. Figure 4-17 Example of Department, Adviser, and Student Entities and Relationships © 2007 Prentice Hall, Inc.

50. Figure 4-18 Example of Relationships-Version 1 © 2007 Prentice Hall, Inc.