DEV Community: Pranav Gowtam

Normalization of a Database

Pranav Gowtam — Sun, 27 Sep 2020 02:43:53 +0000

Welcome back!

In this blog, I will be going over the normalization I implemented into my COVID-19 database to reduce redundancy and clean up the database in general.

So, the best place to start is to answer the overarching question: what is normalization? Also, what are these forms of normalization?

First, normalization is the process of taking tables within a database and breaking them down to reduce the redundancy or repetition of a specific quality. In the example below, in our database, we have a column in the table 'GeographicLocation' named 'Country'. However, since the table also implements the use of zip codes, the 'Country' column becomes irrelevant. Zipcodes lock the app into being US-only.

Normalization has different forms to it. The two I will be exploring in this blog post are 1NF and 2NF -- 1st and 2nd Normal Form.

1NF is the "base" level of all normal forms. Normal forms build off of each other. Each subsequent form must carry all of the qualities of the previous forms. So, 3NF has to have all of the properties in 1NF, 2NF, and the new ones in 3NF.

In the example below, I have my 'Behavior' table. 1NF has three properties that need to be true:

Data is stored within tables and rows; can be uniquely identified by a primary key
Said data is in its most reduced form
There are no repeating/redundant columns

'Behavior' passes these criteria because all of the data in its table is properly stored and has the overarching 'BehaviorID' primary key, all of the data cannot be reduced any further, and there are no redundant columns present.

Moving on, we have the 2nd Normal Form. Looking at the example below, we see that we have a column named 'WorkplacePolicy' in the 'Occupation' table. While the column is not inherently wrong in terms of formatting, it is wrong in terms of placement.

Looking at all of the other attributes on the table, they all directly relate to the primary key. In this case, it is 'CompanyID'. 'WorkplacePolicy' doesn't directly correlate to an individual's ID; rather, it correlates to an entire company. If the policy changes, the change may not sync up to the other values in other columns, which is known as an "anomaly".

To fix this, I created a separate table called 'WorkplacePolicy' with different Boolean values to make it more fleshed out and able to operate independently. I also created a new column named 'CompanyName' to act as a new foreign key in 'Occupation'.

So, I hope this gave a better overview for normalization, 1NF, and 2NF than my video did -- the purpose of my video was mainly to show off the changes I made to my database, while this post was to really go into the technical component of it all.

If you haven't watched my video, you should! I go over the changes above and also how to properly use the alter command.

With that said, thank you for reading -- I hope you have a great day!

~ Incerah

The Slippery Slope of Privacy and Safety

Pranav Gowtam — Mon, 14 Sep 2020 00:47:15 +0000

Around a month ago, the publication TechCrunch released an article detailing the usage of a mandatory, location-tracking application for students of Albion College in Michigan. The app, Aura, aims to automatically alert school officials when a student has tested positive for COVID-19 and alerts students if they had come into close contact with said individual. However, for this functionality to work, it tracks student's locations around the clock.

This, in itself, would cause concern. However, there has also been at minimum 2 security risks within the app. As a result, some students on campus felt rather reluctant to return, as this privacy-invading application was mandatory for all students -- no exception.

Thus, in my eyes, Aura has gone past the fence that separates "necessary" and "invasive". It is a flawed app, both logistically and ethically. Logistically, it is obvious that the app was rushed through development, which has allowed flaws that are easily exploitable by those with the proper knowledge. On the other hand, the large storage of student's location data is cause for concern. While the staff at Albion may have the student's best intentions at mind, the potential leaking of this data can cause massive problems for both the students and staff.

If Penn State, the school I attend, implemented this application like Albion, I would vehemently oppose it. I would never allow such an app, created in such short notice by a relatively unknown company, to collect and store data regarding my every move. A student has the right to keep their personal data private, even if it does come at the cost of safety.

The principle behind the application -- tracking students to inform them if they had come into contact with a person with COVID-19 -- is reasonable, in a sense. Albion does want to protect its students and further their reinforcements against COVID. However, the way they've approached this problem is wrong. There are some ways that this tracking can work in a "tracking lite" mode. For example, logging the location of key-swipes of student IDs across buildings on campus could work. This would allow the institution to still have a rough sketch of the student's whereabouts before their positive test, which would then allow them to alert a larger range of students that may have had contact with this person.

Overall, this is a slippery slope. While Aura is too sketchy of an app for my liking, I do not believe that it is trying to do consumers harm. I believe that it is just a half-baked application that was created in a rush to fill a new void within the market. Over time, I believe that apps such as Aura will begin to iron out these kinks in order to make more secure and accurate applications. Nonetheless, I personally would still be pretty worried if a location-tracking app became mandatory for me to get my education.

Phew. That was a lot to get off my chest. Honestly, I haven't written like that for a few months, ever since my AP English class. Anyways, welcome to week 3 of my blog! Down below you can find my video explaining my new physical diagram for COVID-19 alongside the updated conceptual diagram.

Physical Diagram:

