?????????(Object Relational Database System,ORDBS)?????????(Object Oriented Data Model,??OO??)?????????????

1
??????????
??????? An Introduction to Database System ????
?????????
2
?????????

• ?????????(Object Relational Database
  System,ORDBS)?????????(Object Oriented Data
  Model,??OO??)?????????????
• ??????
• ???????????????,???????????,??OO??
• ?????????????OODBS,??OO????
• ???????SQL???,???????????????ORDBS

3
???? ?????????

• 15.1 ????????
• 15.2 ??-?????
• 15.3 ??

4
15.1 ????????

• ???????????OO??
• ?????????????????????????????
• ??????OO????????????

5
15.1 ????????

• 15.1.1 OO???????
• 15.1.2 ???(??)
• 15.1.3 ??
• 15.1.4 ?????

6
15.1.1 OO???????

• 1.??
• ???????????????????????????????????????
• ????
• ??(Attribute)??
• ???????????????
• ??(Method)??
• ??????????

7
OO???????(?)

• 2. ????OID(Object IDentifier)
• ?????????????????????????????????(OID)
• ??
• ?????
• ?????????????

8
OO???????(?)

• 3. ??(Encapsulation)
• ???????????????
• ????????????????????????????,??????????????
• ???????????????????

9
OO???????(?)

• 4. ?(Class)
• ???(???)???????????????????????
• ???????????????(instance)
• ?OODB?,???,??????????

10
15.1 ????????

• 15.1.1 OO???????
• 15.1.2 ???(??)
• 15.1.3 ??
• 15.1.4 ?????

11
???(??)(?)
?15.1 ????????????

• ???????????????????????
• ??????????????????????
• ????????????????????????

12
???(??)(?)

• ??/??????????IS A???
• ????????(Generalization)???
• ?????????(Specialization)????
• ?????????,??????????????????

13
15.1 ????????

• 15.1.1 OO???????
• 15.1.2 ???(??)
• 15.1.3 ??
• 15.1.4 ?????

14
15.1.3 ??

• ??????????????????(???????)
• ?????????
• ??????????????????
• ?????????????????

15
??(?)

• ????

????????????
16
??(?)

• ??????
• ??,???????,????????????????
• ??,?????????
• ????????
• ???????????????????????????????????
• ?????

17
15.1 ????????

• 15.1.1 OO???????
• 15.1.2 ???(??)
• 15.1.3 ??
• 15.1.4 ?????

18
15.1.4 ?????

• ????
• ??????????????,????????????????
• ?Obj1?Obj2????????Obj2?Obj1???????,?Obj2??Obj1,?Ob
  j1??Obj2?
• ????
• ???
• ??????

19
?????(?)
????????
20
?????(?)

• ?????????????????????????????
• ???????????
• ???????????????

21
?????OO?????
?15.1 ???????OO?????
?? ?????? ????????
?????? ??? ?
????? ? OID
???? ?? ??
???? ???? ??
?????? ? ?
??? ? ?
????? ?????,???? ?????
?????? ? ?
22
???? ?????????

• 15.1 ????????
• 15.2 ??-?????
• 15.3 ??

23
15.2 ??-?????

• 15.2.1 ???????????????????
• 15.2.2 ?????????????????????
• 15.2.3 ????(Reference Type)
• 15.2.4 ???
• 15.2.5 ?????

24
???????????????????

• ?????
• LOB
• BOOLEAN
• ????ARRAY
• ?????DISTINCT???
• ?????????
• ???ROW TYPE
• ??????(Abstract Data Type)

25
ORDBS??????????(?)

• ???LOB(Large OBject )??
• LOB????????????
• LOB??
• ??????BLOB(Binary Large OBject)
• BLOB???????????
• ??????CLOB(Character Large OBject)?
• CLOB??????????

26
ORDBS??????????(?)

• 2. BOOLEAN??
• ????,??3???true?false?unknown
• ???NOT?AND?OR?EVERY?ANY
• ?? WHERE EVERY(QTYgt200)
• ?WHERE ANY(QTYgt200)
• QTY??????unknown
• QTY????
• ??????????(QTYgt200)?true?,EVERY??true,???false
• ??????????(QTYgt200)?false?,ANY??false,???true?

27
ORDBS??????????(?)

• 3.????(Collection Type)ARRAY
• ???????????????ARRAY
• SQL3???????
• ??????????????
• SQL3?????????
• ????????????

28
ORDBS??????????(?)

