Transcript Database - CyberInfrastructure and Geospatial Information Laboratory

Principles of GIS
Fundamental database concepts
Shaowen Wang
CyberInfrastructure and Geospatial Information Laboratory (CIGI)
Department of Geography
and
National Center for Supercomputing Applications (NCSA)
University of Illinois at Urbana-Champaign
January - February, 2011
Database Characteristics








Reliability
Integrity
Security
Concurrency
Data dependence
Distributed access
Interface
Self-describing
2
Database Applications





Business
Engineering
Medicine
Government
Etc.
3
DBMS






User interface and query language
Query compiler and optimizer
Constraint enforcer
Runtime database processor
Stored data manager
System catalog
– Metadata
4
Metadata



Data about data
XML (eXtensible Markup Language)
GML
– http://www.opengis.net/gml/
5
Database Transaction




Insert
Modify
Delete
Retrieve
6
Transaction Support

Concurrency
– Interleaving
– Lost update

Recovery control
– Atomicity
– Independence

DBMS operations
– Commit
– Rollback
7
Database Models



Relational
Object-oriented
Conceptual model
– Designers
– Machines
– Users
8
Entity-relationship model
(E-R)

Entity
– Type
– Instance
– Identifier

Relationship
– One-to-one
– One-to-many
– Many-to-many
9
Structured Query
Language (SQL)



Domain creation
Relation scheme creation
Data manipulation
– Data retrieval
10
Connecting to DB

% ssh [email protected]
– Enter password

Login to DB
– % psql -U username -d database_name
– Help: %psql --help
Some Postgres Commands

List all accessible databases
– # \l

Connect to a DB named 'tutorial'
– # \c tutorial

List all the tables in current DB
– # \dt, # \d (show all relations)

Quit
– # \q
SQL Commands

Create DB
– CREATE DATABASE dbname OWNER
rolename;
– E.g. # create database tutorial;
SQL Commands

Create Tables
– # create table test(key int, attr varchar(20),
value float);

Delete table
– # drop table test;
SQL Commands

Insert a row
– # insert into test values(1, 'attr0', 100);

Update table contents
– # update test set attr='attr1' where key=1;

Delete rows
– # delete from test where key=1;
SQL Commands

List contents of table
– # select * from test;
– # select * from test where attr='attr1';
Documentation

Postgres
– http://www.postgresql.org/docs/8.3/interact
ive/index.html
– http://www.postgresql.org/docs/8.3/interact
ive/sql-commands.html

An SQL Tutorial
– http://www.w3schools.com/sql/default.asp
Extended EntityRelationship Model

Sub-type
– Specialization

Super-type
– Generalization

Inheritance
18
Object-Orientation

Object
– State
– Behavior

Class
– Attributes
– Method
19
O-O Features

Encapsulation
– Reduces modeling complexity
– Promotes reuse

Inheritance and polymorphism
– Combats impedance mismatch
– Metaphorical power
20
Relational Databases




Attribute
Tuple
Relation scheme
Relation
21
Relation ID


Candidate key
Primary key
22
Operations on Relations


Project
Restrict
23
Relational Algebra

Derived relational operators
– Join


Natural join
Performance
24
Extensible RDBMS

RDBMS problems when handling spatial
data
– Data structure
– Performance
– Search
25
Importing data from CSV

Data format
– CSV file
– First is assumed to be column names
– Data values are separated by , and non
numeric values are quoted.
Importing data from CSV

Create insert file from csv file
– /srv/cigi/code/csv2insert.pl --csv-file
/srv/cigi/code/test.csv --output-file
$HOME/insert.sql --table-name test
– /srv/cigi/code/csv2insert.pl --help

Getting data to DB
– psql -U username -d database < insertfile
Logging in to the machine

% ssh [email protected]
– Login name: netid
– Password: your password

% psql -U username –d tutorial
– Login name: geog480
– Password: same
End of This Class
29