This project presents the design and implementation of an Oracle SQL banking database based on an ANZ-inspired business scenario. It covers the full database development process from business rules analysis to conceptual modelling, relational modelling, SQL implementation, triggers, and PL/SQL routines.
The project was originally developed as an academic database case study and is presented here as a portfolio project to demonstrate practical skills in database design, Oracle SQL, PL/SQL, constraints, and business rule enforcement.
Note: This project is inspired by a banking case study based on ANZ business requirements for academic purposes. It is not affiliated with or endorsed by ANZ.
The goal of this project was to design a database capable of storing and managing data generated by a modern banking system, including:
- branches and branch services
- customers and identity verification details
- bank accounts and account ownership
- debit cards and credit cards
- PayID, BPAY, and receiver payment details
- account transactions and payment history
- promotional offers for credit cards
- suspicious activity monitoring and selected business rule enforcement
This project demonstrates how complex banking requirements can be translated into a structured and implementable relational database design.
This project was completed as part of an academic database design assignment.
My work included:
- analysing business rules for a banking system
- refining the conceptual ER design
- converting the ER model into a relational model
- implementing the database in Oracle SQL
- defining primary keys, foreign keys, and check constraints
- developing triggers to enforce selected business rules
- writing PL/SQL procedures and functions
- inserting sample data for testing
The main objective of this project was to build a banking database that supports core customer and transaction management functions while enforcing selected business rules.
The project focused on the following design goals:
- modelling banking entities and relationships accurately
- supporting account ownership, applications, cards, and transactions
- implementing integrity constraints for reliable data storage
- enforcing business rules using triggers
- demonstrating stored logic using PL/SQL procedures and functions
The database is based on a banking environment where:
- the bank operates multiple branches across Australia
- each branch has a unique BSB and may offer different services
- customers can apply for different account types
- accounts may have one or multiple owners
- customers can make payments using account number and BSB, PayID, or BPAY
- customers may hold debit cards and credit cards
- the bank monitors suspicious transactions and account activity
- accounts may become dormant after inactivity
- customers can access services via branch, mobile app, or web portal
This project models key parts of that environment and implements selected rules in Oracle Database.
The design covers core banking entities such as:
- Branch
- Branch Manager
- Service
- Customer
- Bank Account
- Account Application
- Identity Document
- Debit Card
- Credit Card
- Credit Card Account
- Credit Card Type
- Promotional Offer
- Transaction
- PayID
- Biller
- Future Payment
- Receiver / Saved Payee
The model also includes associative relationships to support account ownership, service offerings, card connections, and application processing.
- ER diagram development and refinement
- relational model conversion
- relationship mapping across customer, account, payment, and card data
- table creation scripts
- primary key and foreign key constraints
- check constraints
- sample data insertion scripts
The database includes triggers to enforce selected payment rules, including:
- blocking payments greater than the allowed daily payment threshold
- blocking payments that exceed the customer’s average payment pattern as a suspicious transaction check
The project also includes PL/SQL stored logic to demonstrate account-related processing and data retrieval.
Examples of business rules reflected in the design include:
- each branch has a unique BSB
- each account must be associated with a branch
- one customer may own multiple accounts
- one account may have multiple owners
- debit cards are linked to eligible bank accounts
- customers can pay using account number and BSB, PayID, or BPAY
- future payments can be scheduled and cancelled before the payment date
- suspicious or excessive payments can be blocked using triggers
- Oracle Database
- Oracle SQL
- PL/SQL
- ER Modelling
- Relational Database Design
banking-database-design-oracle-sql/
├── README.md
├── diagrams/
├── docs/
├── sql/