CSUMB CS Online Learning Journal - Ryan

 This learning journal entry is in response to the following article: https://use-the-index-luke.com/sql/anatomy/slow-indexes Q: If indexes are supposed to speed up performance of query, what does the author mean by a slow index? In the article the author refers to a slow index as an index where the database traversing not only the index tree structure but also multiple leaf nodes to find all matches. What this means is that although indexes help the database close in on the requested data without having to look at every node, it can still be slow because it will need to follow multiple branches to ensure it finds all of the matching keys. With leaf nodes possibly having many matches the requested data can span across multiple blocks.  Slow indexes are a myth due to the mistaken belief that the index lookup only involves tree traversal when in reality is also involves following leaf node chains and fetching the table data where there is no limit to the amount of block access requests t

 This weeks learning journal prompts are: 1.  Briefly summarize 5 things what you have learned in the course so far.   2. List at least 3 questions you still have about databases. 1. SQL - Learning how to use SQL was great for me. I had never previously used SQL and I found it to be a very approachable and simple programming language. I learned how to perform queries and build tables to organize data in a manner that was accessible and practical.    Entity Relationship Diagrams - These diagrams I found to be very useful in the initial build out of a database and helps create a visual representation of entities, their attributes and relationships. The ERD can also be used to generate the code required in SQL to model the ERD.   Database Design Process - The text and lectures provided some good guidelines on the process to create a database. I learned how to understand the database requirements by speaking with the end users and also the process of analyzing, designing and building out t

  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. What is an SQL view.  How is it similar to a table? In what ways is it different? 1. From the research I conducted online, 3rd normal form is where a table is organized in such a way that there are no transitive relationships in this system. This means that the key and associated values of a table are directly related. For example, if you have a table with a course id (primary key), course title, instructor and salary in a table, the instructor and salary columns can be moved into a separate table because the instructor and salary are not dependent on the course id and title. 2. SQL views ar

Week 2 Learning Journal Prompt: 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. 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?   One example that I can think of where something other than primary keys would be needed is if you were doing an analysis on a specific aspect of the data. I think it would be useful if you were to compare something like a budget where there was a budget amount and actual cost. The primary key would be the transaction ID, but the budget would be a separate entity. Some