Portfolio Project — Business & Systems Analysis

Plant Variety IP & Royalty Licensing Management Platform

Business process review, legacy systems audit, and PostgreSQL migration design for an organisation managing plant breeders' rights, licensing, and royalty collection.

Business Process Analysis Systems Audit Requirements Elicitation Data Modelling / ERD MS Access → PostgreSQL Migration PostgreSQL DDL

Business context

Organisations that manage plant variety intellectual property — registering breeders' rights, licensing cultivars to growers, and collecting royalties — depend on accurate, well-governed data across the full IP lifecycle: from importation and quarantine, through propagation and block management, to licensing, sales, and royalty reconciliation.

This case study models a common real-world scenario: a growing IP-management operation had outgrown its original Microsoft Access database. Management needed an independent review of whether the existing system could keep supplying reliable information for IP-management decisions, and a plan for what should replace it.

This is a synthetic, independently-built recreation for portfolio purposes. All organisation names, growers, cultivars, and figures shown are fictional. It is not derived from, and does not reproduce, any real employer's or client's data or systems.

Systems & business analysis approach

1
Business process mapping. Walked the end-to-end IP lifecycle — importation & quarantine, registration, propagation planning, virus testing, licensing, nursery orders, and royalty collection — to identify where the current system created risk or manual rework.
2
Current-state systems audit. Assessed the existing Microsoft Access database against the business processes it was meant to support: data integrity controls, concurrent-access limits, reporting capability, and audit trail gaps.
3
Requirements elicitation. Consolidated functional requirements per subject area (licensing, royalties, quarantine, nursery orders) into a target data model, validated against the mapped business processes.
4
Target architecture & migration design. Designed a normalised PostgreSQL schema with referential integrity, indexing for the identified reporting workloads, and a phased Access-to-PostgreSQL migration approach.

Requirements & stakeholder documentation

The professional deliverables produced during requirements gathering and stakeholder sign-off:

Business Requirements Document

Objectives, stakeholder map, and 9 prioritised business requirements (BR-01 to BR-09).

Open BRD →

Requirements Traceability Matrix

Each requirement traced to its data model entity, migration phase, and verification method.

Open matrix →

As-Is / To-Be Process Flow

Business process diagrams comparing the legacy manual reconciliation flow to the target automated flow.

Open process flow →

Stakeholder Presentation

7-slide steering committee deck: problem, approach, findings, recommendation, and sign-off ask.

Open presentation →

Key findings & recommendations

Findings

  • Business-critical relationships (licence ↔ royalty ↔ block) were maintained manually across linked tables, with no enforced referential integrity.
  • Royalty reconciliation relied on ad-hoc queries rerun each cycle, with no single source of truth for payment status.
  • Quarantine and pathology test results were not consistently linked back to the blocks and permits they governed.
  • Single-file Access architecture limited concurrent multi-user access and complicated backup/version control.

Recommendations

  • Migrate to a normalised relational schema (PostgreSQL) with enforced foreign keys across the licensing, royalty, and block-management subject areas.
  • Introduce a royalty_transactions ledger with explicit payment-status tracking to replace recalculated reports.
  • Link quarantine and pathology records directly to blocks and permits to close the audit-trail gap.
  • Phase the migration by subject area, starting with licensing and royalty (highest business risk).

Target data model — 8 subject areas

The requirements were consolidated into a normalised schema spanning:

CultivarsPlant/variety master data, IP ownership, release status
StakeholdersGrowers, licensors, nurseries, customers
LicencesIP licensing agreements per cultivar/stakeholder
BlocksNucleus, foundation, mother & production block tracking
Pathology testsVirus indexing & testing results per block
Royalty contracts & transactionsRates, calculation basis, payment ledger
Nursery ordersPropagation & order fulfilment
Import/export permits & quarantineCross-border movement & clearance tracking

Full DDL: schema.sql  |  Sample data: sample_data.sql  |  Interactive ERD →

Skills demonstrated

Business process analysis Systems audit & gap analysis Requirements elicitation Entity-relationship modelling Data governance design PostgreSQL / SQL DDL Legacy system migration planning Stakeholder-facing documentation