Transcript database

David Kroenke
Database Processing
Chapter 4
© 2007 Prentice Hall, Inc.
1

Know the purpose of database processing.

List the components of a database system.

Understand important database terms.

Know the elements of the entityrelationship model.
© 2007 Prentice Hall, Inc.
2


Understand the general nature of database
design.
Recognize the need for and know the basic
tasks of database administration .
© 2007 Prentice Hall, Inc.
3

The purpose of a database is to keep
track of things that involve more than
one theme.
© 2007 Prentice Hall, Inc.
4
© 2007 Prentice Hall, Inc.
5
© 2007 Prentice Hall, Inc.
6




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.
7




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.
8
© 2007 Prentice Hall, Inc.
9
© 2007 Prentice Hall, Inc.
10

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.
11
© 2007 Prentice Hall, Inc.
12
© 2007 Prentice Hall, Inc.
13





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.
14



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.
15
© 2007 Prentice Hall, Inc.
16



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.
17


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.
18
© 2007 Prentice Hall, Inc.
19



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.
20

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.
21




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.
22
© 2007 Prentice Hall, Inc.
23



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.
24



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.
25


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.
26



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.
27
© 2007 Prentice Hall, Inc.
28


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.
29
© 2007 Prentice Hall, Inc.
30
© 2007 Prentice Hall, Inc.
31


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.
32
© 2007 Prentice Hall, Inc.
33

Multi-user processing is common, but it does
pose unique problems that you, as a future
manager, should know about .
© 2007 Prentice Hall, Inc.
34

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.
35



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 multiuser processing.
© 2007 Prentice Hall, Inc.
36

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.
37
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.
38
© 2007 Prentice Hall, Inc.
39



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.
40


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.
41
© 2007 Prentice Hall, Inc.
42

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
soInc. forth
© 2007
Prentice Hall,
43

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.
44
© 2007 Prentice Hall, Inc.
45




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.
46


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-tomany.
© 2007 Prentice Hall, Inc.
47


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.
48
© 2007 Prentice Hall, Inc.
49
© 2007 Prentice Hall, Inc.
50
© 2007 Prentice Hall, Inc.
51
© 2007 Prentice Hall, Inc.
52


Database design is the process of converting a
data model into tables, relationships, and data
constraints.
The database design team transforms entities
into tables and expresses relationships by
defining foreign keys.
© 2007 Prentice Hall, Inc.
53

Normalization is the process of converting
poorly structured tables into two or more wellstructured tables.
Data Integrity Problems
 For example, suppose for a given table, the value for a
defined attribute is changed correctly in two rows, but
not in the third
◦ The table has what is called a data integrity problem:
 Some rows indicate that the name of the attribute is one
value and another row indicates that the name of the value
of the attribute is another. This is called multi-value.
◦ Data integrity problems are serious.
© 2007 Prentice Hall, Inc.
54
Data Integrity Problems (Continued)
◦ A table that has data integrity problems will produce
incorrect and inconsistent information.
◦ Users will lose confidence in the information, and the
system will develop a poor reputation.
◦ Information systems with poor reputations become
serious burdens to the organizations that use them.
© 2007 Prentice Hall, Inc.
55
© 2007 Prentice Hall, Inc.
56
© 2007 Prentice Hall, Inc.
57
Normalizing for Data Integrity
◦ The data integrity problem can occur only if data are
duplicated.
◦ Because of this, one easy way to eliminate the
problem is to eliminate the duplicated data.
◦ This can be done by transforming the table into two
tables.
◦ Because such joining of tables is common, DBMS
products have been programmed to perform it
efficiently, but it still requires work.
◦ Dealing with such trade-offs is an important
consideration in database design.
◦ The general goal of normalization is to construct
tables such that every table has a single topic or
theme.
© 2007 Prentice Hall, Inc.
58
Normalizing for Data Integrity (continued)
◦ 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.
© 2007 Prentice Hall, Inc.
59
© 2007 Prentice Hall, Inc.
60
© 2007 Prentice Hall, Inc.
61
© 2007 Prentice Hall, Inc.
62
© 2007 Prentice Hall, Inc.
63
© 2007 Prentice Hall, Inc.
64



Users are the final judges as to what data the
database should contain and how records in the
database should be related to one another.
The easiest time to change the database
structure is during the data modeling stage.
However, once the database has been
constructed, loaded with data, and application
forms, reports, queries, and application
programs created, changing a relationship means
weeks of work.
© 2007 Prentice Hall, Inc.
65




When a database is developed for your use, you
must carefully review the data model.
If you do not understand any aspect of it, you
should ask for clarification until you do.
The data model must accurately reflect your view
of the business.
Do not proceed unless the data model is correct.
© 2007 Prentice Hall, Inc.
66



