Week 4 Flashcards

1
Q

What does normalisation do?

A

<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>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Describe normalisation

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What does it mean for a decomposition to be lossless?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What does it mean for a domain to be atomic?

A

If all the elements are considered to be indivisible units

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is the definition of a schema in first normal form

A

If the domains of all the attributes are atomic

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A

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>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

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>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A
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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What does BCNF stand for?

A

Boyce Codd Normal Form

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What does the SQL DDL allow specification about?

A
<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>
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What does numeric(p,d) store?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How would you force the elimination of duplicates in select?

A

Using the keyword distinct

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What does % match?

A

Any substring

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What does _ match?

A

Any character

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What would _ _ _ match?

A

Match any string of exactly three characters

17
Q

What would _ _ _ % match?

A

Match any string of at least three characters

18
Q

What would Intro% match?

A

Match any string beginning with Intro

19
Q

What would %Comp match?

A

Match any string of at least three characters

20
Q

What is the result of any arithmetic expression involving null?

A

null