RDB개요.ppt

Size: px

Start display at page:

Download "RDB개요.ppt"

이우 순
6 years ago
Views:

3 1 2 3 < >

4 1 SQL SQL 2 SQL 3

5 column DEPT DEPT# DNAME BUDGET D1 D2 D3 Marketing Development Research 10M 12M 5M tuple EMP EMP# ENAME DEPT# SALARY D1 40 D1 45 E1 E2 E3 Lopez Cheng Finzi D2 30 E4 Satio D2 35

8 SELECT (RESTRICT) : DEPT# DNAME BUDGET DEPTs where BUDGET > 8M D1 Marketing 10M D2 Development 12M PROJECT : DEPTs over DEPT#, BUDGET DEPT# D1 D2 D3 BUDGET 10M 12M 5M

9 DEPTs and EMPs over DEPT# DEPT# DNAME BUDGET D1 D1 D2 Marketing Marketing Development 10M 10M 12M EMP# E1 E2 E3 ENAME Lopez Cheng Finzi SALARY 40K 42K 30K D2 Development 12M E4 Satio 35K

10 table world operator operator operator

15 DEPT# D1.. EMP# E1, E2.. repeating group DEPT# D1 D1.. EMP# E1 E2..

21 < > DH DH DH DH DH DH03 SP S# S1 S1 S1 S1 S1 S1 S2 S2 S3 S4 S4 S4 P# P1 P2 P3 P4 P5 P6 P1 P2 P2 P2 P4 P5 QTY

23 S# S1 S2 S3 S4 S4 SNAME STATUS CITY S S# S1 S2 SNAME STATUS CITY S S# S1 S2 S3 SNAME STATUS CITY S ERROR!

25 A S0001 S0002 S B S0001 S0001 S0002 5,000 6,000 4,000

27 < > DH DH DH DH DH DH03

29 S# S1 S2 S3 S4 S5 SNAME STATUS CITY S P# P1 P2 P3 P4 P5 P6 PNAME WEIGHT CITY P COLOR Red Green Blue Red Blue Red S# S1 S1 S1 S1 S1 S1 S2 S2 S3 S4 S4 S4 P# P1 P2 P3 P4 P5 P6 P1 P2 P2 P2 P4 P5 QTY SP

30 < > < > DH DH DH DH DH DH03 DH01 DH02 DH03 DH04 DH05 DH06

31 DEPT EMP DEPT# DNAME BUDGET EMP# ENAME DEPT# SALARY primary key primary key foreign key

32 S# P#

34 < > manager DH DH DH DH DH DH03

37 < > < > DH DH DH DH DH DH03 DH01 DH02 DH03 DH04 DH05 DH06

38 fi EMP EMP# ENAME DEPT# SALARY E1 E2 E3 E4 Lopez Cheng Finzi Satio D1 D1 D2 D

39 EMP EMP# ENAME DEPT# SALARY D1 40 D1 45 E1 E2 E3 Lopez Cheng Finzi D2 30 E4 Satio D2 35 NEW_EMP1 EMP# ENAME E1 E2 E3 E4 Lopez Cheng Finzi Satio DEPT# D1 D1 D2 D2

40 EMP EMP# ENAME DEPT# SALARY E1 E2 E3 E4 Lopez Cheng Finzi Satio D1 D1 D2 D

41 EMP EMP# ENAME DEPT# SALARY D1 40 D1 45 E1 E2 E3 Lopez Cheng Finzi D2 30 E4 Satio D2 35 NEW_EMP2 EMP# ENAME SALARY E1 Lopez 40 E2 Cheng 45 E4 Satio 35

42 SELECT emp#, ename FROM new_emp1 WHERE emp# = E3 SELECT emp#, ename, dept# FROM new_emp2 WHERE emp# = E2 SELECT emp#, ename FROM new_emp2 WHERE emp# = E3 OK Error! No result

43 student Student_view1 Student_view2 Student_view

44 EMP EMP# ENAME DEPT# SALARY E1 E2 E3 Lopez Cheng Finzi D1 D1 D2 E4 Satio D NEW_EMP1 EMP# ENAME E1 E2 E3 E4 Lopez Cheng Finzi Satio DEPT# D1 D1 D2 D2

45 SELECT emp#, ename, dname FROM emp, dept WHERE emp.dept# = dept.dept# CREATE VIEW myemp AS SELECT emp#, ename, dname FROM emp, dept WHERE emp.dept# = dept.dept# SELECT emp#, ename, dname FROM myemp

46 SELECT emp#, ename, dname FROM myemp WHERE emp# = E3 SELECT emp#, ename, dname FROM emp, dept WHERE emp.dept# = dept.dept# AND emp# = E3

47 EMP EMP# ENAME DEPT# SALARY E1 E2 E3 Lopez Cheng Finzi D1 D1 D2 E4 Satio D NEW_EMP1 EMP# ENAME E1 E2 E3 Lopez Cheng Finzi E4 Satio DEPT# D1 D1 D2 D2? OK OK

48 NEW_EMP3 EMP# ENAME DEPT# UPSALARY E1 E2 E3 Lopez Cheng Finzi D1 D1 D E4 Satio D2 135

50 Production_data CREATE SNAPSHOT daily_prod_summary AS SELECT prod_date, sum(amount) as total GROUP BY prod_date REFRESH EVERY DAY ;

51 Production_data Daily_prod_summary Daily update

I T C o t e n s P r o v i d e r h t t p : / / w w w. h a n b i t b o o k. c o. k r

I T C o t e n s P r o v i d e r h t t p : / / w w w. h a n b i t b o o k. c o. k r -------------------------------------------------------------------- -- 1. : ts_cre_bonsa.sql -- 2. :