Fundamentals of Database Systems

1
?????????????????
? 5 ?
2
????

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

3
????????

• ?????(relational model)??????????????(relation)??
  ????
• ?????????????(table),??????
• ?????????????????????????
• ?????????,?????????

4
??????

• ?? (Relation)??????????
• ?????????? (row) ???
• ???????????? (column) ???
• ????????????????????
• ???????????????????????????
• ????(row)?????????????????????
• ????????????(key)
• ??,?5.1?,Ssn????(key)

5
Student????????
6
?????-??(schema)

• ????????????
• ??(relation)??
• ??(tuple)???
• ??(attribute)?????
• ???(domain)?????????????
• ?????
• ????R (A1, A2, .....An)
• R??????
• A1, A2, .....An ???????
• ??
• CUSTOMER (Cust-id, Cust-name, Address, Phone)
• CUSTOMER?????
• 4???Cust-id?Cust-name?Address?Phone

7
?????-??(tuple)

• ?? (tuple)???????? (ordered set)
• ???????????????
• ???CUSTOMER???
• ???????????,????4??
• ?? ,lt632895, John Smith, 101 Main St. Atlanta,
  GA 30332, [phone]gt ???????
• ???????????? (tuple) ???
• ?????(degree)?????????

8
?????-???(domain)

• ???(domain)???????????(atomic)??????
• ????????????????
• USA_phone_numbers????????????(ddd)-ddd-dddd,???d??
  ???10????
• ??????????,?yyyy-mm-dd?dd mm,yyyy
• ?????????????????????
• ????????????????,?????????????
• ??,Person_weights?????????(?or??)
• ??Ai????,????dom(Ai)

9
?????-??(state)

• ????(relation state)??R(A1, A2, ...,
  An)?????n???????? rt1, t2, ..., tm
• ??? r(R)
• ??n??(n-tuple) t ???n?vi????????
• ???, t v1, v2, ..., vn,??vi??dom
  (Ai)?????????????(NULL)
• ????????????
• ???????? (cartesian product) ????
• ???????????????????????
• r(R) ? dom (A1) dom (A2) .... dom(An)
• ???????????
• dom (A1) dom (A2) .... dom(An)
• dom (Ai)?????dom (Ai)????????

10
??????

• ??R(A1, A2) ???????
• ? dom(A1) 0,1 ? dom(A2) a,b,c
• ? dom(A1) dom(A2) ?????????,????
• lt0,agt , lt0,bgt , lt0,cgt, lt1,agt, lt1,bgt, lt1,cgt
• ?? r(R) ? dom(A1) dom(A2)
• ??,?????????? r(R) ????????,????
• lt0,agt, lt0,bgt, lt1,cgt

11
???????
???????? ????????
??(relation) ??(table)
??(tuple) ?????(row or record)
??(cardinality) ????(number of rows)
??(attribute) ?????(column or field)
???(domain) ????(Values in a column)
??(degree) ????(number of columns)
??(primary key) ?????(unique identifier)
12
????? (1/4)

• ?? r(R)??????
• ????????????????
• ?5.1??5.2?????????????
• ????????????????
• ??????????,????????
• ????????? n ?? n1 ???????? ????? 6 ?????
• ?????????????????????????????
• ??????,????????????(order by)

13
????? (2/4)
14
????? (3/4)

• ????R?????? (??????????)
• ???R(A1, A2, ..., An) ????,??? tltv1, v2, ...,
  vngt ???????? (ordered)
• ?????????,??????????
• ???,????????????????
• ???????????????????
• ?5.3????????????,???????

15
????? (4/4)

• ???????
• ??????,???????????????? (atomic)?
• ???
• ??? t ????? tAi vi ??? (?? t ???Ai ??)
• tAu, Av, ..., Aw ??? t ????,????????Au, Av,
  ..., Aw ??
• ??????
• ?? (null) ????????????,??????????

16
????????

• ????? (constraint) ??????????????????????
  (condition)??????????3?
• ???? (key constraint)
• ??????? (entity integrity constraint)
• ??????? (referential integrity constraint)
• ????? (domain constraint)
• ????A???????????dom(A)????????????
• ???????????

17
???? (1/2)

• ????(key constraint)
• ???????,??????????(key)???????????????
• ??(key)
• ??(??K)??????(superkey),????
• ????K????????,???????K???????R???
• ??(superkey)
• ????R???,??R????????(??SK),???????
• ????????r(R)?,??????SK?????????????????
• ???r(R)?????? t1 ? t2 ? t1SK? t2SK

18
???? (2/2)

• ????CAR????
• CAR(State, Reg, SerialNo, Make, Model, Year)
• ??CAR????? (key)
• Key1 State, Reg
• Key2 SerialNo
• Key1 ? Key2 ??CAR ??? (superkey)
• SerialNo, Make?CAR???,?????
• ??
• ??????? (??,???)
• ????????????????
• ?????????