Conceptual Diagram:

~ Incerah

Week 2: Making a ER Diagram for COVID-19

Pranav Gowtam — Sun, 06 Sep 2020 23:25:11 +0000

Hello everyone!

Today, I continue my development by working on creating an ER diagram for my class. Specifically, I am making a diagram for COVID-19. While it may not be the cleanest or the most optimal diagram, I hope it is a solid foundation to build upon in the coming weeks.

COVID-19 has had many impacts on the world currently and the ripples of it will be felt for years to come. The diagram I have created attempts to look at some of the components that an individual has to take into consideration about their risk of contracting COVID. Just to get this out of the way now, this is not a comprehensive list -- nowhere near that. This is just a few of the players in the game of COVID.

So, looking at the diagram itself:

I put the entity "patient" in the middle of my diagram for a couple reasons. The first and most obvious one is that many of the other entities in the diagram have connections to the patient. So, having that central location made it a solid starting point. The other, less technical reason is that I believe it just looks better aesthetically in the middle; there's nothing else to it.

Looking at the majority of the attributes, most of them are quite intuitive; however, some of them are rather hard to put into tuples. I'll explain what I mean later. The hardest entity to create attributes for was the "risk" entity. While some of the basic risks are present (ex: contracting COVID-19), there are still some risks that we don't know of yet. Thus, I added the "long-term symptoms" attribute as a type of catch-all to encompass those potential unknown risks.

Finally, you may have noticed the notation on the lines connecting the entities. This is called "cardinality", which illustrates the relationship between the different entities. For example, looking the relationship between a geographic location and hospitals, there is a crow's foot with a circle on the line attaching "hospitals". This is to represent the fact that there can be multiple hospitals in a singular geographic location. If there had to be multiple of some entity, the circle would be replaced with one of the solid lines seen in other parts of the diagram.

Jumping to the tuples (which is essentially a fancier name for a row or ordered list), some of them were harder to set up than others. For "geographic location", I decided to have my primary key be the country; so, the primary keys down below are Sweden, Italy and India, all of which are highlighted in red in the first image.

Yet again, the "risks" entity turns out to be a bit of a pain. For this one, I have a patient ID act as a primary key, as if all of the entries were patients at a hospital. There is probably a better primary key out there, but I decided to run with this solution for the time being.

Overall, I think I did relatively well on this project. Is it perfect? Absolutely not. There are probably attributes I missed that means that the diagram is not as fleshed out as it could be. However, for a beginner's first diagram, I think I did sufficient enough.

For a more in-depth explanation of my diagram and the tuples, make sure to watch my latest video!

Until next time,
Incerah

Downloading...

Pranav Gowtam — Sun, 30 Aug 2020 01:34:53 +0000

Welcome!

My name is Pranav Gowtam, but I'm also known as Incerah. I am a first-year student at Penn State, currently majoring in Cybersecurity Analytics & Operations. This post marks one of my first steps into the professional world.

During my time in high school, I spent a lot of my time involved in extracurricular activities. However, one that has influenced was Penn State Berks' Computer and Cybersecurity Camp in the summer of 2018, which introduced me to the security field. While my overall knowledge may not be as deep as some of my peers, I am motivated to continue learning and become a more well-rounded student.

For the foreseeable future, I plan to use this site to document my adventures in the professional world. I hope you stick around as well.

Alongside this blog, I will also be uploading videos onto my YouTube channel as I continue to develop my skills in the IT field. For my first video, I'll be talking about the differences between SQL and NoSQL.

For those who would like a bit of a TL;DW, here is some of the notes I made while researching for this video. You can find the full notes in the Pastebin link in the video's description.

Both are types of databases. As a result, they have the same goal of storing data. The differences come in the way that they accomplish this task.
SQL databases are relational while NoSQL databases are not. Relational databases define relationships using tables. NoSQL utilizes more a column system.
SQL databases are vertically scalable (can increase load on server by adding things such as CPU or RAM), NoSQL is horizontally scalable (increase load by adding more servers). Imagine building more floors on a singular apartment complex versus making more houses in a large-spanning neighborhood.
SQL uses a predefined schema, NoSQL uses dynamic schemas. For SQL, a lot of preplanning has to be in place because if there comes a time where a structural change is required, it is a large hassle to do so as all of the data within the database needs to have this change applied. Contrastingly, for NoSQL, it allows for a flexible schema that allows the user to change data structure more efficiently than SQL.

I hope I didn't stutter or pause too much; I haven't made videos like these in years, so I'm still a bit rusty. As time goes on, I'm sure that I will become more eloquent regarding my explanations. I also avoided doing too many jump-cuts just so I could see how I would do naturally.

While this may be just something I'll be doing for class for this semester, I plan to turn this into something beyond just an assignment page. In the future, when I start getting my feet wet with personal projects, I plan to keep a solid log of my development here alongside other topics. So, please consider subscribing to my YouTube channel and following here!

Thanks for reading!
Pranav/Incerah