Gt Tinhocungdung

TRNG I HC NHA TRANG

KHOA CNG NGH THNG TIN

B MN H THNG THNG TIN

(Lu hnh ni b)

Nha Trang, thng 04 nm 2011

BI GING

TIN HC NG DNG DNH CHO SINH VIN CC NGNH KINH T

TRNG I HC NHA TRANG



(Lu hnh ni b)

BI GING

TIN HC NG DNG DNH CHO SINH VIN CC NGNH KINH T

i

MC LC

LI M U .................................................................................................................................... v

Phn I NHP MN C S D LIU ...................................................................

CHNG . TNG QUAN V C S D LIU .......................................................................

1.1. Cc khi nim .............................................................................................................

1.2. V d ...........................................................................................................................

1.3. M hnh d liu ..........................................................................................................

1.4. Bi tp ........................................................................................................................

CHNG 2. M HNH D LIU QUAN H ..............................................................................

2.1. Thuc tnh, b, lc quan h v quan h ..............................................................

2.2. Lc c s d liu v c s d liu ......................................................................

2.3. Rng buc ton vn ..................................................................................................

2.3.1. Rng buc Kha ca lc quan h .............................................................

Mi lin h gia cc quan h. Rng buc Kha ngoi ....................................

2.3.3. Cc rng buc khc ..........................................................................................

2.4. Cp nht d liu v vi phm rng buc ton vn .....................................................

2.4.1. Thm (Insert, Append) ......................................................................................

2.4.2. Xa (Delete) .....................................................................................................

2.4.3. Sa (Update) ....................................................................................................

2.5. Bi tp ......................................................................................................................

CHNG . I S QUAN H ..................................................................................................

3.1. Biu thc c bn ......................................................................................................

3.2. Php chn (SELECT) ............................................................................................... 2

3.3. Php chiu (PROJECT)............................................................................................ 2

3.4. Php tch Descartes (CARTESIAN PRODUCT) .................................................... 22

3.5. Php kt ni bng (EQUI JOIN) .............................................................................. 2

3.6. Php nhm d liu (GROUP) .................................................................................. 2

3.7. Bi tp ...................................................................................................................... 2

Phn II H QUN TR C S D LIU MICROSOFT ACCESS ................

CHNG 4. GII THIU H QUN TR C S D LIU MICROSOFT ACCESS .......

4.1. Gii thiu Microsoft Access ....................................................................................

4.2. Khi ng MS Access .............................................................................................

4.3. To c s d liu mi ..............................................................................................

ii

4.4. M mt CSDL c trong my tnh ........................................................................

. . Cc i tng chnh ca mt CSDL Access ............................................................

4.6. Cc ton t s dng trong MS Access .....................................................................

4.7. Mt s hm s dng trong MS Access.....................................................................

4.8. Bi tp ......................................................................................................................

CHNG 5. BNG (TABLE) ......................................................................................................

5.1. Cc khi nim ...........................................................................................................

5.1.1. Bng ..................................................................................................................

Trng d liu (field) .......................................................................................

5.1.3. Bn ghi (record) ...............................................................................................

5.1.4. Kha chnh (primary key) .................................................................................

5.1.5. Mi lin h gia cc bng (relationship) .........................................................

5.2. Xy dng cu trc bng ............................................................................................

5.2.1. S dng ch Design View to bng ........................................................

5.2.2. To kha chnh .................................................................................................

Lu bng ...........................................................................................................

5.2.4. Hiu chnh bng................................................................................................

5.3. Thit lp mi quan h gia cc bng ........................................................................

5.4. Cp nht bng ...........................................................................................................

5.4.1. Xem v nhp d liu .........................................................................................

5.4.2. Nhp d liu s dng Lookup ...........................................................................

5.4.3. Mt s li c th xy ra khi nhp d liu .........................................................

5.4.4. Xa bn ghi .......................................................................................................

5.4.5. Sp xp d liu .................................................................................................

.6. Lc d liu ........................................................................................................

CHNG 6. TRUY VN (QUERY) ............................................................................................ 5

6.1. Cc khi nim ...........................................................................................................

.2. Query truy vn ..........................................................................................................

6.2.1. Select Query (truy vn vi php chn, chiu, kt ni) ......................................

6.2.2. Group by Select Query (truy vn vi php nhm) ............................................

6.2.3. Crosstab Query .................................................................................................

6.3. Query thm, xa, sa ................................................................................................ 2

6.3.1. Make Table Query .........................................................................................

6.3.2. Update Query ...................................................................................................

6.3.3. Delete Query .....................................................................................................

6.3.4. Append Query ...................................................................................................

6.4. Truy vn c tham s .................................................................................................

iii

CHNG . BIU MU (FORM) ............................................................................................... 6

7.1. Khi nim Form .......................................................................................................

7.2. S dng Form Wizard to Form ..........................................................................

7.3. S dng Design View to Form ..........................................................................

7.4. Main-Sub Form ........................................................................................................

7.5. S dng nt lnh (Command Button) ......................................................................

CHNG . BO CO (REPORT) .............................................................................................

8.1. Khi nim v Report ................................................................................................

8.2. S dng Report Wizard to bo co ....................................................................

. . To bo co c phn nhm.......................................................................................

CHNG . BI TP THC HNH ..........................................................................................

9.1. Bi tp 1 Qun l Sinh vin ..................................................................................

9.2. Bi tp 2 Qun l Bn hng ................................................................................

9.3. Bi tp 3 Qun l Th vin .................................................................................

9.4. Bi tp 4 Qun l D n ......................................................................................

TI LIU THAM KHO ............................................................................................................

v

LI M U

Ngy nay cc h thng thng tin ng mt vai tr quan trng trong hot ng

ca doanh nghip. Cc h thng thng tin nh k ton, khch hng, thng tin v sn

xut, phn phi hng ha gip qun tr doanh nghip mt cch hiu qu, a ra

cc quyt nh hp l. S pht trin ca cng ngh thng tin lm cho vic xy dng

v vn hnh cc h thng c s d liu trn c d dng, tin li.

Trong phm vi ca mn hc Tin hc ng dng dnh cho sinh vin cc ngnh

kinh t, chng ti quyt nh chn ch c s d liu ging dy, nhm cung

cp cc kin thc v k nng cn bn, lm tin cho ngi hc tip cn vi vic

xy dng hay s dng cc h thng thng tin sau ny. Ni dung ca mn hc gm

hai phn chnh:

Phn I: Nhp mn C s d liu. Phn ny cung cp cc kin thc l thuyt

v c s d liu, c th l m hnh t chc d liu quan h v cc php ton x l

s liu.

Phn II: H qun tr C s d liu Microsoft Access.Trong phn ny, ngi

hc ng dng cc kin thc trong phn I cho mt phn mm c th l Microsoft

Access, rn luyn cc k nng v thit lp c s d liu, thc hin cc cu truy vn,

xy dng cc biu mu nhp liu, cc bo co

Vi thi lng l 2 tn ch cho c l thuyt v thc hnh, chng ti c gng

cung cp nhng kin thc c bn nht nn cng khng th trnh nhng thiu st.

Chng ti mong nhn c nhng kin ng gp mn hc c hon thin

hn.



Phn I

NHP MN C S D LIU

CHNG . TNG QUAN V C S D LIU

1.1. Cc khi nim

1) D liu (Data)

D liu l cc s kin c th ghi nhn li c v c mt ng ngha no . V d:

Tn ca mt ngi: 'Nguyn Vn Tun'

S in thoi: ' . 2 . '

a ch: '120 Ng Gia T'

Ngy sinh: 21/12/1990

2) C s d liu (Database)

C s d liu l mt tp hp cc d liu c lin quan vi nhau, c lu tr trn cc

thit b tr tin, tha mn yu cu s dng cho nhiu ngi dng khc nhau v cho nhng

mc ch khc nhau.

V d: Thng tin ca sinh vin Trng i hc Nha Trang nh h tn, a ch, qu

qun, gii tnh, ngy sinh c lu tr trong s sch (trc y) hoc trong h thng my

tnh (hin nay). Cc thng tin ny hnh thnh nn mt c s d liu v sinh vin v c

cc phng ban ca trng s dng nhm phc v cho cc nhu cu qun l khc nhau.

Cc tnh cht ca c s d liu:

Mt c s d liu i din cho mt vn no trong th gii thc. Chng hn

nh c s d liu v sinh vin ca mt trng i hc, c s d liu v hng ha

v khch hng ca mt cng ty Nhng thay i trong th gii thc s c

phn nh li trong c s d liu.

Cc d liu trong c s d liu phi lin quan vi nhau theo mt ngha logic no

. Tp hp cc d liu ngu nhin khng th c xem l mt c s d liu

C s d liu p ng c cc yu cu khc nhau ca nhiu ngi s dng.

3) H qun tr c s d liu (Database Management System - DBMS)

C s d liu c th c to ra v qun l th cng trn s sch hoc trn my tnh.

y ch quan tm n c s d liu trn my tnh.

Mt h qun tr c s d liu l mt h thng cc phn mm my tnh cho php

ngi s dng c th to v qun l mt c s d liu. H qun tr CSDL c cc chc nng

chnh sau:

nh ngha d liu: to cu trc lu tr d liu ty theo mc ch ca c s d

liu.

Thao tc d liu: cho php ngi dng c th truy vn cc d liu trong c s d

liu, hay cp nht cc thay i trong th gii thc vo c s d liu.

Chia s: cho php nhiu ngi dng c th khai thc d liu mt cch ng thi.

Bo v h thng: nhm bo m cho c s d liu lun an ton trong trng hp

xy ra hng hc phn cng cng nh phn mm my tnh. Bo v khi cc hnh

vi ph hoi t bn ngoi.

Hnh I- . M t n gin mt h thng c s d liu

Mt s h qun tr c s d liu ph bin hin nay:

Microsoft Access: thch hp cho ngi dng c nhn, cc doanh nghip nh.

Oracle, DB2, SQL Server: dnh cho cc doanh nghip c cc h thng thng tin ln.

1.2. V d

Chng ta hy xem xt mt v d v c s d liu BanHang nhm qun l cc thng

tin v khch hng, cc mt hng, cc ln bn hng cng vi s lng hng bn ca mt

cng ty. Hnh 1-2 bn di th hin cu trc v mt vi d liu v d cho c s d liu

trn. C s d liu c t chc thnh ba h s. Mi h s lu tr d liu v cc i tng

c kiu ging nhau. H s KhachHang lu tr thng tin ca cc khch hng ca cng ty.

H s MatHang lu tr thng tin v cc mt hng m cng ty c bn. H s BanHang ghi

nhn cc ln bn hng.

Qu trnh thit lp c s d liu trn gm hai bc. u tin l nh ngha cu trc

cho cc h s. H s KhachHang c nh ngha gm c hai thng tin l tn khch hng

(HoTen) v a ch (DiaChi). H s MatHang c cc thng tin l m hng, tn hng, n v

tnh v n gi. Tng t nh vy cho h s BanHang. Ch rng cc h s c th lin

quan vi nhau, chng hn nh trong h s BanHang c thng tin v HoTenKH, cho bit

ha n bn cho khch hng no, hoc c thng tin v MaMH, cho bit mt hng no

c bn vi s lng bao nhiu.

H Qun tr

C s d liu

Ngi dng

Chng trnh ng dng

Phn mm qun l lu tr d

liu

Phn mm x l thao tc trn

d liu

Cu trc

c s d

C s

d liu

Hnh I- . C s d liu BanHang

Bc th hai l thao tc trn c s d liu, lin quan n cp nht v truy vn. Cp

nht l phn nh s thay i trong th gii thc vo c s d liu chng hn nh: ghi nhn

thm mt mt hng mi l S c la sa vi n gi . mt ti. Hoc: bn 50 hp

Bnh Choco-Pie cho khch hng L Cng vo ngy 20/03/2011. Mt v d lin quan

n truy vn l: tnh doanh thu ngy 2/2 2 .

1.3. M hnh d liu

m bo tnh ng n, vic nh ngha cu trc ca mt c s d liu phi tun theo

mt m hnh d liu no . Bn di l mt s m hnh c s dng t trc n nay:

M hnh d liu mng (Network Data Model)

M hnh d liu phn cp (Hierachical Data Model)

M hnh d liu quan h (Relational Data Model)

M hnh d liu hng i tng (Object-Oriented Model)

Vic cp chi tit n cc m hnh trn nm ngoi phm vi ca ti liu ny. y

ch nhn mnh rng M hnh d liu quan h l mt m hnh n gin, c nn tng ton

