59 qustions ( GIS )

lym503829712

GIS-Design_lecture5key.pdf

Home >Information Systems homework help >59 qustions ( GIS )

Design of Geographic Information Systems

class 5

Thomas Bittner bittner3@buffalo.edu

�2

= mental models

Reality

refers-to Cat Katze

rules of modelization

refers-to

communication

Data Model

Database

Reports & Visualizations

Digital

Representation Analysis &

Communication

Objects, Attributes, Relations

Files, Tables & Databases

Displays & Analyses

REAL WORLD

Real World Model

Mental

representation

mental models

phenomena in reality,

systems

Components of computerized information systems

P. Bolstad

Data Model

Database

Reports & Visualizations

Digital

Representation Analysis &

Communication

Objects, Attributes, Relations

Files, Tables & Databases

Displays & Analyses

Formal systems Logical inference Query processing SQL ...

Components of computerized information systems

A logic-based view of computerized information systems

• City(Los Angeles) • FedState(California) • PartOf(LosAngeles, California) • part_of(x,y) implies not part_of(y,x) • ...

automatically compute formal proofs • retrieve all facts that match a certain query • retrieve implicit facts • find inconsistencies

Axioms of a Formal theory logical deductions using the axioms.

Data Model

Database

Reports & Visualizations

Digital

Representation Analysis &

Communication

Objects, Attributes, Relations

Files, Tables & Databases

Displays & Analyses

Components of computerized information systems

Ground facts: • City(Los Angeles) • Polluted(Los Angeles) • FedState(California) • PartOf(LosAngeles, California) • ...

Data Model

Database

Reports & Visualizations

Digital

Representation Analysis &

Communication

Objects, Attributes, Relations

Files, Tables & Databases

Displays & Analyses

Components of computerized information systems

Ground facts: • City(Los Angeles) • Polluted(Los Angeles) • FedState(California) • PartOf(LosAngeles, California) • ...

which kinds of entities, properties, relations, do we represent?

Data Model

Database

Reports & Visualizations

Digital

Representation Analysis &

Communication

Objects, Attributes, Relations

Files, Tables & Databases

Displays & Analyses

REAL WORLD

Real World Model

Mental

representation

mental models

phenomena in reality,

systems

Components of computerized information systems

P. Bolstad

From conceptual (mental) models to data models

How to communicate conceptual (mental) models among human beings in a standardized way?

Data Model

Database

Reports & Visualizations

Digital

Representation Analysis &

Communication

Objects, Attributes, Relations

Files, Tables & Databases

Displays & Analyses

REAL WORLD

Real World Model

Mental

representation

mental models

phenomena in reality,

systems

Components of computerized information systems

P. Bolstad

based on slides by Shashi Shekhar

How to communicate conceptual models easily The ER Model

Entities  Examples: Forest, Road, Manager, ... Entities are characterized by Attributes  Example: Forest has attributes of name, elevation, etc. An Entity is related to another Entity through relationships.  Roads allow access to Forest interiors.  This relationship may be named “Accesses”

3 basic components

based on slides by Shashi Shekhar

Relationship Types

Relationships can be categorized by cardinality constraints other properties, e.g. number of participating entities

• Binary relationship: two entities participate  

Types of Cardinality constraints for binary relationships One-One: An instance of an entity type relates to a unique instance of other entity type. Many-One: Many instances of an entity relate to an instance of an other. One-Many: one instance of an entity type relates to multiple entities of another entity type Many-Many: Many instances of one entity type relate to multiple instances of another.

based on slides by Shashi Shekhar

Relationship Types Exercise: Identify type of cardinality constraint for following:

Many facilities belong to a forest. Each facility belongs to one forest. A manager manages 1 forest. Each forest is managed by 1 manager. A river supplies water to many facilities. A facility is supplied with water from many rivers.

based on slides by Shashi Shekhar

ER Diagrams Graphical Notation

Concept Symbol

Entities

Attributes

Multi-valued Attributes

