Principles of Database Design

Principles of Database Design

GING VIN: TS.L TH LAN Thng tin TS. L Th Lan Vin nghin cu quc t MICA, i hc Bch Khoa H Ni Email: [email protected] Web: http://www.mica.edu.vn/perso/LeThi-Lan/Database Ni dung mn hc Chng 1: i cng v cc h c s d liu Chng 2: Cc m hnh d liu Chng 3: Ngn ng nh ngha v thao tc d liu (SQL) Chng 4: Ph thuc hm v chun ha CSDL quan h Chng 5: Tch hp c s d liu trong cc ng dng Php + Mysql

C# + SQL server .. K hoch hc tp L thuyt Bi tp trn lp, bi tp tun (c nhn, nhm sinh vin) nh gi gia k Bi tp ln tham gia vo im nh gi cui k Thi hc k nh gi gia k Thc hin theo nhm. Mi nhm (3 sinh vin theo danh sch) lm 1 bi tp. Cn: Hon thnh bi tp c giao trong thi gian 1 tun (nhn th nm hng tun v trnh by vo th nm tun tip theo)

Phn chia cng vic r rng Chun b slides v trnh by v bi tp cho cc bn trong lp nh gi im cho cc thnh vin da vo cng vic ca tng thnh vin Ti liu tham kho Nguyn Kim Anh, Nguyn l ca cc h c s d liu, NXB i hc quc gia H Ni. SQL Unleashed, Second Edition by Sakhr Youness Sams, Macmillan Computer Publishing Database Management Systems, Second Edition, Raghu Ramakrishnan and Johannes Gehrke Google CHNG 1

I CNG V H C S D LIU Gii thiu D tha, khng nht qun, d thng trong truy nhp ng thi, Gii thiu (tip) 1. Khi nim C s d liu (database) L mt b su tp cc d liu tc nghip c lu tr li v c cc h ng dng ca mt x nghip c th no s dng V d: c s d liu sch ca th vin

i hc Bch Khoa H Ni, c s d liu hng ha ca siu th 1. Khi nim H qun tr c s d liu (database management system) "L mt h thng phn mm cho php to lp c s d liu v iu khin mi truy nhp i vi CSDL ." V d: M. Access, SQL server 1. Khi nim 4 thnh phn ca mt h c s d liu (database system) C s d liu hp nht Ngi s dng Phn mm qun tr c s d liu Phn cng

2. H qun tr c s d liu H qun tr c s d liu c kh nng: Kh nng qun l d liu tn ti lu di Kh nng truy nhp cc khi lng d liu ln mt cch hiu qu 2. H qun tr c s d liu Cc c im ca h qun tr c s d liu: H tr t nht mt m hnh d liu m bo tnh c lp d liu H tr ngn ng cp cao Qun l giao dch iu khin truy nhp Phc hi d liu 2. H qun tr c s d liu Khi nim:

S c s d liu Th hin ca s c s d liu 2. H qun tr c s d liu Cc thay i s Cc thay i truy vn B x l cu hi B qun l lu tr D liu siu d liu

Cc thay i d liu B qun l giao dch 2. H qun tr c s d liu Cc mc tru tng Mc vt l Mc logic Mc khung nhn Cc mc c lp d liu Mc vt l Mc logic (kh t c) Cc mc tru tng

2. H qun tr c s d liu Cc ngn ng c s d liu Ngn ng nh ngha d liu (Data Definition Language-DDL) Ngn ng thao tc d liu (Data Manipulation Language - DML) Tm kim thng tin Thm thng tin mi Xa thng tin Thay i thng tin 2. H qun tr c s d liu Ngi dng Ngi qun tr c s d liu nh ngha s , xc nh cu trc lu tr, y quyn, xc nh rng buc ton vn Ngi s dng c s d liu

Lp trnh ng dng Nh phn tch d liu Ngi s dng cui V d 3. Phn loi cc h c s d liu H c s d liu tp trung H c s d liu c nhn H c s d liu trung tm H c s d liu khch ch H c s d liu phn tn H c s d liu phn tn thun nht H c s d liu phn tn khng thun nht C s d liu tp trung C s d liu trung tm

C s d liu phn tn 3. H c s d liu phn tn H c s d liu phn tn thun nht H iu hnh nh nhau M hnh d liu nh nhau H qun tr c s d liu chung D liu c nh ngha v c khun dng chung H c s d liu phn tn khng thun nht So snh cc h qun tr CSDL quan h Tn Cng ty Bn u tin

Bn quyn ADABAS 1970 Software AG Proprietary Microsoft Access 1992 Microsoft Proprietary

Microsoft SQL Server 1989 Microsoft Proprietary MySQL 1995 Sun Microsystems GPL or Proprietary PostgreSQL

1989 PostgreSQL Global Development Group BSD SQLite 2000 D. Richard Hipp Public domain Oracle

1979 Oracle Corporation Proprietary http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems So snh cc h qun tr CSDL quan h Tn Kch thc ADABAS N/A Microsoft Access

2 GB Microsoft SQL Server 524,258 TB (32,767 files * 16 TB max file size) MySQL Unlimited PostgreSQL Unlimited SQLite

32 TB (230 pages * 32 KB max page size) Oracle Unlimited (4 GB * block size per tablespace) CHNG 2 CC M HNH D LIU 1. Tng quan Quy trnh pht trin ng dng CSDL Source: Data Modeling Using Entity-Relationship Model, NGUYEN Kim Anh

Data modelling M hnh ha d liu Mc ch: M t d liu s lu tr trong c s d liu (v d cc thc th nh sinh vin, bi hc,) M t mi quan h gia cc d liu M t cc rng buc i vi d liu M hnh d liu l : Mt s hnh thc ha ton hc vi mt tp k hiu m t d liu Mt tp cc php ton c s dng thao tc cc d liu 2. M hnh thc th lin kt (Entity Relationship Diagrams) Thc th Thuc tnh

