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?