CST363 - Week 4

Using Java with SQL

This week my team implemented methods for a Java application that interacts with a web server and a database. A big part of that process is how to deal with SQL select statements. After a connection has been made to the database, a prepared statement is created as a string and sent as a query to the database. The results are read into a Java ResultSet object which is then iterated through using a while loop. While the result set still contains rows to read, we loop through each row and extract the data needed. A simple way to store the extracted data is in an array list. When we are finished reading and processing our results, we close the database connection. 

SQL Injection Attacks and Parameterized Statements

SQL injection attacks occur when a malicious actor sends potentially damaging commands to an SQL database without authorization. An example of this exploit occurs when a website allows users to send commands to a database that are not checked for safety first. Certain characters, such as ", are treated a special way by databases and must be accompanied by an escape character, \, in order to ensure that an error does not occur. If they are not, a command such as "drop database" can be deliberately added to a malformed string and the database will interpret it as a normal command. 

Parameterized statements, also known as prepared statements, securely wrap SQL statements. Instead of placing data in a string being sent to a database, the locations of data in the string are replaced with a ? and then later set using programming methods. For example, the SQL command "select * from mytable where name='Michael'" would be replaced by the prepared statement "select * from mytable where name=?" and then a method, such as setString(1, "Michael"), would be used to concatenate the data to the string before sending the query to the database. 

CST363 - Week 3

Database Normalization

Normalization in a relational database is necessary to ensure the integrity of data and avoid data anomalies. Sometimes the tables in databases are not efficient. For example, a single table should not include multiple entities, such as patient, doctor, and pharmacy. Instead, those entities should be split into 3 tables, each containing data unique to those entities. If the entities and their data are in the same table, it makes it very hard to update data and will almost certainly lead to data corruption/duplicate entries. It is better to have unique tables for the entities and link them relationally.

Third normal form (3NF) provides the near-strictest rules for designing database schemas. There is another form that is used on rare occasions, called Boyce-Codd normal form, but generally 3NF is good enough to ensure data integrity. To place a database into 3NF, it must first be placed into 1st normal form, which is a basic set of rules to follow (such as ensuring only 1 value is stored in each column of each row). Then, we place the database into 2nd normal form, which is necessary to remove partial dependencies. Partial dependencies exist when a column does not have a relation to all of the table's primary composite keys. One way to solve this is to move the partially dependent column to a table in which it fits better. Finally, 3NF occurs when all transitive dependencies are eliminated. That is, when a column does not depend at all on its table's primary composite keys, it can be moved to another table. In the end, you end up with the key (1NF), the whole key (2NF), and nothing but the key (3NF), so help you Codd.

SQL Views

An SQL view is like a snapshot of a query. Sometimes when trying to combine tables in complex ways it is easier to create and use a view that holds a partial answer to your question rather than attempting confusing sub-selects and joins. Several views with pieces of the puzzle can be combined to answer a complicated question. Views are also useful when a set of information will be used by several related queries. Rather than repeatedly calling the same select command, a view is created by calling that select statement just one time. This saves times and allows for more efficient queries. 

CST 363 - Week 2

SQL Join

SQL is very flexible in its ability to join tables together. Normally tables are joined by matching primary keys with foreign keys because it allows for improved data integrity and faster queries via indexing. However, it is possible to combine tables without using primary and foreign keys. This practice is generally not recommend or used for the majority of SQL queries, but there may be instances where a join can be completed using standard keys (as long as the data types of the columns match). Since I am new to SQL, I did a bit of searching and found this example. 

Suppose we have two tables, dispatch and orders. The primary key for the orders table, ORD_NUM, is used as a foreign key in the dispatch table. However, we want to join our dispatch table and orders table using the column ORD_AMOUNT, which is found in both tables but is not a key. Since ORD_AMOUNT is of the same type in both tables, we can join the tables using it. 

Example Statement

Select the dispatch number, dispatch date, and sum of all order amounts that are equal to the order amount corresponding to each dispatch number. Use the dispatch and orders tables.

SQL Statement

SELECT a.dis_num, a.dis_date, SUM(b.ord_amount) 

FROM dispatch a, orders b

WHERE a.ord_amount=b.ord_amount

GROUP BY a.dis_num, b.dis_date;

CST 363 - Week 1

A New Start

This week marks the beginning of my Summer 2021 semester. It feels odd telling my friends that I am already in Summer session as most of them are still in Spring session. The pace of this program is pretty intense and I feel as though I am on a crash course roller coaster. The course I am just starting is Introduction to Database Systems. I am excited to start this class because I've never worked with databases before. It's something I've wanted to learn for awhile but never got around to it. Hopefully a couple months from now I'll be able to incorporate them into my programs.

Journal Questions

Both relational tables and spreadsheets look similar because they have rows and columns and are able to hold tables of data and make calculations. There are important differences, however. 

