In a relation, R, FD X->A holds if any two tuples with same attribute set as X have attribute A also. X->A and X->B can be combined to get X->AB.
key is uniquely identify all the other attributes in R and it's minimal.
ER key doesn't have to be minimal but for Relation key, it has to be.
ER key is property of entities while Relation key is property of tuples.
FD's come from keyness of ER, many one relationship or from common sense.
If X is key for E1 and Y is key for E2, X->Y holds when E1 and E2 have many one relationship from E1 to E2. Drinker's name gives you favorite Beer.
Update anomaly, and deletion anomaly can be avoided with normalization.
1NF = all domains are atomic values. A|1,2,3 becomes A|1, A|2, A|3
2NF = key->non-key. but partial-key !-> non-key.
AB is a key. AB -> C is ok. D->C is ok. But, A -> C is not ok.
3NF = key->non-key. non-key!->non-key.
AB->C is ok. D->C is not ok. A->C is not ok.
BCNF = key->non-key. key->partial-key. a key member attribute cannot depend on another key member attribute. superkey -> any-attribute.
AB->C is ok. AB->D is ok. But, A->B is not ok.
bottom-up: project relations. top-down: start with good ER model.
MVD = AB->C means (AB)1 C1, (AB)2 C2, (AB)1 C2, (AB)2 C1 are all in R.
4NF decomposition: X->->Y becomes XY and X union (R-Y)
select, project, cartesian product, union, set difference, set intersection, theta join, natural join, division.
Union, intersection, difference works on relations with same relation schemae.
selection: picks rows.
projection: picks columns.
products and joins: new relation results in.
renaming a relation or attributes is also possible.
SIGMAcondition(Relation) returns array of rows in the parameter Relation.
PIlistofattributes(Relation) returns a relation with those attributes only. eliminates duplicates. order of attributes preserved.
RelationXRelation returns a relation.
Theta join is same as SIGMAcondition(R1XR2).
Natural join considers same attribute name the same.
Unary(select project rename) > products joins > intersection > union difference.