1 / 20

Ch. 4 Relational Algebra (1)

Ch. 4 Relational Algebra (1). Basic concepts. Relational Model. Data models are different in Data representation ( 표현구조 ) Constraints ( 제약조건 ) Operators ( 연산자 ) Operators in the Relational Model Relational Algebra Relational Calculus SQL. Relational Algebra. R(A1, A2, A3)

dylan-potter
Download Presentation

Ch. 4 Relational Algebra (1)

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. Ch. 4Relational Algebra (1) Basic concepts

  2. Relational Model • Data models are different in • Data representation (표현구조) • Constraints (제약조건) • Operators (연산자) • Operators in the Relational Model • Relational Algebra • Relational Calculus • SQL 데이터베이스시스템

  3. Relational Algebra R(A1, A2, A3) Relation value(값) or state(상태) is a time-varying subset of the Cartesian product of all domains. Relation value is a set of tuples 데이터베이스시스템

  4. Operators (1) • Set operators • UNION (합집합) A U B • INTERSECT (교집합) A ∩ B • DIFFERENCE (차집합) A – B • CARTESIAN PRODUCT (곱집합) A x B • Relation operators • SELECT : row (tuple) selection • PROJECT : column (attribute) selection • JOIN : matching the values of common attributes • DIVISION : 데이터베이스시스템

  5. Operators (2) • Cartesian product 데이터베이스시스템

  6. Operators (3) • Relation operators • Join : matching the values of common attributes • DIVISION : values of attributes which include all the tuples of other relation. A [a, b] / B [b] : values of a such that (a, b) exists in A for every value of b in B 학생 [학번, 구독신문] / 신문 [신문] 데이터베이스시스템

  7. Example A : 남학생 (학번, 이름, 학과, 학년) B : 신입생 (학번, 이름, 학과, 학년) C : 과목 (과목이름, 학점수, 개설학과) D : 수강과목 (학번, 과목이름, 학점) E : 학과소속 (대학이름, 학과이름) • A U B • A – B • A ∩ B • A x C 데이터베이스시스템

  8. A : 남학생 (학번, 이름, 학과, 학년) B : 신입생 (학번, 이름, 학과, 학년) C : 과목 (과목이름, 학점수, 개설학과) D : 수강과목 (학번, 과목이름, 학점) E : 학과소속 (대학이름, 학과이름) • E where 대학이름 = ‘경영’ • C [과목이름, 개설학과] • ( (C rename 개설학과 as 학과이름) Join E) [과목이름, 학점수, 학과이름, 대학이름] Relation is closed (닫혔음) under all operators.The result of the operation on relations is a relation Allows nested operation ( (R1 op1 R2) op2 R3) op3 R4 데이터베이스시스템

  9. A : 남학생 (학번, 이름, 학과, 학년) B : 신입생 (학번, 이름, 학과, 학년) C : 과목 (과목이름, 학점수, 개설학과) D : 수강과목 (학번, 과목이름, 학점) E : 학과소속 (대학이름, 학과이름) • ( ( (C rename 개설학과 as 학과이름) Join E) where 대학이름=‘경영’) [과목이름, 학점수] • ( (C rename 개설학과 as 학과이름) Join (E where 대학이름=‘경영) ) [과목이름, 학점수] • ( (C rename 개설학과 as 학과이름) / ( (E where 대학이름=‘경영’)[학과이름]) 데이터베이스시스템

  10. A : 남학생 (학번, 이름, 학과, 학년) B : 신입생 (학번, 이름, 학과, 학년) C : 과목 (과목이름, 학점수, 개설학과) D : 수강과목 (학번, 과목이름, 학점) E : 학과소속 (대학이름, 학과이름) • List subject_name and offering department for all the subjects that freshmen take.( B Join D Join C) [과목이름, 개설학과] ( B[학번] Join D )[과목이름] Join C[과목이름, 개설학과] • Student_numbersand names of 2nd , 3rd, and 4th year male students who got grade(학점) higher than or equal to B (A Join (D where 학점>=‘B’) ) [학번, 이름]– B[학번, 이름] (A – B)[학번, 이름] Join (D where 학점>=‘B’)[학번] 데이터베이스시스템

  11. degree and cardinality (1) R1 (A1, A2, A3), R2 (A1, A2, A3) R3 (A1, B2, B3, B4) • R1 and R2 are union compatible • degree of a relation : number of atttibutes • cardinality of a relation : number of tuples R1 U R2 degree = d(R1) = d(R2)max{c(R1), c(R2)} <= cardinality <= c(R1) + c(R2) R1 - R2degree = d(R1) = d(R2)0 <= cardinality <= c(R1) 데이터베이스시스템

  12. degree and cardinality (2) R1 (A1, A2, A3), R2 (A1, A2, A3), R3 (A1, B2, B3, B4) R1 ∩ R2degree = d(R1) = d(R2)0 <= cardinality <= min {c(R1), c(R2)} R1 X R3degree = d(R1) + d(R3)cardinality = c(R1) x c(R3) R1 Join R3degree = d(R1) + d(R3) - # of common attrs.0 <= cardinality <= max {c(R1), c(R3)} R1 where 조건(SELECT)degree = d(R1) 0 <= cardinality <= c(R1) 데이터베이스시스템

  13. degree and cardinality (3) R1 (A1, A2, A3), R2 (A1, A2, A3), R3 (A1, B2, B3, B4) R1 [A2, A3] (PROJECT)degree = 2 (number of projected attributes)1 (all the values are the same) <= cardinality <= c(R1) R3 / R1[A1] (DIVISION)degree = d(R3) - d(R1[A1]) 0 <=cardinality <= c(R3) / c(R1[A1]) 데이터베이스시스템

  14. Composite operators (1) • Primitive operator Minimal set of operators that cannot be defined by other operators (Union, Difference, Cartesian Product, Select, Project) • Composite operator Operators that can be defined by other primitive operators (Intersect, Join, Division) A ∩ B = A – (A – B) = B – (B – A) A Join B = ( (A X B) where common attributes are matched ) [all but one common attributes] A [a, b] / B [b] = A[a] – ((A[a] X B) – A) [a] 데이터베이스시스템

  15. Composite operators (2) • Theta (Θ) Join cartesian product with a condition Θ • equiJoin theta join where condition Θ is equality (value matching) • Natural Join (자연조인) equiJoin with one common attribute is deleted. 데이터베이스시스템

  16. Operators • Cartesian product • Theta Join 데이터베이스시스템

  17. Composite operators (3) equiJoin Natural Join 데이터베이스시스템

  18. example 데이터베이스시스템

  19. Composite operators (4) A [a, b] / B [b] A[a] – ((A[a] X B) – A) [a] 데이터베이스시스템

  20. Composite operators (5) A[a,b] / B[b] = A[a] – ((A[a] X B[b]) – A)[a] 데이터베이스시스템

More Related