• ?2
• CREATE TABLE SALES
• (
• ITEM_NO CHAR(20), /???/
• QTY INTEGER ARRAY12, /????,?????/
• PRIMARY KEY(ITEM_NO)
• )

29
ORDBS??????????(?)

• ?SALES???????
• INSERT INTO SALES(ITEM_NO,QTY)VALUES
• (T-shirt2000,ARRAY200,150,200,100,50,70,
  80,200,10,20,100,200)
• ??????????100????
• SELECT ITEM_NO
• FROM SALES
• WHERE QTY3gt100

30
ORDBS??????????(?)

• 4. DISTINCT??
• SQL3?????DISTINCT??
• ??DISTINCT??????
• CREAT TYPE lttype namegt
• AS ltbuilt in scalar type namegt FINAL
  ltcast optiongt ltmethod specification
  commalistgt

31
ORDBS??????????(?)

• ????DISTINCT??
• ??,???????(IQ)?????(SHOE_SIZE)???INTEGER??
• WHERE SHOE_SIZE gt IQ

32
ORDBS??????????(?)

• ??DISTINCT??
• ??????????
• CREAT TYPE SHOE_SIZE_TYPE AS INTEGER FINAL
• CREAT TYPE IQ_TYPE AS INTEGER FINAL
• SHOE_SIZE_TYPE?IQ _TYPE???????????
• ???WHERE SHOE_SIZE gt IQ ????
• ?????????????ltcast optiongt,?????????WHERE
  MY_SHOE_SIZE gt CAST (MY_IQ AS SHOE_SIZE)

33
15.2 ??-?????

• 15.2.1 ???????????????????
• 15.2.2 ?????????????????????
• 15.2.3 ????(Reference Type)
• 15.2.4 ???
• 15.2.5 ?????

34
ORDBS????????????

• ?ORDBMS?,??(TYPE)???(CLASS)???,?????
• 1.???????
• ?????(ROW TYPE)
• CREATE ROW TYPE ltrow_type_namegt
• (ltcomponent declarationsgt)

35
ORDBS????????????(?)

• ?????
• ?3
• CREATE ROW TYPE Person_type
  (pno NUMBER, name VARCHAR2(100),
• address VARCHAR2(100) )

36
ORDBS????????????(?)

• ?????????
• CREATE TABLE lttable_namegt OF ltrow_type_namegt
• ?4
• CREATE TABLE person_extent OF Person_type
• (pno PRIMARY KEY )

37
ORDBS????????????(?)

• 2.????????
• ??????????,?????????????
• ?????????
• CREATE TYPE lttype_namegt AS OBJECT
• (ltcomponent declarationsgt)

38
ORDBS????????????(?)

• ?5
• CREATE TYPE address_objtyp AS OBJECT
• (street VARCHAR2(50),
• city VARCHAR2(50) )
• CREATE TYPE name_objtyp AS OBJECT
• (first_name VARCHAR2(30),
• last_name VARCHAR2(30) )

39
ORDBS????????????(?)

• ???,????????????
• ?6
• CREATE TABLE people_reltab
  ( Id NUMBER(10),
• name_obj name_objtyp, address_obj ad
  dress_objtyp)

40
ORDBS????????????(?)

• 3. ??????(Abastract Data Type,ADT)
• ??SQL3?????????????????????????????????????
• ??ADT??????
• CREATE TYPE lttype_namegt (
• ???????????,
• ???????????lt??,
• ?????????(??))

41
ADT???

• (1) ADT?????????????????
• (2) ???ADT????,????????????????
• (3) ADT???????(methods)???(functions)???
• (4) SQL3????????????,???????????
• (5) ADT?3??????????
• (6) ADT????????

42
15.2 ??-?????

• 15.2.1 ???????????????????
• 15.2.2 ?????????????????????
• 15.2.3 ????(Reference Type)
• 15.2.4 ???
• 15.2.5 ?????

43
15.2.3 ????(Reference Type)

• REF??(?????????)
• ?????
• ???????????????
• ??
• REF lt???gt
• ??
• REF????????????????
• ????OID

44
????(?)

• ?????Employee?Company,????????????,????????????
• (1)?????
• ?7
• CREATE ROW TYPE employee_type(
• name VARCHAR(35),
• age INTEGER )
• CREATE ROW TYPE Comp_ type(
• compname VARCHAR(20),
• location VARCHAR(20) )

45
????(?)

