Relational Database Management Systems (RDBMS)

Overview

• RDBMS stands for Relational Database Management System.

• This course falls under RNR/GEOG/GIST – 417/517 with Andrew Grogan as the instructor.

Goals of the Course

• Define the term RDBMS.

• Describe the relational model.

• Identify components of a relation.

• Introduce relational schemes and database schemes.

• Explore common functions and modeling problems.

What is an RDBMS?

• Definition: A type of Database Management System (DBMS) that organizes data into a series of records held in linked tables.

• Functionality:

  • Aids in data access and transformation due to flexible linkages based on record values.

  • Requires the utilization of primary and foreign keys for establishing these relationships.

  • Posted Foreign Key: Links between tables that ensure referential integrity.

  • Primary Keys: Unique identifiers for records in a given table.

Names for Relational Data Components

Definitions of Some DB-Relation Components

• Tuple: An ordered list of values representing a single record/system in a relation.

• Primary Key: A unique identifier for a table; defined as any column or combination of columns such that no two rows have the same value(s) in those column(s).

• Attribute: A specification defining a property of an object, including data typing that determines format and data type.

• Domain: A collection of legal values from which specific attributes draw their actual values.

Relational Database

• DefinitionReiterated: A relational database is a set of relations connected by relationships.

Entity Relationship Model

• Composite Attribute: An attribute composed of many other attributes.

  • Example: The Address attribute of the student Entity type consists of Street, City, State, and Country.

Entity Relationship Symbols

• Figures:

  • Rectangle: Represents Entities in ER Model.

  • Ellipse: Represents Attributes in ER Model.

  • Diamond: Represents Relationships among Entities.

  • Line: Connects attributes to entities and entity sets with other relationship types.

  • Double Ellipse: Represents Multi-Valued Attributes.

  • Double Rectangle: Represents Weak Entities.

Attributes

• Key Attribute: Unique for each Entity (e.g., RollNo, StudentID).

• Composite Attribute: Composed of many other attributes (e.g., Address as a composite of Street, City, State, Country).

• Multi-Valued Attribute: An attribute that has multiple values for a given entity (e.g., Phone_Number).

• Derived Attribute: An attribute derived from another attribute (e.g., Age can be derived from DOB).

Entity with Attributes Example

• Entity: Student

• Attributes:

  • Roll_No

  • DOB

  • Name

  • Phone_No

  • Country

  • State

  • Address

  • Age (derived)

  • Street

  • City

Relationships in an RDBMS

• Relationships: Connect various relations and describe associations between them semantically using terms such as:

  • “is kind of”

  • “owns”

  • “works on”

  • “offers”

The “has” Relationship Example

• Entities: Restaurant and Rooms.

• Semantic Relationship Example:

  • Restaurant “has” Rooms.

  • Restaurant Attributes: (Rest_ID, Name, X-coord, Y-coord, Address, Telephone).

  • Room Attributes: (Roomno, RestID, Capacity, Use, Location).

ERD Structure

• Entities and Relationships Example:

  • Name

  • Owner_ID

  • Owner

  • Shape

  • Parcel ID

  • Parcel

  • Landuse

  • Depth

  • Well_ID

  • Well

  • Volume

  • Association using Owns relationship.

Importance of Diagrams and Schemes

• Purpose of ER Diagrams:

  • Provide the initial framework for the finished application.

  • Allow developers to incorporate complexity of the modeled world free from database software standards.

  • Translate into implementation-dependent models based on specific software.

Example in a GISystem

• Facilities - Feature Class: Represents facilities that have associated activities.

  • Attributes: Facility Id, Type, Name, etc.

  • Shows the relationships among various features and activities in the GIS system.

Caution

• Warning: No matter how good your schema is, there may still be missing components and errors in your database.

Common Database Errors

• Incomplete Data Model Error: Occurs when an analyst is asked to build a model limited in scope, missing important components.

• Mis-modeled Problem Domain: Results from an incomplete understanding of the problem domain:

  • Mis-modeling the relationships between relations.

  • Multi-valued attributes can also contribute to these errors.

Case Examples of Errors

• Example 1:

  • A GIS specialist making an emergency response model for Duluth, MN, resulted in an incomplete data model due to missing GIS data themes.

• Example 2:

  • A College database developed for students and classes led to mis-modeling results from insufficient domain understanding and inconsistency in enrollment data.

Guidelines to Avoid Errors in E-R Modeling

1. Develop a Comprehensive Conceptual Model:

   • Gather additional user views of the data.

   • Slightly expand the problem domain beyond minimum requirements.

2. Understand the Problem Domain:

   • Identify functional dependencies and multi-valued attributes.

RDBMS Architectures

• Client-Server Architecture: Represents client and server setups within RDBMS environments.

• Web Services System Architecture: Encompasses web, desktop, and mobile applications within the RDBMS context.

RDBMS Applications

• Systems Examples:

  • ArcGIS Online

  • Content Services

  • Various web browser clients and mobile devices.

• Server GIS Applications:

  • ArcGIS for Server and Desktop with associated components.

Conclusion

• RDBMS is vital for effective data management, and understanding its architecture, functions, and potential errors is crucial for database design and implementation.