Lecture 5

Question 1

What is a system catalog?

Answer 1

Contains metadata describing data the DMBS handles, stored in tables called catalog tables. Also called data dictonary.

Question 2

What does the system catalog store for system wide information?

Answer 2

Size of buffer pool and size of a page

Question 3

What does the system catalog store for a table?

Answer 3

Tables name and related files
Attributes and types of the table
tables index names
integrity constraints (key and foreign key)

Question 4

What does the system catalog store for an index?

Answer 4

Index name and type
Search key attribute

Question 5

What does the system catalog store for a view?

Answer 5

View name and definition

Question 6

What does the system catalog store for statistics about tables and index?

Answer 6

-Number of tuples in table R
-Number of pages for R
-Number of distinct key values
- Number of pages for index I (leafs)
- Number of non-leaf levels
- Index Range

Question 7

What are the advantages of storing catalogs as tables?

Answer 7

Catalog tables can be queries and we can use the techniques to manage tables for catalog tables

Question 8

Why is sorting important for DMBS?

Answer 8

1. returning answers in some order is easier
2. Bulk loading
3. Eliminating duplicate copies from a select is easier
4. Join operation is easier

Question 9

Why do we need external sorting?

Answer 9

We can’t use sort algorithms in databases, and most DB’s are too large to fit on main memory

Question 10

In Two-Way Merge Sort, what is the number of passes, and what is the overall cost?

Answer 10

Where N is the number of pages:
- Passes: log2(N) + 1
- Cost: 2N(log2(N) + 1)