Lecture 19+20

Question 1

What is a multilevel index? What can it be used with?

Answer 1

First level is an ordered file, the second level is a primary index of the first and so on. This can be used on primary, clustering, or secondary indexes. Though there are problems with insertion and deletion.
They basically function like search trees.

Question 2

What are the requirements of a search tree of order p?

Answer 2

Each node has at most p-1 search values, and p pointers, each pointer points to a child or a NULL. All search values should be unique, there must always be exactly one less search value than pointers and all values in child trees should be less than the search value.

Question 3

Why are search trees not guaranteed to be balanced by default?

Answer 3

Tree balance is sensitive to insertion order. Also deletion can lead to nodes which are close to empty.

Question 4

What is a B-Tree?

Answer 4

A search tree, but the tree must always be balanced, and space wasted by deletion, if any, must never be excessive.
To achieve this:
Each node must be at least half full(except root and leaf nodes).
A node has 1 less search key field values than pointers.
All leaf nodes must be at the same level, and must point to NULL.

Question 5

What is a B+ tree?

Answer 5

B-tree variant, all data pointers are stored only in leaf nodes. Leaf node structure differs from internal node structure, and are typically linked together to allow for ordered access. More entries can be packed into an internal node for a B+ tree than a B tree due to no data pointers in internal nodes of B+

Question 6

What are the nodes of a B+ tree like?

Answer 6

Each internal node has one less search key field than pointers and pointers >= the order of the tree. All values in subtree should stay within the brackets of the search field values they came from.
Each leaf node has a key field and a pointer pairing, up until the final pointer, which is a pointer to the next leaf of the B+ tree. Each leaf node has at least order/2 values and all leaf nodes are at the same level.

Question 7

What is a B* tree?

Answer 7

A B+ tree, but each node must be 2/3 full.

Question 8

What are the disadvantages of indexes?

Answer 8

Add overhead when inserting, deleting, and possibly updating.
Indexes should be added after the data in the event of a mass load.

Question 9

How do we create and drop indexes in SQL?

Answer 9

CREATE INDEX ON (column);

DROP INDEX

Question 10

What is security with regards to databases?

Answer 10

Protection of the database against unauthorized access, either intentional or accidental.

Question 11

What are the main control measures?

Answer 11

Access control: user account and password.
Inference Control: statistical database security.
Flow Control: prevent information from reaching unauthorized users.
Data encryption: Protect sensitive data.

Question 12

Who is responsible for access control? How is it done?

Answer 12

The database administrator. They have an account known as the superuser, it allows for account creation for overall access, privilege granting and revocation(discretionary), and security level assignment(mandatory).

Question 13

What are the two approaches to DB security?

Answer 13

Discretionary: this is done in most commercial DBMSs. A user may have different access rights to different objects, different users may have different rights on the same object. This is very flexible.
Mandatory: Used in some DBMS for government/military type operations.
Each object tagged with classification level, each user has a level, the data object can only be accessed by users with appropriate clearance. This is very rigid.

Question 14

How does mandatory access control work?

Answer 14

There are 4 levels: top secret > secret > confidential > none. Users can not read higher than their rank and can not write lower than their rank. This prevents lowering the security of objects.

Question 15

How does discretionary access control work?

Answer 15

granting and revoking of privileges, providing selective access to relations based on specific users. Two levels are account level(different rights on same object) and relation level(different access rights to different objects).
Each table has an owner, they can grant privileges to their tables, types of privileges are SELECT, MODIFY or REFERENCES (can be referenced in constraints). Or views.
GRANT gives privileges to tables, REVOKE removes them.

Question 16

What are the main object privileges?

Answer 16

SELECT, INSERT, UPDATE, DELETE, ALTER, EXECUTE, INDEX, REFERENCE.

Question 17

What is a role?

Answer 17

A group of related privleges, they can be created with CREATE ROLE ; and then granting it privileges, the role can then be granted to users.

Question 18

What is the problem with WITH ADMIN OPTION?

Answer 18

WITH ADMIN OPTION allows whoever was given it the ability to grant privileges to other users, allowing privileges to propagate without owner knowledge.

Question 19

Why is statistical database security important?

Answer 19

In cases where only statistical information is retrievable it may be important to prevent retrieval of individual data, which may be possible from a sequence of statistical queries. Possible techniques inclue prohibiting statistical queries when number of tuples specified is too low or by prohibiting queries which repeatedly refer to same tuples.