introduction slide 229
play

Introduction - PowerPoint PPT Presentation

Introduction slide 229 Fonctional Dependencies slide 233 Relation decompositions slide 246


  1. ������� ���������� ������������ ��� � Introduction slide 229 � Fonctional Dependencies slide 233 ������������ � Relation decompositions slide 246 � Normal Forms slide 255 Normalisation of relational schemas ��� ��� ����� ������� �� ��� ������ � Bad design => data redonduncy ORDER table � Update mistakes (duplicate values) PRODUCT QUANTITY COLOR VENDOR ADDRESS � insertion mistakes (null values, Umbrella 110 red Labaleine Paris unconsistencies) Hat 50 green Lemelon Lyon � Deletion mistakes (loss of informations) Bag 65 black Toutcuir Lyon parasol 15 yellow Labaleine Paris � Introduce a concept of « good" schema Umbrella 5 red Labaleine Paris (without duplicates) ceinture 25 green Letour Nantes Bag 65 black Legrand Paris � Allow to compare two schemas ��� ��� Normal Forms 1

  2. ������������������� ��������!������������ � Study data properties � Functional dependencies � Multivalued dependencies Universal Relation FD � Product dependencies, ... name on pn � Normal Forms city wn expdate odate Normalisation � partial order on schemas lastname expqty oqty R1(.....) � decomposition / synthesis algorithms to get R2(....) 3rd normal form schemas ...... ��� ��� ���������� ���������$ %�!& � Property defined on the schema ���������� ������������ � Specific case of integrity constraint � defined on intension (so valid for all possible extensions) � Definition � B depends functionaly from A if, for a given value of A, a unique value of B is corresponding (for all extensions) � A and B are attributes sets � Notation A → B ��" ��# Normal Forms 2

  3. ������� �!��������� � PERSONS(pn, name, lastname, city) PN → NAME NAME → CITY ? PN → LASTNAME PN → WN ? � ORDERS(on, odate, wn, oqty, nb ) PN → CITY OQTY → EXPQTY ? � EXPEDITIONS(on, expdate, expqty) ON → ODATE ON → PN ON → WN ON → OQTY ON, EXPDATE → EXPQTY ��' ��( ���������������� !������ )��������� � Reflexivity � Union � X → Y � X → YZ � Y ⊂ X � X → Y and X → Z � Augmentation � Pseudo-transitivity � XZ → YZ � X → Y and YW → Z � XW → Z � X → Y � Transitivity � Decomposition � X → Z � X → Z � X → Y and Y → Z � X → Y and Z ⊂ Y ��� ��� Normal Forms 3

  4. *�������+��������� ���������� ������������ ����� � Transitive closure of F (a set of DF) is � Nodes = attributes denoted by F + � Edges = FD � F + = F U DF produced using axioms � For example on pn � ON → PN and PN → NAME so ON → NAME odate wn oqty name lastname city expdate � ON → NAME so ON, WN → NAME, WN � mainly transitivity and pseudo-transitivity expqty ��� ��� ���������$ ���������� ���������$ *�������+��������� ����� � a functional dependency X → A is elementary if � A is not included into X � It does not exist X’ included into X so that X’ → A on pn � Allow to simplify the process of transitive odate wn oqty name lastname city expdate closure (if not it is always possible to create new FDs using augmentation) expqty � Example: � PN → NAME � PN, WN → NAME not EFD ��� ��� Normal Forms 4

  5. ,���������+������ -�$��� ���������� � Definition � Definition � Minimal set of attributes allowing to determine Minimal subset of EFD allowing to produce all all other ones other ones � R(A 1 , A 2 , ..., A n ) a relation schema. F + the � Example (pn → name; pn → lastname; pn → city; set of FDs associated to R. X (subset of R) on → odate; on → pn; on → wn; on → oqty; is a key for R iff: on, expdate → expqty) � X → A 1 , A 2 , ..., A n � Theorem � It does not exist Y subset of X such as Y → A 1 , Any FD set has (at least) one minimal coverture A 2 , ..., A n ��" ��# -�$��� ����������%�&� ��������!������������ � Example � on, expdate is a key for the example schema � Remarks � A relation may have several keys � A relation has always one key (in the worst case the entire schema) ��' ��( Normal Forms 5

  6. ��������!������������ !������������ .������ ���� �� ����������� � Decomposition of a relation schema � the decomposition of a relation schema R(A 1 , � The decomposition of a relation schema R(A 1 , A 2 , ..., A n ) by a set of relation schemas R 1 , A 2 , ..., A n ) is its substitution by a set of relation schemas R 1 , R 2 , ..., R p such as: R 2 , ..., R p is without loss of information iff: � schema(R) = schema(R 1 ) ∪ schema(R 2 ) ∪ ... schema(R p ) � R = R 1 R 2 ... R p � Criterias of good decomposition � Decomposition without loss of informations � Decomposition preserving FD ��� �"� !������������ ������+��� �!� �������� �� ������������� � ORDERS(on, odate, wn, oqty, pn, name, � R(A 1 , A 2 , ..., A n ) and FD R (associated set of lastnale, city) FDs) � O(on, odate, wn, oqty, pn) and P(name, � decomposition of R in R 1 , R 2 , ..., R p (with lastnale, city): loss of info. FD R1 , ...FD Rn resp. set of FDs of R 1 , ..., R n ) � O(on, odate, wn, oqty, pn) and P(pn, name, preserves FDs iff: lastnale, city): no loss of info and FDs + = FD R1 + U ... U FD Rn + FD R preservation � O(on, pn) and P(on, odate, wn, oqty, name, lastname, city): no loss of info but loss of FDs (pn → name for example) �"� �"� Normal Forms 6

  7. )�������� �� �����$ ������������� !������������ ��������� R(X,Y,Z) and X → Y � ORDERS(on, odate, wn, oqty, pn, name, lastame, city) R(X,Y,Z)=R1(X,Y) R2(X,Z) pn → name; pn → lastname; pn → city; on → odate; on → pn; on → wn; on → oqty; � It is always possible to decompose a relation ORDERS(on, odate, wn, oqty, pn, name, lastame, city) schema using a FD � It is not possible to decompose a schema relation on → odate, wn, oqty without FD O1(on, odate, wn, oqty) O(on, pn, name, lastname, city) � FD decomposition preserves information pn → name, lastname, city C3(pn, name, lastname, city) C2(on, pn) �"� �"� !������������ ��������� ����� ������������� � Ensures that decomposition is without loss of information (uses the binary decomposition ORDERS(on, odate, wn, oqty, pn, name, lastame, city) principle) pn → name, lastname, city � Does not ensure FDs preservations � Decomposition is not unique (depends on the C2(on, odate, wn, oqty, pn) C1(pn, name, lastname, city) order of used FDs during decomposition) �"" �"# Normal Forms 7

  8. /����������� /����������� � First normal form � Second normal form � Third normal form � ... �"' �"( ����� ����������� 0����������������� � Definition � Definition � A relation is in second normal form: � A relation is in first normal form if all its attributes � It is in first normal form are atomics (definition of relational data model) � All non key attributes plenary depend from keys � An atomic attribute is not: � multivalued (list of values) � Example � structured (composed by sub-attributes) � O(on, expdate, expqty, pn) not in 2NF because on, expdate key and on → pn (pn does not plenary depend from on, expdate) �"� �#� Normal Forms 8

Recommend


More recommend