FOSS Relational Database and GeoDatabase
Part I
Marco Ciolli, Fabio Zottele
Dipartimento di Ingegneria Civile e Ambientale
Universita' degli Studi di Trento
Marco Ciolli, Dipartimento di Ingegneria Civile e Ambientale
Database relazionali FOSS e GeoDatabase
Introduzione
Definitions
RDBMS (Relational Data Base Management System): is a data management system
based on relational model (Edgard F. Codd1) and then Peter in 1976.
Data are atomized in little particles and tied with relations as it happens with the
concept of set theory in mathematic.
• Data are presented to the user as relations (tables): a collectio of tables each one
is composed by rows and columns
• Funcions and operators are provided to manage data in table format
non relational DBMS based on hierarchic model exist.
RDBMS are today the most used DBMS, but still a standard defining the meaning of
"relational" referring to database does not exist.
1
”A Relational Model of Data for Large Shared Data Banks”,1970
Database relazionali FOSS e GeoDatabase
Introduzione
Relational model
In relational database, data are represented as relations: data are managed with relational
algebra operators.
The domain or data type is the set of value that a certain attribute can assume.
A tuple is the non ordered set of the attribute value.
therefore:
• The table is the accepted graphic representation to represent the relation;
• All informmation are values inserted into relations (tables)
• a relational database is the set of relations that contain values; the result of any query can be represented
as a relation (tables).
Database relazionali FOSS e GeoDatabase
Introduzione
Relational model
Data type: vales of the same sort (integer, string, date,
true/false...).
Type name: string that identifies the tipe (int, char,
date, boolean...).
Field: the term that indicates column.
Relation: its graphic representation is the table.
Record: indicates the row.
Value (of an attribute): is the value of a cell identified by a
specific couple row-column as for example "Mario Rossi" or
"2006".
Database relazionali FOSS e GeoDatabase
Introduzione
Relational model
The structure of a table is specified
ad a column list, each one has a
unique name and a domain, that is a
set of accepted values.
CREATE TABLE films (
code
char(5),
title
varchar(40) NOT NULL,
did
integer NOT NULL,
date_prod
date,
kind
varchar(10),
len
interval hour to
minute
);
Database relazionali FOSS e GeoDatabase
Introduzione
Constraints
Constraints affect the values that
aparticular kind of data can assume.
The same domain can be seen both as a
constraint and as definition of the
relation.
Primary key is useful to identify rows
and tables. It can be specified once for
each table and one or more attributes
acan be selected as primary key.
CREATE TABLE laghi
(
gid serial NOT NULL,
tronco varchar(3),
rasta varchar(10),
rtronco varchar(13),
the_geom geometry,
CONSTRAINT laghi_pkey PRIMARY KEY (gid),
CONSTRAINT enforce_dims_the_geom CHECK
(ndims(the_geom) = 2),
CONSTRAINT enforce_geotype_the_geom CHECK
(geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom
IS NULL),
CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) =
26591)
)
WITHOUT OIDS;
ALTER TABLE laghi OWNER TO gis;
-- Index: laghi_the_geom_gist
-- DROP INDEX laghi_the_geom_gist;
CREATE INDEX laghi_the_geom_gist
ON laghi
USING gist
(the_geom);
Database relazionali FOSS e GeoDatabase
Introduzione
Relational model: constraints
the tuple constraint is a constraint
that affect each tuple and may
restict the attribute (NOT NULL or
DEFAULT).
FLUSSI
VELOCITÀ
TEMPERATURA
data
tipo_giorno
id_s trada
flusso_h0_1
.
.
.
flusso_h23_0
lunghezza
data
tipo_giorno
id_s trada
velocità_h0_1
.
.
.
velocità_h23_0
data
temp_h0_1
.
.
.
temp_h23_0
SUDDIVISIONE
PARCO
id_strada
clas se
numero_veicoli
percentuale
STRADE
id_s trada
id_utopia
nome_strada
lunghezza
CLASSICOPERT
clas s e
id_com bustibile
codice_settore
classificazione_copert
tipo_legislativo_veicolo
tipo_uso_veicolo
periodo
codice_canister
frazione_ie
fattore_evaporativo
COMBUSTIBILI
id_com bus tibile
nome_combustibile
id_s tagione
nome_stagione
frazione_s
rhc
rvp
COSTANTI
ltrip
coeff _1
.
.
.
coeff _5
INQUINANTI
id_inquinante
nome_inquinante
un_misura_1
un_misura_2
un_misura_3
COEFFCALDO
COEFFFREDDO
id_inquinante
clas s e
coeff_1
.
.
.
coeff_30
id_inquinante
clas se
bcorr
coeff_1
.
.
.
coeff_31
Database relazionali FOSS e GeoDatabase
Introduzione
Index
Index is a data structure that betters speed and operation of relational search and
query.
One ore more columns can be used to create indexes.
Database relazionali FOSS e GeoDatabase
Introduzione
SQL
SQL (Structured Query Language) is the language used to access and maange data in
(R)DBMS, to create and modify database struture and to guarantee the access to the objects
contained in a (R)DBMS.
Even if SQL is an ANSI and ISO standard, many database supports language extensions, that
is dialects.
Present standard is SQL:2008
The most common operation in SQL is query: by means of SELECT operator data are
extracted from one ore more relations (JOIN). The command does not have permanent effects
on data (INSERT,UPDATE,DELETE).
Transactions are repeated queries that must be executed as a group.
Database relazionali FOSS e GeoDatabase
Introduzione
Relational model: ACID
ACID Atomicity, Consistency, Isolation and Durability:
• atomicity: transaction cannot be divided, it must be completely executed or not
executed at all;
• consistency: when a transaction begins the database must be in a consistent state
and when transaction ends the database must be in a consistent state, that is it must
not violate integrity rules, and inconsistency (contradicting informations) in DB must be
absent;
• isolation: each transaction must be executed independently from other transactions,
the failure of a transaction must not interfere with other transactions executed in the
very same time;
• durability: so called also persistency, it refers to the fact that once a transaction has
requested a commit work, the change must persist. To avoid that during the time that
DB prepares modifications and the time that DB writes modifications data lost may
occur due to malfunctions log register are written where all DB operations are
registered.
Database relazionali FOSS e GeoDatabase
Introduzione
dBASE
http://www.dbase.com/
SQLite
http://www.sqlite.org/
Database relazionali FOSS e GeoDatabase
Introduzione
MICROSOFT SQLServer
http://www.microsoft.com/italy/server/sql/default.mspx
Database relazionali FOSS e GeoDatabase
Introduzione
MySQL
http://www.mysql.com/
Database relazionali FOSS e GeoDatabase
Introduzione
PostgreSQL
http://www.postgresql.org/
Database relazionali FOSS e GeoDatabase
Introduzione
ORACLE
http://www.oracle.com/index.html
