This project is a comprehensive database solution designed to manage the end-to-end lifecycle of bank loans. It handles everything from client registration and credit approval to repayment schedules, payment tracking, and automated penalty calculations for overdue installments.
Designed for an Oracle SQL environment, this project demonstrates advanced database modeling, data integrity enforcement, and complex business logic automation.
- Data Definition Language (DDL): Optimized relational schema using constraints (
PRIMARY KEY,FOREIGN KEY,CHECK,UNIQUE,NOT NULL). - Data Manipulation Language (DML): Data management and status automation using correlated subqueries.
- Complex Querying (DQL): Multi-table
JOINs, aggregation (GROUP BY,HAVING), and hierarchical queries (CONNECT BY). - Database Objects: Implementation of
VIEWs for data abstraction,SEQUENCEs for ID generation, andINDEXes for performance tuning.
The system architecture consists of 5 interconnected tables:
| Table Name | Description | Key Features |
|---|---|---|
| PROIECT_CLIENTI | Client Registry | SSN/CNP tracking, Income records |
| PROIECT_CREDITE | Loan Details | Interest rates, Credit types, Amounts |
| PROIECT_RATE | Installments | Repayment schedules, Status tracking |
| PROIECT_PLATI | Payments | Transaction history (Card, Cash, Wire) |
| PROIECT_PENALIZARI | Penalties | Automated late fee calculation |
- Payment Validation: Installments are automatically marked as
'Paid'when the total payment amount meets the requirement through correlated subqueries. - Overdue Tracking: Overdue installments are identified and flagged as
'intarziata'based on the current system date (SYSDATE).
- Financial Analysis: Calculates the real-time remaining balance for each loan.
- Visualization: Visualizes hierarchical repayment schedules using
LEVELandCONNECT BY. - Risk Assessment: Identifies high-income clients relative to their city average.
- Automatic Cleanup: The script includes a robust
DROPblock at the beginning to allow seamless re-runs for testing purposes.
- Access an Oracle SQL environment (e.g., SQL Developer, Oracle Live SQL).
- Download the
ProiectBazeDeDate.sqlfile from this repository. - Run the entire script. It will automatically:
- Clean up existing project tables.
- Build the 5-table relational structure.
- Populate the database with sample data.
- Execute the business logic updates and analytical reports.
Author: Pais Dorian-Alexandru