Relationnel Model Bogdan Shishedjiev Relational Model 1 Relational

Relationnel Model Bogdan Shishedjiev Relational Model 1

Relational Database Bogdan Shishedjiev Relational Model 2

General Concepts • Relation, Attribute, Domain • Cartesian product of domains D 1, D 2, …, Dn gives nuples or tuples {d 1 , d 2 , …, 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 3

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

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(A 1, A 2, …, An) X A 1, A 2, …, An and there is no Y X Y A 1, A 2, …, An. X is a candidate key (superkey) • Primary key Bogdan Shishedjiev Relational Model 5

Functional Dependency • Axioms (Armstrong) – (R 1) Reflexivity : if Y X then X Y – (R 2) augmentation : if X Y and W is any set of attributes then XW YW – (R 3) transitivity : if X Y and Y Z then X Z – (R 4) pseudo-transitivity : if X Y and YW Z then XW Z – (R 5) union : if X Y and X Z then X YZ – (R 6) decomposition : if X YZ then X Y and X Z Bogdan Shishedjiev Relational Model 6

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

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: D 1, SUPPLIER: D 2 } Y={PART D 1, PROJECT: D 3 } Z={PART D 1, SUPPLIER D 2, PROJECT D 3 } R: PART SUPPLIER nut S: - D 1 = { nut, bolt, screw} - D 2 = { Peter, Paul, Mary } - D 3 = { a, b, c } PART PROJECT Peter nut a nut Paul nut b bolt Mary bolt a Bogdan Shishedjiev Relational Model 8

Relational Algebra Projection (1) Z = Y (2) YR ={L A(Y); $L' A(X), (L'/Y = L) и (L' R) } R: PART SUPPLIER PROJECT nut Paul a nut Paul b bolt Peter a Let Y={ PARTD 1, SUPPLIERD 2 } YR : PART SUPPLIER nut Paul bolt Peter Bogdan Shishedjiev Relational Model 9

Relational Algebra • Selection (1) Y =X (2) ER = { L A(X) / (L R) and (E(L) = true) } R: CLASS NAME CITY BIRTH. SPORT 6 peter london 11. 10. 79 judo 6 peter london 11. 10. 79 fencing 6 jacob london 05. 03. 78 swimming 6 paul london 06. 07. 79 football 5 george Bristol 01. 04. 77 football E= E R : (CITY=‘london') (BIRTH≤ '31. 08. 79') ((SPORT='judo') (SPORT='football')) CLASS NAME CITY BIRTH SPORT 6 paul london 06. 07. 79 football Bogdan Shishedjiev Relational Model 10

Relational Algebra • Product or Inner Join (1) Z=X Y (2) R*S = { L A(Z) that (L/X R) and (L/Y S) } R: PART SUPPLIER nut PART PROJECT Peter nut a nut Paul nut b bolt Mary bolt a R*S : S: PART SUPPLIER PROJECT nut Peter a nut Peter b nut Paul a nut Paul b bolt Mary a Bogdan Shishedjiev Relational Model 11

Relational Algebra • Union, Intersection, Difference The schema is identical R: R S : S-R : PART SUPPLIER nut PART SUPPLIER peter nut paul bolt peter bolt mary PART SUPPLIER nut peter nut S: PART SUPPLIER paul nut peter bolt mary bolt peter PART SUPPLIER bolt peter R S : Bogdan Shishedjiev Relational Model 12

Relational Algebra • Cartesian Product - X Y= R: (1) Z=X Y (2) R S = R*S PART SUPPLIER nut PI SUPPLIER peter nut paul bolt mary T S : S: T: PI SUPPLIER PART PROJECT nut peter nut a nut paul nut a PART PROJECT bolt mary nut a nut peter nut b nut paul nut b bolt a bolt mary nut b nut peter bolt a nut paul bolt a bolt mary bolt a Bogdan Shishedjiev Relational Model 13

Relational Algebra • Equi-join S: PART PROJECT nut a nut b bolt a T(⋈PART=PI) S : T: PI SUPPLIER nut peter nut paul bolt mary PI SUPPLIER PART PROJECT nut peter nut a nut paul nut a nut peter nut b nut paul nut b bolt mary bolt a Bogdan Shishedjiev Relational Model 14

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 15

Decomposition • Decomposition – Definition R = R 1*R 2*R 3*…*Rn – Lossless decomposition – Preservation of dependencies Bogdan Shishedjiev Relational Model 16

Decomposition R R 1 N° name age address speciality 1 A 30 x 1 S 1 2 B 30 x 2 S 2 3 C 30 x 3 S 2 4 D 40 x 4 S 1 âge speciality 30 N name addr S 1 1 A 30 S 2 2 40 S 1 R'=R 1*R 2*R 3 R 2 R 3 N age x 1 1 30 B x 2 2 30 3 C x 3 3 30 4 D x 4 4 40 N° name address age speciality 1 A x 1 30 S 1 1 A x 1 30 S 2 2 B x 2 30 S 1 2 B x 2 30 S 2 3 C x 3 30 S 1 3 C x 3 30 S 2 D x 4 40 S 1 4 Bogdan Shishedjiev Relational Model 17

First Normal Form • Definition – A relation is in 1 NM if and only if all underlying domains contain scalar values only and it has no repetitive groups No 500 501 510 EMPLOYEE NAME CHILD FIRSTNAME ANDRE 10 JEAN 11 PIERRE 12 PAUL 13 JACQUES 14 DUPONT DURAND LEFEBVRE No 500 501 510 NAME DUPONT DURAND LEFEBVRE CHILDREN Bogdan Shishedjiev Relational Model AGE No 500 501 510 First. Name André Jean Pierre Paul Jacques age 10 11 12 13 14 18

First Normal Form MOVIE(No, Name, Director, Actor 1, Actor 2, Actor 3) MOVIE No Name 1 2 Director Actor 1 Actor 2 The Silence of the Jonathan Lambs Demme Jodie Foster Anthony Hopkins The Sixth Sense Bruce Willis M. Night Shyamalan No Name Director 1 The Silence of the Lambs The Sixth Sense 2 CAST Actor 3 No Actor Jonathan Demme 1 Jodie Foster M. Night Shyamalan 1 Anthony Hopkins 2 Bruce Willis Bogdan Shishedjiev Relational Model 19

Second Normal Form • Definition – A relation is in 2 NM if and only if it is in 1 NF 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 in 2 NF FD : part, warehouse -> quant ; warehouse -> address Decomposition STOCK (part, warehouse, quant) LOCAL(warehouse, address) Bogdan Shishedjiev Relational Model 20

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