Filemaker MySQL PostGreSQL(PostGIS) Interbase Oracle ... - Ecotrust

townripeΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 9 μήνες)

1.249 εμφανίσεις

Introduction to Databases


Greg Robillard, Database Administrator

Ecotrust

greg@ecotrust.org

Introduction

What’s in a Database


How to Build a Database


How to Use the Data




Definitions


Columns



Rows (tuples)



Field (cell)







Database Programs


Microsoft Access (ESRI Personal Geodatabase)



SQL Server (ArcSDE)



Filemaker



MySQL



PostGreSQL(PostGIS)



Interbase



Oracle (ArcSDE, Spatial Blades)



DB2 (ArcSDE)


Relational Database


Organization



Efficiency



Security


Datatypes


Numeric



Textual



Spatial

Vector Storage

Raster Storage

Data Modeling

Alaska Fish Distribution

Remodeled Fish Distribution

Building the iMDB

Keys and Relationships

Key Types


Primary Key



Foreign Key




Table Relationship Types


One
-
to
-
one



One
-
to
-
many



Many
-
to
-
many

Anomalies


Update Anomalies



Deletion Anomalies



Insertion Anomalies


Normalization


1NF: every field is atomic



2NF: each table dependent on a single primary key



3NF: if an attribute isn't wholly dependent on the primary key,
remove it from the table

Not Normal!

Sample Data Structure

One to Many

Many to Many

One to One

SQL: Select


Select [field,field,field or *] from tablename WHERE some
condition is true


Example: SELECT firstname from t_talent WHERE
lastname = ‘Baldwin’

WHERE Clause


Any boolean statement (math or string):



WHERE year > 75


WHERE genre != ‘Delightful Romantic Comedy’


WHERE state IN (‘CA’,’WA’,’OR’)



Simple functions: Len(), Date_format, Round()


WHERE len(title) <= 75


Aggregate Functions


Let you group results


MIN(),MAX(),COUNT(),AVG(),SUM()



How Many minutes long is the Godfather Trilogy


SELECT sum(duration) from t_movie where movieID
in(10,11,12)



How Many Baldwin Brothers Are there?


SELECT count(firstname) WHERE lastname = ‘baldwin’


Spatial Functions


SELECT
area
(the_geom)/10000 AS hectares FROM bc_municipality
WHERE name = 'PRINCE GEORGE’






CREATE TABLE copper AS SELECT
intersection
(h.the_geom,
n.the_geom) AS intersection_geom,h.*FROM akmonpt AS n, akhydro
AS h WHERE h.the_geom && n.the_geom AND
intersects
(h.the_geom, n.the_geom)


Other Common SQL


DELETE


DELETE from TABLE WHERE Review = ‘Horrible’



UPDATE


UPDATE table SET Review = ‘Boring’ Where Rating =
‘PG’



INSERT


INSERT into
TABLE(fieldA,fieldB,fieldC)VALUES(‘value1’,’value2’,’val
ue3’)

Table Joins


Combine different items by linking a foreign key in one table to a
primary key in another

Inner Join

SELECT movie.title, genre.genreType WHERE
movie.genreID = genre.genreID

Movie Title

Genre

Evil Dead 2

horror

Star Wars

Sci
-
fi

Die Hard

Action

Left Outer Join

SELECT movie.title, genre.genreType WHERE
movie.genreID *= genre.genreID

Movie Title

Genre

Evil Dead 2

horror

Inside Man

Null

Rocky 99

Schlock

Star Wars

Sci
-
fi

Die Hard

Action

Right Outer Join

SELECT movie.title, genre.genreType WHERE
movie.genreID =* genre.genreID

Movie Title

Genre

Evil Dead 2

horror

Star Wars

Sci
-
fi

Null

Thriller

Null

Comedy

Die Hard

Action

Database Tools


Stored Procedures



Views



Triggers



Constraints



Transactions

Database Administration


Network Security



Backup/Restore



Internal Security