Database Re-Engineering Schema Extraction and Normalization

Lecture 23 of SE602 Software Re-Engineering investigates database re-engineering, the discipline of modernizing aging data storage architectures, file systems, and legacy relational databases. In legacy enterprise systems, database schemas frequently suffer from severe design decay: denormalized tables, unconstrained field definitions, embedded procedural business logic in triggers, and flat-file persistence layers. Students explore the database reverse engineering process, which extracts conceptual Entity-Relationship (ER) models from physical Data Definition Language (DDL) scripts, database catalogs, and application query logs. The lecture details normalization transformations to eliminate insertion, update, and deletion anomalies, re-establishing third normal form (3NF) and Boyce-Codd normal form (BCNF). Strategies for preserving historical data constraints, migrating hierarchical databases to relational structures, and maintaining backward compatibility are systematically evaluated. Risk management frameworks, business case evaluations, and cost estimation models such as COCOMO II are analyzed for migration projects. Legal and ethical constraints surrounding reverse engineering, clean-room design implementations, and software intellectual property rights are reviewed. By engaging with solved past papers by Moaaz and Waqar Siddhu, learners gain actionable insights into recurrent university exam questions. Interactive quiz assessments on VU SIGMA facilitate rapid knowledge verification and retention of critical software re-engineering concepts. Comprehensive coverage of standard academic benchmarks ensures learners communicate with technical precision across software engineering disciplines. Detailed analysis of code smell topologies highlights the imperative of eradicating duplicate logic, long methods, and bloated god classes. Test harness construction including characterization testing and regression suites are contrasted with traditional unit testing strategies. Students evaluate the trade-offs between big-bang system rewrites and progressive evolutionary refactoring in mission-critical environments. The lesson explores cognitive program comprehension models, top-down hypothesis validation, and mental mappings of complex legacy architectures. Practical code illustrations demonstrate safe code transformations, automated refactoring preconditions, and behavioral equivalence preservation. Scholars analyze Chidamber-Kemerer object-oriented metrics including WMC, CBO, and RFC to quantify the architectural impact of refactoring. Cloud migration pathways including rehosting, replatforming, and cloud-native refactoring are examined as extensions of legacy modernization.