Transcript DBI - Online Directory Western Illinois University

DATABASES – I
IS 524
Dr. Chandra Amaravadi
1
IN THIS PRESENTATION..
Importance of databases
Real world concepts: entities, eclasses, attributes
Data model
Organization concepts
DBMS & DBMS activities and functions
2
NEED FOR DATABASES
All organizations need to manage information
A church needs to maintain information on
donations
A consulting company needs to manage its
contacts
A doctor needs to know if a patient is allergic to a
particular medication.
An airline exec needs to know occupancy on a
flight.
Information is managed in a database. (Needs organization)
BASIC DATA CONCEPTS
4
REAL WORLD CONCEPTS
Entities
Entity Classes
Attributes
Data models
Database approach/philosophy
5
ENTITIES
Individual examples of things and objects are called entities.
They are modelled only as a collection.
6
WE ARE MORE INTERESTED IN
Eclasses
customers
departments
Organization
product
Sales/vendor
Organization deals with many eclasses
ENTITY CLASSES HAVE PROPERTIES
Properties are called
attributes
Departments
customers
Dept. code, name, manager, budget
machines/
parts
Sales/vendors
8
DISCUSSION
Classify the following as entity, entity class or attribute
John (as a person)
San Francisco (as name of a city)
Customer
Book
Store#
Customs
Microsoft
Microsoft employees
The book “The Second Machine Age”
Invoice
Production
9
SUMMARY OF CONCEPTS
Entity
– Individual example of person, place or thing.
Entity Class – Collection of related entities.
Attributes – Properties of entity classes about which we
would like to collect information.
Eclasses & attributes are used in developing data models
10
DATA MODELS (ER MODELS)
Abstract view of the data & relationships
Captures relationships between eclasses
Supports DB design & implementation
Relationship between students and courses?
between doctors and patients?
between teams and players?
11
DATA MODELS…
Three types of relationships among entity classes
A
A
A
1:1
1:M
M:N
B
For each value of A, one
and only one value of B
and vice versa.
B
For each value of A,
many values of B,
but for each B only one A.
B
For each value of A,
many values of B and vice
versa.
12
DISCUSSION
Identify the types of the following relationships
Company -- president
Instructor -- students
Flights -- pilots
City -- convention centers
Club -- members
Team – players (professional)
Company -- city
Books – authors
Artists -- records
13
THE DATA BASE APPROACH
Entity classes
File 1
cust.
File 2
emp.
Organization
Data model
Data base
14
BASIC ORGANIZATION
CONCEPTS
15
BASIC ORGANIZATION CONCEPTS
data vs information
attribute
primary key
file
record
schema
16
DEFINING DATA
Data: Data consists of raw facts e.g. 298-2034
USA 46 29 29 104 China 38 27 23 88
Russia 24 26 32 82 Great Britain 29 17 19
65 Germany 11 019 14 44 Japan 7 14 17 38
Australia 7 16 12 34 France 11 11 12 34
Are raw facts useful? meaningful?
17
INFORMATION
Information: collection of facts, data presented in a context,
elaboration of data.
Country
Gold
Silver
Bronze
Total
USA
46
29
29
104
China
38
27
23
88
Russia
24
26
32
82
Great Britain
29
17
19
65
Germany
11
19
14
44
Japan
7
14
17
38
Australia
7
16
12
35
France
11
11
12
34
18
INFORMATION AS A
COLLECTION OF FACTS
Collection of facts:
Name:
DOB:
Profession:
Address:
Chris Nash
2-4-1965
Engineer
415 Walnut street.
Details describe a person
more. They are called ______
19
NOTION OF A RECORD
Collection of facts is called a ________
Acct#:
Date:
Time:
Merchant:
Card type:
Issuer:
4555950
12/5/14
4:48 pm
WIU Union
Master Card
First Bank
It is actually a collection
of __________ values
20
ONE ATTRIBUTE HAS A UNIQUE
VALUE
Which of these has a unique value?
Acct#:
Date:
Time:
Merchant:
Card type:
Issuer:
4555950
12/5/14
4:48 pm
WIU Union
Master Card
First Bank
The unique attribute is
called ___________.
3300
3305
3313
21
ORGANIZATION OF DATA/
INFORMATION
Transaction#:
Date:
Time:
Merchant:
Card type:
Issuer:
55643
12/5/14
4:08 pm
Vitales
Master Card
First Bank
Transaction#:
Date:
Time:
Merchant:
Card type:
Issuer:
55644
12/6/14
4:10 pm
WIU Union
Master Card
First Bank
Observations about these two “units” of data?
22
TRADITIONAL CONCEPT OF A FILE
Assume the following is stored somewhere,
Western Illinois University strives to maintain a community which
values academic excellence; institutional integrity; and justice, equity,
and diversity. Such an environment is essential in fostering the
intellectual growth and personal development of all students. Each
member of the University community shares responsibility in
maintaining conditions which support the University's purpose. The
Code of Student Conduct is designed to provide basic guidelines to
advance the University's mission as a premier educational institution.
A traditional file is a collection of characters
23
CONCEPT OF A DATABASE FILE
Transaction#:
Date:
Time:
Merchant:
Card type:
Issuer:
55643
12/5/14
4:08 pm
Vitales
Master Card
First Bank
Transaction#:
Date:
Time:
Merchant:
Card type:
Issuer:
Transaction
ID
Transaction
Date
Transaction
Time
Merchant
55643
12/05/13
4:08 pm
Vitales
55644
12/06/13
4:10 pm
WIU Union
55644
12/6/14
4:10 pm
WIU Union
Master Card
First Bank
A database file consists of _________________ .
24
DATABASE ORGANIZATION
Structure/schema
Primary key
Record
Attributes/field names
PROD#
DESCR.
PRICE
QUANTITY
IR888E
Refrigerator
$1,800
20
TS3233
Television
$67
32
Record
A database is organized into ______, _________ and _______.
attr. values
25
DATABASE ORGANIZATION
Database – A group of related files
File/table – A group of related records
Record – a grouping of related field values
Attribute – property e.g. hair color
Database
Files/tables
Schema -- This is the logical view
Records
of the database (tables and fields)
Primary key – An attribute whose values are
unique within a file
Attr. values
Secondary key – Any other attribute
DBMS – software program to provide controlled data access
26
DBMS
27
DBMS
DBMS - SW program to create, manage and provide
controlled access to the data
Example Access, Oracle, DB2
What can we do with a DBMS?
DBMS ACTIVITIES & FUNCTIONS
Activities with DBMS
Define structure/schema
Data entry
Modify data
query data
get reports
29
DATA DEFINITION
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
Attributes and data types (and other characteristics) are specified as part of data definition
30
DATA ENTRY
A form is used to enter data
Heading
PRODUCT DATA ENTRY
Label
Product #:
Description:
Data entry
form
Field
31
MODIFY DATA
Modify data
Add
Delete
Change
Records/field values
32
QUERYING DATA -QBE
Can use QBE or SQL to query data
list products costing less than $200
prod#
descr.
price
quantity
< 200
Query by example (QBE) form
list employees in finance department
e_ssn
ename
edept
e_join_dt
“finance”
33
RETRIEVAL WITH SQL
The structured query language can be used instead of QBE
Each SELECT statement has three parts
SELECT, FROM and WHERE
SELECT is used to select output attributes
FROM is used to specify the source tables
WHERE is used for row selection criteria
e.g. price < 400; zip = 61455 etc.
SELECT
FROM
WHERE
<attr. list>
<tables>
<condition1 AND/OR condition 2…>
34
RETRIEVAL WITH SQL..
Reservation
Flt#
Pname
Confirm#
AA1802
Smith
PA5R2
PA802
Smith
LX5R2
UA3702
Mahoney
ZB46A
SQL QUERY
Select Flt#, Confirm#
From Reservation
Where Pname = “Smith”;
RESULT
Flt#
AA1802
PA802
Confirm#
PA5R2
LX5R2
35
ANOTHER EXAMPLE
SELECT flt#, deptime, arrtime
FROM
??
WHERE depcity = "PHX" and destcity = "ORD";
FLIGHTS
flt #
depcity
destcity
deptime
arrtime
AA 802
PHX
ORD
1:51 AM
6:05AM
UA 3702
COS
D
7:35 AM
8:05 AM
AA 812
PHX
SEA
4:55 AM
9:00 AM
D 42
PHX
ORD
5:40 PM
9:15PM
UA775
DFW
STL
4:45 PM
5:30 PM
What is the result of the query?
36
DISCUSSION
Write SQL queries to list:
1) employee names.
2) Employees who live in Macomb.
3) employees who enjoy soccer.
Emp
EID
eName
eAddr
eCity
423
Smith
100 Oak lane
Macomb
425
Smith
100 Oak lane
Macomb
501
Johnson
1430 N. Park street
Rock Island
37
THE THEORY OF RETRIEVAL
Dept
DEPTS.
have
EMPLOYEES
dCode
dName
dMgr
Fin.
Finance
Jon
Sas
Sales and service
Ross
Acc.
Accounts
Jan
Emp
EID
eName
eAddr.
11893
Jon
100 Oak street
11895
Carey
15 Candy lane
11896
Ross
1317 Fox creek
list EID, eName, dName
38
MULTI-TABLE SELECT STATEMENT
Used to retrieve data from more than one table
SELECT
FROM
WHERE
table1.attr1, table2.attr2 . . . .
table1, table2, . . . . .
table1.fkey = table2.fkey AND/OR
condition1 AND/OR
.... .........;
RULES
When there are multiple tables, attr. names preceded by table name.
“From” will have list of tables as usual.
The WHERE part will have values of common attr. equated
there can be more than one condition, connected by AND or OR
Using the rules above, write a query to list EID, eName, dName
39
GETTING DATA OUT..
RH
PRODUCT LISTING
Title
PH
PRODUCT # DESCR. PRICE
Detail
Product #
Descr.
Price
Column
Headings
Fields
PF
RF
Average Price
Footer
A report specification
40
GETTING DATA OUT..
PRODUCT LISTING
PROD#
M100
M150
DESCRIPTION
Chair
Table
Average Price
PRICE
$ 50.00
$200.00
$153.00
A generated report
41
SUMMARY OF DBMS ACTIVITIES
Activities with DBMS
Define structure /schema
Enter data
modify data
query data
get reports
42
DBMS ARCHITECTURE
D B M S Kernel
Data
Defn.
SQL
Prog.
Language
Interface
Data
Dictionary
Screen/
Report
Gen.
Appln.
Gen.
D B M S Kernel
Export/Import
43
USEFULNESS OF DATABASES
44
USAGE OF DATA/INFORMATION
What if a customer wants know price of a shirt in a
department store? Returns?
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?
45
IMPORTANCE OF DBMS’s
Operational Usage:
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?
Strategic Usage:
To analyze trends
Identify sales prospects
46
DISCUSSION QUESTIONS
A collection of information about machines and parts is called _____?
A collection of records is called________ ?
The smallest unit of data in a database is _________ ?
What is QBE apart from expansion of abbreviation?
How does QBE differ from SQL?
What does data definition mean?
Where would databases be used?
How are databases used?
What is a good database application?
47
THAT’S ALL
FOLKS!
48