using mis 3e chapter 5

7/9/2010

1

Database Processing

David Kroenke

Using MIS 3e

Chapter 5

Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall 5-2

• 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 Dell and Amazon.com use databases to support complex sales,

marketing, and operations activities. In between, we have businesses like FlexTime that use

databases as a crucial part of their operations, but they don’t have a trained and experienced

staff to manage and support the databases. To obtain answers to the one-of-a-kind queries he

needs, Neil needs to be creative and adaptable in the way that he accesses and uses his

database.

• 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 depends 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 their functions. You will learn about the use of database reporting and data

mining in Chapter 9.

Chapter Preview

7/9/2010

2


Q1 What is the purpose of a database?

Q2 What is a database?

Q3 What are the components of a database application system?

Q4 How do database applications make databases more useful?

Q5 How are data models used for database development?

Q6 How is a data model transformed into a database design?

Q7 What is the users’ role in the development of databases?

Q8 2020?

Study Questions


• Purpose: to keep track of things

• If structure of a list is simple, i.e., one theme,

no need to use database technology (video)

What Is the Purpose of a

Database?

http://media.pearsoncmg.com/ph/bp/bp_akamai/kroenke/videos.php?title=Chapter%205-%20What%20is%20the%20purpose%20of%20a%20database?&clip=pandc/kroenke/Ch_05_1.flv

7/9/2010

3


Form for Recording Multiple

Themes


• Lists of data involving a single theme can be

stored in a spreadsheet.

• Lists that involve data with multiple themes

require a database.

General Rule

7/9/2010

4









Q8 2020?

Study Questions


• Database:

A self-describing collection of integrated

records

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.

Database

7/9/2010

5


Characters, Fields, and Records


Hierarchy of Data Elements

7/9/2010

6


Metadata Describes Structure of

Database

Components of a Database


What Are Relationships Among

Rows?

First row of the Email Table is

related to Andrea Baker in

Student Table

Last row in Office_Visit Table

related to Adam Verberra in

Student Table

7/9/2010

7


• Key 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.

Every table must have a key.

Sometimes more than one column is needed to form a

unique identifier. In a table called City, for example, the key

would consist of combination of columns (City, State).

Email_Num is the key of Email Table.

VisitID is the key of Office_Visit Table.

Relationship Special Terms


• Foreign keys

These are keys of a different (foreign) table

than the table in which they reside.

• Relational databases

Relationships among tables are created by

using foreign keys.

• Relation

Formal name for a table

Relationship Special Terms

7/9/2010

8


• Database:

A database is a

self-describing

collection of

integrated

records.

• Metadata

Data that

describe data

Metadata




Q3 What are the components of a database

application system?





Q8 2020?

Study Questions

7/9/2010

9


• Applications make database data more accessible and useful.

• Users employ a database application that consists of forms,

formatted reports, queries, and application programs.

• Database management system (DBMS) processes database

tables for applications.

Components of a Database

Application System


What Is a Database Management

System (DBMS)?• DBMS

A program (software) used to create, process, and administer

a database

• Companies license DBMS products from vendors:

IBM, Microsoft, Oracle, and others

• Popular DBMS products are:

DB2 from IBM

Access and SQL Server from Microsoft

Oracle from the Oracle Corporation

MySQL—an open-source DBMS product that is license-free

for most applications

7/9/2010

10


• Database developers use the DBMS to create and modify tables,

relationships, and other structures in the database.

• Below, the developer has added a new column called

Response?. This new column has data type Yes/No.

Creating the Database and Its

Structures


• DBMS operations

Read, insert, modify, delete data

Applications call DBMS in different ways

• 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 an application program, the program calls

the DBMS directly to make the change.

Processing the Database

7/9/2010

11


• SQL—“see-quell”

• International standard language for creating

databases and database structures, and processing

databases

• All five of the most popular DBMS products accept

and process SQL.

• 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);

Structured Query Language (SQL)


• DBMS provides tools to assist in administration of the

database.

• Used to set up a security system involving user

accounts, passwords, permissions, and limits for

processing the database

• Backing up database data, adding structures to

improve performance of database applications,

removing data no longer wanted or needed, and

similar tasks

• Most organizations dedicate one or more employees

to the role of database administration.

Administering the Database

http://en.wikipedia.org/wiki/SQL

http://en.wikipedia.org/wiki/DBMS

7/9/2010

12


Major Responsibilities of

Database Administration





Q4 How do database applications make

databases more useful?




Q8 2020?

Study Questions

7/9/2010

13


Database Applications at

FlexTime


What Are Forms, Reports, and

Queries?

Reports show data in a

structured context.

7/9/2010

14


What Are Forms, Reports, and

Queries? Sample query

form used

to enter

phrase for

search

Sample query results of query operation


• Forms, reports, and queries work well for standard

functions. However, most applications have unique

requirements that a simple form, report, or query

cannot meet.

• Application programs process logic that is specific to

a given business need.

• Application programs serve as an intermediary

between the Web server and database.

Responds to events, such as when a user presses

a submit button; also reads, inserts, modifies, and

deletes database data

Why Are Database Application

Programs Needed?

7/9/2010

