CST363 Week 4

Briefly summarize what you have learned in the course so far. List at least 5 things.

CST363 is focused on database systems; so far, most of the course has dealt with MySQL. Some topics we have learned about include:

Relational Structure: Relational databases have certain constraints on how the data is structured. For example: a table must contain at least one column that serves as a primary key, column names must be unique, each row must be unique, column values must be derived from a known, predefined domain, columns may only contain one value for each row, and values in a single column must all be of the same type.

Normalization Forms: Rules that ensure a table does not contain redundant, or duplicated data. There are three main forms of normalization: 1st normal form, 2nd normal form, and 3rd normal form. In practice, normalization is an important step towards avoiding insertion, deletion, and modification anomalies.

General SQL Query Syntax: Statements that retrieve data from the database. A non-exhaustive list of key SQL-query sections include:

• SELECT <column names> FROM <table names>
• WHERE <predicates that filter what rows appear in the result>
• GROUP BY <column names to collapse and apply aggregate functions to>
• HAVING <predicates that apply after GROUP BY has been processed>

Aggregate Functions: Operations that work on multiple rows of a table. A non-exhaustive list of aggregate functions include:

• avg(<column name>): Returns the average value across all rows in the given column.
• count(<column name or *>): Returns the number of rows that contain a non-null value in the given column.
• max(<column name>): Returns the maximum value across all rows in the given column.
• min(<column name>): Returns the minimum value across all rows in the given column.
• sum(<column name>): Returns the sum of all row values in the given column.

Null Values Can Be Troublesome: Unlike some traditional programming languages, a null value does not cause a catastrophic error when encountered in arithmetic or Boolean logic. In SQL, arithmetic involving a null value always results in null. Boolean logic treats null as a third value; the following identities may be helpful:

• NOT:
• NOT null = null
• AND:
• true AND null = null
• false AND null = false
• OR:
• true OR null = true
• false OR null = null

List at least 3 questions you have about databases that have not been covered in the course so far.

• How would one recover from an erroneously issued query? For example, what if a table was accidently dropped; could it be recovered?
• Could a standard database be distributed over multiple computers, or do they usually occur as one monolithic instance? If/when they are distributed, how do the instances coordinate and remain synchronized?
• Can multiple users issue commands to a database in parallel? If so, are the operations guaranteed to not clash, and always resolve successfully?