Relationships

Cardinality of Relationship 1:1, M:1, M:N

•ER Diagrams are graphic representation of ER models •Several different graphic notation are used

•We use a simple notation summarized below

•Example ER Diagram for Forest example in next slide

based on slides by Shashi Shekhar

Relationship Types Example: Represent as an ER-diagram:

Many facilities belong to a forest. Each facility belongs to one forest.

Entities

based on slides by Shashi Shekhar

Relationship Types Example: Represent as an ER-diagram:

Many facilities belong to a forest. Each facility belongs to one forest.

facility forest

Entities

based on slides by Shashi Shekhar

Relationship Types Example: Represent as an ER-diagram:

Many facilities belong to a forest. Each facility belongs to one forest.

facility forest

Relationship

based on slides by Shashi Shekhar

Relationship Types Example: Represent as an ER-diagram:

Many facilities belong to a forest. Each facility belongs to one forest.

facility forestbelongs

Relationship

based on slides by Shashi Shekhar

Relationship Types Example: Represent as an ER-diagram:

Many facilities belong to a forest. Each facility belongs to one forest.

facility forestbelongs

Cardinality constrains

based on slides by Shashi Shekhar

Relationship Types Example: Represent as an ER-diagram:

Many facilities belong to a forest. Each facility belongs to one forest.

facility forestbelongs M

Cardinality constrains

based on slides by Shashi Shekhar

Relationship Types Example: Represent as an ER-diagram:

Many facilities belong to a forest. Each facility belongs to one forest.

facility forestbelongs M 1

Cardinality constrains

based on slides by Shashi Shekhar

Relationship Types Exercise: Represent as an ER-diagram:

A manager manages 1 forest. Each forest is managed by 1 manager. A river supplies water to many facilities. A facility is supplied with water from many rivers.

based on slides by Shashi Shekhar

Relationship Types Exercise: Represent as an ER-diagram:

A manager manages 1 forest. Each forest is managed by 1 manager.

manager forestmanages 1 1

based on slides by Shashi Shekhar

Relationship Types Exercise: Represent as an ER-diagram:

A river supplies water to many facilities. A facility is supplied with water from many rivers.

river facilitysupplies water

M N

based on slides by Shashi Shekhar

ER Diagram for “State-Park”

Fig 2.4

based on slides by Shashi Shekhar

ER Diagram for “State-Park”

•Exercise: •List the entities, attributes, relationships in this ER diagram •Identify cardinality constraint for each relationship. •How many roads “Access” a “Forest_stand”? (none, one, many ?) !28

ER Diagram, EXAMPLE 2

2.2.2 Logical Data Model: The Relational Model

Data Model

Database

Reports & Visualizations

Digital

Representation Analysis &

Communication

Objects, Attributes, Relations

Files, Tables & Databases

Displays & Analyses

REAL WORLD

Real World Model

Mental

representation

mental models

phenomena in reality,

systems

Components of computerized information systems

P. Bolstad

integer domain

real domain

alpha- numeric domain (a string)

Record (or tuple)

Attribute (or item or field)

Common components of a database: 2.2.2 Logical Data Model: The Relational Model

based on slides by Shashi Shekhar

2.2.2 Logical Data Model: The Relational Model

Relational model is based on set theory Main concepts

Domain: a set of values for a simple attribute Relation: subset of the cross-product of a set of domains

• Represents a table, i.e. collection of rows (tuples) • The set of columns (i.e. attributes) are same for each row

based on slides by Shashi Shekhar

Relational Schema

Schema of a Relation Enumerates columns.

based on slides by Shashi Shekhar

key

Relational Schema

Schema of a Relation Enumerates columns. Identifies Primary Keys :

• one or more attributes that uniquely identify each row within a table

key

based on slides by Shashi Shekhar

Relational Schema

foreign key

key

Schema of a Relation Enumerates columns. Identifies Primary Keys :

• one or more attributes uniquely identify each row within a table

