Week 4 Flashcards
What does normalisation do?
<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>
Describe normalisation
A technique used to analyse the relation based on its primary key and the functional dependencies between the attributes of that relation
What does it mean for a decomposition to be lossless?
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
What does it mean for a domain to be atomic?
If all the elements are considered to be indivisible units
What is the definition of a schema in first normal form
If the domains of all the attributes are atomic
What are the negatives of having non atomic values in your database?
Complicate storage and search by encouraging redundant storage of data and pattern matching
What are the conditions for a relation to be in second normal form?
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>
What are the conditions for a relation to be in third normal form?
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>
What are the conditions for a relation to be in BCNF?
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)
What does BCNF stand for?
Boyce Codd Normal Form
What does the SQL DDL allow specification about?
<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>
What does numeric(p,d) store?
A fixed point number with p digits, and d digits to the right of the decimal point
How would you force the elimination of duplicates in select?
Using the keyword distinct
What does % match?
Any substring
What does _ match?
Any character