Lin kt Thc th (Entity) L mt i tng c th hay tru tng trong th gii thc m n tn ti v c th phn bit vi cc i tng khc V d: sinh vin, cn b Tp thc th: nhm cc thc th ging nhau real customers customer surrogates Tn ca thc th l duy nht Thuc tnh (Attribute) Thuc tnh l mt thng tin v thc th.

Mt thuc tnh c mt min gi tr Thuc tnh (tp thuc tnh) kha l thuc tnh (hay tp thuc tnh) m gi tr ca n c xc nh duy nht cho mi thc th V d: S chng minh th nhn dn l thuc tnh kha cho nhn vin trong mt x nghip Username v password l tp thuc tnh kha cho mt ti khon th V d H tn M s Gii tnh

Nhn vin Tui Ngy sinh Thc th yu (Weak entity) -L thc th ph thuc vo thc th khc -Lin kt nh danh +l lin kt gia thc th yu v thc th nh danh n Thc th yu (Weak Entity) VD: -Thn nhn l thc th yu -Nhn vin l thc th nh danh ca thn nhn -Ph thuc l lin kt nh danh

Thc th yu(Weak Entity) Gii tnh Quan h Ngy sinh M s Nhn vin Ph thuc Thn nhn Tn Lin kt 1-1 femalecustomer

1 current marriage 1 malecustomer partial functions A partial function from X to Y is a function : X' Y, where X' is a subset of X Lin kt 0-N v 1-N N

1 airport from partial function flt-schedule Lin kt bt buc 0-N v 1-N N 1 airport from total function

flt-schedule Lin ht N-M (nhiu-nhiu) customer N reservation M flt-instance Lin ht N-M (nhiu-nhiu) N Thi u cho

i no Cu th Mc lng tn Th trng Quc tch N i bng

tn Sn nh Ti chnh Lin kt lp out flt-schedule connection in departure arrival time

time flt# Lin kt is-a A is-a B nu B l s tng qut ha ca A Th lc Tc Cu th Tn L-mt MNV

Nhn vin Lng 2. M hnh quan h Tch cc (Cartesian Product): Tch cc () ca n tp l tp tt c cc cch kt hp ca cc thnh phn trong n tp. Min gi tr (Domain) ca mt thuc tnh l tp tt c cc gi tr c th ca mt thuc tnh. K hiu min gi tr ca thuc tnh A l dom(A). 3. M hnh quan h S quan h (Relation Schema) biu din R(A1, A2, , An) l mt quan h R v danh sch cc thuc tnh A1, A2, , An. Quan h (Relation): l mt tp con ca tch cc trong min gi tr.

S quan h R, Quan h r Tp cc thuc tnh A1..An r(R) (dom(A1) dom(A2) ... dom(An)) 3. M hnh quan h (tt) B quan h ((N)-tuple): l mt tp cc cp thuc tnh-gi tr biu din mt th hin ca quan h. Bc quan h (Degree) : l s thuc tnh ca quan h. Lc lng ca quan h (Cardinality): l s b ca quan h. 3. M hnh quan h (tt) Kha (Key): l mt tp thuc tnh m cc gi tr ca n xc nh duy nht mt b trong quan h. t1 v t2 l 2 b trong quan h r trn m hnh quan h R sk l mt tp cc thuc tnh kha

t1[sk] t2[sk]. Kha ti thiu (Candidate key): l tp thuc tnh kha nh nht. Mt quan h c th c nhiu tp kha ti thiu. Kha chnh (primary key): l kha ti thiu c la chn lm kha cho quan h. Kha ngoi (Foreign Key): l kha ti thiu ca quan h A v c s dng trong quan h B. C s d liu: L mt tp quan h 3. M hnh quan h - V d Courseno Subject Lecturer Machine

CS250 Programming Lindsey Sun CS260 Graphics Hubbold Sun CS270

Micros Woods PC CS290 Verification Barringer Sun Course(Courseno, Subject, Lecturer, Machine) CHNG 3 NGN NG NH NGHA

V THAO TC D LIU 1. Ngn ng nh ngha d liu Cho php nh ngha: S i vi mi bng Kiu d liu hay min gi tr Cc rng buc ton vn Tp cc ch dn Thng tin an ton v y quyn i vi tng bng Cu trc lu tr vt l

Thi-Lan Le, MICA HUST 52 1. Ngn ng nh ngha d liu Kiu d liu: D liu xu k t: D liu s:

Char(n) Varchar (n) : di thay i Int or Integer (t -2,147,483,648 n 2,147,483,647) Smallint (t -32,768 n 32,767) Numeric (p,s) Real, double precision Float (n) D liu ngy thng Date: YYYY-MM-DD

Time: HH:MM.SS.MMMM Thi-Lan Le, MICA HUST 53 1. Ngn ng nh ngha d liu To bng trong CSDL: CREATE TABLE ( [NOT NULL], [CONSTRAINT ]) Thi-Lan Le, MICA HUST 54 1. Ngn ng nh ngha d liu To bng Xc nh kha chnh

Thi-Lan Le, MICA HUST 55 1. Ngn ng nh ngha d liu Thm - Xa ct trong bng Thi-Lan Le, MICA HUST 56 1. Ngn ng nh ngha d liu Xa bng: C php: DROP TABLE relation_name V d: DROP TABLE branch

Thi-Lan Le, MICA HUST 57 2. Ngn ng thao tc d liu Mt s t kha General Struc ture SELECT, ALL / DISTINCT, *, AS, FROM, WHERE Comparison IN, BETWEEN, LIKE "% _" Grouping

