DATABASE DESIGN: BUILDING A BUSINESS DATABASE FROM A CSV FILE
Super Store is a business organization that has been doing well with keeping their sales Data in a CSV file. They have just learnt about the importance of Database and now they have reached out to me to help to help them design a PostgreSQL Database based on their existing CSV file.
You can find the dataset and the full code on my GitHub
Well, let’s get started.
Step taken:
- Database Creation
- Creation of Orders Table
- Copy Data from CSV
- Data Normalization
- Entity Relationship Diagram
- Table alteration
- Roles and View.
Database Creation
I named the Database “SuperStoreDB” and I created using the code below
Our Database was created successfully, Now, the next step is to create our first table named “orders,” encompassing all the columns from the CSV file and specifying their respective data types.
Creation of Orders Table
The “orders” table has been successfully created, but it currently does not contain any data. To populate the table, I used the Copy command to transfer the data from the CSV file into the newly created “orders” table.
Copying Data from CSV File.
We have successfully populate our orders table. Now let’s see what it looks like.
Data Normalization
Are you perceiving the aroma of what we are cooking already? 😄 Looking at the data, you’ll notice numerous duplications. Let’s address this by creating additional tables into our database to minimize these redundancies. 🛠️. We will be adding:
Product Table
Customer Table
Sales Team Table and
Location Table
Entity Relationship Diagram(ERD)
This ERD was designed using dbdiagram.io.
Table alteration
At this stage, we can now start the table alteration stage. Let’s start with Removing all the unnecessary or redundant columns from a orders table table.
We’re removing these columns since they are already present in other tables and are no longer needed in this context.
We can now finish up with our table alteration by specifying primary and foreign keys.
With our tables in place and finalized, the next step involves creating views and roles within the database.
Creating Views
In addition to creating the Database, SuperStore had asked that we help them create view that categorize customers based on their spending levels as follows: those with amounts spent less than 5000 as “Silver customer,” those with amounts less than or equal to 10000 as “Gold customer,” and those with amounts exceeding 10000 as “Diamond customer.”
Let’s go create the view
Output:
Finally, I have been asked to create two roles: ‘Intern’ and ‘Data Engineer.’ Assign the ‘Intern’ role with SELECT privileges only. For the ‘Data Engineer’ role, grant privileges to CreateDB and Createrole. Additionally, set
the ‘Intern’ role to expire on October 10, 2024.
NB: I am currently taking a Database Administration Course on Datacamp and everything I did here is based on my knowledge of Database as of Today. As I progress in my learning journey, my plan is to regularly update and expand this project along with its documentation.
It’s important to also know that the data I worked with was quite messy, with over 30 productIDs having two product names associated with them. This made it difficult to determine the correct product name for each productID. Additionally, the same product was listed under different categories, further complicating the normalization process. Since there were no stakeholders available to provide clarifications.
I’m eager to hear your thoughts on this project and would greatly appreciate any feedback or corrections you have to offer. You can reach out to me on Linkedin or on Twitter
