Databases

Question 1

What is a transitive dependency?

https://www.lifewire.com/transitive-dependency-1019760

Answer 1

A transitive dependency is when A -> B -> C, A has a transitive dependency of C through B. This can be true for databases as well, which is why the 3rd normal form looks to remove all transitive dependencies

Question 2

What is a partial dependency?

https://stackoverflow.com/questions/25747802/partial-dependency-databases

Answer 2

A partial dependency is when we have a primary key, maybe a consisting of a natural key used as a primary key, (A,B) -> C, but we can remove B and still get A -> C, meaning A,B is partially dependent on C.

Question 3

What is denormalization? pros and cons

Answer 3

denormalization is when we add duplication to a database to reduce join statements and improve query speeds.

pros: less joins, potentially faster queries, potentially fewer tables too
cons: writes are very difficult bc data is in multiple places, the sql update/insert statements are harder to write, we need to increase storage as well

Question 4

What is normalization? pros and cons

Answer 4

normalization is when we structure databases in a way that removes unnecessary dependencies such as partial/transitive, reduces data redundancy, and helps keep databases maintainable

pros: above
cons: can lead to many more tables than “required”

Question 5

what is an ACID database?

Answer 5

Atomic, if you make a commit and it fails, all parts of it fail together and we do not get partial queries
Consistent, if you read the same data with the same query, you get the same result
Isolation, guarantees all transactions occur in isolation, transaction1 cannot affect transaction2. This means if t1 is a write, t2 cannot complete until t1 is finished writing so we do not get a dirty read
Durable, once commits are made, they remain in the system even in the event of a system crash following the transaction. If we succeed in a transaction, it must actually succeed

Question 6

Dirty read, non-repeatable read, phantom read

https://stackoverflow.com/questions/11043712/what-is-the-difference-between-non-repeatable-read-and-phantom-read

Answer 6

dirty read, is when a transaction writes and a transaction reads, but the read complete before the write, so the read returns bad data
non-repeatable read, during a query, if a row is retrieved twice, the row returns different data each time
phantom read, with 2 identical queries, the rows are different between each query

Question 7

Isolation levels

Answer 7

https://www.youtube.com/watch?v=-gxyut1VLcs

Question 8

What are constraints in a database

Answer 8

??

Question 8

What are constraints in a database

Answer 8

??