GROUP BY, HAVING, COUNT( ), SUM( ), AVG( ), MAX( ), MIN( ) Display Order ORDER BY, ASC / DESC L ogic al Operators AND, OR, NOT Output INTO TABLE / CURSOR TO FILE [ADDITIVE], TO PRINTER, TO SCREEN

Union UNION Thi-Lan Le, MICA HUST 58 2. Ngn ng thao tc d liu V d: Bng student lu cc thng tin v sinh vin Thuc tnh Kiu d liu rng Gii thch id name dob sex class hcode

dcode remission mtest numeric character date character character character character logical numeric Thi-Lan Le, MICA HUST 4 10Tn

8 1 2 1 3 1 2 S hiu sinh vin sinh vin Ngy thng nm sinh Gii tnh (M / F) Tn lp M nh (R, Y, B, G) M qun Quyn nhp hc (T, F) im thi ton

59 2. Ngn ng thao tc d liu D liu trong bng student id name 9801 Peter 9802 Mary 9803 Johnny 9804 Wendy 9805 Tobe : : dob 06/04/86 01/10/86

03/16/86 07/09/86 10/17/86 : Thi-Lan Le, MICA HUST sex M F M F M : class 1A 1A

1A 1B 1B : mtest 70 92 91 84 88 : hcode R Y G B

R : dcode SSP HHM SSP YMT YMT : remission .F. .F. .T. .F. .F. :

60 2. Ngn ng thao tc d liu Thm mt b d liu (~ 1 dng) vo 1 bng trong CSDL Cch 1: INSERT INTO Table_name VALUES (value 1, value 2, , value n) Cch 2: INSERT INTO Table_name (column 1, column 2, , column n) VALUES (value 1, value 2, , value n) Cu hi: nh gi c im cng nh u, nhc im ca tng cch Thi-Lan Le, MICA HUST 61

2. Ngn ng thao tc d liu SELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2] ; FROM tablename WHERE condition Truy vn la chn cc dng trn cc bng c tn tablename v tr kt qu di dng bng Biu thc expr1, expr2 c th : mt ct hoc mt biu thc gm cc hm v cc trng col1, col2 l tn cc ct trong bng kt qu DISTINCT: b ht cc b lp trong kt qu trong khi t kha ALL th d li ton b cc b lp

condition c th l : Ton t so snh >, <, = hoc Ton t so snh xu Ton t logic AND, OR, NOT Thi-Lan Le, MICA HUST 62 2. Ngn ng thao tc d liu Yu cu 1: Lit k tt c sinh vin Cu truy vn: SELECT * FROM student Kt qu id name 9801 Peter

9802 Mary 9803 Johnny 9804 Wendy 9805 Tobe : : Thi-Lan Le, MICA HUST dob 06/04/86 01/10/86 03/16/86 07/09/86 10/17/86 : sex

M F M F M : class 1A 1A 1A 1B 1B : mtest 70 92

91 84 88 : hcode R Y G B R : dcode SSP HHM SSP YMT

YMT : remission .F. .F. .T. .F. .F. : 63 2. Ngn ng thao tc d liu Yu cu 2: Lit k tn , m nh v lp ca sinh vin

lp 1A Cu truy vn: SELECT name, hcode, class FROM student WHERE class=1A Kt qu Thi-Lan Le, MICA HUST name Peter Mary Johnny Luke Bobby Aaron :

hcode R Y G G B R : class 1A 1A 1A 1A 1A 1A :

64 2. Ngn ng thao tc d liu Yu cu 3: Lit k m qun ca sinh vin c m nh l R (Red house) Cu truy vn: SELECT DISTINCT dcode FROM student WHERE hcode=R Nhn xt: Ti sao cn t kha DISTINCT dcode Kt qu Thi-Lan Le, MICA HUST HHM KWC

MKK SSP TST YMT 65 2. Ngn ng thao tc d liu Yu cu 4: Lit k tn ca cc n sinh lp 1B Cu truy vn: SELECT name FROM student WHERE class=1B AND sex=F Kt qu name Janet Sandy Mimi

Bi tp: 1) Lit k tn n sinh ca lp 1A v 1B 2) Lit k tn n sinh ca lp 1 A v nam sinh lp 1 B Thi-Lan Le, MICA HUST 66 2. Ngn ng thao tc d liu Yu cu 5: Lit k tn, id ca sinh vin khng c min ph ng k ca lp 1A Cu truy vn: SELECT name, id, class FROM student WHERE class=1A AND NOT remission

Kt qu Thi-Lan Le, MICA HUST name Peter Mary Luke Bobby Aaron Ron Gigi : id 9801 9802

9810 9811 9812 9813 9824 : class 1A 1A 1A 1A 1A 1A 1A : 67

2. Ngn ng thao tc d liu Cc tc t so snh: expr IN ( value1, value2, value3) expr BETWEEN value1 AND value2 expr LIKE "%_" Thi-Lan Le, MICA HUST 68 2. Ngn ng thao tc d liu Yu cu 6: Lit k cc sinh vin sinh vo th t hoc th by Cu truy vn:

SELECT name, class, CDOW(dob) AS bdate FROM student WHERE DOW(dob) IN (4,7) Trong : CDOW (): tr v tn ngy trong tun ca 1 ngy DOW (): tr v th ca 1 ngy trong tun di dng s (Day of Week) Kt qu Thi-Lan Le, MICA HUST name Peter Wendy

Kevin Luke Aaron : class 1A 1B 1C 1A 1A : bdate Wednesday Wednesday Saturday Wednesday

Saturday : 69 2. Ngn ng thao tc d liu Yu cu 7: Lit k cc sinh vin khng sinh vo thng 1, 2, 5, 6 v 9 Cu truy vn: SELECT name, class, dob FROM student WHERE MONTH(dob) NOT IN (1,3,6,9) Trong : MONTH (): tr v thng

