1 / 30

Geography 357 Geographic Information Systems

Geography 357 Geographic Information Systems. Realtional Databases. 1. Relational data model. Composed of a set of tables called relations Records (rows) in the table are called tuples Fields (columns) in the table are called domains We will use the terms tables, records, and fields.

debra
Download Presentation

Geography 357 Geographic Information Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Geography 357Geographic Information Systems Realtional Databases 1

  2. Relational data model • Composed of a set of tables called relations • Records (rows) in the table are called tuples • Fields (columns) in the table are called domains • We will use the terms tables, records, and fields

  3. Relational data model • Each record represents a logical entity (e.g. a student) (or a relationship) • Each field represents an attribute (property) of the logical entity Student ID Last First Grade Class 1 Wood Bob C Geog357 2 Kent Chuck B Geog115 3 Smith Jane A Geog357 4 Boone Dan B Geog357

  4. Relational data model • Each table has a primary key, one field (or a combination of fields) that has a unique value for each and every record in the table Student ID Last First Grade Class ID is the primary key in this table (two students may share either a last or first name) 1 Wood Bob C Geog357 2 Kent Chuck B Geog115 3 Smith Jane A Geog357 4 Boone Dan B Geog357

  5. Relational data model • Tables can be related (joined or linked) together based on their keys. Student Class ID Last First Grade Class Name #Stud Instructor 1 Wood Bob C Geog357 2 Kent Chuck B Geog115 3 Smith Jane A Geog357 4 Boone Dan B Geog357 Geog357 48 Jones Geog115 120 Brower Geog20 120 Fountain

  6. Relational data model Primary key Foreign key Primary key Student Class ID Last First Grade Class Name #Stud Instructor 1 Wood Bob C Geog357 2 Kent Chuck B Geog115 3 Smith Jane A Geog357 4 Boone Dan B Geog357 Geog357 48 Jones Geog115 120 Brower Geog20 120 Mennis

  7. Relational data model Student Class ID Last First Grade Class Name #Stud Instructor 1 Wood Bob C Geog357 2 Kent Chuck B Geog115 3 Smith Jane A Geog357 4 Boone Dan B Geog357 Geog20 120 Brower Geog115 120 Jones Geog357 48 Jones Instructor Name Office Jones 332 Brower 517

  8. Relational data model • Normalization • process of structuring tables and table relationships in a logical way that minimizes data redundancy • 3 rules or steps in normalization • first normal form • second normal form • third normal form

  9. Functional Dependency • Main concept associated with normalization. • Functional Dependency • Describes the relationship between attributes in a relation. • For example, B is functionally dependent on A (denoted A  B), if each value of A in relation R is associated with exactly one value of B in relation R. • Diagrammatic representation.

  10. Relational data model • First normal form • only one value per field for each record Violates first normal form Student ID Last First Grades Classes 1 Wood Bob C, B Geog357, Geog20 2 Kent Chuck B, D Geog115, Geog356 3 Smith Jane A, B Geog357, Geog20 4 Boone Dan B, A Geog357, Geog455

  11. Relational data model • Second normal form • each non-primary key field must be totally dependent on the entire primary key (and not on only part of the primary key) primary key Student Year Last First Grade Status Violates second normal form because Status is dependent only on Year, not on Year/Last/First 2 Wood Bob C sophomore 4 Kent Chuck B senior 3 Smith Jane A junior 3 Boone Dan B junior

  12. Relational data model • To resolve second normal form violation - create separate tables Student Year primary key primary key Last First Grade Year Last First Grade Year Status Wood Bob C 2 Kent Chuck B 4 Smith Jane A 3 Boone Dan B 3 Wood Bob C Kent Chuck B Smith Jane A Boone Dan B 1 freshman 2 sophomore 3 junior 4 senior

  13. Relational data model • Third normal form • every field that is not a primary key must be totally and directly dependent on the primary key (no transitive dependency) Student ID Last First Grade Class Instructor Violates third normal form because Instructor is dependent on Class, not on the primary key ID 1 Wood Bob C Geog357 Jones 2 Kent Chuck B Geog115 Brower 3 Smith Jane A Geog357 Jones 4 Boone Dan B Geog357 Jones

  14. Relational data model • To resolve third normal form violation - create separate tables Instructor is dependent on primary key Name Class is dependent on primary key ID Student Class ID Last First Grade Class Name #Stud Instructor 1 Wood Bob C Geog357 2 Kent Chuck B Geog115 3 Smith Jane A Geog357 4 Boone Dan B Geog357 Geog357 48 Jones Geog115 120 Brower Geog20 120 Jones

  15. Why Normalization? 1. If the instructor to a class changed - all students with that class would have to have their instructors changed 2. Every time a student changed a class, the instructor would also have to be changed Student ID Last First Grade Class Instructor 1 Wood Bob C Geog357 Jones 2 Kent Chuck B Geog115 Brower 3 Smith Jane A Geog357 Jones 4 Boone Dan B Geog357 Jones

  16. Why Normalization? ID Last First Grade Class Instructor These update problems may result in logical inconsistencies in the database 1 Wood Bob C Geog357 Jones 2 Kent Chuck B Geog115 Brower 3 Smith Jane A Geog357 Jones 4 Boone Dan B Geog357 Jones Original table Student ID Last First Grade Class Instructor 1 Wood Bob C Geog115 Jones 2 Kent Chuck B Geog115 Brower 3 Smith Jane A Geog357 Knight 4 Boone Dan B Geog357 Jones Logical inconsistency Updated table Logical inconsistency

  17. Why Normalization? When the table is in third normal form, these logical inconsistencies cannot take place. When an instructor is changed, the change is enforced for all students When a student changes classes, the change is instructor is automatically enforced Class ID Last First Grade Class Name #Stud Instructor 1 Wood Bob C Geog357 2 Kent Chuck B Geog115 3 Smith Jane A Geog357 4 Boone Dan B Geog357 Geog357 48 Jones Geog115 120 Brower Geog20 120 Jones

  18. Relational data model • Operations on the relational data model: defined by relational algebra • intersection • union • difference • join • projection • selection Each operation takes one or more tables as input and returns one table as output

  19. Relational data model • Intersection: find records common to two tables given certain criteria (an and operation)

  20. Relational data model • Intersection: Find all classes that are Geography classes and that are also Gen Ed classes Geography Classes Gen Ed Classes Class Instructor Class Instructor Geog357 Jones Geog115 Brower Geog20 Fountain Geog435 Karnes Geog20 Fountain Geog115 Brower Meteo110 Stankle Meteo200 Turlock Result of Intersection Class Instructor Geog20 Fountain Geog115 Brower

  21. Relational data model • Union: find records common to either of two tables (an or operation)

  22. Relational data model • Union: Find all classes that are either Geography classes or that are Gen Ed classes Results of Union Geography Classes Gen Ed Classes Class Instructor Class Instructor Class Instructor Geog357 Jones Geog20 Fountain Geog115 Brower Geog435 Karnes Meteo110 Stankle Meteo200 Turlock Geog357 Jones Geog115 Brower Geog20 Fountain Geog435 Karnes Geog20 Fountain Geog115 Brower Meteo110 Stankle Meteo200 Turlock

  23. Relational data model • Difference: Find the records in one table that are not also present in another table (an xor operation)

  24. Relational data model • Difference: Find all classes that are Geography classes butthat are not Gen Ed classes Geography Classes Gen Ed Classes Class Instructor Class Instructor Results of Difference Geog357 Jones Geog115 Brower Geog20 Fountain Geog435 Karnes Geog20 Fountain Geog115 Brower Meteo110 Stankle Meteo200 Turlock Class Instructor Geog357 Jones Geog435 Karnes

  25. Relational data model • Join: Match records in both tables based on a common field Geography Classes Instructor Result of Join Class Instructor Instructor Office Class Instructor Office Geog357 Jones Geog115 Brower Geog20 Fountain Geog435 Karnes Jones 332 Brower 423 Fountain 125 Karnes 312 Geog357 Jones 332 Geog115 Brower 423 Geog20 Fountain 125 Geog435 Karnes 312

  26. Relational data model • Projection: reduces one table in the attribute dimension (a selection of a subset of fields, for all records)

  27. Relational data model • Projection: List all Geography classes, but not the instructors Geography Classes Result of Projection Class Instructor Class Geog357 Jones Geog115 Brower Geog20 Fountain Geog435 Karnes Geog357 Geog115 Geog20 Geog435

  28. Relational data model • Selection (restriction): reduces one table in the record dimension (a selection of a subset of records, for all fields) • Criteria for selection is called a predicate

  29. Relational data model • Selection: Find Geography classes taught by ‘Jones’ Geography Classes Class Instructor Geog357 Jones Geog115 Brower Geog20 Fountain Geog435 Karnes Result of Selection Class Instructor Geog357 Jones

More Related