Project Overview
Led the complete design and implementation of a comprehensive database system for Castle Coaches Hire Ltd as part of an intensive database development module at Kingston University. This practical application required creating a fully functional relational database to manage vehicle fleet operations, bookings, staff records and compliance requirements for a transport company.
This project represented a practical application of database design principles to a real-world business scenario. As the primary database designer, I was responsible for analysing complex business requirements, designing an appropriate relational schema, and implementing a fully functional database solution with appropriate constraints and validation rules.
Key Outcomes
Key Responsibilities
- Created detailed entity-relationship diagrams to model complex business requirements
- Designed and implemented a complete relational database with 10+ integrated tables
- Developed a data dictionary with comprehensive field definitions and constraints
- Implemented advanced SQL queries to extract meaningful business intelligence
- Applied database normalisation techniques to optimise data storage and retrieval
- Created data validation rules to maintain integrity across related records
- Populated the database with representative test data to validate functionality
Technical Achievements
Systems Analysis
- Analysed complex business requirements for a transport company's operations
- Identified critical relationships between customers, vehicles, employees and bookings
- Established appropriate cardinality and participation constraints for all entities
- Created clear entity-relationship diagrams using professional UML notation
- Documented all assumptions and system rules to ensure database accuracy
Database Architecture Design
- Designed normalised relational schema to eliminate data redundancy
- Established primary and foreign key relationships across multiple tables
- Created a comprehensive data dictionary with appropriate data types and constraints
- Implemented column, entity and referential integrity constraints
- Developed a logical structure supporting complex transport booking scenarios
SQL Development
- Wrote SQL scripts for table creation with appropriate constraints
- Created complex queries to support business intelligence requirements
- Implemented joins across multiple tables to produce integrated data views
- Developed comprehensive test procedures to validate database functionality
- Populated tables with realistic test data to demonstrate system capabilities
Technical Documentation
- Produced comprehensive documentation of database design decisions
- Created detailed constraint analysis explaining the business rules implementation
- Documented entity relationships with full cardinality and participation constraints
- Developed technical specifications for the complete database solution
- Prepared query examples demonstrating database functionality
Selected screenshots demonstrating key aspects of the Castle Coaches database implementation. Additional documentation and comprehensive technical artifacts are available upon request.
Entity-Relationship Diagram
Comprehensive entity-relationship diagram showing the complete database structure with cardinality and participation constraints
Data Dictionary Implementation
Detailed data dictionary containing field definitions, data types, constraints, and validation rules for the transport company database
SQL Query Development
Complex SQL queries using joins and aggregate functions to generate meaningful business intelligence from the database
Normalized Schema Design
Normalized relational schema showing table structures, primary keys, foreign keys, and relationships between entities
Integrity Constraints Implementation
Implementation of entity, referential, and domain integrity constraints to ensure data quality and consistency
System Testing Documentation
Comprehensive testing procedures demonstrating database functionality, constraint enforcement, and query performance
Technologies Applied
Experience Impact & Reflection
This project significantly enhanced my database design skills and provided practical experience in translating complex business requirements into a robust database solution. The systematic approach to entity-relationship modelling and schema design developed my ability to create efficient, normalized database structures.
Working on the Castle Coaches Hire Ltd database project at Kingston University provided valuable hands-on experience with professional database design methodologies and implementation techniques. The knowledge gained from this project has directly contributed to my expertise in data management and relational database design.
The database solutions implemented during this project demonstrate my ability to:
- Analyse complex business requirements and translate them into appropriate database structures
- Design normalized, efficient database schemas for real-world business scenarios
- Implement robust data validation and integrity constraints to maintain data quality
- Develop comprehensive technical documentation for database solutions
- Create efficient SQL queries to extract meaningful business intelligence from complex data sets
- Apply theoretical database concepts to practical implementation challenges