Bi tp: Vit li cu truy vn trn s dng ton t IN Thi-Lan Le, MICA HUST Kt qu name Wendy Tobe Eric Patty Kevin Bobby Aaron : class

1B 1B 1C 1C 1C 1A 1A : dob 07/09/86 10/17/86 05/05/87 08/13/87 11/21/87 02/16/86 08/02/86 :

70 2. Ngn ng thao tc d liu Yu cu 8: Lit k sinh vin lp 1A c im kim tra ton t 80 n 90 Cu truy vn: SELECT name, mtest FROM student WHERE class=1A AND mtest BETWEEN 80 AND 90 Kt qu Thi-Lan Le, MICA HUST name Luke Aaron

Gigi mtest 86 83 84 71 2. Ngn ng thao tc d liu SELECT ...... FROM ...... WHERE condition ; GROUP BY groupexpr [HAVING requirement] Cc hm ca nhm: COUNT( ), SUM( ), AVG( ), MAX( ), MIN( ) Nhm d liu:

groupexpr xc nh cc dng lin quan s c nhm li WHERE condition xc nh iu kin cho tng dng trc khi nhm HAVING requirement xc nh iu kin tng ng n ton b nhm Thi-Lan Le, MICA HUST 72 2. Ngn ng thao tc d liu Yu cu 9: Xc nh tng s sinh vin ca mi lp Cu truy vn: SELECT class, COUNT(*) as NumberofStudent FROM student GROUP BY class Kt qu

Thi-Lan Le, MICA HUST class 1A 1B 1C 2A 2B 2C NumberofStudent 10 9 9 8 8 6 73

2. Ngn ng thao tc d liu Yu cu 10: Lit k im ton trung bnh ca tng lp Cu truy vn: SELECT class, AVG(mtest) FROM student GROUP BY class Kt qu Thi-Lan Le, MICA HUST class 1A 1B

1C 2A 2B 2C avg_mtest 85.90 70.33 37.89 89.38 53.13 32.67 74 2. Ngn ng thao tc d liu Bi tp:

Tnh im trung bnh ca n sinh ca tt c cc lp Tnh im trung bnh ca n sinh ca tng lp Thi-Lan Le, MICA HUST 75 2. Ngn ng thao tc d liu Yu cu 11: Xc nh s sinh vin n ca tng qun Cu truy vn: SELECT dcode, COUNT(*) FROM student WHERE sex=F GROUP BY dcode Kt qu Thi-Lan Le, MICA HUST

dcode HHM KWC MKK SSP TST YMT cnt 6 1 1 5 4 8 76

2. Ngn ng thao tc d liu Yu cu 12: Xc nh im kim tra cao nht v thp nht ca sinh vin tng qun Cu truy vn: SELECT MAX(mtest), MIN(mtest), dcode FROM student GROUP BY dcode Kt qu Thi-Lan Le, MICA HUST max_mtest min_mtest dcode 92 36 HHM

91 19 MKK 91 31 SSP 92 36 TST 75 75 TSW 88 38 YMT 77 2. Ngn ng thao tc d liu

Yu cu 13: Xc nh im ton trung bnh ca nam sinh vin trong mi lp vi iu kin lp c t nht 3 nam sinh vin Cu truy vn: SELECT AVG(mtest), class FROM student WHERE sex=M GROUP BY class HAVING COUNT(*) >= 3 Kt qu Thi-Lan Le, MICA HUST avg_mtest class 86.00

1A 77.75 1B 35.60 1C 86.50 2A 56.50 2B 78 a ra tn lp, im ton trung bnh ca nam sinh vin trong mi lp, s sinh vin nam ca lp vi iu kin lp c t nht 3 nam sinh vin Thi-Lan Le, MICA HUST

79 2. Ngn ng thao tc d liu SELECT ...... FROM ...... WHERE ...... ORDER BY colname [ASC / DESC] Sp xp kt qu tr v: ASC: chiu tng dn DESC: chiu gim dn Mc nh: l ASC Thi-Lan Le, MICA HUST 80

2. Ngn ng thao tc d liu Yu cu 14: Lit k nam sinh vin ca lp 1 A theo tn Cu truy vn: SELECT name, id FROM student WHERE sex=M AND class=1A ORDER BY name name Peter Johnny Luke Bobby Aaron Ron

Thi-Lan Le, MICA HUST id 9801 9803 9810 9811 9812 9813 Kt qu ORDER BY name name Aaron Bobby

Johnny Luke Peter Ron id 9812 9811 9803 9810 9801 9813 81 2. Ngn ng thao tc d liu Yu cu 15: Lit k sinh vin lp 2A theo m qun Cu truy vn:

SELECT name, id, class, dcode FROM student WHERE class=2A ORDER BY dcode Kt qu name Jimmy Tim Samual Rosa Helen Joseph Paula Susan Thi-Lan Le, MICA HUST id

9712 9713 9714 9703 9702 9715 9701 9704 class 2A 2A 2A 2A 2A 2A 2A 2A

dcode HHM HHM SHT SSP TST TSW YMT YMT 82 2. Ngn ng thao tc d liu Yu cu 16: Lit k s lng sinh vin ca mi qun theo chiu gim dn Cu truy vn: SELECT COUNT(*) AS cnt, dcode FROM student

GROUP BY dcode ORDER BY cnt DESC cnt Kt qu Thi-Lan Le, MICA HUST 11 10 10 9 5 2 1 1 1 docode YMT

HHM SSP MKK TST TSW KWC MMK SHT 83 Yu cu: Lit k theo th t gim dn v im ca sinh vin n c im thi ln hn im trung bnh ca cc sinh vin n Thi-Lan Le, MICA HUST 84