hc cht ch. Do m hnh ny c s dng ph bin rng ri trn khp th gii trong

cc h qun tr c s d liu thng mi ngy nay nh Access, Oracle, DB2, SQL

Server cng l m hnh c trnh by trong cc chng tip theo.

1.4. Bi tp

1) Mt trung tm Anh ng cn t chc mt c s d liu nhm qun l hot ng ging

dy ca trung tm. Cc thng tin cn qun l nh sau:

Danh sch cc hc vin ng k hc.

Danh sch cc lp hc c th, mn hc ca lp (TOEIC, IELTS, Headway)

ngy khai ging lp.

Hc vin no ng k lp no, cui kha thi t im bao nhiu.

Yu cu: Hy nh ngha cu trc cho c s d liu ni trn v cung cp mt s d

liu mu nh trong v d Hnh I- .

KhachHang HoTenKH DiaChi

Nguyn Trang 12 Hng Bng

L Cng 60 Trn Ph

Mai Ha 22 Bch ng

MatHang MaMH TenMH DonViTinh DonGia

S03 Sa chua Vinamilk Lc 4.000

B02 Bnh Choco-Pie Hp 15.000

K01 Kem Mote-Rosa K 20.000

BanHang STT NgayBan HoTenKH MaMH SoLuong

1 12/2/2010 Nguyn Trang S03 20

2 12/2/2010 L Cng B02 30

3 17/2/2010 Mai Nguyn K01 100

4 25/3/2010 Nguyn Trang K01 10

2) Mt cng ty sn xut bnh ko cn t chc mt c s d liu nhm qun l nhn s

ca cng ty. Cc thng tin cn qun l nh sau:

Danh sch cc phng ban v cc nhn vin theo tng phng ban. D liu v

nhn vin bao gm h tn, a ch, nm sinh, h s lng ca nhn vin .

Bng lng chi tr hng thng cho tng nhn vin, bit rng lng c tnh

theo ngy cng lm vic ca nhn vin trong thng:

Lng = . x S ngy cng x H s lng.

Yu cu: Hy nh ngha cu trc cho c s d liu ni trn v cung cp mt s d

liu mu nh trong v d Hnh I- .

CHNG . M HNH D LIU QUAN H

Trong m hnh d liu quan h, mt c s d liu l mt tp hp cc quan h. Mi

quan h c th c xem l mt bng cc gi tr.

KhachHang MaKH HoTen DiaChi

A001 Nguyn Trang 12 Hng Bng

B002 L Cng 60 Trn Ph

C003 Mai Nguyn 22 Bch ng

Mi dng trong bng th hin mt i tng hay mt s kin trong th gii thc.

Mi dng gm nhiu gi tr c lin quan vi nhau. Tn ct gip th hin ngha ca cc

gi tr trong mt dng. V d bng trn c gi l bng KhachHang v mi dng cho bit

thng tin v mt i tng khch hng c th. Cc tn ct MaKH, HoTen, DiaChi cho bit

ngha ca tng gi tr trong dng .

Theo thut ng ca m hnh quan h, mi dng c gi l mt b, tiu ca cc

ct c gi l thuc tnh, mt bng c gi l mt quan h. Phn tip theo s nh ngha

cc khi nim b, thuc tnh, quan h mt cch chnh xc hn.

2.1. Thuc tnh, b, lc quan h v quan h

1) Thuc tnh v kiu d liu:

Thuc tnh th hin tnh cht, c im ca mt i tng no trong thc t. V

d i tng sinh vin c th c cc thuc tnh nh h tn, gii tnh, ngy sinh, a ch,

qu qun. i tng hng ha c th c cc thuc tnh tn hng ha, n v tnh, n

gi

Mi thuc tnh thuc mt kiu d liu. Chng hn tn v a ch ca mt ngi gm

cc k t nn c gi l kiu chui (text). Ngy sinh thuc khi nim ngy thng nn

c gi l kiu ngy gi (datetime). n gi mt mt hng c th dng tnh ton nn

thuc kiu s (number). Mt kiu d liu khc cng c s dng ph bin l kiu logic

(yes/no xem chng ).

Thuc tnh Kiu d liu V d

HoTen Text Nguyn Th Trang

DiaChi Text 120 Ng Gia T

NgaySinh datetime 12/10/1 0

DonGia number 300.000

2) Lc quan h:

Mt lc quan h R, biu th bi R(A , A , , An), gm c tn quan h R v danh

sch n thuc tnh A , A , , An .

Lc quan h c dng m t mt quan h. V d mt lc c nm thuc

tnh dng m t i tng sinh vin ca trng i hc c th hin nh sau:

SinhVien(MaSV, HoTen, SoCMND, NgaySinh, DiemTOEIC)

Trong SinhVien l tn quan h. MaSV, HoTen, SoCMND, NgaySinh,

DiemTOEIC l cc thuc tnh. Nu trnh by y kiu d liu ca cc thuc tnh th c

th vit nh sau:

SinhVien(MaSV: text, HoTen: text, SoCMND: text, NgaySinh: datetime,

DiemTOEIC: number)

SinhVien

MaSV HoTen SoCMND NgaySinh DiemTOEIC

3) Quan h

Mt quan h r pht sinh t lc quan h R(A , A , , An) l mt tp hp m b r

= {t , t , , tm }, trong mi b l mt danh sch n gi tr t = {v ,v ,, vn}, mi gi tr vi

l tng ng vi thuc tnh Ai

V d: xt quan h sv pht sinh t lc quan h SinhVien(MaSV, Ho, Ten,

SoCMND, NgaySinh, TOEIC) trn. Quan h ny c 4 b:

t = { 5202 , Cng , 333 , 2 /5/ 99 , 500 }

t2 = { 5207 , Sn , 456 , 7/7/ 990 , 400 }

t3 = { 5306 , Nga , 777 , 22/3/ 992 , 200 }

t4 = { 5308 , Trang , 23 , 2/7/ 990 , 700 }

Trong quan h trn, mi b t , t , t , t i din cho mt i tng sinh vin c th.

Trong b t gi tr v = Cng tng ng vi thuc tnh A l HoTen cho bit HoTen ca

sinh vin ny l Cng. n gin v r rng, quan h sv trn c trnh by li theo

dng bng nh hnh bn di :

Hnh I- . Quan h th hin di dng bng

T lc quan h SinhVien, cng c th xy dng mt quan h sv2 khc gm 5 b

ng vi 5 sinh vin, hoc mt quan h sv3 cng c b nhng c thng tin MaSV, HoTen

khc.

nh ngha trn cho thy ng vi mt lt quan h, th c th c nhiu quan h

khc nhau. Xt ti mt thi im no th ch c mt quan h phn nh ng thc t

trong th gii thc. Quan h c gi quan h hin hnh. Khi th gii thc thay i,

quan h hin hnh cng thay i theo bng cc thao tc thm, xa, sa. Chng hn nh tip

nhn thm mt sinh vin mi vo hc (thm), thay i DiemTOEIC ca mt sinh vin

sv MaSV HoTen SoCMND NgaySinh DiemTOEIC

5202 Cng 333 21/5/1 1 500

5207 Sn 456 17/7/1 0 400

5306 Nga 777 22/3/1 2 200

5308 Trang 123 12/7/1 0 700

thuc tnh tn quan h

b

(sa). Do khi ni n tn ca mt lc quan h, v d R hay SinhVien, chng ta

ngm hiu rng cng l tn ca quan h hin hnh ca lc . Cn tn i km vi

danh sch thuc tnh, R(A , A , , An) hay SinhVien(MaSV, HoTen,), th ch c mt

ngha duy nht l lc quan h.

Tnh th t ca cc b trong quan h: Th t ca cc b trong mt quan h l khng

quan trng: theo nh ngha quan h l mt tp hp ca cc b, nn th t cc b l khng

c ngha. Theo l thuyt tp hp th {A, B, C} = {C, B, A}

Gi tr NULL:

Trong mt s trng hp, trong mt b gi tr ca mt thuc tnh l khng c hoc

c nhng khng c bit. Gi tr NULL c s dng trong cc trng hp ny. Xt b

sau trong quan h SinhVien

t = { 5306 , Nga , 777 , NULL , NULL }

B trn c gi tr NULL ti thuc tnh ngy sinh v thuc tnh im TOEIC. i vi

thuc tnh im TOEIC do sinh vin cha thi nn cha c im (khng c). i vi thuc

tnh ngy sinh, do giy t b tht lc v cha m khng nh, nn cha xc nh c ngy

sinh chnh xc cho sinh vin ny (ngy sinh c nhng khng bit, c th c b sung

sau). C hai trng hp u dng gi tr NULL.

2.2. Lc c s d liu v c s d liu

Mt lc c s d liu S l tp hp cc lc quan h S = { R , R , , Rp}

trong Ri l cc lc quan h c lin quan vi nhau.

Mt c s d liu DB pht sinh t lc c s d liu S l tp cc quan h DB = {

r , r , , r } trong ri l quan h pht sinh t lc Ri.

V d: vi mc ch qun l sinh vin trong trng i hc, Hnh I- th hin mt

lc c s d liu c gi l DaiHoc gm c nm quan h: DaiHoc = { Khoa,

SinhVien, MonHoc, LopHoc, KetQua }

Hnh I-5 th hin mt c s d liu tng ng vi lc DaiHoc. Chng ta s s

dng c s d liu ny minh ha cho cc vn khc v sau.

Khoa

MaKhoa TenKhoa

SinhVien

MaSV HoSV TenSV NgaySinh GioiTinh HocBong MaKH

MonHoc

MaMH TenMH SoTinChi

LopHoc

MaLop MaMH HocKy NamHoc GiaoVien

KetQua

MaSV MaLop Diem

Hnh I- . Lc c s d liu quan h DaiHoc

Hnh I-5. Mt c s d liu pht sinh t lc c s d liu DaiHoc

Nh vy, khi ni n mt c s d liu, chng ta ngm hiu l ni n c lc

ca c s d liu cng vi trng thi hin hnh phn nh thc t trong th gii thc

tng ng.

2.3. Rng buc ton vn

D liu trong cc quan h ca mt c s d liu c c l thng qua qu trnh tin

hnh cc thao tc thm, xa, sa. Tuy nhin vic xy ra cc sai st trong qu trnh cp nht

d liu c th dn n nhng tnh trng d liu khng mong mun. V d xt quan h

KetQua sau trong c s d liu DaiHoc:

KetQua MaSV MaLop Diem

5308 01 8

5308 02 -1

5202 01 6

5202 03 11

Khoa MaKhoa TenKhoa

CN Cng ngh Thng tin

KT Kinh t

SinhVien MaSV HoSV TenSV NgaySinh GioiTinh HocBong MaKH

5202 L Cng 21/5/1 1 Nam 200 KT

5207 Nguyn Sn 17/7/1 0 N 800 KT

520 Ng Ha 16/8/1 0 Nam 200 CN

5306 Hunh Nga 22/3/1 2 N 400 CN

5308 Phm Trang 12/7/1 0 N 800 CN

MonHoc MaMH TenMH SoTinChi

102 Qun tr hc 2

207 Cu trc d liu 3

306 Tin hc c s 2

403 K ton ti chnh 3

LopHoc MaLop MaMH HocKy NamHoc GiaoVien

01 306 1 2010 Tun

02 403 1 2010 Nga

03 306 2 2011 Sn

04 207 1 2010 Nam

KetQua MaSV MaLop Diem

5202 01 4

5202 02 7

5202 03 8

5202 04

5308 01 3

5308 02

5308 03

Trong th gii thc, thuc tnh Diem ch c gi tr t n . Do cc gi tr -1

hay 11 l nhng tnh trng d liu khng mong mun. C th khc phc tnh trng ny

bng cch a ra quy nh rng Diem ch c php nhn cc gi tr t n . y l

mt v d v rng buc ton vn.

Rng buc ton vn l cc quy tc cho mt c s d liu nhm m bo n phn nh

ng ng ngha ca th gii thc. Sau y l mt s loi rng buc ton vn ph bin.

2.3.1. Rng buc Kha ca lc quan h

Theo nh ngha, mt quan h l mt tp hp ca cc b r = {t , t , , tm }, do hai

b bt k trong mt quan h phi khc nhau. Kha (key) l mt cng c gip phn bit cc

b trong quan h.

nh ngha:

Kha ca mt lc quan h R(A , A , , An) l mt tp thuc tnh nh nht

K { A , A , , An } sao cho:

r pht sinh t R, t , t2 r th t [K] t2[K] (*)

V d: xt lc quan h SinhVien, mi sinh vin u c MaSV khc nhau, do

MaSV l mt kha ca lc quan h ny. Vic chn MaSV lm kha gip m bo

rng buc ton vn rng trong qu trnh cp nht d liu, khng c hai b bt k no c

MaSV ging nhau. Vic thm, xa, sa s b t chi nu lm cho quan h c hai b ging

nhau MaSV.

Lu : K phi c tnh nh nht, ngha l K K, th K khng c tnh cht (*)

V d: cng c th chn hai thuc tnh {MaSV, HoTen} phn bit hai b bt k

trong lc quan h SinhVien. Tuy nhin ch cn MaSV l . Do {MaSV, HoTen}

khng c gi l kha (c gi l siu kha).

Lu : Mt lc c th c nhiu kha. V d SoCMND cng l kha v khng

c hai sinh vin no ging nhau v SoCMND. Thuc tnh ny cng c th dng phn

bit hai sinh vin vi nhau. Tuy nhin i vi mt lc , ch chn mt kha lm i din

v c gi l kha chnh (primary key). Trong ng ngha ca c s d liu DaiHoc,

thuc tnh MaSV c ngha hn l SoCMND. Do MaSV c chn lm kha chnh

cho lc quan h SinhVien.

Nh vy, kha chnh cho cc lc quan h trong lc c s d liu DaiHoc l

nh sau:

2

MaKhoa : dng phn bit cc b trong quan h Khoa

MaSV : dng phn bit cc b trong quan h SinhVien

MaMH : dng phn bit cc b trong quan h MonHoc

MaLop : dng phn bit cc b trong quan h LopHoc

{MaSV, MaLop}: trong lc KetQua, kha c hai thuc tnh l MaSV v

MaLop. Nu ch chn MaSV lm kha th khng v mt sinh vin c th

theo hc nhiu lp, do c nhiu b c cng MaSV. Tng t, nu ch chn

MaLop lm kha cng khng v mt lp c nhiu sinh vin theo hc, nn c

nhiu b c cng MaLop. Vic chn {MaSV, MaLop} lm kha l ph hp v

theo ng ngha thc t mt sinh vin ch theo hc mt lp mt ln v ch c mt

im s.

2.3.2. Mi lin h gia cc quan h. Rng buc Kha ngoi

Xt hai quan h SinhVien v Khoa trong c s d liu DaiHoc

Trong thc t, gia hai i tng Khoa v SinhVien c mi lin h l mt Khoa th

c nhiu sinh vin v mt sinh vin th trc thuc mt Khoa no . Ta gi y l mi lin

h mt nhiu (Khoa -1, SinhVien - ) Hay ni cch khc Khoa l quan h cha, SinhVien

Khoa

MaKhoa TenKhoa

SinhVien


MonHoc

MaMH TenMH SoTinChi

LopHoc

MaLop MaMH HocKy NamHoc GiaoVien

KetQua

MaSV MaLop Diem

Khoa MaKhoa TenKhoa

CN Cng ngh Thng tin

KT Kinh t


5202 L Cng 21/5/1 1 Nam 200 KT

5207 Nguyn Sn 17/7/1 0 N 800 KT

520 Ng Ha 16/8/1 0 Nam 200 CN

5306 Hunh Nga 22/3/1 2 N 400 CN

5308 Phm Trang 12/7/1 0 N 800 CN

l quan h con. V vy trong lc quan h SinhVien c thuc tnh MaKH biu din

mi lin h ny.

Lc ny thuc tnh MaKH c gi l kha ngoi ca quan h SinhVien (quan h

con) v n tham chiu n kha chnh MaKhoa ca quan h Khoa (quan h cha)

Rng buc ton vn v kha ngoi pht biu rng: vi mt gi tr ti thuc tnh kha

ngoi trong quan h con, th gi tr phi tn ti trc trong quan h cha.

Xt v d :

y l mt trng thi khng hp l v ti b th ca quan h SinhVien, MaKH

NN cha tn ti ti thuc tnh MaKhoa bt c b no trong quan h Khoa. Trng thi

sau y mi c gi l hp l.

Nh vy, c th thit lp cc mi quan h v rng buc ton vn v kha ngoi cho

lt c s d liu DaiHoc nh sau:

Khoa

MaKhoa TenKhoa

SinhVien

MaSV Ho Ten NgaySinh GioiTinh HocBong MaKH

Khoa MaKhoa TenKhoa

CN Cng ngh Thng tin

KT Kinh t


5202 L Cng 21/5/1 1 Nam 200 KT

5207 Nguyn Sn 17/7/1 0 N 800 KT

520 Ng Ha 16/8/1 0 Nam 200 NN

5306 Hunh Nga 22/3/1 2 N 400 NN

5308 Phm Trang 12/7/1 0 N 800 CN

Khoa MaKhoa TenKhoa

CN Cng ngh Thng tin

KT Kinh t

NN Ngoi ng


5202 L Cng 21/5/1 1 Nam 200 KT

5207 Nguyn Sn 17/7/1 0 N 800 KT

520 Ng Ha 16/8/1 0 Nam 200 NN

5306 Hunh Nga 22/3/1 2 N 400 NN

5308 Phm Trang 12/7/1 0 Nam 800 CN

Hnh I- . Mi lin h v rng buc kha ngoi gia cc lt quan h trong CSDL DaiHoc

2.3.3. Cc rng buc khc

1) Rng buc v min gi tr

Quy nh Diem ch nhn cc gi tr t n 10 l mt rng buc min gi tr.

2) Rng buc gi tr NULL

Rng buc ny cho php mt thuc tnh c c nhn gi tr NULL hay khng

(NULL hay NOT NULL). V d thuc tnh SoTinChi ca lc quan h MonHoc phi l

NOT NULL v mi mn hc phi c s tn ch c th.

3) Rng buc lin thuc tnh:

Rng buc ny nh hng n nhiu thuc tnh trong mt quan h. Xt quan h t

phng khch sn sau y :

DatPhong(TenKhachHang, TenPhong, TuNgay, DenNgay, DonGia)

Th c rng buc lin thuc tnh: TuNgay

2.4.1. Thm (Insert, Append)

1. Thm b { NULL, Mai, Anh, 23/ / 989, N, 300, CB } vo quan h SinhVien.

Thao tc thm ny vi phm rng buc ton vn kha chnh phi khc

NULL. V vy thm khng thnh cng.

2. Thm b { 5202, Mai, Anh, 23/ / 989, N, 300, CB } vo quan h SinhVien.

Thao tc thm ny vi phm rng buc ton vn kha chnh v gi tr kha

2 2 tn ti. Thm khng thnh cng.

3. Thm b { 5205, Mai, Anh, 23/ / 989, N, 300, CB } vo quan h SinhVien.

Thao tc thm ny vi phm rng buc ton vn kha ngoi MaKH v trong

quan h Khoa khng c b no c MaKhoa l CB. Thm khng thnh

cng.

4. Thm b { 5205, Mai, Anh, 23/ / 989, N, 300, KT } vo quan h SinhVien.

Thao tc ny tha mn cc rng buc. Thm thnh cng.

2.4.2. Xa (Delete)

1. Xa mn hc c TenMH l Qun tr hc

Xa thnh cng.

2. Xa mn hc c TenMH l Tin hc c s

Thao tc ny khng thnh cng v trong quan h LopHoc (bng con) c lp

v lp tham chiu n mn hc ny. Nu xa b ny trong bng

MonHoc (bng cha) th vi phm rng buc ton vn.

trnh vi phm rng buc ton vn kha ngoi khi xa mt b trong bng cha, mt

phng n x l khc l xa lun cc b c lin quan trong bng con (CASCADE

DELETE).

2.4.3. Sa (Update)

1. Trong quan h SinhVien sa HocBong ca b c MaSV = thnh .

Thao tc sa ny thnh cng.

2. Sa MaKH ca sinh vin Trang thnh CK

Vi phm rng buc ton vn kha ngoi v trong quan h Khoa khng c b

no c MaKhoa l CK.

MonHoc MaMH TenMH SoTinChi

102 Qun tr hc 2

207 Cu trc d liu 3

306 Tin hc c s 2

403 K ton ti chnh 3

LopHoc MaLop MaMH HocKy NamHoc GiaoVien

01 306 1 2010 Tun

02 403 1 2010 Nga

03 306 2 2011 Sn

04 207 1 2010 Nam

cascade

delete

3. Sa MaKhoa ca khoa Cng ngh Thng tin thnh IT

Thao tc sa ny vi phm rng buc ton vn kha ngoi v trong quan h

SinhVien c 3 c sinh vin c MaKH l CN. Thao tc ny b t chi. Mt

phng n x l khc trnh vi phm rng buc ton vn l sa lun

MaKH ca cc sinh vin ny thnh IT (CASCADE UPDATE)

2.5. Bi tp

1) Mt cng ty du lch cn t chc mt c s d liu nhm qun l hot ng kinh

doanh du lch ca cng ty. Cc thng tin cn qun l nh sau:

Danh sch cc khch hng ca cng ty

Danh sch cc im du lch cng ty c m tour, s ngy n im du lch ,

v n gi cho mt ngi.

Danh sch cc tour du lch c th, mi tour c mt im n v xut pht vo

mt ngy no .

Khch hng no ng k tour du lch no, vi s lng bao nhiu.

Yu cu:

a) Hy nh ngha lc c s d liu quan h cho m hnh trn v thit lp cc

rng buc ton vn v kha chnh v kha ngoi tng t nh trong Hnh I- .

b) To mt s d liu mu cho c s d liu trn nh trong Hnh I-5

Khoa MaKhoa TenKhoa

IT Cng ngh Thng tin

KT Kinh t


5202 L Cng 21/5/1 1 Nam 200 KT

5207 Nguyn Sn 17/7/1 0 N 800 KT

520 Ng Ha 16/8/1 0 Nam 200 IT

5306 Hunh Nga 22/3/1 2 N 400 IT

5308 Phm Trang 12/7/1 0 N 800 IT

cascade

update

2) Xt c s d liu BanHang nh sau:

a) Hy thit lp cc rng buc ton vn v kha chnh v kha ngoi.

b) Cho bit cc thao tc sau c vi phm rng buc ton vn hay khng, nu l do:

Thm b { A00 , Sn H, 33 Hng Lnh } vo quan h KhachHang.

Thm b { S02, Sa chua Yomost, Lc, 4500 } vo quan h

MatHang.

Thm b { 005, 2/2/20 0, A004 } vo quan h HoaDon

Xa khch hng c tn L Cng trong quan h KhachHang

Xa mt hng c tn Kem Monte-Rosa trong quan h MatHang

Xa ha n trong quan h HoaDon (c CASCADE DELETE)

Xa mt hng bn S ca ha n 2 trong quan h MatHangBan.

Trong quan h KhachHang, sa MaKH A thnh A 2

Trong quan h KhachHang, sa MaKH B 2 thnh B (c

CASCADE UPDATE)

Trong quan h MatHang sa n gi ca mt hng S thnh .

Trong quan h HoaDon, sa MaKH ca ha n thnh D








K01 Kem Monte-Rosa K 20.000

HoaDon MaHD NgayHD MaKH

001 12/2/2010 A001

002 12/2/2010 B002

003 20/4/2011 B002

004 25/7/2011 A001

MatHangBan MaHD MaMH SoLuong

001 S03 2

001 B02 5

002 K01 6

002 S03 8

003 S03 10

004 B02 7

CHNG . I S QUAN H

Cc thao tc trn c s d liu c th c chia thnh hai loi. Loi th nht l cp

nht d liu vo cc quan h, l cc thao tc thm, xa, sa c trnh by trong

chng 2. Loi th hai l truy xut d liu t cc quan h, hay cn gi l truy vn (query).

Sau y l mt v d v truy vn trong c s d liu DaiHoc:

Hy cho bit bng im (gm tn cc mn hc v im s tng ng) ca sinh vin

c m s . Kt qu ca truy vn ny cng c dng quan h nh sau:

MaSV HoTen MaMH TenMH SoTinChi Diem

5308 Phm Trang 306 Tin hc c s 2

5308 Phm Trang 403 K ton ti chnh 3

M hnh d liu quan h nh ngha cc php ton dng truy vn. Tp hp cc

php ton ny c gi l i s quan h. Mt dy cc php ton to thnh mt biu thc

i s quan h. Trc khi i vo cc php ton i s quan h, phn 3.1 trnh by li mt

