Cody Codes Code

This week concludes CST 363. Overall, the class was an interesting insight into database design, programming, and how to integrate databases into different programs and web applications. Being my first class ever on databases (not counting the very brief encounter I had with SQLite in an Android development class a few years ago), I was initially overwhelmed with learning MySQL, a completely new dev environment (Workbench), and how to integrate my existing, yet still somewhat elementary, Python knowledge with SQL and HTML in order to create a working application. My favorite part of course was designing the databases and writing the associated Python statements for the group project. The subject we decided to tackle was something I've wanted to do as a personal project as a long time, and it was great to see it come into fruition, at least on a smaller scale. I believe the most important thing I've learned in this class is how to think about and analyze moving la

This week, I learned about multiple version concurrency control and how it affects database design and operations. Multi-version concurrency control, or MVCC, is a solution for a common database problem - given a database with many different users, how can a database handle several transactions at the same or different times while preserving data integrity and maintaining performance? A database or system that uses MVCC can support two or more transactions at the same time. Because multiple users might modify or read data from this database at the same time, if these operations aren't handled correctly, this could lead to problems such as lost updates (two transactions happening at the same time, with one transaction overwriting the other), or phantom reads (reading data that does not reflect current or recently made changes to it, making it seem as if no changes were made at all).  One of the simplest ways to prevent problems like this from happening is locking rows or imple

This week,  I learned about b-trees, why they are used in database and file systems, and how to work with b-trees in a simple database implentation using Python. B-trees are used widely in databases and file systems because they offer O(log n) run times for read and write operations. Because b-trees are always balanced and maintain a constant height, it takes few read and write operations to access or write data to a disk. I find it especially interesting how b-trees are like a combination of a linked list and a binary tree, albeit with special properties, and I can see how they are used in modern file systems due to their performance. I also learned about how space is managed within a database system using pointers vs. occurrence tables with primary and secondary keys, and how pointers are faster in terms of runtime yet prone to memory errors and unintended read/write operations that can compromise data, versus indexed tables with primary and secondary keys which are harder to imp

This week involved a lot of work finishing up our group project, completing the exam, and brushing up on our SQL skills before the final exam. I had to learn much about OLAP queries, the type of user that would require them, and how to implement them into our existing project database this week. An OLAP approach to database queries is directly related to business intelligence and other types of metrics-based intelligence, so it was important to understand OLAP principles in order to incorporate them into our project.  Additionally, I also learned about how to effectively refactor/redesign a database using a star schema, and when it is most effective. Our artists and records database is a perfect example of an application that can utilize a star schema well because it is an internal application that benefits from simplified queries (who was the best selling artist of the year?) and fast aggregations. Data integrity is an issue, but it is not quite as serious as if the app was used b