2. Ngn ng thao tc d liu Yu cu 17: Lit k nam sinh vin trong mt nh theo trt t m qun v tn lp Cu truy vn: SELECT name, class, hcode FROM student WHERE sex=M ORDER BY hcode, class Thi-Lan Le, MICA HUST 85 2. Ngn ng thao tc d liu

Blue House Tr t theo hcode Green House Thi-Lan Le, MICA HUST name Bobby Teddy Joseph Zion Leslie Johnny

Luke Kevin George : hcode B B B B B G G G G : class

1A 1B 2A 2B 2C 1A 1A 1C 1C : Trt t theo class 86 2. Ngn ng thao tc d liu

Hp, giao v hiu ca cc bng Hp ca A v B (AB) A B Mt bng bao gm tt c cc dng ca A v B. Thi-Lan Le, MICA HUST 87 2. Ngn ng thao tc d liu Hp, giao v hiu ca cc bng Giao ca A v B (AB) A

B Mt bng bao gm cc dng va tn ti trong A va tn ti trong B Thi-Lan Le, MICA HUST 88 2. Ngn ng thao tc d liu Hp, giao v hiu ca cc bng Hiu ca A v B A B Bng bao gm cc dng trong A nhng khng trong B Thi-Lan Le, MICA HUST

89 2. Ngn ng thao tc d liu Bng lu tr cc thnh vin ca Bridge v Chess c cng cu trc nh sau: Thuc tnh Kiu d liu rng id numeric 4 name character 10 sex character 1 class character

2 Bridge [A] 1 2 3 4 5 Chess [B] id name sex class

9812 9801 9814 9806 9818 : Aaron Peter Kenny Kitty Edmond : M M M F

M : 1A 1A 1B 1B 1C : Thi-Lan Le, MICA HUST ngha M hiu sv Tn Gii tnh (M / F) Lp

1 2 3 4 5 id name sex class 9802 9801 9815 9814

9817 : Mary Peter Eddy Kenny George : F M M M M : 1A

1A 1B 1B 1C : 90 2. Ngn ng thao tc d liu Yu cu 17: Xc nh hp ca 2 cu lc b Cu truy vn: SELECT * FROM bridge UNION SELECT * FROM chess ORDER BY class, name SELECT ...... FROM ...... WHERE ...... ; UNION ;

SELECT ...... FROM ...... WHERE ...... Thi-Lan Le, MICA HUST 91 2. Ngn ng thao tc d liu Yu cu 18: In danh sch cc sinh vin tham gia c 2 cu lc b Cu truy vn: SELECT * FROM bridge WHERE id IN ( SELECT id FROM chess) SELECT ...... FROM table1 ; WHERE col IN ( SELECT col FROM table2 )

Thi-Lan Le, MICA HUST 92 2. Ngn ng thao tc d liu Yu cu 19: Xc nh danh sch cc sinh vin tham gia cu lc b Bridge nhng khng tham gia cu lc b Chess Cu truy vn: SELECT * FROM bridge WHERE id NOT IN (SELECT id FROM chess ) SELECT ...... FROM table1 ; WHERE col NOT IN ( SELECT col FROM table2 ) Thi-Lan Le, MICA HUST 93

2. Ngn ng thao tc d liu Bng Music lu tr thng tin v nhc c ca sinh vin Thuc tnh Kiu d liu rng ngha id numeric 4 m hiu sinh vin type character 10 kiu nhc c Thi-Lan Le, MICA HUST 94 2. Ngn ng thao tc d liu

Kt ni: Kt ni t nhin (Natural Join) hay (Inner joint): l mt thao tc kt ni 2 bng bng cc ct chung. Kt ni ngoi (outer join): LEFT OUTER JOIN (thng vit LEFT JOIN) la chn tt c cc dng trong bng u tin sau FROM ngay c khi n khng c trong bng th hai. RIGHT OUTER JOIN Thi-Lan Le, MICA HUST 95 2. Ngn ng thao tc d liu Kt ni t nhin:

C php 1: SELECT a.comcol, a.col1, b.col2, expr1, expr2 FROM table1 a JOIN table2 b ON a.comcol = b.comcol C php 2: SELECT a.comcol, a.col1, b.col2, expr1, expr2 FROM table1 a, table2 b WHERE a.comcol = b.comcol Thi-Lan Le, MICA HUST 96 2. Ngn ng thao tc d liu Yu cu 20: Xc nh danh sch cc sinh vin v

nhc c sinh vin hc Cu truy vn: SELECT s.class, s.name, s.id, m.type FROM student s, music m WHERE s.id=m.id ORDER BY class, name Kt qu Thi-Lan Le, MICA HUST class 1A 1A 1A 1A 1A 1A

1A : name Aaron Bobby Gigi Jill Johnny Luke Mary : id 9812 9811 9824 9820

9803 9810 9802 : type Piano Flute Recorder Piano Violin Piano Flute : 97 2. Ngn ng thao tc d liu

Yu cu 20: Xc nh s sinh vin hc piano trong mi lp Cu truy vn: SELECT s.class, COUNT(*) FROM student s, music m WHERE s.id=m.id AND m.type=Piano GROUP BY class ORDER BY class Student Join iu kin m.type= "Piano" Kt qu Music Thi-Lan Le, MICA HUST

Nhm theo lp class 1A 1B 1C cnt 4 2 1 98 2. Ngn ng thao tc d liu Yu cu 21: Xc nh danh sch sinh vin cha la chn nhc c Cu truy vn:

SELECT class, name, id FROM student WHERE id NOT IN ( SELECT id FROM music ) ORDER BY class, name Kt qu Thi-Lan Le, MICA HUST class 1A 1B 1B 1C 1C : name Mandy

Kenny Tobe Edmond George : id 9821 9814 9805 9818 9817 : 99 2. Ngn ng thao tc d liu Yu cu 22: To danh sch cc sinh vin vi danh sch

