FIT5137 Chap.7 Reverse Engineering Database Schemas
Reverse Engineering Database Schemas
Reverse Engineering Database Schemas reconstructs a useful conceptual model from an existing database. Large schemas contain operational tables, lookups, bridges, histories, aggregates, views and system objects, so the goal is not to draw everything. The method begins with an inventory of objects, columns, declared keys, dependencies, row counts and update patterns.
It then classifies structures by behaviour, selects tables essential to the scoped workflow, verifies or infers relationships and creates a bounded baseline ERD. Names are clues but not proof; an apparent parent-child relationship needs match coverage, uniqueness and cardinality evidence. Derived tables can lack physical foreign keys, and small tables can still be essential.
The chapter aligns closely with the reverse-engineering assessment, which requires exact database names, essential-table selection, a baseline ERD and an evidence-led comparison between generated recommendations and independent database investigation.
What this chapter covers
- 01
Inventory tables, views, columns, keys and dependencies
- 02
Sample row patterns without treating samples as complete proof
- 03
Classify operational, lookup, history and derived structures
- 04
Trace one core workflow from entry to outcome
- 05
Select the smallest defensible baseline model
- 06
Verify match coverage, uniqueness and cardinality
- 07
Label inferred relationships and remaining uncertainty
- 08
Compare generated suggestions with database evidence
Worked example · free
Measure support for an inferred relationship
- 1Separate nulls: 420 total rows minus 400 non-null request identifiers equals 20 null rows.
- 1Among non-null identifiers, 396 match and 4 are orphans. Check that 396 plus 4 equals 400.
- 2Match rate among non-null values equals 396 divided by 400, which is 0.99 or 99%.
- 1Orphan rate among non-null values equals 4 divided by 400, which is 0.01 or 1%.
- 2The strong overlap supports an inferred relationship but does not prove mandatory participation because nulls and orphans exist.
- 1Inspect the 24 exceptional rows and seek query, application or constraint evidence before finalising cardinality.
Key terms
- Schema inventory
- A structured record of database objects, columns, keys, dependencies, sizes and observed roles.
- Core table
- A table necessary to represent an essential entity or transaction in the scoped workflow.
- Derived table
- A stored or virtual result computed from other structures for reporting or processing.
- Baseline ERD
- A deliberately bounded entity-relationship model explaining the core workflow and verified relationships.
- Match coverage
- The share of candidate relationship values that correspond to referenced keys.
- Inferred edge
- A proposed relationship supported by evidence but not guaranteed by a declared physical constraint.
Reverse Engineering Database Schemas FAQ
Should a baseline ERD include every table?
No. It should include the smallest set that explains the required workflow with defensible entities, keys and relationships. Lookup, history or derived structures may be included when essential to the scope, while report summaries and system logs are often shown separately or excluded with justification.
Can table names prove their roles?
No. Names can guide exploration, but inspect keys, dependencies, row patterns, update behaviour, view definitions and value overlap. A table called summary may be operationally important, while a table with a business noun may be an imported or reporting copy.
How do I compare generated suggestions fairly?
Record the generated classification, collect independent database evidence, then mark each suggestion accepted, modified or rejected. Explain the effect on the baseline model. Confidence or fluency is not evidence, and every final choice should be traceable to observed structure or workflow logic.
What if no physical foreign key exists?
Test value overlap, uniqueness, nulls, orphans and observed cardinality; inspect views, stored queries or application logic; and label the relationship as inferred until authoritative evidence confirms it. Lack of a constraint does not mean no relationship, but it increases the need for transparent evidence.
Assessment move
Practise reverse engineering as a funnel: inventory, classify, select, relate and validate. For a sample schema, write one line of evidence beside every table class and every ERD edge. Count nulls, matches, orphans and multiplicity rather than relying on names. Deliberately exclude a reporting summary and explain why it is not the core transaction. Then critique one confident generated suggestion with database evidence.
Keep exact identifiers in technical outputs and use plain language to describe their workflow role. Run a classification challenge with misleading object names. Require at least two independent signals before accepting each role, such as dependency plus update pattern or key structure plus value overlap. For every inferred edge, create a small table of matched values, nulls, orphans and parent multiplicity.
Add one exception that forces the relationship to remain tentative. Redraw the baseline after removing every nonessential object and verify that the core workflow can still be narrated from entry to outcome. Finally, list excluded tables by reason so scope appears intentional rather than incomplete.
Practise a five-minute walkthrough of the baseline ERD in which every entity and edge earns its place through one observable fact. If an object cannot be justified without appealing to its name, return to the inventory and gather stronger evidence. Save the supporting query or metadata excerpt beside the classification table so each conclusion can be checked independently of the diagram.
