Transcript Document

Chapter 12
Information Systems
Information Systems
•Spreadsheets
•Databases
12-2
Chapter Goals
•
•
•
•
•
•
Spreadsheets
Spreadsheet “What if” analysis
Spreadsheet formulas
Database Management Systems
Basic SQL statements
Entity-Relationship Diagrams
12-3
Information Systems
• Organization and Storage of Data
– database management systems
• Data Analysis
– electronic spreadsheets
12-4
Different Tools
• Spreadsheets
– Mainly to Analyze Data
• Databases
– Mainly to Store Data
• Sometimes, these separate roles overlap
12-5
Lots of Jobs for Specialists
•
•
•
•
•
Database administrator
Database Designer/Programmer
Data Analyst
Information Systems Analyst
Various IT support, supervision, and
management roles
• Many, many, many other jobs…
12-6
Lots of Jobs for EVERYBODY
• Most jobs you will find yourself using a
database and/or a spreadsheet just to do your
job
• Examples:
– Doctors and Nurses: Accessing patient medical
records
– Grocery Store Clerk: Inventory on store items
12-7
Spreadsheets
12-8
Spreadsheets
• Spreadsheet A software
application that allows the user
to organize and analyze data
using a grid of labeled cells
– A cell can contain data or a
formula that is used to calculate a
value
– Data stored in a cell can be text,
numbers, or “special” data such as
dates
– Spreadsheet cells are referenced
by their row and column
designation
12-9
Common Spreadsheets
• You have your choice of:
• Microsoft Excel
– That’s about it…
12-10
Spreadsheets
• Last week at the tutoring center…
12-11
Spreadsheet Analysis
• Spreadsheets can do what-if analysis
– What if the number of attendees decreased by 10%?
– What if we increase the ticket price by $5?
– What if we could reduce the cost of materials by half?
– What if I skipped the midterm exam?
12-12
Spreadsheet Formulas
• The power of spreadsheets comes from
formulas
– Some cells have input values
– Other cells have formulas that display a calculated
value
– Calculated values are AUTOMATICALLY UPDATED
when input values change
12-13
Spreadsheet Formulas
Figure 12.1 The formulas behind some of the cells
12-14
Spreadsheet Formulas
• Formulas use basic arithmetic operations
+, 2, *, and /
• They also use built-in functions
Sum(A2:A3)
12-15
Spreadsheet Formulas
Figure 12.4 Some common spreadsheet functions
12-16
Spreadsheet Analysis
• Spreadsheets are versatile!
• Spreadsheet analysis can be applied to just about
any topic area
–
–
–
–
–
–
–
Track sales
Analyze sport statistics
Maintain student grades
Keep a car maintenance log
Record and summarize travel expenses
Track project activities and schedules
Plan stock purchases
12-17
Database Systems
12-18
Database Management Systems
• Database A structured set of data
• DataBase Management System (DBMS)
– Physical database File(s) that contain the data
– Database engine Software that accesses the data
– Database schema The structure of the data
12-19
Common Databases
• Microsoft Access
– Popular PC Database
– Good for personal use and for small companies
and organizations
• Oracle
– Popular powerful DBMS for large organizations
• Microsoft SQL Server
– Competition for Oracle
12-20
Database Management Systems
Figure 12.6 The elements of a database management system
12-21
Database Management Systems
• The database schema provides the logical
view of the data in the database
• The Schema defines:
– Tables
– Fields names and datatypes
– Relationships
12-22
The Relational Model
• In a relational DBMS, the data items
and the relationships among them are
organized into tables
– A table is a collection of records
– A record is a collection of related fields
– Each field of a database table contains a single data
value
– Each record in a table contains the same fields
12-23
A Database Table
Figure 12.7 A database table, made up of records and fields
12-24
A Database Table
• We can express the schema for this part of the
database as follows:
Movie (MovieId:key, Title, Genre, Rating)
12-25
Relationships
Figure 12.8 A database table containing
customer data
12-26
Relationships
• We can use a table to represent a collection of
relationships between objects
Figure 12.9 A database table storing current
movie rentals
12-27
Database Design
• Entity Relationship (ER) Diagram Models the
relationships between tables in a graphical
form
12-28
An ER Diagram
• A One-to-Many relationship
Figure 12.10 An ER diagram for the movie rental database
12-29
Structured Query Language
• Structured Query Language (SQL) A
comprehensive database language for
managing relational databases
• SQL allows the user to:
– add, modify, delete, and query data
12-30
Queries in SQL
select attribute-list from table-list where condition
select Title from Movie where Rating = 'PG'
select Name, Address from Customer
select * from Movie where Genre like '%action%'
select * from Movie where Rating = 'R' order by Title
12-31
Modifying Database Content
insert into Customer values (9876, 'John Smith',
'602 Greenbriar Court', '2938 3212 3402
0299')
update Movie set Genre = 'thriller drama' where
title = 'Unbreakable‘
delete * from Movie where Rating = 'R'
12-32