????? Database Managent Ex.1-2 ??????

1
?????Database ManagentEx.1-2 ??????

• ?????
• ??49814201
• ?????
• ??????? ??
• ??2013/03/25

2
??
3
??????

• P. 2-12????

4
?? Banking Database ???
??????Banking_Database???? ???? CREATE
DATABASE ?????
5
?? P. 2-12 ??
???? CREATE TABLE ???? ( ????1 ????(????),
????2 ????(????) PRIMARY KEY(????) )
CREATE TABLE branch ( branch_name char((16),
branch_city char(16) PRIMARY KEY(branch_name) )
??PRIMARY KEY ?????????? ????
6
?? P. 2-12 ??
7
????
?????? ???? INSERT INTO ???? (??1,
??2) VALUES (??1, ??2), (??3, ??4)
???branch?????
8
?????????
9
P. 2-43????

• CASE 1????

10
P. 2-43???? CASE 1
Find the name of all customers who have a loan at
the Perryridge branch
???? SELECT ??1, ??2 FROM ????? WHERE ???
11
P. 2-43???? CASE 1
Customer_name
Adams
Hayes
12
P. 2-43????

• CASE 2????

13
P. 2-43???? CASE 2 First Step
borroewr loan
SELECT FROM borrower, loan ??borrower
loan???(Cartesian-Product)
14
First Step Result
8756 tuples
15
P. 2-43???? CASE 2 Second Step
sborrow.loan_number loan.loan_number(borroewr
loan)
SELECT FROM borrower, loan WHERE
borrow.loan_number loan.loan_number ??borrower
loan??,loan_number?????
16
Second Step Result
customer_name loan_number loan_number branch_name amount
Adams L_16 L_16 Perryridge 1300
Hayes L_15 L_15 Perryridge 1500
Jackson L_14 L_14 Downtown 1500
Jones L_17 L_17 Downtown 1000
Smith L_11 L_11 Round Hill 900
Smith L_23 L_23 Redwood 2000
Williams L_17 L_17 Downtown 1000
17
P. 2-43???? CASE 2 Third Step
sbranch_name perryridge (sborrow.loan_number
loan.loan_number(borroewr loan))
SELECT FROM borrower, loan WHERE
borrow.loan_number loan.loan_number
branch_name Perrtridge ??borrower
loan??,loan_number???branch_name Perryridge???
18
Third Step Result
customer_name loan_number loan_number branch_name amount
Adams L_16 L_16 Perryridge 1300
Hayes L_15 L_15 Perryridge 1500
19
P. 2-43???? CASE 2 Final Step
?customer_name(sbranch_name perryridge (sborrow
.loan_number loan.loan_number(borroewr loan)))
SELECT customer_name FROM borrower,
loan WHERE borrow.loan_number
loan.loan_number branch_name
Perrtridge ?borrower loan???loan_number???br
anch_name Perryridge???,?????customer_name??
20
Final Result
customer_name
Adams
Hayes
21
P. 2-45????

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

22
?????!
????? 0.0006 ?!
23
?????????
Rand() ??????01?????
???loan???1000?????
24
?????????
???borrower???1000?????
25
????????????
1060 Duplicate column name loan_number
??? SELECT FORM ( SELECT FROM ??1)NewName
26
What is better?
27
Query 1(????????) Step 1
???? 0.1265 ?
28
Query 1(????????) Step 2
???? 0.1073 ?
29
Query 1(????????) Final Step
???? 0.1086 ?
SELECT customer_name FROM ( SELECT FROM
borrower, loan WHERE borrower.borrower_loa
n_number loan.loan_number )step1 WHERE
branch_name "Perryridge"
30
Query 1 Average Access Time
0.1086
0.1086 0.1066
0.1086 0.10660.1064
(0.1086 0.10660.1064) / 3 0.1072 (sec)
31
Query 2(????????) Step 1
???? 0.0011 ?
32
Query 2(????????) Step 2
???? 0.0014 ?
33
Query 2(????????) Step 3
???? 0.0033 ?
SELECT FROM ( SELECT FROM loan WHERE
branch_name Perryridge )step1, borrower
WHERE step1.loan_number borrower.loan_number
34
Query 2(????????) Final Step
???? 0.0038 ?
SELECT customer_name FROM ( SELECT FROM
loan WHERE branch_name Perryridge )step1,
borrower WHERE step1.loan_number
borrower.borrower_loan_number
35
Query 2 Average Access Time
0.0038
0.0038 0.0024
0.0038 0.00240.0055
(0.0038 0.00240.0055) / 3 0.0039 (sec)
36
Answer Query 2 is better!
?
37
P. 2-12????

• ????

38
????
39
????LEFT JOIN ON
40
????LEFT JOIN ON
41
The End.

• Thank you!