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.

CST363: Week 2 (Week 18)

 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.

If a manufacturer imports raw materials, a Materials table migth contain columns for the material name (e.g. aluminum, lithium, lumber, etc.), supplier name, and address fields for the supplier including a column for the country. In that case the primary key would be a composite key formed by the material name and the supplier name.
A second table tracking tariff percentages might have a column for country ID (primary key), country, and tariff rate.
The join could be done using the country columns as follows to show what tariff rate is associated with each supplier:

SELECT *

FROM Materials M

INNER JOIN Purchases P

ON M.country = P.country;

What is your opinion of SQL as a language?  Do you think it is easy to learn and use?  When translating from an English question to SQL, what kinds of questions do you find most challenging?

So far, SQL hasn't been too difficult to learn. I would rate it as moderate difficulty since on one hand it is completely new to me and on the other hand the reserved words and organization are relatively intuitive. I need to spend more time reviewing the nested queries and the join operations to really get comfortable with them. They don't always translate to English as easily so it takes a bit more thought for me sometimes to see what is happening.

CST363: Week 1 (Week 17)

 Relational database tables and spreadsheets look similar with both having rows and columns.  What are some important differences between the two?

We've transitioned to the era of big data and databases are far better suited to large data sets than spreadsheets which become unwieldy after a time. Database systems have built-in features that allow atomic operations and ensure data integrity. Relational databases also have features that support linking tables for example the function of primary and foreign keys. Overall databases provide a broader range of operations and flexibility than spreadsheets which are better suited for simpler applications.

Installing and configuration a database and learning how to use it is more complicated that just reading and writing data to a file.  What are some important reasons that makes a database a useful investment of time?

Databases are safer when it comes to insuring data integrity versus something like an online excel spreadsheet. A bank, a stock exchange index, an airline ticketing system, etc. A very large spreadsheet could conceivably hold all of the data in any of those applications but errors would be all but guaranteed. The cost incurred by those errors would far exceed the cost of setting up and maintaining a proper database system.

What do you want to learn in this course that you think will be useful in your future career?

I want to learn how data is stored behind the scenes. We see databases in web applications and even in embedded applications now. I imagine that in applications smart cameras, geological survey systems, tracking systems, etc. they use some type of embedded database. I would like to at least have an idea as to how that is implemented.