19
?????? (1/2)

• ???(candidate key)
• ????????????????,?????,???????????
• ??5.4?,CAR????????
• License_number ? Engine_serial_number
• ???????????????????
• ??(primary key)
• ??????????????????
• ????????????????,????????
• ??????????
• ??????????????????????????
• ???????(null)???

20
?????? (2/2)
21
????????

• ????????(schema)
• ??????????S,??????????,S R1, R2 ,
  Rm,?????????(integrity constraint) IC
• ????????(state)
• S?????????DB,??????????,DB r1, r2 ,
  rm,?????ri ??Ri ???,?ri ??IC???????
• ?5.5?????????????
• ??????
• ?????????????????

22
???????????
23
???????????
24
????????????? (1/2)

• ???????(entity integrity constraint)
• ??????????? (NULL)
• ???????(referential integrity constraint)
• ????R1????????R2?, R1?????????????????
• ????????????????????
• ????????????????,???????????????

25
????????????? (2/2)

• ???(foreign key)
• ?????R1????????FK??????,??FK?R1??R2????
• ?FK?????R2?????PK???????
• ?????r1(R1)?t1???,FK???????r2(R2)?????t2??PK?,????
  
• ????????
• R1?????? (referencing relation)
• R2??????? (referenced relation)
• ?????????????
• ??????????????????????

26
?????????
27
???????

• ??????? (Semantic Integrity Constraint)
• ?????????,??????????
• ??,???????????????????56????????
• ??????????????? (constraint specification
  language) ???
• ??????????? (trigger) ???(assertion)
• SQL-99? CREATE ASSERTION?????

28
????????? (1/2)

• ??????????????
• ?? (INSERT)
• ????????????
• ?? (DELETE)
• ??????
• ?? (UPDATE)
• ?????????????
• ??
• ????????,?????????????????
• ??????????????????
• ?????????(propagate)?????????????
• ???????????????

29
????????? (2/2)

• ??????????????,??????????
• ??(cancel)???????????
• REJECT ??
• ??(perform)???????(inform)??????????
• ??(trigger)???????,?????????
• CASCADE???SET NULL??
• ????????????????

30
??(insert)??

• ??????????????
• ?????
• ????????????????????
• ????
• ???????????????????????
• ???????
• ??????????NULL
• ???????
• ???????????????????????????

31
????-?? (1/2)

• ? ltCecilia, F, Kolonsky, null,
  1960-04-05, 6357 Windy Lane, Katy, TX, F,
  28000, NULL, 4gt ???EMPLOYEE ?
• ??(Reject)??????????Ssn ?NULL,???????????
• ????????????Ssn?
• ? ltAlicia, J, Zelaya, 999887777,
  1960-04-05, 6357 Windy Lane, Katy, TX, F,
  28000, 987654321, 4gt???EMPLOYEE ?
• ??(Reject)??????????Ssn ?? 999887777????EMPLOYE
  E?,????????

32
????-?? (2/2)

• ? ltCecilia, F, Kolonsky, 677678989,
  1960-04-05, 6357 Windswept, Katy, TX, F,
  28000, 987654321, 7gt ???EMPLOYEE ?
• ????????????????Dno ? 7,???????????(?DEPARTMENT??
  ?Dnumber7)
• ? ltCecilia, F, Kolonsky, 677678989,
  1960-04-05, 6357 Windy Lane, Katy, TX, F,
  28000, NULL, 4gt???EMPLOYEE ?
• ??(Accept)??????????????

33
??(delete)?? (1/2)

• ?????????????????
• ??
• ??Essn999887777?Pno10?WORKS_ON??
• ??(Accept)?????????
• ??Ssn999887777?EMPLOYEE??
• ????(Not Accept)??WORKS_ON??????????,????????????
  ???
• ??Ssn333445555?EMPLOYEE??
• ????(Not Accept)??EMPLOYEE, DEPARTMENT,
  WORKS_ON?DEPENDENT?????????????

34
??(delete)?? (2/2)

• ???????????,??????????
• ??(Reject)??
• ??(Cascade)??????????????????
• ??(update)????????????????????,???????NULL,??????
  ?????????
• ??DBMS??????????????,?????????????

35
??(Update)?? (1/2)

• ?Ssn999887777?EMPLOYEE????Salary?????28000
• ???(Acceptable)
• ?Ssn999887777?EMPLOYEE????Dno?????1
• ???(Acceptable)
• ?Ssn999887777?EMPLOYEE????Dno?????7
• ????(Unacceptable)?????????
• ?Ssn999887777?EMPLOYEE????Ssn?????987654321
• ????(Acceptable)????????????,??????
• ????????Ssn????,?????????

36
??(Update)?? (2/2)

• ??
• ??????????,???????????,???????
• ???????????????????????????
• ???????,???????
• ????????????,??????
• ??????????????????
• ????????,?????????????????(??NULL)