week8 gis rdbms
Post on 10-Apr-2018
228 Views
Preview:
TRANSCRIPT
-
8/8/2019 Week8 Gis Rdbms
1/34
Understanding RDBMSUnderstanding RDBMSUnderstanding RDBMSUnderstanding RDBMS
Keith T. Weber
GIS DirectorISU-GIS Training and Research Center
-
8/8/2019 Week8 Gis Rdbms
2/34
RDBMSRDBMSRDBMSRDBMS Relational Database Management
System
-
8/8/2019 Week8 Gis Rdbms
3/34
DatabasesDatabasesDatabasesDatabases
The I in GIS
-
8/8/2019 Week8 Gis Rdbms
4/34
Database software...Database software...Database software...Database software... Light Duty
Medium Duty
Heavy Duty
-
8/8/2019 Week8 Gis Rdbms
5/34
Database software...Database software...Database software...Database software... Light Duty
Medium Duty
Heavy Duty
-
8/8/2019 Week8 Gis Rdbms
6/34
IBM DB2 UDBIBM DB2 UDBIBM DB2 UDBIBM DB2 UDB
The GIS Centers
heavy hitter- IBM
DB2, UniversalDatabase
-
8/8/2019 Week8 Gis Rdbms
7/34
Spreadsheets vs. DatabasesSpreadsheets vs. DatabasesSpreadsheets vs. DatabasesSpreadsheets vs. Databases
Integrity! Structure
-
8/8/2019 Week8 Gis Rdbms
8/34
RDBMS Concepts andRDBMS Concepts and
TermsTerms
RDBMS Concepts andRDBMS Concepts and
TermsTerms
-
8/8/2019 Week8 Gis Rdbms
9/34
IndependenceIndependenceIndependenceIndependence
Physical
Logical
-
8/8/2019 Week8 Gis Rdbms
10/34
IntegrityIntegrityIntegrityIntegrity
Important for consistency and transaction
management.
Types: Domain
Redundancy
Constraint
Entity
Referential
Cascading or non-cascading
-
8/8/2019 Week8 Gis Rdbms
11/34
K
ey FieldsK
ey FieldsK
ey FieldsK
ey Fields
Unique Identifiers
(?) Primary key
Foreign key
AKA- Relate fields.
-
8/8/2019 Week8 Gis Rdbms
12/34
RDBMS StructureRDBMS StructureRDBMS StructureRDBMS Structure
-
8/8/2019 Week8 Gis Rdbms
13/34
Database TablesDatabase TablesDatabase TablesDatabase Tables
Database
Table1 Table2 Table3
-
8/8/2019 Week8 Gis Rdbms
14/34
Table StructureTable StructureTable StructureTable Structure
Column 1
(Field 1)
Column 2
(Field 2)
Row 1
(Record 1)
Value
Row 2(Record 2)
-
8/8/2019 Week8 Gis Rdbms
15/34
Data Value TypesData Value TypesData Value TypesData Value Types
Type Name Storage Occupied/ data value Valid Domain Range
Short Integer 2 bytes -35768 to 32767
Long Integer 4 bytes -2147483648 to 2147483647
Float 4 bytes Any numberfrom n-45 ton38
Double 8 bytes Any numberfrom n-324 ton308
Text (stri
ng) 10 + max. length
= bytes Any alph
anumeric
ch
ara
cte
rs
Date 8 bytes Jan 1, 100 to Dec. 31 9999
LOB (variant) 22 + max. length = bytes Any alphanumericcharacters
-
8/8/2019 Week8 Gis Rdbms
16/34
Basic Steps in DatabaseBasic Steps in Database
DesignDesign
Basic Steps in DatabaseBasic Steps in Database
DesignDesign Understand and document thebusiness needs. Problem statement
Business object types
Business relationships Business constraints
Create an ERM
Data and process inventory
Develop tuple types
Tuple types to tables
Integrity
Populate the database
-
8/8/2019 Week8 Gis Rdbms
17/34
A Scenario...A Scenario...A Scenario...A Scenario...
Develop a GIS-
Based Tourism
database forSoutheast Idaho.
-
8/8/2019 Week8 Gis Rdbms
18/34
Document the businessDocument the business
needsneeds
Document the businessDocument the business
needsneeds What problem or issue is this database
going to address?
-
8/8/2019 Week8 Gis Rdbms
19/34
DINING
The Preliminary ERMThe Preliminary ERMThe Preliminary ERMThe Preliminary ERM
Symbolized.
Standard Representation
Attribute Representation
Entity Instance Representation
DINING
K Restaurant Number
Name
Type of food
DINING
K Restaurant Number: 126
Name: Burger King
Type of food: Fast
-
8/8/2019 Week8 Gis Rdbms
20/34
RelationshipsRelationshipsRelationshipsRelationships
Determine the relationships between
your entity types.
Add these to the ERM
-
8/8/2019 Week8 Gis Rdbms
21/34
Define the ListDefine the ListDefine the ListDefine the List
Database Dictionary
Restaurant_Name
Food_Type
Cost_Mean
The name ofthe restaurant
Categories offood (e.g., 1 =Continental, 2= Fast food,etc.)
The averagecost of allregular menuitems.
-
8/8/2019 Week8 Gis Rdbms
22/34
Develop Tuple TypesDevelop Tuple TypesDevelop Tuple TypesDevelop Tuple Types
Use your ERM with relationships
Perform a Walk-through exercise
Simulate information is being added/used
in your database.
Symbolize using Attribute
Representation
-
8/8/2019 Week8 Gis Rdbms
23/34
Tuple Types to TablesTuple Types to TablesTuple Types to TablesTuple Types to Tables
ENTITY TYPES
RELATIONSHIP TYPES
TUPLE TYPES
TABLES
-
8/8/2019 Week8 Gis Rdbms
24/34
NormalizationNormalizationNormalizationNormalization
First-Fifth Form Normal (1FN,
2FN,5FN)
Academic
Applied
-
8/8/2019 Week8 Gis Rdbms
25/34
1FN1FN1FN1FN
All values are atomic
Single cell contains single data value
-
8/8/2019 Week8 Gis Rdbms
26/34
2FN2FN2FN2FN
Satisfy 1FN and
Redundant data must be eliminated
How?
Example: Puppy_ID, Trick_ID, Trick_Name
-
8/8/2019 Week8 Gis Rdbms
27/34
3FN3FN3FN3FN
Satisfy 1NF and 2FN and
No non-key attributes are dependent on
other non-key attributes.
Example: Appointment_ID, Name, Date,
Time, Species
-
8/8/2019 Week8 Gis Rdbms
28/34
AfterNormalizationAfterNormalizationAfterNormalizationAfterNormalization
New tuple types will be created.
New tables will be planned.
Many-many relationships will behandled using associative tables (bridge
tables).
-
8/8/2019 Week8 Gis Rdbms
29/34
DeDe--NormalizationNormalizationDeDe--NormalizationNormalization
What? Is this heresy?
-
8/8/2019 Week8 Gis Rdbms
30/34
Designing the ActualDesigning the Actual
RDBMSRDBMS
Designing the ActualDesigning the Actual
RDBMSRDBMS Visual modeling based upon your ERM
and Tuple type model.
Implementation of integrity rules basedupon your business constraints.
-
8/8/2019 Week8 Gis Rdbms
31/34
Coming Up NextComing Up NextComing Up NextComing Up Next
RDBMS Design
Hands-on exercise
-
8/8/2019 Week8 Gis Rdbms
32/34
Populate...Populate...Populate...Populate...
Questions and concerns to revisit
Null data
Reporting discrepancies and variations
Measuring or estimating methods
Client utility/efficiency
-
8/8/2019 Week8 Gis Rdbms
33/34
The Last Step?The Last Step?The Last Step?The Last Step?
-
8/8/2019 Week8 Gis Rdbms
34/34
Questions?Questions?Questions?Questions?
Your assignment
Read the handout to familiarize yourself
with RDBMSs
Complete the On-line SQL course
Complete the Exercise
top related