Exam 4

Question 1

Mapping of EER diagrams to relations will in ________ cases, result in a database that is normalized?

a) All
b) Some
c) No

Answer 1

All

Question 2

The 4 elements of normalising a database

Answer 2

1) No redundancy of facts
2) No clutterig of facts
3) Must preserve information
4) Must preserve functional dependencies (e.g. if I know email, I know name and birthdate)

Question 3

How might you end up with a table that is not a relation (or not F2… non-first normal form function)

Answer 3

Example: if you have tags against a record… it is multivalued so you’d technically have many tags against one row. So instead you replicate the rest of the info for each tag

Question 4

What’s the problem with redundancy in a relation?

Answer 4

Requires multiple places to do the update which can mean inconsistency.

Question 5

Insertions, based on functional requirements, cause what problems?

Answer 5

Can lead to NULL values.

For example, if you say anyone born in 1970 has 40k salary… and there is no person born in 1970, then you have to put null values after this statement.

Question 6

What is the problem with deletion anomalies?

Answer 6

If we delete a user who is the only example of our functional reequirement (e.g. the only user born in 1970) then you lose that requirement (i.e. you don’t have an example of someone with 1970 birth earning 40k)

Question 7

What’s the problem with update anomalies?

Answer 7

If you have multi-value and do an update in one row, you have to do work to do it everywhere

Question 8

One answer to anomalies is to decompose tables into multiple tables. What is the problem with this?

Answer 8

This can result in “information loss” where when you recombine the tables, they match too many places and you can no longer rely on any one tuple being correct (i.e. you’ve lost the real connection to the entity and thus, lost the info)

Question 9

What is another problem with decomposing tables?

Answer 9

Dependency loss. Where we can enforce functional dependencies (e.g. this birthdate requires this salaray) on two sub-tables that don’t share those columns.

Question 10

How do you fix all the potential anomalies with a relation?

Answer 10

You decompose them into the right combo of columns to align with the functional dependencies.

Question 11

But how do we get to a place with no functional dependencies?

Answer 11

Functional Dependencies?

Question 12

What is a functional dependency in discrete math terms?

Answer 12

Let X and Y be sets in R. Y is functionally dependent on X in R IFF for each x is an element of RX, there is a recisely one y taht is element of RY

Question 13

How do you ensure FULL functional dependency?

Answer 13

You basically make them “functions” in the true math sense. Each unique key or value of x only has one value y

Question 14

The 3 normal form types

Answer 14

All attributes must depend on the key (1NF), the whole key (2NF), and nothing but the key (3NF), so help me Codd

Question 15

Does something ever land at 3rd NF and not BCNF?

Answer 15

No. They do in theory but prof never saw in practice

Question 16

Transitivity Rule

Answer 16

X->Y and Y->Z implies X->Z

Question 17

Augmentation Rules

Answer 17

If X->Y you can augment with same Z on both sides ZX->ZY

Question 18

Reflexivity

Answer 18

If Y is part of X then X->Y

Question 19

Extent Transfer times?

Answer 19

This means you pick up extra data once you find it and just incur transfer time. which is just .5-1ms

Question 20

Extent Transfer times?

Answer 20

This means you pick up extra data once you find it and just incur transfer time. which is just .5-1ms

Question 21

Buffer Management Strategies to do extent transfer?

Answer 21

LRU = Least Recently Used means that when you need to overwrite buffer you.

Good for merged joins (because you are done with the old tables).

Bad with nested loops because you still need top of the loop.

Question 22

Buffer Management Strategies to do extent transfer?

Answer 22

LRU = Least Recently Used means that when you need to overwrite buffer you.

Good for merged joins (because you are done with the old tables).

Bad with nested loops because you still need top of the loop.

Question 23

Primary Index

Answer 23

Used to help expedite searching with sorted dta.

Basically is a parallel records that point to the key value at front of each block… is an index of what values are start of each block.

So then you can search the sorted index.

The sorted index picks up the values in order so it itself is sorted.

Also, you can use these for “point and range” search wherein you find the start of a block and grab or know that the item is in some range before the next item listed in the index. This speeds search time because you know the range to search in.

Question 24

Size of primary index and fanning

Answer 24

Block pointer is 4 bytes. You have 200,000 records. Each email is 50bytes. So each index record is 54 bytes. Given a block is 4000 bytes but only 80% utilised, you have 3200 bytes which can hold 60x 54 byte records. This 60 is called the “fanout”

Question 25

Sparse index block

Answer 25

Comes from primary index pointing to start of each block

Question 26

Dense index block

Answer 26

Comes from primary index pointing at each record

Question 27

Secondary Index

Answer 27

Builds an index on something other than start of a block and not sorted

Good for point queries only because the underlying data not sorted.

You can find the data quickly but can use the range to find the next value.

Question 28

multilevel index

Answer 28

If you do index for one level you can index each level.

Makes it all faster.

But makes it more vulnerable to overflows because if you add one record, it has to cascade back up the indexes.

Question 29

Hash index

Answer 29

Could look up lecture.

Basically it’s faster unless it gets too deep as then becomes heap again.