s khi nim v cc biu thc c bn.

3.1. Biu thc c bn

C 4 loi biu thc c bn

1) Biu thc s hc:

Tng t nh biu thc s hc thng thng, cc ton hng ca biu thc c kiu s.

V d :

Biu thc Kt qu

HocBong 100 600 (nu HocBong 500

Sqrt(x) 5 (nu x=25)

SoLuong*DonGia 100000 (nu SoLuong = 5, DonGia=20000)

2) Biu thc chui:

L biu thc m cc ton hng thuc kiu chui. V d :

Biu thc Kt qu

'Nguyen' + 'Son' 'NguyenSon'

Ho + Ten 'LeMai' (nu Ho='Le' v Ten='Mai')

Ho + ' ' + Ten 'Le Mai'

Len(Ten) 3 (nu Ten = 'Mai')

5 (nu Ten = 'Nguyen')

Left(Ten, 2) 'Ma'

3) Biu thc ngy thng:

Biu thc ngy thng thng lin quan n kiu d liu ngy thng v kiu s. V d:

Biu thc Kt qu

Month(#8/12/2011#) 8

Day(#5/2/2011#) 2

Year(Now()) 2011

Year(Now()) Year(NgaySinh) Tnh s tui ca sinh vin

2

4) Biu thc logic:

Biu thc logic l mt biu thc m kt qu ch c hai gi tr l ng (true) hoc sai

(false). V d:

Biu thc Kt qu

8 5 ng

12 4 15 Sai

'Nam' < 'Nga' ng

HocBong >= 200 ng nu HocBong = 300, 500

Sai (nu HocBong = 100)

Ten like 'N*' ng nu TenSV bt u bng ch N nh: Nam, Nga,

Nhn

NgaySinh < 1/2/1 1 ng, nu sinh vin sinh trc ngy 2/1/1991

Ngoi cc php so snh, chng ta cn c cc php AND, OR, XOR. Mc ch l

kt hp hai biu thc logic thnh mt biu thc logic khc theo bng bn di :

a b a AND b a OR b NOT a

ng ng ng ng sai

ng sai Sai ng sai

sai ng sai ng ng

sai sai sai sai ng

V d :

Biu thc Kt qu

Year(NgaySinh)= 1992 AND HocBong> 0 Sinh vin sinh nm 1 2 v hc bng trn 500

(HocBong>=200) AND (HocBong=3) (MonHoc)

2

V d 2: Lit k cc sinh vin sinh nm v c hc bng trn

(Year(NgaySinh) = 1990 AND DiemTOEIC > 300) (SinhVien)


5202 L Cng 21/5/1 1 Nam 200 KT

5207 Nguyn Sn 17/7/1 0 N 800 KT

520 Ng Ha 16/8/1 0 Nam 200 CN

5306 Hunh Nga 22/3/1 2 N 400 CN

5308 Phm Trang 12/7/1 0 N 800 CN

3.3. Php chiu (PROJECT)

Php chiu dng ly ra mt s thuc tnh ca cc b trong mt quan h. Nu quan

nim mt quan h l mt bng gm nhiu ct, php chn dng chn ra mt s dng th

php chiu chn ra mt s ct.

C php: (danh-sch-thuc-tnh) (TenQuanHe)

V d : Lit k cc sinh vin vi MaSV, HoSV, TenSV

Php chiu cng c th to ra mt thuc tnh mi cho quan h kt qu:

V d 4: MaSV, HoTen: HoSV+ ' ' + TenSV(SinhVien)

MaSV HoTen

5202 L Cng

5207 Nguyn Sn

520 Ng Ha

5306 Hunh Nga

5308 Phm Trang


5202 L Cng 21/5/1 1 Nam 200 KT

5207 Nguyn Sn 17/7/1 0 N 800 KT

520 Ng Ha 16/8/1 0 Nam 200 CN

5306 Hunh Nga 22/3/1 2 N 400 CN

5308 Phm Trang 12/7/1 0 N 800 CN

MaSV HoSV TenSV

5202 L Cng

5207 Nguyn Sn

520 Ng Ha

5306 Hunh Nga

5308 Phm Trang

MaSV, HoSV, TenSV(SinhVien)

22

Sau y l mt v d minh ha php chiu kt hp vi php chn:

V d : Lit k cc sinh vin sinh nm v c im HocBong trn , ch lit

k cc ct MaSV, Ho, Ten, NgaySinh

MaSV, HoSV, TenSV, NgaySinh [ Year(NgaySinh)=1990 AND HocBong > 300 (SinhVien)]

MaSV HoSV TenSV NgaySinh

5207 Nguyn Sn 17/7/1 0

5308 Phm Trang 12/7/1 0

Tuy nhin nu thc hin php chiu trc s gy ra li v sau khi thc hin php

chiu th khng cn thuc tnh HocBong thc hin php chn. Biu thc sau y l biu

thc sai:

Year(NgaySinh) = 1990 AND HocBong > 300[MaSV, HoSV, TenSV, NgaySinh (SinhVien)]

3.4. Php tch Descartes (CARTESIAN PRODUCT)

Trong l thuyt tp hp, php tch Descartes dng nhn hai tp hp vi nhau. V

d: cho tp A = { a , a }, tp B = { b , b , b } th tch Descartes ca A v B l:

A B = { a b , a b , a b , a b , a b , a b }

Tp kt qu c 6 phn t, bng s lng ca tp A nhn s lng tp B.

T php tch Descartes cho hai quan h c nh ngha nh sau:

Cho hai quan h r v s pht sinh t hai lt R(A , A ,, An) v S(B , B , , Bm)

trong r c nR b v s c nS b. Tch Descartes ca r v s l mt quan h c lt

Q(A , A ,, An, B , B , , Bm). Quan h ny c nR * nS b. Nhng b ny hnh thnh bng

cch kt hp mt b trong r v mt b trong s.

2

V d: SinhVien Khoa

Hnh I- . Php tch Descartes

Bn thn php tch Descartes t c ngha thc t. Tuy nhin php tch Descartes l

c s cho php kt ni c trnh by trong phn tip theo.

3.5. Php kt ni bng (EQUI JOIN)

V d Xt truy vn sau y: hy cho bit tn khoa ng vi tng sinh vin.

Truy vn trn c th thc hin c dng php tch Descartes, sau p dng thm

mt mt php chn da vo iu kin MaKH = MaKhoa.


5202 L Cng 21/5/1 1 Nam 200 KT

5207 Nguyn Sn 17/7/1 0 N 800 KT

520 Ng Ha 16/8/1 0 Nam 200 CN

5306 Hunh Nga 22/3/1 2 N 400 CN

5308 Phm Trang 12/7/1 0 N 800 CN

MaKhoa TenKhoa

CN Cng ngh Thng tin

KT Kinh t

MaSV HoSV TenSV NgaySinh GioiTinh HocBong MaKH MaKhoa TenKhoa

5202 L Cng 21/5/1 1 Nam 200 KT CN Cng ngh Thng tin

5202 L Cng 21/5/1 1 Nam 200 KT KT Kinh t

5207 Nguyn Sn 17/7/1 0 N 800 KT CN Cng ngh Thng tin

5207 Nguyn Sn 17/7/1 0 N 800 KT KT Kinh t

520 Ng Ha 16/8/1 0 Nam 200 CN CN Cng ngh Thng tin

520 Ng Ha 16/8/1 0 Nam 200 CN KT Kinh t

5306 Hunh Nga 22/3/1 2 N 400 CN CN Cng ngh Thng tin

5306 Hunh Nga 22/3/1 2 N 400 CN KT Kinh t

5308 Phm Trang 12/7/1 0 N 800 CN CN Cng ngh Thng tin

5308 Phm Trang 12/7/1 0 N 800 CN KT Kinh t

2

Cch thc hin mt tch Descartes v sau l mt php chn c iu kin bng nh

vy c gi l php kt ni bng.

C php:

QuanHe1 iu-kin-bng QuanHe2 V d trn c vit li l:

SinhVien MaKH = MaKhoa Khoa Trong trng hp cc thuc tnh kt ni bng c hai quan h ging tn nhau th

php kt ni bng c gi l php kt ni t nhin. Lc ny ch cn vit php ton kt ni

m khng cn vit danh sch iu-kin-bng i km.

V d Hy cho bit tn mn hc ng vi cc lp hc

LopHoc MonHoc (ngm hiu iu kin bng l thuc tnh MaMH c hai quan h)

Kt qu:

Trong quan h kt qu, do c 2 thuc tnh MaMH nn c hai cch x l:

Ch gi li mt thuc tnh MaMH

Dng tn quan h phn bit: LopHoc.MaMH v MonHoc.MaMH


5202 L Cng 21/5/1 1 Nam 200 KT CN Cng ngh Thng tin


5207 Nguyn Sn 17/7/1 0 N 800 KT CN Cng ngh Thng tin



520 Ng Ha 16/8/1 0 Nam 200 CN KT Kinh t


5306 Hunh Nga 22/3/1 2 N 400 CN KT Kinh t


5308 Phm Trang 12/7/1 0 N 800 CN KT Kinh t







MaLop MaMH HocKy NamHoc GiaoVien MaMH TenMH SoTinChi

01 306 1 2010 Tun 306 Tin hc c s 2

02 403 1 2010 Nga 403 K ton ti chnh 3

03 306 2 2011 Sn 306 Tin hc c s 2

04 207 1 2010 Nam 207 Cu trc d liu 3

(MaKH=MaKhoa) (SinhVien Khoa)

2

V d Hy cho bit MaSV, HoSV, TenSV cc sinh vin c theo hc nhng lp do

gio vin Tun ging dy:

MaSV, HoSV, TenSV ( GiaoVien=Tun [(LopHoc KetQua) SinhVien])

Hoc:

MaSV, HoSV, TenSV ( GiaoVien=Tun [LopHoc (KetQua SinhVien)])

Hoc:

MaSV, HoSV, TenSV ([ GiaoVien=Tun (LopHoc)] KetQua SinhVien)

Tuy nhin, vit nh bn di l khng hp l:

MaSV, HoSV, TenSV ( GiaoVien=Tun [LopHoc SinhVien KetQua])

v gia LopHoc v SinhVien khng c thuc tnh kt ni.

V d Hy cho bit TenMH, SoTinChi, Diem ca cc mn hc m sinh vin c

MaSV = 2 2 hc:

TenMH, SoTinChi, Diem ([ MaSV=5202 (KetQua LopHoc MonHoc))

3.6. Php nhm d liu (GROUP)

V d 10: Gi s c truy vn sau: Da vo bng SinhVien, tnh s lng sinh vin v

tng hc bng ca tng khoa. Kt qu nh bng di:

Cch thc thc hin nh trn c gi l php nhm.


5202 L Cng 21/5/1 1 Nam 200 KT

5207 Nguyn Sn 17/7/1 0 N 800 KT

520 Ng Ha 16/8/1 0 Nam 200 CN

5306 Hunh Nga 22/3/1 2 N 400 CN

5308 Phm Trang 12/7/1 0 N 800 CN

MaKhoa SoSV TongHB

KT 2 1.000

CN 3 1.400

Thuc tnh dng

tch nhm cc b

Count l hm m s MaSV

(s b) trong tng nhm

MaKH G SoSV: Count(MaSV), TongHB: Sum(HocBong) (SinhVien)

2

3

Tch nhm

theo m khoa

m s SV

trong nhm

2

C php:

Thuc-tnh-nhm G Cc-hm-gp (QuanHe)

Hm Count c c gi l hm gp (aggregate function). Danh sch cc hm gp

c lit k bn di

Tn hm Mc ch

Count m s b

Sum Tnh tng cc gi tr

Avg Tnh gi tr trung bnh

Max Tm gi tr ln nht

Min Tm gi tr nh nht

V d 11: Tnh hc bng cao nht, thp nht, v trung bnh ca cc sinh vin theo

tng khoa

MaKH G Max(HocBong), Min(HocBong), Avg(HocBong) (SinhVien)

MaKhoa Max Min Avg

KT 800 200 500

CNTT 800 200 466

V d 12: m rng v d , tnh s sinh vin ca tng Khoa, lit k cc thuc tnh:

MaKhoa, TenKhoa, SoSV.

c thm ct TenKhoa, cn thc hin php kt gia bng SinhVien v bng Khoa,

sau thc hin php nhm nh bnh thng, tuy nhin ct TenKhoa phi c a vo

lm thuc tnh nhm chung vi MaKhoa

MaKhoa, TenKhoa G SoSV: Count(MaSV) (SinhVien MaKH = MaKhoa Khoa)







MaKhoa TenKhoa SoSV

KT Kinh t 2

CN Cng ngh Thng tin 3

Thuc tnh nhm gm

MaKhoa v TenKhoa

2

V d 1 : Cho bit s s ca tng lp hc. Lit k: MaLop, TenMH, SiSo

MaLop, TenMH G SiSo: Count(MaSV) (LopHoc MonHoc KetQua)

MaLop TenMH SiSo

01 Tin hc c s 2

02 K ton ti chnh 2

03 Tin hc c s 1

04 Cu trc d liu 1

V d 14: Da vo bng KetQua v bng MonHoc, tnh im trung bnh hc k 1

nm 2 ca cc sinh vin theo quy tc.

tam NamHoc=2010 AND HocKy= 1 (KetQua LopHoc MonHoc)

Kt qu ca tam:

MaSV MaLop Diem MaMH HocKy NamHoc GiaoVien TenMH SoTinChi

5202 01 4 306 1 2010 Tun Tin hc c s 2

5202 02 7 403 1 2010 Nga K ton ti chnh 3

5202 04 207 1 2010 Nam Cu trc d liu 3

5308 01 3 306 1 2010 Tun Tin hc c s 2

5308 02 403 1 2010 Nga K ton ti chnh 3

MaSV G DiemTB: Sum(Diem*SoTinChi)/Sum(SoTinChi) (tam)

Kt qu:

MaSV DiemTB

5302 7.0

5308 6.6

2

3.7. Bi tp

1) Cho c s d liu v BanHang nh bn di:

Hy cho bit biu thc i s quan h thc hin cc cu truy vn sau v cho bit

ni dung kt qu ca tng cu truy vn:

a) Lit k tn cc mt hng c n gi ln hn 10.000 (MaMH, TenMH, DonGia)

