Introduction to MIS Chapter 6
Download
Report
Transcript Introduction to MIS Chapter 6
Introduction to MIS
Database Management Systems
Introduction to MIS
1
Database Management Systems
Reports and
ad hoc queries
Database
DBMS
Programs
Sales and transaction data
Introduction to MIS
2
Outline
Relational Databases
Queries
Designing a Database
Database Applications
Database Administration
Database and e-Business
Cases: Airlines
Appendix: Building Forms in Access
Introduction to MIS
3
DBMS & People
Database Administrator
(Standards, Design, and Control)
Programmer
Analyst
Data
Programs
& Revisions
Database
Management
System
Program
Ad Hoc Queries
Managers
and Reports
Program
Business Needs
Data Collection
and Transaction
Processing
Business Operations
Introduction to MIS
4
Relational Databases
Tables
Rows
Columns
Primary keys
Data types
Text
Dates & times
Numbers
Objects
Customer Table
Phone
312-555-1234
502-555-8876
602-555-9987
612-555-4325
Name
Jones
Smith
Juarez
Olsen
City
Chicago
Glasgow
Phoenix
Minneapolis
Orders Table
Customer
502-555-8876
602-555-9987
612-555-4325
502-555-8876
Introduction to MIS
Address
123 Main
456 Oak
887 Ribera
465 Thor
Date
3/3/04
4/4/04
4/9/04
5/7/04
Salesperson
2223
8876
8876
3345
Total_sale
157.92
295.53
132.94
183.67
5
Focus on data
Accuracy.
Time.
Concurrency.
Security.
Ad hoc queries
Speed of development
Change programs without
altering data.
Data integrity
Database Advantages
Data independence
Stable data
Programs change.
Report writers.
Input forms.
Data manipulation.
All Data Files
Database Management
System
Invoice
Program
Billing
Program
Flexibility & Queries
Introduction to MIS
7
Database Queries
Single Table
Computations
Joining Tables
Four questions to create a query
1)
2)
3)
4)
Introduction to MIS
What output do you want to see?
What tables are involved?
What do you already know? (constraints)
How are the tables joined?
8
File: C05E15a.mdb
Single Table Query Introduction
Sample Data
CID
28764
87535
44453
29587
Name
Adamz
James
Kolke
Smitz
Phone
602-999-2539
305-777-2235
303-888-8876
206-676-7763
City
Phoenix
Miami
Denver
Seattle
AccountBalance
197.54
255.93
863.39
353.76
Query: Which customers have balances greater than $200?
Access Query Screen (QBE)
Introduction to MIS
9
“AND” Conditions and Sorting
Sample Data
CID
28764
87535
44453
29587
Name
Adamz
James
Kolke
Smitz
Phone
602-999-2539
305-777-2235
303-888-8876
206-676-7763
City
Phoenix
Miami
Denver
Seattle
AccountBalance
197.54
255.93
863.39
353.76
Query: Which Denver customers have balances greater than $200?
Access Query Screen (QBE)
Introduction to MIS
10
SQL Introduction
CID
28764
87535
44453
29587
Name
Adamz
James
Kolke
Smitz
Phone
602-999-2539
305-777-2235
303-888-8876
206-676-7763
City
Phoenix
Miami
Denver
Seattle
AccountBalance
197.54
255.93
863.39
353.76
Query: Which customers have balances greater than $200?
SQL:
SELECT CID, Name, Phone, City, AccountBalance
FROM
Customers
WHERE AccountBalance > 200 ;
Query: Which Denver customers have balances greater than $200?
SQL:
SELECT CID, City, AccountBalancel
FROM
Customers
WHERE AccountBalance > 200 and City = “Denver”
ORDER BY Name ASC ;
Introduction to MIS
11
Useful WHERE Conditions
Comparisons
Numbers
Text
<, =, >, <>, BETWEEN
AccountBalance > 200
Name > “Jones”
Common
LIKE
Match all
Match one
Dates
Missing data
NOT
Name LIKE “J*”
Name LIKE “?m*”
Odate between #8/15/95# and #8/31/95#
City is NULL
Name is NOT NULL
Use with QBE or SQL
Introduction to MIS
12
SQL General Form
SELECT
FROM
JOIN
WHERE
GROUP BY
ORDER BY
Introduction to MIS
columns
tables
link columns
conditions
column
column (ASC | DESC)
13
Computations
Sum
Avg
Min
Max
Count
StDev
Var
Introduction to MIS
SQL
SELECT Count(C#), AVG(AccountBalance)
FROM
Customers ;
QBE
14
QBE
Groups or Subtotals
Sample Output
SQL
SELECT
FROM
GROUP BY
Introduction to MIS
City, AVG(AccountBalance)
Customers
City ;
City
AVG(AccountBalance)
Chicago
197.54
Denver
863.39
Miami
255.93
Phoenix
526.76
Seattle
353.76
15
Groups with Conditions
Query: What is the average account balance for customers from Denver?
Introduction to MIS
16
Multiple Tables
Customers
CID
12345
28764
29587
44453
87535
Name
Jones
Adams
Smitz
Kolke
James
Phone
312-555-1234
602-999-2539
206-656-7763
303-888-8876
305-777-2235
City
Chicago
Phoenix
Seattle
Denver
Miami
AccountBalance
$197.54
$526.76
$353.76
$863.39
$255.98
Salespeople
SID
225
452
554
663
887
Name DateHired
West
5/23/75
Zeke
8/15/94
Jabbar 7/15/91
Bird
9/12/93
Johnson 2/2/92
Phone
Commission
213-333-2345
5
213-343-5553
3
213-534-8876
4
213-225-3335
4
213-887-6635
4
Orders
OrderID CID
117
12345
125
87535
157
12345
169
29587
178
44453
188
29587
201
12345
211
44453
213
44453
215
87535
280
28764
SID
887
663
554
255
663
554
887
255
255
887
663
Odate
3/3/2004
4/4/2004
4/9/2004
5/5/2004
5/1/2004
5/8/2004
5/28/2004
6/9/2004
6/9/2004
6/9/2004
5/27/2004
Amount
$57.92
$123.54
$297.89
$89.93
$154.89
$325.46
$193.58
$201.39
$154.15
$563.27
$255.32
ItemsSold
Items
ItemID
1154
2254
3342
7653
8763
9987
Description
Corn Broom
Blue Jeans
Paper Towels--3 rolls
Laundry Detergent
Men's Boots
Candy Popcorn
Introduction to MIS
Price
$1.00
$12.00
$1.00
$2.00
$15.00
$0.50
OID
117
117
117
125
125
157
169
169
178
ItemID
1154
3342
7653
1154
8763
7653
3342
9987
2254
Quantity
2
1
4
4
3
2
1
5
1
17
Linking Tables
The Orders to ItemsSold relationship enforces referential integrity.
One Order can list many ItemsSold.
Introduction to MIS
18
Query Example
Which customers (CID) have placed
orders since June 1, 2004?
QBE
SQL
SELECT CID, ODate
FROM
Orders
WHERE Odate >= #6/1/2004# ;
CID
44453
44453
87535
28764
ODate
6/9/2004
6/9/2004
6/9/2004
6/27/2004
Introduction to MIS
Results
19
Query Example
What are the names of the customers who placed orders since June 1,
2004?
QBE
SQL
SELECT DISTINCT Name, Odate
FROM
Orders
INNER JOIN Customers ON
Orders.CID = Customers.CID
WHERE Odate >= #6/1/2004# ;
Name
Adamz
James
Kolke
Odate
6/27/2004
6/9/2004
6/9/2004
Introduction to MIS
Results
20
List the salespeople (sorted alphabetically) along
with the names of customers who placed orders
with that salesperson.
Query Example
SQL
SELECT DISTINCT Salespeople.Name,
Customers.Name
FROM
Salespeople INNER JOIN (Customers INNER JOIN Orders ON
Customers.CID=Orders.CID) ON Salespeople.SID = Orders.SID
ORDER BY Salespeople.Name ;
Results
SalesName Cust.Name
Bird
Adamz
Bird
James
Bird
Kolke
Jabbar
Jones
Jabbar
Smitz
Johnson
James
Johnson
Jones
West
Kolke
West
Smitz
Introduction to MIS
QBE
21
Aggregation Query
What is the total amount of orders placed from customers who live in
Miami?
QBE
Results
$2,418.84
SQL
SELECT SUM(Amount)
FROM
Orders
INNER JOIN Customers ON Orders.CID = Customers.CID
WHERE City = “Miami” ;
Introduction to MIS
22
Primary keys
One value per cell
Column depends on whole
key and nothing but the key.
Database Design
Customers
CID
11
22
33
44
name
Jones
Smith
James
Ricci
city
Chicago
Chicago
Chicago
Chicago
home
business
111-1111 222-2222
111-4567
111-2567 222-8976
fax
222-35534
service
876-3456
444-5353
333-8765
Phones(CID, phone_type, number)
Customers(CID, name, city)
CID
11
22
33
44
Introduction to MIS
name
Jones
Smith
James
Ricci
city
Chicago
Chicago
Chicago
Chicago
CID
11
11
11
11
22
22
33
44
phone_type
home
business
fax
service
home
service
home
fax
number
111-1111
222-2222
222-3534
876-3456
111-4587
444-5353
111-2567
333-8765
23
File: C05Vid.mdb
Database Design: Normalization
Introduction to MIS
24
Notation
Table
columns
Table
name
Customer (CustomerID, Phone, Name, Address, City, State, ZipCode)
Primary key is
underlined
CustomerID
1
2
3
4
5
6
7
8
9
10
Phone
502-666-7777
502-888-6464
502-777-7575
502-333-9494
502-474-4746
615-373-4746
615-888-4474
615-452-1162
502-222-4351
502-444-2512
Introduction to MIS
LastName
Johnson
Smith
Washington
Adams
Rabitz
Steinmetz
Lasater
Jones
Chavez
Rojo
FirstName
Martha
Jack
Elroy
Samuel
Victor
Susan
Les
Charlie
Juan
Maria
Address
125 Main Street
873 Elm Street
95 Easy Street
746 Brown Drive
645 White Avenue
15 Speedway Drive
67 S. Ray Drive
867 Lakeside Drive
673 Industry Blvd.
88 Main Street
City
Alvaton
Bowling Green
Smith's Grove
Alvaton
Bowling Green
Portland
Portland
Castalian Springs
Caneyville
Cave City
State
KY
KY
KY
KY
KY
TN
TN
TN
KY
KY
ZipCode
42122
42101
42171
42122
42102
37148
37148
37031
42721
42127
25
1st: Repeating
RentalForm(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode, (VideoID, Copy#, Title, Rent ) )
Repeating Section
Causes duplication
TransID RentDate
CustomerID LastName
Phone
Address
VideoID
Copy#
Title
Rent
1
1
2
2
2
3
3
3
4
4
4
4
3
3
7
7
7
8
8
8
3
3
3
3
502-777-7575
502-777-7575
615-888-4474
615-888-4474
615-888-4474
615-452-1162
615-452-1162
615-452-1162
502-777-7575
502-777-7575
502-777-7575
502-777-7575
95 Easy Street
95 Easy Street
67 S. Ray Drive
67 S. Ray Drive
67 S. Ray Drive
867 Lakeside Drive
867 Lakeside Drive
867 Lakeside Drive
95 Easy Street
95 Easy Street
95 Easy Street
95 Easy Street
1
6
8
2
6
9
15
4
3
8
13
17
2
3
1
1
1
1
1
1
1
1
1
1
2001: A Space Odyssey
Clockwork Orange
Hopscotch
Apocalypse Now
Clockwork Orange
Luggage Of The Gods
Fabulous Baker Boys
Boy And His Dog
Blues Brothers
Hopscotch
Surf Nazis Must Die
Witches of Eastwick
$1.50
$1.50
$1.50
$2.00
$1.50
$2.50
$2.00
$2.50
$2.00
$1.50
$2.50
$2.00
4/18/04
4/18/04
4/30/04
4/30/04
4/30/04
4/18/04
4/18/04
4/18/04
4/18/04
4/18/04
4/18/04
4/18/04
Introduction to MIS
Washington
Washington
Lasater
Lasater
Lasater
Jones
Jones
Jones
Washington
Washington
Washington
Washington
26
First Normal
Customer Rentals
Name
Phone
Address
City
State
ZipCode
VideoID Copy# Title
1. 6
1
Clockwork Orange
2. 8
2
Hopscotch
3.
{Unused Space}
4.
5.
Rent
1.50
1.50
Not in First Normal Form
Introduction to MIS
27
1st: Split
RentalForm(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode, (VideoID, Copy#, Title, Rent ) )
RentalForm2(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode)
RentalLine(TransID, VideoID, Copy#, Title, Rent )
RentalForm2
TransID
1
2
3
4
RentDate
4/18/04
4/30/04
4/18/04
4/18/04
CustomerID
3
7
8
3
Phone
502-777-7575
615-888-4474
615-452-1162
502-777-7575
LastName
Washington
Lasater
Jones
Washington
FirstName
Elroy
Les
Charlie
Elroy
Address
95 Easy Street
67 S. Ray Drive
867 Lakeside Drive
95 Easy Street
Introduction to MIS
State
KY
TN
TN
KY
ZipCode
42171
37148
37031
42171
RentalLine
Note: replication
Note: replication
City
Smith's Grove
Portland
Castalian Springs
Smith's Grove
TransID
1
1
2
2
2
3
3
3
4
4
4
4
VideoID
1
6
8
2
6
9
15
4
3
8
13
17
Copy#
2
3
1
1
1
1
1
1
1
1
1
1
Title
2001: A Space Odyssey
Clockwork Orange
Hopscotch
Apocalypse Now
Clockwork Orange
Luggage Of The Gods
Fabulous Baker Boys
Boy And His Dog
Blues Brothers
Hopscotch
Surf Nazis Must Die
Witches of Eastwick
Rent
$1.50
$1.50
$1.50
$2.00
$1.50
$2.50
$2.00
$2.50
$2.00
$1.50
$2.50
$2.00
28
2nd Split
Column depends on entire (whole) key.
RentalLine(TransID, VideoID, Copy#, Title, Rent )
VideosRented(TransID, VideoID, Copy# )
TransID
1
1
2
2
2
3
3
3
4
4
4
4
VideoID
1
6
2
6
8
4
9
15
3
8
13
17
Introduction to MIS
Copy#
2
3
1
1
1
1
1
1
1
1
1
1
Videos(VideoID, Title, Rent )
VideoID
1
2
3
4
5
6
7
8
Title
2001: A Space Odyssey
Apocalypse Now
Blues Brothers
Boy And His Dog
Brother From Another Planet
Clockwork Orange
Gods Must Be Crazy
Hopscotch
Rent
$1.50
$2.00
$2.00
$2.50
$2.00
$1.50
$2.00
$1.50
29
3rd Split
RentalForm2(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode )
Rentals(TransID, RentDate, CustomerID )
Customers(CustomerID, Phone, Name, Address, City, State, ZipCode )
Rentals
TransID
1
2
3
4
RentDate
4/18/04
4/30/04
4/18/04
4/18/04
CustomerID
1
2
3
4
5
6
7
8
9
10
CustomerID
3
7
8
3
Phone
502-666-7777
502-888-6464
502-777-7575
502-333-9494
502-474-4746
615-373-4746
615-888-4474
615-452-1162
502-222-4351
502-444-2512
Introduction to MIS
Customers
LastName FirstName
Johnson Martha
Smith
Jack
WashingtonElroy
Adams
Samuel
Rabitz
Victor
Steinmetz Susan
Lasater
Les
Jones
Charlie
Chavez
Juan
Rojo
Maria
Address
125 Main Street
873 Elm Street
95 Easy Street
746 Brown Drive
645 White Avenue
15 Speedway Drive
67 S. Ray Drive
867 Lakeside Drive
673 Industry Blvd.
88 Main Street
City
State
Alvaton
KY
Bowling Green KY
Smith's Grove
KY
Alvaton
KY
Bowling Green KY
Portland
TN
Portland
TN
Castalian Springs
Caneyville
KY
Cave City
KY
ZipCode
42122
42101
42171
42122
42102
37148
37148
TN
370
42721
42127
30
3NF Tables
Introduction to MIS
31
Text/Labels
Data Variables
DBMS Input Screen
Command
Buttons
Scrolling Region/Subform
Introduction to MIS
Record Selectors
- Subform
- Main
32
DBMS Report Writer
Report header
Page header
Break/Group header
Detail
Footers
Introduction to MIS
33
Sample Report
with Groups
Introduction to MIS
34
Designing Menus for Users
Main Menu
1.
2.
3.
4.
5.
Setup Choices
Data Input
Print Reports
DOS Utilities
Backups
Customer Information
Daily Sales Reports
Friday Sales Meeting
Monthly Customer Letters
Quit
As a secretary, which menu is easier to understand?
Introduction to MIS
35
Database Administration
Database Administrator
Testing
Backup
Recovery
Standards
Access Controls
Introduction to MIS
36
E-Business Databases
E-business is transaction-based
Databases support multiple users and protect
transactions
Modern websites are driven by databases
Introduction to MIS
41
Order Form
Descriptions
Prices
E-Business Databases
Internet
Customer
Web Server
Web program script
<HTML>
Text
<%
Database connection
%>
Introduction to MIS
42
Cases: Airlines
Introduction to MIS
43
Cases: American Airlines
Southwest Airlines
USAirways
www.americanair.com
www.iflyswa.com
What is the company’s current status?
What is the Internet strategy?
How does the company use information technology?
What are the prospects for the industry?
Introduction to MIS
44
Appendix: Building Forms in Access
Begin with the form wizard. Select everything from the Sale table.
Introduction to MIS
45
Selecting Columns
Select additional data from the other tables. But be careful with key
columns. For example, do not include the CustomerID from the
Customer table or the ItemID from the Item table. Instead, use
CustomerID from the Sale table and ItemID from the SaleItem table.
Introduction to MIS
46
Form Wizard: First Pass
The wizard builds the Sale form with a sub form to enter
multiple Items being sold. But it needs some work with layout.
Introduction to MIS
47
Form Design View
1.
Lookup values
2.
Customer
table
3.
CustomerID,
LastName,
FirstName,
Phone
4.
Store value in:
CustomerID
In design view, you can move the labels and boxes to make the form
easier to read. Then, delete the original CustomerID box and use the
wizard to add a Combo box to let users select a customer from a list.
Introduction to MIS
48
Form Design: Sub Form and SubTotal
Edit the subform and add a Combo box for ItemID. Delete the Description box. Add
an ItemValue text box to multiply Quantity * List Price. Add a text box to compute the
subtotal [=Sum([Quantity]*[ListPrice])]. Be sure to set the correct names and formats
in the property window.
Introduction to MIS
49
Sale Form
The final sale form adds a text box that copies the subtotal from the sub
form to the main form. Just add a text box and set its value to
=[SaleItem Subform].[Form].[SubTotal]
and set its name and format properties.
Introduction to MIS
50