DEV Community: Owen Lloyd

Database Normalization

Owen Lloyd — Sun, 27 Sep 2020 17:42:31 +0000

In this blog post I chose the Database admin centric option to identify and explain different normal forms of data in my SQL database.

The most important question to ask when it comes to Database Normalization and normal forms in general, is what do those words mean? Database normalization is a technique to organize data in a database by reducing duplicated data (redundancy) and ensuring only relevant data is stored. Each of these stages of organization are called normal forms, with each form building upon the one before it to ensure the database is normalized.
While there are six levels of normalization and normal forms, typically only the first three are necessary for most applications, and in this blog post we will primarily be looking at the First and Second Normal Forms.

The requirements for the First Normal Form (1NF) are:

Data is stored in tables with rows uniquely identified by a primary key
Data within each table is stored in individual columns in its most reduced form
There are no repeating or redundant columns

Looking at the COVID-19 database we have been building over the past few posts, we can see the table GeographicLocation is in violation of the first normal form because of the Country column. As our primary key for a GeographicLocation is the ZipCode, the Country for our GeographicLocation will always be USA, making the Country column in the table redundant.

After removing the Country column, our GeographicLocation table satisfies the requirements for 1NF.

The Second Normal Form has all the same requirements for 1NF, along with the additional requirement that only data related to a table’s primary key.

Looking at the table Hospital, we can see it is in violation of the requirement for 2NF because the Population column is not related to the Hospital’s primary key of HospitalID, but rather the foreign key of a GeographicLocation’s ZipCode. As the population of a GeographicLocation changes, it would be very easy to forget to update the Population value in the Hospital table because of its indirect relationship with the primary key. To solve this problem, we need to move the Population column to the GeographicLocation table where it is directly associated with the primary key of ZipCode.

Below is a video where I walk through the things I discussed in this post, as well as show the SQL commands I used to normalize and clean up my COVID-19 database. Thanks for reading/watching!

COVID-19 App Controls College Campus?

Owen Lloyd — Mon, 14 Sep 2020 02:03:26 +0000

Out of fear of coronavirus, a small liberal arts school in Michigan (Albion College) has implemented a contact-tracing app called Aura with the purpose of helping the university in handling any potential coronavirus outbreaks on campus. While the hope of the app is to limit the spread of the virus, many vulnerabilities within the app have raised questions about how secure students’ private information like real time location tracking data and test results is.

Albion College has made the use of Aura completely mandatory on their campus as students are expected to use the app or face suspension from the school. Given the difficulties and danger that the virus has caused for many other schools and universities including Penn State, I believe that Albion College has good intentions at heart with the implementation of this app. With that being said, the amount of location data and personal medical data that Aura is storing is extremely risky, and the potential repercussions of data leaks would be disastrous. Overall, I would say this app crosses the line from necessary into intrusive because of the quantity of personal data that it collects.
I do not think the implementation of an app like Aura would go well at a school like Penn State, primarily because of the number of students at the university. While Albion College has only 1,500 students, Penn State Main Campus has nearly 40,000 students. Additionally, the lack of internal testing that was done on the app would make me feel very concerned if Penn State were to force us to use it.

I do not believe that privacy of data is more valuable than the safety of the general public when it comes to the virus, but I don’t think that the way Aura was built or implemented at Albion College will make that strong of a difference in the spread of the virus. There are other methods for contact tracing, such as using Bluetooth signals, that are much less intrusive and more privacy friendly. Additionally, the lack of testing that went into Aura means that the app was not ready to be used when it comes to private health information like virus test results.

The implementation of Aura at Albion College will likely serve as a lesson for future apps that also aim to help with contact-tracing and preventing the spread of the virus, showing that much more testing and oversight is needed to effectively build an app of this kind.

On the topic of apps related to COVID-19, I have embedded my video here where I discuss more in depth the data model for modeling an individual patient's risk level for COVID-19 that I started last week, building a Physical Diagram along with the Conceptual Diagram. This data model could be potentially applied to building an algorithm to calculate an individual's risk level for COVID-19 given all of the attributes discussed in the video. I hope you enjoy!

Conceptual Diagram:

Physical Diagram:

COVID-19 Database and Tuples

Owen Lloyd — Sun, 06 Sep 2020 20:39:22 +0000

Hello!

As you have probably heard a thousand times in the past few months, these are uncertain times we are living in. The COVID-19 pandemic has had monstrous impacts on people all over the world, forcing many college students including myself to do their education virtually while also putting many people out of work, and causing the death and loss of nearly 900 thousand people around the world.

There are many risks and factors that play into the likelihood of an individual person or patient contracting the COVID-19, as well as whether they fully recover from the illness. Because of the many nuanced levels that these factors can take, I have created a Diagram included below that shows many of the entities, relationships and attributes that contribute to risk factors for COVID-19.

The first entity is a patient, which I outlined with five attributes (Age, Gender, Patient ID, Physical Activity Level, and an Underlying Condition). I see these attributes as potential factors for the patient’s risk of contracting COVID-19, and the Patient ID as a primary key to identify the patient at their Occupation as well as in the Hospital.

The second entity is a Geographic Location with the relationship that a Patient lives in a specific Geographic Location. I outlined five attributes for a Geographic Location (Country, Climate, Population Density, Culture, and Government Healthcare Standards) as I see these as markers for a specific Geographic Location to be more likely to have a high quantity of COVID-19 cases.

The third entity is Occupation with the relationship that a Patient has a specific Occupation. I outlined five attributes for an Occupation (Close Contact with Others, Mask Wearing is Feasible, Physically Active, At-Risk Groups, Health Insurance Provided) as I see these as markers for a specific Occupation to be likely to contract COVID-19.

The fourth entity is a Hospital, with the relationships that each Geographic Location has Hospitals and Patients go to Hospitals for treatment/testing. I outlined five attributes for Hospitals (Beds Available, Staff Available, ICU Max Capacity, Treatment or Quarantine Options, and Health Insurance Accepted) as I see these as markers for if a specific Hospital can handle the COVID-19 cases in their area.

The fifth entity is Behavior, with the relationship that a patient displays specific behavior. I outlined five attributes for Behavior (Partying, Wearing Mask, Eating Out, Washing Hands, and Traveling) as I see these specific activities as ways to either increase or decrease a patient’s likelihood of contracting COVID-19.

Finally, the last entity is Testing, with the relationship that Hospitals provide Testing. I outlined five attributes for Testing (Tests Available, Result Speed, Accuracy, Cost, and Locations) as I see these attributes as important markers as to which type of COVID-19 test a patient should get and hospitals should be administering.

Some example tuples for each of these entities is shown below:

While this diagram that I have created is in no way a comprehensive description of the risks and factors associated with COVID-19, I do think it can help give an idea of the nuance that is associated with the pandemic and the many different ways one can come into contact and be at risk for the virus.

You can watch where I walk through this diagram for COVID-19 risks below:

Welcome and SQL vs NoSQL

Owen Lloyd — Sun, 30 Aug 2020 15:11:18 +0000

Hello!

My name is Owen Lloyd and I’m a sophomore triple majoring in Mathematics, Data Science (with a focus in Statistical Modeling), and Economics. My interests in technology initially stemmed from playing video games and then progressed through my in-class and extracurricular experiences throughout middle school and high school.

While I do not see a future for myself as an IT Professional specifically, I definitely know and want my career to be ingrained in or related to tech in some way. Coming into college I was only a Mathematics major, but after exploring the other programs and opportunities around Penn State, I found that Data Science was a great way to build off of my coursework and interest in Math and apply it to an area that is currently in much demand.

There are many areas where Data Science and Data Analytics, in general, can be applied that are a lot of interest to me. Specifically, I think that the application of data analytics and algorithms based on large historical sets of data to finance and the markets is a super interesting area and a career that could be super fulfilling.

Over time I will be posting videos sharing my progression from a student to a professional in the technological field. In this first video, I will be discussing what SQL and NoSQL are, their difference, and their ideal situations for use.

Here is a quick summary of the information covered in the video:

Both SQL and NoSQL serve the same primary purpose of storing data for your project but differ in the way they go about that
SQL databases are relational while NoSQL databases are non-relational.
SQL databases are designed for vertical scaling, meaning they increase the load on a single server by increasing RAM, CPU, or SSD as the size of the database grows, while NoSQL databases are horizontally scalable, meaning they increase the number of servers in the NoSQL database.
SQL has a standard schema definition (a blueprint of how the data is organized) while NoSQL has no standard schema definition which makes it best suited for big data as flexibility is very important.

And here are the links to the articles shown in the video:

Thank you for taking the time to read my blog and watch my introductory video on SQL vs. NoSQL. I will be making more of these blog/video combos throughout the fall semester and possibly beyond then.

Thanks again for reading!
Owen