nhc c sinh vin hc. Danh sch ny phi bao gm c sinh vin cha ng k nhc c Cu truy vn: SELECT s.class, s.name, s.id, m.type FROM student s, music m WHERE s.id=m.id UNION SELECT class, name, id, "" FROM student WHERE id NOT IN ( SELECT id FROM music ) ORDER BY 1, 2 Sinh vin t xut cc cu truy vn khc thc hin yu cu 22 Thi-Lan Le, MICA HUST 100 2. Ngn ng thao tc d liu class

1A 1A 1A 1A 1A 1A 1A : class 1A 1B 1B 1C 1C : name

Aaron Bobby Gigi Jill Johnny Luke Mary : name Mandy Kenny Tobe Edmond George : id

9812 9811 9824 9820 9803 9810 9802 : type Piano Flute Recorder Piano Violin Piano Flute :

id 9821 9814 9805 9818 9817 : Thi-Lan Le, MICA HUST class 1A 1A 1A 1A 1A 1A

1A 1A 1A 1A 1B 1B 1B 1B : name Aaron Bobby Gigi Jill Johnny Luke Mandy

Mary Peter Ron Eddy Janet Kenny Kitty : id 9812 9811 9824 9820 9803 9810 9821 9802

9801 9813 9815 9822 9814 9806 : type Piano Flute Recorder Piano Violin Piano Flute Piano Guitar

Piano Guitar Recorder : 101 Bi tp Cho bng thng tin: Vit cu truy vn lit k tn khch hng v tng s tin m h s dng Thi-Lan Le, MICA HUST 102 CHNG 4: Ph thuc hm v chun ha

c s d liu quan h Ni dung Mt s khi nim Cc vn trong thit k s quan h Chun ha 1NF 2NF 3NF Boyce Codd (BCNF) Ni dung Mt s khi nim

Cc vn trong thit k s quan h Chun ha 1NF 2NF 3NF Boyce Codd (BCNF) Mt s khi nim Ph thuc hm Lut ca ph thuc hm Bao ng ca tp ph thuc hm Cc loi kha Thuc tnh thuc kha

Ph thuc hm Ph thuc hm (functional dependency) c s dng nh 1 o nh gi cht lng ca tp s quan h c thit k Cc ph thuc hm v cc kha c dng xc nh cc chun ca quan h Ph thuc hm l cc rng buc c xc nh t ng ngha v mi quan h bn trong ca cc thuc tnh Ph thuc hm Mt tp thuc tnh X xc nh tp thuc tnh Y nu gi tr trong X cho php xc nh mt gi tr duy nht trong Y Y ph thuc hm vo X X Y l ng nu bt c hai b no c cng gi tr X th phi c cng gi tr Y Nu t1[X]=t2[X], th t1[Y]=t2[Y] vi mi b r(R)

X Y trong R xc nh mt rng buc cho tt c cc th hin r(R) Ph thuc hm chnh l cc rng buc trn d liu V d v ph thuc hm M s bo him cho php xc nh tn nhn vin SSN ENAME M d n cho php xc nh tn d n v a im d n PNUMBER {PNAME, PLOCATION}PNAME, PLOCATION} M s bo him ca nhn vin SSN v m d n xc nh s gi m nhn vin phi lm trong d n {PNAME, PLOCATION}SSN, PNUMBER} HOURS Ph thuc hm Mt ph thuc hm l mt tnh cht ca cc

thuc tnh trong lc quan h R Mt rng buc phi ng cho tt c cc th hin ca lc quan h r(R) Cc lut cho ph thuc hm Vi mt tp ph thuc hm F, ta c th suy ra cc ph thuc hm mi Lut Armstrong A1. (Phn x - Reflexive) Nu Y l tp con ca X th X Y A2. (Tng trng - Augmentation) Nu X Y, th XZ YZ (Ch : XZ c ngha l X U Z) A3. (Bc cu - Transitive) Nu X Y v Y Z, th X Z A1, A2, A3 to mt tp cc lut ng v y Cc lut cho ph thuc hm Lut tch - Decomposition

If X YZ, then X Y and X Z Lut hp If X Y and X Z, then X YZ Lut ta bc cu - Psuedotransitivity If X Y and WY Z, then WX Z Bao ng ca F cho tp ph thuc hm l tp F+ ca cc ph thuc hm suy din t F Ph thuc hm y Ph thuc hm y (Full functional

dependency): Y Z l ph thuc hm y nu b i bt c mt thuc tnh no trong Y th ph thuc hm ny khng cn ng {PNAME, PLOCATION}SSN, PNUMBER} HOURS l mt ph thuc hm y bi v SSN HOURS hoc PNUMBER HOURS khng cn ng {PNAME, PLOCATION}SSN, PNUMBER} ENAME khng phi l ph thuc hm y (gi l ph thuc hm b phn) bi v SSN ENAME Mt s khi nim khc Ph thuc hm tm thng (Trivial functional dependency) {PNAME, PLOCATION}Employee ID, Employee Address} {PNAME, PLOCATION}Employee Address} l ph thuc hm tm thng v {PNAME, PLOCATION}Employee Address} {PNAME, PLOCATION}Employee Address}.

Kha Siu kha (Superkey) KR Kha (Candidate Key) KR Khng tn ti K K, sao cho K R (ti thiu) Kha chnh (Primary Key) Kha chnh Kha Thuc tnh thuc kha (Prime attribute) l 1 thuc tnh Siutrong kha kha chnh K Kha - v d Bng employee (employeeID, name, job, v

departmentID) Siu kha: {PNAME, PLOCATION}employeeID }, {PNAME, PLOCATION}employeeID, Name}, {PNAME, PLOCATION}employeeID, Name, job}, v {PNAME, PLOCATION}employeeID, Name, job, departmentID}. employeeID l mt kha Bao ng ca tp thuc tnh nh ngha: X, Y l cc thuc tnh ca R: X Y nm trong F+ Y X+ Algorithm: X(0) := X Repeat