In light of both the importance and the
management challenges of databases, most
organizations have created a staff function called
database administration.
In smaller organizations, this function is usually
served by a single person, sometimes even on a
part-time basis.
Larger organizations assign several people to an
office of database administration.
© 2007 Prentice Hall, Inc.
67

Depending on the context, the letters DBA either
stand for the database administrator or for the
office of database administration.


The purpose of database administration is to
manage the development, operation, and
maintenance of a database so as to achieve the
organization’s objectives.
This function requires balancing conflicting
goals: protecting the database while maximizing
its availability for authorized use.
© 2007 Prentice Hall, Inc.
68
© 2007 Prentice Hall, Inc.
69




The DBA is not a user of the database or any of
its applications.
The DBA is an auditor, a consultant, sometimes a
policeman, and a diplomat who works as a
liaison between the users and professional
developers.
One of the first tasks for the DBA is to create a
steering committee that consists of key users.
The DBA uses the committee as a forum for
community-wide decisions regarding the
development, use, and maintenance of the
database.
© 2007 Prentice Hall, Inc.
70


An important DBA function is to establish
community-wide policies for the processing of
the database.
The DBA uses the steering committee to
determine processing rights for each column of
each table.
◦ These rights include what data users are authorized to
read, create, modify, and delete.
◦ The DBA also works with development personnel to
ensure that a security system is in place to enforce
these processing rights.
© 2007 Prentice Hall, Inc.
71



As a protector of the database, the DBA has the
responsibility to ensure that appropriate
procedures and policies exist for backing up the
database and that those procedures are followed.
The DBA needs to ensure that users and
operations personnel are appropriately trained
with regard to backup and recovery procedures.
Finally when failures occur, in many
organizations the DBA is responsible for
managing the recovery process.
© 2007 Prentice Hall, Inc.
72




Over time, requirements for the database will
change.
Changes that benefit one group in the
organization may not benefit other groups.
The DBA needs to set up a system for recording
and tracking requests for changes.
The responsibility of the DBA is to provide the
forum and to ensure that requests are
considered and acted upon in a responsible
manner.
© 2007 Prentice Hall, Inc.
73


The DBA function has broad managerial
responsibilities for the database.
Part of the DBA function is technical:
◦ Monitoring performance
◦ Managing the DBMS
◦ Developing backup and recovery procedures

For larger organizations, and for databases that
touch many different departments and business
functions, the DBA’s job is more diplomatic than
technical.
© 2007 Prentice Hall, Inc.
74





The purpose of a database is to keep track of
things.
A database is a self-describing collection of
integrated records.
With the relational model, data are stored in
tables and relationships are represented by
column values.
A key, or table key, is a column or group of
columns that uniquely identifies a row.
A foreign key is a column or group of
columns in one table that identifies a row in
a second table.
© 2007 Prentice Hall, Inc.
75





Metadata are data that describe data.
A database application system includes the
database, the database management system
(DBMS), and database applications.
The DBMS is a program used to create,
process, and administer a database.
Structured Query Language (SQL) is an
international standard for defining and
processing database data.
A database application consists of forms,
reports, queries, and application programs.
© 2007 Prentice Hall, Inc.
76





A data model is a logical representation of a
database that describes data and
relationships.
An entity is something that users want to
track.
Entities have relationships to one another.
Database design is the process of converting
a data model into tables, relationships, and
data construction.
Normalization is the process of converting
poorly structured tables into tables that are
well structured.
© 2007 Prentice Hall, Inc.
77
Access
Attribute
Byte
Column
Crow’s foot
Crow’s-foot diagram
version
Data integrity problem
Data model
Database
Database administration
Database application
system
Database management system
(DBMS)
DBA
DB2
Enterprise DBMS
Entity
Entity-relationship (E-R) data
model
Entity-relationship (E-R)
diagram
Field
File
Firewall
© 2007 Prentice Hall, Inc.
78
Foreign key
Form
Identifier
Key
Lost-update problem
Maximum cardinality
Metadata
Minimum cardinality
Multi-user processing
MySQL
Object-relational database
1:N(one-to-many)
relationship
N:M (many-to-many)
relationship
Normal forms
Normalization
Oracle
Query
Personal DBMS
Record
Relation
Relational database
Relationship
Report
© 2007 Prentice Hall, Inc.
79
Row
SQL Server
Structured Query Language
(SQL)
Table
Unified Modeling Language
(UML)
User account
User role
© 2007 Prentice Hall, Inc.
80
Kelly make a backup copy of his company’s
database on CD and took it home and installed it
on his home computer.
While performing queries on the database, he
found customer order information and focused in
on his friend Jason’s customer order entries.
© 2007 Prentice Hall, Inc.
81
Ethics Guide–Nobody Said I Shouldn’t (Continued)
Kelly noticed that Jason gave huge discounts to
Valley Appliances but not to his other customers.
At an after work gathering, Kelly asked Jason
about the Valley Appliances discount telling him
what he had been doing.
When Kelly returned to work, he was fired.
© 2007 Prentice Hall, Inc.
82
The firewall, a computing device located between a
firm’s internal network and external networks,
prevents unauthorized access to the internal
network.
For the best security, the DBMS computer should
be protected by a firewall, and then all other
security measures should be designed as if the
firewall has been breached.
© 2007 Prentice Hall, Inc.
83
All operating systems and DBMS patches should
be installed as soon as they become available.
To prevent unauthorized access, no one other than
authorized operations personnel should be able to
directly access the computer that runs the DBMS.

