CST363 - Week 7

Prompt for this week's journal entry. Compare MongoDB with MySQL.

What are some similarities?

On the surface, both MongoDB and MySQL are database management systems designed to store and retrieve data. Both systems utilize indexing and support various programming languages (such as Java, which we worked with for both systems throughout the semester).

What are some differences?

I would say the most noticeable difference is that, unlike MySQL, MongoDB does not require a predefined schema with tables, rows, and columns. Additionally, MongoDB uses its own query language, while MySQL uses Structured Query Language (SQL).

When would you choose one over the other?

You would want to use MySQL when handling structured data with many relationships between tables and involving complex transactions, such as in finance, where stricter data validation is necessary.

In contrast, you would opt for MongoDB when dealing with large amounts of unstructured or semi-structured data, especially if you frequently need to make changes to the schema and require scalability.

CST363 - Week 6

Summary of This Week's Learning

This week, our team created a web application in Java utilizing Spring and JDBC, which allowed us to work with the pharmacy database we designed last week. It was exciting to see the changes in the database, especially since we had mostly been working with SELECT statements in MySQL. Last semester, my team used an API for a movie database for our final project, but I had never worked with Spring before. In addition to this, we conducted peer reviews of our classmates' pharmacy database designs from last week, completed the short weekly quiz, and completed a MySQL homework assignment.

CST363 - Week 5

Include in your journal entry for this week the following

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 supposed to speed up performance of query, what does the author mean by a slow index?


While indexes are generally used to speed up the performance of database queries, there are cases where they don't work as efficiently as expected. Markus Winand explains that a "slow index" occurs when the database has to follow a chain of leaf nodes continuously checking for matching entries, and also access the table. These steps may involve accessing many blocks, which can slow down the query rather than improve its performance.

CST363 - Week 4

Briefly summarize 5 things what you have learned in the course so far.

Prior to this class, I had minimal knowledge about databases, aside from a little from the previous Java class. So far, the basics of databases and writing queries is the first thing that comes to mind. We’ve also learned about the importance of data integrity through the use of primary keys, foreign keys, and constraints. Additionally, we covered how to use different join operations to combine data from various tables, as well as data normalization to further organize information and eliminate redundancy. Finally, we explored indexing and how it can improve the performance.

List at least 3 questions you still have about databases.

I’d be interested to know more about the differences between SQL and NoSQL, but this is something I will likely research on my own. I’m also curious whether SQL supports error handling similar to Java or other programming languages. I briefly searched this topic last week, and from what I found, it seems that error handling in SQL relies on conditionals rather than things like try-catch blocks. Lastly, I would like to gain a deeper understanding of how massive databases are maintained, as it seems quite daunting.

CST363 Week - 3

What is an SQL view. How is it similar to a table? In what ways is it different (think about primary keys, insert, update, delete operations) ?

An SQL view is a virtual table created based on the result of a query. Views do not store data physically; instead, they provide access to data from the underlying tables. This is especially helpful for sensitive data you don't want everyone to access. Both views and tables have rows and columns, and you can use SELECT statements on both. However, views do not have primary keys and are not typically used with INSERT, UPDATE, and DELETE statements.

We have completed our study of SQL for this course. This is not to imply that we have studied everything in the language. There are many specialized features such as calculating rolling averages, query of spatial data (data with latitude and longitude) coordinates, and more. But take a minute to think about how SQL compares to other programming languages such as Java. What features are similar , and which are present in one language but not in the other? For example, Java has conditional if statements which are similar to SQL WHERE predicates, the SELECT clause is similar to a RETURN statement in that it specifies what data or expression values are to be returned in the query result (although it is strange that a statement should specify the RETURN as the first part of a SELECT.

Honestly, I haven't thought about this too much. The first thing that comes to mind is statements like INSERT, UPDATE, and DELETE. In Java, we don't manage data in that way. Another aspect that comes to my mind is error handling, which is quite robust in Java. I'm not sure how in-depth SQL's error-handling capabilities are when it comes to exceptions.

CST363 - Week 2

This week we were tasked to answer the following questions

SQL has the flexibility to join tables on any column(s) using any predicate (=, >, < ). Most of the time the join will use equality between a primary and foreign key. Think of example where joining on something other than keys would be needed. Write the query both as an English sentence and in SQL. If you can't think of your own example, search the textbook or internet for an example.

I think one way to look at this in English would be sales in a store. For example, we want a list of the total stock of a product along with the total number of that product the store has sold.

In SQL, I think it would look like this:

SELECT p.product_name

    SUM(p.quantity) AS current_stock

    SUM(s.quantity AS total_sold

FROM products AS p

LEFT JOIN sales AS s ON  p.product_name = s.product_name

WHERE p.product_name = 'apples'

GROUP BY p.product_name;

What is your opinion of SQL as a language? Do you think it is easy to learn and use? When translating from an English question to SQL, what kinds of questions do you find most challenging?

Aside from the basic SELECT SQL problems, I find SQL fairly difficult to understand compared to languages like Java or Python for some reason. I particularly struggle with JOIN problems and often have to refer to documentation. There are other aspects that I constantly get mixed up, and it just doesn't come as easily to me for whatever reason.

CST363 - Week 1

Relational database tables and spreadsheets look similar, as both have rows and columns. What are some important differences between the two?

In relational databases, data is organized into tables with defined keys (such as foreign keys), whereas spreadsheets organize data in rows and columns without utilizing any keys.

Installing and configuring a database and learning how to use it is more complicated than simply reading and writing data to a file. What are some important reasons that make a database a useful investment of time?

Using constraints (like primary and foreign keys) allows for consistent and accurate data. Spreadsheets do not have this feature, which can lead to errors. Databases can also handle large amounts of data and facilitate easier scaling in the future.

What do you want to learn in this course that you think will be useful in your future career?

Databases are incredibly useful, and although I don't have much experience now or a clear idea of how I will use them going forward, I always enjoy learning and expanding my knowledge.