Information Systems - 1/12

Course Description and Overview

  • This course introduces relational database management systems (RDBMS) with a focus on an industry-standard system, specifically the Oracle database.

  • Comparison of Oracle with other systems like Postgres will be discussed, highlighting similarities and differences.

Relational Database Management Systems

  • Key Points:

    • Students will gain hands-on experience with SQL, a standardized language for accessing databases.

    • Standardization: Both Oracle and Postgres implement standardized SQL, allowing users to transition between systems with relative ease.

  • SQL (Structured Query Language):

    • Refers to a standardized language, regulated by an external organization, which makes it easier for users to operate on various RDBMS like Oracle, Postgres, and SQL Server.

Database Implementation Contexts

  • Examples of use cases where different databases are employed based on client preferences:

    • Experience managing data collection systems with different database requirements, such as Postgres and SQL Server.

    • Note different implementations based on client needs, highlighting database agnosticism facilitated by SQL standards.

Database Concepts and Features

  • Data Types and Keywords:

    • Discussion on serial data type in Postgres for auto-incrementing columns vs. Oracle's identity column implementation, demonstrating differences in terminology and techniques while adhering to SQL standards.

  • Cloud and Local Implementation:

    • Postgres and Oracle can be run in a client-server architecture, on local machines or in the cloud. Some databases are offered as managed services (e.g., Oracle Cloud, Crunchy Data for Postgres).

  • Entity Relationship Diagrams (ERDs):

    • Students will build logical data models using ERDs to represent data needs and relationships in a structured format.

Data Normalization in Databases

  • Definition of Normalization:

    • The process of efficiently designing relations (tables) adhering to strict rules to avoid poor design issues.

  • Normalization Levels:

    • Introduction to Normal Forms (1NF, 2NF, 3NF), focusing on:

    • First Normal Form (1NF): Ensures all table entries are atomic (single-valued).

    • Second Normal Form (2NF) and Third Normal Form (3NF): Further defines data structuring and relationships by addressing dependencies and anomalies.

    • Students will work on datasets to transition from unnormalized forms to higher normal forms through practical SQL examples.

Practical SQL and SQL Extensions

  • PL/SQL:

    • Introduction to PL/SQL, a procedural language extension of SQL used for database programming.

    • Differences between traditional SQL and procedural languages like Python are discussed:

    • SQL operates through set-based operations, while Python is procedural and step-by-step.

  • Control of Execution Order:

    • SQL queries take precedence in execution order defined by the database engine.

    • Explanation of optimization and usage of indexes in SQL statements, highlighting database management processes.

Geographic Information Systems (GIS) and Database Integration

  • Geospatial Data Management:

    • Discussion on storing spatial data in RDBMS using Oracle Spatial, a built-in feature, vs. installing extensions in Postgres, like PostGIS.

  • Open Geospatial Consortium (OGC):

    • Introduction to OGC standards for storing geospatial data, how they relate to ISO standards, and implementation within Oracle and PostGIS.

Learning Outcomes

  1. Create and populate multi-table databases with constraints.

    • Examples of Table Constraints:

      • NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY.

      • Primary Keys must be unique and not null, allowing unique identification of records.

      • Foreign Keys ensure relational integrity between tables.

  2. Use SQL for database maintenance and reporting.

    • Understanding the importance of relations in relational databases, focusing on atomic values and proper structure.

  3. Apply optimization processes to develop normalized data models.

    • Emphasizing the need for well-designed databases to avoid poor performance issues.

  4. Utilize procedural SQL to create procedures and perform logical operations in a database.

    • The process of creating and invoking functions in a database to encapsulate business logic.

Course Schedule Highlights

  • Course will kick off with practical applications of normalization before diving into PL/SQL and Oracle Spatial.

  • Assignments will involve normalizing datasets, practicing SQL queries, and understanding database programming concepts through PL/SQL.

  • Tests will be practical, involving working with datasets and applying learned concepts rather than memory recall.

Configuration and Continuous Learning

  • Setting up Visual Studio Code and integrating connections to Oracle databases, emphasizing practical skills applicable in real-world scenarios.

  • Encouragement to explore the course’s various learning resources and actively participate in practical exercises throughout the term.