PROGRAMMING Lecturer: K. N.
MATINGO
FOR GEOMATICS
matingok@staff.msu.ac.zw
APPLICATIONS
HSVG245
To
be
continued
…
SPATIAL DATABASES AND
DATABASE DESIGN 98
LECTURE OUTLINE
• The
relational
data model
• Introduction to Database Management Systems
(DBMS)
• Entity
Relationship
• Types of DBMS
Diagrams
(ERDs)
• Database Design Concepts and Procedures
Image Source: GIS Geography
2
DATABASE MANAGEMENT SYSTEMS
DATABASE MANAGEMENT SYSTEMS(1)
DATABASE MANAGEMENT SYSTEMS(2)
DATABASE MANAGEMENT SYSTEMS(3)
DATABASE MANAGEMENT SYSTEMS(4)
DATABASE MANAGEMENT SYSTEMS(5)
DATABASE MANAGEMENT SYSTEMS(6)
DATABASE MANAGEMENT SYSTEMS(7)
DATABASE MANAGEMENT SYSTEMS(8)
Query optimiser
• One of its tasks is to derive for every query request
a number of alternative, but equivalent, execution
plans, and then to choose that plan, using a time
cost model, that is expected to perform best (will
use least amount of time).
DATABASE MANAGEMENT SYSTEMS (9)
DATABASE MANAGEMENT SYSTEMS(10)
DATABASE MANAGEMENT SYSTEMS(11)
Self test
DATABASE MANAGEMENT SYSTEMS(12)
In a nutshell the general purpose of a database
is: • to provide a reliable repository for a possibly
large volume of well-structured data to a
possibly large community of end-users, who
may want to use the repository simultaneously
under acceptable performance requirements.
DATABASE MANAGEMENT SYSTEMS(12)
In a nutshell a database management system is: •
a software package that allows us to work with a
database. It is not the database itself, but
rather a batch of tools that help to access the
(any) database, by querying it, updating it,
making back-ups, and so on
DBMS - SUMMARY
STAGES INDATABASE DESIGN
1. Define the problem for which you require database
methods 2. Determine likely and appropriate data
requirements and their sources
3. Construct the data model to show the structure of the data
and the inter-relationships which require to be represented 4.
Translate the model into an appropriate database structure 5.
Implement the database
A good and thorough database design will prevent the need for
Stage 6.
6.Re-design and re-implement the database
18
STAGES INDATABASE
DESIGN
Maintenance
Support
Database Design
Requirements
Analysis
Operation Implementation
HSVG245 - Programming for Geomatics Applications 116
PHYSICAL ANDLOGICALDATABASEDESIGN
Physical Design
• This is concerned with the location of different parts of the
database within the computer file system
• The database may be spread across multiple physical disk
drives to balance I/O load or for security in the event of
media failure
• The physical database design is the responsibility of the
database administrator
Logical Design
• This represents the user’s view of the relationships
between datasetsstored in the database
• Logical and physical design should be quite separate, users should not
have to concern themselves with where and how their datasets are
physically stored (Martin, 1976)19
DATAANALYSIS
• The firststage in logical database design
• Data analysis allows a clearly defined conceptual model of the relationships between
different datasets to be developed, before the necessary resources are committed
to actual implementation of the database
• Without a clear understanding of these relationships, it is likely that the database will be
inefficient in design and will have a poor match to user’srequirements • A variety of
possible data analysis or data modelling techniques have been developed. The approach of
Chen (1976), based on the entity- relationship (ER) model, is probably the widest accepted
• Another important aspect of data analysistechniques is that they have
associated with them clear and unambiguous diagramming standards
• The ability to represent the structure of a complex set of data interrelationships in a
consistent graphical form is a powerful aid to understanding the problem and
communicating the results of the da20ta analysisto the intending users of the final
database
DATAANALYSISANDSYSTEM
DESIGN
21
PROBLEMS ENCOUNTERED INDATA MODELING
1.Understanding the problem
– A very clear understanding of the user requirements is required at the
outset.
2.Granularity of the model
– Too much detail?
3.Getting lost in the detail
– One approach is a ‘top-down’ design, where the major entities are defined and then a separate
Entity-Relationship model is constructed for each, which contains the detailed sub-entities etc.
4.Should derived data be included?
– E.g. balance on customer accounts, or total population for a census district
5.Dealing with time
– A multitude of issues relating with time e.g. are the data dynamic in time?
Continuous? Discrete intervals? 22
TYPESOFDBMS
• Hierarchical Systems
• Network Systems
• Relational Systems
• Object-Oriented Systems
• It is really only the relational systems which have an established place
in today’s commercial marketplace
• The first two types represent older systems, and the chronological order reflects an increasing
reliance on theoretical considerations, in terms of design, rather than pragmatic data
processing requirements
• Some software packages may have characteristics drawn from more than one of the above
types
• Generally, the category into which a software product falls gives an indication of how it
structures datasets and their inter-relationships at the logical design level • Choice of
system has a major impact on the way the data model maps onto the database structure
23
2. THE RELATIONAL DATA
MODEL
THE RELATIONAL DATA
MODEL(1)
THE RELATIONAL DATA
MODEL(1)
THE RELATIONAL DATA MODEL(2)
▪an attribute is a property
of an entity
– e.g. a person has an age,
a car has a colour
THE RELATIONAL DATA MODEL
(3)
THE RELATIONAL DATA MODEL(4) Self test
THE RELATIONAL DATA MODEL(5)
Self
test
THE RELATIONAL DATA MODEL(6)
THE RELATIONAL DATA MODEL(7)
At any point in time, a relation consist of two parts
the relation schema, which is a set of attributes with their
names and corresponding domains and
the relation instance, which is a set of tuples composed
of attribute values and domains at any given time.
THE RELATIONAL DATA MODEL(8)
1. The relation schema is normally constant: –
it does not change over time, or very rarely so.
2. But the relation instance changes all the time,
– every time when records are added, changed or
removed
THE RELATIONAL DATA MODEL(9)
THE RELATIONAL DATA MODEL(10)
THE RELATIONAL DATA MODEL(11)
THE RELATIONAL DATA MODEL(12)
THE RELATIONAL DATA
MODEL(13)
THE RELATIONAL DATA MODEL(14)
• A key
made
of
more
that
one
attribute is called a composite key
• In this example there is no guarantee that one
person will own one property
• Hence the use of a composite key to guard
against such eventuality
Self test
THE RELATIONAL DATA MODEL(15)
Self test
THE RELATIONAL DATA
MODEL(16)
Self test
THE RELATIONAL DATA
MODEL(17)
