1 / 31

GUS: 0262 Fundamentals of GIS

GUS: 0262 Fundamentals of GIS. Lecture Presentation 3: Relational Data Model Jeremy Mennis Department of Geography and Urban Studies Temple University. File Structures. A file: “STUDENT”. field. ID Last First Grade. 3 Smith Jane A 1 Wood Bob C

kennethlittle
Download Presentation

GUS: 0262 Fundamentals of GIS

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. GUS: 0262Fundamentals of GIS Lecture Presentation 3: Relational Data Model Jeremy Mennis Department of Geography and Urban Studies Temple University

  2. File Structures A file: “STUDENT” field ID Last First Grade 3 Smith Jane A 1 Wood Bob C 2 Kent Chuck B 4 Boone Dan B record

  3. File Structures Simple ordering is based on order of entry into the file Ordered Sequential ordering is based on numeric or alphabetical ordering Indexed an index provides pointers to certain positions in the file

  4. Databases and Data Models A database is a collection of data files that is structured (organized) to facilitate data storage, manipulation, and retrieval. A database management system (DBMS) is a software package that performs these database functions

  5. Databases and Data Models • A data model is a particular way of conceptually organizing multiple data files in a database: • Hierarchical, Network, Relational • Hierarchical and network data models have generally been replaced by the relational data model. • Relational DBMSs (and their derivatives) dominate the (non-GIS) database market: Oracle, Informix.

  6. 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

  7. 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

  8. 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 1 Wood Bob C Geog357 2 Kent Chuck B Geog115 3 Smith Jane A Geog357 4 Boone Dan B Geog357

  9. 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 Mennis Geog115 120 Brower Geog20 120 Fountain

  10. 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 Mennis Geog115 120 Brower Geog20 120 Mennis

  11. 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 Mennis Geog357 48 Mennis Instructor Name Office Mennis 332 Brower 517

  12. Normal Forms The 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

  13. Normal Forms 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

  14. Normal Forms 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

  15. Normal Forms 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

  16. Normal Forms 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 1 Wood Bob C Geog357 Mennis 2 Kent Chuck B Geog115 Brower 3 Smith Jane A Geog357 Mennis 4 Boone Dan B Geog357 Mennis

  17. Normal Forms 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 Mennis 2 Kent Chuck B Geog115 Brower 3 Smith Jane A Geog357 Mennis 4 Boone Dan B Geog357 Mennis

  18. Normal Forms 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 Mennis Geog115 120 Brower Geog20 120 Mennis

  19. 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 Mennis 2 Kent Chuck B Geog115 Brower 3 Smith Jane A Geog357 Mennis 4 Boone Dan B Geog357 Mennis

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

  21. 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 Mennis Geog115 120 Brower Geog20 120 Mennis

  22. Relational Algebra Operations on the relational data model are defined by relational algebra join projection selection

  23. Relational Algebra 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 Mennis Geog115 Brower Geog20 Fountain Geog435 Karnes Mennis 332 Brower 423 Fountain 125 Karnes 312 Geog357 Mennis 332 Geog115 Brower 423 Geog20 Fountain 125 Geog435 Karnes 312

  24. Relational Algebra Projection: reduces one table in the attribute dimension (a selection of a subset of fields, for all records)

  25. Relational Algebra Projection: List all Geography classes, but not the instructors Geography Classes Result of Projection Class Instructor Class Geog357 Mennis Geog115 Brower Geog20 Fountain Geog435 Karnes Geog357 Geog115 Geog20 Geog435

  26. Relational Algebra 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

  27. Relational Algebra Selection: Find Geography classes taught by Mennis Geography Classes Class Instructor Geog357 Mennis Geog115 Brower Geog20 Fountain Geog435 Karnes Result of Selection Class Instructor Geog357 Mennis

  28. SQL • Structured (Standard) Query Language • Formal language for interacting with relational databases • Implementation and language for relational algebra

  29. SQL SQL - basic syntax SELECT <fields> FROM <tables> WHERE <condition> SELECT Class, Instructor FROM Geography Classes WHERE Instructor = “Mennis” Geography Classes Class Instructor Geog357 Mennis Geog115 Brower Geog20 Fountain Geog435 Karnes Result of Selection Class Instructor Geog357 Mennis

  30. SQL SELECT Class, Office FROM Geography Classes, Instructor WHERE Class = “Geog357” or Instructor = “Karnes” or Office = 125 ORDER BY Office Instructor Geography Classes Result of SQL Query Class Instructor Name Office Class Office Mennis 332 Brower 423 Fountain 125 Karnes 312 Geog357 Mennis Geog115 Brower Geog20 Fountain Geog435 Karnes Geog20 125 Geog435 312 Geog357 332

More Related