DEV Community: MTrageser

Normal data and ALTER to improve your database

MTrageser — Mon, 28 Sep 2020 03:03:34 +0000

Hello and welcome back to Today in Tech.

This week's blog post will discuss what normalization of a database is and follow up by explaining how to implement and use normal data types to improve my COVID 19 database. As a recap from last week, we were tasked with creating a database from our entity relationship diagrams modeling one’s risk of contracting COVID 19. Using “CRUD” commands of SQL, I demonstrated how to manipulate data and construct the database. This week using normal data to my advantage, I will be showing how to improve the readability and flow of the database.

Normalization, in short, is the process of constructing a database to fit a certain mold of data. Data is classified as normal on a series of 6 levels. For this week’s post I will be sharing details on the first two levels and how to use normal data within my own database. Normal data is cumulative from one level to the next, meaning that whatever is needed to be considered first normal must also be included to be a second order classification.

The first type of normal data I will be discussing is first normal form data. The first of 6 levels, naturally first order normal data should be the most simplistic. In order to be considered first order normal, data within a table must be unique to a primary key, the data must also be reduced as far as it can be with no repeated groups.

Seen below is my Occupation table within my database. This data is considered to be first normal form because it meets all criteria specified above. The primary key in this case is OccupationID which then is inclusive of all other pieces of data in this table.

Using knowledge of how to construct data so that it is normal, cleaning up your database via modification becomes easier. Using the ALTER command, you can delete certain sections of your database that otherwise would be unnecessary. Data might need to be altered because it is too similar to a neighboring data set. Seen below, is my Patient data table. Two of the columns from last week's creation appear to be strikingly similar. FamilyHistory and ExistingConditions essentially mean the same thing. As I said before, I created this database from the ER diagramming I had been working on in the previous weeks. Until learning of how to simplify a database, I was unaware of how similar these datasets look. Using the ALTER command, I can edit the setup of this table to improve its simplicity.

The next level of complexity within a database would naturally be second normal form. To be classified as second normal form, data must first fulfill all qualifications of being first normal form as well as being exclusively reliant on the table’s primary key. An example of this is in my Hospitals table. All of the data within this table relates directly to the primary key of HospitalID. The remaining columns of data in the table are directly related to HospitalID and exclusively related to that primary key.

I’ve followed up this post with a screencast showing these definitions and tools in action. Thanks for reading and stay tuned for next week’s post!

Lab 3. Continued ER Diagrams and Data Privacy vs. Safety.

MTrageser — Mon, 14 Sep 2020 03:56:19 +0000

Hello and welcome back to the blog. Today’s post includes a refinement of last week's ER Diagram modeling risk of contracting COVID. I have made two diagrams one being a physical representation, including more detailed explanation of data types and relationships between entities. The other diagram is a conceptual diagram which is more reader friendly and provides the “big picture” of how all entities relate to each other. In addition to the diagrams and supporting screencast explanation, I will also be discussing my thoughts on a recent development in Michigan involving the mandated use of a location tracking app on a college campus. The app is designed to track where students go and work to limit and track exposure. I will be discussing just how ethical a practice like this is, and whether data like this should be used in this manor.

After reading the article by Zach Whittaker, I believe a practice like this is intrusive on the privacy of the students who attend this school. When these students made the choice to go to college, granted it is a private school, they were not signing up to be constantly tracked by the university. I think this type of monitoring is not necessary. The article discusses how students are not permitted to leave the “bubble” of campus and that they would face punishments if doing so. In my opinion this violates the students rights and privacy. Being a student myself I can say that there are so many reasons why someone would need to leave campus. Not being allowed to would be a serious restriction.

Furthermore, personally I would not accept these restrictions at my school. I believe it should be up to the people how they choose to social distance, and it should not be mandated by authorities. I would feel creeped out knowing that the university can always see where I am and have to constantly worry about using the app.

I think privacy of data should be more important than safety. Data privacy can be a very slippery slope, and if we as citizens give too much leeway, it could become a much more serious issue than just a college campus. Safety should be up to individuals, with help from authorities. It is not fair to the students that their location is given out at all times.

A more ethical way of solving this issue would be making this program happen on an opt in basis. I am in no way saying that this practice would be ineffective, I just do not believe it should be forced upon the students. If students want to be involved in limiting exposure in this manner, they should be given that choice. Looking at another possible solution, I think that an app with reward for being safe in limiting exposure would work well. Washing hands and keeping your distance are obviously good practices to limit exposure so perhaps if a student gets a notification to wash their hands and they do so, they could get extra meal points. This solution might not be as effective as direct access to students' location, however it is clearly more ethical.

Physical Diagram:

Conceptual Diagram:

Lab #2

MTrageser — Mon, 07 Sep 2020 03:53:32 +0000

Hello and welcome back to the blog.

In this week's post I will be discussing ER Diagrams and how they can be a useful tool when analyzing data. The ER Diagram (Entity Relationship) I made today is built to model one’s risk of contracting the coronavirus. I find this topic to be rather important because so much of the information we are given on the coronavirus is flooded in through biased media trying to convey their views.

As a student of technical information and as I continue to learn how to analyze data rather than jumping to conclusions, the ER diagram becomes a useful tool to assess how different aspects of one’s life can play into contracting the virus. Below in this blog post I have embedded a video explaining the ER Diagram as well as providing some sample Touples showing how different Patients would input data.

Even though the video spells out the different entities and how they are all interrelated, I think it would be of great benefit to provide justification as to how I chose to set up my diagram.

Patient
Obviously the patient in question when determining risk of contraction is very important. It is crucial to understand the patient as thoroughly as possible. Age, gender, and family medical history are clearly related to how the disease might affect a person however factors like fitness level that are specific to a patient might be overlooked.

Location
Geographic Location of the patient is a very useful way to try and weigh in on risk factors. Climate and temperature are important for considering how the virus spreads through the air. Population density and local attitude towards regulations in place are key ways to examine how much exposure a patient might face. Location also brings forth the variable of how effective the local hospital is in treating and testing for the virus. Some areas of the world might struggle in this department more than others.

Occupation
Everyone has an occupation in life whether that be student, retired worker, or 9-5 office worker. How you spend your day is important when considering how the virus might affect you. Someone who works as a construction worker outside will face different exposure compared to an 8 year old who is doing online class. Travel is also an important part of how your risk of contraction might increase. Staying home is clearly the best option however it might not be possible to do so.

Social Behavior
Lifestyle of the patient is arguably the most important factor to consider. If a patient chooses to go out and be in contact with others, they must also consider what consequences this will bring. Eating healthy food and getting enough exercise is crucial to try and limit the risk one might inherit.

Conclusion
Overall it is important to consider such a wide variety of variables when considering how great a patient’s risk of contracting the virus is. Using data and technical thinking is the best approach compared to reading into headlines that might not apply to you. The ER Diagram works as a tool to consider multiple patients and set predetermined bias aside.

Thanks for tuning in to this week's post. Stay tuned for next week as I look to build upon this knowledge and refine this model.

Lab 1

MTrageser — Mon, 31 Aug 2020 01:17:10 +0000

Hello everyone,

My name is Michael Trageser. I am a sophomore at Penn State majoring in Industrial Engineering. I am from 25 minutes north of Pittsburgh. My hobbies include doing fun things outside like hiking, kayaking, and snowboarding. I also like to go to the gym and watch my Pittsburgh sports teams, although watching the Pirates this year has been brutal! My interests in technology stemmed from playing at first desktop computer games on my family's old “dinosaur” computer. This then morphed into console video games. Every fall I use data from fantasy football to help me draft my winning team. I like how something as simple as fantasy football can be broken down and improved through statistics and technology. I primarily use technology to help me complete tasks in life that would otherwise be unnecessarily difficult. I choose to study engineering out of high school because I have always enjoyed being able to work on real world applications and I like the idea that when I have a job after college, my work will be bettering society as a whole.

I would say that my experience in an IT environment is limited as this is my first IT class at Penn State. Much of my coursework is heavy on physics and various math courses. This summer I was working as an intern for a machine health monitoring company based out of State College called KCF Technologies. My goal for this course is to gain some more exposure with different platforms within the vast world of tech. Hopefully I can take some things I will learn into my engineering courses as well as working in a technical setting later on. I would like to build myself as a well rounded engineer who can help solve a variety of problems.

My coding experience is quite limited (only one class back in early high school). I know that the future is online and being able to code would be a great asset for me to add to my tools I can use as an engineer to solve problems. My goal is not to become an IT professional because I know that is not where my strengths lie. I would rather use technical knowledge of technology to help me relate to others who are IT professionals. If I am able to “talk the talk” when conversing with those who are more technical than me, I know it would be a great advantage compared to being left in the dust.

My interest in Industrial Engineering specifically deals with how the field focuses on optimizing processes and solving problems on a large scale. I enjoy looking at “big picture” issues and how in many industries, everything relates to each other. I hope to use my voice and communication skills to share my work and help a team accomplish great things.

This blog will include weekly posts such as this on topics within technology. The blog will function as a way for me to share my work with the world while practicing using different platforms along the way! In this week's post I have included a video comparing SQL and NoSQL. I will discuss functionality and compare their applications.

Enjoy and welcome to the blog!