chapter 12 information systems. spreadsheets databases 12-2
TRANSCRIPT
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
12-14Figure 12.1 The formulas behind some of the cells
Spreadsheet Formulas
• Formulas use basic arithmetic operations +, 2, *, and /
• They also use built-in functionsSum(A2:A3)
12-15
Spreadsheet Formulas
12-16Figure 12.4 Some common spreadsheet functions
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
12-21Figure 12.6 The elements of a database management system
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 fields12-23
A Database Table
12-24Figure 12.7 A database table, made up of records and fields
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
12-26
Figure 12.8 A database table containing customer data
Relationships
• We can use a table to represent a collection of relationships between objects
12-27
Figure 12.9 A database table storing current movie rentals
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
12-29
Figure 12.10 An ER diagram for the movie rental database
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