Transcript Introduction to Database Systems

Introduction to
Database Systems
1
Database and Database System
A database is a shared collection of
logically related data designed to meet the
information needs of an organization.
Components of a Database Systems
Database
Hardware
Software - DBMS
Users
2
Database
The data in the database will be
expected to be both integrated and shared
particularly on multi-user systems
Integration - The database may be
thought of as a unification of several
otherwise distinct files, with any
redundancy among these files eliminated
Shared - individual pieces of data in the
database may be shared among several
different users
3
Hardware
These are secondary storage on which the
database physically resides, together with the
associated I/O devices, device controllers etc.
4
DBMS
Examples of DBMS Products
Oracle
 Informix
 Access
 DB2
 Fox pro
 dBase
 SQL Server
 My SQL

5
Typical Function of DBMS
•
•
•
•
•
•
•
•
•
•
Data storage, retrieval and update
A user-accessible catalog
Transaction support
Concurrency and control services
Recovery services
Authorization services
Support of data communication
Integrity Services
Services to promote data independence
Utility services
6
Users
Application Programmer - writes
programs that use the database
Database Designers - designs
conceptual and logical database
Database Administrator (DBA)
Data Administrator
End - user - interacts with the system
from an on-line terminal by using
Query Languages etc.
7
Data & Database Administration
 Data Administrator – a business manager
responsible for controlling the overall
corporate data resources
 Database Administrator (DBA) - a technical
person responsible for development of the
total system
8
Sample Applications









Student Records
Banking
Insurance
Billing Systems e.g. Electricity, Phone
ISPs
Personnel Records
Accounting Systems
Reservation Systems e.g. Airline, Hotel
Medical Records
9
Advantages
 Control of data
redundancy
 Data consistency
 Multipurpose use of
data
 Sharing of data,
 Enforcement of
standards
 Economy of scale
 Balance conflicting user
requirement
 Improved data
accessibility and
responsiveness
 Increased productivity
 Improved maintenance
through data
independence
 Increased concurrency
 Improved backup and
recovery services.
10
Disadvantages





Complexity
Size
Cost of DBMS
Additional hardware costs
Cost of conversion
11
Database Architecture
External Level – concerned with the way
users perceive the database
Conceptual Level – concerned with
abstract representation of the database in
its entirety
Internal Level – concerned with the way
data is actually stored
12
Architecture of Db System
External Level
Application 1
Application 2
Application 3
Logical Data
Independence
Conceptual
Level
DBMS
Physical Data
Independence
Internal Level
Database
13
Data Independence
 Logical Data Independence – users and user
programs are independent of logical structure
of the database
 Physical Data Independence – the separation
of structural information about the data from
the programs that manipulate and use the
data i.e. the immunity of application
programs to changes in the storage structure
and access strategy
14
Data Independence
 Different applications will need different views of
the same data, so that if they are not interested in a
part of the database, that part need not be included
in their view. This feature is also important for
controlling access to parts of database
 The DBA must have the freedom to change the
storage structure or access strategy in response to
changing requirements, without having to modify
the existing applications
15
Db Development Life Cycle











Database planning
System definition
Requirement collection and analysis
Database design
DBMS selection
Application design
Prototyping
Implementation
Data conversion and loading
Testing
Operational maintenance
16
Database Design
 Conceptual database design - the process of
constructing a model of the information used
in an organization, independent of all
physical considerations
Step 1 Build local conceptual data model for
each user view
17
Database Design
 Logical database design for the relational
model - the process of constructing a model
of the info used in an organization based on a
specific data model, but independent of a
particular DBMS and other physical
considerations
Step 2 Build and validate local data model for
each user view
Step 3 Build and validate global logical data
model
18
Database Design
• Physical database design for relational
databases - the process of producing a
description of the implementation of the
database on secondary storage.
Step 4 Translate global data model for target
DBMS
Step 5 Design physical representation
Step 6 Design security mechanisms
Step 7 Monitor and tune the operational
system
19
Conceptual Database Design
Step 1 Build local conceptual data model for
each user view
 Identify entity types
 Identify relationship types
 Identify and associate attributes with entity or





relationship
Determine attributes domains
Determine candidate and primary key attributes
Specialize/generalize entity types (optional step)
Draw Entity-Relationship diagram
Review local conceptual data model with user
20
Logical Database Design for the
Relational Model
Step 2 Build and validate local data model for
each user view
 Map local conceptual data model to local
logical data model
 Derive relations from local logical data model
 Validate model using normalization
 Validate model against user transactions
 Draw Entity-Relationship diagram
 Define integrity constraints
 Review local logical data model with user
21
Logical Database Design for the
Relational Model
Step 3 Build and validate global logical data
model
 Merge local logical data models into global
model
 Validate global data model
 Check for future growth
 Draw final Entity-Relationship diagram
 Review global logical data model with users
22
Physical Database Design for
Relational Databases
Step 4 Translate global data model for target DBMS
 Design base relations
 Design enterprise constraints for target DBMS
Step 5 Design physical representation
 Analyze transactions
 Choose file organizations
 Choose secondary indexes
 Consider the introduction of controlled redundancy
 Estimate disk space requirements
23
Physical Database Design for
Relational Databases
Step 6 Design security mechanisms
 Design user views
 Design access rules
Step 7 Monitor and tune the operational system
24