b) Cho bit tn khch hng ng vi tng ha n (MaHD, NgayHD, TenKH)

c) Hy cho bit tn cc khch hng mua mt hng Sa chua Vinamilk

d) Lit k tn cc mt hng bn c trong ngy 12/2/2010

e) Tnh thnh tin ca tng ha n (MaHD, NgayHD, TenKH, ThanhTien)

f) Cho bit tn cc mt hng m khch hng Nguyn Trang mua, vi tng s

lng v tr gi l bao nhiu.

g) Tnh doanh thu bn hng theo tng ngy (NgayHD, DoanhThu)

h) Tnh doanh thu bn hng theo tng ngy ca tng mt hng (NgayHD,

TenMatHang, DoanhThu)








K01 Kem Monte-Rosa K 20.000

HoaDon MaHD NgayHD MaKH

001 12/2/2010 A001

002 12/2/2010 B002

003 20/4/2011 B002

004 25/7/2011 A001

MatHangBan MaHD MaMH SoLuong

001 S03 2

001 B02 5

002 K01 6

002 S03 8

003 S03 10

004 B02 7

2

2) Cho c s d liu v qun l cc d n ca mt cng ty nh sau:

Hy cho bit kha chnh, mi lin h v rng buc kha ngoi gia cc quan h trong

c s d liu trn.

Thc hin cc cu truy vn sau bng biu thc i s quan h v cho bit ni dung

kt qu ca tng cu truy vn:

a) Lit k cc phng ban v tn trng phng ca tng phng ban (h tn y ).

b) Tm cc nhn vin lng t hn . . Phng Kinh doanh hoc t hn

. . Phng K ton.

c) Lit k cc nhn vin v cho bit tn trng phng ca tng nhn vin .

d) Hy cho bit cc phng ban no c thc hin d n ti Nha Trang.

e) Tnh mc lng trung bnh ca tng phng ban.

f) Trong cc d n thc hin ti Nha Trang, tnh tng s gi nhn vin ca tng d

n.

g) Vi mi gi lm vic cho d n, mt nhn vin s c thng 1% tin lng.

Tnh s tin thng ca tng nhn vin.

h) Tnh s tin thng ca tng nhn vin theo tng d n.

PhongBan MaPB TenPB TruongPhong

KD Phng Kinh doanh N01

KT Phng K ton V02

NhanVien MaNV HoNV TenNV Luong MaPB

N01 Hoi Nam 3.000.000 KD

V02 Bnh Phng 4.000.000 KT

N03 M Ha 5.000.000 KD

V04 Phng Anh 5.000.000 KT

DuAn MaDA TenDA DiaDiem NgayBD MaPB

D1 H Con Ra Si Gn 27/3/2011 KD

D2 Cha Ty Phng H Ni 10/6/2012 KD

D3 Cha Linh Sn Nha Trang 16/8/2011 KD

D4 Dinh Bo i Nha Trang 22/1/2010 KT

PhanCong MaDA MaNV SoGio

D1 N01 2

D1 N03 5

D2 N01 6

D2 V02 8

D3 V02 10

D4 V04 7

Phn II

H QUN TR C S D LIU

MICROSOFT ACCESS

CHNG 4. GII THIU H QUN TR C S D LIU

MICROSOFT ACCESS

4.1. Gii thiu Microsoft Access

MS Access l mt H qun tr c s d liu quan h (RDBMS - Relational Database

Management System), l mt phn mm trong b ng dng Microsoft Office chy trn

mi trng Windows ca hng phn mm Microsoft.

MS Access c giao din thn thin, d s dng, hiu nng cao, cho php x l d

liu v kt xut vo cc biu mu, bo co theo dng thc chuyn nghip rt ph hp cho

cc bi ton qun l va v nh.

Microsoft Access cn cung cp nhiu cng c pht trin y nng lc nng cao

hiu sut cng vic. Bng cch dng cc Wizard ca MS Access v cc lnh c sn

(macro) ngi s dng c th d dng t ng ha cng vic m khng cn lp trnh.

4.2. Khi ng MS Access

Start All Programs Microsoft Office Microsoft Office Access

4.3. To c s d liu mi

Mt c s d liu Access c lu trn a di dng mt tp tin. to mt CSDL

mi th chn menu File New Blank Database

hp thoi tip theo, chn ni lu tr v tn tp tin c s d liu

Sau khi chn ni lu tr v tn tp tin th nhn vo nt to c

s d liu mi.

4.4. M mt CSDL c trong my tnh

S dng mt trong cc cch sau

Vo menu File Open

Chn th mc v double-click vo tp tin CSDL.

4.5. Cc i tng chnh ca mt CSDL Access

Access l mt h qun tr theo m hnh c s d liu quan h, v vy v vy Access

c cc i tng th hin cc khi nim quan h, lc quan h v truy vn:

Bng (Table) : th hin khi nim quan h trong c s d liu. Bng c hai ch

: design view - lc quan h v data view - quan h. Trong bng gm

nhiu dng - b v ct thuc tnh

Truy vn (Query): thc hin cc truy vn v mt s thao tc thm, xa, sa

trn cc bng.

Cc thnh phn tin ch khc:

Biu mu (Form): Giao din do ngi dng t to cho php lm vic vi d

liu, ch yu c s dng trong vic nhp v hin th d liu.

Bo co (Report) : Kt qu ca qu trnh khai thc d liu c t chc v

nh dng to thnh bn in.

Pages: Tng t nh Form nhng hot ng trn nn Web.

Macro : Tp hp cc lnh nhm t ng ha cc thao tc.

Module: Cho php lp trnh thao tc trn cc bng v truy vn.

Ni lu tp tin c s d liu

Tn tp tin c s d liu

4.6. Cc ton t s dng trong MS Access

Ton t ngha

( ) Kt nhm biu thc

Not Ph nh mnh

And Kt hp mnh : tt c mnh thnh phn ng th cho kt qu ng, nu c t nht 1 mnh thnh phn sai th cho kt qu sai.

Or Kt hp mnh : tt c mnh thnh phn sai th cho kt qu sai, nu c t nht 1 mnh thnh phn ng th cho kt qu ng.

Xor 2 mnh c gi tr tri ngc nhau th cho kt qu ng

Epv 2 mnh c gi tr tri ngc nhau th cho kt qu sai

^ Lu tha (5^3=75)

+ - * / Cng, Tr, Nhn, Chia

\ Php chia ly phn nguyn

Mod Php chia ly phn d

, = Nh hn, Ln hn, Bng

= Nh hn hoc bng, Ln hn hoc bng

Khng bng

Is So snh 2 i tng

In Thuc, trong tp hp hoc min gi tr (dng trong cc cu truy vn)

Between ... And ...

Trong khong tn

Like Ging: * (t hp k t bt k), ? ( k t bt k), # (Mt k s bt k) V d: like "A*", like "Access????", like 1#00

& Ghp chui

+ Ghp chui hoc cng s

Ton t ngha Ton t ngha

True Gi tr ng "" hoc '' Gi tr chui, v d "ACCESS"

False Gi tr sai [] Gi tr tham s, v d [tham so]

Null Gi tr rng #...# Gi tr thi gian, v d 2 2

Date Ngy hin hnh

[Color]

Mu, v d

Now Ngy gi hin hnh

[white] : mu trng

Time Gi hin hnh [red] : mu

4.7. Mt s hm s dng trong MS Access

1) Cc hm v chui

Hm Kt qu

Ten LIKE 'N*' Tn bt u bng ch N

Ten LIKE '*a*' Tn c cha ch a

Len(Ten) 3 (nu Ten = 'Mai')

5 (nu Ten = 'Nguyen')

Left(Ten, 2) 'Ma'

Right(TenSV,2) 'ai' (nu TenSV = 'Mai')

Mid(TenSV,2,3) 'guy' (nu TenSV = 'Nguyen')

2) Cc hm v ngy thng

Hm Kt qu

Now() Ngy gi hin ti

Month(#8/12/2011#)

Day(#5/27/2011#) 2

Year(Now()) 2

Year(Now()) Year(NgaySinh) Tnh s tui ca sinh vin

Hour(#06:30#)

Minute(#13: 2

DatePart('q', Tnh qu 2

3) Hm IIF

C php: IIF(iu-kin, A, B)

(vi iu-kin l biu thc logic. A, B l cc biu thc bt k)

ngha: nu iu-kin ng th kt qu ca biu thc IIF l biu thc A,

nu iu-kin sai th kt qu ca biu thc IIF l biu thc B.

V d: IIF(Diem>=5, 't', 'Khng t')

Vy nu Diem = 6, 7, 8 th kt qu ca IIF l 't'. Nu Diem = 2, 4 th kt qu ca

IIF l 'Khng t'.

4.8. Bi tp

Hy thc hin cc thao tc sau trn my tnh:

1) Khi ng Access

2) To mt c s d liu mang tn bn v lu vo a C hoc D

3) Trong c s d liu va to, quan st cc mc Table, Query, Form, Report,

Macro v Module

4) ng c s d liu v ng Access

5) Khi ng li Access, m li c s d liu va to (lu : dng menu File ->

Open, khng c dng menu File -> New)

CHNG 5. BNG (TABLE)

5.1. Cc khi nim

5.1.1. Bng

Bng l thnh phn c bn v quan trng nht ca CSDL Access, th hin khi nim

lc quan h v quan h.

Mt bng d liu ca CSDL Access bao gm cc thnh phn: tp hp cc trng d

liu (thuc tnh trong m hnh quan h), trng kho chnh, tp hp cc thuc tnh cn

thit cho mi trng d liu, tp hp cc bn ghi (b).

V d:

Bng d liu trong trng thi Datasheet (dng cp nhp, xem, sa d liu)

Bng d liu trong trng thi Design view (dng thit k cu trc lc

quan h)

Bn ghi (record)

Trng d liu (field) Tn bng

Cc thuc tnh m

t trng d liu

Trng kha chnh

(primary key)

5.1.2. Trng d liu (field)

Trng d liu, tng ng vi ct ca bng, m t tng c im ring ca i

tng. Mi trng d liu s c mt tn gi, kiu d liu v tp hp cc tnh cht m t

trng d liu .

V d: Trng MaKhoa c kiu d liu l Text, kch thc 2 k t, c hin th l

ch in hoa,

5.1.3. Bn ghi (record)

Mi dng d liu ca bng c gi mt bn ghi.

V d: Bng Khoa c 3 bn ghi l (CN, Khoa Cng ngh thng tin), (KT, Khoa

Kinh t), (NN, Khoa Ngoi ng)

5.1.4. Kha chnh (primary key)

Kha chnh ca bng l trng hoc tp trng dng phn bit cc bn ghi trong

cng mt bng.

