# DBMS Final Exam Study Guide
## Armstrong's Axioms & Functional Dependency
1. Reflexivity:
- if `y` is a proper subset of `x` then `x -> y`
3. Augmentation
- if `x -> y` then `ZX -> ZY` (or `ZX -> Y`)
5. Transivity
- If `x -> y` and `y -> z`, then `x -> z`

## Normal Forms
1. **First Normal form**
* All rows must be unique
* no duplicates
* Each cells must only contain a single value
* ie not a list
* Each value should be non-divisible
* Can't be split down further
2. **Second Normal Form**
* No partial dependencies
* Must first be in 1NF
* All non prime attributes must be fully functionally dependent on the candidate key
3. **Third Normal Form**
* No transitive dependencies
* Must already be in first & second NF
* All fields must be determined by the primary/composite key, not by other keysrst
4. **Boyce-Codd Normal Form**
* It should already be in 3NF.
* For a functional dependency `P -> Q`, `P` should be a super key.

| 3NF | BCNF |
| ------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------- |
| In 3NF there should be no transitive dependency that is no non prime attribute should be transitively dependent on the candidate key. | In BCNF for any relation A->B, A should be a super key of relation. |
| It is less stronger than BCNF. | It is comparatively stronger than 3NF. |
| In 3NF the functional dependencies are already in 1NF and 2NF. | In BCNF the functional dependencies are already in 1NF, 2NF and 3NF. |
| The redundancy is high in 3NF. | The redundancy is comparatively low in BCNF. |
| In 3NF there is preservation of all functional dependencies. | In BCNF there may or may not be preservation of all functional dependencies. |
| It is comparatively easier to achieve. | It is difficult to achieve. |
| Lossless decomposition can be achieved by 3NF. | Lossless decomposition is hard to achieve in BCNF. |
| | |
## Indexing
1. **B-Tree**
* log(n)
* Equality checks
* Comparisons
* used for highly compared keys and attributes like names
2. **Hashing**
* O(1)
* Equality checks
* I am SPEEEEEEEEEEEEEEDDD
* Used for Id's and other non-comparable keys and valued attributes

## SQL
* ez pz except when it's not
* UNION
* joins all "friends" together as long as they have the same relation scheme
* symmetrical friendship

## ER / EER Diagrams
* Entities
* Relationships
* Weak Entities
* Weak relationships
* Cardinality
* Primary Keys
* Foreign keys
* Ternary Relationships


## Concurrency and Transactions
1. Concurrency
* Consistency
* Isolated
2. Recovery
* Atomic
* Durable
### Transactions
* There are no read locks if someone commits a write while I'm in a transaction...my transaction will read their commited updates
* A transaction failure could occur due to bad input or some other violation of consistency. It could also be due to a timeout or deadlock in the DBMS.
#### SQL Isolation Levels
3. **Serializable (DEFAULT!!)**
2) Repeatable Read
* Write/read
* Cursor stability

1. Read Committed
0) Read Uncommitted

#### ACID
1. **A**tomic
- All or nothing
- The commit is the only thing that makes changes
- guarantees that either all of the transaction succeeds or none of it does. You don’t get part of it succeeding and part of it not. If one part of the transaction fails, the whole transaction fails.
2. **C**onsistency
- goes from consistent state to consistent state
- All data will be valid according to all defined rules, including any constraints, cascades, and triggers that have been applied on the database.
4. **I**solated
- Multiple concurrent users are isolated to their own sessions
- No transaction will be affected by any other transaction. So a transaction cannot read data from any other transaction that has not yet completed.
6. **D**urable
- Commits persist
- Durability means that, once a transaction is committed, it will remain in the system – even if there’s a system crash immediately following the transaction. Any changes from the transaction must be stored permanently. If the system tells the user that the transaction has succeeded, the transaction must have, in fact, succeeded.

#### Note
NoSQL databases are a bit different. NoSQL databases are often designed to ensure high availability across a cluster, which usually means that consistency and/or durability is sacrificed to some degree. However, most NoSQL DBMSs can provide atomicity to some degree.

Most NoSQL DBMS work on a eventually consistent basis, meaning that, data may be out of sync for a time, but it will eventually be in sync.