X(i+1) := X(i) Z, where Z is the set of attributes such that there exists YZ in F, and Y X(i) Until X(i+1) := X(i) Return X(i+1) Bao ng ca tp thuc tnh R = (A, B, C, D, E) F = {PNAME, PLOCATION}ABC, CDE, BD, EA} Tnh A+ v B+: A+ := {PNAME, PLOCATION}A} := {PNAME, PLOCATION}A, B, C} ABC v {PNAME, PLOCATION}A} A+ := {PNAME, PLOCATION}A, B, C, D} BD v {PNAME, PLOCATION}B} A+ := {PNAME, PLOCATION}A, B, C, D, E}CDE v {PNAME, PLOCATION}C, D} A+ khng thay i

Bao ng ca tp thuc tnh B+ := {PNAME, PLOCATION}B} := {PNAME, PLOCATION}B, D} BD v {PNAME, PLOCATION}B} B+ dng gii thut v B+ khng thay i na Bao ng ca tp thuc tnh A+ l bao ng ca tp thuc tnh Nu A+ = R, th A l mt siu kha ca quan h R Tnh kha da trn bao ng tp thuc tnh R = (A, B, C, D, E) F = {PNAME, PLOCATION}ABC, CDE, BD, EA} Lit k tt c cc kha ca R A+ = {PNAME, PLOCATION}A, B, C, D, E}do AABCDE, th A l kha ca quan h R. V EA, nn EABCDE. (bc cu)

V CDE, nn CDABCDE. (bc cu) V BD, nn BCCD, BCABCDE. (tng trng, bc cu) So A, E, CD, BC are candidate keys of R. Thuc tnh kha - V d Cho lc quan h R trn tp thuc tnh U={PNAME, PLOCATION}A, B, C, D} vi cc ph thuc hm AB->C v B->D v BC->A. Xc nh thuc tnh kha v thuc tnh khng kha Ni dung Mt s khi nim Cc vn trong thit k s quan h Chun ha

1NF 2NF 3NF Boyce Codd (BCNF) Cc quy tc cho thit k c s d liu quan h Thit k c s d liu quan h: cch nhm cc thuc tnh to thnh cc lc quan h Cc chun 1NF 2NF 3NF BCNF D tha d liu v cc d thng khi cp nht D tha d liu

Cc d thng: D thng khi thm b D thng khi xa b D thng khi thay i V d v cc d thng V d quan h: EMP_PROJ ( Emp#, Proj#, Ename, Pname, No_hours) D thng khi cp nht Chuyn tn ca d n t P1 t Billing thnh CustomerAccounting phi cp nht nhiu ln do 100 nhn vin ang lm

trong d n P1 D thng khi thm b Khng th thm mt d n khng c nhn vin. Khng th thm mt nhn vin nu nhn vin cha c ch nh vo 1 d n c th. V d v cc d thng D thng xa b Khi xa 1 d n th ton b nhn vin lm cho d n b xa Khi xa mt nhn vin ca d n chi bao gm 1 nhn vin th kt qu l d n b xa theo

D tha d liu Khng nht qun m bo c s d liu thit k khng cha cc d thng thm, xa v cp nht Gi tr Null trong cc b Cc quan h phi c thit k sao cho cc b ca n c gi tr NULL t nht c th

Cc thuc tnh thng c gi tr NULL c th c t trong 1 quan h ring Gi tr NULL xy ra do: thuc tnh khng hp l gi tr ca thuc tnh khng bit (c th tn ti) gi tr ca thuc tnh tn ti nhng khng cha xc nh c B gi Thit k khng tt c th sinh ra li khi thc hin cc thao tc kt ni Cc quan h phi c thit k m bo khng c b gi no c sinh ra khi thc hin kt ni t nhin Ni dung Mt s khi nim Cc vn trong thit k s quan h

Chun ha 1NF 2NF 3NF Boyce Codd (BCNF) Chun ha Chun ha: L mt qu trnh chia nh quan h thit k khng tt thnh tp cc quan h nh hn Cc chun: dng kha v cc ph thuc hm xc nh dng chun ca quan h

Chun 2, chun 3, chun Boyce-Codd da vo kha v cc ph thuc hm Chun 4 da vo kha v cc rng buc a tr Chun 1 Khng cho php cc thuc tnh a tr, thuc tnh khng nguyn t Bi tp: xc nh quan h no l 1NF Chun 2 Mt quan h R dng chun 2 nu tt c thuc tnh khng kha A trong R l ph thuc y vo kha chnh R c th chia thnh cc quan h dng chun 2 thng qua

qu trnh chun ha dng chun 2 Chun 2: V d FIRST (supplier_no, status, city, part_no, quantity) Ph thuc hm: (supplier_no, part_no) -> quantity (supplier_no) -> status (supplier_no) -> city city -> status Chun 2 Hai thuc tnh city v status ch ph thuc

mt phn vo kha chnh (supplier_no v part_no) INSERT: Khng th thm mt nh cung cp (supplier) mt thnh ph no nu nh cung cp cha tham gia cung cp. DELETE: Nu xa hng cui cng ca mt nh cung cp th thng tin v a ch ca nh cung cp b xa. UPDATE: Thng tin v thnh ph (city) xut hin nhiu ln cho cng mt nh cung cp->khng ng nht khi thay i thng tin. Phn r quan h thnh 2 quan h dng chun 2NF: SECOND (supplier_no, status, city) SUPPLIER_PART (supplier_no, part_no, quantity) Chun 3 nh ngha

Ph thuc hm bc cu (Transitive functional dependency): Tp thuc tnh Z khng phi l kha v tn ti X Y v Y Z V d SSN DMGRSSN l mt ph thuc hm bc cu v SSN DNUMBER v DNUMBER DMGRSSN SSN ENAME khng phi l ph thuc hm bc cu v khng tn ti tp thuc tnh X sao cho SSN X v X ENAME Chun 3 Mt quan h R dng chun 3 nu n :

dng chun 2 v khng c thuc tnh khng kha A trong R ph thuc bc cu vo kha chnh Chun 3NF SECOND (supplier_no, status, city) supplier_no -> status supplier_no ->city city -> status SUPPLIER_CITY (supplier_no, city) CITY_STATUS (city, status) Chun Boyce-Codd (Boyce-Codd Normal Form) Mt quan h R l dng chun Boyce-Codd nu c mt ph thuc hm X A trong R th X l mt kha ca R

Chun sau lun lun mnh hn chun trc: Tt c cc quan h dng chun 2 u dng chun 1 Tt c cc quan h dng chun 3 u dng chun 2 Tt c cc quan h dng chun Boyce-Codd u dng chun 3 Tuy nhin c mt s quan h dng chun 3 nhng khng dng chun Boyce-Codd. Mc ch l chun ha quan h dng chun 3 hoc dng chun Boyce-Codd V d title

Star Wars Star Wars Star Wars Mighty Ducks Waynes yea lengt filmTyp studioNa r h e me 197 124 color Fox 7 197 124 color

Fox 7 197 124 color Fox 7 199 104 color Disney 1 199 95 color Paramou starNam e Fisher Hamill

Ford Esteves Carvey V d {PNAME, PLOCATION}title, year, starName} l candidate key title, year length, filmType, studioName V d Chia quan h Movies thnh 2 quan h Quan h cha tt c cc thuc tnh xut hin trong ph thuc hm {PNAME, PLOCATION}title, year, length, filmType, studioName}

