Developing a SQL Database for an Online Clothing Store

Abstract

This project report delineates the design and execution of a Knowledge Management System-based Customer Order Management System for the online retailer Katana. The system incorporates a MySQL relational database to administer customer, product, and order information, with knowledge management techniques to optimise customer insights and enhance operational efficiency. The database schema is completely normalised (to 3NF) and comprises tables for Customers, Products, Orders, Order details, Categories, and other pertinent entities, ensuring data integrity via primary and foreign keys. We employed MySQL as the relational database management system and developed an Entity-Relationship Diagram (ERD) to illustrate the schema. Sales and order data were transferred to Microsoft Excel for analysis, where pivot tables and charts were generated to elucidate trends and client behaviour. The system not only facilitates efficient order processing but also integrates Knowledge Management (KM) elements, capturing and documenting transactional knowledge such as common customer complaints, product FAQs, and sales patterns, thereby rendering this information accessible to both staff and consumers. This KMS connection facilitates self-service customer assistance, enhances informed decision-making, and promotes continuous development. Our literature analysis and implementation reveal that integrating an Order Management System (OMS) with knowledge management improves operational efficiency and customer satisfaction. The paper finds that enhanced data visibility and knowledge sharing result in superior consumer insights and offers recommendations for future improvements, including analytics and AI-driven knowledge retrieval.