Monica's CS Journey

Using this article :  Slow Indexes If indexes are supposed to speed up performance of query,  what does the author mean by a slow index?  What the author means by a slow index is to debunk the myth that if an index lookup is slow, it is due to the tree being unbalanced or broken. This is from the misconception that the use of indexes will allow for faster queries. Rather, an index is slow because multiple processes are involved when traversing the tree, allowing for more efficiency. Then comes the task of following the leaf node chain to fetch the data from the table, both of which might have to access several other blocks.  Therefore, it is unnecessary to rebuild an index for the sake of improving performance in the long run.

Briefly summarize 5 things what you have learned in the course so far.   The benefits of a database tables over spreadsheets Database tables allow more complexity over spreadsheet and are considered more advantageous when it comes to handling of large amount of data. This complexity comes in the form of data manipulation and differs in how data is accessed. With the addition of rules and constraints, the use of databases can help reduce human error made when handling data. There is also the benefit of data recovery, where in case of any failures, the system will revert back to the most recent database before failure. The difference between join, left/right join, inner/outer join A 'join' is a select query that will join different tables together by comparing columns between the tables. The difference between inner and outer join is that 'inner join' will only select matching rows between tables whereas 'outer join' will join them regardless of whether or not the...

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) ? SQL view is not a table itself but rather a way for one to look at the data from other tables, even up to multiple tables, in one query. It is the result of how one executes a 'select' query. However, you can run a 'select' query on it as if it were a table. But because it is not a table, it does not store any data and does not have a direct way of creating a primary key. It cannot be updated if it joins more than one tables or includes any aggregates. 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. ...

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. Joining on something other than keys would be required sometimes, such as when creating a table of properties that would be within budget for buyers. In this case, a left join of the properties could be done with buyers, but instead of this being done in equality, one would ensure the price of the property is less than the budget of the buyer. A query of this in SQL might look like: SELECT Name, Address From Buyer LEFT JOIN Property On Price < MaxPrice; 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 t...

Relational database tables and spreadsheets look similar with both having rows and columns.  What are some important differences between the two? Unlike spreadsheets, database tables can help constrain data types for each entry and allows references between tables, adding much more complexity into how data is accessed and manipulated. Spreadsheets are also more restricted to smaller databases that are shared between one or two users. Installing and configuration a database and learning how to use it is more complicated that just reading and writing data to a file.  What are some important reasons that makes a database a useful investment of time?  Some important reasons that making a database is a more useful investment of time is the fact that it can help minimize human error with the constraint of data. By being able to constrain rules to columns on how added data is to be handled, it also allows for more efficient automation. As databases grow larger and more complex, ...