Historical Data Management System with Oracle 19c and Microsoft Access

This project was part of a group assignment that showcased our collaborative ability to create a database application with historical data tracking using Oracle 19c and Microsoft Access. It allows users to manage and analyze relationships (e.g., authors and their genres, books and their authors) while maintaining a history of changes over time. The front end is built with Microsoft Access, utilizing ODBC drivers to link to the Oracle database for real-time data operations.

Key Features:

• Replaced original tables with views in Oracle to manage historical data for multi-valued fields.

• Designed triggers for INSERT, UPDATE, and DELETE operations, keeping a history of changes with timestamps.

• Included optional fields like Notes for detailed descriptions of changes.

• Created association tables with STARTTIME and ENDTIME fields to track changes over time.

• Built intuitive forms in Microsoft Access for adding, editing, and deleting records.

• Integrated dropdown menus to view historical data.

• Generated reports summarizing current and historical data.

Technologies Used:

• Database: Oracle 19c

• Frontend: Microsoft Access

• Connectivity: ODBC Driver

• SQL Features: Views, Triggers, Stored Procedures.

What the Application Does:

User Operations:

• Users can add, edit, or delete records for:

  Authors and their associated genres.

  Books, including their authors and genres.

Historical Data Tracking:

• Every change to a record (e.g., updates or deletions) is automatically stored with a timestamp for future reference.

• Users can view the history of changes directly from the application via dropdown or reports.

Real-Time Integration:

• Microsoft Access serves as the frontend, connected to Oracle 19c via ODBC.

• Forms and reports in Access provide a user-friendly interface for managing data, with real-time updates reflecting changes in the database.