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

Download Presentation
Download Policy: The content available on the website is offered to you 'AS IS' for your personal information and use only. It cannot be commercialized, licensed, or distributed on other websites without prior consent from the author. To download a presentation, simply click this link. If you encounter any difficulties during the download process, it's possible that the publisher has removed the file from their server.

Recommend


More recommend