Ethan's Computer Science Journey

If indexes are supposed to speed up performance of query,  what does the author mean by a slow index?    https://use-the-index-luke.com/sql/anatomy/slow-indexes What the author, Markus Winand, means by a slow index is that indexes are designed to speed up queries by organizing data in a search-optimized structure. However, they don't always guarantee a fast performance. This can be due to scanning leaf node chains to find all matches and fetching table data for each marked row, often involving slow random I/O. Indexes don't always guarantee fast approaches, due to the cost of large/scattered data. As for myself, I've been trying to read every day after work. Some of the readings were a little harder to retain than others, but I've been trying to remain diligent. I've gotten a better grasp on databases and stuff like deadlocks. Just gotta keep at it. Links to an external site.

 Briefly summarize 5 things what you have learned in the course so far.   1. I've learned about database systems and basics, concepts and designs of a database and how it stores and manages data. 2. Relational model and how data is organized in tables, select, insert, update, and delete rows. 3. Keys and integrity constraints, primary keys, foreign keys, referential integrity, etc.  4. Complex queries including joins, subqueries, and aggregate functions. 5. Database design and structure, including database design principles, storage media, table structures, and indexing methods. List at least 3 questions you still have about databases. 1. We skipped chapter 3.9, so what is the full scope and application of relational algebra in database querying? 2. What are the best practices for designing complex queries to optimize performance while maintaining readability? 3. What is cardinality in database design, and how does understanding cardinality impact the way you model r...

 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 based on the results of a select query. It doesn't store the data but presents data from one or more tables. It is similar in that you can query a view using select like a regular table, it has columns/rows, and you can join, filter, or sort a view like a table. It is different in that it does not store data, just saves a query. It cannot define primary keys. Insert/Update/Delete not allowed. And cannot be indexed. 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 ...

 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. A scenario where keys wouldn't be necessary is finding pairs of instructors and students where the student's total credits are greater than the instructor's years of service. "List instructors and students where the student's total credits exceed the instructor's years of service." SELECT      i.name AS instructor_name,     s.name AS student_name,     i.years_of_service,     s.tot_cred FROM instructor i JOIN student s ON s.tot_cred > i.years_of_service;  -- Join on inequality What is your opinion of S...

 The first week of my third class, Intro to Database Systems, is about wrapping up. Due to complications and what not my previous weeks may not add up to week 17 but this is officially my 17th week. Relational database tables and spreadsheets look similar, with both having rows and columns.  What are some important differences between the two? Database Tables are designed with relationships between tables like foreign keys. Referential integrity and avoids redundancy through nomalization. Spreadsheets have no enforced relationships and has a lot of redundant data. Databases also have constraints like not null and check preventing invalid data.  Installing and configuring a database and learning how to use it is more complicated than just reading and writing data to a file.  What are some important reasons that make a database a useful investment of time?  Databases are important to learn because they have enforced rules and no duplicates. Ensuring there are no m...