Xem bản tiếng việt đầy đủ tại đây : http://thethao60s.com/index/1308/02062007.aspx

Kien truc du lieu Rational DA va DB2 9: Xay dung mot lenh SQL

Ban da tung quen thuoc voi Database Explorer? Du da, hay chua thi cung xin moi doc bai sau de biet ve mot so chuc nang, thanh phan cua kieu kien truc du lieu nay, cu the hon la ve kha nang xay dung cac lenh SQL gap trong co so du lieu (CSDL) cua ban.

Xay dung mot lenh SQL

Rational DA (kien truc du lieu huu ty) tich hop san mot cong cu ho tro tao cac lenh SQL. Ban co the truy cap no tu nhieu nguon khac nhau cua Rational DA nhu su dung bang ao Database Explorer…

Hay xem xay dung truy van ben duoi su dung Rational DA nhu the nao:

SELECT DEPARTMENT.LOCATION, EMPLOYEE.EMPNO, EMPLOYEE.FIRSTNME, EMPLOYEE.LASTNAME, EMPLOYEE.PHONENO FROM DEPARTMENT, EMPLOYEE WHERE DEPARTMENT.DEPTNO = EMPLOYEE.WORKDEPT AND EMPLOYEE.SEX = 'F' ORDER BY LASTNAME DESC, FIRSTNME DESC

Truy van nay tra ra ket qua la tat ca nhan vien nu co trong cong ty.

De xay dung mot lenh SQL tu bang ao Database Explorer, thuc hien cac buoc sau:

1. Kich phai chuot len doi tuong ket noi CSDL va chon New SQL Statement.

Ban co the chon tuy chon nay tu mot doi tuong ket noi CSDL lien mach hoac tach roi. Neu chon New SQL Statement tu doi tuong roi, no se tu dong tao mot ket noi toi CSDL. Sau do kich phai chuot len doi tuong CSDL va chon tuy chon.

2. Trong cua so New SQL Statement, go ten lenh o o Statement name va chon kieu truy van trong danh sach Statement template. Vi du, dat ten cho Statement name la FemaleEmployees, Statement template la SELECT va dat SQL builder cho o Edit using, sau do bam OK. Trinh tao SQL se duoc mo ra voi mau lenh la SELECT.

Trong danh sach Statement template, Rational DA cung cap cac mau goc cho tat ca kieu truy van, chang han nhu: INSERT, UPDATE, DELETE, FULLSELECT, va WITH (cho cac dinh nghia bieu thuc bang chung (Common Table Expression)).

Tuy chon SQL builder vien dan mot cong cu tao lenh SQL ho tro do hoa, cho phep ban xay dung lenh SQL voi thao tac keo-tha, cac thay doi thuoc tinh tro-va-kich… don gian (ban se thay o nhung buoc phia duoi cua bai).

Trinh tao lenh SQL nhu hinh ben duoi:

Tuy chon SQL editor chi mo trinh soan thao lenh SQL (la mot phan cua trinh tao SQL, noi chua cac lenh SQL trong SQL builder). Trinh soan thao SQL cung cap mot so ma tro giup thong thuong nhu to mau cu phap va tra tim gian do tu dong, nhung khong cung cap cac chuc nang keo-tha gan voi trinh tao SQL de xay dung cac lenh SQL. Chung ta se quan tam den trinh soan thao SQL trong mot bai gan day.

3. Kich phai chuot len o Tables va chon Add Table. Cua so Add Table mo ra. Chung ta se lay vi du voi hai bang EMPLOYEE va DEPARTMENT dat trong co so du lieu SAMPLE.

Ban co the thay nhu o hinh tren, gian do du lieu HR va PAULZ duoc hien thi trong cua so Add Table.

De y ban cung se thay co tuy chon tao bi danh cho bang trong moi truy van cung duoc hien thi. Bi danh bang giup viec tham chieu toi no trong lenh SQL de dang hon. Khi su dung bi danh de the hien bang (hoac bang ao), Rational DA se dieu chinh cu phap SQL va giao dien tuong ung nhu hinh ben duoi:

Khi them ca hai bang vao vung lam viec cua trinh tao SQL , workspace cua Rational DA se co dang:

Rational DA ho tro cac thao tac keo-tha tu bang ao Database Explorer toi trinh tao SQL builder. Don gian chi can kich chuot va keo bang ban muon them vao khung Tables, sau do nha nut chuot de dua bang vao vi tri mong muon.

Ho tro keo-tha nay thay the cho tuy chon Add Table mo ta o buoc tren; qua trinh chon bang theo kieu keo-tha cung cap hinh thuc xay dung truy van tu nhien hon:

4. Kich phai chuot len o Tables, chon Create Join de tao lien ket nhu minh hoa o hinh duoi:

Vi du, tao lien ket giua cac bang DEPARTMENT va EMPLOYEE, dung cot DEPARTMENT.DEPTNO va EMPLOYEE.WORKDEPT nhu hinh tren.

Neu chon hai cot ket noi khong tuong thich, trinh tao SQL builder se khong cho phep lien ket hai bang vi se xay ra loi trong thoi gian chay. Kieu loi co dang:

Ban co the dung hinh thuc keo-tha de tao lien ket truc tiep tu o Tables bang cach di chuot tren cot ket noi o mot bang, sau do nha chuot tren cot ket noi o bang dich:

Nhu tren hinh minh hoa, khi di chuot toi mot cot, kieu du lieu cua cot ben duoi duoc dua ra trong phan giai thich ben canh. Dieu nay lam phong phu them kha nang ve thoi gian thiet ke va toi thieu hoa kha nang xay ra loi lien ket. Neu tao lien ket giua hai kieu du lieu khong tuong thich, Rational DA se thay doi bieu tuong chuot thanh bo chi bao loi va ngung thao tac.

5. Kich vao o vuong tuong ung ben canh cac cot neu ban muon co chung trong tap hop ket qua cua lenh SQL tu ca hai bang. Hay dam bao rang trinh tao SQL co dang tuong tu nhu vi du bang cach chon cac cot sau: DEPARTMENT.DEPTNAME, DEPARTMENT.LOCATION, EMPLOYEE.EMPNO, EMPLOYEE.FIRSTNME, EMPLOYEE.LASTNAME, va EMPLOYEE.PHONENO.

Chu y xem lenh SQL ma ban dang xay dung trong o SQL Source thay doi tich cuc nhu the nao khi thuc hien mot so thao tac khac nhau nhu them hay loai bo bang, them hay gioi han cac cot, nhan dang thuoc tinh lien ket…

Cung chu y rang Rational DA cung cap cho ban phan giai thich truc quan boi cac quan he thuong mai duoc ma hoa ben trong gian do du lieu lien quan den cac cot cua moi bang. Vi du, nhu o hinh tren, ban co the thay cac cot DEPTNO va EMPNO la truong khoa chinh boi dau hieu khoang trong o dang sau: . Cac cot MGRNO va ADMRDEPT cua bang DEPARTMENT cung nhu cot WORKDEPT cua bang EMPLOYEE la truong khoa ngoai lien ket toi bang khac cung boi dau hieu khoang trong dang sau: .

Ban co the dung the Columns o cuoi trinh tao SQL de them cot vao lenh SQL cua minh, don gian bang cach kich vao danh sach Column va chon mot hay nhieu cot muon them vao.

6. Su dung the Columns, sap xep thu tu thong qua cac cot Sort Type (kieu sap xep) va Sort Order (trinh tu sap xep). Kich chuot len chung va chon tuy chon tuong ung. Vi du, the Columns co dang:

Nhu hinh tren ban co the thay, phan ket qua cua lenh SQL vi du dau tien se duoc sap xep theo truong LASTNAME (ho) voi trinh tu giam dan tu Z ve A (ban co the chon tang dan tu A -> Z neu muon); sau do tiep tuc duoc sap xep theo truong FIRSTNAME (ten), theo mo ta trong cot Sort Order, cung voi trinh tu giam dan nhu mo ta o cot Sort Type.

7. Su dung thuoc tinh dieu kien cho lenh SQL (vi du menh de WHERE) bang cach chon the Conditions va xay dung dieu kien khi kich vao tung cot tuong ung:

Vi du, de nhap gia tri cho truong Value don gian chi can kich vao no va nhap gia tri vao thay vi xay dung bieu thuc.

Chu y: Dung quen rang cot SEX (gioi tinh) cua bang EMPLOYEE co kieu CHARACTER (ky tu). Do do, gia tri ban mo ta trong cot Value la chu hoa va phai duoc dat trong cap nhay don (‘’). Do la ly do vi sao toi dung ky tu ‘F’ o hinh tren.

8. Ban co the su dung cac tuy chon nhom neu muon them no vao lenh SQL thong qua the Groups va Group Conditions o cuoi trinh tao SQL.

Buoc nay co the duoc bo qua neu thay khong can thiet. Nhung ban nen tim hieu qua ve no phong truong hop lenh SQL ma ban dang xay dung yeu cau.

9. An Ctrl+S de ghi lai truy van. Truy van se co dang tuong tu nhu hinh ben duoi:

10. Kiem tra truy van vua xay dung bang cach bam Run->Run SQL hoac bam vao bieu tuong tuong ung nhu hinh ben duoi:

Phan ket qua tra ra va tap hop ket qua di kem duoc hien thi trong the Data Output o phia duoi vung lam viec cua Rational DA:

Giam toi thieu cac loi thiet ke thoi gian voi Rational DA

Voi mot Database Explorer, ban co the thiet lap ket noi CSDL lam viec trong mo hinh offline. No dem lai tac dong hieu qua cho qua trinh tao cache schema (bo nho luu tru tam thoi gian do du lieu) cho cac doi tuong CSDL. Tuy chon nay cho phep tao mo hinh, lenh SQL va nhieu hon the ma khong can dung den mot ket noi CSDL. No cung tang cuong khai thac them loi ich voi bo tro giup thoi gian thiet ke ben trong Rational DA nhu cac ho tro ma va bo phan tich cu phap lenh SQL. Thanh phan nay cung giup giam mot luong lon cac loi ma hoa ma thong thuong ban khong phat hien duoc cho den khi trien khai tren thuc te.

Vi du nhu khi ghi mot lenh SQL, Rational DA se canh bao ban rang lenh SQL that bai (cho du cu phap dung) khi co mot loi nao do xay ra nhu thay doi tu PAULZ.DEPARTMENT.DEPTNO thanh PAULZ.DEPARTMENT.DEPTNUMB, khong dung voi co so du lieu va an Ctrl+S.

Rational DA ngay lap tuc se the hien mot thong bao loi cho ban, tuong tu nhu:

(Loi ve xac dinh tinh hop le: Khong the tim thay cot PAULZ.DEPARTMENT.DEPTNUMB . Mot so chuc nang cua trinh tao SQL da ngung hoat dong. De cho phep cac chuc nang nay hoat dong, thay doi cu phap va ghi lai lenh.

De phuc hoi cac thay doi moi thuc hien gan day, chon “Revert to Last Correct Source” tren menu SQL ).

Lai mot lan nua ban co the thay co che cache schema giup ban tiet kiem duoc thoi gian quy bau cua minh nhu the nao khi thiet ke cac lenh SQL. No duoc dung de canh bao nhung lenh co van de trong thoi gian thiet ke thay vi o giai doan kiem tra hay trong thoi gian chay.

O hinh minh hoa ben duoi, ban co the thay Rational DA cung cap mot so nguyen nhan co the cho lenh SQL xuat hien loi. O ben trai hinh, phan phia duoi trinh tao SQL co mau xam de chi ra rang co loi xay ra. (Ky hieu r duoc dat de hien thi truy van nay khong chinh xac). Phia ben phai hinh (ky hieu a) la lenh SQL duoc viet chinh xac. Chu y, cac phan cua trinh tao SQL khong duoc danh bong.

Rational DA con cung cap mot so cong cu khac giup giam toi thieu loi thoi gian thiet ke, nhung de tim hieu chi tiet ve chung thi vuot qua gioi han cua bai. Hy vong chung ta se gap lai chu de nay vao mot ngay gan nhat.

Khong the ghi duoc lenh SQL?