V d: mt trng i hc, mi sinh vin khi nhp hc u c cp 1 m sinh

vin khng ging nhau, do c bng SinhVien c thit k nh sau:

Nh vy trng MaSV cha d liu m sinh vin s l kha chnh ca bng

SINHVIEN v m sinh vin l duy nht, cc sinh vin c th trng h tn, ngy

sinh,nhng khng th c m sinh vin ging nhau.

V d: Kt qu hc tp ca sinh vin bao gm cc d liu sau: m sinh vin, m cc

lp hc sinh vin ng k v im kt qu ca cc lp hc . Trong CSDL ca

trng c bng KetQua c thit k nh sau

Nh vy 2 trng MaSV (cha d liu m sinh vin) v MaLop (cha m lp hc m

sinh vin ng k) s to thnh kha chnh ca bng KetQua, khng th c 1 sinh vin

ng k lp hc m c 2 im kt qu khc nhau.

Ch : MS Access khng chp nhn cc gi tr trng nhau hay trng (null) trong trng kha chnh.

5.1.5. Mi lin h gia cc bng (relationship)

Th hin khi nim mi lin h gia cc quan h trong m hnh l thuyt (xem phn

Phn I2.3.2). i km vi mi lin h l rng buc ton vn v kha ngoi.

Trong Access tn ti 2 kiu quan h: quan h - v quan h - (mt-nhiu)

a) Quan h 1-1

Mi bn ghi ca bng ny s khng lin kt hoc lin kt vi duy nht ti mt bn

ghi ca bng kia v ngc li

V d: Mi s bo danh ca th sinh

thi i hc ch c 1 s phch bi thi v s

phch ca mi bi thi ch thuc v 1 s

bo danh.

M t d liu 2 bng nh sau:

b) Quan h 1-

Mt bn ghi trong bng A khng kt hp hoc kt hp vi mt hay nhiu bn ghi

trong bng B, nhng ngc li mt bn ghi trong bng B ch c th kt hp duy nht vi

mt bn ghi trong bng A.

V d: mt khoa ca trng i hc Nha Trang

c nhiu sinh vin nhng mt sinh vin ch thuc v

duy nht mt khoa.

M t d liu 2 bng nh sau:

5.2. Xy dng cu trc bng

5.2.1. S dng ch Design View to bng

Tab Tables Create Table in Design View hoc Tab Tables

Sau ta c bng thit k nh sau:

Tn trng

La chn kiu d liu

M t ngn gn ca trng

Bng thit k sau khi c nhp gi tr:

a) Tn trng

Tn trng c th t ty , tuy nhin d qun l nn t ngn gn, d gi nh,

khng cha k t trng v khng dng ch ting Vit c du.

b) Kiu d liu (Data Type)

MS Access cung cp mt s kiu d liu c bn sau:

Text: kiu chui c di ti a 2 k t

Memo: kiu chui c di ti a . k t

Number: kiu s

Date/Time: kiu ngy (nu cn c th lu thm thng tin v gi)

Currency: kiu s c nh dng theo loi tin t

AutoNumber: kiu s nhng t ng tng do Microsoft Access cung cp v

qun l, ngi dng khng th cp nht

Yes/No: kiu Logic

OLE Object: kiu i tng kt nhng: Word, Excel,..

Hyperlink: kiu chui ch ng dn ca mt tp tin trn cng hay trn my

ch ca mng hay mt a ch URL trn mng.

Lookup Wizard: to mt ct chn gi tr v tm kim t mt bng khc

Kha

chnh Kch thc ca trng

Quy nh dng hin th d liu

Quy nh dng thc d liu

phi nhp

Tiu ct m trng hin th

Gi tr mc nh ca trng khi nhp d liu

Cc quy tc hp l ca d liu nhp

Cho php nhp

chui rng

Thng bo khi nhp d liu sai

quy tc

Bt buc phi nhp d

liu hay khng To ch mc

2

Khai bo thuc tnh cho trng th General:

c) Field Size

Xc nh kch thc ti a cho d liu kiu Number hay Text ca trng.

i vi kiu s (number) th Access cung cp cc kiu sau

Loi s ln

Byte s nguyn byte

Integer s nguyn 2 byte

Long Integer s nguyn byte

Single s thc byte

Double s thc 2 byte

Decimal s thc byte

d) Format

nh dng cch th hin ca d liu khi hin th hoc khi in n.

Kiu chui: gm 3 phn

[Phn 1];[Phn 2];[Phn 3]

Trong :

- Phn 1: Chui nh dng tng ng trong trng hp c cha vn bn.

- Phn 2: Chui nh dng tng ng trong trng hp khng cha vn bn.

- Phn 3: Chui nh dng tng ng trong trng hp null

Cc k t dng nh dng chui

K t Tc dng

@ Chui k t

"123ABC" Hin th nhng g trong ngoc kp nh k t

> i tt c k t nhp vo thnh in hoa

< i tt c k t nhp vo thnh in thng

\ Hin th k t k tip nh k t bnh thng

(dng hin th cc k t c bit)

V d

Cch nh dng D liu nhp vo Hin th

@@@-@@@ 2

abcdef

2 -

abc-def

> Tinhoc TINHOC

< TinHoc tinhoc

@; "Khng c"; "Cha nhp"

ABC

Null

ABC

Khng c

Cha nhp

Kiu s (Number) v kiu s tin t (Currency)

nh dng do ACCESS cung cp

Loi s D liu nhp vo Hin th

General Number 2 . 2 .

Currency 2 . ,2 .

Euro 2 . ,2 .

Fixed 2 . 2

Standard 2 . ,2 .

Pecent . 2 2.

Scientific 2 . 1.23E+0

nh dng do ngi s dng:

[Phn 1];[Phn 2];[Phn 3];[Phn 4]

Trong :

- Phn 1: Chui nh dng tng ng trong trng hp s dng.

- Phn 2: Chui nh dng tng ng trong trng hp s m.

- Phn 3: Chui nh dng tng ng trong trng hp s bng zero.

- Phn 4: Chui nh dng tng ng trong trng hp null.

V d

nh dng Hin th

0;(0);;"Null"

S dng hin th bnh thng

S m c bao gia 2 du ngoc ( )

S b b trng

Null hin ch Null

. - . .

Hin th du + pha trc nu s dng

Hin th du - pha trc nu s m

Hin th . nu m hoc Null

Kiu Date/Time


nh dng Hin th

General date 10/30/99 5:10:30PM

Long date Friday, may 30 , 1999

Medium date 30-jul-

Short date

Long time 6:20:00 PM

Medium time 6:20 PM

Short time :2

Kiu Yes/No


nh dng Tc dng

Yes / No ng Sai

True / False ng Sai

On / Off ng Sai

nh dng do ngi s dng: Gm 3 phn

;[Phn 1];[Phn 2]

Trong :

- Phn : Trng hp gi tr trng ng

- Phn 2: Trng hp gi tr trng sai

V d

nh dng Hin th

Trng hp True Trng hp False

"Nam" "N" Nam N

;"C";"Khng" C Khng

Ch : xem c hin th ca kiu Yes/No ta phi thay i thuc tnh Display Control th Lookup thnh Text Box

e) Input Mask

Mt n nh dng d liu, ngi s dng bt buc phi nhp d liu cho trng ng

theo quy nh ci t thuc tnh ny.

K t Tc dng

Bt buc nhp k t s

Khng bt buc nhp, k t s

# Khng bt buc nhp, s 0-9, khong trng, du + v -

L Bt buc nhp, k t ch

? Khng bt buc nhp, k t ch hoc khong trng

a Bt buc nhp, k t ch hoc s

A Khng bt buc nhp, k t ch hoc s

& Bt buc nhp, k t bt k

C Khng bt buc nhp k t bt k

< Cc k t bn phi c i thnh ch thng

> Cc k t bn phi c i thnh ch hoa

! D liu c ghi t phi sang tri

V d:

Input Mask D liu nhp vo

( ) - ( ) 2 - 2 2

(000)AAA-A ( 23)124-E

Ngoi ra ta c th s dng nhng Input Mask c sn do Access cung cp

f) Caption

Dng lm tiu cho cc trng trong ch Datasheet ca bng, c th dng ting

Vit. Chui k t ny cng xut hin ti nhn cc ca cc iu khin trong cc biu mu

hoc bo co.

Nu khng xc nh Caption th Access s ly tn trng lm tiu .

g) Default Value

Quy nh gi tr mc nh cho trng tr Auto number v OEL Object. C th l mt

biu thc, hng, cc hm mu v cc php ton.

h) Validation rule v Validation Text

Quy nh quy tc hp l d liu (Validation rule) gii hn gi tr nhp vo cho

mt trng. Khi gii hn ny b vi phm s c cu thng bo Validation text.

V d:

Validation rule Tc dng

0 Khc s khng

Like "*HUE*" Trong chui phi cha HUE

>= #10/10/99# and

5.2.3. Lu bng

Thc hin bi 1 trong nhiu cch sau

Ctrl+S

Click biu tng trn thanh cng c.

Menu File Save

5.2.4. Hiu chnh bng

Di chuyn trng: a con tr ra u trng

cn di chuyn, gi v ko n v tr mi.

Chn thm trng mi: Chn trng hin thi

l trng s nm sau trng c chn vo

Right Click Insert Row

Xa trng: Chn trng cn xa

Right Click Delete Row

5.3. Thit lp mi quan h gia cc bng

Bc 1: Chn biu tng Relationship hoc vo menu Tool Relationships.

Bc 2: a cc bng mun to mi quan h vo ca s Relationships bng cch ln

lt chn bng Add

Sau khi chn xong th click Close

Nu cn chn thm bng th Right Click (ti vng ca

s Relationships) Show Table

Bc 3: Trong ca s Relationships dng

chut ko v th trng lin h t bng ny

sang bng kia.

Bc 4: Xc nh cc qui tc rng buc

ca mi quan h ny bng cch chn vo

kim tra hiu lc ca rng buc ton vn

(Enforce Referential Integrity)

ng thit lp ton vn tham chiu, ngha l d liu trn

trng tham chiu ca bng con phi tng ng vi d liu tn ti trn trng ca bng

cha. Ngoi ra thit lp ny cn cho bit kiu quan h gia 2 bng

m bo ton vn d liu khi cp nht d liu gia 2 bng lin quan. Khi , nu gi tr trng kho lin kt bng 1 b thay i, ton b gi tr

trng kho lin kt bng nhiu cng b thay i theo.

m bo ton vn d liu khi xo d liu gia 2 bng lin quan. Khi , nu mt bn ghi bng c quan h 1 b xo, ton b cc bn ghi c quan

h vi bn ghi hin ti s c t ng xo bng c quan h nhiu (nu xo 1 CHA, ton

b cc con ca cha s t ng b xo khi bng CON)

Hp Relationship Type: cho bit kiu quan h gia 2 bng ang thit lp:

One To One Kiu -

One To Many Kiu -

Indeterminate Khng xc nh c kiu lin kt

Bc 5: Chn Create

Bc 6: Lu li cc mi quan h vo ca s quan h: chn menu File Save

hoc click vo biu tng trn thanh cng c.

Ch : Khi thit lp mi quan h phi ng tt c cc bng tham gia.

5.4. Cp nht bng

5.4.1. Xem v nhp d liu

C th xem v nhp d liu trong bng bng cc cch sau

Double click vo bng cn xem (hoc nhp d liu)

Click chn bng, sau click vo biu tng

5.4.2. Nhp d liu s dng Lookup

vic nhp cc gi tr cho cc thuc tnh kha ngoi c d dng, nhanh chng v

trnh sai st, c th nhp d liu cho cc thuc tnh ny dng lookup. V d bn di l

thit lp lookup cho kha ngoi MaKH ca bng SinhVien

Mn hnh khi nhp liu:

Trng kha ngoi n

bng quan h

Bng c quan h

nhiu ()

Kiu th hin d liu

Bng c tham chiu

(bng c quan h )

5.4.3. Mt s li c th xy ra khi nhp d liu

Li do: Nhp vo gi tr khng tng thch vi kiu d liu ca trng ch nh.

V d: trng kiu Numeric m g vo ch ci; hoc khng g y cc gi tr

ngy, thng, nm cho trng kiu Date/Time,..

Khc phc: Nhp li cho ng, gi tr cc trng yu cu n khi khng xut

hin thng bo li.

Li do: Khng nhp gi tr hoc trng gi tr trng kho.

Khc phc: phi nhp y gi tr cho trng kho.

