CST 363 Week 6 Report

Summary of Week's Learning

This week, we learned a ton about spring web server and also JDBC API. JDBC is a Java based API that enables us to interact with databases. The spring web server is like a programming component that sort of acts like an environment in which we can create web applications. We learned how to connect these two with a custom made SQL data schema, which was actually pretty fun. I really liked how we were able to see real time updates from our spring web server updates in our Mysqlworkbench queries. This makes sense because they were using the exact same database, through JDBC api access. Overall, it was a great week full of learning and I look forward to learning about mongoDB next week.

CST 363 Week 5 Report

Generally speaking, an index provides faster read times for queries in SQL. There are three elements that, when combined, can prove an index search to be slower than expected, however. The first regards the leaf node chain. The second is accessing the table. If the hits point to many table blocks, that is a bad sign for performance. The third is fetching the relevant data from table(s). Databases can actually be asked how they use an index. In the oracle database, which has three operations for describing an index lookup, the index range scan, which performs the tree traversal and follows the leaf node chain to find matching entries, is the default fallback operation if multiple potential entries are triggered. It is precisely in this scenario where a "slow index" can manifest.

CST 363 Week 4 Report

Five Things I Have Learned

1. The first thing I have learned in the course so far is how to write SQL queries. I thought it was actually pretty cool to learn, because it is a new language. The language is kind of cute to be honest. 

2. The second thing I have learned in the course is how to join tables in SQL. This is a big deal and we spent almost a whole week on the topic. I like that we have the flexibility to choose which tables to join, and whether it is outer or inner. Creating views is also really cool and useful for analyzing data. 

3. The third thing I have learned in the course is how to create an ER diagram in MySQLWorkbench. This feature is actually simple and effective, I intend on exploring it more going forward. 

4. The fourth thing I have learned is how to to program mock tables and tuples in Java, as well as querying and indexing. This is so far a great experience because it is not only teaching us about databases but also keeping our programming skills sharp. 

5. The fifth thing I have learned is the normal forms in relational databases, which are fundamental concepts to help ensure databases are efficient and stable in the long term, especially when migrating them. There are also fourth and fifth forms, but more often than not are unnecessary to work with, given that the first three forms will take care of the vast majority of database designs. 

Questions

 1. Is it possible to use directly use a SQL database in Java (or any other popular language), and if so how? 

2. Is there a way to create graphs from SQL queries? 

3. Can databases be encrypted for security purposes? 

4. Realistically speaking, how often is the fourth and fifth normal form actually considered in SQL? 

5. What is the fundamental difference between SQL and NoSQL?

CST 363 Week 3 Report

 1. The third normal form is a concept in normalization that helps to eliminate unnecessary data and "entanglements" between table columns. Every column in a table that is not a key must only rely upon the table's primary key in order to maintain data integrity, less redundancy, and a more straightforward database. Normalizing tables is especially important for ensuring that queries are more efficient and faster, as well as for future scalability of the database. 

 2.  An SQL view is an "abstract" table that is temporarily created in order to help users analyze data. Some similarities to a table include: the ability to query, columns and rows, and ability to join them together. A key difference is that, whereas a table is physically stored, the view lacks persistence and is only temporarily created. Another difference is that views lack indexes and constraints, whereas tables are able to support them. Views are also typically not able to be updated unlike tables. 

CST 363: Week 2 Report

 1. One case in which an SQL join would be used on columns on non keys are in this given example: lets say we have two tables named "Products" and "Orders". If we were to require the total sales amount for each type of product, then we would have to join the two tables by the product type column, which may not necessarily be the primary or foreign key. This means that we would write in SQL as such:

 select a.product_type, sum(b.total_amount) as total_sales_amount from orders b

join products a on b.product_type = a.product_type

group by a.product_category;

2.  SQL as a language is actually very straightforward, the only confusing parts are the terms one has to learn in order to accomplish data manipulation. For example, it is tricky to remember when to use the term "on" and "as" when writing prompts. It is easy to forget what each term does, ultimately. However, with enough practice I am certain it will become far easier.