Chapter 5 Database Processing Study Questions Q 1

Chapter 5 Database Processing

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

This Could Happen to You • Dee selected Movable Type application program • Needs database program to store blog – Consultant suggested My. SQL – Network standardized on Oracle • May not be able to run Movable Type • Will require revision of labor estimates – IT resists installing My. SQL • Watch video Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -3

Q 1: What Is the Purpose of a Database? • To organize and keep track of things • Spreadsheets do that too – Keep lists of single concept – Example: Student test scores in a course • Databases – Keep lists that involve multiple themes/concepts – Examples: Student grades, grades for all courses in a department, courses offered in all departments, faculty records, and so on • Watch video Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -4

Q 2: What Does a Database Contain? • Self-describing collection of integrated records • Hierarchy of data elements – Bytes/data are grouped into columns/fields – Columns grouped into rows/records – Rows are grouped into tables/files • Collection of tables plus relationships among rows – Also includes “metadata” • Describes the structure of the database • A database is a structured collection of records or data that is stored in a computer system so that a computer program or person using a query language can consult it to answer queries. Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -5

Student Table (also called a file) Copyright © 2010 Pearson Education, Inc. Publishing as

Relationships Among Records • Values in one table may relate to rows/records in other tables • Keys – Column(s) that identify unique row in table – Each table has a key • Foreign keys – Keys of a different table than the one in which they reside • Relational databases – Databases using tables, keys, and foreign keys Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -7

Example of Relationships Among Rows Figure 5 -6 5 -8

Metadata • Database is self-describing – Contains descriptions of its data • Metadata – Data that describe data – Make databases more useful – Make databases easier to use • Describe data – Data type – Field name – Field properties Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -9

Q 3: What Is a DBMS and What Does It Do? • Database management system (DBMS) – Program that creates, processes, and administers database –Usually licensed from vendors – Examples: Microsoft Access, Oracle, My. SQL, DB 2 • DBMS and database are two different things – Database is a structured collection of records or data that is stored in a computer system so that a computer program or person using a query language can consult it to answer queries. The computer program used to manage and query a database is known as a database management system (DBMS). Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -10

Components of a Database Application System Copyright © 2010 Pearson Education, Inc. Publishing as

Database Management Systems • DBMS used to create tables, relationships in databases • Applications use DBMS to read, insert, modify, and delete data – Structured Query Language (SQL) • International standard for processing database Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -12

Creating the Database and Its Structure Copyright © 2010 Pearson Education, Inc. Publishing as

Processing the Database • Four DBMS operations 1. 2. 3. 4. Read data Insert data Modify data Delete data • Structured Query Language INSERT INTO Student ([Student Number], [Student Name], HW 1, HW 2, Mid. Term) VALUES (1000, ‘Franklin, Benjamin’, 90, 95, 100) Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -14

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

Q 4: What Is a Database Application? • Collection of forms, reports, queries, and application programs that process a database • Databases can have multiple applications • Applications can have multiple users Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -16

Use of Multiple Database Applications Copyright © 2010 Pearson Education, Inc. Publishing as Prentice

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

Database Application Programs • Application programs – Process logic specific to a business need – May enable database processing over Internet to: • Serve as intermediary between Web server and database • Respond to events • Read, insert, modify, delete data Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -19

Example of a Query Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall

Four Application Programs on a Web Server Computer Copyright © 2010 Pearson Education, Inc.

Multiuser Processing Considerations • Lost-update problem – Occurs when an update made by a transaction is lost due to an update made by another transaction. • Locking – Used to ensure that a transaction does not interfere with any other transaction. Locking prevents the problem of lost update, uncommitted data, and inconsistent data. Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -22

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

Personal Database System Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5

How Does the Knowledge in This Chapter Help Dee and You? Helps to: • Know what a DBMS is and what roles it can play • Understand the diagram of the role of the DBMS for her blog – System is isolated from rest of databases – Uses My. SQL as part of functionality of her application • Be better able to explain what she wants, and that it is no threat or exception to Oracle standard • Maybe avoid hiring a consultant Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -25

Role of DBMS for Dee’s Blog Copyright © 2010 Pearson Education, Inc. Publishing as

MIS in Use: How Much Is a Database Worth? • “Take away our customer database, . . . it would take us 8 years to get back. . . ” • Database contains everything customers do • Targeted solicitation – Political candidates raised $500, 000 in a day with accurate targeted solicitation – mybarackobama. com Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -27

Ethics Guide: Nobody Said I Shouldn’t • Kelly was employed maintaining servers and backing up database – Made copy of database to practice with • Queried SQL Server metadata • Discovered tables with order data, customers, salespeople • Uncovered anomalies: one entry clerk gave discounts to a buyer that no other clerks gave discounts to – Mentioned it to a clerk – Was terminated for accessing database Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -28

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

Guide: No, Thanks, I’ll Use a Spreadsheet • Databases take time to build • Complicated to operate – May require use of multiple applications • Need IS people to create it and keep it running • Will share data that you may not want to expose • Spreadsheets may be a better option in some cases Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall 5 -30

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

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 © 2010 Pearson Education, Inc. Publishing as Prentice Hall 32