Li do: Gi tr trng kho trng nhau. Gi tr trng kho va nhp vo trng

vi gi tr ca mt kha ca bn ghi khc trn bng d liu.

Khc phc: nhp li gi tr trng kho khc sao cho va ng, v khng b

trng kho.

Li do: Khng nhp d liu trng bt buc nhp d liu (nhng trng c thit

lp thuc tnh Required=Yes)

Khc phc: Phi nhp d liu cho cc trng bt buc phi nhp d liu.

L do: Li do thc hin mt thao tc vi phm cc nguyn tc m bo ton vn d liu.

V d: Nhp d liu trn mt bng c quan h m bn ghi ang nhp khng th lin kt

c ti c mt bn ghi no ca bng c quan h 1 vi n (nhp mt hng bn m m

hng cha c trong bng danh mc hng ho).

5.4.4. Xa bn ghi

Vi bng d liu ang m c th thc hin 2 bc sau xo cc bn ghi:

Bc 1: Chn nhng bn ghi cn xo. C th chn mt hoc nhiu bn ghi bng

cch dng chut nh du u dng nhng bn ghi cn chn;

Bc 2: Ra lnh xo bng mt trong cc cch sau

Nhn phm Delete.

Right Click trn vng chn Delete Record

Click vo biu tng trn thanh cng c

Mt hp thoi xut hin bn khng nh mt ln na vic xo d liu:

Chn Yes ng xo, No hu lnh xo.

5.4.5. Sp xp d liu

Sp xp l vic thay i th t hin th mt bng d liu theo mt trt t no . Kt

qu ca vic sp xp gip ngi dng c th quan st c tt hn d liu trn bng, tt

nhin mun quan st bng d liu theo trng no phi thc hin sp xp bng theo d liu

trng y.

Cch sp xp d liu trn bng ang m nh sau:

Bc 1: t con tr ln trng (ct) mun sp xp;

Bc 2: Nhn nt lnh sp xp trn thanh cng c:

- Sp xp tng dn

- Sp xp gim dn.

2

5.4.6. Lc d liu

Lc d liu l vic chn ra nhng bn ghi trn bng c cng mt s gi tr. Kt qu

vic lc d liu s gip ngi dng lm vic mt cch hiu qu trn tp hp cc bn ghi h

mong mun.

Cc bc lc d liu trn mt bng ang m nh sau:

Bc 1: Click chn trng cn lc d liu.

Bc : Click chn biu tng Filter by Form

Bc 3: Thit lp iu kin lc trn trng ang chn

CHNG 6. TRUY VN (QUERY)

6.1. Cc khi nim

Sau khi xy dng c CSDL, ngi dng s cn kt xut d liu trn CSDL nhm

phc v cho nhng yu cu khc nhau. Mt trong nhng cng c x l d liu m MS

Access cung cp l Query. Query cho php thc hin cc php ton i s quan h nh

php chiu, chn kt, gom nhm.

C cc loi query sau trong Access:

Query truy vn:

Select Query

Group by Select Query

Crosstab Query

Query thm, xa, sa d liu:

Append Query

Delete Query

Update Query

Make Table Query

6.2. Query truy vn

6.2.1. Select Query (truy vn vi php chn, chiu, kt ni)

Phn ny minh ha li cch thc hin Query cho cc v d v cc php chn, chiu,

kt ni trong chng v i s quan h.

V d 01: Cho bit tn khoa ca sinh vin c m s 2 2. Lit k cc thng tin

MaSV, HoSV, TenSV, TenKhoa.

Biu thc i s quan h cho truy vn trn:

MaSV, HoSV, TenSV, TenKhoa [ MaSV=5202 (SinhVien MaKH = MaKhoa Khoa)]

To truy vn trong Access:

Trong mn hnh c s d liu, chn mc Queries, sau chn Create query in

Design view m mn hnh Show Table.

Trong mn hnh Show Table, chn v nhn nt Add cc table tham gia truy vn

(Khoa v SinhVien). Sau khi chn xong, nhn nt Close tr v mn hnh Design Query.

Mn hnh design Query gm hai phn:

Phn A dng th hin cc bng tham gia truy vn. Phn ny cng th hin mi

quan h gia cc bng v mi quan h c s dng lm php kt ni bng.

Phn B gm nhiu ct dng thc hin cc php chn v chiu. Mt ct trong

phn B tng ng vi mt ct trong php chiu.

Ko th (hoc Double-Click) cc trng lin quan n truy vn t phn A vo cc

ct phn B. Du * i din cho tt c cc ct trong bng.

Nhn nt View Design chuyn i gia ch thit k

Query v ch xem kt qu thc hin php kt ni gia bng

SinhVien v Khoa:

A

B

Cc trng (ct)

c chn Bng tham gia

truy vn

Vng nhp iu kin ca php chn

Sp xp

Cho php (khng cho php)

hin th kt qu

B sung php chn v php chiu:

Ko bn ct MaSV, HoSV, TenSV, TenKhoa vo phn B.

Thc hin php chn MaSV = ' 2 2': g mnh = ' 2 2' vo dng Criteria

ng vi ct MaSV.

Kt qu thc hin:

V d 02: To ct mi (HoTen) t 2 trng HoSV v TenSV

Kt qu thc hin:

Ch : Tn ct mi t dng Field c cu trc nh sau :

V d: HoTen : [HoSV] + ' ' + [TenSV]

V d 0 : Biu thc chn c mnh AND

Xt cu truy vn: Lit k cc sinh vin sinh nm v c hc bng trn 500.

Year(NgaySinh)=1990 AND HocBong>300 (SinhVien)

Trong cu truy vn ny, biu thc logic gm 2 phn: Year(NgaySinh)=1990 v

HocBong > c t phn Criteria:

Ch : Year([NgaySinh]) ging nh l mt thuc tnh mi nhng khng c hin ra do khng c chn dng Show.

Xt cu truy vn: Lit k cc sinh vin t mc hc bng t n 500

