Zach's Blog

Some of the things I have learned about over the span of this course are foreign keys, column and table constraints, GROUP BY and HAVING clauses, view tables, and normalization. Foreign keys, table constraint are powerful tools when setting up a table schema that allows us to define links between table columns and establish rules for what values are allowed in our database. GROUP BY and HAVING clauses allow us to group selected rows and columns together and filter the retrieved information. View tables are associated with SELECT statements in which they restructure the columns and data types of tables without changing the tables themselves. Lastly, with normalization i have learned about the first, second, and third normal forms for organizing database data. Some questions I still have are the following: How can we dynamically generate temporary tables upon an insert statement and associate it with a value in a table row? Is there other forms of table management automation besides fore...

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 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 words what 3rd normal form is and why it is important.      Third normal form is when a candidate key in a table depends on all other columns in the table to be unique. Third normal form is important because it helps reduce the duplication of data in a table. 2. What is an SQL view. How is it similar to a table? In what ways is it different?      An SQL view is a table that's associated with a SELECT query. Views are similar to tables in that they can be queried in SELECT statements using FROM. They are different because a view doesn't store table data, it is a saved query statement that references data from other tables....

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 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.     A query that might require joining without using equality between primary and foreign key is when there is no primary and foreign key to compare. For example, a table "Employees" exists with columns primary key ID, name, manager, and  salary, and a table "Department" exists with columns primary key code,  dept_name, size, and manager. With this a question is posed: Can you list all employees, their department name, and salary? An SQL query could be the following: select name, dept_name, salary from Employees inner join Department on Employee.manager = Depart...

1. Relational database tables and spreadsheets look similar with both having rows and columns. What are some important differences between the two?      One important difference is that databases make use of schemas to determine how data is stored in a database table. With such a schema, relationships are able to be formed between data, keys are able to be set on data enabling easy retrieval, and much more. Spreadsheets do not operate in this fashion. Spreadsheets offer the ability to filter and analyze data while not enforcing a schema. 2. 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?      A database is a useful investment of time as it allows billions of data entries to be stored and retrieved efficiently. Additionally, a database supports usage by thousands of users at once, ke...