Identifies Foreign keys • R’s attributes which form primary key of another relation S • Value of a foreign key in any tuple of R match value of a key in some row of S 

based on slides by Shashi Shekhar

Relational Schema

Relational schema of a database collection of schemas of all relations in the database

based on slides by Shashi Shekhar

Relational Schema

Relational schema of a database collection of schemas of all relations in the database

A blue print summary drawing of the database table structures Allows analysis of storage costs, data redundancy, querying capabilities Some databases were designed as relational schema in 1980s Nowadays, databases are designed as ER models and relational schema is generated via CASE tools

based on slides by Shashi Shekhar

Relational Schema Example

Fig 2.5

•Exercise: •Identify relations with

•primary keys •foreign keys •other attributes

based on slides by Shashi Shekhar

Relational Schema Example

Fig 2.5

•Exercise: •Identify relations with

•primary keys •foreign keys •other attributes

based on slides by Shashi Shekhar

Relational Schema Example

Fig 2.5

•Exercise: •Identify relations with

•primary keys •foreign keys •other attributes

based on slides by Shashi Shekhar

Relational Schema Example

Fig 2.5

•Exercise: •Identify relations with

•primary keys •foreign keys •other attributes

based on slides by Shashi Shekhar

More on Relational Model Integrity Constraints

Key: Every relation has a primary key. Entity Integrity: Value of primary key in a row is never undefined Referential Integrity: Value of an attribute of a Foreign Key must appear as a value in the primary key of another relationship or must be null.

Normal Forms (NF) for Relational schema Reduce data redundancy and facilitate querying 1st NF: Each column in a relation contains an atomic value. 2nd and 3rd NF: Values of non-key attributes are fully determined by the values of the primary key, only the primary key, and nothing but the primary key. Other normal forms exists but are seldom used Translating a well-designed ER model yields a relational schema in 3rd NF

• satisfying definition of 1st, 2nd and 3rd normal forms

based on slides by Shashi Shekhar

!43

Mapping ER to Relational

Data Model

Database

Reports & Visualizations

Digital

Representation Analysis &

Communication

Objects, Attributes, Relations

Files, Tables & Databases

Displays & Analyses

REAL WORLD

Real World Model

Mental

representation

mental models

phenomena in reality,

systems

based on slides by Shashi Shekhar

!44

2.2.3 Mapping ER to Relational

•Important translation rules  

•Entities become tables which have   primary keys)

based on slides by Shashi Shekhar

!45

2.2.3 Mapping ER to Relational

•Important translation rules •Attributes become columns in the tables

based on slides by Shashi Shekhar

!46

2.2.3 Mapping ER to Relational

•Important translation rules

•Relationships (1:1, 1:N) become     foreign keys

based on slides by Shashi Shekhar

!47

2.2.3 Mapping ER to Relational

•Important translation rules •M:N Relationships become tables   (without primary keys)

•containing foreign keys of    relations from participating entities

Query languages - SQL

Based on slides by Shashi Shekhar

What is a query?

What is a Query ? A query is a “question” posed to a database Queries are expressed in a high-level declarative manner

• Algorithms needed to answer the query are not specified in the query

Examples: Mouse click on a map symbol (e.g. road) may mean

• What is the name of road pointed to by mouse cursor ?

Typing a keyword in a search engine (e.g. google, yahoo) means • Which documents on web contain given keywords?

SELECT S.name FROM Senator S WHERE S.gender = ‘F’ means • Which senators are female?

Based on slides by Shashi Shekhar

What is a query language?

What is a query language? A language to express interesting questions about data A query language restricts the set of possible queries

Examples: Natural language, e.g. English, can express almost all queries Structured Query Language(SQL)

• Can express common data intensive queries • Not suitable for recursive queries

Graphical interfaces, e.g. web-search, mouse clicks on a map • can express few different kinds of queries

Based on slides by Shashi Shekhar

What is SQL?

