Data Design Management (BS514) — Data Strategy Consultancy: Relational Database Design, SQL Implementation and Pipeline Transformation
This Level 7 assessment places the writer in the role of a Data Strategy and Analytics Consultant appointed by an organisation operating in a realistic industry sector. The brief is entirely simulated, so the work sets out a defensible set of assumptions about the organisation's data environment before any design begins, and populates the resulting database with synthetic but realistic records. The deliverable is a slide deck carrying full explanatory notes, submitted as a single PDF, and weighted across three connected tasks. Task one establishes the business case. It describes how the chosen organisation currently collects, stores and uses data across customer interactions, sales transactions, operational processes and digital channels, and identifies where that fragmented picture costs the business in efficiency, resource use, retention and decision quality. A SWOT analysis benchmarks the organisation against a named real-world competitor in the same sector, drawing on publicly available market information rather than assertion. The section closes with a critical evaluation of modern relational database advancements — cloud-hosted SQL services, distributed architectures and data warehousing — assessed not in the abstract but against what each would actually change about this organisation's business model. Task two carries the heaviest weighting and is the technical core. Key business entities are identified from the scenario, a current-state data flow diagram traces how data moves from collection points through to storage and reporting with the existing ETL approach made explicit, and a future-state ER diagram is then built with full attributes, primary and foreign keys, relationships and cardinality. The design is normalised to third normal form with the decomposition reasoning shown. Implementation follows in SQL: tables created with appropriate integrity constraints, at least ten realistic sample records inserted per table, and five business questions answered through working queries — highest-performing product or campaign, average conversion by category, workload distribution across staff, accounts with overdue or pending items, and most effective service channel. Outputs accompany every script. A transformation demonstrating query optimisation is included with before-and-after samples so the improvement is evidenced rather than claimed. Task three steps back to the technology decision. Two widely used data processing platforms are compared in tabular form across integration, cleaning, transformation and automation capability, judged specifically against this organisation's constraints, with a reasoned justification for the tool finally selected. The transformed dataset is then used to answer two management-level questions — where investment should be prioritised and how retention might be improved from observed behavioural patterns — each interpreted briefly and tied back to a concrete recommendation. Slide structure follows the prescribed layout, SQL scripts sit in the notes section, and the complete script file is reproduced in the appendix.
Read Model Answer →