Tin Học Quản Trị - Hệ Cơ Sở Dữ Liệu phần 1

Chia sẻ: Qwdqwgferhrt Verbnrtjheth | Ngày: | Loại File: PDF | Số trang:12

Thêm vào BST

Báo xấu

78
lượt xem 9
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Cơ sở dữ liệu (viết tắt CSDL; tiếng Anh là database) được hiểu theo cách định nghĩa kiểu kĩ thuật thì nó là một tập hợp thông tin có cấu trúc. Tuy nhiên, thuật ngữ này thường dùng trong công nghệ thông tin và nó thường được hiểu rõ hơn dưới dạng một tập hợp liên kết các dữ liệu

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Tin Học Quản Trị - Hệ Cơ Sở Dữ Liệu phần 1

TRƯỜNG ĐẠI HỌC ĐÀ LẠT KHOA TOÁN - TIN HỌC HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU (Baøi giaûng toùm taét) NGƯỜI BIÊN SOẠN TẠ THỊ THU PHƯỢNG Ñaø Laït 2009
M CL C Chương 1: T ng quan v H qu n tr cơ s d li u .....................................Trang 1 I. Gi i thi u................................................................................................................1 II. C u trúc và thành ph n c a h qu n tr cơ s d li u ............................................2 Chương 2: Xây d ng, qu n lý và khai thác cơ s d li u.........................................5 I. Khái ni m cơ s d li u .........................................................................................5 II. T o cơ s d li u. ...................................................................................................5 III. Ki u d li u. ...........................................................................................................6 IV. T o và qu n lý b ng. .............................................................................................7 V. Các thao tác trên d li u.......................................................................................11 VI. Truy v n d li u ...................................................................................................12 VII. T o và s d ng khung nhìn (View)......................................................................14 VIII. T o và s d ng ch m c (Index)...........................................................................14 IX. Chuy n i d li u v i các ng d ng khác..........................................................18 Chương 3: T-SQL nâng cao ......................................................................................19 I. Khai báo và s d ng bi n .....................................................................................19 II. C u trúc i u khi n...............................................................................................20 III. Th t c thư ng trú (Stored Procedures)...............................................................22 IV. Ki u d li u cursor ...............................................................................................26 V. Hàm ngư i dùng (User Defined Functions).........................................................32 VI. Triggers và cài t ràng bu c d li u ...................................................................35 Chương 4: B o m t và an toàn d li u .....................................................................40 I. B o m t trong h qu n tr cơ s d li u ..............................................................40 II. B n sao d li u .....................................................................................................46 III. Sao lưu và khôi ph c d li u ...............................................................................59 IV. Qu n lý giao d ch .................................................................................................61 Chương 5: L p trình cơ s d li u............................................................................92 I. L p trình ADO.NET.............................................................................................92 II. Thi t k ch c năng c/ ghi d li u .....................................................................95 III. T o báo bi u v i Crystal Report ..........................................................................98 Bài t p ........................................................................................................................105
1 Bài gi ng tóm t t H qu n tr cơ s d li u Chương 1 T NG QUAN V H QU N TR CƠ S D LI U I. Gi i thi u Thông tin là ngu n tài nguyên quý giá c a m t t ch c. Các ph n m m máy tính là nh ng công c hi u qu x lý thông tin và h qu n tr cơ s d li u là công c ph bi n cho phép lưu tr và rút trích thông tin m t cách hi u qu . H qu n tr cơ s d li u quan h là h qu n tr cơ s d li u ph bi n nh t hi n nay và ư c h tr b i nhi u nhà cung c p ph n m m. Tính hi u qu c a các ng d ng ph thu c vào ch t lư ng c a vi c t ch c d li u. Nh ng c i ti n trong k thu t và x lý cơ s d li u ưa n các cơ h i s d ng thông tin m t cách linh ho t và hi u qu khi d li u ư c t ch c và lưu tr trong các c u trúc quan h . H qu n tr cơ s d li u là m t thành công trong lĩnh v c thương m i. M c tiêu c a h qu n tr cơ s d li u. H qu n tr cơ s d li u ph i m b o các m c tiêu sau: d li u s n dùng (data availability), tính toàn v n d li u (data integrity), an toàn d li u (data secutity), và c l p d li u (data independency). o D li u s n dùng (data availability): d li u ư c t ch c sao cho m i ngư i dùng có th truy c p d dàng theo ch c năng và nhi m v c a h . o Tính toàn v n d li u (data integrity): d li u lưu tr trong cơ s d li u là úng n, áng tin c y. o An toàn d li u (data secutity): Ch nh ng ngư i dùng ư c phép m i có th truy c p d li u. N u nhi u ngư i dùng truy c p chung m t m c d li u cùng lúc thì h qu n tr cơ s d li u không cho phép h th c hi n nh ng thay i gây mâu thu n d li u. c l p d li u (data independency): h qu n tr cơ s d li u ph i cho phép t t c o m i ngư i dùng ư c phép lưu tr , c p nh t và rút trích d li u hi u qu mà không c n n m chi ti t v c u trúc c a cơ s d li u ư c bi u di n và cài t. Quá trình phát tri n c a h qu n tr cơ s d li u. Quá trình phát tri n c a DBMS như sau: − Flat files: 1960s – 1980s − Hierarchical: 1970s –1990s − Network : 1970s – 1990s − Relational: 1980s – n nay − Object-oriented: 1990s – n nay
2 Bài gi ng tóm t t H qu n tr cơ s d li u − Object-relational: 1990s – n nay − Data warehousing: 1980s – n nay − Web-enabled: 1990s – n nay II. C u trúc và thành ph n c a h qu n tr cơ s d li u. Application Forms SQL Interface Front ends SQL Commands Query Execution Parser Plan Executor Engine Operator Evaluator Optimizer Concurrency Control File and Access Transaction Methods Manager Recovery Buffer Manager Manager Lock Manager DBMS Disk Space Manager DATABASE Index System Files catalog Data Files Hình 1.1 Ki n trúc c a DBMS Ki n trúc c a h qu n tr cơ s d li u g m 2 thành ph n ch c năng: o B qu n lý lưu tr (Storage manager). o B X lý truy v n (Query Processor). 1. B qu n lý lưu tr B qu n lý lưu tr có nhi m v lưu tr , rút trích và c p nh t d li u vào cơ s d li u. B qu n lý lưu tr g m có các ơn v sau: − Ki m tra ch ng th c và toàn v n. − Qu n lý giao d ch . − Qu n lý file.
3 Bài gi ng tóm t t H qu n tr cơ s d li u − Qu n lý vùng m. Qu n lý giao d ch (Transaction management) Thông thư ng, m t s thao tác trên cơ s d li u t o thành m t ơn v logic công vi c. Ta hãy xét ví d chuy n kho n, trong ó m t s ti n x ư c chuy n t tài kho n A (A:=A-x) sang m t tài kho n B (B:=B+x). M t y u t c n thi t là c hai thao tác này ho c cùng x y ra ho c không ho t ng nào x y ra c . Vi c chuy n kho n ph i x y ra trong tính toàn th c a nó ho c không. Yêu c u toàn th -ho c-không này ư c g i là tính nguyên t (atomicity). M t y u t c n thi t khác là s th c hi n vi c chuy n kho n b o toàn tính nh t quán c a cơ s d li u: giá tr c a t ng A + B ph i ư c b o toàn. Yêu c u v tính chính xác này ư c g i là tính nh t quán (consistency). Cu i cùng, sau khi th c hi n thành công ho t ng chuy n kho n, các giá tr c a các tài kho n A và B ph i b n v ng cho dù có th có s c h th ng. Yêu c u v tính b n v ng này ư c g i là tính lâu b n (durability). M t giao d ch là m t t p các ho t ng th c hi n ch m t ch c năng logic trong m t ng d ng cơ s d li u. M i giao d ch là m t ơn v mang c tính nguyên t l n tính nh t quán. Như v y, các giao d ch ph i không ư c vi ph m b t kỳ ràng bu c nh t quán nào: N u cơ s d li u là nh t quán khi m t giao d ch kh i ng thì nó cũng ph i là nh t quán khi giao d ch k t thúc thành công. Tuy nhiên, trong khi ang th c hi n giao d ch, ph i cho phép s không nh t quán t m th i. S không nh t quán t m th i này tuy là c n thi t nhưng l i có th d n n các khó khăn n u x y ra s c . Trách nhi m c a ngư i l p trình là xác nh úng n các giao d ch sao cho b o toàn tính nh t quán c a cơ s d li u. m b o tính nguyên t và tính lâu b n là trách nhi m c a h cơ s d li u nói chung và c a thành ph n qu n tr giao d ch (transaction-management component ) nói riêng. N u không có s c , t t c giao d ch hoàn t t thành công và tính nguyên t ư c hoàn thành d dàng. Tuy nhiên, do s hi n di n c a các s c , m t giao d ch có th không hoàn t t thành công s th c hi n c a nó. N u tính nguyên t ư c m b o, m t giao d ch th t b i không gây nh hư ng n tr ng thái c a cơ s d li u. Như v y, cơ s d li u ph i ư c hoàn l i tr ng thái c a nó trư c khi giao d ch b t u. H qu n tr cơ s d li u ph i có trách nhi m phát hi n s c h th ng và tr l i cơ s d li u v tr ng thái trư c khi x y ra s c . Khi m t s giao d ch tương tranh c p nh t cơ s d li u, tính nh t quán c a d li u có th không ư c b o toàn, ngay c khi m i giao d ch là chính xác. B qu n tr i u khi n tương tranh (concurency-control manager) có trách nhi m i u khi n các tương tác gi a các giao d ch ng th i m b o tính th ng nh t c a CSDL.
4 Bài gi ng tóm t t H qu n tr cơ s d li u Thành ph n Ki m tra ch ng th c và toàn v n (Authorization and Integrity Manager) Ki m tra ràng bu c toàn v n và quy n truy c p d li u c a ngư i dùng cơ s d li u. Thành ph n qu n lý giao d ch (Transaction manager) Thành ph n này m b o r ng cơ s d li u luôn tr ng thái nh t quán. Nó qu n lý vi c th c thi các yêu c u thao tác d li u và m b o các truy c p d li u ng th i không d n n mâu thu n. Thành ph n qu n lý file (File manager): qu n lý vi c c p phát không gian trên ĩa. Các file ư c dùng ch a t p các d li u tương t nhau. H qu n lý file qu n lý các file c l p, giúp nh p và l y các m u tin. Thành ph n qu n lý file thi t l p và duy trì danh sách các c u trúc và ch m c ư c nh nghĩa trong lư c trong. Thành ph n qu n lý file có th : o T o file. o Xóa file. o C p nh t m u tin trong file. o L y m t m u tin t m t file. Thành ph n qu n lý vùng m (Buffer Manager): có trách nhi m chuy n d li u t ĩa lưu tr vào b nh chính theo yêu c u c a chương trình. 2. B x lý truy v n (Query Processor) Th c hi n câu truy v n nh n ư c t ngư i dùng qua các giai o n phân tích (parser), t i ưu hóa câu h i (query optimizer), l p k ho ch th c hi n (plan executor) và th c hi n tính toán (operator evaluator).
5 Bài gi ng tóm t t H qu n tr cơ s d li u Chương 2 XÂY D NG, QU N LÝ VÀ KHAI THÁC CƠ S D LI U I. Khái ni m cơ s d li u m c logic, m t cơ s d li u (CSDL) g m: • − Các b ng (tables) ch a d li u có c u trúc và các ràng bu c (constraint) nh nghĩa trên các b ng. − Các khung nhìn (view). − Các th t c/ hàm. − Các vai trò (role) và ngư i dùng (user). −… m c lưu tr v t lý, m t database c a SQL Server ư c lưu tr b i 3 lo i t p tin: • − T p tin d li u (data file) g m có: 1 t p tin d li u chính (primary data file), thư ng có ph n m r ng “mdf”: ch a các d li u kh i u c a database. 0-n t p tin d li u th c p (secondary data file), thư ng có ph n m r ng “ndf”: ch a các d li u không lưu tr h t trong t p tin d li u chính. − T p tin nh t ký giao tác (transaction log file) g m có 1-n t p tin nh t ký, thư ng có ph n m r ng “ldf”: ch a các thông tin v nh t ký giao tác, dùng ph c h i database sau khi x y ra s c . II. T o cơ s d li u 1. Cú pháp l nh t o CSDL Create Database database_name [ On [Primary] { file_spec [,…n] } ] [ Log on { file_spec [,…n] } ] vi file_spec :: = ( Name = logical_file_name, Filename = 'os_file_name '
6 Bài gi ng tóm t t H qu n tr cơ s d li u [ , Size = size ] [ , Maxsize = { max_size | Unlimited } ] [ , Filegrowth = growth_increment ] ) M c nh, các t p tin d li u và log ư c lưu trong thư m c MSSQL\ Data c a thư m c cài t SQL Server. Ví d • Ví d 1: t o CSDL QLSinhVien theo các quy nh m c nh c a SQL Server Create Database QLSinhVien • Ví d 2: t o CSDL QLSinhVien v i khai báo tên file logic, thư m c lưu t p tin d li u chính, kích thư c, … Create Database QLSinhVien On ( Name = QLSV_Data Filename = ‘C:\ ...\ QLSV_Data.mdf ’, Size = 1, Filegrowth = 10% ) • Ví d 3 Create Database QLSinhVien On ( Name = QLSV_Data1, Filename = ‘C:\ ...\ QLSV_Data.mdf ’, Size = 1, Maxsize = 10 MB, Filegrowth = 1 MB ) , ( Name = QLSV_Data2 , Filename = ‘C:\...\QLSV_Data1.ndf’ ) Log on ( Name = QLSV_Log, Filename = ‘D:\...\QLSV_Log.ldf’) 2. Xoá m t CSDL ã t n t i Drop Database database_name 3. Thay i m t CSDL Alter Database database_name …. Dùng : – Thêm/xoá/thay i các t p tin. – Thay i các tùy ch n cho CSDL. III. Ki u d li u SQL Server cung c p các ki u d li u: 1. S – S nguyên: bit, tinyint, smallint, int, bigint.
7 Bài gi ng tóm t t H qu n tr cơ s d li u – S th c Floating point: o float(n) o real = float(24) Fixed point o Decimal(p,s) o Numeric(p,s) 2. Chu i char(n): chu i có dài c nh. nchar(n): chu i (theo mã Unicode) có dài c nh. varchar(n): chu i có dài thay i. nvarchar(n): chu i (theo mã Unicode) có dài thay i. text: ki u d li u cho phép ch a chu i có kích thư c hơn 8KB. ntext: ki u d li u cho phép ch a chu i (theo mã Unicode) có kích thư c hơn 8KB. 3. Ngày gi Datetime. Smalldatetime 4. Ki u ngư i dùng t nh nghĩa a. nh nghĩa m t ki u d li u: sp_addtype type_name, system_type [, ‘null_type’][, ‘owner’] Ví d : nh nghĩa ki u d li u Code là ki u chu i g m 10 ký t cho phép tr ng Exec sp_addtype Code, char(10), ‘NULL’ b. Xóa m t ki u d li u ngư i dùng nh nghĩa: sp_droptype ‘type_name’ IV. T o và qu n lý b ng 1. T o b ng – Xác nh các c t (các thu c tính) c a b ng. – Xác nh khóa chính. – Xác nh các thu c tính null/ not null. – Xác nh thu c tính identity (n u có) (ph i là ki u s nguyên). Lưu ý: – Luôn t o khóa chính cho m t b ng. – Ràng bu c khóa ngo i nên ư c t o sau khi ã t o xong t t c các b ng liên quan. a. Cú pháp l nh t o b ng
8 Bài gi ng tóm t t H qu n tr cơ s d li u Create table Table_name ( { Column_name Data_type [null | not null] [default default_value ] [identity [( seed, increment)] ] } [,…n] [, constraint constraint_name primary key ( Column_name [,…n] ) ] ) Ví d : T o b ng h c sinh có khóa chính là (STT, Lop) Create table HOCSINH ( STT tinyint not null, Lop char(5) not null default ‘11A1’ , HoTen nvarchar(30) not null, NgaySinh datetime not null, DiaChi nvarchar(100), constraint pk_HS primary key (STT, Lop) ) b. Thay i c u trúc b ng / xóa b ng Thay i c u trúc b ng là th c hi n: Thêm/ xoá/ c p nh t ki u d li u c a m t c t (column). – Thêm/ xoá/ ki m tra/ không ki m tra ràng bu c (constraint). – Cho phép/ không cho phép trigger ho t ng. – Cú pháp: Alter table … Xóa b ng: xoá d li u và c u trúc c a b ng Cú pháp: Drop table Ví d − Thêm thu c tính DanToc vào b ng HOCSINH: Alter table HOCSINH Add DanToc nvarchar(20) null default ‘Kinh’ − S a ki u d li u c a thu c tính NgaySinh thành ki u SmallDatetime: Alter table HOCSINH Alter column NgaySinh SmallDatetime not null 2. Qu n lý b ng − Các tên b ng, tên ràng bu c không ư c trùng nhau trong cùng m t database.
9 Bài gi ng tóm t t H qu n tr cơ s d li u − Tên các c t trong cùng m t b ng không ư c trùng nhau. − Thông tin v các b ng, các ràng bu c ư c lưu trong b ng h th ng sysobjects Ví d : c thông tin v các b ng trong database hi n hành: Select * from sysobjects where type = ‘U’ − M t s th t c SQL Server cung c p qu n lý b ng và c u trúc b ng: o sp_databases o sp_tables [‘table_name’] [, ‘owner’][,‘database_name’][, “ ‘type’ ”] Ví d : Exec sp_tables null, null, null, “ ‘TABLE’ ” o sp_help [object_name] sp_help cho bi t các thông tin v i tư ng b t kỳ trong database ( i tư ng có ch a trong sysobjects). Ví d : Exec sp_help HOCSINH o sp_columns object [, owner] [, database] [,column] o sp_helpconstraint ‘table_name’ o… 3. Cài t ràng bu c toàn v n ơn gi n SQL Server cung c p s n cơ ch ki m tra các lo i ràng bu c toàn v n (RBTV) sau: o Khóa chính (primary key constraint). o Khóa ngo i (foreign key constraint). o Giá tr duy nh t (unique constraint). o Check constraint (Ki m tra ràng bu c mi n giá tr ). Có th khai báo ràng bu c trong lúc t o b ng ho c khi b ng ã t n t i. Thông thư ng nên khai báo ràng bu c toàn v n trư c khi nh p d li u. a. Khai báo ràng bu c trong lúc t o b ng Cú pháp: Create table Table_name (… [, constraint Constraint_name { primary key (Column_name [,…n]) | unique ( Column_name [,…n]) | check ( logical_expression ) } ] […n] ) Ví d
10 Bài gi ng tóm t t H qu n tr cơ s d li u Create table SinhVien ( MaSV char(10) not null, HoTen nvarchar(30) not null, Nam tinyint, CMND char(10), Khoa char(5), constraint pk_SV primary key (MaSV), constraint u_CMND unique (CMND), constraint chk_Nam check (Nam > 0 and Nam