IT Asset Management Database (Oracle SQL & PL/SQL)

Project Overview

This project is a production-oriented IT Asset Management (ITAM) relational database using Oracle SQL and PL/SQL. It focuses on designing, securing, migrating, and optimizing enterprise-grade databases that support real business workflows such as asset tracking, auditing, and operational reporting.

This project demonstrates exercises covering:

• How to model business-critical data using relational design best practices
• How to enforce correctness and consistency at the database layer
• How to automate operational logic with PL/SQL instead of relying solely on application code
• How to prepare and migrate databases for cloud environments
• How to analyze and optimize query performance under realistic workloads

This project reinforced that strong relational database design prevents entire classes of defects before they reach application code, while constraints and triggers serve as essential mechanisms for defensive data engineering. It highlighted that successful cloud migration extends beyond schema movement to include deliberate planning around users, roles, and security boundaries.

Core Capabilities & Exercises

Business-Driven Query Design

Developed SQL queries to support real business questions such as asset utilization, ownership tracking, and inventory status.

• Complex SELECT statements
• Multi-table JOINs
• Aggregations, filtering, grouping, and ordering
• Query readability and maintainability

Cloud Migration & User Management

Migrated the ITAM database to Oracle Cloud, simulating an enterprise transition from on-prem to cloud infrastructure.

• Schema migration
• User creation and role-based access control
• Separation of duties and least-privilege access

Data Integrity & Relational Enforcement

Implemented robust constraints to ensure long-term data correctness and prevent invalid states.

• PRIMARY KEY, FOREIGN KEY
• UNIQUE, CHECK, NOT NULL
• Referential integrity across asset and user tables

Database Automation with PL/SQL

Automated business logic directly at the database layer to improve reliability and reduce application complexity.

• User-defined functions
• Stored procedures
• Triggers for lifecycle events
• Reduction of manual and repetitive operations

Performance Tuning & Optimization

Analyzed and improved database and query performance using Oracle tooling and indexing strategies.

• Index design and trade-offs
• Execution plan analysis
• Query refactoring for efficiency
• Performance considerations for scaling systems

Architecture & Database Resources

Included design artifacts and schema documentation to support maintainability and knowledge transfer:

• SQL scripts to create ITAM and Murach reference tables
• Clear separation between schema creation, constraints, and procedural logic
• IT Asset Management ER Diagram
• AP Schema Diagram

Tech Stack

Layer	Technology
Database	Oracle Database
Query Language	SQL
Procedural Logic	PL/SQL
Tooling	Oracle SQL Developer
Cloud	Oracle Cloud Infrastructure (OCI)
Concepts	Data Integrity, RBAC, Automation, Performance Tuning

Repository Structure

├── student_create_itam/            # Core IT Asset Management schema data
│   ├── application_data.tsv        # Seed data for applications tracked in ITAM
│   ├── asset_desc_data.tsv         # Descriptions of IT assets (metadata, models)
│   ├── asset_type_data.tsv         # Types/categories of assets (hardware, software)
│   ├── ci_inventory_data.tsv       # Configuration item inventory details
│   ├── ci_status_data.tsv          # Status of configuration items (active, retired, etc.)
│   ├── computer_data.tsv           # PC/laptop inventory for employees
│   ├── department_data.tsv         # Department master data (organizational units)
│   ├── employee_ci_data.tsv        # Links employees to configuration items they own/use
│   ├── employee_data.tsv           # Employee master records (name, role, department)
│   ├── it_asset_inv_summary_data.tsv # Aggregated IT asset inventory summary for reporting
│   ├── it_service_data.tsv         # IT service catalog and mappings to assets
│   ├── other_data.tsv              # Miscellaneous or auxiliary reference data
│   ├── peripheral_data.tsv         # Non-core devices (monitors, keyboards, mice)
│   └── server_data.tsv             # Server inventory and specifications
├── create_Murach_tables/           # Reference tables and sample datasets for learning/exercises
│   ├── student_create_ap.sql       # Schema and sample data for Accounts Payable exercises
│   ├── student_create_ex.sql       # Schema and sample data for general exercises
│   └── student_create_om.sql       # Schema and sample data for Order Management exercises
├── diagrams/                       # Architecture and data model documentation
│   ├── ITAssetMgmnt_Diagram.png    # ER diagram for the ITAM relational schema
│   └── ap_schema.png               # ER diagram for the AP schema
├── plsql/          # Database automation and procedural logic
│   ├── ex3.sql     # Queries for a Business Purpose
│   ├── ex4.sql     # Migrate ITAM to the Cloud and Create and Enable Users
│   ├── ex5.sql     # Constraints to Support Data Integrity
│   ├── ex6.sql     # User-Defined Functions, Stored Procedures, and Triggers to Automate 
Processes 
│   ├── ex7.sql     # Database and Query Performance
└── README.md       # Project summary

References

• Murach’s Oracle SQL and PL/SQL for Developers (2nd Edition) - Joel Murach
• Oracle SQL Developer Documentation
• Oracle Cloud Infrastructure Database Guides