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;