Transcript Chapter 5 – Storing and Organizing Information

Chapter 5 – Storing and
Organizing Information
Information Systems, First Edition
John Wiley & Sons, Inc
by France Belanger and Craig Van Slyke
Contributor: Brian West, University of Louisiana at
Lafayette
Copyright 2012 John Wiley & Sons, Inc.
5-1
Learning Objectives
• Decide whether it is better to store data using a
database management system or a spreadsheet.
• Discuss the purpose of a database management system
• Explain the basic structure and components of
relational databases
• Describe the purpose of foreign keys in a relational
database
• Discuss the purpose of a relational database schema
and explain its notation
• List and describe a number of online databases
Copyright 2012 John Wiley & Sons, Inc.
5-2
The database behind Facebook
Odds are you are one of the over 500 million active users
of Facebook.
Keeping track of all of this information requires a very
complex database design, in addition to a robust
infrastructure. Facebook uses a variety of tools to create
and manage their data.
• Almost 1 billion objects, such as pages, groups, events
and communities
• Over thirty billion pieces of content, including links,
posts, photos, notes, videos and new stories.
• Friend connections among the over 500 million active
users. The average user has well over 100 friends.
Copyright 2012 John Wiley & Sons, Inc.
5-3
Focusing Questions
• Identify the information elements that are
part of a Facebook profile.
• What information do you think Facebook uses
to determine what friend suggestions to
make?
Copyright 2012 John Wiley & Sons, Inc.
5-4
Data for an Amazon order
Suppose you are ordering something from
Amazon. What pieces of data does Amazon
need to carry out your order.
• For example, Amazon would need your name.
• What other data elements does Amazon
need?
Copyright 2012 John Wiley & Sons, Inc.
5-5
Databases vs. spreadsheets
Spreadsheets are fine for very simple data storage
tasks, such as keeping simple lists, like a personal
contact list or a home inventory, but , spreadsheets
have a number of limitations.
•
•
•
•
•
Copyright 2012 John Wiley & Sons, Inc.
5-6
Spreadsheets
Strengths
•
Weaknesses
•
• Relatively easy to use
• Require less planning
•
• Formulas could be incorrect
• Out of date version
• Out of date data
Copyright 2012 John Wiley & Sons, Inc.
5-7
Databases
Strengths
•
• Data is of higher quality
than data stored in
spreadsheets
•
Weaknesses
• Requires more planning and
designing
• More complicated
Copyright 2012 John Wiley & Sons, Inc.
5-8
Databases and Database Management
Systems
A ____________ is an organized collection of data.
• These databases can store different types of
“information” including text, numbers,
documents, images and videos.
Databases are managed by
__________________________ (DBMS).
• A database management system provides the
means for creating, maintaining and using
databases.
Copyright 2012 John Wiley & Sons, Inc.
5-9
Database Management Systems
(DBMS)
• Professional database designers and
administrators handle the creation and
maintenance tasks in most organizations.
• Smaller database-oriented tasks can be
handled by personal DBMS, such as Microsoft
Access
• Larger, more complex databases require
enterprise-level DBMS, such as Oracle,
MySQL, Microsoft SQL Server, and IBM’s DB2
Copyright 2012 John Wiley & Sons, Inc.
5-10
Databases
• Databases are integral elements of
information systems.
• Most information systems today use multitiered architectures that divide processing into
different elements.
• Applications handle the processing of data,
while the DBMS is responsible for managing
the data.
Copyright 2012 John Wiley & Sons, Inc.
5-11
Multi-tiered Architecture
Figure 5.1 – Interaction between Applications and Databases
Copyright 2012 John Wiley & Sons, Inc.
5-12
Relational Databases
• A _________________ stores data in the form
of connected tables. Tables are made up of
records (rows) and fields (columns).
• A ____________ is a set of fields that all
pertain to the same thing, while the fields
represent some characteristic of the thing.
• A _____________ represents some
characteristic of the thing
Copyright 2012 John Wiley & Sons, Inc.
5-13
Relational Databases
InstructorID
LastName
FirstName
Email
1
Smith
Sadie
[email protected]
2
Jones
Maggie
[email protected]
3
Thurman
Annie
[email protected]
4
Wilson
Fred
[email protected]
Figure 5.2 – Instructor Table
Copyright 2012 John Wiley & Sons, Inc.
5-14
Primary Key
In order for database to work, we need some
way to uniquely identify each record.
• This unique identifier is called the
____________ .
• Each table in a database has a primary key.
• Sometimes a primary key is made up of more
than one field, called a composite primary key.
Copyright 2012 John Wiley & Sons, Inc.
5-15
Foreign Keys
Connections are implemented by foreign keys
• _______________ are fields that reference a
primary key in a related table.
• This cross-referencing is called a relationship.
• Cross-referencing foreign keys make it easy to
combine data contained in multiple tables.
Copyright 2012 John Wiley & Sons, Inc.
5-16
Related Tables
Figure 5.3 – A small Database
Copyright 2012 John Wiley & Sons, Inc.
5-17
Normal Forms
There are some rules that govern relational
database design called “normal forms”
• These rules are in place to ensure data
consistency by eliminating unnecessary
redundancy.
• A particular row in a table can be related to at
most one row in a related table.
• For example, in most businesses a specific
order can only be related to one customer.
Copyright 2012 John Wiley & Sons, Inc.
5-18
Normal Forms
SectionID
CourseID
1001
IT101
Introduction to IT
1
Smith
Sadie
IT101
Introduction to
Computers
3
Thurman
Maggie
1003
IT320
Database
Administration
1
Jackson
Sadie
1004
IT400
Systems Analysis
4
Wilson
Fred
1005
IT600
IT Management
4
Wilson
Fred
1002
CourseTitle
InstructorID
LastName FirstName
Figure 5.4 – Redundancy Example
Copyright 2012 John Wiley & Sons, Inc.
5-19
Normal Forms
• What is Instructor #1’s last name?
• This instructor’s last name is “Smith” in the
first row, and Jackson in the third row.
• This inconsistency, which we call an anomaly,
results from storing the instructor’s name in
multiple rows.
• If you only store a data element in one place,
you cannot have inconsistent values.
Copyright 2012 John Wiley & Sons, Inc.
5-20
Relationships
• Many-to-many relationships also exist
• In many-to-many relationships a specific row
can be related to multiple rows in a related
table.
• In contrast to the one-to-many relationship,
this is true in both directions.
• Many-to-many relationships require creating a
new table that links the two related tables.
• These are called linking or intersection tables.
Copyright 2012 John Wiley & Sons, Inc.
5-21
Linking Tables
Figure 5.5 – Many-to-many, linking table example
Copyright 2012 John Wiley & Sons, Inc.
5-22
Relationships
• Although they are more unusual, one-to-one
relationships also exist.
• These relationships a specific row in a table
can be related to at most one row in a related
table
• This is true in both directions of the
relationship
Copyright 2012 John Wiley & Sons, Inc.
5-23
Database Diagrams
As you might imagine, using the actual data to
show the structure of a database only works for
very small databases. For larger databases, we
illustrate structure using database schema
diagrams.
•
•
Copyright 2012 John Wiley & Sons, Inc.
5-24
Database Diagrams
Figure 5.6 – Database Diagram
Copyright 2012 John Wiley & Sons, Inc.
5-25
Finding business databases online
Your campus library likely has a variety of
business-related online databases. . Identify and
briefly describe one database for each of the
related purposes.
• Economic statistics
• Industry information
• Demographic statistics
• Business-related news articles.
Copyright 2012 John Wiley & Sons, Inc.
5-26
Online databases
We have primarily discussed relational databases
that store text and numeric data related to business
operations.
• Online databases that are quite useful for
performing research
• Databases serve a different purpose than the
databases we discussed earlier
• These online databases contain pointers to
sources of information.
• Most of these resources are available through
libraries.
Copyright 2012 John Wiley & Sons, Inc.
5-27
Online databases
• Career research
– The United States Bureau of Labor Statistics
(http://www.bls.gov/data/)
• Preparing for a job interview
– Article databases such as ABI/Inform and LexisNexis
Academic are useful for finding news articles about
the company and its executives
• Gathering information on customers or
competitors
– Hoovers.com provides extensive information about
companies and industries.
Copyright 2012 John Wiley & Sons, Inc.
5-28
Summary
• Spreadsheets are good for storing simple lists of
information.
• Database management systems provides the
means for creating, maintaining and using
databases.
• A relational database stores data in the form of
connected tables. Tables are made up of records
and fields.
• In a relational database a record is a set of fields
that all pertain to the same thing, while the fields
represent some characteristic of the thing.
Copyright 2012 John Wiley & Sons, Inc.
5-29
Summary
• In a relational database, foreign keys are fields
that reference the primary keys in related tables.
• A database diagram shows the logical structure of
a relational database, including its tables, fields
and the relationships among tables.
• The are many online databases that store a vast
array of information. These databases include
article databases, market and economic
databases and databases of demographic and
governmental information, among other topics
Copyright 2012 John Wiley & Sons, Inc.
5-30
Copyright 2012 John Wiley & Sons, Inc.
All rights reserved. Reproduction or translation of this
work beyond that permitted in section 117 of the 1976
United States Copyright Act without express permission
of the copyright owner is unlawful. Request for further
information should be addressed to the Permissions
Department, John Wiley & Sons, Inc. The purchaser may
make back-up copies for his/her own use only and not for
distribution or resale. The Publisher assumes no
responsibility for errors, omissions, or damages caused by
the use of these programs or from the use of the
information herein.
Copyright 2012 John Wiley & Sons, Inc.
5-31