SQL - General Information is a standard query language for relational databases It support logical data model concepts, such as relations, keys, ... Supported by major brands, e.g. IBM DB2, Oracle, MS SQL Server, Sybase, ... 3 versions: SQL1 (1986), SQL2 (1992), SQL 3 (1999) Can express common data intensive queries SQL 1 and SQL 2 are not suitable for recursive queries

SQL and spatial data management ESRI Arc/Info included a custom relational DBMS named Info Other GIS software can interact with DBMS using SQL

• using open database connectivity (ODBC) or other protocols In fact, many software use SQL to manage data in back-end DBMS And a vast majority of SQL queries are generated by other software Although we will be writing SQL queries manually!

Based on slides by Shashi Shekhar

Three Components of SQL?

Data Definition Language (DDL) Creation and modification of relational schema Schema objects include relations, indexes, etc.

Data Manipulation Language (DML) Insert, delete, update rows in tables Query data in tables

Data Control Language (DCL) Concurrency control, transactions Administrative tasks, e.g. set up database users, security permissions

Based on slides by Shashi Shekhar

Creating Tables in SQL

• Table definition • “CREATE TABLE” statement • Specifies table name, attribute names and data types • Create a table with no rows.

CREATE TABLE river (name varchar(35), origin varchar(35), length int, shape char(15), primary key (name) );

Based on slides by Shashi Shekhar

Populating Tables in SQL

• Adding a row to an existing table • “INSERT INTO” statement • Specifies table name, attribute names and values formulate as SQL statement:  

insert into the table River a row with the values name: ‘Mississippi’, country: ‘USA’, length: 6000

INSERT INTO River(Name, Origin, Length) VALUES(‘Mississippi’, ‘USA’, 6000)

Based on slides by Shashi Shekhar

SELECT Statement- General Information

• Clauses •SELECT specifies desired columns •FROM specifies relevant tables •WHERE specifies qualifying conditions for rows •ORDER BY specifies sorting columns for results •GROUP BY, HAVING specifies aggregation and statistics

•Operators and functions •arithmetic operators, e.g. +, -, … •comparison operators, e.g. =, <, >, BETWEEN, LIKE… •logical operators, e.g. AND, OR, NOT, EXISTS, •set operators, e.g. UNION, IN, ALL, ANY, … •statistical functions, e.g. SUM, COUNT, ... • many other operators on strings, date, currency, ...

The theory behind SQL: Relational algebra

vIntroduction nCodd specified an algebra that takes relations as inputs and returns relations as output nAlgebra combines or splits tables either by rows or by columns based on a fixed set of operations n8 primary operators—restrict, project, union, intersection, difference, product, join and divide are combined in queries to select specific records and items

Relational Algebra

Eight Fundamental Operations (a) Restrict (query) – subset by rows

Eight Fundamental Operations (b) Project – subset by columns

Eight Fundamental Operations (c) Product – all possible combinations

Eight Fundamental Operations (d) Divide – inverse of product

Eight Fundamental Operations (a) Union – combine top to bottom

Eight Fundamental Operations Intersect – row overlap

Eight Fundamental Operations Difference – row non-overlap

Eight Fundamental Operations Join (relate) – combine by a key column

Relatio nal Algebra

vOperators nRestrict—also known as a table query; serves up records based on values of one or more attributes (horizontal subsetting) nProject—returns entire columns to be output as a new table, in cases where only a few table attributes are important, to hasten up speed (vertical subsetting) nProduct—combines every record of one table with all records of another table (used when there is no common attribute) n Divide—used to compare a (dividend) attribute value list with a (dividend) list; outputs only those divisor values which have records with all values of divisor in a third table.

Relational Algebra (summary)

vOperators

nUnion—combines tables to return records found in either or in both tables (equivalent of OR Boolean operator) nIntersection—combines tables to return records found only in both tables (equivalent of AND Boolean operator) nDifference—returns records found in first but not in second nJoin—joins two tables through values found in keys to output a bigger table with more attributes

Relational Algebra (summary)

based on slides by Shashi Shekhar

