PAAKASA

Business Database Project Design

Introduction

Databases are structured collections of data that allow this data to be easily accessed, managed and updated.

How data is organized in a database greatly impacts the performance of an application considering databases are the heart of most business applications, which is why It’s important to properly design databases in order to speed up queries, indexing, and storage among other processes & performance targets.

Reading Resource

While working on this project I utilized these resources for learning purposes;-

• Why Is Database Design Important?
• Database Design Project: Building A Business Database from Scratch
• Database-Design
• Why Database Character Set Matter?
• Understanding Database Character Sets and Collations”
• Understanding Character Encoding
• Introduction to Character Encoding
• The ASCII table and mapping
• Data Normalization: Definition, Importance, and Advantages

The Paakasa Database process

I initially followed the process from the GitHub repo until the Normalization and Denormalization step at which point I realized I needed to revisit the previous SQL commands as well as the Excel file and clean the data a little bit better.

I therefore embarked on proper versioning by creating branches for each step so that I can inspect and perform each step properly.

Database Creation & Data Loading

• I created the file db_pakaasa-create.sql for this step.
• I also made sure to clean the Excel data a bit, reduce it and make it a bit relevant to my test project.
• I tried loading the data from CSV using the SQL command in file db_pakaasa-load-data.sql and failed due to restriction, I ran the SET GLOBAL local_infile = true; and checked it with SHOW GLOBAL VARIABLES LIKE 'local_infile'; but also failed even with (value) showing ON, so run SET GLOBAL local_infile = false; to change value back to OFF and resorted to using the Table Import Wizard as well.
• Data was uploaded successfully(107 rows) and confirmed by running sql query SELECT * FROM TblPaakasa;.

All in all at the end of the project, i was working with only 94 rows after making some data cleaning.

Normalization and Denormalization

As per the tutorial, the imported data is denormalized, thus the need for, Data Normalization

Normalization involves organizing data based on assigned attributes as a part of a larger data model. The main objective of database normalization is to eliminate redundant data, minimize data modification errors, and simplify the query process.

After Normalizing the table, The table was sub-divided into the following entities

• Customers with query SELECT * FROM db_paakasa.customers; to confirm table.
• Categories with query SELECT * FROM db_paakasa.categories; to confirm table.
• Suppliers with query SELECT * FROM db_paakasa.suppliers; to confirm table.
• Products with query SELECT * FROM db_paakasa.products; to confirm table.
• Orders with query SELECT * FROM db_paakasa.orders; to confirm table.
• Employees with query SELECT * FROM db_paakasa.employees; to confirm table.

The file i used for this step was db_pakaasa-data-normalization.sql

Database Diagram Design and Table Alterations

This section of the tutorial gave me a bit of a challenge as I had to go back and forth between the SQL scripts, Excel data and BARD(As my peer programmer) to get it right.

Creating Views, Triggers, and Stored Procedures

The file i used for this step was db_paakasa-create-views-triggers-stored-procedures.sql

User Management and Privileges

The 1st user "JohnDoe" a DBA was granted full DATABASE ADMINISTRATOR PRIVILEGES. THE 2nd user "JaneDoe" an ANALYST was granted only Read Access. The file i used for this step was db_paakasa-user-management-privileges.sql

Project SQL file

After completing the project I compiled it all back into one file db_paakasa.sql Check my Documentation for SQL Script.

Database Backup

I did a Data Export from my MySQL workbench to create a backup of the DB file PaakasaBackUp20231024.sql

Conclusion

As part of my learning, I forked the original project to my account for learning purposes, backup and easy follow-up purposes. Here is my Paakasa project on github

I organized the files and pushed all files to the repository to complete the project.

Tool Utilized

• Markdown, Medium & Hashnode for this documenting my project.
• VS CODE , MySQL WorkBench for the SQL.
• Google, LinkedIn & Medium for the tutorial reading material.
• Project Documentation by Okonkwo Chukwuebuka Malcom on github.
• I also thought it a good practice to use BARD as a peer Programmer and for quickly researching complex syntax, terminologies to save time using google search or specific sites. Its important not to add sensitive information to Bard or any chatGPT platform and also ask questions that you are familiar with a a control measure incases of genAI hallucinations.