Relational Database Management System
A software package designed to define, manipulate, retrieve and manage tabular data in a database.
SQL Data Querying & Data Pipeline
The updating of a table’s structure, relationships and data types in the SQL database.
The providing of training documentation for employees that describes how to use the product effectively.
The manager at Momezon Korean Market, a grocery market located in State College, PA, has managed product inventory through Microsoft Excel for the past 17 years since assuming managerial responsibilities. His current method is to manually input data for each products’ quantity in stock, unit cost, type, supplier, and location for each supplier. All spreadsheets are stored in numerous directories on his personal computer. He desires this data to be stored in one single space, as well as the ability to conduct data analyses. Thus, delivering a software for organized collection of structured inventory data able to be stored, manipulated, retrieved, and accessed electronically is the main focus of this project.
Relational Database Management System (RDBMS)
This desire meant the need for a database management system (DBMS), more specifically, a relational database management system (RDBMS). A DBMS is a software tool that is designed to store, retrieve, and manage data as files. A RDBMS follows the same principals, but stores the data in tabular form. In a RDBMS, each table has an identifier called a primary key, a column whose values ensure that each row is unique. A foreign key is a column whose values correspond to the values of the primary key in another table. In order to add a row with a given foreign key value, there must be a row in the related table with the same primary key value. Exploration began to find potential primary/foreign key relationships.
Database Design for RDBMS
After reading/cleaning the compiled dataset of product inventory and validating our tables in the database to include the correct columns and foreign keys, parameterized queries were run to manage foreign/primary keys with all data split out to relevant tables in the process.
Products Table (View in Python)
Stocks Table (View in phpMyAdmin)
The scripts were ensured to be reusable as they have been formatted to read/clean datasets that I have compiled using the actual case datasets. If the manager pastes the work directories of his files (csv) in the same compiled format at the designated placeholder, the code will run with no issues and update the database accordingly, adding to the existing data.
As requested, training documentation will be provided for various purposes: how to connect to phpMyAdmin from Python, how to run the python scripts to access the database, how to insert csv files at the correct placeholder, and how to run effective SQL queries in phpMyAdmin for data analysis purposes. SQL query documentation will be provided in the form of example statements that can be run, in addition to what each SQL command entails. For example:
WHERE supplier_id = 1;