One More Byte CS Devlog

 Final learning journal post for 363 Intro to Databases Briefly summarize the 3 most important things learned from this course 1. One of the most immediately impactful things I've learned from this course was the advanced uses of SQL clauses to help refine a queries results. While I was aware of how to use SQL in the simple SELECT FROM WHERE format, expanding on this with the inclusion of things such as JOIN tables creates a more robust method for data retrieval and organization. 2. While I had known of SQL and its basic uses coming into this course, I gained a deeper understanding of the inner workings and design of relational databases. Learning how Schemas, Tuples, and Indexing is designed has strengthened my ability to write more efficient and optimized SQL queries. Not only does this information help in actively using SQL, but it also gives me insight on how to organize a databases properties when designing one so that it is efficient. 3. Learning the differences and advantage...

 Compare MongoDB to MySQL Similarities Both are database models that are used to store data and use querying to filter and sort stored data. Additionally, both use indexing to help with returning queries. Both types of databases also have APIs to help integrate them with programming languages like Java so that they can be used in large scale projects seamlessly. Differences While MySQL uses table based schemas organized in documents and rows, MongoDB uses a document based storage where information is held in filetypes like JSON. Because of this MySQL is more structured and designed to be less flexible and rigid, where as the design of a database using MongoDB can be more easily manipulated and altered. When would you use one over the other It is best to use MongoDB in situations where the documents will be altered more frequently and when the structure for the database needs to be more flexible. With MySQL's more structured database you are able to make better use of its complex qu...

 What I have learned this week This week I learned how database programming bridges SQL with traditional languages like Java. I learned that languages generally fall into two categories: imperative, which execute step by step, and declarative, like SQL, which is more focused on outcome than process. Being declarative makes SQL optimal for things like databases. We also explored Java Database Connectivity which enables Java to interact with relational databases using the MySQL Connector. I also learned the differences between Statement, PreparedStatement, and CallableStatement. In this lab I was able to successfully connect Java to MySQL and run queries. This gave a clearer picture of how APIs can be used to allow for Javas object oriented programming code to work alongside SQL's declarative language for use in databases.

 Five things I've learned in the course so far. 1. How to modify basic querying to get exact results and organize them too. Using key words such as SELECT, FROM, and WHERE is easy enough, but after having learned to use additional qualifiers and arguments like age < 21 AND age > 65 to further refine results has made SQL feel so much more robust. 2. Baseline knowledge of how Tables, Schema, and Tuples work. While I knew SQL is a database language that can be used to store and retrieve data, the inner workings of it were never clear to me. After following the labs and building methods that work with a Schema and Tuple's the inner workings of SQL make more sense. 3. JOIN tables. Being able to use SQL to compare and combine tables in different ways makes organizing data much easier. Inner join allows for combining Tables by selecting only the matching rows between the two, where as Outer join combines the full set of rows from both tables. This can be further defined using Le...

The web site "Use the Index Luke" has a page on "slow indexes". https://use-the-index-luke.com/sql/anatomy/slow-indexesLinks to an external site. If indexes are to speed up performance of query, what does the author mean by a slow index? Sometimes indexes can be used in inefficient ways leading to slow queries despite the use of an index. This can happen in different ways but the main two come from either an index not properly filtering out rows or a query can be written in an inefficient way. When an index is designed in a way that many rows with duplicate values are returned it could be the result of an indexed column designed with low selectivity. In the instance of queries written in inefficient ways it could be due to misusing the indexes design resulting in many rows being returned.