Lecture 5 - B

Question 1

Clustered index

Answer 1

• The unique index per table that uses a primary key to organize the data that is within the table.
• Ensures that the primary key is stored in increasing order, which is also the order the table holds in memory.
• Do not have to be mentioned, uses the primary key to be stored in ascending order.

Question 2

Non-clustered indexes

Answer 2

• Sorted references for a specific field, from the main table, that hold pointers back to the original entries of the table.

Question 3

Value Pointers

Answer 3

Non-clustered indexes store value pointers, not values themselves.

• The pointers with the key used can be used to immediately locate a record in the indexed table.

Question 4

B- Tree

Answer 4

• Each node contains key values , value pointers and pointers to child nodes.

Question 5

B+ Tree

Answer 5

• Same as B- but leaf nodes are linked.
• Is a self-balancing tree data structure that keeps data sorted and allows searches, sequential access, insertion, deletion in O(log n)

Question 6

Advantages of B- trees

Answer 6

• Can find values closer to the root fairly quickly.
• In B+ we would have to look all the way down to the leaf nodes.

Question 7

B+ tree index 3 properties:

Answer 7

What differs B+ and B- is the way data pointers are stored.

1. Perfectly balanced
2. Every inner node other than the root is at least half full.
3. Every inner node with k keys has k+1 non-null children.

Question 8

Hash Table indexes, 2 properties:

Answer 8

• Very fast (O(1)) when querying for equality, since it uses the whole key as the lookup value.
• Can’t help us find the range, use > < or ORDER BY instead.

Question 9

Use of an indexes

Answer 9

• Foreign keys, to speed up joins
• Often queries fields, dates are often good candidates.
• Columns that have many different values.

Question 10

Indexes drawbacks:

Answer 10

• Indexes need to be mantained
• Indexes need to be stored

Question 11

When changing large amounts of data:

Answer 11

1. DROP the index
2. Run many statements.
3. Recreate the index.

Question 12

EXPLAIN

Answer 12

The EXPLAIN statement provides information about how DBMS executes statements.

Question 13

VIEWS

Answer 13

Views give you the ability to store query results as if they were tables.

• By default, a view does not use any shortage, when queried it always presents the latest data.

Question 14

Why use views?

Answer 14

1. Security, compliance and privacy.
2. Data aggregation.
3. Hiding complexity.
4. Joining partitioned data