Edward's CS Journey

Slow Index In the article "Slow Indexes" on the webpage "Use The Index, Luke," the author mentions that indexes can still be slow in some ways, even though they are meant to make searches fast.  First, the databases need to follow the leaf node chain to see if there are any other matches besides the one that is found. Second, table data may be scattered across many table blocks, which would take an index search longer to execute as the search may need to access each table from different leaf nodes.  Overall, having an index may make a query faster, but it may also be slow because it has to follow the leaf node chain in addition to the tree transversals and obtain data from the tables where the data is scattered across table blocks. These steps may require an index to access many blocks when searching, which would lead to a slow index. 

 Part 1: 5 things I have learned so far We are now halfway through CST 363. Below is what I have learned so far:  In database design, where large databases are needed, there are three phases to ensure the database's functionality. First, we need to complete an analysis of the database and identify the entities, relationships, and attributes. Then, we need to develop the logical design of the table, which shows the columns, rows, and table names and identifies items such as keys. The final step is the physical design which includes adding indexes and specifies how data is organized on storage media.  Within the database design, the logical design is more important as it affects query results. The physical design affects query processing speed but not the result.  A relational data structure is based on set theory, where a set is an unordered collection of elements, and a tuple is an ordered collection of elements.  To write queries, we use SQL statements to insert, select, delete, a

Part 1: 3rd Normal Form 3rd Normal Form is when all columns that are not considered a key depend on the key of the table and not a non-key column. All data is connected by the defined keys of the table, and columns that are not keys are not dependent on each other. All columns are dependent on the primary key(s). If more than one key exists, all columns are dependent on all keys, not only some keys. This is important as it will prevent data from being repeated as only one specific key(s) is being used to reference the data.   Part 2: SQL Views An SQL view is a table that can display columns and rows from a base table. The data in the view table is selected by a select query. Creating these tables can help protect sensitive data, save information from complex queries, and save information from optimized queries. It is similar to a table in that it has data that can be referenced when using queries and that can be joined with other tables. View tables are different from based tables in t

  Part 1: Joining tables on columns that are not keys An example of joining two tables using columns that are not keys would be in the example below:  Query: select I.ID, I.name, C.course_id, C.title from instructor I inner join course C on I.dept_name=C.dept_name; English sentence: List all instructors and the possible courses they can teach in their subject area.  In the example above, we are combining an instructor table and a course table, where the primary keys are instructor ID and Course ID. We are connecting the two via dept_name, which is a column they share. The join above will connect each instructor to a possible class they can teach based on their assigned department.  Part 2: Opinion of SQL as a Language SQL is a pretty fun language to learn, even though it is a bit stressful at times. I think of it as a puzzle where each part matters in obtaining the right results (just like math!). It is easy to learn and use but one must be careful in each detail of the data that nee