CS Online Learning Journal

    Week 5 – CST– 363 Introduction to Database Systems The web site  "Use the Index Luke"  has a page on "slow indexes."    https://use-the-index-luke.com/sql/anatomy/slow-indexes Links to an external site.   If indexes are supposed to speed up performance of query, what does the author mean by a slow index?  What the author refers to as slow indexes is not that indexes are slow but that there are situations that cause indexes to perform slower. He mentions two ingredients that cause indexes to perform a slow lookup. The first is the leaf node chain. The database must read the next leaf node to search for matching entries after it has found the node it was looking for. The author points out that an index lookup must perform a tree traversal and follow the leaf chain as well. The second ingredient he mentions is accessing the table after finding a matching node. There is an additional table access for each hit and a single node can contain hundreds of hits.

  Week 4 – CST– 363 Introduction to Database Systems This week is halfway through the course. Briefly summarize 5 things you have learned in the course so far. List at least 3 questions you still have about databases. The five things I have learned in the course so far are the structure of relational databases, SQL syntax, database design, normal forms, and how databases store data. The relational database structure is organized using tables, which have names, fixed tuples of columns, and a varying set of rows. A column has a name and a data type. A row is a tuple of values and does not have a name. Each value belongs to a column and the column’s data type. The data type is used to assign a value to a column, such as int, char, and varchar. SQL is a high-level computer language for storing, manipulating, and retrieving data. It uses statements which are complete commands composed of one or more clauses. The most common clauses included Select, From, and Where. There are also co

  Week 3 – CST– 363 Introduction to Database Systems 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 to 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 own words, what 3 rd normal form is and why it is important.   The 3 rd normal form is a rule used to design tables that eliminate redundancy. Redundancy is the repetition of related values in a table. When a table is in 3 rd normal form, all the non-key columns depend on the key only. In case of a composite key, non-key columns must depend on all of the columns that make up the primary key. This is important because when non-key values depend on other non-key values it causes redundancies. Redundancies in a database can cause problems because all related values must be changed when updating, which m

  Week 2 – CST– 363 Introduction to Database Systems 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 an 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.   (Found example in stackoverflow). In SQL you would need to use something other than keys when you need to join two tables that do not have keys in common. An example would be two tables that have one column each. There would be Table A with column CustomerId and TableB with RewardCode. The query would be, join TableA with TableB. Select tA.CustomerId, tB.RewardCode From (Select customerId,      ROW_NUMBER() OVER (ORDER BY CustomerId) as TableA_RowNumber From TableA) tA Inner join (Select RewardCode,      ROW_NUMBER()