1 / 21

Relationnel Model

Relationnel Model. Relational Database. General Concepts. Relation, Attribute, Domain Cartesian product of domains D 1 ,D 2 ,…,D n gives n-uples or tuples {d 1 ,d 2 ,…,d n : d i  D i } Relation is a subset of the Cartesian product of a list of domains

marlon
Download Presentation

Relationnel Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. RelationnelModel Bogdan Shishedjiev Relational Model

  2. Relational Database Bogdan Shishedjiev Relational Model

  3. General Concepts • Relation, Attribute, Domain • Cartesian product of domains D1,D2,…,Dn gives n-uples or tuples {d1 ,d2 ,…,dn: di Di} • Relation is a subset of the Cartesian product of a list of domains • The attribute is the column of the relation and it has a name. • Relational Schema (intention), extension (relation) • Relational database Bogdan Shishedjiev Relational Model

  4. Example D1 = {Car, Motor, Wheel, Cylinder Head, Spring, Chassis, Piston, Connecting Rod, Tyre, Wheel Rim}, D2 ={1..8} R(Unit:D1,Part:D1,Quantity:D2) Bogdan Shishedjiev Relational Model

  5. Functional Dependency • Let R(X, Y, Z). There is functional dependency between X and Y or XY if and only if for any values of X,Y and Z – (a,b,c) and (a’,b’,c’) R(a,b,c) and R(a,b’,c’) b=b’ • Key If in R(A1,A2,…,An) X  A1,A2,…,An and there is no YX Y  A1,A2,…,An . X is a candidate key (superkey) • Primary key Bogdan Shishedjiev Relational Model

  6. Functional Dependency • Axioms (Armstrong) • (R1) Reflexivity : if Y  X then X  Y • (R2) augmentation : if X  Y and W is any set of attributes then XW  YW • (R3) transitivity : if X  Y and Y  Z then X  Z • (R4) pseudo-transitivity : if X  Y and YW  Z then XW  Z • (R5) union : if X  Y and X  Z then X  YZ • (R6) decomposition : if X  YZ then X  Y and X  Z Bogdan Shishedjiev Relational Model

  7. Refering relation Refered relation Nonkey attrubute key Integrity Constraints • Entity Integrity • Domain Integrity • Referential Integrity – Foreign key Bogdan Shishedjiev Relational Model

  8. Relational Algebra Notation X – set of attrubutes A(X) – The set of all possible tuples of X L/Y – The restriction of the realization L over the subset Y X X={ PART:D1, SUPPLIER:D2 } Y={PARTD1, PROJECT:D3 } Z={PARTD1, SUPPLIERD2, PROJECTD3 } - D1 = { nut, bolt, screw} - D2 = { Peter, Paul, Mary } - D3 = { a, b, c } Bogdan Shishedjiev Relational Model

  9. Relational Algebra Projection (1) Z = Y (2) YR={LA(Y); $L'A(X), (L'/Y = L) и (L'  R) } LetY={ PARTD1, SUPPLIERD2 } Bogdan Shishedjiev Relational Model

  10. Relational Algebra • Selection (1) Y =X (2) ER = { L  A(X) / (L  R) and (E(L) = true) } E = (CITY=‘london')  (BIRTH≤ '31.08.79')  ((SPORT='judo')  (SPORT='football')) Bogdan Shishedjiev Relational Model

  11. Relational Algebra • Product or Inner Join (1) Z=XY (2) R*S = { LA(Z) that (L/X  R) and (L/Y  S) } Bogdan Shishedjiev Relational Model

  12. Relational Algebra • Union, Intersection, Difference The schema is identical Bogdan Shishedjiev Relational Model

  13. Relational Algebra • Cartesian Product - XY= (1) Z=XY (2) RS = R*S Bogdan Shishedjiev Relational Model

  14. Relational Algebra • Equi-join Bogdan Shishedjiev Relational Model

  15. Normalization • Good schema • Universal Relation FIRM ( N_employee, N_department, chef, contract_type) HOSPITAL(N_patient, Name, Ward, Doctor) • Update anomaly • Deletion anomaly • Insertion anomaly • Redundancy Bogdan Shishedjiev Relational Model

  16. Decomposition • Decomposition • DefinitionR = R1*R2*R3*…*Rn • Lossless decomposition • Preservation of dependencies Bogdan Shishedjiev Relational Model

  17. Decomposition Bogdan Shishedjiev Relational Model

  18. First Normal Form • Definition – A relation is in 1NM if and only if all underlying domains contain scalar values only and it has no repetitive groups Bogdan Shishedjiev Relational Model

  19. First Normal Form MOVIE(No, Name, Director, Actor1, Actor2, Actor3) Bogdan Shishedjiev Relational Model

  20. Second Normal Form • Definition – A relation is in 2NM if and only if it is in 1NF and no non-prime attribute in the table is functionally dependent on a proper subset of a candidate key (total dependency) • Example • STOCK (part, warehouse, quant, address) is not in2NF • FD : part, warehouse-> quant ; warehouse -> address • Decomposition • STOCK (part, warehouse, quant) • LOCAL(warehouse, address) Bogdan Shishedjiev Relational Model

  21. Third Normal Form • Definition – A relation is in 3NM if and only if it is in 2NF and every nonkey attribute is nontransitively dependent on the primary key • Example PERSONAL(employee, name, FirstName, Service, Address)DF : employee -> name, FirstName, Service, Address; Service -> Address Decomposition PERSONAL(employee, name, FirstName, Service) LOCAL(Service, Address) Bogdan Shishedjiev Relational Model

More Related