Relational Database 1Data model 2Relational Database 3 Relational algebra 4 Algebraic operations 4.1 Standard set-theoretic operations 4.2 Cartesian product 4.3Special operations of relational algebra 4.3.1Projection 4.3.2Selection 4.3.3Join 4.3.4Division 5 Integrity Constraints Щукин B.А. 2015г1

Relational database (EXAMPLE). Щукин B.А. 2017г 2 Groups GrSpecDept B13-401Mathematics67 B13-502Physics31 B14-402Nuclear Reactors40 B14-501Management33 B14-505Software22 B14-701Systems Theory28 B14-901Computer Science82 B15-903Computer Science71 Students NzFioSAgeGr st401.1Krymov V.A.M17B st401.2Khromov V.A.M18B st401.3Dronov V.S.M19B st401.4Krutov M.A.M19B st401.5Krymova A.A.W16B st401.6Rizina G.N.W17B st402.1Stoyanov A.T.M17B st501.1Serw S.Y.W17B st501.2Kotova V.I.W17B st502.1Dikiy M.T.M16B st502.2Ozerova T.I.W17B st505.1Ivanova V.I.W17B st505.2Levina A.U.W18B st505.3Polyakova S.T.W19B st505.4Polina S.T.W19B st505.5Silina S.T.W19B st505.6Smolina S.T.W19B st701.1Volodin K.P.M19B st701.2Koldin O.P.M1818B ExamRep NzIdKDateCr st501.1K_ st501.2K_ st502.1K_ st502.1K_ st502.1K_ st502.2K_ st502.2K_ st502.2K_ st505.1K_ st505.1K_ st505.2K_ st505.3K_ st505.3K_ st701.1K_ st701.1K_ st701.1K_ st701.2K_ st701.2K_ st701.2K_ ExamPl IdKGrDate K_2B K_3B K_4B K_1B K_1B K_2B K_2B K_3B K_4B

Data model Data model defines : 1. Data structure 2. Requests 3. Integrity Constraints Relation Data model : 1. Relations (Tables) 2. Relational algebra, Tuple relational calculus 3. Algebraic identities or closed formulas of the calculus The data model is a modeling tool, not the result of modeling Щукин B.А. 2017г3

Relations The relation R is a subset of the Cartesian product of domains : R D 1 *D 2 *…*D n D i = {d i1, d i2, …, d ik } Relation Scheme : Relation Name Set of attributes Attribute : Name, Domain (Data Type) Example : Students(Nz, Fio, S, Age, Gr) Щукин B.А. 2017г4

Relational database The relational database is defined as a set of relations :R 1, R 2,…, R N. The schema of a relational database is defined as a set of relation schemes. In practice, a relational database is defined as a set of tables, schema – set of table structures : Students( Nz, Fio, S, Age, Gr ) Groups( Gr, Spec, Dept ) ExamRep( Nz, IdK, Date, Cr ) ExamPl( IdK, Gr, Date ) Щукин B.А. 2017г5

Relational algebra In the general case, the algebra is defined by two: where N is the carrier, S is the signature. As a carrier in a relational algebra there is a set of relations, as a signature a set of operations consisting of 1. Standard set-theoretic operations; 2. Cartesian product operation; 3. Special operations. Щукин B.А. 201г6

Standard set-theoretic operations 1. set union R 1 R 2, 2. set intersect ion R 1 R 2, 3. set difference R 1 \ R 2 but adds additional constraints to these operations: the two relations involved must be union-compatiblethat is, the two relations must have the same set of attributes. Because set intersection is defined in terms of set union and set difference, the two relations involved in set intersection must also be union-compatible. Щукин B.А. 2017г 7

Cartesian product R w = R 1 * R 2 { AtrR w } = { AtrR 1 } + { AtrR 2 } R 1 = Students( Nz, Fio, S, Age, Gr ) R 2 = Groups( Gr, Spec, Dept ) { AtrR w } = {Nz, Fio, S, Age, Students.Gr, Groups.Gr, Spec, Dept} st401.1 Krymov V.A. M 17 B B Mathematics 67 st401.1 Krymov V.A. M 17 B B Phisics 31 st401.1 Krymov V.A. M 17 B B Nuclear Reactors 40 etc Щукин B.А. 2017г 8

Special operations of relational algebra Щукин B.А. 2017г Projection R w = R 1 [{AtrProj}] {AtrProj} { AtrR 1 } { AtrR w } = {AtrProj } Example : R w ( Gr, S ) = Students[ Gr, S ] B M B W B M B W B M B W B W B M 9

Special operations of relational algebra Щукин B.А. 2017г Selection R w = R 1 [ Boolean expr ] { AtrR w } = { AtrR 1 } Boolean expr = pred And|Or|Not pred … pred = =|!=|>| =|<= Example : R w (Nz, Fio, S, Age, Gr ) = Students[ Gr = B ] st701.1 Volodin K.P. M 19 B st701.2 Koldin O.P. M 18 B

Special operations of relational algebra Щукин B.А. 2017г Join R w = R 1 [ Boolean expr ]R 2 { AtrR w } = { AtrR 1 } + { AtrR 2 } Boolean expr = pred And|Or|Not pred … pred = AtrR 1 AtrR 2 {=|!=|>| =|<=} Example : R w = Students[ Students.Gr = Groups.Gr ]Groups … st701.1 Volodin K.P. M 19 B B Systems Theory 28 st701.2 Koldin O.P. M 18 B B Systems Theory 28 11

Special operations of relational algebra Щукин B.А. 2017г Division R w = R 1 [AtrR 1 : AtrR 2 ]R 2 { AtrR w } = { AtrR 1 } \ AtrR 1 Example : LiLa( IdL ) - list of foreign languages KnLa( Nz, IdL) - knowledge of foreign languages R w ( Nz ) = KnLa[KnLa.LdL : LiLa.IdL]LiLa R w ( Nz ) = KnLa[Nz] \ (KnLa[Nz]*LiLa \ KnLa)[Nz] 12

Integrity Constraints Щукин B.А. 2017г Atr Nz - Key Table Students : Rename - Students As X, Students As Y X[X.Nz=Y.Nz And X.Fio!=Y.Fio And X.S!=Y.S And X.Age!=Y.Age And X.Gr!=Y.Gr]Y = Atr Gr – Foreign Key Table Students : Rename – Groups As Z X[Gr] \ Z[Gr] = 13

Converter : Algebra - SQL Щукин B.А. 2017г 14

Examles «Converter» Щукин B.А. 2017г Students doing rugby only. Type variables : Rugby AS X, Gymnastics AS Y, Students AS Z Target list : Z.* Request body : ((X[X.Nz=Z.Nz]Z)[X.Nz]) EXCEPT (Y[Y.Nz]) 15

Examles «Converter» Щукин B.А. 2017г Students doing rugby only. SELECT DISTINCT Z.* FROM Students AS Z WHERE EXISTS (SELECT * FROM Rugby AS X WHERE X.Нз=Z.Нз AND NOT EXISTS (SELECT * FROM Gymnastics AS Y WHERE Y.Нз=X.Нз)) 16