CST363: Week 3 (Week 19)

 1. 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)?

A view table is generated by a view query - a SELECT statement which uses existing tables as inputs. Views are similar to tables in that they are both organized/presented in row-column format. There are a few advantages that views have over tables. For example, views can omit data that was contained in the base tables. For example, a table containing patient data (patient ID, SSN, home address, medical history,etc) was used in combination with a table specifying zip codes at risk for certain ailments, a view could be created to show only names and addresses for patients who should be notified of the risk without divulging SSNs. Views persist in the database so they are also useful for saving complex queries for future reuse and improving readability by not having to use the verbose definition of the query data every time. Lastly, they are a way to save optimized queries for future use by database users. Similar to to the way that some platform/chip makers distribute software libraries with their developer kits and hardware.

Views are different from standard tables in that the data generated by the view is not stored. Rather, every time the view is referenced, the view query is executed.

The View is also subject to the limitations of the underlying base table. For example, an insert into a view that doesn't include the base table's primary key is not allowed. It would generate a null primary key which is forbidden. For aggregate values in view tables, the problem is that back propagating the insert to the base tables would would lead to undefined behavior so it is forbidden. For example, inserting into a view with rows containing AVG outputs of 3 base table values per entry. If the view insert operation set the AVG for the new entry to 4, how would SQL know whether to populate the 3 input values to (3, 4, 5), (5, 4, 3), (4, 4, 4), (8, 0, 0), etc.? Lastly, in view table entry delete operations the base table primary and/or foreign keys may not be included in the view so this could lead to ambiguities if for example the delete operation matches a view table entry that matches multiple base table entries. This is another forbidden operation that is specific to view tables and not standard tables.

2. 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 such as Java.  What features are similar , and which are present in one language but not in the other?  For example,  Java has conditional if statements which are similar to SQL WHERE predicates,  the SELECT clause is similar to a RETURN statement in that it specifies what data or expression values are to be returned in the query result (although it is strange that a statement should specify the RETURN as the first part of a SELECT.

It's not Java but the "drop database if exists <database name>" directive is reminiscent of the C++ directive to prevent include the same code twice:

    #ifndef HEADER_FILE_H
    #define HEADER_FILE_H
    ...
    #endif

As for Java, even though it is not outwardly apparent you can see that there's implicit use of loops and iteration when operations are performed using multiple values in a column. For example, when we use SUM or AVG on numeric values, SQL has to iterate through each row and add each value to the running total. In the case of AVG, it also must have a count to use as a denominator.

The way views are executed when referenced and not cached is reminiscent of volatile variables in C/C++/Java in that they are accessed in real-time.

There are also SQL and database constructs where we can see that something like references(Java) or pointers(C/C++) are used. In single and muli-level indexes the behavior is similar to a using the name of a reference which stores a memory address, to access the memory at that location, which contains the actual entry, or in the case of multi-column indexes, another index.