Identify an open source dbms product

[Content_Types].xml
_rels/.rels
word/_rels/document.xml.rels
word/document.xml
CHAPTER PREVIEW Businesses of every size organize data records into collections called databases. At one extreme, small businesses use databases to keep track of customers; at the other extreme, huge corporations such as Boeing and Verizon use databases to support complex sales, marketing, and operations activities. In between are businesses such as All Road Parts that use databases as a crucial part of their operations. Such businesses have a small staff of professionals and can’t always support special needs, like those of Addison and Drew at All Road Parts. To obtain the one-of-a-kind reports they need, Addison and Drew need to be creative and adaptable.This chapter discusses the why, what, and how of database processing. We begin by describing the purpose of databases and then explain the important components of database systems. We then overview the process of creating a database system and summarize your role as a future user of such systems.Users have a crucial role in the development of database applications. Specifically, the structure and content of the database depend entirely on how users view their business activity. To build the database, the developers will create a model of that view using a tool called the entity-relationship model. You need to understand how to interpret such models because the development team might ask you to validate the correctness of such a model when building a system for your use. Finally, we describe the various database administration tasks.This chapter focuses on database technology. Here we consider the basic components of a database and the functions of database applications. You will learn how Addison used database reporting to solve the All Road Parts problem in Chapter 9.Q1 What Is the Purpose of a Database? The purpose of a database is to keep track of things. When most students learn that, they wonder why we need a special technology for such a simple task. Why not just use a list? If the list is long, put it into a spreadsheet.In fact, many professionals do keep track of things using spreadsheets. If the structure of the list is simple enough, there is no need to use database technology. The list of student grades in Figure 5-1, for example, works perfectly well in a spreadsheet.Figure 5-1 A List of Student Grades Presented in a SpreadsheetFigure 5-2 Student Data Shown in a Form from a Database lefttopSuppose, however, that the professor wants to track more than just grades. Say that the professor wants to record email messages as well. Or perhaps the professor wants to record both email messages and office visits. There is no place in Figure 5-1 to record that additional data. Of course, the professor could set up a separate spreadsheet for email messages and another one for office visits, but that awkward solution would be difficult to use because it does not provide all of the data in one place.Instead, the professor wants a form like that in Figure 5-2. With it, the professor can record student grades, emails, and office visits all in one place. A form like the one in Figure 5-2 is difficult, if not impossible, to produce from a spreadsheet. Such a form is easily produced, however, from a database.The key distinction between Figures 5-1 and 5-2 is that the data in Figure 5-1 is about a single theme or concept. It is about student grades only. The data in Figure 5-2 has multiple themes; it shows student grades, student emails, and student office visits. We can make a general rule from these examples: Lists of data involving a single theme can be stored in a spreadsheet; lists that involve data with multiple themes require a database. We will say more about this general rule as this chapter proceeds.As you will see, databases can be more difficult to develop than spreadsheets; this difficulty causes some people to prefer to work with spreadsheets—or at least pretend to—as described in the Security Guide on pages 186–187. Q2 What Is a Database? A database is a self-describing collection of integrated records. To understand the terms in this definition, you first need to understand the terms illustrated in Figure 5-3. As you learned in Chapter 4, a byte is a character of data. In databases, bytes are grouped into columns, such as Student Number and Student Name. Columns are also called fields. Columns or fields, in turn, are grouped into rows, which are also called records. In Figure 5-3, the collection of data for all columns (Student Number, Student Name, HW1, HW2, and MidTerm) is called a row or a record. Finally, a group of similar rows or records is called a table or a file. From these definitions, you can see that there is a hierarchy of data elements, as shown in Figure 5-4.It is tempting to continue this grouping process by saying that a database is a group of tables or files. This statement, although true, does not go far enough. As shown in Figure 5-5, a database is a collection of tables plus relationships among the rows in those tables, plus special data, called metadata, that describes the structure of the database. By the way, the cylindrical symbol labeled “database” in Figure 5-5 represents a computer disk drive. It is used like this because databases are stored on disks.lefttopFigure 5-3 Student Table (also called a file)Figure 5-4 Hierarchy of Data ElementsFigure 5-5 Components of a DatabaselefttopConsider the terms on the left-hand side of Figure 5-5. You know what tables are. To understand what is meant by relationships among rows in tables, examine Figure 5-6. It shows sample data from the three tables Email, Student, and Office Visit. Notice the column named Student Number in the Email table. That column indicates the row in Student to which a row of Email is connected. In the first row of Email, the Student Number value is 1325. This indicates that this particular email was received from the student whose Student Number is 1325. If you examine the Student table, you will see that the row for Andrea Baker has this value. Thus, the first row of the Email table is related to Andrea Baker.lefttopFigure 5-6 Example of Relationships Among Rows Now consider the last row of the Office Visit table at the bottom of the figure. The value of Student Number in that row is 4867. This value indicates that the last row in Office Visit belongs to Adam Verberra.From these examples, you can see that values in one table relate rows of that table to rows in a second table. Several special terms are used to express these ideas. A key (also called a primary key) is a column Now consider the last row of the office Visit table at the bottom of the figure. The value of Student Number in that row is 4867. This value indicates that the last row in office Visit belongs to Adam Verberra.From these examples, you can see that values in one table relate rows of that table to rows in a second table. Several special terms are used to express these ideas. A key (also called a primary 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. Given a value of Student Number, you can determine one and only one row in Student. Only one student has the number 1325, for example.Every table must have a key. The key of the Email table is EmailNum, and the key of the office Visit table is Visit ID. Sometimes more than one column is needed to form a unique identifier. In a table called City, for example, the key would consist of the combination of columns (City, State) because a given city name can appear in more than one state.Student Number is not the key of the Email or the office Visit tables. We know that about Email because there are two rows in Email that have the Student Number value 1325. The value 1325 does not identify a unique row; therefore, Student Number cannot be the key of Email.Nor is Student Number a key of office Visit, although you cannot tell that from the data in Figure 5-6. If you think about it, however, there is nothing to prevent a student from visiting a professor more than once. If that were to happen, there would be two rows in Office Visit with the same value of Student Number. It just happens that no student has visited twice in the limited data in Figure 5-6.In both Email and Office Visit, Student Number is a key, but it is a key of a different table, namely Student. Hence, the columns that fulfill a role like that of Student Number in the Email and Office Visit tables are called foreign keys. This term is used because such columns are keys, but they are keys of a different (foreign) table than the one in which they reside.Before we go on, databases that carry their data in the form of tables and that represent relationships using foreign keys are called relational databases. (The term relational is used because another, more formal name for a table like those we’re discussing is relation.) You’ll learn about another kind of database, or data store, in Q8 and in Case Study 5.Metadata Recall the definition of database: A database is a self-describing collection of integrated records. The records are integrated because, as you just learned, rows can be tied together by their key/foreign key relationship. Relationships among rows are represented in the database. But what does self-describing mean?It means that a database contains, within itself, a description of its contents. Think of a library. A library is a self-describing collection of books and other materials. It is self-describing because the library contains a catalog that describes the library’s contents. The same idea also pertains to a database. Databases are self-describing because they contain not only data, but also data about the data in the database.Metadata is data that describes data. Figure 5-7 shows metadata for the Email table. The format of metadata depends on the software product that is processing the database. Figure 5-7 shows the metadata as it appears in Microsoft Access. Each row of the top part of this form describes a column of the Email table. The columns of these descriptions are Field Name, Data Type, and Description. Field Name contains the name of the column, Data Type shows the type of data the column may hold, and Description contains notes that explain the source or use of the column. As you can see, there is one row of metadata for each of the four columns of the Email table: EmailNum, Date, Message, and Student Number.11430020955Figure 5-7 Sample Metadata (in Access) The bottom part of this form provides more metadata, which Access calls Field Properties, for each column. In Figure 5-7, the focus is on the Date column (note the light rectangle drawn around the Date row). Because the focus is on Date in the top pane, the details in the bottom pane pertain to the Date column. The Field Properties describe formats, a default value for Access to supply when a new row is created, and the constraint that a value is required for this column. It is not important for you to remember these details. Instead, just understand that metadata is data about data and that such metadata is always a part of a database.Ethics Guide: QUERYING INEQUALITY? MaryAnn Baker works as a data analyst in human relations at a large, multinational corporation. As part of its compensation program, her company defines job categories and assigns salary ranges to each category. For example, the category M1 is used for first-line managers and is assigned the salary range of $75,000 to $95,000. Every job description is assigned to one of these categories, depending on the knowledge and skills required to do that job. Thus, the job titles Manager of Customer Support, Manager of Technical Writing, and Manager of Product Quality Assurance are all judged to involve about the same level of expertise and are all assigned to category M1.One of MaryAnn’s tasks is to analyze company salary data and determine how well actual salaries conform to established ranges. When discrepancies are noted, human relations managers meet to determine whether the discrepancy indicates a need to:• Adjust the category’s salary range; • Move the job title to a different category; • Define a new category; or • Train the manager of the employee with the discrepancy on the use of salary ranges in setting employee compensation. MaryAnn is an expert in creating database queries. Initially, she used Microsoft Access to produce reports, but much of the salary data she needs resides in the organization’s Oracle database. At first, she would ask the IS Department to extract certain data and move it into Access, but over time she learned that it was faster to ask IS to move all employee data from the operational Oracle database into another Oracle database created just for HR data analysis. Although Oracle provides a graphical query interface like that in Access, she found it easier to compose complex queries directly in SQL, so she learned it and, within a few months, became an SQL expert.“I never thought I’d be doing this,” she said. “But it turns out to be quite fun, like solving a puzzle, and apparently I’m good at it.”left643890One day, after a break, MaryAnn signed into her computer and happened to glance at the results of a query that she’d left running while she was gone. “That’s odd,” she thought, “all the people with Hispanic surnames have lower salaries than the others.” She wasn’t looking for that pattern; it just happened to jump out at her as she glanced at the screen.As she examined the data, she began to wonder if she was seeing a coincidence or if there was a discriminatory pattern within the organization. Unfortunately for MaryAnn’s purposes, the organization did not track employee race in its database, so she had no easy way of identifying employees of Hispanic heritage other than reading through the list of surnames. But, as a skilled problem solver, that didn’t stop MaryAnn. She realized that many employees having Hispanic origins were born in certain cities in Texas, New Mexico, Arizona, and California. Of course, this wasn’t true for all employees; many non-Hispanic employees were born in those cities, too, and many Hispanic employees were born in other cities. This data was still useful, however, because MaryAnn’s sample queries revealed that the proportion of employees with Hispanic surnames who were also born in those cities was very high. “OK,” she thought, “I’ll use those cities as a rough surrogate.”Using birth city as a query criterion, MaryAnn created queries that determined employees who were born in the selected cities earned, on average, 23 percent less than those who were not. “Well, that could be because they work in lower-pay-grade jobs.” After giving it a bit of thought, MaryAnn realized that she needed to examine wages and salaries within job categories. “Where,” she wondered, “do people born in those cities fall in the ranges of their job categories?” So, she constructed SQL to determine where within a job category the compensation for people born in the selected cities fell. “Wow!” she said to herself, “almost 80 percent of the employees born in those cities fall into the bottom half of their salary range.”MaryAnn scheduled an appointment with her manager for the next day.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. To find out what a database contains, we just look at the metadata inside the database.Q3 What Is a Database Management System (DBMS)? A database management system (DBMS) is a program used to create, process, and administer a database. As with operating systems, almost no organization develops its own DBMS. Instead, companies license DBMS products from vendors such as IBM, Microsoft, Oracle, and others. Popular DBMS products are DB2 from IBM, Access and SQL Server from Microsoft, and Oracle Database from the Oracle Corporation. Another popular DBMS is MySQL, an open source DBMS product that is license-free for most applications.1 Other DBMS products are available, but these five process the great bulk of databases today.Note that a DBMS and a database are two different things. For some reason, the trade press and even some books confuse the two. A DBMS is a software program; a database is a collection of tables, relationships, and metadata. The two are very different concepts.Creating the Database and Its Structures Database developers use the DBMS to create tables, relationships, and other structures in the database. The form in Figure 5-7 can be used to define a new table or to modify an existing one. To create a new table, the developer just fills the new table’s metadata into the 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. For example, in Figure 5-8 the developer has added a new column called Response?. This new column has the data type Yes/No, which means that the column can contain only one value—Yes or No. The professor will use this column to indicate whether he has responded to the student’s email. A column can be removed by deleting its row in this table, though doing so will lose any existing data.Figure 5-8 Adding a New Column to a Table (in Access) Source: Microsoft Access 2013 1MySQL was supported by the MySQL company. In 2008, that company was acquired by Sun Microsystems, which was, in turn, acquired by Oracle later that year. However, because MySQL is open source, Oracle does not own the source code. A GROUP EXERCISE Using MIS in Class 5: How Much Is a Database Worth? The Firm, Minneapolis (www.TheFirmMpls.com), is a workout studio that realizes more than 15,000 person-visits per month, an average of 500 visits per day. Neil Miyamoto, one of the two business partners, believes that the database is The Firm’s single most important asset. According to Neil:Take away anything else—the building, the equipment, the inventory—anything else, and we’d be back in business 6 months or less. Take away our customer database, however, and we’d have to start all over. It would take us another 8 years to get back where we are.2 Why is the database so crucial? It records everything the company’s customers do. If the Firm decides to offer an early morning kickboxing class featuring a particular trainer, it can use its database to offer that class to everyone who ever took an early morning class, a kickboxing class, or a class by that trainer. Customers receive targeted solicitations for offerings they care about and, maybe equally important, they don’t receive solicitations for those they don’t care about. Clearly, The Firm database has value and, if it wanted to, The Firm could sell that data.In this exercise, you and a group of your fellow students will be asked to consider the value of a database to organizations other than The Firm.1. Many small business owners have found it financially advantageous to purchase their own building. As one owner remarked upon his retirement, “We did well with the business, but we made our real money by buying the building.” Explain why this might be so. 2. To what extent does the dynamic you identified in your answer to item 1 pertain to databases? Do you think it likely that, in 2050, some small business owners will retire and make statements like, “We did well with the business, but we made our real money from the database we generated?” Why or why not? In what ways is real estate different from database data? Are these differences significant to your answer? 3. Suppose you had a national database of student data. Assume your database includes the name, email address, university, grade level, and major for each student. Name five companies that would find that data valuable and explain how they might use it. (For example, Pizza Hut could solicit orders from students during finals week.) 4. Describe a product or service that you could develop that would induce students to provide the data in item 3. 5. Considering your answers to items 1 through 4, identify two organizations in your community that could generate a database that would potentially be more valuable than the organization itself. Consider businesses, but also think about social organizations and government offices. For each organization, describe the content of the database and how you could entice customers or clients to provide that data. Also, explain why the data would be valuable and who might use it.6. Relate what you have learned in this exercise to the 3D printing discussion at All Road Parts. 7. Prepare a 1-minute statement of what you have learned from this exercise that you could use in a job interview to illustrate your ability to innovate the use of technology in business. 8. Present your answers to items 1–6 to the rest of the class. 2Personal conversation with the author, 2012. Reprinted by permission. Processing the Database The second function of the DBMS is to process the database. Such processing can be quite complex, but, fundamentally, the DBMS provides applications for four processing operations: to read, insert, modify, or delete data. These operations are requested in application calls upon the DBMS. From a form, when the user enters new or changed data, a computer program behind the form calls the DBMS to make the necessary database changes. From a Web application, a program on the client or on the server calls the DBMS directly to make the change.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 (pronounced “see-quell”) statements. As an example, the following SQL statement inserts a new row into the Student table:INSERT INTO Student ([Student Number], [Student Name], HW1, HW2, MidTerm) VALUES (1000, ‘Franklin, Benjamin’, 90, 95, 100); As stated, statements like this one are issued “behind the scenes” by programs that process forms and reports. Alternatively, they can be issued directly to the DBMS by an application program.You do not need to understand or remember SQL language syntax. Instead, just realize that SQL is an international standard for processing a database. SQL can also be used to create databases and database structures. You will learn more about SQL if you take a database management class.Administering the Database A third DBMS function is to provide tools to assist 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. To provide database security, a user must sign on using a valid user account before she can process the database.Permissions can be limited in very specific ways. In the Student database example, it is possible to limit a particular user to reading only Student Name from the Student table. A different user could be given permission to read the entire Student table, but limited to update only the HW1, HW2, and MidTerm columns. Other users can be given still other permissions.In addition to security, DBMS administrative functions 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.For important databases, most organizations dedicate one or more employees to the role of database administration. Figure 5-9 summarizes the major responsibilities for this function. You will learn more about this topic if you take a database management course.Q4 How Do Database Applications Make Databases More Useful? A set of database tables, by itself, is not very useful; the tables in Figure 5-6 contain the data the professor wants, but the format is awkward at best. The data in database tables can be made more useful, or more available for the conception of information, when it is placed into forms like that in Figure 5-2 or other formats.A database application is a collection of forms, reports, queries, and application programs3 that serves as an intermediary between users and database data. Database applications reformat database table data to make it more informative and more easily updated. Application programs also have features that provide security, maintain data consistency, and handle special cases.3Watch out for confusion between a database application and a database application program. A database application includes forms, reports, queries, and database application programs. Figure 5-9 Summary of Database Administration (DBA) TasksThe specific purposes of the four elements of a database application are:Forms View data; insert new, update existing, and delete existing dataReports Structured presentation of data using sorting, grouping, filtering, and other operationsQueries Search based upon data values provided by the userApplication programs Provide security, data consistency, and special purpose processing, e.g., handle out-of-stock situationsDatabase applications came into prominence in the 1990s and were based on the technology that was available at that time. Many existing systems today are long-lived extensions to those applications; the ERP system SAP (discussed in Chapter 7) is a good example of this concept. You should expect to see these kinds of applications during the early years of your career.Today, however, many database applications are based on newer technology that employs browsers, the Web, and related standards. These browser-based applications can do everything the older ones do, but they are more dynamic and better suited to today’s world. To see why, consider each type.Figure 5-10 Components of a Database Application System Traditional Forms, Queries, Reports, and Applications In most cases, a traditional database is shared among many users. In that case, the application shown in Figure 5-10 resides on the users’ computers and the DBMS and database reside on a server computer. A network, in most cases not the Internet, is used to transmit traffic back and forth between the users’ computers and the DBMS server computer.Database technology puts unprecedented ability to conceive information into the hands of users. But what do you do with that information when you find something objectionable? See the Ethics Guide on pages 164–165 for an example case. Single-user databases like those in Microsoft Access are an exception. With such databases, the application, the DBMS, and the database all reside on the user’s computer.Traditional forms appeared in window-like displays like that in Figure 5-2. They serve their purpose; users can view, insert, modify, and delete data with them, but by today’s standards, they look clunky.lefttop00Figure 5-11 Example of a Student ReportFigure 5-11 shows a traditional report, which is a static display of data, placed into a format that is meaningful to the user. In this