• Each cell in a spreadsheet is unique and can hold any data type, whereas cells in the same column of a database table must be of the same data type. This can lead to errors in a spreadsheet that would not exist in a database, such as accidentally entering a string into a date field.
• Mathematical formulas can be stored in the cells of spreadsheets. Database cells only contain raw data and calculations are performed elsewhere.
• Extremely large data sets cannot be stored in spreadsheets. Excel, for instance, only accommodates a little over a million rows. 

Databases simply offer more in terms of data integrity, consistency, and security. Using databases for front-end application development is also standard. It does not make much sense to use spreadsheets to hold data when databases make the job much easier. Databases also make it easier for organizations to share and update large amounts of data.

A basic SQL select statement only requires the SELECT and FROM clauses. Select statements have a few optional parts for conditionals, ordering, and placing limits on the number of rows returned. Select statements can get a bit complicated, especially when nested or combined with other select statements, but they are simple in their most basic form.

CST 338 - Week 8

 Final Thoughts

So the class has come to an end. It's strange to me how quickly it passed. I feel like I was just getting the hang of coding with my team, and now we'll be split up for the next term. In any case, I am finished with my final project and all assignments for the class. My advice for an incoming cohort is to be patient with your teammates, teach them what you can, and don't be afraid to ask for help. The class may seem trivial to some, but there are important lessons to be learned.  

The biggest takeaways for me from this class were how to create software with a team, how to follow a spec without doing your own thing, and how to create software using the MVC design pattern. I feel like I will be using this pattern from now on (wherever applicable), and I have an interest in trying out other patterns. The experience with UML diagrams was also valuable. I feel like this course has set me up for success in a real-world programming environment.  

CST 338 - Week 7

Android App

Today I wrote my first Android app. Although it's a basic school assignment, it taught me a lot about making mobile apps. I attempted to learn how to make them a few years ago but gave up without figuring out how to get the code to run on my phone. Seeing "hello world" print out on a virtual device was not very thrilling. Today, I did my app testing directly on my Pixel 4a device. I also figured out how to install it so that I can show it to people everywhere I go, even if they don't really care. 

Now that I have a better understanding of how to make Android UIs (which seems to be the real magic behind these apps), I'd like to make a mobile version of my old bill management program. I am not sure if my mom will use it, but it's worth a shot. Other than that, I don't really have any good ideas for mobile apps at the moment. It seems like everything I want to make has been done already, so I need to find something I am passionate enough about to improve. 

 

Keeping Up

This week unlocks the last 2 modules for this class. It's crazy to me how fast these courses go, but in a way I am glad I don't have to spend too much time in any single class. In preparation for the final assignment, I have decided to finish this week's assignments early. An interesting thing I realized as I was making my app is that I approached it with the MVC pattern in mind. It seems that this class has met its intended purpose of helping me learn how to design software in a more professional manner. 

 

CST 338 - Week 6

UML Diagrams & MVC

I have very limited experience with UML diagrams. My professor at community college used them when teaching us about interfaces and inheritance, but I never had to make one myself. I have made class flowcharts, however, most notably this one. UML diagrams in their most technical form are generally worthless for me, but it's nice to know how to decipher them. The diagram I created for this week's assignment is about as close as I hope to ever get to an official one, but I suspect we may be required to make them again. Based on my internet readings (Reddit browsing), it seems that their usage may be rare, but some large companies do use them.

We also discussed the MVC design pattern this week (Model-View-Controller). In this model a controller class acts as a middleman between the view and the model, who should not have any knowledge of each other. The controller sends and receives data from the model and then updates the view using the received data. Its job is to make sure that what the user sees on the screen and what the computer sees in memory are the same. This is very helpful because it separates projects into different branches, making it easier to build and debug. 

Multithreading

My first attempt at multithreading occurred when I was writing an IRC client a few years ago. I realized that it was not possible to interact with my GUI while a socket connection was open. The infinite loop of the connection needed to run on its own thread. Unfortunately I failed miserably and was forced to abandon my aspirations to create a full-fledged client. A simple IRC bot would not be the same. Eventually I did learn how to use simple threads, and now I am able to implement multiple threads with synchronized methods (especially using TimerTask in Java).

Keeping Up

This week was a wild ride. What was meant to be an easy week turned into an academic nightmare. I would rather not get into the details on this public blog, but I experienced what it's like to be investigated for academic dishonesty. Fortunately the true offender confessed, but this should never have happened to begin with. I forgot an important lesson I learned just a couple of years ago.

When I was a newly promoted Corporal, I trusted my Soldiers to perform a task on their own and did not check that they correctly completed it. Although things worked out in the end, I learned to always "trust, but verify." In this case I did not verify the integrity of code that was submitted to me. Some may say that it's not my job to do so, but it sits on my shoulders knowing that my team had to go through that.