Java????

1

• Java????
• ???????
•  
• Java System Concepts and Database Programming
•  
•  
• 
  ??? ??? ??? ??

2
??? 

• Java?????

3

• ?????????,??Java/Access?????,??????????????Access?
  ????????????,??Office???,??????,????????Java??????
  ??????,?????????,??????????????,??????????????????
  ,????Java???????????

4
??? 

• Java????

5
1-1 ??

• ??????????J2SE,?????????(J2SE Development
  Kit),???jdk???????(http//java.sun.com)
  ???????????????Java??????,????????????????????
•  
• ????????Java ???? (jdk-6.0)???Java????????Java????
  ?Java??????????

6
1-2 ??Java ???? (jdk-6.0)

• jdk-6.0?Java???????,???? http//java.sun.com
  ??????(Sun) ????????????C\BookJavaVol_4\System???
  ???? jdk-6u4-windows-i586-p.exe?

7
1-3 ??Java??

• Java????????C\Program Files\Java\jdk1.6.0_04\bin?
  ?? (???)?????Java??????Java????,??????????????????
  ,????,?????

8
1-4 ?????Java??

• ???Java??????????,???? ??? ????,???????,???????
  ??? ?Java???????

9
??? 

• ???Java?????

10
2-1 ??

• ????????(Relation Algebra) ?????????????????SQL???
  ??????,?Java???????SQL?????????
• ??????????????,????????Java?Access???,???????????
  ??????ODBC?????????????????

11
2-2 ??Access???

• ????Java/Access??,?Access2007??????(??????????????
  ???),????????C\BookJavaVol_4\Program\ch02\02_2???
  ??Book.accdb?

12
2-3 ??ODBC

• ?????????(????Book.accdb),???????????????(Data
  Tables)???/???????????Win??????????????????/?????
•  
• ?????Win??????????????????/????,?????ODBC(Open
  Database Connectivity),????Win?????????

13
2-4 ?????

• ???(Data Relation Tabes) ????????,????(Field)
  ?????(Type)?????????????(by hands)?Java???(by
  Java)?
•  
• ??????,??????????,??????????,?????????(Relation
  Algebra) ?????????????????SQL?????????,?Java??????
  ?SQL?????????

14
2-4-2 Java?????(by Java)

• 1???ODBC,?????Win????
• 2???????sun.jdbc.odbc.JdbcOdbcDriver,?????
• Class.forName(sun.jdbc.odbc.JdbcOdbcDriver)
• 3???????,?????,?????
• Connection con DriverManager.getConnection(j
  dbcodbcxxx)
• ??con?????,xxx?????????
• 4???????????createStatement() ??????,?????
• Statement stmt con.createStatement()
• ??stmt??????
• 5??SQL?????? create table ,??
  ???????????????????????????executeUpdate()
  ?????,?????
• stmt.executeUpdate(xxxxx)
• ??xxxxx?SQL???

15
??01????CreateTable02_4_2.java,???????Java??????

• 01 import java.sql.
•  
• 02 public class CreateTable02_4_2
• 03 public static void main(String args)
• 04 String JDriver "sun.jdbc.odbc.JdbcOdbcDriv
  er"
• 05 String connectDB"jdbcodbcBook02_4"
•  
• 06 try
• 07 Class.forName(JDriver)
• 08
• 09 catch(java.lang.ClassNotFoundException e)
• 10 System.out.println("ForName "
  e.getMessage())
• 11
•  

16
??01?

• 12 try
• 13 Connection con DriverManager.getConnecti
  on(connectDB)
• 14 Statement stmt con.createStatement()
• 15 String query "create table ??_by_Java (
  "
• "?? Text(20),"
• "?? Text(20),"
• "?? Text(10),"
• "?? Number"
• ")"
• 16 stmt.executeUpdate(query)
• 17 stmt.close()
• 18 con.close()
• 19
• 20 catch(SQLException e)
• 21 System.out.println("SQLException "
  e.getMessage())
• 22
• 23
• 24

17
2-5-2 Java?????(by Java)

• ??Java????(Data Bases) ????(Data Tables)
  ?????,??????2-4-2???,????????5?SQL?? create
  table ?? insert into ?

18
??02????InsertTable02_5_2.java,???????Java?????
????

• 01 import java.sql.
•  
• 02 public class InsertTable02_5_2
• 03 public static void main(String args)
• 04 String JDriver "sun.jdbc.odbc.JdbcOdbcDriv
  er"
• 05 String connectDBjdbcodbcBook02_5
• 06 try
• 07 Class.forName(JDriver)
• 08
• 09 catch(java.lang.ClassNotFoundException e)
• 10 System.out.println("ForName "
  e.getMessage())
• 11
•  

19
??02?1

• 12 try
• 13 Connection con DriverManager.getConnecti
  on(connectDB)
• 14 Statement stmt con.createStatement()
• 15 String query1 "insert into ??_by_Java
  values ("
• "'001','??? ????','???',
  527)"
• 16 String query2 "insert into ??_by_Java
  values ("
• "'002','Java??????','???',
  442)"
• 17 String query3 "insert into ??_by_Java
  values ("
• "'003','Java??????','???',
  476)"
• 18 String query4 "insert into ??_by_Java
  values ("
• "'004','Java??????','???',
  500)"
• 19 String query5 "insert into ??_by_Java
  values ("
• "'005','Java???????','???'
  , 480)"

20
??02?2

• 20 String query6 "insert into ??_by_Java
  values ("
• "'006','Java???????','???'
  , 510)"
• 21 stmt.executeUpdate(query1)
• 22 stmt.executeUpdate(query2)
• 23 stmt.executeUpdate(query3)
• 24 stmt.executeUpdate(query4)
• 25 stmt.executeUpdate(query5)
• 26 stmt.executeUpdate(query6)
• 27 stmt.close()
• 28 con.close()
• 29
• 30 catch(SQLException e)
• 31 System.out.println("SQLException "
  e.getMessage())
• 32
• 33
• 34

21
2-6 ?????

• ??Java????(Data Bases) ????(Data Tables)
  ?????,??????2-4-2???,????
• (1) ?SQL???? "select from xxx",??xxx??????
• (2) ??SQL????ResultSet??
• (3) ??ResultSet???while??????????????

22
??03????PrintTable02_6.java,???????Java????????
?

• 01 import java.sql.
•  
• 02 public class PrintTable02_6
• 03 public static void main(String args)
• 04 String JDriver "sun.jdbc.odbc.JdbcOdbcDriv
  er"
• 05 String connectDB"jdbcodbcBook02_6"
•  
• 06 try
• 07 Class.forName(JDriver)
• 08
• 09 catch(java.lang.ClassNotFoundException e)
• 10 System.out.println("ForName "
  e.getMessage())
• 11
•  

23
??03?

• 12 try
• 13 Connection con DriverManager.getConnecti
  on(connectDB)
• 14 Statement stmt con.createStatement()
• 15 ResultSet rs stmt.executeQuery("select
  from ??_by_Java")
•  
• 16 System.out.println("??""\t""??""\t""\t
  ""\t""??""\t""??")
• 17 while(rs.next())
• 18 System.out.println(rs.getString("??")"\
  t"rs.getString("??")"\t"
• rs.getString("??")"\t"
  rs.getInt("??"))
• 19
• 20 stmt.close()
• 21 con.close()
• 22
• 23 catch(SQLException e)
• 24 System.out.println("SQLException "
  e.getMessage())
• 25
• 26
• 27

24
2-7 ??(Exercises)

• 01????????????,??????Java????????
• 02???ODBC(Open Database Connectivity) ?????
• 03???Java?????(Data Bases) ????(Data Tables)
  ?,????????
• 04???Java????(Data Bases) ????(Data Tables)
  ?????,????????
• 05???Java????(Data Bases) ????(Data Tables)
  ?????,????????

25
???

•  
• Java???????

26

• ???(Database) ????????????(Data Store) ?????(Data
  Query),?????????,?????????,??????????,???????
  ???Java????SQL??????????????

27
???

•  
• ????(Basic Operations)

28
3-1 ??

• ?????????????????????(Record Relational
  Model),??????,???????,?Access2007????????Java?????
  ??SQL?????,?????????

29
3-3 SQL??????(Basic Query Structure)

• ??????(Relational Database) ???????(Relations)
  ????,????????????????,?????SQL??????(Clauses)
  ?????????(select Clause)????????(from
  Clause)??????(where Clause)?

30
3-5 ??????(select Clause)

• ??????(Relational Database) ???????(Relational
  Table) ???,??????(Record) ??,?????(Columns)
  ??????????????????????????,??????,??????????????

31
??04??Java??Select_04.java,?????Bank03.accdb????
Deposit(??3-3-4),?? ????branch_name???????

• 01 import java.sql.
•  
• 02 public class Select_04
• 03 public static void main(String args)
• 04 String JDriver "sun.jdbc.odbc.JdbcOdbcDri
  ver"
• 05 String connectDB"jdbcodbcBank03"
•  
• 06 try
• 07 Class.forName(JDriver)
• 08
• 09 catch(java.lang.ClassNotFoundException e)
  
• 10 System.out.println("ForName "
  e.getMessage())
• 11
•  

32
??04?

• 12 try
• 13 Connection con DriverManager.getConnect
  ion(connectDB)
• 14 Statement stmt con.createStatement()
• 15 ResultSet rs stmt.executeQuery("SELECT
  branch_name "
• "FROM
  Deposit")
•  
• 16 System.out.println(" branch_name")
• 17 System.out.println("-------------")
• 18 while(rs.next())
• 19 System.out.println(rs.getString("branc
  h_name"))
• 20
• 21 stmt.close()
• 22 con.close()
• 23
• 24 catch(SQLException e)
• 25 System.out.println("SQLException "
  e.getMessage())
• 26
• 27
• 28

33
??05????04,??Java??Select_05.java,?????Bank03.a
ccdb????Deposit(??3-3-4),?? ????branch_name??????
,??distinct?

• (1) SQL??????
• SELECT distinct branch_name
• FROM Deposit
•  
• (2) ????Select_05.java(????CD??)?

34
??06????05,??Java??Select_06.java,?????Bank03.a
ccdb????Deposit(??3-3-4),?? ????branch_name??????
,??all?

• (1) SQL??????
• SELECT all branch_name
• FROM Deposit
•  
• (2) ????Select_06.java(????CD??)?

35
??07??Java??Select_07.java,?????Bank03.accdb????
Deposit(??3-3-4),?? ????branch_name?customer_name
???????

• (1) SQL??????
• SELECT branch_name, customer_name
• FROM Deposit
•  
• (2) ????Select_07.java(????CD??)?

36
??08??Java??Select_08.java,?????Bank03.accdb???
?Deposit(??3-3-4),?? ????(balance)
??5??,????branch_name?customer_name?balance??????
?

• (1) SQL??????
• SELECT branch_name, customer_name,
  balance1.05 as newbalance
• FROM Deposit
• ??newbalance???5?????????????
•  
• (2) ????Select_08.java(????CD??)?

37
3-6 ????(where Clause)

• ??????????????,??????,?????????,??????????????????
  ???????(???09)???????(???10)???????(???11)?

38
??09??Java??Where_09.java,?????Bank03.accdb????
Borrow(??3-3-1),?? ????(branch_name)
Perryridge????????(loan_number) ????(amount)?

• (1) SQL??????
• SELECT loan_number, amount
• FROM Borrow
• WHERE branch_name Perryridge
•  
• (2) ????Where_09.java(???CD??)

39
??10??Java??Where_10.java,?????Bank03.accdb????
Borrow(??3-3-1),?? ????(branch_name)
Perryridge??????2500????????(loan_number)
????(amount)?

• (1) SQL??????
• SELECT loan_number, amount
• FROM Borrow
• WHERE branch_name Perryridge and
  amount gt 2500
•  
• (2) ????Where_10.java(????CD??)?

40
??11??Java??Where_11.java,?????Bank03.accdb????Bo
rrow(??3-3-1),?? ?????????2500??????3000????????(
loan_number) ????(amount)?

• (1) SQL?????1?
• SELECT loan_number, amount
• FROM Borrow
• WHERE amount between 2500 and 3000
•  
• (2) SQL?????2?
• SELECT loan_number, amount
• FROM Borrow
• WHERE amount lt 2500 and amountgt2500
•  
• (3) ????Where_11.java(????CD??)?

41
3-7 ?????????(from Clause)

• ???????????????????,???????,???????????????????
•  
• ????????Bank03.accdb,?????????Deposit??????????Bor
  row??????????Customer??????????Branch?????????????
  ,???????????,???????????????
•  
• ????????????,???????????????????,?????????????????
  ?,????????,??????????

42
??12??Java??Tables_12.java,?????Bank03.accdb???
?Borrow(??3-3-1) ?Deposit(??3-3-4),??
?????????????(customer_name) ???????(customer_cit
y)?

• (1) SQL??????
• SELECT Borrow.customer_name,
  Customer.customer_city
• FROM Borrow, Customer
• WHERE Borrow.customer_name
  Customer.customer_name
• ???????????,???????????????,??????????????????
  
•  
• (2) ????Tables_12.java(???CD??)

43
??13???12??,??Java??Tables_13.java,?????Bank03.
accdb????Borrow_Deposit,?? ?????????????(customer
_name) ???????(customer_city)?

• (1) SQL??????
• SELECT Borrow.customer_name,
  Customer.customer_city
• FROM Borrow_Customer
•  
• (2) ????Tables_13.java(????CD??)?

44
3-8 ????(Rename Operation)

• ??????,????(Rename) ???????????????,?????????,SQL?
  ????? as,??????(Relations) ???(Attributes)
  ???????????????????(???08)?

45
??14??Java??Rename_14.java,?????Bank03.accdb???
?Borrow_Deposit,?? ????SQL?????????

• (1) SQL??????
• SELECT customer_name as client_name,
  customer_city as city
• FROM Borrow, Customer
• WHERE Borrow.customer_name
  Customer.customer_name and
• branch_name Perryridge
•  
• ????????,?????Borrow_Customer?????Borrow,
  Customer,??SQL?????????
•  
• (2) SQL??????
• SELECT customer_name as client_name,
  customer_city as city
• FROM Borrow_Customer
• WHERE branch_name Perryridge
•  
• (3) ????Rename_14.java(????CD??)

46
3-9 ?????(Tuple Variables)

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

47
??15??Java??Variables_15.java,?????Bank03.accdb
????Deposit,?? ???????(branch_name)
?????(customer_name),??????????,??????(amount)
???????

• (1) SQL??????
• SELECT distinct T.branch_name,
  T.customer_name
• FROM Deposit as T, Deposit as S
• WHERE T.balance gt S.balance
•  
• (2) ????Variables_15.java(????CD??)

48
3-10 ????(String Operations)

• ?SQL???,??????????,??????????????????(like)
  ???,?????
•  
• 1????(Single Quote)SQL?????????????,? It is my
  book ?
•  
• 2????(Double Quote)???????????,? Its my
  book,????????,? Its my book?
•  
• 3????(Percent) ?????(Sub String) ?????(like),?
  downtown down,?? town?
•  
• 4???(Underscore) _????(Character) ?????(like),?
  down_own downtown,?? _ t down_ _
  _n downtown,?? _ _ _ t o w?
•  
• 5???? \??????(Escape),??????????,??????,SQL?????
  \ ???,? 10 of people ??? 10\ of people?

49
??16??Java??String_16.java,?????Bank03.accdb???
?Deposit_Customer,?? ?????(branch_name)
rryr ???????????(customer_name)
???????(customer_city)?

• (1) SQL??????
• SELECT customer_name, customer_city
• FROM Borrow_Customer
• WHERE branch_name like rryr
•  
• (2) ????Strein_16.java(????CD??)

50
3-11 ????(Ordering the Display of Tuples)

• SQL???? order by ?????(Attribute)
  ?????,???????????(Tuples) ??(Ordering)?

51
??17??Java??Order_17.java,?????Bank03.accdb????
Borrow_Customer,?? ??Perryridge??(branch_name)
???????(customer_name),???????????????

• (1) SQL??????
• SELECT customer_name
• FROM Borrow_Customer
• WHERE Branch_name Perryridge
• ORDER BY customer_name
•  
• (2) ????Order_17.java(????CD??)

52
??18??Java??Order_18.java,?????Bank03.accdb????
Borrow_Customer,?? ????SQL??????

• (1) SQL??????
• SELECT customer_name
• FROM Borrow_Customer
• WHERE Branch_name Perryridge
• ORDER BY customer_name desc
•  
• (2) ????Order_18.java(????CD??)?

53
??19??Java??Order_19.java,?????Bank03.accdb????
Borrow,?? ????SQL??????

• (1) SQL??????
• SELECT
• FROM Borrow
• ORDER BY amount asc, loan_name desc
•  
• (2) ????Order_19.java(????CD??)?

54
3-12 ??(Exercises)

• 01?????SQL???????(Clauses) ????
• 02?SQL?????????,????????
• 03?SQL?????????,????????
• 04????????,SQL??????????????
• 05????????????,??????????
• 06?????????????,????????
• 07?????????????
• 08???????,????(Rename) ?????????????
• 09? ?????(Tuple Variable) ?????
• 10??SQL???,?????????????
• 11????????(Tuples) ??(Ordering)?

55
???

•  
• ????(Set Operations)

56
4-1 ??

• ????(Set) ?? ?????????(well-defined
  List/Collection),?????????????(Elements)?
•  
• SQL????????,??? ??(union)???(intersect)???(exc
  ept) ????????(Sets) ?????

57
4-2 ?????(Definitions of Set)

• ?????????????????(Union)?????(Intersect)????????(
  Relative Complement)?????????(Absolute
  Complement)?

58

(b)
(a)
(d)
(c)
?4-2
59
4-3????(Union Operation)

• SQL????(Union Operation) ??????(Relational
  Algebra) ? ?,?????????????? or ??,????
  union?

60
??20??Java??Set_20.java,?????Bank03.accdb????Bo
rrow?Deposit,?? ?????????????????(customer_name)
?

• (1) SQL??????
• (SELECT customer_name
• FROM Borrow)
• UNION
• (SELECT customer_name
• FROM Deposit)
•  
• (2) ????Set_20.java(???CD??)

61
??21????20,??Java??Set_21.java,?????Bank03.accd
b????Borrow?Deposit,?? ????SQL?????????

• (1) SQL??????
• (SELECT customer_name
• FROM Borrow)
• UNION all
• (SELECT customer_name
• FROM Deposit)
•  
• (2) ????Set_21.java(???CD??)?

62
4-4????(Intersect Operation)

• SQL????(Intersect Operation) ??????(Relational
  Algebra) ? n,?????????????? and ??,????
  intersect?

63
??22??Java??,?????Bank03.accdb????Borrow?Deposi
t,?? ??????????????????(customer_name)?

• (1) SQL??????
• (SELECT customer_name
• FROM Borrow)
• INTERSECT
• (SELECT customer_name
• FROM Deposit)
•  
• (2) ????
• ???Access???INTERSECT???????,???????????????
  ?????Access???????(Nested Subqueries)?
  ??????(Test Set Membership) ????(?6-2???37)?

64
4-5????(Except/Minus Operation)

• SQL????(Except/Minus Operation) ??????(Relational
  Algebra) ? -,?????????????? not ??,????
  except ? minus?

65
??23??Java??,?????Bank03.accdb????Borrow?Deposit,
?? ???????????????????(customer_name)?

• (1) SQL??????
• (SELECT customer_name
• FROM Deposit)
• MINUS
• (SELECT customer_name
• FROM Borrow)
•  
• (2) ????
• ???INTERSECT,???Access???EXCEPT/MINUS
  ???????,????????????????????Access???????(Nested
  Subqueries)? ??????(Test Set Membership)
  ????(?6-2???38)?

66
4-6 ??(Exercises)

• 01?????(Set)?
• 02???????(Union)?
• 03???????(Intersection)?
• 04??????????(Relative Complement)?
• 05??????????(Absolute Complement)?
• 06?SQL????(Union Operation) ??????(Relational
  Algebra) ? ?,?????????????? or ??,???????
• 07?SQL????(Intersect Operation) ??????(Relational
  Algebra) ? n,?????????????? and ??,???????
• 08????Access?????INTERSECT???????
• 09?SQL????(Except/Minus Operation)
  ??????(Relational Algebra) ? -,??????????????
  not ??,???????
• 10????Access?????EXCEPT/MINUS ???????

67
???

•  
• ????(Aggregate Functions)

68
5-1 ??

• SQL???? avg ???????????????

69
??24??Java??Aggregate_24.java,?????Bank03.accdb
????Branch(??3-3-2),?? ????????????

• (1) SQL??????
• SELECT avg(assets) as avg_assts
• FROM Branch
•  
• (2) ????Aggregate_24.java (???CD??)?

70
5-3 ???(Sum)

• SQL???? sum ???????????????

71
??25??Java??Aggregate_25.java,?????Bank03.accdb?
???Borrow(??3-3-1),?? ?????????????(sum of
loan)?

• (1) SQL??????
• SELECT sum(amount) as total_amount
• FROM Borrow
•  
• (2) ????Aggregate_25.java (???CD??)?

72
??26??Java??Aggregate_26.java,?????Bank03.accdb?
???Borrow(??3-3-1),?? ?????(branch_name)
?????(group_amount)?

• (1) SQL??????
• SELECT branch_name, sum(amount) as
  group_amount
• FROM Borrow
• GROUP BY branch_name
•  
• (2) ????Aggregate_26.java(???CD??)?

73
??27??Java??Aggregate_27.java,?????Bank03.accdb?
???Borrow(??3-3-1),?? ??????(group_amount)
??3000????(branch_name)?

• (1) SQL??????
• SELECT branch_name, sum(amount) as
  group_amount
• FROM Borrow
• GROUP BY branch_name
• HAVING sum(amount)gt3000
•  
• (2) ????Aggregate_27.java(???CD??)?

74
5-4 ???(Count)

• SQL???? count ????????????????

75
??28??Java??Aggregate_28.java,?????Bank03.accdb?
???Borrow(??3-3-1),?? ?????(branch_name)
???????(customer_count)?

• (1) SQL??????
• SELECT branch_name, count(customer_name)
  as
• customer_count
• FROM Borrow
• GROUP BY branch_name
•  
• (2) ????Aggregate_28.java (???CD??)?

76
??29??Java??Aggregate_29.java,?????Bank03.accdb?
???Borrow(??3-3-1),?? ??????(group_amount)
??3000????2?????????(branch_name)?

• (1) SQL??????
• SELECT branch_name, sum(amount) as
  group_amount
• FROM Borrow
• GROUP BY branch_name
• HAVING sum(amount)gt3000 and
• count(customer_name)gt2
•  
• (2) ????Aggregate_29.java(????CD??)?

77
??30??Java??Aggregate_30.java,?????Bank03.accdb?
???Borrow(??3-3-1) ?Deposit(??3-3-4),??
??????(customer_count) ??2????(branch_name)?

• (1) SQL??????
• SELECT branch_name, count(customer_name)
  as
• customer_count
• FROM Borrow, Deposit
• WHERE Borrow.branch_name
  Deposit.branch_name
• GROUP BY branch_name
• HAVING count(customer_name)gt2

78
??30?

• (2) ?????????,?3-7-2???,????Bank03.accdb??Borrow.b
  ranch_name Deposit.branch_name????????Borrow_Dep
  osit,???SQL??????
• SELECT branch_name, count(customer_name)
  as
• customer_count
• FROM Borrow_Deposit
• GROUP BY branch_name
• HAVING count(customer_name)gt2
• (3) ????Aggregate_30.java(????CD??)?

79
5-5 ???(Maximum)

• SQL???? max ???????????????

80
??31??Java??Aggregate_31.java,?????Bank03.accdb?
???Borrow(??3-3-1),?? ??????????(max_amount)?

• (1) SQL??????
• SELECT max(amount) as max_amount
• FROM Borrow
•  
• (2) ????Aggregate_31.java (????CD??)?

81
??32??Java??,?????Bank03.accdb????Borrow(??3-3-1
),?? ???????????(customer_name) ?????

• (1) SQL??????
• SELECT customer_name, amount
• FROM Borrow
• WHERE amount max(amount)
•  
• (2) ????
• ????where????????,????6-2???
• 35?????

82
5-6 ???(Minimum)

• SQL???? min ???????????????

83
??33??Java??Aggregate_33.java,?????Bank03.accdb?
???Borrow(??3-3-1),?? ??????????(max_amount)?

• (1) SQL??????
• SELECT min(amount) as min_amount
• FROM Borrow
•  
• (2) ????Aggregate_33.java (????CD??)?

84
??34??Java??,?????Bank03.accdb????Borrow(??3-3-1
),?? ???????????(customer_name) ?????

• (1) SQL??????
• SELECT customer_name, amount
• FROM Borrow
• WHERE amount min(amount)
•  
• (2) ????
• ???33,????where????????,???
• ?6-2???36?????

85
???

•  
• ?????(Nested Subqueries)

86
6-1 ??

• ????(Query Clauses) ????????(Subquery Clauses),??
  ?????(Nested Subqueries),????????????(Test Set
  Membership)???????(Make Set Comparisons)???????(Te
  st for Existed Relations) ??
•  
• ???????????????,?? ?????(Nested Subqueries)
  ????????

87
6-2??????(Test Set Membership)

• ?????(Query Clauses) ????????????(Subquery
  Clauses),???????????,?????????,?????????

88
??35????32,??Java??Nest_35.java,?????Bank03.accd
b????Borrow(??3-3-1),?? ???????????(customer_name
) ?????

• (1) SQL??????(??32?????)
• SELECT customer_name, amount
• FROM Borrow
• WHERE amount max(amount)
•  
• (2) ?????where????????,?SQL??????
• SELECT customer_name, amount
• FROM Borrow
• WHERE amount (SELECT max(amount)
• FROM Borrow)
•  
• (3) ????Nest_35.java (????CD??)?

89
??36????34,??Java??Nest_36.java,?????Bank03.accd
b????Borrow(??3-3-1),?? ???????????(customer_name
) ?????

• (1) SQL??????(??34?????)
• SELECT customer_name, amount
• FROM Borrow
• WHERE amount min(amount)
•  
• (2) ?????where????????,?SQL??????
• SELECT customer_name, amount
• FROM Borrow
• WHERE amount (SELECT min(amount)
• FROM Borrow)
•  
• (3) ????Nest_36.java(????CD??)?

90
??37????22,??Java??Nest_37.java,?????Bank03.acc
db????Borrow(??3-3-1) ?Deposit(??3-3-4),??
??????????????????(customer_name)?

• (1) SQL??????(??22?????)
• (SELECT customer_name
• FROM Borrow)
• INTERSECT
• (SELECT customer_name
• FROM Deposit)
•  
• (2) ???SQL??????
• SELECT distinct customer_name
• FROM Borrow
• WHERE customer_name in (SELECT
  customer_name
• FROM Deposit)
•  
• (3) ????Nest_37.java(????CD??)?

91
??38????23,??Java??Nest_38.java,?????Bank03.acc
db????Borrow(??3-3-1) ?Deposit(??3-3-4),??
???????????????????(customer_name)?

• (1) SQL??????(??23?????)
• (SELECT customer_name
• FROM Deposit)
• MINUS
• (SELECT customer_name
• FROM Borrow)
•  
• (2) ???SQL??????
• SELECT distinct customer_name
• FROM Deposit
• WHERE customer_name not in (SELECT
  customer_name
• FROM Borrow)
•  
• (3) ????Nest_38.java(????CD??)?

92
6-3 ??????(Make Set Comparisons)

• ????????,SQL??????? some ?????? lt some?lt
  some?gt some?gt some? some ?ltgt some?
•  
• ????????,SQL??????? all ?????? lt all?lt
  all?gt all?gt all? all ?ltgt all?

93
??39????15,??Java??Nest_39.java,?????Bank03.accd
b????Deposit(??3-3-4),?? ???????(branch_name)
?????(customer_name),??????????,??????(amount)
???????

• (1) SQL??????(??15?????)
• SELECT distinct T.branch_name,
  T.customer_name
• FROM Deposit as T, Deposit as S
• WHERE T.balance gt S.balance
•  
• (2) ???SQL??????
• SELECT branch_name, customer_name
• FROM Deposit
• WHERE balancegtsome (SELECT balance
• FROM Deposit)
•  
• (3) ????Nest_39.java (????CD??)?

94
??40??Java??Nest_40.java,?????Bank03.accdb????Br
anch(??3-3-2),?? ???????(branch_name),???(assets)
???????Horseneck????????

• (1) SQL??????
• SELECT branch_name
• FROM Branch
• WHERE assets gt some (SELECT assets
• FROM Branch
• WHERE branch_city
  Horseneck )
•  
• (3) ????Nest_40.java(????CD??)?

95
??41??Java??Nest_41.java,?????Bank03.accdb????B
ranch(??3-3-2),?? ???????(branch_name),???(assets
) ?????Horseneck?????????

• (1) SQL??????
• SELECT branch_name
• FROM Branch
• WHERE assets gt all (SELECT assets
• FROM Branch
• WHERE branch_city
  Horseneck )
•  
• (3) ????Nest_41.java(????CD??)?

96
6-4??????(Test for Existed Relations)

• SQL???? exists?not exists ????(Relation)
  ??????

97
??42????37,??Java??Nest_42.java,?????Bank03.accd
b????Borrow(??3-3-1) ?Deposit(??3-3-4),??
??????????????????(customer_name)?

• (1) SQL??????(??37?????)
• SELECT distinct customer_name
• FROM Borrow
• WHERE customer_name in (SELECT
  customer_name
• FROM Deposit)

98
??42?

• (2) ???SQL??????
• SELECT distinct customer_name
• FROM Borrow
• WHERE exists(SELECT
• FROM Deposit
• WHERE Borrow.customer_name
  
• 
  Deposit.customer_name)
•  
• (3) ????Nest_42.java

99
??43????38,??Java??Nest_43.java,?????Bank03.acc
db????Borrow(??3-3-1) ?Deposit(??3-3-4),??
???????????????????(customer_name)?

• (1) SQL??????(??38?????)
• SELECT distinct customer_name
• FROM Deposit
• WHERE customer_name not in (SELECT
  customer_name
• 
  FROM Borrow)

100
??43?

• (2) ???SQL??????
• SELECT distinct customer_name
• FROM Deposit
• WHERE not exists(SELECT
• FROM
  Borrow
• WHERE
  Borrow.customer_name
• 
  Deposit.customer_name)
•  
• (3) ????Nest_43.java(????CD??)

101
6-5 ??(Exercises)

• 01??? ?????(Nested Subqueries)?
• 02??????(Nested Subqueries) ??????
• 03?????????(Test Set Membership)?
• 04?????????(Make Set Comparisons)?
• 05?????????(Test for Existed Relations)?

102
???

•  
• Java?????(Database Modifications)

103

• ????????????????,?????????,??????(Data
  Modification) ?????(Structure Modification)?
•  
• ????(Data Modification) ????????(Delete)?????(Ins
  ert)?????(Update)?
•  
• ????(Structure Modification) ????????????,????????
  ????,????????????(Join Operations),???????,??????
  ?(Join) ?????(Natureal Join)?

104
???

•  
• ????(Data Modification)

105
7-1 ??

• ??????,?????????????????????,?????????????????????
  ?????,??????????(Delete)?????(Insert)?????(Update
  )?

106
7-2????(Delete)

• SQL???? delete from ?????????,??????(1)
  ????????(Tuple) ???,?????????????(Attributes)
  ????(2) ??????,????????(Relation)
  ????????????????????????,?????????

107
??44??Java??Modification_44.java,?????Bank44.acc
db????Borrow(??3-3-1),?? ??????(branch_name)
Downtown?????

• (1) SQL??????
• DELETE FROM Borrow
• WHERE branch_name Downtown
•  
• (1) ????Modification_44.java (????CD??)?

108
??45??Java??Modification_45.java,?????Bank45.acc
db????Borrow(??3-3-1),?? ??????(amount)
??20003000?????

• (1) SQL?????1?
• DELETE FROM Borrow
• WHERE amount between 2000 and 3000
• (2) ????Modification_45.java(????CD??)?

109
??46??Java??Modification_46.java,?????Bank46.acc
db????Borrow(??3-3-1),?? ?????(amount)
????????????

• (1) SQL??????
• DELETE FROM Borrow
• WHERE amount lt (SELECT avg(amount)
• FROM
  Borrow)
•  
• (2) ????Modification_46.java(????CD??)?

110
7-3????(Insert)

• SQL???? insert into ?????????,??????(1)
  ????????(Tuple) ???(2) ??????,????????(Relation)
  ????????

111
??47??Java??Modification_47.java,?????Bank47.acc
db????Borrow(??3-3-1),?? ????? (Taipei, 101,
George, 1500)?

• (1) SQL??????
• INSERT INTO Borrow
• VALUES (Taipei, 101, George, 1500)
•  
• (2) ????Modification_47.java

112
7-4????(Update)

• ?????????,???????(Tuple) ???,???????(Update),?????
  ???????????SQL???? update set ???????

113
??48??Java??Modification_48.java,?????Bank48.acc
db????Deposit(??3-3-4),?? ?????Deposit,?????????(
balance) ??5???

• (1) SQL??????
• UPDATE Deposit
• SET balance balance1.05
•  
• (2) ????Modification_48.java (????CD??)?

114
??49??Java??Modification_49.java,?????Bank49.accd
b????Deposit(??3-3-4),?? ?????Deposit,?????????(b
alance) ??800????6??,????5???

• (1) SQL??????
• UPDATE Deposit
• SET balance balance1.06
• WHERE balance gt 800
•  
• UPDATE Deposit
• SET balance balance1.05
• WHERE balance lt 800
• (2) ????Modification_49.java (????CD??)?

115
7-5 ??(Exercises)

• 01??????????,SQL???????
• 02???????????,????????
• 03??????????,SQL???????
• 04???????????,????????
• 05??????????,SQL???????
• 06??????????,??????????
• ???????

116
???

•  
• ????(Structure Modification)

117
8-1 ??

• ???????,????????????,????????,??????,?????????????
  ????(Query Table)?????(View)?
•  
• ???????????????????,???????,????????????????????3-
  7-2?????????????,??????Java???????(View),?????????
  ?????????(Join Operations)?
•  
• ?????(Join Operations)?,????????(Join)
  ?????(Natural Join),??????(Inner Join)?????(Left
  Outer Join)?????(Right Outer Join)?????(Full
  Join)????????(Natural Inner Join)???????(Natural
  Left Outer Join)???????(Natural Right Outer
  Join)???????(Natural Full Join)?

118
8-2 ???(Views)

• ????????,??????????,???????????(Result
  Table),??????????????????,??????????
  ???(View)?SQL???? CREATE VIEW AS
  ?????(View)?
•  
• ?????????(Views) ???????(Query Tables),???????????
  ?(Join Operations)?

119
??50??Java??View_50.java,?????Bank08.accdb,??
?????Borrow?Customer?????View50,??Borrow.customer
_name Customer.customer_name?

• (1) SQL?View???
• CREATE view view50 as
• SELECT
• FROM Borrow inner join Customer
• ON Borrow.customer_name
• 
  Customer.customer_name
• (2) ?????????view_50.java (????CD??)?

120
??51????13,?????Bank08.accdb????View50?????Borro
w_Customer,??Java??Join_51.java,??
?????????????(customer_name) ???????(customer_cit
y)?

• (1) SQL??????
• SELECT Borrow.customer_name,
  Customer.customer_city
• FROM View50
•  
• (2) ????Join_51.java(????CD??)?

121
8-3 ????(Join)

• ????????????????(Attribute) ?????,?????????????,??
  ??????(Attributes) ????(Join),?????????????????,??
  ???(Natural Join) ?????????????????

122
??52????50,??Java??View_52.java,?????Bank08.accd
b,?? ??????Borrow?Customer?????View52,??Borrow.cu
stomer_name Customer.customer_name?

• (1) SQL?View???
• CREATE view view52 as
• SELECT
• FROM Borrow inner join
  Customer
• ON Borrow.customer_name
  Customer.customer_name
• (2) ?????????View_52.java(????CD??,?????view_50.ja
  va??)?

123
??53??Java??View_53.java,?????Bank08.accdb,??
???????Borrow?Customer?????View53,??Borrow.custom
er_name Customer.customer_name?

• (1) SQL?View???
• CREATE view view53 as
• SELECT
• FROM Borrow left outer join
  Customer
• ON Borrow.customer_name
  Customer.customer_name
• (2) ?????????view_53.java (????CD??)?

124
??54??Java??View_54.java,?????Bank08.accdb,??
???????Borrow?Customer?????View54,??Borrow.custom
er_name Customer.customer_name?

• (1) SQL?View???
• CREATE view view54 as
• SELECT
• FROM Borrow right outer join
  Customer
• ON Borrow.customer_name
  Customer.customer_name
• (2) ?????????view_54.java(????CD??)?

125
??55?????Bank08.accdb,?? ????????Borrow?Custom
er?????View55,??Borrow.customer_name
Customer.customer_name?

• (1) SQL?View???
• CREATE view view55 as
• SELECT
• FROM Borrow full outer join Customer
• ON Borrow.customer_name
  Customer.customer_name
• (2) ????
• ??SQL??????????,??Access????full???????,????Ac
  cess???(???11-3-3???85?????)

126
8-4 ????(Natural Join)

• ???,????????????????(Attribute)
  ?????,?????????????,???????????(Attributes)
  ????(Join),?????????????????,????????(Natural
  Join) ?????????????????

127
??56????52,??Java??View_56.java,?????Bank08.acc
db,?? ????????Borrow?Customer?????View56,??Borrow
.customer_name Customer.customer_name?

• (1) SQL?View???
• CREATE view view56 as
• SELECT
• FROM Borrow natural inner join
  Customer
• ON Borrow.customer_name
  Customer.customer_name

128
??56?

• (2) ???SQL????
• CREATE view view56 as
• SELECT branch_name, loan_number,
  Borrow.customer_name,
• amount, street,
  customer_city
• FROM Borrow inner join Customer
• ON Borrow.customer_name
  Customer.customer_name
•  
• (3) ?????????view_56.java(????CD??)?

129
??57????53,??Java??View_57.java,?????Bank08.acc
db,?? ?????????Borrow?Customer?????View57,??Borro
w.customer_name Customer.customer_name?

• (1) SQL?View???
• CREATE view view57 as
• SELECT
• FROM Borrow natural left outer join
  Customer
• ON Borrow.customer_name
  Customer.customer_name

130
??57?

• (2) ???SQL????
• CREATE view view57 as
• SELECT branch_name, loan_number,
  Borrow.customer_name,
• amount, street,
  customer_city
• FROM Borrow left outer join Customer
  
• ON Borrow.customer_name
  Customer.customer_name
•  
• (3) ?????????view_57.java(????CD??)?

131
??58????54,??Java??View_58.java,?????Bank08.acc
db,?? ?????????Borrow?Customer?????View58,??Borro
w.customer_name Customer.customer_name?

• (1) SQL?View???
• CREATE view view58 as
• SELECT
• FROM Borrow natural right outer join
  Customer
• ON Borrow.customer_name
  Customer.customer_name

132
??58?

• (2) ???SQL????
• CREATE view view58 as
• SELECT branch_name, loan_number,
  Borrow.customer_name,
• amount, street,
  customer_city
• FROM Borrow right outer join
  Customer
• ON Borrow.customer_name
  Customer.customer_name
•  
• (3) ?????????view_58.java(????CD??)?

133
??59?????Bank08.accdb,?? ??????????Borrow?Custom
er?????View59,??Borrow.customer_name
Customer.customer_name?

• (1) SQL?View???
• CREATE view view59 as
• SELECT
• FROM Borrow full outer join
  Customer
• ON Borrow.customer_name
  Customer.customer_name
• (2) ????
• ??SQL????????????,??Access????natural?
• full???????,????Access???

134
8-5 ??(Exercises)

• 01?????(Join Operations) ????????
• 02??????(View)?
• 03????????????????
• 04?????(Join) ?????(Natural Join) ??????
• 05??????(Inner Join)?
• 06???????(Left Outer Join)?
• 07???????(Right Outer Join)?
• 08????????(Full Outer Join)?
• 09???????????Access???

135
???

•  
• ????(Relational Model)
•  

136

• ?????????????????(Object-Based Logical
  Model)??????????(Record-Based Logical
  Model)??????????,?????????????????(Entity
  Relationship Model)????????????????(Record
  Relational Model)????????(Record Network
  Model)?????????(Record Hierarchical Model)?
•  
• ?????????????(Relational Algebra)???????(Tuple
  Relational Calculus)??????????,??????????,????????
  ????,????????????

137
???

•  
• ??????(Entity Relational Models)

138
9-1 ??

• ????(Entity Relational Model ??E-R)
  ??,????????????????????????????(Objects)
  ????(Entities),??????????????????(Relationship)???
  ??????,??????????,????????????????????????????????
  ??,????????????

139
9-2 ??

• ????????(E-R Data Model) ??????????(Entity
  Sets)?????(Attributes)?????(Relationship Sets)?

140
9-3 ??(Constraints)

• ????E-R??????????(Constraints Definition),????????
  ?????????????????????????????(Mapping
  Cardinalities)??????(Participation Constraints)?

141
9-4 ???(Keys)

• ??????(Entity Set) ??????(Entities)
  ??,??????????????,???????????????????(Key)
  ????????,?????,???????? ??(Unique)
  ???????(Attributes) ?????,???????????9-2-3-3,???Cu
  stomer Set?,??????(Social Security Number)
  ????,???????????,???????????????,???????

142
9-5 ?????(E-R Diagram)

• ?????(E-R Diagram) ?????????????,?????????????????
  ??,??????
•  
• (1) ??(Rectangles)?????(Entity Sets)?
• (2) ??(Ellipses)???????(Attribute Sets)?
• (3) ??(Diamonds)?????(Relationship Sets)?
• (4) ??(Lines)????????????????,
• ????????
• (5) ??(Arrow)??????????

143
9-6 ????(Design Issues)

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

144
9-7 ????

• ??????????????(Entity Relational Model ??E-R)
  ??,?????????????????,?????????????????????????????
  ??

145
9-8 ??(Exercises)

• 1???????????(E-R Data Model) ????????
• 2?????(Entity)?
• 3??????(Entity Set)?
• 4???????(Attribute)?
• 5???E-R???????(Attributes),???????
• 6???????E-R?????????? (Constraints Definition)?
• 7?E-R?????????????
• 8???????(Total Participation)?
• 9???????(Partial Participation)?
• 10?????(Entity Sets),???????
• 11????????(Super Keys)??????(Candidate Keys)???
• ??(Primary Key)?
• 12??????(E-R Diagram) ???????

146
???

•  
• ???????(Record Relational Model)

147
10-1 ??

• ???????(Record Relational Model)
  ????????????????,????????????????(Record Network
  Model)?????????(Record Hierarchical Model)?
•  
• ?????,?????????????,???????????????????????????,?
  ???????????????

148
10-2 ????????(Structure of Relational Database)

• ??????(Relational Database) ???????(Data Tables)
  ???,??????(Record) ??,?????(Columns)
  ??,??????????????????????????????????(??????
  ??????,??),???????????????????????

149
10-3????(Query Lanquage)

• ???(Database) ???????(Relations)
  ???,?????????????????,??????????,???????????Access
  ?SQL?Oracle?Mysql??,??????????????????,???????????
  ?(Procedural) ?????(Nonprocedural) ??????

150
10-4 ???????(Fundamental Relation Algebra)

• ?????(Relational Algebra) ????????(Procedural
  Query Language),??????????(Relations),????????????
  ??,????????????????
•  
• ?????????????????(Select)?????(Project)?????(Unio
  n)?????(Set Difference)??????(Cartesian Product)
  ???????????????,?????????,??????(Unary
  Operations)???????????????,?????????,??????(Binar
  y Operations)?

151
??60???10-4-1-1 Borrow???,? ??????(branch_name
) ?Perryridge?????(Tuples)?

• ????(Select Operation) ?????? (Relational
  Algebra) ?
• s branch_name Perryridge (Borrow)

152
??61????60,??Java??Select_61.java,?????Bank10.a
ccdb????Borrow(??3-3-1),?? ??????(branch_name)
?Perryridge?????(Tuples)?

• (1) SQL??????
• SELECT
• FROM Borrow
• WHERE branch_name Perryridge
•  
• (2) ????Select_61.java(????CD??)?

153
??62?????Borrow,? ????(amount)
??3000?????(Tuples)?

• (1) ???????(Relational Algebra) ?
• s amount gt 3000 (Borrow)
•  
• ?? amount ????(Domain) ????,gt ??????,3000
  ????????
•  
• (2) ??SQL????
• SELECT
• FROM Borrow
• WHERE amount gt 3000
•  
• (3) ????Select_62.java(????CD??)?

154
??63?????Borrow,? ??????(branch_name)
?Perryridge,???(amount) ??3000?????(Tuples)?

• (1) ???????(Relational Algebra) ?
• s branch_name Perryridge ? amount gt
  3000 (Borrow)
•  
• ????? and (?) ?? s branch_name Perryridge
  (Borrow) ?s amount gt 3000 (Borrow) ?
•  
• (2) ????SQL????
• SELECT
• FROM Borrow
• WHERE branch_name Perryridge and
  amount gt 3000
•  
• (3) ????Select_63.java(????CD??)?

155
??64?????Borrow,? ????branch_name?customer_nam
e???????

• (1) ???????(Relational Algebra) ?
• ? branch_name, customer_name (borrow)
•  
• ?? ? ??????,branch_name ? customer_name
  ????(Domain) ????,Borrow ????????
•  
• (2) ????SQL????
• SELECT branch_name, customer_name
• FROM Borrow
•  
• (3) ????Select_64.java(????CD??)?

156
??65?????Customer,? ??????(customer_city)
Harrison?????(customer_name)?

• (1) ???????(Relational Algebra) ?
• ? customer_name (s customer_city
  Harrison (Customer))
•  
• ?????s????,???s??????,?????,????
•  
• (2) ????SQL????
• SELECT customer_name
• FROM Customer
• WHERE customer_city Harrison
•  
• (3) ????Select_65.java(????CD??)?

157
??66?????Borrow?Deposit,? ?????????????????(cust
omer_name)?

• (1) ???????(Relational Algebra) ?
• ? customer_name (Borrow) ? ?
  customer_name (Deposit)
•  
• (2) ????SQL????
• (SELECT customer_name
• FROM Borrow)
• UNION
• (SELECT customer_name
• FROM Deposit)
•  
• (3) ????Union_66.java(????CD??)?

158
????

•  
• ??????(Advance Relation Algebra)

159
11-1 ??

• ?????????????,????????,????????(Relation Algebra)
  ?????????,???????????(Additional Relational
  Algebra)??????????(Extended Relation Algebra
  Operations)?????(Modification of the Database) ??

160
11-2 ???????(Additional Relation Algebra)

• ??????????????(Fundamental Relation
  Algebra),??????(Operators) ???????????????????,???
  ?,?????????,?????????????????,?????????,?????
•  
• ?????????????????(Set Intersection)?????(Join)???
  ??(Division) ????????????????????

161
??72?????Borrow?Deposit,? ??????????????????(cus
tomer_name)?

• (1) ???????(Relational Algebra) ?
• ? customer_name (Borrow) n ?
  customer_name (Deposit)
•  
• (2) ????SQL????
• SELECT distinct customer_name
• FROM Borrow
• WHERE customer_name in (SELECT
  customer_name
• 
  FROM Deposit)
•  
• (3) ????Intersect_72.java (????CD??)?

162
??73?????Borrow?Deposit,? ???????(branch_name)
?Perryridge??????????????(customer_name)?

• (1) ???????(Relational Algebra) ?
• (? customer_name (s branch_name
  Perryridge (Borrow)))
• n(? customer_name (s branch_name
  Perryridge (Deposit)))

163
??73?

• (2) ????SQL????
• SELECT distinct customer_name
• FROM Borrow
• WHERE branch_name Perryridge
• and customer_name in
  (SELECT customer_name
• 
  FROM Deposit
• 
  WHERE branch_name
  Perryridge)
•  
• (3) ????Intersect_73.java(????CD??)?

164
??74?????Borrow?Customer,? ?????????(Relational
Algebra) ???

• (1) ???????(Relational Algebra) ?
• s Borrow.customer_name
  Customer.customer_name (Borrow Customer)

165
??75?? ???Borrow?Customer???,?
????????????(customer_name) ???????(customer_city
)?

• (1) ???????(Relational Algebra) ?
• ? customer_name, customer_city (Borrow ?
  Customer)
• (2) ????Borrow ? Customer????SQL??(????56)
• CREATE view view75 as
• SELECT branch_name, loan_number,
  Borrow.customer_name,
• amount, street,
  customer_city
• FROM Borrow inner join Customer
• ON Borrow.customer_name
  Customer.customer_name

166
??75?

• (3) ????SQL????
• SELECT customer_name, customer_city
• FROM view75
•  
• (4) ?????????view_75.java(????CD??)?

167
??76?????Borrow?Customer???view75,?
???????(branch_name) Perryridge????????(customer_
name) ???????(customer_city)?

• (1) ???????(Relational Algebra) ?
• ? customer_name, customer_city (Borrow
  ?? Customer)
• ?? borrow.branch_name Perryridge
• ? borrow.customer_name
  customer.customer_name
•  
• (2) ????SQL????
• SELECT customer_name, customer_city
• FROM view75
• WHERE branch_name Perryridge
•  
• (3) ????Join_76.java(????CD??)?

168
??77?????Branch?Deposit,??????Brooklyn????????
??????????(customer_name)?

• (1) ???????(Relational Algebra) ?
• ? customer_name, branch_name (Deposit)
• ? branch_name (s branch_city
  Brooklyn (Branch)

169
11-3?????????(Extended Relation Algebra
Operations)

• ????,???????????????????(Additional
  Algebra),?????????????(Extended Relation Algebra
  Operations),?????????????,???????????????????????
  ??(Generalized Projection)?????(Aggregate
  Functions)????(Outer Join)?

170
??78?????Borrow?Customer???view75,?
?????????(Relational Algebra) ???

• (1) ???????(Relational Algebra) ?
• ? branch_name, customer_name,
  customer_city (Borrow?Customer)
•  
• (2) ????SQL????
• SELECT branch_name, customer_name,
  customer_city
• FROM view75

171
??79?????Borrow, ????,????????,????????1(??amo
unt-amount 1),???????????(loan_number)
?????(new_amount)

• (1) ???????(Relational Algebra) ?
• ? loan_number, (amount - amount 1) as
  new_amount (Borrow)
•  
• (2) ????SQL????
• SELECT loan_number, amount0.99 as
  new_amount
• FROM Borrow
• ??new_amount?????????1 ???????????
•  
• (3) ????Extended_79.java(????CD??)?

172
??80?????Borrow,??????????????(sum_of_amount)?

• (1) ???????(Relational Algebra) ?
• G sum (amount) (Borrow)
•  
• (2) ????SQL????
• SELECT sum(amount) as sum_of_amount
• FROM Borrow
•  
• (3) ????Aggregate_80.java(????CD??)?

173
??81?????Borrow,??????(branch_name)
?????(group_amount)?

• (1) ???????(Relational Algebra) ?
• branch_name G sum (amount) as
  group_amount (Borrow)
•  
• (2) ????SQL????
• SELECT branch_name, sum(amount) as
  group_amount
• FROM Borrow
• GROUP BY branch_name
•  
• (3) ????Aggregate_81.java(????CD??)?

174
??82?????Customer?Borrow,? ?????????(Relationa
l Algebra) ????

• (1) ???????(Relational Algebra) ?
• Customer ? Borrow
•  
• (2) ????Borrow ? Customer???(???11-2-2???75)?
• (4) ????(??????Bank11.accdb?view75)

175
??83?????Borrow?Customer,? ?????????(Relational
Algebra) ????

• (1) ???????(Relational Algebra) ?
• Borrow ?? Customer
•  
• (2) ????View?SQL???(????53)
• CREATE view view83 as
• SELECT
• FROM Borrow left outer join Customer
• ON Borrow.customer_name Customer.customer_name
•  
• (3) ????View_83.java

176
??84?????Borrow?Customer,? ?????????(Relationa
l Algebra) ????

• (1) ???????(Relational Algebra) ?
• Borrow ?? Customer
•  
• (2) ????View?SQL???
• CREATE view view84 as
• SELECT
• FROM Borrow right outer join Customer
• ON Borrow.customer_name
  Customer.customer_name
•  
• (3) ????View_84.java(????CD??)?

177
??85?????Borrow?Customer,? ?????????(Relationa
l Algebra) ????

• (1) ???????(Relational Algebra) ?
• Borrow ??? Customer
•  
• (2) ????View?SQL???
• CREATE view view85 as
• SELECT
• FROM Borrow full outer join Customer
• ON Borrow.customer_name
  Customer.customer_name

178
??85?

• (3) ??????View?SQL???
• CREATE view view85 as
• (SELECT
• FROM view83)
• UNION all
• (SELECT
• FROM view84)
•  
• (4) ????View_85.java(????CD??)?

179
11-4????????(Modification of the Database)

• ?????,?????????????????????,??????????????????????
  ??,??????????(Deletion)?????(Insertion)??????(Upd
  ating)?

180
??86?????Bank86.accdb????Borrow,???????(branch_
name) Downtown????

• (1) ???????(Relational Algebra) ?
• Borrow ? Borrow - s branch-name
  Downtown (Borrow)
•  
• (2) ????SQL????
• DELETE FROM Borrow
• WHERE branch_name Downtown
•  
• (3) ????Modification_86.java(????CD??)?

181
??87?????Bank87.accdb????Borrow,????????(amount
) ??3001????

• (1) ???????(Relational Algebra) ?
• Borrow ? Borrow - s amount lt 3001
  (Borrow)
•  
• (2) ????SQL????
• DELETE FROM Borrow
• WHERE amount lt 3001
•  
• (3) ????Modification_87.java(????CD??)?

182
??88?????Bank87.accdb????Borrow,??????
(Taipei, 101, George, 1500)

• (1) ???????(Relational Algebra) ?
• Borrow ?
• Borrow?(Taipei, 101, George, 1500)
•  
• (2) ????SQL????
• INSERT INTO Borrow
• VALUES (Taipei, 101, George, 1500)
•  
• (3) ????