Chapter 5 Database Processing This Could Happen to

Chapter 5 Database Processing

This Could Happen to You: “The Database Shows. . . ” Neil uses software to query a database, but it has about 25 standard queries that don’t give him all he needs. He imports data from database into Excel where he moves, sorts, sums and averages data until he gets information he wants. It’s a pain, but it works. Flex. Time video clip Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -2

Study Questions Q 1 What is the purpose of a database? Q 2 What does a database contain? Q 3 What is a DBMS, and what does it do? Q 4 What is a database application? Q 5 What is the difference between an enterprise DBMS and a personal DBMS? How does the knowledge in this chapter help Flex. Time and you? Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -3

Q 1: What Is the Purpose of a Database? Purpose: To organize and keep track of things Spreadsheets • Keep track of only a single theme ØExample: Student test scores in a course Databases • Allow keeping track of multiple themes ØExamples: Student grades, office visits, student email Watch video Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -4

A List of Student Grades, Presented in a Spreadsheet Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -5

Student Data Shown in a Form from a Database Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -6

Q 2: What Does a Database Contain? Table or file: A group of records Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -7

Hierarchy of Data Elements Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -8

Components of a Database “Metadata” Describes structure of database and its data Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -9

Relationships Among Records Database has multiple tables (one for each theme) Values in one table may relate to records in other tables Relational database Primary or Unique key Foreign key Field(s) that uniquely identify a record in a table Each table must have a key A non-primary key in one table that is related to a primary key of a different table Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -10

Special Terms Key • A column or group of columns that identifies a unique row in a table • Student Number is key of Student table • Every table must have a key • Sometimes more than one column is needed to form a unique identifier Example: key of City table, would be a combination of City and State columns. • Email_Num is key of Email Table • Visit. ID is key of Office_Visit Table Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 1 -11

More Special Terms Relational databases Relation • Relationships among tables are created by using foreign keys • Formal term for a table Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 1 -12

Relationships Among Records in Three Tables Figure 5 -6 Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -13

Metadata Database: Self-describing collection of integrated records Metadata Describes data by: • Data that describes data • Makes databases more useful • Makes databases easier to use • Data type: text, number, date, etc. • Field name • Field properties Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -14

Sample Metadata in Access Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -15

Experiencing MIS In. Class Exercise 5: How Much Is a Database Worth? Flex. Time database has value and could sell that data. Data on everything customers do— 15, 000 person-visits a year, an average of 500 visits per day. Use database to offer class to everyone whoever took early morning class, kickboxing class, or class by particular trainer. Customers receive targeted solicitations for offerings they care about, and don’t receive solicitations for those they don’t. “Take away our customer database, however, and we’d have to start all over. It would take. . . 8 years to get back (to) where we are. ” Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -16

Experiencing MIS In. Class Exercise 5: How Much Is a Database Worth? (cont’d) 1. Many small business owners have found it financially advantageous to purchase their own building. Why? 2. In what ways is real estate different from database data? Are these differences significant to your answer for question 1? 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. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -17

Experiencing MIS In. Class Exercise 5: How Much Is a Database Worth? (cont’d) 4. Describe a product or service that you could develop that would induce students to provide the data in step 3. 5. Considering your answers to steps 1 through 4, identify two organizations in your community that could generate a database that could be potentially be more valuable than the organization itself. Consider businesses, social organizations and government offices. Explain why the data would be valuable and who might use it. 6. Prepare a 1 -minute statement of what you learned from this exercise that you could use in a job interview to illustrate your ability to innovate use of technology in business. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -18

Q 3: What Is a DBMS and What Does It Do? Database management system (DBMS) ―Program that creates, processes, and administers databases ―Examples: DB 2, Microsoft Access, SQL Server, Oracle, My. SQL (open-source DBMS) Database Collection of tables, relationships, and metadata DBMS A software program Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -19

Components of a Database Application System Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -20

Creating the Database and Its Structure: Adding “Response? ” Field to a Table Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -21

Processing the Database Four DBMS operations • Read, Insert, Modify, and Delete data Structured Query Language (SQL) • International standard language for processing databases Example SQL statement • INSERT INTO Student • ([Student Number], [Student Name], HW 1, HW 2, Mid. Term) • VALUES (1000, ‘Franklin, Benjamin’, 90, 95, 100) If Neil knew SQL, he could formulate his own queries against the database. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -22