Doi khi ban khong the ghi duoc lenh SQL moi tao vi khong co cho de ghi no. Day la mot trong nhung diem han che cua qua trinh xay dung lenh SQL tu bang ao Database Explorer. Neu muon ghi lenh SQL dung lai cho tuong lai, ban can tao no nhu mot phan cua mot du an. (Tat nhien luon luon co the dung thao tac copy/paste de cat/dan lenh SQL vao mot du an Rational DA hoac vao mot file rieng).

Neu de y ky hon o cac hinh minh hoa lenh SQL trong bai, ban se thay co mot dau hoa thi (*) ben canh ten truy van de chi ra rang do khong phai la truy van mai mai.

Neu muon tao truy van ghi truc tiep ben trong Rational DA, ban can tao mot du an Data Design (thiet ke du lieu) hoac Data Development (phat trien du lieu).

Thuc hien cac buoc sau de tao du an Data Development va su dung cac buoc da noi chi tiet o tren de tao lenh SQL. Thuc hien nhu sau:

1. Vao File->New->Project, chon Data Development Project tu cua so New Project, sau do kich Next. (Ban co the chon Show All Wizards de xem du an nay).

2. Nhap ten cho du an Data Development (vi du MyFirstRDAProject) va bam Next.

Ban co the dung cua so nay de thiet lap cac tuy chon khac nhu gian do du lieu mac dinh cho du an va tuy chon ten nhan dang gian do trong cac lenh duoc tao. Nhung trong pham vi cua bai nay, chung ta se bo qua buoc nay.

3. Mo ta ket noi CSDL mac dinh muon dung cho du an (trong truong hop nay la DB2SAMPLE) va kich Next.

O day, ket noi co so du lieu DB2SAMPLE duoc chon. Neu chua co mot ket noi toi CSDL dich, ban co the vao Create a new connection va bam Next. (Cac khung trong Wizard Add Database Connection se xuat hien. Vi the chi can thuc hien theo cac buoc da chi dan).

4. Mo ta duong dan mac dinh cho JDK va kich Finish. Mac dinh, o nay duoc dien san duong dan JavaTM trong Database DB2 9:

De y o buoc 3, nut Finish hoat dong sau khi ban chon doi tuong ket noi CSDL dich. Dieu nay xay ra la do truong JDK home duoc dien mac dinh boi Rational DA. Neu truong nay khong duoc dien, nut Finish se khong hoat dong. Va sau nay, ban chi can kich vao Finish la du.

Mot du an Data Development duoc tao trong bang ao Data Project, thuong nam tren Database Explorer trong vung lam viec cua Rational DA (ban co the dieu chinh vi tri thu tu nay bat cu luc nao).

5. Mo rong du an moi, chon SQL Scripts->New->SQL Statement.

6. Trong cua so New SQL Statement, chon du an ban muon tao lenh SQL moi va bam Next.

Mac dinh, du an tao tu Wizard nay duoc dung de luu tru lenh SQL moi. Ban co the chon tu cac du an khac ton tai trong Rational DA bang cach kich vao Project hoac tao moi du an bang cach kich New.

7. Theo cac buoc da duoc gioi thieu o tren de tao lai lenh FEMALEEMPLOYEES (tim ra nhan vien nu trong cong ty). Khi ghi va kiem tra lenh SQL nay, bang ao Rational DA Data Project Explorer co dang:

Ban co the thay rang lenh SQL FemaleEmployees bay gio co the duoc ghi (va truy van) tu ben trong du an Data Development moi duoc tao.

Tom tat

Trong bai nay, toi da chi ra cho cac ban cach dung mot ket noi CSDL de tao moi lenh SQL va tat ca nhung ho tro phat trien Rational DA cung cap cho qua trinh nay. Ben canh do con la cach dam bao co the truy van duoc lenh SQL ve sau bang cach dung mot du an Data Development. (Chu y: Ban cung co the dung du an Data Design thay the cho Data Development). Chung ta se gap lai nhau trong phan tiep theo cua bai nay voi nhung gi co the lam duoc o Rational DA voi mot lenh SQL da duoc ghi cung nhu mot so chuc nang khac cua trinh soan thao SQL chua duoc gioi thieu trong bai.