CS Online Learning Journal

  This week we learned about programming languages and database programming. We explored the differences between imperative and declarative languages. Imperative languages, such as procedural and object-oriented languages, use control flow statements to determine the execution order of program steps. In contrast, declarative languages, such as SQL, do not explicitly specify how results are processed but rely on a compiler or optimizer. SQL stands out as a leading declarative language, often used for database queries due to its simplicity and efficiency.  In chapter 9 also talks about various database programming techniques designed to bridge the syntax and paradigm gaps between SQL and other programming languages. These include embedded SQL, procedural SQL, and application programming interfaces (APIs). Embedded SQL integrates SQL statements directly into programs written in another language, such as C, while procedural SQL extends SQL with control flow statements, creating a ...

  The website "Use the Index Luke" has a page on "slow indexes"  https://use-the-index-luke.com/sql/anatomy/slow-indexes   If indexes are supposed to speed up performance of query, what does the author mean by a slow index? The author refers to a "slow index" as an index lookup process that becomes inefficient due to the need for additional steps beyond the initial tree transversal. Indexes can become slow if the query requires the database to follow the leaf node chain extensively or perform numerous table data accesses for each matching entry. A slow index results from the combination of leaf node chain transversal and accessing table data, causing the overall query performance to degrade despite the presence of an index. 

  1. Briefly summarize 5 things what you have learned in the course so far. MySQL Workbench - I have learned how to use MySQL workbench and I think this tool is going to be beneficial in my future career.  SQL - I have learned how to write complicated queries in SQL. I think the fundamentals of SQL are easy to learn but it gets complicated once you start writing queries where you have to join multiple tables.  Database Fundamentals - Learning how a database works using Java is also beneficial for us as students. I think the Java labs are challenging but it helps us learn more about databases.  ER Diagrams - I have learned how to read ER diagrams. Sometimes ER diagrams get so complicated, but I think I have learned the fundamentals to be able to read simple ER diagram.  Relational Data - I have learned the importance of relational databases. Understanding many tables full of data can be challenging, this is where relational databases play a critical role in manag...

  Explain in your words what 3rd normal form is and why it is important. Third normal form, also known as 3NF, is a crucial aspect of database normalization. The third normal form makes sure that the data stored in a relational database is organized to reduce redundancy and improve data integrity. In other words, 3NF is a database schema design approach for relational databases which uses normalizing principles to reduce the duplication of data, avoid data anomalies, ensure referential integrity, and simplify data management. Adhering to 3NF in database design is fundamental for building efficient and reliable database applications. It not only helps maintaining clean data structures but also supports accurate and efficient query responses.  What is an SQL view. How is it similar to a table? In what ways is it different? An SQL view is essentially a virtual table in a database that is not physically stored. Instead, it is defined by a query. A view can contain all rows of a ...

  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. An example for this is when a company wants to analyze its employee's performance relative to their start dates. The query in English could be something like: "Find each employee's total sales and compare it to the average sales of employees who started in the same year." The query will display the employee's name, their total sales, and the average sales of their start-year cohort. Let's assume we have two tables, employees and sales. The employee's table contains employee_id, name, and start_date. The sales table contains the employee_id, sale_amount and sale...