FIT5137 Advanced database technology
The Complete Study & Assessment Guide · S2 2026FIT5137 Overview
- Monash University
- S2 2026
- 10 chapters
- Information Technology
FIT5137 Advanced Database Technology is a Monash University unit in advanced database technologies. The captured Semester 2, 2026 materials organise the subject around two connected areas.
- Assessed by Take Home Quiz 10% · Data Warehouse Major Assignment 40% · Spatial Database 35% · Reverse Engineering 15%
- Key terms Fact grain, Conformed dimension, Bridge table, Surrogate key
- Assessment move Study FIT5137 as an evidence chain.
- Most asked Is there a hurdle requirement?
How FIT5137 is assessed
| Component | Weight | Format |
|---|---|---|
| Take Home Quiz | 10% | Individual quiz or test component. The captured LMS page shows an open period from 24 August to 30 August 2026. |
| Data Warehouse Major Assignment | 40% | Artefact focused on operational exploration, cleaning, dimensional design, implementation, analysis, visualisation, reporting and presentation. Due 17 September 2026 in the captured LMS page. |
| Spatial Database | 35% | Artefact focused on spatial database work and analysis. |
| Reverse Engineering | 15% | Quiz-based answer sheet worth 3% plus a video presentation worth 12%. Both are due 8 October 2026 in the captured LMS page. |
What FIT5137 covers
Move from data engineering and warehouse design through cleaning, dimensional modelling and reverse engineering, then apply the same evidence discipline to spatial databases, queries, visualisation and indexing.
Data Engineering and Warehouse Architecture
Week 1. Operational systems, analytical questions, transformation responsibilities, warehouse layers, lineage and reconciliation.Data Engineering and Warehouse Architecture explains how operational records become reliable analytical data.
- Start with the business decision and analytical question
- Separate operational capture from analytical storage
- Define measures, grain and descriptive dimensions
- Map source fields into transformation responsibilities
- Preserve rejected rows and transformation lineage
- Reconcile warehouse outputs to source control totals
Star Schemas and Fact Design
Week 2. Business processes, fact grain, dimensions, additive measures, role-playing dates and source-to-fact controls.Star Schemas and Fact Design centres dimensional modelling on one question: what does a fact row represent? The answer is the grain, and every dimension key and measure must describe that same event.
- Select one measurable business process
- Declare one fact row in a complete sentence
- Choose dimensions that describe that event
- Place descriptive attributes in dimensions
- Test each measure for valid additivity
- Distinguish date roles and unknown members
Data Quality and Cleaning
Week 3. Profiling, defect diagnosis, survivor rules, rejected rows, before-and-after evidence and sensitivity checks.Data Quality and Cleaning treats cleaning as diagnosis rather than cosmetic rewriting.
- Profile completeness, validity, consistency and uniqueness
- Define the defect at row, attribute or relationship level
- Separate diagnostic queries from correction queries
- Distinguish duplicate events from status history
- Apply the least destructive supported rule
- Preserve rejected and unresolved records
Hierarchies, Bridges and Dimension Keys
Week 4. Roll-up paths, snowflakes, many-to-many bridges, allocation factors, surrogate keys and historical membership.Hierarchies, Bridges and Dimension Keys addresses dimensional relationships that a simple star cannot represent safely. A hierarchy defines how lower-level members roll into higher levels. A snowflake separates some levels into related tables.
- Define hierarchy levels and parent dependencies
- Compare denormalised dimensions with snowflakes
- Recognise genuine many-to-many membership
- Introduce a bridge without creating new events
- Distinguish participation from allocated measurement
- Validate allocation factors and rounding
Multi-Fact Models and Slicing
Week 5. Distinct fact grains, conformed dimensions, compatible comparison levels, rates, slicing and zero-versus-missing logic.Multi-Fact Models and Slicing keeps related business processes separate while making valid comparison possible. Booking, completed journey and maintenance work order facts have different grains, measures and sometimes different dimensional coverage.
- Separate facts by business process and native grain
- Test dimensional conformance beyond matching labels
- Assign explicit roles to shared date dimensions
- Build a fact-dimension compatibility matrix
- Aggregate facts independently before comparison
- Preserve numerator and denominator behind rates
Granularity and Multi-Input Warehouses
Week 6. Detail and aggregation layers, vertical stacking, horizontal integration, crosswalks, source lineage and freshness.Granularity and Multi-Input Warehouses explains how detail changes across warehouse layers and how data from several operational systems can be combined without losing meaning.
- Define each fact layer with an explicit row meaning
- Distinguish detail from levels of aggregation
- Preserve additive components behind derived rates
- Reconcile summaries to detailed facts
- Compare vertical stacking with horizontal integration
- Conform identifiers, units, codes and event rules
Reverse Engineering Database Schemas
Week 7. Object inventory, table classification, relationship evidence, essential tables, baseline ERDs and independent validation.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.
- Inventory tables, views, columns, keys and dependencies
- Sample row patterns without treating samples as complete proof
- Classify operational, lookup, history and derived structures
- Trace one core workflow from entry to outcome
- Select the smallest defensible baseline model
- Verify match coverage, uniqueness and cardinality
Spatial Data and PostGIS
Week 8. Geometry types, coordinate reference systems, loading, validity, extent, transformation and measurement units.Spatial Data and PostGIS introduces geometries, coordinate reference systems and database operations that give location analytical meaning.
- Model phenomena as points, lines or polygons
- Preserve stable business identifiers beside geometry
- Verify actual coordinate reference systems
- Distinguish assigning metadata from transforming coordinates
- Validate geometry type, emptiness and validity
- Check extent against the expected operating region
Spatial Relationships and Query Functions
Week 9. Intersects, touches, crosses, containment, boundaries, distance, shape measurement and known-case testing.Spatial Relationships and Query Functions translates geographical statements into topological and measurement tests. Intersects is broad: geometries share at least one point. Touches focuses on boundary contact without the relevant interior overlap.
- Write the spatial claim in plain language
- Distinguish interiors, boundaries and exteriors
- Compare intersects, touches and crosses
- Keep contains and within argument order explicit
- Decide the policy for boundary locations
- Separate topological relations from measurement
Spatial Visualisation, Indexes and Catchments
Weeks 10 and 11. Spatial loading, visual inspection, catchment queries, candidate pruning, indexes, maps and coverage limits.Spatial Visualisation, Indexes and Catchments combines data loading, QGIS inspection, advanced query decomposition, indexing and cautious interpretation.
- Define the catchment rule, unit and boundary policy
- Reconcile populations at every query stage
- Preserve stable identifiers through transformation
- Separate candidate pruning from exact predicates
- Inspect plans, selectivity and repeated timings
- Keep logical results unchanged during optimisation
The first is data engineering: transforming and cleaning data, understanding warehouse architecture, designing dimensional models and querying them for analysis. The second is emerging database technology: geospatial data, spatial database management, visualisation, modelling, indexing and query techniques.
The weekly sequence builds deliberately.
It begins with the roles of data engineering, business intelligence, operational databases and warehouses. It then develops star schemas, fact and dimension design, data-quality diagnosis, hierarchies, bridges, multi-fact models, multiple operational inputs and levels of granularity.
Reverse engineering applies those ideas to an existing schema by classifying tables, recovering keys and relationships, and selecting a defensible baseline ERD. The spatial weeks add geometry types, coordinate systems, topological predicates, shape measurement, data loading, visual inspection, indexing and catchment analysis.
The published assessment is coursework-based.
Four components carry weights of 10%, 40%, 35% and 15%, which total 100%, and the official handbook does not flag a hurdle. The largest warehouse assignment expects exploration, cleaning evidence, dimensional diagrams, SQL implementation, analysis, visualisations, a written findings report and a short presentation.
The reverse-engineering component combines a quiz-based answer sheet and a video, with emphasis on table classification, essential operational structures, a baseline ERD and independent validation of generated suggestions.
Success therefore depends on evidence: declare grain, explain relationships, reconcile counts, show before-and-after effects, test spatial assumptions and keep conclusions within the data actually examined.
Worked example · free
Design a completion-rate metric across two fact tables
- 2Declare the native grains: one booking row per booking identifier and one journey row per completed journey identifier. Do not join detailed rows merely because date and customer columns look similar.
- 2Aggregate bookings independently to week plus route class, producing 240. Aggregate completed journeys independently to the same conformed keys, producing 180.
- 2Join the two summaries at their unique comparison level. Calculate completion rate as 180 divided by 240, which equals 0.75 or 75%.
- 1Reconcile the joined booking total back to 240 and the journey total back to 180. Any increase reveals duplicate summary keys or an incompatible join.
- 1Interpret cautiously: 75% of bookings in the defined population have completed journeys. The metric alone does not explain the remaining quarter.
Key terms
- Fact grain
- The precise business meaning of one fact-table row, such as one completed journey or one maintenance event. Grain controls valid dimensions, measures and aggregation.
- Conformed dimension
- A dimension whose keys, attributes, history and roll-up meaning are shared consistently across multiple fact tables, sometimes under explicit role names.
- Bridge table
- A relationship table used to represent many-to-many membership. Measures joined through it may require distinct counting or a documented allocation factor.
- Surrogate key
- A warehouse-controlled identifier for a dimension row that separates analytical identity and history from mutable operational identifiers.
- Granularity layer
- A fact representation at a declared level of detail, from event rows to summaries. Moving to a coarser layer discards distinctions that cannot be reconstructed.
- Reverse engineering
- The systematic reconstruction of a useful conceptual model from existing tables, keys, dependencies, row patterns and workflow evidence.
- Geometry
- A spatial value such as a point, line or polygon whose coordinates and reference system support location, topology and measurement operations.
- Coordinate reference system
- The declared framework that gives coordinates their location and unit meaning. Labelling coordinates is different from transforming them.
- Spatial predicate
- A Boolean test of a geometric relationship, such as intersects, touches, crosses, contains or within distance.
- Spatial index
- An access structure that narrows candidate geometries for a query. The exact predicate still determines final membership.
FIT5137 FAQ
How is this unit assessed?
The 2026 Monash handbook publishes four components: a 10% quiz or test, a 40% artefact, a 35% artefact and a 15% quiz or test. The captured LMS page identifies them as the Take Home Quiz, Data Warehouse Major Assignment, Spatial Database task and Reverse Engineering assessment. The numeric weights total 100%.
Is there a hurdle requirement?
The official 2026 handbook rows do not carry a hurdle flag. The captured assessment page also presents the four weighted components without a separate hurdle. Always read current Monash instructions for operational changes, but this guide does not invent an additional hurdle.
How demanding is this unit?
The materials evidence breadth and integration rather than a published difficulty rating. Students move from operational data and warehouse design through cleaning, complex dimensional relationships, granularity and reverse engineering, then into coordinate systems, spatial predicates, loading, visualisation and indexing.
The assessment specifications expect working artefacts plus explanation and validation, so isolated definition memorisation is insufficient.
What matters most in the warehouse assignment?
The specification asks students to explore the operational database, represent its structure, clean data with SQL and before-and-after evidence, provide dimensional schema versions, implement a selected model, analyse it, create appropriate visualisations and explain findings. A strong submission keeps identifiers, grain, diagrams, SQL, results and recommendations consistent.
What does reverse engineering require?
Reverse engineering begins with an inventory of tables, views, keys and dependencies. It then classifies structures, selects tables essential to the core workflow, infers or verifies relationships, and produces a bounded baseline ERD. The assignment also requires generated suggestions to be compared with independent Oracle database evidence rather than accepted on confidence alone.
What should I check before a spatial query?
Confirm geometry type, stable identifiers, actual coordinate reference system, extent, validity and compatibility between layers. Predict the relation for known inside, outside and boundary cases. For distance or area, state the measurement method and unit. A successful query is not trustworthy if its geometry assumptions are wrong.
How should I study across the semester?
Build one evidence notebook. For warehouse topics, record grain, keys, transformations and reconciliations. For reverse engineering, record classification evidence and cardinality tests. For spatial work, record geometry metadata, transformations, known cases, counts and query plans. Rework the original practice cases without looking at the checks, then explain why each result is bounded.
Questions students ask in each FIT5137 chapter
- Why should a warehouse start with a question?
- The question determines the measure, event grain, dimensions, history and refresh timing that matter. Starting from available tables can create a technically neat warehouse that cannot answer the intended decision. Working backwards also exposes missing source evidence before implementation becomes expensive. Chapter 01 · Data Engineering and Warehouse Architecture
- What is a good fact-grain sentence?
- It names one repeatable business occurrence, such as one completed hire identifier or one maintenance work order. Phrases like monthly performance or customer analysis describe reports, not rows. The sentence should let another person decide whether a candidate record belongs in the fact. Chapter 02 · Star Schemas and Fact Design
- Why is DISTINCT not a cleaning strategy?
- DISTINCT removes identical projected rows, but it does not establish which records represent the same event or which version is valid. It can hide status history, discard meaningful differences or leave non-identical duplicates. Diagnose business identity and chronology before selecting a survivor. Chapter 03 · Data Quality and Cleaning
- When is a bridge table necessary?
- Use a bridge when one member can belong to several members of another classification at the same analytical time, such as a product with multiple labels. A single foreign key cannot represent that multiplicity without choosing one value or repeating the dimension row. Chapter 04 · Hierarchies, Bridges and Dimension Keys
- Why not join two fact tables directly?
- Their relationship may be one-to-many or many-to-many, so detailed joining repeats measures. Even shared customer and date values may not identify the same event. Aggregate each fact to a supported common level or use an explicit relationship that the operational process defines. Chapter 05 · Multi-Fact Models and Slicing
- Does lower detail always mean a bad design?
- No. Summaries can improve repeated query performance and match a stable reporting need. The design is weak only when discarded distinctions are still needed, aggregation rules are invalid, or the summary is presented as if it retained transaction detail. Chapter 06 · Granularity and Multi-Input Warehouses
- 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. Chapter 07 · Reverse Engineering Database Schemas
- Why is a coordinate pair not enough?
- The same numbers can indicate different locations under different reference systems and axis conventions. Geometry type and reference metadata provide meaning, while stable identifiers connect the feature to business records. Validate all three before analysis. Chapter 08 · Spatial Data and PostGIS
- Why not use intersects for every relationship?
- Intersects answers whether any point is shared, so it combines overlap, crossing and boundary contact. That breadth is useful for some questions but too coarse for others. Choose a narrower predicate when the policy distinguishes traversal, containment or mere touching. Chapter 09 · Spatial Relationships and Query Functions
- What does a spatial index change?
- It changes how the database finds likely candidates, often using bounding regions. It must not change the logical definition of the result. The exact relationship or distance predicate still determines qualification, and identifier sets should match before and after tuning. Chapter 10 · Spatial Visualisation, Indexes and Catchments
How to prepare for the assessments
Study FIT5137 as an evidence chain. First, translate every warehouse requirement into a business process, fact grain, dimensions, measures and controls. Second, treat cleaning as diagnosis, correction and validation; preserve rejected rows and reconcile both record counts and business totals.
Third, test advanced schema devices by their promise: hierarchies define roll-ups, bridges define multiple membership, surrogate keys define warehouse identity, and conformed dimensions make cross-fact comparison possible. Fourth, reverse engineer systematically by inventorying objects, classifying roles, verifying keys and cardinalities, and limiting the baseline ERD to the workflow in scope.
Fifth, keep spatial meaning explicit: geometry type, coordinate reference system, unit, predicate semantics and known-case tests come before visualisation or indexing. Sixth, rehearse communication. For every important result, state the population, database rule, validation and remaining limitation. Use exact database identifiers in technical artefacts and plain language in the explanation.
Before submission, trace the strongest claim backwards through report, query, transformed data and source identifier; repair any broken link or narrow the conclusion.
