Week 4

Question 1

What does normalisation do?

Answer 1

<ul>
<li>Identify the minimal number of attributes necessary</li>
<li>Identify the optimal grouping of attributes in relations</li>
<li>Ensure the minimal redundancy</li>
</ul>

Question 2

Describe normalisation

Answer 2

A technique used to analyse the relation based on its primary key and the functional dependencies between the attributes of that relation

Question 3

What does it mean for a decomposition to be lossless?

Answer 3

One of the two sets is functionally dependent on the attributes common to both

R1 union R2 is a superkey of either R1 or R2

Question 4

What does it mean for a domain to be atomic?

Answer 4

If all the elements are considered to be indivisible units

Question 5

What is the definition of a schema in first normal form

Answer 5

If the domains of all the attributes are atomic

Question 6

What are the negatives of having non atomic values in your database?

Answer 6

Complicate storage and search by encouraging redundant storage of data and pattern matching

Question 7

What are the conditions for a relation to be in second normal form?

Answer 7

If it is in 1NF and each attribute is one of

<ol>
<li>Part of a candidate key</li>
<li>Fully functionally dependent on a candidate key</li>
</ol>

Question 8

What are the conditions for a relation to be in third normal form?

Answer 8

For each of A->B in R, at least one of the following holds

<ol>
<li>A->B is trivial</li>
<li>A is a superkey</li>
<li>Each attribute in B - A is in a candidate key</li>
</ol>

Question 9

What are the conditions for a relation to be in BCNF?

Answer 9

For every A->B in R at least one of the following holds
<ol>
<li>A->B is trivial</li>
<li>A is a superkey</li>
</ol>

(if A determines the values of any other attributes, A must be a superkey)

Question 10

What does BCNF stand for?

Answer 10

Boyce Codd Normal Form

Question 11

What does the SQL DDL allow specification about?

Answer 11

<ul>
<li>The schema for each relation</li>
<li>The domain of values associated with each attribute</li>
<li>Integrity constraints</li>
<li>Set of indices</li>
<li>Security and authorisation information</li>
<li>Physical storage structure</li>
</ul>

Question 12

What does numeric(p,d) store?

Answer 12

A fixed point number with p digits, and d digits to the right of the decimal point

Question 13

How would you force the elimination of duplicates in select?

Answer 13

Using the keyword distinct

Question 14

What does % match?

Answer 14

Any substring

Question 15

What does _ match?

Answer 15

Any character

Question 16

What would _ _ _ match?

Answer 16

Match any string of exactly three characters

Question 17

What would _ _ _ % match?

Answer 17

Match any string of at least three characters

Question 18

What would Intro% match?

Answer 18

Match any string beginning with Intro

Question 19

What would %Comp match?

Answer 19

Match any string of at least three characters

Question 20

What is the result of any arithmetic expression involving null?

Answer 20

null