15


Four Database Application

Programs Running on a Web

Server Computer


• Lost-update problem Process A reads a customer record from a file containing account

information, including the customer’s account balance and phone

number.

Process B now reads the same record from the same file so it has

its own copy.

Process A changes the account balance in its copy of the customer

record and writes the record back to the file.

Process B—which still has the original stale value for the account

balance in its copy of the customer record—updates the customer’s

phone number and writes the customer record back to the file.

Process B has now written its stale account balance value to the file,

causing the changes made by process A to be lost.

(Source: http://en.wikipedia.org/wiki/File_locking)

Multi-User Processing Problem

http://dba.fyicenter.com/Interview-Questions/RDBMS-FUNDAMENTALS/RDBMS_FUNDAMENTALS_Lost_Update_Problem.html



7/9/2010

16


• Enterprise DBMS

Process large organizational and workgroup databases

Support many, possibly thousands, of users and many different

database applications

Support 24/7 operations and can manage databases that span

dozens of different magnetic disks with hundreds of gigabytes or

more of data

IBM’s DB2, Microsoft’s SQL Server, and Oracle’s Oracle are

examples of enterprise DBMS products.

• Personal DBMS

Designed for smaller, simpler database applications

Used for personal or small workgroup applications that

involve fewer than 100 users (normally fewer than 15), single

user

Enterprise DBMS vs. Personal

DBMS


Access: A DBMS and an

Application Development ProductBefore building a database, developers construct a logical

representation of database data called a data model to describe the

data and relationships to be stored in database.

7/9/2010

17






Q5 How are data models used for database

development?



Q8 2020?

Study Questions


Database Development Process

(video link needed)

7/9/2010

18


• Entity-relationship (E-R) data model

A tool for constructing data models

Developers use it to describe the content of a data

model by defining entities that will be stored in

database and relationships among those entities

Unified Modeling Language (UML), less popular,

tool for data modeling

What Is the Entity-Relationship

Data Model?


• Some thing that the users want to track

• Examples of entities:

Order, Customer, Salesperson, and Item. Some entities represent a

physical object, such as Item or Salesperson; others represent a

logical construct or transaction, such as Order or Contract.

Entity names are always singular.

• Attributes

Describe characteristics of an entity.

Examples: order attributes are OrderNumber, OrderDate, SubTotal,

Tax, Total, and so forth.

• Identifier

An attribute (or group of attributes) whose value is associated with

one and only one entity instance.

Entities

http://en.wikipedia.org/wiki/Entity-relationship_model





http://en.wikipedia.org/wiki/Unified_Modeling_Language

http://en.wikipedia.org/wiki/Unified_Modeling_Language

7/9/2010

19


Student Data Model Entities


Entities with Relationships

7/9/2010

20


Sample Relationship (Version 1)

Crow’s

Feet

1:N N:M

N:M = many-to-many

relationships

One adviser can have many

students and one student can

have many advisers.

1:N = many-to-many

relationships

One department can have

many advisers, but an adviser

has at most one department.


Sample Relationships (Version 2)

Advisers may advise in more than one department, but a

student may have only one adviser, representing a policy that

students may not have multiple majors.

7/9/2010

21


Crow’s-Foot Diagram Version

Maximum cardinality—maximum number of entities that can be

involved in a relationship. Vertical bar on a line means that at least one

entity of that type is required.

Minimum cardinality—minimum number of entities that can be involved

in a relationship. Small oval means that the entity is optional; the

relationship need not have an entity of that type.







Q6 How is a data model transformed into a

database design?


Q8 2020?

Study Questions

7/9/2010

22


• Database design is the process of converting a data

model into tables, relationships, and data

constraints.

• Database design team transforms entities into tables

and expresses relationships by defining foreign keys.

• Two important database design concepts:

normalization and the representation of two kinds

of relationships.

• Normalization is a foundation of database design.

• Representation of relationships will help you

understand important design considerations.

Database Design


Normalization

Normalization is the process of converting a poorly structured table into

two or more well-structured tables. Problem with these tables, have two

independent themes: employees and departments.

http://dba.fyicenter.com/Interview-Questions/Data-Warehousing/What_is_Normalization_First_Normal_Form_Second.html

7/9/2010

23


• In previous figure, some rows show Dept. 100 is “Accounting and

Finance” and others show Dept. 100 is “Accounting.” Which one

is correct?

• A table with data integrity problems will produce incorrect results

and inconsistent information.

• Data integrity problems happen when data are duplicated.

• Users will lose confidence in the information, and system will

develop a poor reputation. Information systems with poor

reputations become serious burdens to the organizations that

use them.

Data Integrity Problems


Normalizing for Data Integrity

• Normalized tables eliminate data duplication,

but they can be slower to process.

• General goal of normalization is to construct

tables such that every table has a single topic

or theme.

http://en.wikipedia.org/wiki/Data_integrity

http://en.wikipedia.org/wiki/Database_normalization

7/9/2010

24


• The way to correct the problem is to split the table into two

tables, each with its own theme.

Normalizing for Data Integrity


• Database practitioners classify tables into various

normal forms according to the kinds of problems they

have.

• Transforming a table into a normal form to remove

duplicated data and other problems is called

normalizing the table.

• Normalization is just one criterion for evaluating

database designs. Normalized designs can be slower

to process, database designers sometimes choose to

accept nonnormalized tables. The best design

depends on the users’ processing requirements.

Summary of Normalization

http://en.wikipedia.org/wiki/Normal_forms

http://en.wikipedia.org/wiki/Database_normalization

7/9/2010

25


Representing Relationships


Representing

a 1:N

Relationship

7/9/2010

26


Representing an N:M

Relationship








Q7 What is the users’ role in the

development of databases?

Q8 2020?

Study Questions

7/9/2010

27


• Users are the final judges of:

What data the database should contain,

How tables should be related.

• Users review data model to be sure it accurately

reflects users’ view of the business.

Mistakes will come back to haunt you.

• Easiest time to change database structure is during

data modeling stage. Changing a relationship from

one-to-many to many-to-many in a data model is

simply a matter of changing the 1:N notation to N:M.

• User review of the data model is crucial.

Users’ Role









Q8 2020?

Study Questions

7/9/2010

28


• Acxiom obtain data from public and private sources and store

and process it in sophisticated ways.

• Use your grocery store club card, that data is sold to a data

aggregator. Credit card data, credit data, public tax records,

insurance records, product warrantee card data, voter

registration data, and hundreds of other types of data are sold to

aggregators.

• Using a combination of phone number, address, email address,

name, and other partially identifying data, such companies can

integrate that disparate data into an integrated, coherent whole

to form detailed descriptions about companies, communities, zip

codes, households, and individuals.

Data Aggregators


• Laws that limit the data that federal and other

governmental agencies can acquire and store.

• Some legal safeguards on data maintained by credit

bureaus and medical facilities.

• No such laws that limit data storage by most

companies (nor are there laws that prohibit

governmental agencies from buying results from

companies like Acxiom.

Data Aggregators

7/9/2010

29


• Absent any public outcry for legislation to limit such

activity, aggregator data storage will continue to grow

exponentially and companies will have even more

data about you, the state of your health, your wealth,

your purchase habits, your family, your travel, your

driving record, and, well, anything you do.

• Query, reporting, and data mining technology will

improve and Moore’s law will make computer

operations that are too slow to be practical today,

feasible tomorrow.

• The picture of you will become more and more

detailed.

How Will this Change by 2020?


• Data could be stolen and used for criminal

activity against you.

• Data might not be accurate.

• No organization is required by law to tell you

the data that it stores about you and what it

does with it.

Why Do You Care?

7/9/2010

30


• You enroll in a “healthy eaters” medical

insurance program, similar to “safe drivers”

auto insurance. Your premiums are lower

because you eat well, except that the

insurance company notes from last month’s

data that you bought four large packages of

Cheetos, and your health insurance premium

is increased, automatically. You have no idea

why.

What If…


• 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

Ethics Guide: Nobody Said I

Shouldn’t

7/9/2010

31


• 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 the anomaly better?

• Does Kelly have any legal recourse over

being fired?

• How can a business protect its databases

from unauthorized use?

Ethics Guide: Nobody Said I

Shouldn’t


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

Guide: No, Thanks, I’ll Use a

Spreadsheet

7/9/2010

32


• What we perceive as reality is based on our

perceptive apparatus. That which we perceive

he called phenomena.

• Our perceptions, such as of light and sound,

are processed by our brains and made

meaningful.

• But we do not and cannot know whether the

images we create from the perceptions have

anything to do with what might or might not

really be.

Guide: Immanuel Kant, Data

Modeler


• Noumenal world refers to the essence of “things in

themselves”—to whatever it is out there that gives rise

to our perceptions and images.

• Phenomenal world refers to what we humans

perceive and construct.

• Easy to confuse the noumenal world with the

phenomenal world, because we share the

phenomenal world with other humans.

• Shared mutual view does not mean that the mutual

view describes the “real world.”


Modeler

http://www.philosophers.co.uk/cafe/phil_jul2002.htm

7/9/2010

33


• Nothing that humans can do represents the real, noumenal

world. A data model, therefore, is a model of a human’s

model of what appears to be “out there.”

• Data models do not model “the real world.” A data model is

simply a model of what the data modeler perceives.

• Must ask the question, “How well does the data model fit

the mental models of the people who are going to use the

system.”

• Only valid point is whether it reflects how users view their

world. Will it enable the users to do their jobs?


Modeler









Q8 2020?

Active Review

7/9/2010

34


• PC-Magazine test compared five DBMS products:

DB2 (from IBM)

MySQL (a free, open source DBMS)

Oracle (from Oracle Corporation)

SQL Server (from Microsoft)

ASE (from Sybase Corporation)

• SQL Server’s performance was worst performer.

• Microsoft complained, so PC-Magazine reran the test using Microsoft-

supporting software. SQL server performed best this time.

• However, the second test compares apples and oranges.

• Do benchmarks really mean anything? How should customers use

benchmarks?

Case Study 5: Benchmarking,

Bench Marketing, or Bench

Baloney


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

using mis 3e chapter 5

Documents