CS Learning Journal

  WELCOME TO WEEK 5 Learning Journal - Slow Indexes:   This week, I learned about the concept of slow Indexes. A "slow index" does not refer to a literal slow index, but rather to cases where using an index does not give the expected performance boost in query execution. This misconception comes from the misunderstanding of index functionality. An index lookup requires three steps:  The tree transversal. Following the leaf node chain. Fetching the table data. Therefore, the idea of a slow index is caused by a broken or unbalanced tree is incorrect. Databases use various operations like IDEX UNIQUE SCAN and INDEX RANGE SCAN depending on query specifics. A slow index highlights the complexity of optimizing databases and the importance of multiple factors beyond simply having an index to enhance performance. 

WELCOME TO WEEK 4 Learning Journal:       I can't believe we are halfway through the course! I have really enjoyed this course and all the material I have learned so far. One thing I have learned so far is that for large complex databases we need to complete 3 phases:  Analysis - Specification of database requirements without regard to implementation. Logical Design - Implements of database requirements as tables, keys, and column in a specific database system. Physical Design - Addition of indexes and specification of how tables are organized on storage media.      I also learned how join queries work. The select statement combines two tables (left and right tables) into a single result using the = operator. However, these columns must have comparable data types. There are many types of join queries such as:  Inner Join: Selects only matching left and right table rows. Full Join: Selects all left and right tables rows regardless of matching. Left Join: Selects all left table

  WELCOME TO WEEK 3 Learning Journal:  1. Someone described normalization rule as  "a non-key column depends on the key, the whole key, and nothing but the key, so help me Codd."  Key refers a primary or other candidate key of a table.  If the key has multiple columns, then "whole key" means  all columns together and not just some part of the key.  Explain in your words what 3rd normal form is and why it is important.  - 3rd normal form is a way or organizing data in the database that avoids repeating information. There are two rules that are important: each piece of data should be stored in one place, and the information that does not relate to the main data should be separated.  - It is important because it prevents mistakes in the data and makes the database more efficient. It is also easier to do changes on the database if it is needed.  2. What is an SQL View?  - An SQL view is a virtual table that provides a specific view of the data. For instance, admin can s

  WELCOME TO WEEK 2 Learning Journal - SQL:  1. 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.      An example that I found is suppose we have two tables, 'customers' and 'stores', and we want to join them based on the location of the store and proximity of the customer to these stores. Lets say the state or even city.       The query sentence would be written as is: Join the customers table with the stores table based on proximity of customer addresses to store locations.       Query: select c.customer_id, c.customer_name, s.store_id, s.store_name from customers c cross join stores s where ST_distance(c.customer_locat