Fichas

Normalisation

SEZ McKelvey Inglés 23 fichas

Fichas del conjunto (23)

1What is the purpose of normalisation? Why do we need to avoid redundancy?Purpose: every relation represents a real world entity
Single valued columns
avoid redundancy
data is easy to update correctly and avoid anomalies
Redundancy:
-Minimise amount of space required
-Simplify maintenance of database
exception (foreign keys)
2What is one cause of redundancy?-Set valuedd attributes in the ER diagram can result in multiple rows in the corresponding table
3What is another cause of redundancy?Dependencies between attibutes can cause redundancy
eg: all addresses in the same town have the same zip code.
4What are: 1. Modification anomaly 2. Deletion anomaly 3. Insertion anomaly1. Failure to maintain all existing instances of a specific value
2. Losing other values as a side effect when you delete data - eg deleting Top Gun from video list at blockbuster. What genre is it now?
3. Inconsistency caused by adding a new item - eg collection of blockbuster videos. Top Gun classed as action. add new video, class Top Gun as mystery. Which is it?
5How to avoid modification, insertion, or deletion anomalies?-Decompose the table into two tables. This is called schema refinement
6What is a functional data dependency?-Describes the relationship among attributes in the same relation
-Let A and B be two sets of attributes. A is functionally dependent on B (B->A) if each value of B is associated with exactly one value of A.
7What are full, partial, and transitive functional dependencies?Full: A->B. B is functionally dependent on A, but not on any proper subset of A
Partial: A->B. B functionally dependent on A and on at least one proper subset of A
Transitive: A->B and B->C, then A->C.
8What is the closure of F?F^+. The set of all functional dependencies that are implied by the dependencies in F.
9Define Armstrong's Axioms.
10What are the further rules that can be derived from Armstrong's axioms?
11Compute the closure of this set of dependencies
12What is a candidate key? How can we choose a primary key from them?-Minimal set of attributes which functionally determine all attributes in a relation
-Among all candidate keys, we can choose any one of them as the primary key.
13How to bring a table in First Normal Form?- Table is in 1NF if it has no repeating groups (every cell has one value), no identical rows.
-Can "fill in the blanks" by entering appropriate data in the empty columns (but introduces a lot of redundancy).
-Or can place repeating data in separate relation, place copy of the original primary key in new relation which now becomes foreign key.
14Flatten this table:
15Put this table in 1NF by using two-tables. Label FK and PK of new table
16When is a table in 2NF?-If it is in 1NF and nthere are no partial functional dependencies (ie every non-key attribute is dependent on the whole primary key).
-Applies to relations with composite keys, if relation has a single primary key, the table is in 2NF if it is in 1NF
17How do you bring a table into 2NF?-Remove partially dependent attributes, place them in a new relation, along with a copy of their determinant.
18Is this in 2NF?
19Convert to 2NF
20Convert to 2NF
21When is a table in 3NF?-If it is in 2NF and there are no transitive functional dependencies (ie no non-key attribute is transitively dependent on the primary key)
22How do you bring a table into 3NF?-Remove transitively dependent attributes
-Place them in a new relation
-Take the attributes of their determinant as the PK in a new table
23Convert to 3NF