Relational database system: Design and implementation of a SQL-based system to manage inventory in a small business

Abstract

Inventory management is an important aspect of the daily operations of the small business and is often handled using crude and unsophisticated tools or manual processes that are open to errors and inaccura-cies. This thesis is a practice design and implementation of a SQL-based relational database system that was specially designed to support inventory operations in a small-scale supermarket. The general idea behind the work is to demonstrate how a formalized data warehouse and the use of SQL queries could be implemented to manage inventory data in an open and honest way, as opposed to the untrustworthy paper records.

The system was developed using the fundamental concepts related to relational database (entity-relationship modeling (ERD), data normalization, and integrity constraints), and created a schema with items of products, categories, suppliers, and stock operations using a standard SQL environment to pro-vide a logical connection of all data. The most significant element of the methodology was the devel-opment of automated database triggers, namely, a so-called prevention of negative stock functionality, which would guarantee that the digital records were always consistent with the real-life appearance of the shelves in the shop. This technical solution is to make sure that the database is not a passive storage tool, but rather an active part in the process of avoiding human error in the hectic retail shifts.