Normal Forms (NF) for Relational schema

!69

based on slides by Shashi Shekhar

More on Relational Model Integrity Constraints

• satisfying definition of 1st, 2nd and 3rd normal forms

Relational Database Model

v1st Normal Form nNo repetition of attributes i.e. attribute cannot have more than one value for the same row

Relational Database Model

v1st Normal Form nNo repetition of attributes i.e. attribute cannot have more than one value for the same row

Functional Dependency Knowing the value of an item (or items) means you know the values of other items in the row e.g., if we know the person’s name, then we know the address In our example, if we know the Owner-ID, we know the Owner name, and Owner address:

Own-ID - > Own_name

Own-ID - > Owner-address

2NF if: table is in 1NF and if every non-key attribute is functionally dependent on the primary key

2nd Normal Forms in Relational Tables v2nd Normal Form

nTo go from 1NF to 2NF, functionally dependent attributes are identified and are placed in different tables with one key each

???

Moving from First Normal Form (1NF to Second Normal Form (2NF), we need to:

Identify functional dependencies

Place in separate tables, one key per table

3rd Normal Forms in Relational Tables

Remove transitive functional dependencies

A transitive functional dependency is when A -> B (if we know A, then we know B) and B -> C (if we know B, then we know C) So A -> C (if we know A, then we know C).

To be in 3NF, we must identify all transitive functional dependencies, and remove them, typically by splitting the table(s) that contain them

In our example, one transitive functional dependency: Parcel-ID -> Tship-ID, Alderman Tship-ID -> Tship_name, Thall_add

Relational Database Model Normal Forms Summary

No repeat columns (create new records such that there are multiple records per entry)

Split the tables, so that all non-key attributes depend on a primary key.

Split tables further, if there are transitive functional dependencies. This results in tables with a single, primary key per table.

Normal Forms Are Good Because:

It reduces total data storage

Changing values in the database is easier

It “insulates” information – it is easier to retain important data

Many operations are easier to code

Summary Data Model

Database

Reports & Visualizations

Digital

Representation Analysis &

Communication

Objects, Attributes, Relations

Files, Tables & Databases

Displays & Analyses

REAL WORLD

Real World Model

Mental

representation

mental models

phenomena in reality,

systems

Components of computerized information systems

P. Bolstad

based on slides by Shashi Shekhar

ER Diagram for “State-Park”

Fig 2.4

based on slides by Shashi Shekhar

ER Diagrams Graphical Notation

Concept Symbol

Entities

Attributes

Multi-valued Attributes

Relationships

Cardinality of Relationship 1:1, M:1, M:N

•ER Diagrams are graphic representation of ER models •Several different graphic notation are used

•We use a simple notation summarized below

•Example ER Diagram for Forest example in next slide

integer domain

real domain

alpha- numeric domain (a string)

Record (or tuple)

Attribute (or item or field)

Common components of a database: 2.2.2 Logical Data Model: The Relational Model

based on slides by Shashi Shekhar

Relational Schema

Relational schema of a database collection of schemas of all relations in the database

based on slides by Shashi Shekhar

Relational Schema

foreign key

key

Schema of a Relation Enumerates columns. Identifies Primary Keys :

• one or more attributes uniquely identify each row within a table

Identifies Foreign keys • R’s attributes which form primary key of another relation S • Value of a foreign key in any tuple of R match value of a key in some row of S 

based on slides by Shashi Shekhar

!90

2.2.3 Mapping ER to Relational

•Important translation rules  

•Entity becomes Relation

Based on slides by Shashi Shekhar

What is SQL?

SQL and spatial data management ESRI Arc/Info included a custom relational DBMS named Info Other GIS software can interact with DBMS using SQL

Relatio nal Algebra

Normal Forms Summary

No repeat columns (create new records such that there are multiple records per entry)

Split the tables, so that all non-key attributes depend on a primary key.

Split tables further, if there are transitive functional dependencies. This results in tables with a single, primary key per table.