Instead, all access should be via authorized applications
programs
The computer running the DBMS should be
secured behind locked doors, and visits to that
room should be recorded in a log.
© 2007 Prentice Hall, Inc.
84
All major DBMS products have extensive, built-in
security features.




These features allow for the definition of user accounts
and user roles.
Each user account belongs to a specific person.
A role is a generic employee function, such as payroll
clerk or field salesperson.
Once an account is defined, it can be assigned specific
permissions, and it can also be assigned particular roles.
Most DBMS products log failed attempts to sign on
and produce other usage reports as well.
© 2007 Prentice Hall, Inc.
85
The database administrator (DBA) should
periodically monitor such logs and reports for
suspicious activity.
It is important to have a plan of action for security
emergencies.
The steps to be taken vary from database to
database.
© 2007 Prentice Hall, Inc.
86
Only the users can say whether a data model
accurately reflects their business environment.
What happens when the user’s disagree among
themselves?


It’s tempting to say, “The correct model is the one that
better represents the real world.” The problem with this
statement is that data models do not model “the real
world.”
A data model is simply a model of what the data
modeler perceives.
© 2007 Prentice Hall, Inc.
87
What happens when the user’s disagree among
themselves? (continued)

This very important point can be difficult to understand;
but if you understand it, you will save many hours in
data modeling validation meetings and be a much better
data modeling member.
Nothing that humans can do represent the real
world.


A data model, therefore, is a model of a human’s model
of what appears to be “out there.”
For example, a model of a salesperson is a model of the
model that humans make of salespeople.
© 2007 Prentice Hall, Inc.
88
What do we do when people disagree about what
should be in a data model?



First, we realize that anyone attempting to justify her
data model as a better representation of the real world
is saying, quite arrogantly, “The way I think of the world
is the way that counts.”
Second, in times of disagreement we must ask the
question, “How well does the data model fit the mental
models of the people who are going to use the system?”
The only valid point is whether it reflects how the users
view their world.
 Will it enable the users to do their jobs?
© 2007 Prentice Hall, Inc.
89
I’m not buying all this stuff about databases.






“I’ve tried them and they’re a pain-way too complicated to
set up, and most of the time, a spreadsheet works just as
well.”
“No, unless you are a General Motors or Toyota, I
wouldn’t mess with a database.”
“You have to have professional IS people to create it and
keep it running.”
“Besides, I don’t really want to share my data with
anyone.”
“I work pretty hard to develop my client list.”
“Why would I want to give it away?”
© 2007 Prentice Hall, Inc.
90
I’m not buying all this stuff about databases
(continued)




“When I want something, I use Excel’s Data Filter.”
“I can usually get what I need.”
“Of course, I can’t still send form letters, but it really
doesn’t matter.”
“I get most of my sales using the phone, anyway.”
© 2007 Prentice Hall, Inc.
91
Changing requirements is the biggest challenge for
creating and managing databases and database
applications.



The development team just finishes the order entry
database and applications when a user asks, innocently
enough, “Where do I enter the second salesperson?”
The development team responds “This is the first time
I’ve heard of it. Why didn’t someone tell me this before?”
Of course, the best way to solve this problem is not to
have it in the first place; it would be better to learn of the
need for multiple salesperson’s names long before the
system is created.
© 2007 Prentice Hall, Inc.
92
It is very important for user involvement in both
requirements specification and data model
validation.
Unfortunately, however, not all change requests are
preventable.

Some occur only after a period of system use.
An information system enables its users to behave
in new ways, and as they behave in new ways, they
think of new requirements for the system.
© 2007 Prentice Hall, Inc.
93
As the system is adapted to add new features, the
users again will be able to behave in new ways,
and they will then think of yet additional features
and functions.
The bottom line is there will always be new
requirements for an information system.
The users and the development team must specify
all the requirements that they know about and
validate the data model as best they can.
© 2007 Prentice Hall, Inc.
94