Quan h cha kha v thuc tnh pha bn tri ca ph thuc hm {PNAME, PLOCATION}title, year, starName} Bi tp Customer-schema = (cname, street, ccity) cname ->street, ccity Branch-schema = (bname, assets, bcity) bname ->assets, bcity Loan-info-schema = (bname, cname, loan#, amount) loan# ->amount, bname Bi tp Cho quan h Book(Book_title, Authorname, Book_type, Listprice, Author_affil, Publisher)

V cc rng buc Book_title Publisher, Book_type Book_type Listprice Authorname Author_affil Xc nh quan h dng chun no? Bi tp: Xc nh dng chun ca quan h R=(A, B, C, D, E, F) FD = {PNAME, PLOCATION}AB, BCD, CE, BF} Xc nh dng chun ca quan h CHNG 5: TCH HP C S D LIU TRONG CC NG DNG Two-tier architecture: E.g. client programs using ODBC/JDBC to

communicate with a database Three-tier architecture: E.g. web-based applications, and applications built using middleware ODBC (Open DataBase Connectivity) Open Database Connectivity (ODBC) provides A standard software API method for using database management systems (DBMS). The designers of ODBC aimed to make it independent of programming languages, database systems, and operating systems. ADO.NET KT THC

Recently Viewed Presentations

  • Pennsylvania Safety Inspection Course

    Pennsylvania Safety Inspection Course

    The LOW BEAMS are adjusted to project the main part of the beam 4 inches down from horizontal and 4 inches to the right at a distance of 25 feet. The HIGH BEAMS are adjusted straight out from the vehicle....
  • Summarizing Data - Statistics Department

    Summarizing Data - Statistics Department

    Multivariate numerical data. Each dimension in multivariate data is univariate and hence, we can use the numerical summaries from univariate data (e.g. sample mean, sample variance) However, to study two measurements and their relationship, there are numerical summaries to analyze...
  • Rising 9th Grade Information Night NORTH PAULDING HIGH

    Rising 9th Grade Information Night NORTH PAULDING HIGH

    Dress Code. Please refer to the student handbook for dress code information and standards. It is available on the school website. It is distributed each year on the first day of school. NPHS is strict with this policy and does...
  • AP English Language Exam - Miller Hosey

    AP English Language Exam - Miller Hosey

    Document presentation format: On-screen Show Company: Happen' Group Other titles: Arial MS Pゴシック Wingdings Blends Argument in the AP English Language Classroom Argument vs. Persuasion Types of Argument Toulmin Model - terminology The Toulmin Sentence Writing the Toulmin Sentence Toulmin...
  • Habit 1 - Community Unit School District 308

    Habit 1 - Community Unit School District 308

    Habit 1, Being Proactive, is the key to unlocking all the other habits, that's why it comes first. Habit 1 says, "I am the force. I am the captain of my life. I can choose my attitude. I'm responsible for...
  • Department Away day - Aston University

    Department Away day - Aston University

    Sidaway v Bethlehem Royal Hospital Governors[1985] 1 AC 871 643 - applied Bolam v Friern Hospital Management Committee [1985] 1 WLR 582. "Except in the case of an emergency or where disclosure would prove damaging to the patient, a medical...
  • The Progressive Mention Principle I. What is the

    The Progressive Mention Principle I. What is the

    c. God is omnipotent (Heb. 11:3; Ps. 33:6-9). d. God is a compound unity. ... It is the word Elohim. This name for God occurs in the Bible over 2500 times. It is a very unique word in many ways....
  • Delivering Carbon Saving Communities Rural and off-gas ... - ALEO

    Delivering Carbon Saving Communities Rural and off-gas ... - ALEO

    Mark Ducker (NG-AWS) Business Development Manager. National Grid Affordable Warmth Solutions. An independent Community Interest Company established by National Grid to deliver its initial regulatory commitment to Ofgem of 5,000 new gas connections by March 2013, extended for a further...