Administering the Database DBMS security features to set up user accounts, passwords, permissions, processing limits Permissions—setting data access rights for specific users or groups of users Database backup and replication, adding structures, removing unneeded data Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -23

Q 4: What Is a Database Application? Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -24

Forms, Reports, and Queries Forms • Used to read, insert, modify, and delete data Reports • Show data in structured context • May compute values such as Totals, within a report Queries • Are a means of getting answers from database data Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -25

Example of a Database Query and Query Result Query result Query form Report Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -26

Database Application Programs Internet database processing to: Processing logic for a specific business need Connect web server and database Respond to events Read, insert, modify, delete data Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -27

Four Database Application Programs Running on a Web Server Via Internet Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -28

Multiuser Processing Considerations Lost-update problem Occurs when two or more transactions select same record at same time, then update it based on values first received. Each transaction is independent. Last update overwrites updates previously made by others. 10 -5 = 5 5, 7 or 3? 10 -3 = 7 Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -29

Multi-User Processing Problem: Lost-update problem 1. Process A reads customer record from file containing customer’s account balance. 1 3. Process A updates account balance in its copy of customer record and writes record to file. 2 2. Process B reads same record from same file now has its own copy. 3 5. Process B writes stale account balance value to file, causing changes made by Process A to be lost. 4 5 4. Process B has original stale value for account balance. Updates customer’s phone number and writes customer record to file. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 1 -30

Preventing Lost Update Problem Locking • Prevents another user or process to open a record currently being used by another user or process. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -31

Q 5: What Is the Difference Between an Enterprise DBMS and a Personal DBMS? Enterprise DBMS Personal DBMS • Processes large organizational and workgroup databases • Supports many users (thousands plus) • Examples: DB 2, SQL Server, Oracle, My. SQL • Designed for smaller, simpler database applications • Supports fewer than 100 users (mostly 1– 10 users) • Examples: Access, d. Base, Fox. Pro, R-Base Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -32

Personal Database System Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -33

How Does the Knowledge in This Chapter Help Flex. Time and You? Video Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -34

Ethics Guide: Nobody Said I Shouldn’t Chris employed to configure computers, manage network, maintaining servers and making database backups. Queried SQL Server metadata Made unauthorized copy of one database backup Located tables with order data, customers, salespeopl e Mentioned it to Jason on Friday Discovered one order-entry clerk (Jason) gave large discounts to a buyer that no one else did Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall Chris was fired Monday mornin g 5 -35

Ethics Guide: Nobody Said I Shouldn’t (cont’d) Where did Chris go wrong? Was it illegal, unethical, or okay for Chris to copy the database and take it home? How could Chris have handled his discovery of anomaly and protected himself? Does Chris have any legal recourse over being fired? How can a business protect its databases from unauthorized use or duplication? Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -36

Guide: No, Thanks, I’ll Use a Spreadsheet Databases take time to build Complicated to operate Shares data that you may not want to expose Is the car salesman’s arguments against keeping his business data in a database valid? Need IS people to create it and keep it running Spreadsheets may be a better option in some cases Would it be better for employer if all its salespeople used a database? Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -37

Active Review Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -38

Aviation Safety Network Provides up-to-date, complete, reliable information on airliner accidents and safety issues Data include information on commercial, military and corporate airplanes. Gathers data from variety of sources, including International Civil Aviation Board, National Transportation Safety Board, and Civil Aviation Authority; magazines, such as Air Safety Week and Aviation Week and Space Technology; books; and prominent individuals in aviation safety industry. Compiles source data into a Microsoft Access database. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -39

Aviation Safety Network (cont’d) Core table over 10, 000 rows of data concerning incident and accident descriptions, and linked to tables that store data about airports, airlines, aircraft types, countries, etc. Data reformatted and exported to a My. SQL database. Database used by programs that support queries on ASN’s website (http: //aviation-safety. net/database). Nearly 10, 000 email subscribers in 170 countries; website receives over 50, 000 visits per week. (http: //aviation-saftey. net/about) Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall 5 -40

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall