DEV Community: Patrick Byrne

What is Database N0rmalizati0n???

Patrick Byrne — Mon, 28 Sep 2020 03:22:53 +0000

What's going on guys,
So for the past few weeks as you know I have been working with a Entity Relationship diagram mapping the risks of COVID-19. In my most recent video, I coded my tables using SQL and phpMyAdmin. This week I will be 'normalizing' my data.
Basically normalization is a technique for organizing your data in a database. This process is used to minimize redundancy in your data and ensure your data relates in a logical way. Technically, there are six stages to this process known as normal forms, but we'll only be focusing on the first three, as they are the most relevant. These forms continue to build off of each other.

First Normal Form (1NF)

This step ensures that the data stored in the tables are identified with a primary key, there are no repeating groups, and data is stored in columns in its most reduced form.

Second Normal Form (2NF)

The second step of the normalization process holds the same requirements as 1NF and adds that only data relating to a table's primary key is stored in each table.

Third Normal Form (3NF)

The third and final step we'll discuss includes everything from 2NF and adds that there are no in-table dependencies between the columns in each table.

Applying Our Knowledge:

To start we'll be looking at the Location table. Off the bat, I can see that this is in violation of 1NF. As you can see, the table's primary key is associated with the zip code. Because the zip code is a unique identifier, it will always yield a country of origin, in this case, USA. This means the country column in this table is redundant and can therefore be removed. With this the removal of this column, our table now meets the 1NF requirements.

Next, we'll have a look at the Hospital table. This table meets the 1NF requirements, but when we get to the 2NF, it fails. We see there is a column whose data doesn't relate to the table's primary key. The primary key in this case is the HospitalID, a unique identifier for a particular hospital. The column in violation is 'Quantity'. Since the primary key is a unique identifier, asking how many hospitals in the area does not make sense. Instead, we can either remove the column completely or move it under the Location table.

That about sums up the work for this week. I posted the link to my video below where I quickly reviewed the three normalization steps and used the ALTER function in SQL to my the changes necessary to meet these requirements. Thank you and I'll see ya next week :)

https://www.youtube.com/watch?v=ngRU3mPXnG4

A Privacy Invasion and Physical ER

Patrick Byrne — Mon, 14 Sep 2020 03:54:10 +0000

COVID-19 VIOLATIONS!!

With the current COVID-19 pandemic, ideas for how to prevent the spread and limit exposure are being issued left and right. With schools opening back up again, many wondered how safe it would be for students to return to live on college campuses. There are right ways to handle the situation and wrong ways. A small private college in Michigan, Albion College, may have crossed a line. Their response to the pandemic was to make students download a contract tracing app known as Aura. This app was required to be downloaded by all students who attended the college and refusing would result in a suspension from the college. While contact tracing apps can be helpful, this one in particular tracked students' locations in real-time. The controversy comes from those who feel that collecting and storing students location was an invasion of privacy. The app would even inform the college if you left campus and would proceed to lock your access to the campus facilities.

My Thoughts...

Being a student at Penn State myself I too consider this app to be way too intrusive. I understand that the college had good intentions in using this app, but I would not be comfortable with my college knowing where I am at all times. If Penn State were to implement a protocol similar to this, I for sure would not accept that. I would sooner take the semester off, than sign away my privacy to the university like that. While I understand the tremendous threat of COVID-19, tracking students at all times is not the answer. I'm all for being safe and cautious in regards to the virus, but I would consider this invasion of privacy a greater threat than the virus. If colleges felt that the threat was so high that they'd require an app like this, then I believe it would make more sense for colleges to remain closed.
I think that for any students that are living on campus, a more ethical approach would be weekly testing of ALL students and staff. If one tests positive, then move them to a designated place for them to be treated and quarantined. If you were to feel that something like this tracking app would be beneficial to the safety of everyone, then maybe you'd be better off just staying home for the semester. Sanitation and safety are crucial at the moment, a line needs to be drawn. And having real-time access to every students location is not the answer.

--Enough with that story, now here are my ER diagrams--

Conceptual:

Physical:

My Video

https://www.youtube.com/watch?v=e9ipStaUnOY&feature=youtu.be

COVID-19 ER Diagram

Patrick Byrne — Mon, 07 Sep 2020 03:57:27 +0000

I have been tasked with creating an Entity-Relationship diagram using diagrams.net to express a conceptual model for COVID-19. My diagram is below:

I have also included an example tuple regarding this particular diagram:

The Entity-Relationship diagram I have created consists of three key components: entities, relationships, and attributes. The entities are designated by the rectangles. The relationships are those with a diamond shape. And their respective attributes are marked with circles. The arrows show the direction of flow for the relationships.

Patient

The patient is the center entity of the diagram, hence its positioning in the middle. The patient hosts several different attributes as well as relationships to all the other entities. Most attributes concerning the patient are biological truths, such as age and gender. The attribute of health conditions is very important to this diagram since we are concerned about COVID-19. If the patient has underlying health conditions, he or she is more at risk for contracting the virus. It is essential to know this information. Being physically active is essential in maintaining a better overall health. Being physically active means you are likely a more healthy person and have more success in fighting off viruses.

Location

The location entity relates to the patient in that the patient lives in said location. We see 5 attributes associated with this. We have the country and city/town to know where exactly the patient is living. The population of this location is important so we have an idea of the crowds you could be in contact with. COVID-19 has forced us to set certain restrictions for the general population. Every state differs in their restrictions with some being more strict than others. And the number of infected provide an idea of how potent the virus is in said location.

Hospitals

Every location has hospitals. We assign hospitals with 6 different attributes. There are many differences between hospitals in the same location that are essential to consider. The quality of a hospital and its amenities are help to find where the care will be the best and you have an enjoyable experience. The capacity of a hospital is crucial because in places where COVID-19 hit hard, the hospitals are at max capacity, meaning some of those who need care cannot receive it.

Occupation

This entity consists of 5 attributes. Where you work can greatly impact your COVID-19 related decisions. The ability to telework is great so you can work from home. And depending on your field of work, such as healthcare, you may be putting yourself and others in contact with more people.

Behavior

We have 5 attributes associated with this entity: Partying?, Going to gym?, Travelling?, Wearing a mask?, and Social distancing?. All these behaviors impact your vulnerability to COVID-19. We know wearing a mask is essential and you hurt not only yourself, but others by not following this guideline. The more you go to public places, the more people you come in contact with, so limiting your travels and parties are crucial.

Risk

This is the sixth and final entity on this diagram. We see 5 attributes associated with risk: Infection Rate, Severity, Hotspots, Family Interaction, and Number of Infected. While there are so many attributes that contribute to the risk of COVID-19, I feel these are the most important. Knowing the infection rate can help you better assess the threat to you, along with its severity. If you have been near hotspots, your risk goes up. The older aged are more at risk and if you have a lot of interaction with your extended family, you are threatening their health.

--There you have it. A rundown of my COVID-19 ER diagram. Although much more detail could be added, the key points are included and that's what I feel matters the most. Our world is very chaotic at the moment and who knows when this pandemic will end. So I ask that you follow federal guidelines and don't be stupid.--

https://youtu.be/ozmv64_X3aA

SQL vs. NoSQL.. What do they mean??

Patrick Byrne — Mon, 31 Aug 2020 01:54:30 +0000

About Me

My name is Patrick Byrne and I am a Sophomore at Penn State University studying in the College of IST with a focus in Cyber Security. I am just beginning my career as an IT professional and this is almost all new to me.

I have always been interested in technology and electronics growing up. I have played video games all my life and was always truly fascinated with computers and how they work. I have taken a few coding classes through the University already and absolutely loved it. Aside from basic knowledge of the inner workings of computers and standard electronics, I have no prior IT experience. But that will change very soon. My goal with this degree in Cyber Security is to hopefully work for the NSA in their Cyber Defense division. When I look to the future and picture myself in this position, I feel nothing but excitement and passion.

This post will be the first of many. It will document my progression of becoming an IT professional. I can't wait to see my advancements from my early posts as time moves on and I hope you all will join me in this adventure. The video below marks my first steps in my adventure. I will be comparing the differences between SQL and NoSQL.

SQL vs. NoSQL

What is SQL?
SQL stands for Structured Query Language. This is a database that utilizes a relational data structure. The relational database means the data is structured vertically to recognize relations among stored items of information, like a table for instance.

What is NoSQL?
NoSQL is another type of data that uses a non-relational data structure. A non-relational database does not use the table/key model that a normal relational database would. It allows for a horizontally scalable data structure.

Popularity
SQL is much more a much more common database type given its strong data integrity through the use of ACID (Atomicity, Consistency, Isolation, Durability). While NoSQL allows for more flexibility and is usually faster than an SQL, the lack of ACID transactions make it a less appealing choice.

https://youtu.be/JgQvzEk3m1s