Description
Turn operational requirements into a reliable relational database—not merely a collection of tables.
In this hands-on CDI Practical, you will design and implement a synthetic service-delivery database containing regions, customers, service categories, staff, service requests, and status history. You will define the grain of each table, identify relationships and cardinality, normalize a flat-table concept, enforce business rules, populate the database reproducibly, and validate the finished system.
The project uses SQLite and Python, so no database server is required. SQL is used to implement and test the design, while the main emphasis remains relational modeling, integrity, maintainability, and documentation.
What You Will Learn
- Translate operational requirements into entities, attributes, and relationships
- Define table grain and relationship cardinality
- Distinguish conceptual, logical, and physical database design
- Normalize a flat structure and prevent update, insertion, and deletion anomalies
- Choose primary keys and enforce foreign-key relationships
- Apply unique, check, nullability, and referential-integrity constraints
- Preserve time-varying status history
- Use transactions for consistent multi-step changes
- Create a reusable view and targeted indexes
- Inspect query plans and validate database integrity
- Document design decisions, assumptions, and limitations
What Is Included
- An online guided practical
- A reproducible relational database project
- Python scripts for building and validating the database
- A Bash workflow runner
- A generated synthetic SQLite database
- Schema-inspection and design-validation outputs
- Integrity, foreign-key, and business-rule checks
- A targeted query-plan output and reproducibility manifest
- Learner setup and workflow documentation
Who This Is For
This practical is suitable for students, researchers, analysts, professionals, mentors, and learners who want to move beyond writing SQL queries and learn how reliable relational databases are structured, constrained, validated, and maintained.
Tools Used
- Python 3.10 or later
- SQLite
- pandas
- VS Code
- Quarto for the online guide
No database server is required.
Learning Format
Follow the online practical, build the supplied database in a dedicated Python environment, inspect its schema and constraints, review the generated validation outputs, and complete a relational schema-extension challenge.
The supplied records are synthetic and intended only for teaching and workflow testing.
This is a digital product. No physical item will be shipped.