(HocBong>=300 AND HocBong

V d 0 : Lit k cc mn hc cha m lp:

iu kin NOT IN (SELECT MaMH FROM LopHoc) ngha l: trong bng mn hc,

hy tm cc mn m MaMH khng c trong bng LopHoc. Kt qu:

V d 0 : Sp xp v la dng

Xt cu truy vn: Chn hai sinh vin t mc hc bng cao nht.

Trc tin sp xp danh sch sinh vin theo hc bng t cao n thp:

Kt qu sp xp:

Sau trong mn hnh Design, trn thanh cng c, thay t All thnh 2:

Kt qu thc hin:

6.2.2. Group by Select Query (truy vn vi php nhm)

V d 0 : Xt cu truy vn: da vo bng SinhVien, m s sinh vin v tnh tng

hc bng theo tng khoa:

Trong Access to Group by Select Query, u tin to mt Select Query nh bnh

thng. Sau nhn vo nt trn thanh cng c xut hin dng Total.


5202 L Cng 21/5/1 1 Nam 200 KT

5207 Nguyn Sn 17/7/1 0 N 800 KT

520 Ng Ha 16/8/1 0 Nam 200 CN

5306 Hunh Nga 22/3/1 2 N 400 CN

5308 Phm Trang 12/7/1 0 N 800 CN

MaKhoa SoSV TongHB

KT 2 1.000

CN 3 1.400

2

3

Tch nhm

theo m khoa

m s SV

trong nhm

Sau a ba trng MaKh, MaSV, HocBong vo ba ct bn di.

Trng MaKh dng tch nhm, nn trong dng Total chn l Group By.

Trng MaSV dng m s lng sinh vin nn trong dng Total chn hm Count.

Trng HocBong dng tnh tng hc bng nn trong dng Total chn hm Sum.

Kt qu thc hin.

V d 0 : B sung thm trng TenKhoa.

Kt ni bng SinhVien vi bng Khoa, sau chn trng MaKhoa, TenKhoa l

Group by

V d 0 : Tnh s sinh vin n ca tng khoa

Cu truy vn ny cng tng t nh cu tnh tng s sinh vin ca tng khoa, tuy

nhin trc khi tch nhm th phi thc hin php chn lc ra cc sinh vin n

y, trng GioiTinh ch dng chn ra cc sinh vin n, khng dng tch

nhm, do dng Total ca ct GioiTinh chn Where (khng chn Group By).

Kt qu thc hin

V d 10 Da vo cc bng SinhVien, LopHoc, KetQua v MonHoc, tnh im

trung bnh hc k nm 2 ca cc sinh vin. Lit k: MaSV, HoSV, TenSV, DTB.

tam NamHoc=2010 AND HocKy= 1 (LopHoc MonHoc KetQua)

MaSV, HoSV, TenSV G DTB: Sum(Diem*SoTinChi)/Sum(SoTinChi) (tam)

Trong v d trn, v cch tnh im trung bnh l mt cng thc ton hc nn dng

Total ct DTB:Sum([Diem]*[SoTinChi])/Sum([SoTinChi]) c chn l Expression

(ngha l mt biu thc).

Kt qu thc hin

Tm tt Chc nng Total cung cp cho ngi dng cc phng thc thng k sau: Group By: Gom nhm cc trng cn thng k.

Sum: Tnh tng cc gi tr trong cng mt nhm

Count: m s phn t trong mt nhm

AVG: Tnh trung bnh cng cc phn t trong cng mt nhm

Min: Tnh gi tr nh nht ca cc phn t trong cng mt nhm

Max: tnh gi tr ln nht ca cc phn t trong cng mt nhm

Expresion: Biu thc ton hc.

6.2.3. Crosstab Query

L dng query tng hp s liu kt nhm theo hng v ct t s liu ca cc table

hay query khc.

Cu trc ca 1 kt qu crosstab query

Row heading: l tiu cc dng, c cha cc gi tr ca hay nhiu trng

lm i tng thng k. Mi crosstab query phi c ti thiu 1 trng lm Row heading.

Column heading l tiu cc ct, c cha cc gi tr ca mt trng lm iu

kin thng k. Mi crosstab query ch c duy nht 01 trng lm Column heading.

Value l vng d liu tng hp (kiu s). Ch c duy nht mt trng lm

Value, tng ng vi n l cc hm thng k: Sum, Count, Avg, Max, Min

V d 11: Thng k im s cui cng ca tng sinh vin theo tng mn hc:

Kt qu ca php kt:

MaSV, Ho,Ten, MaLop, TenMH, Diem (SinhVien KetQua LopHoc MonHoc)

MaSV HoTen MaLop TenMH Diem

5308 Phm Trang 01 Tin hc c s 3

5308 Phm Trang 03 Tin hc c s

5308 Phm Trang 02 K ton ti chnh

5202 L Cng 01 Tin hc c s 4

5202 L Cng 03 Tin hc c s 8

5202 L Cng 04 Cu trc d liu 7

5202 L Cng 02 K ton ti chnh

MaSV HoTen Cu trc d liu K ton ti chnh Tin hc c s

5202 L Cng

5308 Phm Trang

to bng crosstab, trc tin to mt Group By Select Query. Sau chuyn

thnh Crosstab Query bng cch vo menu Query Crosstab Query

Value

Row heading

Column heading

Max(3, 9)

Max(4, 8)

2

Phn thit k Query xut hin thm dng Crosstab.

- MaSV, HoTen sp theo chiu dc nn ti dng total l Group By, ti dng

crosstab chn l Row Heading.

- TenMH sp theo chiu ngang nn ti dng total l Group By, ti dng crosstab

chn l Column Heading.

- Diem l gi tr ca bng nn ti dng total l Max, ti dng crosstab chn l

Value.

Kt qu thc hin:

6.3. Query thm, xa, sa

6.3.1. Make Table Query

Trong cc phn trn, truy vn c to theo hai ch : ch thit k (Design) v

ch xem kt qu (View). c th lu kt qu ca query nhm mc ch s dng sau

ny, Access cung cp Make-Table Query.

Make-Table Query hon ton ging nh mt Select Query bnh thng, ch khc l

cho php lu kt qu thnh mt bng.

V d 12: Tnh s sinh vin theo tng Khoa v lu vo bng DemSinhVien.

Trc tin thit k Select query m s sinh vin theo tng Khoa nh bnh thng.

Sau vo menu Query Make-Table Query.

Sau nhp vo tn ca bng cn lu kt qu

chnh thc lu kt qu vo bng DemSinhVien, click vo biu tng Run (biu

tng View vn c tc dng xem trc kt qu nh bnh thng)

Sau khi nhn nt Run, chn Yes hp thoi tip theo. Quay tr li mn hnh xem

Table ca database, xut hin bng DemSinhVien.

6.3.2. Update Query

Sau khi nhp d liu vo cc table, nu mun thay i d liu, mt cch thc hin

l m cc table cp nht trc tip trn cc table . Tuy nhin nu d liu cn sa i

nhiu, th vic cp nht tn nhiu cng sc. Trong thc t, vic thay i d liu thng

theo mt quy tc no . V d: tng hc bng cc sinh vin thm 50%, hay b sung cc

mn hc thm mt tn ch.

Access cung cp query update c th thc hin vic cp nht mt cch t ng.

V d 1 : Tng hc bng cho cc sinh vin n thm 50%.

thc hin truy vn ny, trc tin to mt Select Query cho bng SinhVien vi 2

trng c chn l HocBong v GioiTinh

Sau vo menu Query Update Query.

Sau khi chn kiu query l Update Query, phn thit k xut hin dng Update To.

- Ct HocBong l ct s phi cp nht d liu mi, do trong dng Update to,

nhp vo biu thc [HocBong]*1,5

- Ct GioiTinh, nhp iu kin chn l No chn ra cc sinh vin n tng hc

bng (Gi s khi thit k bng SinhVien ta thit lp gi tr No trng GioiTinh ch

sinh vin n)

Sau khi thit k truy vn xong, click Run tht s cp nht d liu (biu tng

View xem trc cc dng c chn).

Nhn nt Yes cp nht. Kt qu ca bng SinhVien sau khi update l:

6.3.3. Delete Query

Tng t nh Update Query, Delete Query cng c dng xa cng lc nhiu

dng trong bng. Cc dng cn xa phi tha mn mt iu kin no .

V d 14: Hy b kt qu ca lp hc c m .

MaSV MaLop Diem

5202 01 4

5202 02 7

5202 03 8

5202 04

5308 01 3

5308 02

5308 03

thc hin truy vn ny, trc tin ta to mt Select Query cho bng KetQua vi

trng c chn l MaLop

Sau vo menu Query Delete Query.

dng Criteria nhp gi tr m mn hc cn xa l . Click Run, sau click

Yes, Access s tin hnh xa trong bng KetQua.

6.3.4. Append Query

Tng t nh Make-Table Query, Append Query cng c dng lu li kt qu

truy vn. im khc nhau l Make-Table Query to ra mt bng mi. Cn Append th lu

kt qu vo mt bng c sn.

V d, lu tr im trung bnh ca tng sinh vin trong tng hc k (xem v d

10), chng ta c th thit k thm mt bng DiemTrungBinh nh sau:

Sau lu tr im trung bnh ca tng sinh vin trong hc k nm hc 2010,

thc hin truy vn nh trong v d 10 v chuyn thnh Append Query, vo menu Query

Append Query.

Mt hp thoi xut hin yu cu cho bit bng d liu ch:

Thc hin ch nng Run tng t nh cc query Update, Delete. Kt qu ca truy

vn s c lu vo table DiemTrungBinh. Sau c th lu tr tip im trung bnh ca

cc hc k khc.

6.4. Truy vn c tham s

Xt cu truy vn: Lit k cc sinh vin t mc hc bng t n

( HocBong>=300 AND HocBong

CHNG . BIU MU (FORM)

7.1. Khi nim Form

Form l mt thnh phn trong CSDL Access h tr thit k giao din gia ngi s

dng v CSDL.

C hai cch xy dng Form trong Access:

S dng Form Wizard: y l cch rt n gin, nhanh chng, d dng gip

to nhanh mt Form. Tuy nhin Form c to ra theo nhng mu (templates)

c sn nn ch c th p ng nhng nhu cu n gin.

S dng Form Design View: mt cng c gip to ra cc form p ng nhiu

yu cu khc nhau ca ngi s dng,

Ch : Thng thng nn s dng Form Wizard hnh thnh cc Form n gin sau s dng tip Form Design View tip tc thit k.

7.2. S dng Form Wizard to Form

Phn ny trnh by vic to Form bng cch s dng Form Wizard vi v d l trnh

by v nhp liu cho bng SinhVien.

Bc 1: Trong ca s CSDL ang s dng,

Chn Forms Create form by using wizard.

Bc 2: Chn thng tin cn a ln Form

Ti mc Tables/ Queries chn Table: SinhVien

Chn bng (table) hay truy vn (query) cn thit cho Form

Vic chn table SinhVien c ngha l Form s tng tc vi cc gi tr trong cc ct

ca bng SinhVien. Trong Available Fields hin th cc trng c trong bng SinhVien m

cha c chn hin th trn Form. Trong Selected Fields cha cc trng s hin th

trn Form.

chn mt vi trng vo Selected Fields, chn trng Available Fields ri click

. ly tt c cc trng t Available Fields, click .

Ngc li, cc nt v loi b mt vi hoc tt c cc trng

trong Selected Fields.

Sau khi chn c cc trng cn thit vo Selected Fields, click Next

Bc 3: Chn kiu trnh by (layout) ca Form

Ti giao din ny, Access cung cp mt s mu (template) cho vic hin th d liu

ln Form. C 4 kiu chnh

- Columnar: hin th d liu ca mt bn ghi trn mt trang Form.

- Tabular: hin th d liu ca tt c cc record trn mt trang Form

- Datasheet: hin th d liu ca tt c cc bn ghi trn mt trang Form di dng

bng (tng t nh bng c m dng Open)

- Justified: Phn b d liu ca bn ghi trn Form theo cu trc nh sn ca Access.

Thng thng chn Columnar Next

Bc 4: Chn kiu dng (Style) cho form.

Trong giao din ny Access cung cp mt danh sch cc kiu dng. Hy th tng

mu v chn cho Form mt kiu dng ph hp.

Thng thng chn Standard Next

Bc 5: Hon thin cng vic

- t tiu hin th cho Form, tiu ny cng s l tn ca Form (tuy nhin tn

Form c th c thay i ca s cc i tng CSDL m khng nh hng n tiu

Form).

- La chn Open the form to view or enter information: hin th Form sau khi click Finish

- La chn Modify the forms design: m giao din Form Design chnh sa Form.

Nu la chn hin th Form th:

Chnh sa li tn cc nhn, tiu trong Form: click biu tng thit k Form

Xem/Thit

k Form

Hin th bn

ghi u tin

Hin thi bn

ghi trc

Hin th bn

ghi tip theo

Hin th bn

ghi cui cng

Thm bn ghi

mi

7.3. S dng Design View to Form

to ra c form mang tnh chuyn nghip v p ng c st yu cu thc t

bt buc phi s dng n Form Design View. Ni nh vy khng c ngha l khng dng

Form Wizard to form, m trong nhiu tnh hung vn dng Form Wizard ri s dng

n Form Design View tip tc hon thin yu cu.

To form mi ngay t u

Chn Forms Create form in Design view.

Thit k li form to trc bng Form Wizard

Chn Form click vo biu tng thit k

2

Mn hnh thit k form xut hin nh sau

1) Ca s thuc tnh (Properties): m t chi tit v i tng ca form ang c thit k

Ch : - Ngun d liu c s dng hin th trn form c khai bo th All ca

i tng Form

- Mun thay i nh dng ca i tng trn form (v d: font ch, mu nn,

mu ch) th chn i tng tinh chnh ca s thuc tnh.

2) Thanh cng c Toolbox: ni cha nhng i tng, nhng cng c c th a ln

form vi mc ch thit k giao din v iu khin d liu theo mc ch thit k.

3) Field List: hin th danh sch cc trng ca ngun d liu c khai bo ca s

thuc tnh. Mun hin th d liu ca trng no trn form ch cn ko trng

trong Field List vo Form Design.

Phn tiu

u

Phn thn

form

Phn tiu

cui

Danh sch cc trng

ca ngun d liu

(Table/Query)

Bt/Tt ca s

ngun d liu

Bt/Tt ca s

Toolbox

Bt/Tt ca s

thuc tnh ca form

Ca s thuc tnh

ca form

Ca s cc

cng c thit k

(Toolbox)

Ngun d liu

i tng ang

thit k

7.4. Main-Sub Form

t vn : cn xy dng mt Form hin th thng tin cc Khoa, khi mt Khoa c

hin th th phi hin th lun danh sch sinh vin thuc Khoa .

Trong trng hp ny, Access cung cp k thut to form c th tha mn yu cu

trn, form c to ra c gi l Main-Sub form. C th hiu Sub form l form ny lng

trong form kia (c th lng trong nhau nhiu lp). Form cha gi l form cha (Main form);

form c lng vo gi l form con (Sub form). Vic x l d liu trn tng form c th

x l c lp hoc c quan h vi nhau tu theo mc ch cng vic.

V d: cc bc to mt mt Main-Sub form cho bng Khoa v SinhVien nh hnh

bn di:

Cch 1: To Main Form v Sub Form ring ri lin kt li vi nhau:

Bc 1: To Form hin th cho bng Khoa bng Wizard v chn Style Columnar.

t tn cho Form ny l Khoa_MainForm

Bc 2: To Form hin th cho bng SinhVien bng Wizard v chn Style DataSheet, tn

l SinhVien_SubForm. Form ny c hnh dng tng t nh phn DataView ca table

SinhVien.

Bc 3: M Form Khoa ch Design View M rng phn Detail Ko

Form SinhVien_SubForm vo phn trng va c to ra.

Bc 4: iu chnh giao din v chuyn qua Form View

Trong Sub Form SinhVien hin th cc sinh vin thuc Khoa Cng ngh Thng tin.

Giao din Main-Sub nh trn trnh by nhng sinh vin trong tng khoa. ng thi

c th tin hnh thm, xa, sa thng tin SinhVien.

Cch 2: To Main-Sub Form cng lc:

Bc 1: S dng form Wizard, chn cc thuc tnh ca bng Khoa:

Bc : Chn tip cc thuc tnh ca bng SinhVien:

Bc : trong mc ny chn l by Khoa to Main-Sub form (nu chn by

SinhVien th ch l form thng)

Bc : trong mc ny chn l by Khoa to Main-Sub form. Access t ng

hiu rng Khoa l Main-Form cn SinhVien l Sub-Form.

Bc 4: Chn kiu cho Sub form l Datasheet

Bc 5: Lu li tn ca form Main v form Sub

Kt qu t c cng tng t nh vic to 2 form ring ri lin kt li vi nhau:

7.5. S dng nt lnh (Command Button)

Nt lnh l mt biu tng trn form m khi click cht vo th form s thc hin

mt cng vic no , v d: khi nhn ln nt

- Mt bng lng s c in ra.

- Form ang lm vic s c ng li.

Access cung cp tnh nng Control Button Wizard gip ngi thit k to mt s

loi nt lnh m khng cn bit nhiu n lp trnh. Di y l cc bc s dng:

V d: To nt

Bc : M form ch Design v m ca s cc cng c thit k (Toolbox).

Ch phi kch hot tnh nng Control Wizard ca toolbox

Bc : M rng phn Detail ca Form, chn biu tng to nt lnh trn

Toolbox, dng chut v nt lnh v tr thch hp

Control Wizard

Bc : Mt hp thoi xut hin yu cu chn hnh ng cho nt lnh

- Categories: cha cc nhm thao tc m mt nt lnh c th nhn

- Actions: cha danh sch cc lnh ca mi nhm.

Nhm / Lnh ngha

Record Navigation nh hng tr chut trn bn ghi

- Go To First Record - Chuyn v bn ghi u

- Go To Last Record - Chuyn n bn ghi cui cng

- Go To Next Record - Chuyn n bn ghi k sau

- Go To Previous Record - Chuyn v bn ghi k trc

Record Operations Cc x l vi bn ghi

- Add New Record - Thm bn ghi mi

- Delete Record - Xo bn ghi hin ti

- Update Record - Cp nht bn ghi hin ti

- Undo Record - Phc hi thay i d liu bn ghi

Form Operations Cc x l vi Form

- Close Form - ng form

- Open Form - M mt form khc

Report Operations Cc x l vi Report

- Preview report - Xem trc ni dung (Preview) report

- Print report - In report

- Send report to file - Xut report ra mt tp tin khc

Applications X l ng dng

- Quit Application - Thot khi Access

- Run Application - Chy mt ng dng no (v d, file .exe)

Miscellaneous Mt s thao tc khc

- Print table - In ni dung mt bng

- Run macro - Thi hnh mt macro

- Run query - Thi hnh mt query

Vi yu cu ng form th chn: Categories l Form Actions v Actions l Close Form.

Documents

Gt Tinhocungdung