Transcript CS 262-557

CS 262-557
Introduction to Database
Systems
Chapter 1
1
Chapter 1
Databases and Database
Users
Chapter 1
2
What is a Database?
• A collection of related data.
• It contains data specific to part of the real
world, a miniworld.
• Consequently, it is a logically coherent
collection of data.
• It is maintained for specific applications.
Chapter 1
3
Address Book
Alan Arkin
200 Olive Street
Ann Arbor, MI 48104
313-555-1212
Birthday May 10th
. . .
Mary Ziegler
341 Einstein Drive
Berkeley, CA 94703
510-555-1234
Annivsy: July 12th
Chapter 1
4
September 1998
Sun Mon Tues Wed Thurs Fri
Sat
1
2
3
4
5
11
12
18
19
25
26
6
7
8
9
13
14
15
16
10
John’s
Bday
17
20
21
22
23
24
27
Sue’s
Annvy
28
29
30
Chapter 1
5
Index on Cities
Ann Arbor
Ann Arbor
Berkeley
Berkeley
Compton
Allan Arkin
John Doe
Sue Smith
Mary Ziegler
John Law
Chapter 1
6
Stages in the Life
of a Database
• Definition: specifying structures of tables
and data to be stored.
• Construction: populating tables with data.
• Manipulation: queries, updates, deletes, etc.
Chapter 1
7
• A DBMS (database management system) is
a general-purpose software that enables
users to define, construct and maintain
various databases. ie. Oracle, Dbase IV
• However, databases have been implemented
using a high-level language to define a
collection of files. Using programs to
access this files. This is traditional file
processing.
Chapter 1
8
Fig 1.1
Chapter 1
9
• A database system, which consists of a
database together with DBMS software, is
the complete environment in which a user
operates.
Chapter 1
10
Fig 1.2
Chapter 1
11
Hypothetical situation: Smith and Brown are
on probation and need approval from the
instructor of each course that they scored a
C or less.
What are the names of these courses?
Who are the instructors?
Suggested changes to tables if these queries
are frequent.
Chapter 1
12
What Problems could the
University have regarding access
to these tables?
Chapter 1
13
Solution
• “Virtual” tables or views allows the
organization to selectively provide access to
information while avoiding concurrency
problems.
• Views also allow for the creation of
dynamic summary views of information.
Chapter 1
14
fig 1.4
Chapter 1
15
Characteristics of the DBMS
Approach
• Self-describing nature: Together with
each database is stored a system catalog
which is a separate entity containing
information about each table in the
database, types of each data item I.e.
metadata!
Chapter 1
16
• Insulation between programs & data:
Consequence of using metadata. In
traditional file processing the structure of
the file is embedded in the access program,
while with DBMS the structure of a file is
defined separately in the system catalog,
and is transparent to the access program.
E.g.. in the University database if we added
extra fields in GRADE_REPORT table, all
programs that access this table would have
to be rewritten! This is not true with a
DBMS.
Chapter 1
17
• Support of multiple views: A view may
be a subset of the database, or may contain
virtual data that is not explicitly stored, but
instead it is derived. i.e. TotalHours in
STUDENT_DUES.
• Sharing of data: A multi-user DBMS
provides concurrency control software to
allow multiple users to simultaneously
access data. Such as bank accounts and
airline reservations.
Chapter 1
18
• Control of Redundancy: There are 3 main
problems with redundancy:
– duplication of effort
– waste of space
– possible inconsistency
• In the DBMS approach, redundancy is
regulated by centralizing the data in one
database, and providing various user groups
with the views they need. However,
controlled redundancy may sometimes be
desirable
Chapter 1
19
• Security & Authorization Mechanisms:
Consequence of centralization. The DBA
may restrict privileges (retrieve, update,etc)
by either database, user or user’s role.
• Enforcing integrity constraints: A DBMS
provides capabilities to enforce constraints
or business rules I.e. each student must have
a unique studentnumber.
• Providing Backup & Recovery: A DBMS
provides facilities to recover from either
software or hardware failures.
Chapter 1
20
DBMS Personnel
•
•
•
•
•
Tool Developers
DBMS Designers
Database Administrator (DBA)
System Analysts
End Users
– Casual - occasional high level use
– Parametric- frequent canned transactions
– Sophisticated - frequent high level use
Chapter 1
21