Transcript Intro I - Western Illinois University

INTRO TO
ENTERPRISE
DATABASES - I
Dr. Chandra Amaravadi
Western Illinois University
1
IN THIS PRESENTATION..
Basic concepts: data and info, entity, attr, pkey, file, record
Need for databases
Historical approach to data
Definitions
Database organization
Importance of databases/Organizational importance
DBMS and DBMS activities and functions
Course features
2
WHAT IS INFORMATION?
3
DEFINING DATA AND INFORMATION
Data: Data consists of raw facts e.g. 2982034
Information: collection or organization of facts;
USA 30 32 31 93 Great Britain 19 20 12
51 China 19 15 20 54 Germany 13 8 9 30
Russia 12 14 15 41 Japan 10 5 18 33
France 8 11 12 31
4
DATA VS INFORMATION
Country
Gold
Silver
Bronze
Total
USA
30
32
31
93
Great Britain
19
20
12
51
China
19
15
20
54
Germany
13
08
09
30
Russia
12
14
15
41
Japan
10
5
18
33
France
08
11
12
31
Italy
08
09
06
23
Data becomes information when we
________________ it.
5
NEED FOR DATABASES
We live in an information age
cannot function without information
can be a life or death issue
finding acct balance
print transaction history
order spare parts for MRI machine
find patient records
needs to be a click away
otherwise, lost sale, lost profits or lost career!
How can we find information when we need it?
6
BASIC ORGANIZATION
CONCEPTS
(Entity, attribute, primary key, file, record)
7
INTRODUCING ENTITIES,
ECLASSES AND ATTRIBUTES
Information is a collection of facts usually DESCRIBING
something. What does it describe here? How?
Name:
DOB:
Profession:
Address:
Steve Nash
2-7-1980
Basket ball player
415 Walnut street.
8
COLLECTION OF DATA...
Following is another example of information as a
collection of facts describing a transaction.
Acct#:
Date:
Time:
Merchant:
Card type:
Issuer:
4555950
12/5/15
4:48 pm
WIU Union
Master Card
First Bank
______describe a transaction
more.
9
ONE ATTRIBUTE HAS A UNIQUE
VALUE
One of these attributes always has a unique
value which one is it?
Acct#:
Date:
Time:
Merchant:
Card type:
Issuer:
4555950
12/5/15
4:48 pm
WIU Union
Master Card
First Bank
The unique attribute is
Called a ______________
10
ORGANIZATION CONCEPTS
What can we say about these two sets of data?
Transaction#:
Date:
Time:
Merchant:
Card type:
Issuer:
55643
12/5/15
4:09 pm
WIU Union
Master Card
First Bank
Transaction#:
Date:
Time:
Merchant:
Card type:
Issuer:
55644
12/5/15
4:20 pm
Vitales
Master Card
Midwest Bank
____________________________
11
ORGANIZATION CONCEPTS..
Transaction#:
Date:
Time:
Merchant:
Card type:
Issuer:
55643
12/5/15
4:09 pm
WIU Union
Master Card
First Bank
________
Transaction
ID
Transaction
Date
Transaction
Time
Merchant
55643
12/05/15
4:09 pm
WIU Union
55644
12/05/15
4:20 pm
Vitales
Data can be organized into a _____ with ________
12
DEFINITION OF A FILE..
CBT QUALITY MANAGEMENT COMMITTEE
Meeting Minutes
Meeting Date/time: December 2nd, 2:00 p.m.
Present: Brad Burke, Ken Dien, Chin Yi,
John Smith
Mary Ash
1215 Oak Drive
415 Ponds
$515.00
$ 56.00
11/5/15
5/9/15
A database ______ is a collection of _______ .
13
DATABASE
14
DATABASE
Database - A shared collection of logically related data
designed to meet the needs of multiple users in an
organization.
Example, cars listed in auto-trader
Matches played in the olympics
Properties owned by a company
Books available from Amazon.com
DBMS is the software program.
15
DATABASE ORGANIZATION
Structure/schema
Attributes/field names
Primary key
Record
Record
PROD#
DESCR.
PRICE
QUANTITY
IR888E
Refrigerator
$1,800
20
TS3233
Television
$67
32
A database has a structure or schema that organizes the data
into attributes. Collections of attribute values become records.
A primary key guarantees unique access.
Attr. values
16
DATABASE ORGANIZATION
Database – A group of related files
File/table – A group of related records
Record – a grouping of related fields
Attr. value – value of an attr. e.g. hair color
Schema -- This is the structure or logical view
of the database (tables and attr.)
Primary key – An attribute whose values are
unique within a file
Secondary key – Any other attribute
Database
Files/tables
Records
Attr. values
17
HISTORICAL APPROACH TO DATA
The historical method of handling data is called file processing
55643 3/02/15
55644 5/08/15
55644 7/05/15
55646 12/11/15
4:09pm
4:20pm
4:20pm
8:05am
File 1
WIU Union
Vitales
Vitales
Pizza Hut
55643
55644
55644
55646
$25.60
$38.00
$63.00
$55.00
MC
Visa
Visa
Amex
WIU Union
Vitales
Vitales
Pizza Hut
File 2
Instead of storing data centrally in a database, in the file processing
approach, the same data was often duplicated in multiple files.
18
FILE PROCESSING PROBLEMS
What problems did the file processing approach
result in?
19
DATABASE MANAGEMENT
SYSTEMS (DBMS)
20
DBMS
DBMS - software program to create, manage and provide
controlled access to the data
Example Access, Oracle, DB2
What can we do with a DBMS?
21
DBMS ACTIVITIES & FUNCTIONS
The operations that can be carried out with a DBMS include:
Define structure / schema
Enter data
modify data
query data
get reports
22
DBMS ACTIVITIES & FUNCTIONS
Define structure/schema
Field Name
Data type
Description
Length
Decimals
Prod#
Numeric
Unique prod
code
6
0
Descr
Text
Short prod
description
25
0
Price
Currency
Product price
6
2
This is called data definition
23
DATA ENTRY
A data entry form allows data to be entered easily.
Title
EMPLOYEE DATA ENTRY
Label
EMP ID:
Field
NAME:
Data entry
form
24
ENTERING DATA
EMPLOYEE DATA ENTRY
EMP ID:
1117
NAME:
John
The data is entered into the data entry form
This is called data entry. Where does the data go to?
25
RETRIEVING DATA
Data can be obtained from the database in the
following ways:
SQL commands
QBE (Query by Example)
Reports
26
RETRIEVING DATA
THE STRUCTURED QUERY LANGUAGE
Each SQL statement has three parts as shown below:
SELECT is used to select attributes needed
FROM is used to specify the tables from which data is obtained.
WHERE is used for including criteria (filter)
Select car_id, car_color, car_price, car_mileage
From cars
Where car_mileage < 100,000
27
RETRIEVING DATA..
Write an SQL statement for listing names of employees
in finance department.
Employee
Emp ID
Emp Name
Department
Join Date
1115
John
Finance
4/9/15
1117
Han
Sales & Mktg.
12/15/15
1120
Mary
Human
Resources
1/19/15
SELECT
FROM
WHERE
??
??
??
This is called query/retrieval.
28
QUERY BY EXAMPLE
List names of employees in finance department.
Employee
Emp ID
Emp Name
Department
Join Date
“finance”
A query by example form (QBE)
This is also another form of query/retrieval.
29
RETRIEVING DATA: REPORTS..
We can get data out through queries or reports
Reporting resembles COBOL reports
Report specification needs to be created
Consists of RH, PH, Detail, RF, PF
Report is generated from specification
30
SPECIFYING REPORTS..
RH
PRODUCT LISTING
Title
PH
PRODUCT # DESCR. PRICE
Detail
Product #
Descr.
Price
Column
Headings
Fields
PF
Average Price
RF
Footer
A report specification
31
SUMMARY OF DBMS ACTIVITIES
Activities with DBMS
define structure/schema
enter data (into table/tables)
modify data (not discussed)
query data (from table/tables)
get reports (from table/tables)
32
USAGE OF DATABASES
Databases are used (operational):
To store and record information e.g. bal, price, grades etc.
To retrieve information e.g. check#432 cashed?
To report information e.g. daily sales
To answer queries e.g. how many shoes were sold?
Advanced uses (Strategic):
To analyze trends
Identify sales prospects
33
OPERATIONAL & STRATEGIC USAGE
What if a customer wants to return a tie purchased
in a store?
What if a manager wants to know what products
were sold on a particular day?
Suppose we have detailed information on each and
every transaction in a store, what can we do with
that?
34
THE DEVELOPMENT CYCLE
35
THE DATABASE DEVELOPMENT
CYCLE
How do we know what information we need for the
database?
36
THE DATABASE DEVELOPMENT
CYCLE..
The database development cycle parallels the information
systems development cycle.
database planning
database analysis
database design
implementation
support/maintenance
37
THE DATABASE DEVELOPMENT
CYCLE..
Planning – is concerned with identifying what information will be
in the database and the resources that will be required.
Analysis – this is concerned with the detailed
requirements such as attributes, entity classes as well
as with their relationships.
Design -- is concerned with creating a database structure
that will fulfill the requirements
Implementation – this is the phase that is concerned with actually
defining the structures, entering data and creating
reports.
Maintenance – this is concerned with ensuring that the schema,
reports etc are up to date and the database is
performing well.
38
THE DEVELOPMENT CYCLE AND
THIS COURSE..
Database environment
Database planning
Analysis
Design
Design
Implementation
Support/maintenance
-- Evolution & environment
-- Enterprise analysis
-- Database analysis
-- Logical design
-- Physical design
-- Access, MySQL assignments
-- Database adminstration.
39
ABOUT THE COURSE
40
COURSE OBJECTIVES
Thorough understanding of database concepts
Ability to develop ER models
Ability to develop database designs
Ability to implement databases in PC based systems
Ability to write SQL queries
Familiarity with database administration issues
Ability to analyze database requirements and
implement systems for a small organization
41
COURSE FEATURES
Very concept oriented
Database concepts are abstract
Participation required!
Digressions welcome!
Guidelines are very subjective
Numerous in-class exercises
Reinforcing assignments
Need to have a good “schema”!
42
COURSE EVALUATION
EVALUATION ITEM
POINTS
Six assignments*
100
Midterms
200
Final
100
Quizzes (2 x 20)
40
Participation
40
Attendance
20
Total
500
*Lowest will be dropped, but -20 point penalty for non-submission
Note: Please refer to your course syllabus for up to date
information on the evaluation schedule.
43
CLASS PARTICIPATION
Answer questions
Raise an issue relevant to the topic at hand
Solve an in-class problem
Max, two “tallies” per session
Quality and quantity of participation
Participation quizzes
Disruptive activities negatively assessed
Review powerpoints before coming to class!
44
GENERAL COMMENTS
Database practice is an application of theory
Get theories/frameworks and definitions
into your system;
Need to have good conceptual understanding
Need to review frequently (before class, after class
and everyday)
Participate in class
Exams cover all topics (don’t strategize)
Most important course in the IS curriculum
45
46