1 / 35

Understanding RDBMS

Understanding RDBMS. Keith T. Weber GIS Director ISU-GIS Training and Research Center. RDBMS. Relational Database Management System. Databases. The “I” in GIS. Database software. Light Duty Medium Duty Heavy Duty. Database software. Light Duty Medium Duty Heavy Duty. IBM DB2 UDB.

dionysius-hilary
Download Presentation

Understanding RDBMS

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. Understanding RDBMS Keith T. Weber GIS Director ISU-GIS Training and Research Center

  2. RDBMS • Relational Database Management System

  3. Databases • The “I” in GIS

  4. Database software... • Light Duty • Medium Duty • Heavy Duty

  5. Database software... • Light Duty • Medium Duty • Heavy Duty

  6. IBM DB2 UDB • The GIS Center’s heavy hitter- IBM DB2, Universal Database

  7. Spreadsheets vs. Databases • Integrity! • Structure

  8. RDBMS Concepts and Terms

  9. Independence • Physical • Logical

  10. Integrity • Important for consistency and transaction management. • Types: • Domain • Redundancy • Constraint • Entity • Referential • Cascading or non-cascading

  11. Key Fields • Unique Identifiers (?) • Primary key • Foreign key • AKA- Relate fields.

  12. RDBMS Structure

  13. Database Tables Database Table1 Table2 Table3

  14. Table Structure

  15. Data Value Types

  16. Basic Steps in Database Design • Understand and document the business’ 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

  17. A Scenario... • Develop a GIS-Based Tourism database for Southeast Idaho.

  18. Document the business needs • What problem or issue is this database going to address? • This is a business statement

  19. DINING DINING DINING K Restaurant Number: 126 K Restaurant Number Name: Burger King Type of food: Fast Name Type of food The Preliminary ERM • Symbolized. • Standard Representation • Attribute Representation • Entity Instance Representation

  20. Relationships • Determine the relationships between your entity types. • Add these to the ERM

  21. Database Dictionary Restaurant_Name Food_Type Cost_Mean The name of the restaurant Categories of food (e.g., 1 = Continental, 2 = Fast food, etc.) The average cost of all regular menu items. Define the List

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

  23. Tuple Types to Tables ENTITY TYPES TUPLE TYPES TABLES RELATIONSHIP TYPES

  24. Normalization • First-Fifth Form Normal (1FN, 2FN,…5FN) • Academic • Applied

  25. 1FN • All values are atomic • Single cell contains single data value • Eliminate repeating groups • Puppy_Trick1, Puppy_Trick2, … • Note: some tables will be OK as planned… just check to make sure.

  26. Check this (1FN)…

  27. 2FN • Satisfy 1FN and… • Redundant data must be eliminated • How? • Example: Puppy_ID, Trick_ID, Trick_Name

  28. Check this (2FN)…

  29. 3FN • Satisfy 1NF and 2FN and… • No non-key attributes are dependent on other non-key attributes. • Example: Appointment_ID, Name, Date, Time, Species

  30. After Normalization • New tuple types will be created. • New tables will be planned. • Many-many relationships will be handled using associative tables (bridge tables).

  31. De-Normalization • What? Is this heresy?

  32. Designing the Actual RDBMS • Visual modeling based upon your ERM and Tuple type model. • Implementation of integrity rules based upon your business constraints.

  33. Populate... • Questions and concerns to revisit • Null data • Reporting discrepancies and variations • Measuring or estimating methods • Client utility/efficiency

  34. The Last Step? Validation!

  35. Questions?

More Related