• (2)?????????
• CREATE TABLE Employee OF
  employee_type
• CREATE TABLE Company OF Comp_ type
• (3)??????
• CREATE ROW TYPE Employment _type (
• employee REF (employee_type),
• company REF (Comp_ type) )
• CREATE TABLE Employment OF Employment
  _type
• ?Employment???????employee?????????OID
• company????????????OID

46
????(?)

• ?8
• CREATE ROW TYPE employee_type(
• name VARCHAR(35),
• age INTEGER,
• emp_id REF(employee_type) )
• ?9
• CREATE TABLE Employee OF employee_type
• VALUES FOR emp_id ARE SYSTEM GENERATED

47
????(?)

• ??????
• lt?????gtREF(lt???gt)SCOPE IS lt???gt
• ?10
• CREATE TABLE address_objtab OF
  address_objtyp
• ?11
• CREATE TABLE people_reltab2 (
• id NUMBER(4) PRIMARY KEY,
• name_obj name_objtyp,
• addresss_ref REF(address_objtyp)
  SCOPE IS address_objtab )

48
????(?)

• ?12
• CREATE INDEX address_ref_idx ON
  people_reltab2(address_ref)
• ?13
• SELECT id
• FROM people_reltab2 p
• WHERE p.address_ref.city'??' and
• p.address_ref.street'??'
  

49
15.2 ??-?????

• 15.2.1 ???????????????????
• 15.2.2 ?????????????????????
• 15.2.3 ????(Reference Type)
• 15.2.4 ???
• 15.2.5 ?????

50
15.2.4 ???

• ORDBMS???????
• ???????
• ?14
• CREATE TYPE emp_type
• UNDER person_type AS( emp_id
  INTEGER, salary REAL )
• NOT FINAL
• NOT FINAL????????????????
• FINAL?????????????

51
15.2 ??-?????

• 15.2.1 ???????????????????
• 15.2.2 ?????????????????????
• 15.2.3 ????(Reference Type)
• 15.2.4 ???
• 15.2.5 ?????

52
15.2.5 ?????

• ????????????????????
• ???????????????

53
?????(?)

• ?15 ?????????,???????TYPE,????????????

??????
54
?????(?)

• CREATE TYPE person /??person ??,???/
• (id INTEGER,
• name VARCHAR(20),
• birthyear INTEGER,
• address VARCHAR(40))
• NOT FINAL /NOT
  FINAL????????/
• CREATE TYPE employee /??person????employee/
• UNDER person /??employee??person???/
• (salary INTEGER) / employee???????/
• NOT FINAL

55
?????(?)

• CREATE TYPE executive /??employee????executive/
• UNDER employee
• (bonus INTEGER)
• FINAL
• CREATE TYPE student /??person????student /
• UNDER person
• (major VARCHAR(10),wage DECIMAL)
• FINAL

56
?????(?)

• ?16Department???employee?????????,??REF???????
• CREATE TYPE department
• (ID INTEGER,
• manager REF(employee),
• Budget INTEGER)
• ALTER TYPE employee
• ADD ATTRIBUTE dept REF(department)

57
?????(?)

• ????????????????
• CREATE TABLE person_table OF person
  (name WITH OPTIONS NOT NULL)

employee_table? person_table???

• CREATE TABLE employee_table OF employee
• UNDER person_table

• CREATE TABLE exec_table of executive
• UNDER employee_table

58
?????(?)

• CREATE TABLE student_table OF student
• UNDER person_table
• CREATE TABLE dept_table OF department
• (manager SCOPE IS employee_table)
• ALTER TABLE employee_table
• ALTER COLUMN dept ADD SCOPE IS dept_table

59
?????(?)

• ???16?????
• ?17
• SELECT name,address
• FROM person_table
• WHERE birthyear lt1970

60
?????(?)

• ???????
• ?18
• SELECT name,address
• FROM ONLY person_table
• WHERE birthyear lt1970

61
?????(?)

• INSERT?DELETE?UPDATE???????????
• INSERT?????????????????????????
• DELETE????????????????????????????

62
???? ?????????

• 15.1 ????????
• 15.2 ??-?????
• 15.3 ??

63
15.3 ??

• OO???????
• ??
• ????OID
• ??
• ?
• ???????????????????
• ???LOB
• BOOLEAN??
• ????ARRAY
• DISTINCT??

64
??(?)

• ?????????????????
• ???????
• ????????
• ??????
• ???????????
• ??
• ?????