Lý thuyết bài giảng môn Quản trị cơ sở dữ liệu kinh doanh | Học viện Công nghệ Bưu chính Viễn thông
Lý thuyết bài giảng môn Quản trị cơ sở dữ liệu kinh doanh của Học viện Công nghệ Bưu chính Viễn thông với những kiến thức và thông tin bổ ích giúp sinh viên tham khảo, ôn luyện và phục vụ nhu cầu học tập của mình cụ thể là có định hướng ôn tập, nắm vững kiến thức môn học và làm bài tốt trong những bài kiểm tra, bài tiểu luận, bài tập kết thúc học phần, từ đó học tập tốt và có kết quả cao cũng như có thể vận dụng tốt những kiến thức mình đã học vào thực tiễn cuộc sống. Mời bạn đọc đón xem!
Môn: Quản trị cơ sở dữ liệu kinh doanh
Trường: Học viện Công Nghệ Bưu Chính Viễn Thông
Thông tin:
Tác giả:
Thông tin :
Môn:
Quản trị cơ sở dữ liệu kinh doanh
Trường:
Học viện Công Nghệ Bưu Chính Viễn Thông
Tác giả:
Preview text:
lOMoARcPSD| 36991220
HỌC VIỆN CÔNG NGHỆ BƯU CHÍNH VIỄN THÔNG
VIỆN KINH TẾ BƯU ĐIỆN BÀI GIẢNG MÔN
QUẢN TRỊ CƠ SƠ DỮ LIỆU KINH DOANH
Mã môn học: EPT1312
Danh mục các từ viết tắt ..................................................................................................................... 1
Danh mục hình vẽ ................................................................................................................................ 2
Danh mục bảng biểu ........................................................................................................................... 4
Chương 1 . Tổng quan về CSDL và thiết kế CSDL kinh doanh .................................................... 4
1.1 Tổng quan về CSDL ................................................................................................................. 4
1.1.1 Dữ liệu và CSDL ................................................................................................................. 4
1.1.2 Hệ quản trị CSDL (DBMS) kinh doanh .............................................................................. 7
1.1.3 Thông tin kinh doanh ........................................................................................................... 9
1.1.4 Dữ liệu kinh doanh ............................................................................................................ 10
1.1.5 Quản trị CSDL kinh doanh ................................................................................................ 11
1.2 Thiết kế CSDL kinh doanh .................................................................................................... 11
1.2.1 Các mô hình thiết kế CSDL kinh doanh ............................................................................ 11
1.2.2 Quy trình thiết kế CSDL kinh doanh ................................................................................. 14
1.2.3 Bài tập: Thiết kế CSDL kinh doanh nhỏ ........................................................................... 31
1.2.4 Đánh giá hiệu xuất thực thi và điều chỉnh CSDL .............................................................. 37
Chương 2 . Quản lý CSDL kinh doanh........................................................................................... 38
2.1 Giới thiệu ngôn ngữ truy vấn có cấu trúc SQL .................................................................... 38 lOMoARcPSD| 36991220
2.1.1 Khái niệm ngôn ngữ truy vấn có cấu trúc .......................................................................... 38
2.1.2. Các nhóm lệnh SQL .......................................................................................................... 38
2.2 Giới thiệu công cụ Microsoft Access trong quản lý CSDL kinh doanh ............................. 42
2.2.1. Tổng quan về Access ......................................................................................................... 42
2.2.2. Làm việc với các đối tượng trong Access ......................................................................... 43
2.2.3. Bài tập: Thực hành tạo và quản lý CSDL với Microsoft Access ...................................... 48
2.3 Phân tích CSDL kinh doanh với Mocrosoft Access ............................................................. 51
2.3.1. Các kỹ thuật cơ bản ......................................................................................................... 51
2.3.2. Sử dụng SQL ................................................................................................................... 51
2.3.3. Các kỹ thuật thống kê ...................................................................................................... 59
2.3.4. Pivot Tables và Pivot Charts ........................................................................................... 64
2.3.5. Bài tập: Thực hành phân tích CSDL kinh doanh với Microsoft Access ......................... 87
Tài liệu tham khảo ............................................................................................................................. 93
Danh mục các từ viết tắt Từ viết tắt Diễn giải CSDL Cơ sở dữ liệu DBMS Database Management System SQL Structure Query Language HQTCSDL
Hệ Quản Trị Cơ Sở Dữ Liệu lOMoARcPSD| 36991220 Danh mục hình vẽ
Hình 1.1Một ví dụ về quản lý CSDL trong một hệ thống thông tin ngân hàng. ............................. 9
Hình 1.2: Ví dụ về một mô hình phân cấp .................................................................................... 13
Hình 1.3: Ví dụ về mô hình mạng ................................................................................................. 13
Hình 1.4: Mô hình dữ liệu hướng đối tượng ................................................................................. 14
Hình 1.5: Mô hình thực thể của nghiệp vụ bán hàng .................................................................... 16
Hình 1.6: Mối quan hệ 1-1 ............................................................................................................ 18
Hình 1.7: Mối quan hệ một-nhiều ................................................................................................. 18
Hình 1.8: Mối quan hệ nhiều-nhiều ............................................................................................... 18
Hình 1.9: Liên kết thành viên ........................................................................................................ 19
Hình 1.10: Ví dụ liên kết tùy chọn thành viên .............................................................................. 19
Hình 1.11: Mô hình thực thể liên kết hệ thống bán hàng .............................................................. 22
Hình 1.12: Quan hệ n-n giữa Hàng và Phiếu xuất ......................................................................... 22
Hình 1.13: Tự liên kết .................................................................................................................... 28
Hình 1.14: Mô hình thực thể liên kết quản lý hàng hóa ................................................................ 33
Hình 1.15: Hai quan hệ 1-n giữa hàng-dòng phiếu xuất-phiếu xuất ............................................. 33
Hình 1.16: Biểu đồ quan hệ sau khi tách ....................................................................................... 34
Hình 1.17: Mô hình hệ thống thông tin công ty nội thất A ........................................................... 35
Hình 1.18: Mối liên kết Customer-Order-Product ........................................................................ 36
Hình 1.19: Mối quan hệ Order- Order line - Product .................................................................... 36 lOMoAR cPSD| 36991220
Hình 1.20: Biểu đồ ER sau khi phân tách các quan hệ .................................................................. 37
Hình 2.1: Tab Create Access ......................................................................................................... 44
Hình 2.2: Mở giao diện Design để thiết kế bảng dữ liệu ............................................................... 45
Hình 2.3: Đặt tên bảng ................................................................................................................... 46
Hình 2.4: Chép dữ liệu từ Access sang ứng dụng khác ................................................................. 46
Hình 2.5: Mở file dữ liệu từ bên ngoài .......................................................................................... 48
Hình 2.6: Mở bảng dữ liệu ............................................................................................................ 48
Hình 2.7: Giao diện Design bảng dữ liệu ...................................................................................... 50
Hình 2.8: Giao diện Design bảng Customer .................................................................................. 51
Hình 2.9: Cách tạo một ô trong báo cáo để tính giá trị trung bình ................................................ 60
Hình 2.10: Tính toán cả giá trị tổng và giá trị trung bình của lượng bán ra trong một vùng ........ 62
Hình 2.11: Tính toán Median và Mode ......................................................................................... 63
Hình 2.12: Bảng dữ liệu tổng hợp bán hàng .................................................................................. 64
Hình 2.13: Create PivotTable trong excell .................................................................................... 66
Hình 2.14: Tạo region .................................................................................................................... 67
Hình 2.15: Tạo camping ................................................................................................................ 68
Hình 2.16: Pivot được sinh ra ........................................................................................................ 68
Hình 2.17: Chọn mục Region muốn xem ...................................................................................... 69
Hình 2.18: Dữ liệu của cửa hàng cho thuê băng video .................................................................. 70
Hình 2.19: PivotTable Field List ................................................................................................... 71
Hình 2.20: Kết quả Pivot Table ..................................................................................................... 72
Hình 2.21: Field setting ................................................................................................................. 73
Hình 2.22: Kết quả Pivot table sau khi sắp xếp hiển thị ................................................................ 74
Hình 2.23: Nút tắt-bật PivotTable Field List ................................................................................. 75
Hình 2.24: Hộp thoại Insert Chart ................................................................................................. 75
Hình 2.25: Kết quả PivotTable ...................................................................................................... 77
Hình 2.26: Chọn vùng dữ liệu trong excel .................................................................................... 78
Hình 2.27: Tạo PivotTable dựa vào tên bảng ................................................................................ 79
Hình 2.28: Tạo PivotTable ............................................................................................................ 80
Hình 2.29: Nguồn dữ liệu có thể sử dụng chức năng Multiple Consolidation Ranges ................. 81
Hình 2.30: Multiple Consolidation Ranges ................................................................................... 81
Hình 2.31: Tạo một trang tổng hợp duy nhất với chức năng Create a single page field for me ... 82
Hình 2.32: Tạo một trang tổng hợp duy nhất với chức năng Create a single page field for me ... 83
Hình 2.33: Tạo một PivotTable có nhiều trang tổng hợp từ những dữ liệu nguồn có cùng cấu trúc
....................................................................................................................................................... 84
Hình 2.34: Sử dụng tùy chọn I will create the page fields .......................................................... 85
Hình 2.35: PivotTable hoàn tất ...................................................................................................... 86
Hình 2.36: Chọn nguồn dữ liệu bên ngoài cho PivotTable ........................................................... 88
Hình 2.37: Tạo Pivot Table ........................................................................................................... 89 lOMoARcPSD| 36991220
Hình 2.38: Mở hộp thoại Existing Connections .......................................................................... 89
Hình 2.39: Chọn bảng bạn muốn làm dữ liệu nguồn cho PivotTable ........................................... 90
Hình 2.40: PivotTable sẽ sử dụng câu truy vấn Product Sales for 1997 ....................................... 91
Hình 2.41: Tìm đến đường dẫn chứa file cơ sở dữ liệu Northwind.mdb ...................................... 92
Hình 2.42: Chọn bảng sử dụng làm dữ liệu cho PivotTable ......................................................... 93
Hình 2.43: PivotTable được tạo sử dụng câu truy vấn trong cơ sở dữ liệu Access ....................... 94
Hình 2.44: Danh sách các trường dữ liệu ...................................................................................... 94
Hình 2.45: Trường CategoryName và ShippedQuarter được chọn trong vùng Row Labels ........ 95
Hình 2.46: Chuyển trường ShippedQuarter sang vùng Column Labels ........................................ 96
Hình 2.47: Hộp thoại Value Field Settings.................................................................................... 96
Hình 2.48: Định dạng tiền tệ ......................................................................................................... 97
Hình 2.49: Một báo cáo PivotTable sau khi hoàn thành ............................................................... 97
Danh mục bảng biểu
Bảng 1-1: Ánh xạ giữa mô hình thực thể liên kết, mô hình quan hệ và hệ quản trị CSDL ........... 15
Bảng 1-2: Xác định các thực thể, định danh thực thể và các thuộc tính mô tả ............................. 21
Bảng 1-3: So sánh tương ứng giữa mô hình thực thể liên kết, mô hình quan hệ và hệ quản trị
CSDL ............................................................................................................................................. 23
Bảng 1-4: Biểu diễn quan hệ dưới dạng bảng ............................................................................... 24
Bảng 1-5: Bảng dữ liệu Hàng hóa ................................................................................................. 25
Bảng 1-6: Bảng dữ liệu Số hóa đơn- Mã hàng- Số lượng ............................................................. 25
Bảng 1-7: Bảng dữ liệu thông tin hàng nhập ................................................................................. 26
Bảng 2-1: Một số các câu lệnh CREATE trong ngôn ngữ định nghĩa dữ liệu .............................. 40
Bảng 2-2: Thiết kế cơ sở dữ liệu bán hàng của công ty nội thất A ............................................... 49
Bảng 2-3: Nhóm câu lệnh tìm kiếm............................................................................................... 54 Chương 1
. Tổng quan về CSDL và thiết kế CSDL kinh doanh
1.1 Tổng quan về CSDL
1.1.1 Dữ liệu và CSDL
Dữ liệu: là cơ sở của thông tin: tên khách hàng, danh mục sản phẩm, ngày giao hàng, mã bưu chính,
v..v. Dữ liệu nhận được một số giá trị, có thể xác định trên một tập hợp nào đó( ví dụ: giá trị của
mã bưu chính phải là số và năm ký tự chữ,..v..v). Dữ liệu biểu diễn một tập hợp các giá trị mà khó
biết được sự liên hệ. Ví dụ: Tên: NGUYEN VAN ANH Danh mục: 345321 Ngày: 12/07/2000 Paris, Ney, 18, 22
Như vậy, khái niệm dữ liệu hẹp hơn khái niệm thông tin. Thông tin luôn mang ý nghĩa và nói chung
gồm nhiều giá trị dữ liệu. Những ví dụ nêu ở phần trên có thông tin: lOMoARcPSD| 36991220
- Thủ kho Nguyễn Văn Anh xuất kho mặt hàng mục 345321 vào ngày 12/07/2000
- Số nhà 20 đại lộ Ney trong quận 18 của Paris là địa chỉ một khách hàng trực tiếp.
Đối với mốt số giá trị của dữ liệu, bản thân cũng có ý nghĩa vì gợi cho chúng ta một sự hiểu biết
hoặc một nhân vật nào đó (Ney là tên một nhà quân sự, một nhân vật lịch sử). Những thông tin này
có thể hữu ích đối với hệ thống thông tin hoặc không cần thiết trong trường hợp ví dụ trên.
Các dữ liệu có thể được biểu diễn dưới nhiều dạng khác nhau (viết, nói,…) dễ hoặc khó dùng tùy
thuộc vào hiện tượng mà nó dựa vào.
- Hiện tượng chắc chắn: kết thúc một niên độ kế toán, số ngày công thực hiện bởi công nhân
A nào đó trong một tháng, thuế suất áp dụng cho một mặt hàng nào đó,…
- Hiện tượng ngẫu nhiên: Dự báo doanh số dựa vào một phân tích thị trường, lợi nhuận dự kiến
- Hiện tượng chưa biết: số ngày đình công do việc áp dụng dây chuyền sản xuất, yếu tố chính
trị, yếu tố con người.
Cơ sở dữ liệu
Những nhà quản lý luôn luôn phải lưu trữ và xử lý dữ liệu phục vụ cho công việc quản lý kinh
doanh của mình. Những danh sách khách hàng, danh sách nhà cung cấp, sổ bán hàng, hồ sơ nhân
viên,… là những ví dụ cần thiết về quản trị dữ liệu.
Trong một tổ chức, những dữ liệu được lưu trữ trong các cơ sở dữ liệu mà dung lượng của chúng
có thể lên tới hàng tỉ, hàng ức (trillions) bytes. Nếu mất những dữ liệu đó, tổ chức sẽ gặp khó khăn
lớn trong khấu định giá, bán sản phẩm hoặc dịch vụ, tính lương cho nhân viên, điều hành hoạt động
tổ chức… Nói rằng:”Dữ liệu của một tổ chức có vai trò sống còn” là điều khẳng định không hề quá một chút nào.
Cơ sở dữ liệu là một bộ sưu tập rất lớn về các loại dữ liệu tác nghiệp, bao gồm các loại dữ liệu âm
thanh, tiếng nói, chữ viết, văn bản, đồ hoạ, hình ảnh tĩnh hay hình ảnh động....đượcmã hoá dưới
dạng các chuỗi bit và được lưu trữ dưới dạng File dữ liệu trong các bộ nhớ của máy tính. Cấu trúc
lưu trữ dữ liệu tuân theo các quy tắc dựa trên lý thuyết toán học. -
Cơ sở dữ liệu phản ảnh trung thực thế giới dữ liệu hiện thực khách quan. -
Cơ sở dữ liệu là tài nguyên thông tin dùng chung cho nhiều người. Bất kỳ người sử
dụng nào trên mạng máy tính, tại các thiết bị đầu cuối, về nguyên tắc có quyền truy
nhập khai thác toàn bộ hay một phần dữ liệu theo chế độ trực tuyến hay tương tác mà
không phụ thuộc vào vị trí địa lý của người sử dụng với các tài nguyên đó. Ví dụ:
Danh bạ điện thoại là một ví dụ về CSDL. -
Là các thông tin có ý nghĩa -
Là tập hợp các thông tin có cấu trúc. lOMoARcPSD| 36991220 -
Các thông tin này có liên quan với nhau và có thể hệ thống được.
Trong khái niệm này, cần nhấn mạnh CSDL là tập hợp các thông tin có tính chất hệ thống,
không phải là các thông tin rời rạc, không có liên quan với nhau. Các thông tin này phải có cấu trúc
và tập hợp các thông tin này phải có khả năng đáp ứng nhu cầu khai thác của nhiều người sử dụng
một cách đồng thời. Đây chính là nét đặc trưng của CSDL.
Đặc điểm của Cơ sở dữ liệu
Tổ chức lưu trữ dữ liệu theo lý thuyết cơ sở dữ liệu có những đặc điểm:
Giảm bớt dư thừa dữ liệu trong lưu trữ: Trong các ứng dụng lập trình truyền thống, phương
pháp tổ chức lưu trữ dữ liệu vừa tốn kém, lãng phí bộ nhớ và các thiết bị lưu trữ, vừa dư
thừa thông tin lưu trữ. Nhiều chương trình ứng dụng khác nhau cùng xử lý trên các dữ liệu
như nhau, dẫn đến sự dư thừa đáng kể về dữ liệu. Ví dụ, trong các bài toán nghiệp vụ quản
lý "Cước thuê bao điện thoại" và "Doanh thu &thời lượng ", tương ứng với mỗi một chương
trình là một hay nhiều tệp dữ liệu được lưu trữ riêng biệt, độc lập với nhau. Trong cả hai
chương trình cùng xử lý một số thuộc tính của một cuộc đàm thoại như “số máy gọi đi”,
“số máy gọi đến”, “hướng cuộc gọi”, “thời gian bắt đầu” và “thời gian kết thúc” ....Nhiều
thuộc tính được mô tả và lưu trữ nhiều lần độc lập với nhau. Nếu tổ chức lưu trữ theo lý
thuyết CSDL thì có thể hợp nhất các tệp lưu trữ của các bài toán trên, các chương trình ứng
dụng có thể cùng chia sẻ tài nguyên trên cùng một hệ CSDL.
Tổ chức lưu trữ dữ liệu theo lý thuyết CSDL sẽtránh được sự không nhất quán trong lưu
trữ dữ liệu và bảo đảm được tính toàn vẹn của dữ liệu: Nếu một thuộc tính được mô tả trong
nhiều tệp dữ liệu khác nhau và lặp lại nhiều lần trong các bản ghi, khi thực hiện việc cập
nhật, sửa đổi, bổ sung sẽ không sửa hết nội dung các mục đó. Nếu dữ liệu càng nhiều thì sự
sai sót khi cập nhật, bổ sung càng lớn. Khả năng xuất hiện mâu thuẫn, không nhất quán
thông tin càng nhiều, dẫn đến không nhất quán dữ liệu trong lưu trữ. Tất yếu kéo theo sự dị
thường thông tin, thừa, thiếu và mâu thuẫn thông tin.
Thông thường, trong thực thể, giữa các thuộc tính có mối quan hệ ràng buộc lẫn nhau, tác
động ảnh hưởng lẫn nhau. Trong ví dụ xét trên, cước của một cuộc đàm thoại phụ thuộc
vào khoảng cách và thời gian cuộc gọi, tức là phụ thuộc hàm vào các thuộc tính máy gọi đi,
máy gọi đến, thời gian bắt đầu và thời gian kết thúc cuộc gọi. Các trình ứng dụng khác nhau
cùng xử lý cước đàm thoại trên các thực thể lưu trữ tương ứng khác nhau chưa hẳn cho
cùng một kết quả về sản lượng phút và doanh thu. Điều này lý giải tại sao trong một doanh
nghiệp, cùng xử lý trên các chỉ tiêu quản lý mà số liệu báo cáo của các phòng ban, các công
ty con lại cho các kết quả khác nhau, thậm chí còn trái ngược nhau. Như vậy, có thể khẳng
định, nếu dữ liệu không tổ chức theo lý thuyết cơ sở dữ liệu, tất yếu không thể phản ảnh thế
giới hiện thực dữ liệu, không phản ảnh đúng bản chất vận động của dữ liệu.
Sự không nhất quán dữ liệu trong lưu trữ làm cho dữ liệu mất đi tính toàn vẹn của nó. Tính
toàn vẹn dữ liệu đảm bảo cho sự lưu trữ dữ liệu luôn luôn đúng. Ví dụ, không thể có mã lOMoARcPSD| 36991220
vùng ngoài quy định của cơ quan quản lý, hoặc ngày sinh của một nhân viên không thể xảy
ra sau ngày tốt nghiệp ra trường của nhân viên đó...
Tổ chức lưu trữ dữ liệu theo lý thuyết CSDL có thể triển khai đồng thời nhiều ứng dụng
trên cùng một CSDL: Điều này có nghĩa là các ứng dụng không chỉ chia sẻ chung tài nguyên
dữ liệu mà còn trên cùng một CSDL có thể triển khai đồng thời nhiều ứng dụng khác nhau
tại các thiết bị đầu cuối khác nhau.
Tổ chức dữ liệu theo lý thuyết cơ sở dữ liệu sẽ thống nhất các tiêu chuẩn, thủ tục và các
biện pháp bảo vệ, an toàn dữ liệu: Các hệ CSDL sẽ được quản lý tập trung bởi một người
hay một nhóm người quản trị CSDL, bằng các hệ quản trị CSDL. Người quản trị CSDL có
thể áp dụng thống nhất các tiêu chuẩn, quy định, thủ tục chung như quy định thống nhất về
mẫu biểu báo cáo, thời gian bổ sung, cập nhật dữ liệu. Điều này làm dễ dàng cho công việc
bảo trì dữ liệu. Người quản trị CSDL có thể bảo đảm việc truy nhập tới CSDL, có thể kiểm
tra, kiểm soát các quyền truy nhập của người sử dụng. Ngăn chặn các truy nhập trái phép,
sai quy định từ trong ra hoặc từ ngoài vào...
1.1.2 Hệ quản trị CSDL (DBMS) kinh doanh
Một hệ quản trị cơ sở dữ liệu là một phần mềm được thiết kế với mục đích là quản lý cơ sở dữ liệu
dựa vào một loạt các mô hình dữ liệu.
a. Mô tả một hệ quản trị CSDL
Một hệ quản trị CSDL là một tập phức tạp các chương trình phần mềm, chúng có thể quản trị việc
tổ chức, lưu trữ, quản lý, và khôi phục dữ liệu của một cơ sở dữ liệu. Hệ quản trị CSDL được phân
loại dựa vào cấu trúc dữ liệu hoặc kiểu dữ liệu mà chúng quản lý.
Mỗi CSDL trong hệ quản trị CSDL sẽ được lược đồ hóa bằng ngôn ngữ mô hình dựa vào mô hình
dữ liệu của hệ quản trị CSDL đó.
Có bốn kiểu mô hình dữ liệu chung nhất cho hệ quản trị CSDL là: mô hình phân cấp, mô hình
mạng, mô hình quan hệ và mô hình đối tượng.
Trong hệ quản trị CSDL, các cấu trúc dữ liệu (các trường dữ liệu, các bản ghi dữ liệu, các file và
các đối tượng) được tối ưu hóa để đáp ứng được nhu cầu lưu trữ một khối lượng lớn dữ liệu trên
các thiết bị lưu trữ trong thời gian dài.
Ngôn ngữ truy vấn dữ liệu và các chương trình xuất báo cáo trong hệ quản trị CSDL cho phép
người dùng tương tác với CSDL, phân tích và cập nhật dữ liệu tùy thuộc vào quyền hạn của người dùng trên dữ liệu đó.
b. Lợi ích của hệ quản trị CSDL -
Cải thiện các chiến lược sử dụng CSDL. -
Giảm độ phức tạp trong môi trường hệ thống thông tin của các tổ chức, các doanh nghiệp. -
Giảm thiểu tính không đồng nhất dữ liệu và dư thừa dữ liệu. lOMoARcPSD| 36991220 -
Tăng tính toàn vẹn của CSDL. -
Tăng tính độc lập giữa ứng dụng và dữ liệu. -
Tăng cường bảo mật dữ liệu. -
Giảm chi phí bảo trì và phát triển ứng dụng. -
Cải tiến tính mềm dẻo của các hệ thống thông tin. -
Tăng cường khả năng truy nhập và tính sẵn sàng của dữ liệu và thông tin. -
Độc lập dữ liệu vật lý và dữ liệu logic.
The image part with rel ations hip I D rId11 was not found in the file.
Hình 1.1Một ví dụ về quản lý CSDL trong một hệ thống thông tin ngân hàng.
c. Các tính năng của hệ quản trị CSDL
Một hệ thống quản trị CSDL thường bao gồm các tính năng sau: -
Khả năng truy vấn dữ liệu: truy vấn là quá trình xử lý các yêu cầu thông tin từ các quan
điểm và sự kết hợp các yếu tố dữ liệu khác nhau. Ví dụ: “Có bao nhiêu khách hàng có
mức lãi suất trên một triệu đồng một tháng?” - Có cơ chế sao lưu và dự phòng dữ liệu. - Tính năng bảo mật. - Khả năng tính toán. -
Thay đổi và truy cập logging. -
Khả năng tự động tối ưu hóa.
d. Mục đích sử dụng hệ quản trị CSDL
Có bốn mục đích chính sử dụng hệ thống quản trị CSDL: lOMoARcPSD| 36991220 -
Phát triển cơ sở dữ liệu -
Truy vấn cơ sở dữ liệu -
Bảo trì cơ sở dữ liệu - Phát triển ứng dụng
e. Một số phần mềm quản trị CSDL phổ biến - Oracle - DB2 -
Sybase Adaptive Server Enterprise - FileMaker - Firebird - Ingres - Informix - Microsoft Access - Microsoft SQL Server - Microsoft Visual FoxPro - MySQL - PostgreSQL - Progress - SQLite - Teradata - CSQL - OpenLink Virtuoso
1.1.3 Thông tin kinh doanh Các dạng thông tin
Thông tin viết: Dạng thông tin này là thông tin thường gặp trong các hệ thống thông tin. Nó thường
thể hiện trên giấy, đôi khi trên màn hình của máy tính điện tử. Các dữ kiện thể hiện thông tin này
có thể có cấu trúc hoặc không. Ví dụ: -
Một bức thư chép tay của một ứng viên vào một vị trí tuyển dụng không có cấu trúc,
song cần phải gồm những thông tin “bắt buộc” (họ, tên, địa chỉ,…) -
Một bức thư đề nghị thanh toán gửi cho một khách hàng thường có cấu trúc được xác
định trước, phụ thuộc vào loại khách hàng, vào thời gian đáo hạn. -
Một hóa đơn có cấu trúc xác định trước và những dữ liệu bắt buộc (tham chiếu, khách
hàng, tham chiếu sản phẩm,…) lOMoARcPSD| 36991220
Thông tin nói: dạng thông tin này là một phương tiện khá phổ biến giữa các cá thể và thường gặp
ở trong các hệ thống thông tin kinh tế- xã hội. Đặc trưng của loại này là phi hình thức và thường
khó xử lý. Vật mang thông tin thường là hệ thống điện thoại.
Thông tin hình ảnh: Dạng thông tin này xuất phát từ các thông tin khác của hệ thống hoặc từ các
nguồn khác. Ví dụ: đề thi thu được từ các bảng tính số liệu hoặc bản vẽ một số chi tiết nào đó của
ô tô có được từ các số liệu của phòng nghiên cứu thiết kế.
Thông tin khác: Một số các thông tin có thể cảm nhận qua một số giai đoạn như xúc giác, vị giác,
khứu giác không được xét trong hệ thống thông tin quản lý.
Thông tin kinh doanh là một trong ba lĩnh vực chính của ngành công nghiệp thông tin. Hai lĩnh
vực còn lại là các thông tin nhóm (khoa học, công nghê, y học) và nhóm các thông tin về giáo dục.
Hình thức chính của thông tin kinh doanh bao gồm: - Tin tức - Nghiên cứu thị trường -
Thông tin tài chính, tín dụng -
Hồ sơ điều hành công ty -
Các phân tích về dữ liệu công nghiệp, quốc gia và kinh tế -
Nghiên cứu công nghệ thông tin
Các định dạng chính của thông tin kinh doanh có thể được chia thành các nhóm sau: -
Các nguồn tài liệu tham khảo cơ bản như hướng dẫn, thư mục, từ điển, niên giám, bách
khoa toàn thư, sổ tay, kỷ yếu và tài nguyên Internet. - Thư mục - Báo - Các dịch vụ Loose-leaf -
Các dịch vụ thông tin Chính phủ - Thống kê -
Thông tin kinh doanh điện tử
Thông tin kinh doanh có vai trò và ý nghĩa vô cùng quan trọng. Ngoài các thị trường tài chính,
thông tin kinh doanh được sử dụng nhiều cho bán hàng và tiếp thị, thông tin tình báo cạnh tranh,
lập kế hoạch chiến lược, nguồn nhân lực và nhiều chức năng kinh doanh chiến lược khác.
Hiện nay, có rất nhiều tổ chức cung cấp các thông tin kinh doanh. Mạng Internet đã làm cho việc
thu thập và cung cấp thông tin kinh doanh dễ dàng hơn từ các tổ chức cung cấp tới người dùng.
1.1.4 Dữ liệu kinh doanh
Dữ liệu kinh doanh và thông tin kinh doanh có mối quan hệ chặt chẽ với nhau và đôi khi có thể
được dùng qua lại lẫn nhau. Dữ liệu kinh doanh chỉ những phần thông tin kinh doanh có thể được
ghi lại, lưu trên các thiết bị máy tính. Các dữ liệu này được thể hiện dưới các hình thứ sau: văn bản, lOMoARcPSD| 36991220
tài liệu, hình ảnh, âm thanh, clip… Chúng được lưu bởi các CSDL, các bảng, mảng, hoặc các trang
quản lý dữ liệu. Thông tin kinh doanh là các thông tin tri thức, có ý nghĩa và có được từ sự tổng
hợp các dữ liệu kinh doanh.
1.1.5 Quản trị CSDL kinh doanh
Quản trị CSDL kinh doanh cũng giống như việc quản lý CSDL nói chung nhưng đối tượng dữ liệu
ở đây là dữ liệu kinh doanh.
Thông tin kinh doanh hay các tri thức, thông tin có được từ dữ liệu kinh doanh có ý nghĩa rất to
lớn. Nó có thể phục vụ để đưa ra các tổng kết báo cáo, các chiến lược kinh doanh cũng như các
định hướng của các doanh nghiệp, tổ chức kinh doanh.
1.2 Thiết kế CSDL kinh doanh
1.2.1 Các mô hình thiết kế CSDL kinh doanh
Đã có một loạt các mô hình mở ra trong quá trình phát triển cơ sở dữ liệu và phát triển các hệ thống
quản trị cơ sở dữ liệu, phần này sẽ giới thiệu các mô hình CSDL thường gặp nhất như: mô hình
mạng, mô hình phân cấp, mô hình liên kết…
a. Mô hình phân cấp (Hierarchical model)
Trong mô hình phân cấp, dữ liệu được tổ chức dưới dạng cấu trúc hình cây, mỗi nút tương ứng với
một kiểu dữ liệu, có thể có một hoặc nhiều trường, mô tả thực thể và một nhánh cây tạo lên một
liên kết giữa kiểu dữ liệu này với kiểu dữ liệu khác. Mỗi nút đều có một nút cha và nhiều nút con,
trừ nút gốc là không có cha.
The image part with rel ations hip I D rId12 was not found in the file.
Hình 1.2:Ví dụ về một mô hình phân cấp lOMoARcPSD| 36991220
Mô hình này chỉ thuận lợi khi các phần tử dữ liệu vỗn dĩ đã phân cấp, điều bất lợi là việc chuẩn bị
cơ sở dữ liệu gặp khó khăn trong việc xác định các nhóm dữ liệu sao cho hợp lý. Vì thế mô hình
dữ liệu phân cấp không phải lúc nào cũng đủ linh hoạt để đáp ứng các nhu cầu của một tổ chức.
b. Mô hình mạng (network model)
Mô hình dữ liệu mạng là mô hình thực thể quan hệ, trong đó các mối liên kết bị hạn chế trong kiểu:
một - một và nhiều – một. Trong mô hình CSDL mạng, dữ liệu được biểu diễn trong các bản ghi
liên kết với nhau bằng các mối nối liên kết (link) tạo thành một đồ thị có hướng. CSDL mạng có
cấu trúc tổng quát hơn so với cấu trúc CSDL phân cấp. Mỗi một xuất hiện của một bản ghi có thể
có rất nhiều các xuất hiện kiểu bản ghi trên nó và các xuất hiện kiểu bản ghi dưới nó. Ngoài các
kiểu bản ghi biểu diễn dữ liệu còn có kiểu bản ghi các phần tử kết nối, biểu diễn sự kết hợp giữa
các biểu diễn dữ liệu. Cho phép mô hình hoá tương ứng nhiều - nhiều.
The image part with rel ations hip I D rId13 was not found in the file.
Hình 1.3: Ví dụ về mô hình mạng
c. Mô hình dữ liệu hướng đối tượng (Object Oriented model)
Là mô hình dữ liệu trong đó các thuộc tính dữ liệu và các phương thức thao tác trên các thuộc tính
đó đều được đóng gói trong các cấu trúc gọi là đối tượng.
Tập dữ liệu được tổ chức theo cấu trúc của mô hình dữ liệu hướng đối tượng gọi là CSDL hướng đối tượng. lOMoARcPSD| 36991220
Hình 1.4: Mô hình dữ liệu hướng đối tượng
d. Mô hình dữ liệu quan hệ thực thể
Mô hình thực thể quan hệ cho phép mô tả lược đồ khái niệm của một tổ chức, áp dụng cho vòng
đời của cơ sở dữ liệu mà không để ý đến tính hiệu quả hoặc thiết kế CSDL vật lý như cách tiếp
cận với các mô hình khác. Nó mô tả được thế giới thực gồm những đối tượng cơ bản được gọi
là những thực thể và những quan hệ giữa thực thể. Ánh xạ và những tác động qua lại của thế
giới thực phức tạp được mô tả bằng các khái niệm lược đồ. Nó được phát triển để tạo thuận lợi
cho thiết kế cơ sở dữ liệu bằng cách cho phép định rõ một lược đồ phức tạp, đại diện cho toàn
bộ cấu trúc logic của cơ sở dữ liệu.Dạng đơn giản của mô hình thực thểquan hệ được dùng làm
cơ sở để trao đổi một cách hiệu quả với người sử dụng đầu cuối về CSDL khái niệm. Từ sơ đồ
thực thể quan hệ người ta có thể chuyển thành lược đồ khái niệm mô hình quan hệ. Mô hình
thực thể quan hệ có thể được xem như là mô hình hướng đối tượng. Mô hình thực thể - quan
hệ cơ bản bao gồm ba lớp đối tượng: thực thể (Entity), mối quan hệ (Relationship), và thuộc tính (Attribute). e.
Mô hình quan hệ Khái niệm
Mô hình CSDL quan hệ hay ngắn gọn là mô hình quan hệ được E.F.Codd phát triển vào đầu
những năm 1970. Mô hình này được thiết lập trên cơ sở lý thuyết tập hợp nên nó rất dễ hiểu và
được sử dụng rất rộng rãi trong việc tổ chức dữ liệu cho các hệ thống. lOMoARcPSD| 36991220
Các thành phần trong mô hình quan hệ gồm: Các quan hệ - các bộ - các thuộc tính.Tương ứng
với các thành phần trong mô hình thực thể liên kết là: Các thực thể - các thể hiện của thực thể
– các thuộc tính.
Mô hình thực thể liên kết → Mô hình quan hệ → Các bảng trong hệ quản trị cơ sở dữ liệu.
So sánh tương ứng giữa mô hình thực thể liên kết, mô hình quan hệ và hệ quản trị CSDL chúng ta có bảng sau:
Bảng 1-1: Ánh xạ giữa mô hình thực thể liên kết, mô hình quan hệ và hệ quản trị CSDL
1.2.2 Quy trình thiết kế CSDL kinh doanh
Quy trình thiết kế CSDL kinh doanh cũng tương đương với quy trình thiết kế CSDL khác. Tất
cảmọi hệ thống đều phải sử dụng một cơ sở dữ liệu của mình, đó có thể là một cơ sở dữ liệu đã có
hoặc một cơ sở dữ liệu được xây dựng mới. Cũng có những hệ thống sử dụng cả cơ sở dữ liệu cũ
và mới. Việc phân tích và thiết kế cơ sở dữ liệu cho một hệ thống có thể tiến hành đồng thời với
việc phân tích và thiết kế hệ thống hoặc có thể tiến hành riêng. Vấn đề đặt ra là cần xây dựng một
cơ sở dữ liệu giảm được tối đa sự dư thừa dữ liệu đồng thời phải dễ khôi phục và bảo trì.
1.2.2.1 Các bước tiến hành phân tích và thiết kế CSDL a. Phân tích CSDL
Bước phân tích CSDL độc lập với các hệ quản trị CSDL, bước này thực hiện các công việc sau: -
Xác định các yêu cầu về dữ liệu: Cần xác định được các nhu cầu về thông tin của người
dùng cuối trong quy trình nghiệp vụ của doanh nghiệp. -
Mô tả được yêu cầu người dùng, có thể dùng ngôn ngữ tự nhiên hoặc dùng các công cụ
chuyên biệt cho thiết kế để mô tả. -
Mô hình hoá dữ liệu: Xây dựng mô hình thực thể liên kết biểu diễn các yêu cầu về dữ liệu. b. Thiết kế CSDL Thiết kế CSDL bao gồm: -
Thiết kế logic CSDL: độc lập với một hệ quản trị CSDL. lOMoARcPSD| 36991220
Xác định các quan hệ: Chuyển từ mô hình thực thể liên kết thành mô hình dữ liệu của hệ
quản trị cơ sở dữ liệu. Ta gọi mô hình này là mô hình dữ liệu logic. Mô hình dữ liệu logic
có thể là: mô hình dữ liệu quan hệ, mô hình dữ liệu mạng, mô hình dữ liệu phân cấp hoặc
mô hình dữ liệu hướng đối tượng… -
Thiết kế vật lý CSDL: dựa trên một hệ quản trị CSDL cụ thể, xác định được cấu trúc dữ
liệu và các phương pháp xử lý dữ liệu cho phù hợp.
Phần tiếp theo sẽ tìm hiểu chi tiết các bước thiết kế cơ sở dữ liệu quan hệ vì 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.
1.2.2.2 Mô hình thực thể liên kết
Mục đích của việc xây dựng mô hình
Xây dựng mô hình thực thể liên kết nhằm các mục đích sau: -
Mô tả thế giới thực gần với quan niệm, suy nghĩ của con người. Đây là mô hình tốt với
lượng thông tin ít nhất, mô tả thế giới dữ liệu đầy đủ nhất. -
Việc xây dựng mô hình nhằm thành lập một biểu đồ cấu trúc dữ liệu bao gồm dữ liệu cần
xử lý và cấu trúc nội tại của nó. Ví dụ một mô hình thực thể liên kết
The image part with rel ations hip I D rId16 was not found in the file.
Hình 1.5: Mô hình thực thể của nghiệp vụ bán hàng
Các thành phần của mô hình thực thể liên kết Thực thể
Khái niệm thực thể
Thực thể là khái niệm để chỉ một lớp các đối tượng có cùng đặc tính chung mà ta muốn quản lý
thông tin về nó. Ví dụ: sinh viên, hàng hóa, vật tư.
Một đối tượng cụ thể trong thực thể được gọi là một cá thể (còn gọi là một thể hiện của thực thể).
Ví dụ Lê Văn Bình là một cá thể của thực thể“Khách hàng”, “Xi măng” là cá thể của thực thể “Hàng hóa”
Thuộc tính của thực thể
Để mô tả thông tin về một thực thể người ta thường dựa vào các đặc trưng riêng của thực thể đó.
Các đặc trưng đó được gọi là thuộc tính của thực thể. Ví dụ thực thể Sinh viên có các thuộc tính lOMoARcPSD| 36991220
Mã sinh viên, Họ tên sinh viên, Ngày sinh, Địa chỉ, Trường, Khoa, Khóa sinh viên, Lớp,… Đối với
thực thể Hàng hóa thì giá trị của các thuộc tính Mã hàng hóa, Tên hàng hóa, Đơn vị tính, Đơn
giá,… là những thông tin mà nhà quản lý cần quan tâm để quản lý hàng hóa.
Thuộc tính của thực thể bao gồm các loại sau:
Thuộc tính định danh (còn gọi là định danh thực thể, đôi khi còn gọi là thuộc tính khoá): Là
một hoặc một số thuộc tính mà giá trị của nó cho phép phân biệt các thực thể khác nhau. Một thực
thể bao giờ cũng được xác định một thuộc tính định danh làm cơ sở để phân biệt các thể hiện cụ thể của nó.
Ví dụ: Số hiệu khách hàng, Mã mặt hàng, Mã sinh viên,...
Thuộc tính mô tả: Là các thuộc tính mà giá trị của chúng chỉ có tính mô tả cho thực thể hay liên
kết mà thôi. Hầu hết các thuộc tính trong một kiểu thực thể đều là mô tả. Có một số thuộc tính mô tả đặc biệt như sau:
Thuộc tính tên gọi: là thuộc tính mô tả để chỉ tên các đối tượng thuộc thực thể. Thuộc
tính tên gọi để phân biệt các thực thể (tách các thực thể).
Thuộc tính kết nối (thuộc tính khoá ngoài): là thuộc tính chỉ ra mối quan hệ giữa một
thực thể đã có và một thực thể trong bảng khác. Thuộc tính kết nối giống thuộc tính mô
tả thông thường trong thực thể chứa nó nhưng nó lại là thuộc tính khoá của một thực thể trong bảng khác.
Liên kết và các kiểu liên kết
Liên kết (còn gọi là quan hệ) là sự kết hợp giữa hai hay nhiều thực thể phản ánh sự ràng buộc trong
quản lý. Đặc biệt: Một thực thể có thể liên kết với chính nó ta thường gọi là tự liên kết. Giữa hai
thực thể có thể có nhiều hơn một liên kết.
Có ba kiểu liên kết: một - một, một - nhiều, nhiều - nhiều.
Liên kết một – một (1-1)
Mỗi thể hiện của thực thể A quan hệ với một thể hiện của thực thể B và ngược lại. Kí hiệu:
The image part with rel ations hip I D rId17 was not found in the file.
Ví dụ: Một kho hàng có một loại sản phẩm, một sản phẩm thuộc một kho hàng lOMoARcPSD| 36991220 Có thuộc Sản phẩm Kho hàng Hình 1.6: Mối quan hệ 1-1
Liên kết một – nhiều (1-N):mỗi thể hiện của thực thể A quan hệ với nhiều thể hiện của thực thể B.
Ngược lại mỗi thể hiện của thực thể B quan hệ với chỉ một thể hiện của thực thể A. Kí hiệu:
- Ví dụ: Một cửa hàng nhận được 0, 1 hoặc nhiều phiếu xuất. Mỗi phiếu xuất cần được xuất cho chỉ một cửa hàng. Cửa hàng 1 n Phiếu xuất
Hình 1.7: Mối quan hệ một-nhiều
Liên kết nhiều – nhiều (N-N): Mỗi thể hiện của thực thể A quan hệ với nhiều thể hiện của thực
thể B. Ngược lại mỗi thể hiện của thực thể B quan hệ với nhiều thể hiện của thực thể A. Kí hiệu
The image part with rel ations hip I D rId19 was not found in the file.
Ví dụ: Một mặt hàng có trong nhiều phiếu xuất, 1 phiếu xuất có nhiều mặt hàng. Mặt hàng n n Phiếu xuất
Hình 1.8: Mối quan hệ nhiều-nhiều
Điều kiện để một cá thể của thực thể tham gia vào liên kết với một thực thể khác gọi là loại thành
viên. Nó có thể là bắt buộc hay tuỳ chọn trong quan hệ. Các loại thành viên cho biết số thể hiện
nhỏ nhất của mỗi thực thể tham gia vào liên kết với một thể hiện của một thực thể khác. Kí hiệu: lOMoARcPSD| 36991220
Hình 1.9: Liên kết thành viên Ví dụ: -
Tuỳ chọn (ít nhất 0) – “một mặt hàng có thể được xuất trong một hoặc nhiều phiếu xuất.” -
Bắt buộc (ít nhất 1) – “mộtphiếu xuấtcó một hoặc nhiều mặt hàng.”
The image part with rel ations hip I D rId21 was not found in the file.
Hình 1.10: Ví dụ liên kết tùy chọn thành viên Chú ý: -
Mô hình dữ liệu không chỉ là công cụ phân tích thiết kế mà còn như một phương pháp
kiểm tra chặt chẽ các yêu cầu nghiệp vụ của người sử dụng. -
Nếu hai thực thể có quan hệ một - một thường có ít lý do để coi chúng như hai bảng
tách biệt nên người ta thường gộp hai thực thể làm một. lOMoARcPSD| 36991220 -
Nếu hai thực thể có quan hệ nhiều - nhiều thì không có sự khác biệt về bản chất giữa
các chiều vì vậy ít khi được sử dụng.
Tóm lại trong ba kiểu liên kết trên, liên kết một nhiều là quan trọng hơn cảvà hầu như các
mối quan hệ trong mô hình thực thể liên kết đều là một nhiều. Bài tập
Xác định các liên kết của các kiểu thực thể trong hệ thống quản lý bến xe: Khách hàng - Vé (1-n);
Vé - Phương tiện (n-1); Đơn hàng- Nhà cc(n-1); Đơn hàng-Phương tiện (1-n).
Xây dựng các mô hình thực thể liên kết của hệ thống Các
bước tiến hành:
Bước 1: Xác định và định danh thực thể
Xác định các thực thể là các mục thông tin cần thiết cho hệ thống và hệ thống cần lưu giữ. Tìm các thực thể từ ba nguồn: -
Thông tin tài nguyên: con người, kho bãi, tài sản (VD: nhà cung cấp, mặt hàng, kho...) -
Thông tin giao dịch: là các luồng thông tin đến từ môi trường và kích hoạt một chuỗi
hoạt động của hệ thống (VD: đơn hàng (mua,bán), dự trù, phiếu yêu cầu,...). -
Thông tin tổng hợp: thường ở dưới dạng thống kê liên quan đến các kế hoạch hoặc kiểm
soát (VD: dự toán chi tiêu, tính lương...) Việc xác định và định danh thực thể phải thoả mãn: - Tên gọi là danh từ. - Có nhiều thể hiện. -
Có duy nhất một định danh. -
Có ít nhất một thuộc tính mô tả. -
Có quan hệ với ít nhất một thực thể khác.
Bước 2: Xác định các thuộc tính mô tả cho các thực thể
Khi xác định các thuộc tính mô tả cho các thực thể cần chú ý rằng:
- Mỗi thuộc tính chỉ xuất hiện một lần trong thực thể tương ứng.
- Nếu không chắc chắn là thuộc tính hay thực thể cần tiếp tục nghiên cứu và phân tích nó.
Chú ý: Khi một thuộc tính của thực thể A có nhiều giá trị ta sẽ mô hình hoá thuộc tính đó là một
thực thể B có quan hệ phụ thuộc với thực thể A. Định danh của thực thể B sẽ bao gồm các thuộc
tính định danh của thực thể A và một số thuộc tính khác của thực thể B. Liên kết giữa thực thể A
và thực thể B được gọi là liên kết phụ thuộc. lOMoARcPSD| 36991220
Bước 3: Xác định liên kết giữa các thực thể
Xác định liên kết giữa các thực thể theo trình tự sau: -
Thiết lập sự tồn tại của liên kết (Vẽ đường thẳng và đặt tên quan hệ tại hai đầu) -
Xác định loại liên kết (1-1, 1-N, N-N) và loại thành viên (tuỳ chọn hay bắt buộc). -
Tách liên kết N-N thành hai liên kết 1-N với một thực thể kết hợp. Khi đó thực thể kết
hợp sẽ có định danh được tạo thành từ hai thuộc tính định danh của các thực thể ban đầu.
Ví dụ về xây dựng mô hình thực thể liên kết
Một công ty thương mại Y chuyên kinh doanh các mặt hàng điện tử. Công ty nhập các mặt hàng từ
các nhà cung cấp khác nhau. Chi tiết về các mặt hàng gồm có: mã hàng (duy nhất), tên hàng và các mô tả mặt hàng.
Công ty cũng cần lưu giữ thông tin về các nhà cung cấp như tên, địa chỉ, điện thoại, fax. Mỗi nhà
cung cấp có một mã duy nhất. Mỗi nhà cung cấp có thể cung cấp nhiều mặt hàng nhưng mỗi mặt
hàng chỉ được cung cấp từ một nhà cung cấp.
Các mặt hàng được lưu giữ trong các kho. Mỗi kho hàng có một diện tích khác nhau và chỉ chứa
một loại mặt hàng. Công ty có nhiều cửa hàng đại lý để bán các mặt hàng. Hàng được cung cấp
cho các cửa hàng thông qua các phiếu xuất. Thông tin trên mỗi phiếu xuất cần có mã số cửa hàng
nhận hàng, ngày xuất, thông tin về các mặt hàng được xuất như tên hàng, số lượng, đơn giá, thành tiền.
Yêu cầu: Vẽ mô hình thực thể liên kết của hệ thống.
Xác định các thực thể, định danh thực thể và các thuộc tính mô tả:
The image part with rel ations hip I D rId22 was not found in the file.
Bảng 1-2: Xác định các thực thể, định danh thực thể và các thuộc tính mô tả lOMoARcPSD| 36991220
Xác định liên kết giữa các thực thể -
Một mặt hàng cần được cung cấp bởi một nhà cung cấp. Một nhà cung cấp cần cung
cấp một hoặc nhiều mặt hàng. -
Một mặt hàng cần được lưu giữ trong một kho. Mỗi kho lưu giữ 0 hoặc một loại hàng. -
Một mặt hàng được xuất trong 0, 1 hoặc nhiều phiếu xuất. Một phiếu xuất có thể xuất 1 hoặc nhiều mặt hàng. -
Một cửa hàng nhận được 0, 1 hoặc nhiều phiếu xuất. Mỗi phiếu xuất cần được xuất cho chỉ một cửa hàng.
Vẽ mô hình thực thể liên kết
Hình 1.11: Mô hình thực thể liên kết hệ thống bán hàng -
Quan hệ n-n giữa Hàng và Phiếu xuất có thể được tách thành 2 quan hệ 1- n với thực
thể kết hợp Dòng phiếu xuất như sau:
The image part with rel ations hip I D rId24 was not found in the file. lOMoARcPSD| 36991220
Hình 1.12: Quan hệ n-n giữa Hàng và Phiếu xuất
1.2.2.3 Mô hình quan hệ
a. Các khái niệm Khái niệm mô hình quan hệ
Mô hình CSDL quan hệ hay ngắn gọn là mô hình quan hệđược E.F.Codd phát triển vào đầu những
năm 1970. Mô hình này được thiết lập trên cơ sở lý thuyết tập hợp nên nó rất dễ hiểu và được sử
dụng rất rộng rãi trong việc tổ chức dữ liệu cho các hệ thống. Các thành phần trong mô hình quan
hệ gồm: Các quan hệ - các bộ - các thuộc tính.
Mô hình thực thể liên kết => Mô hình quan hệ => Các bảng trong hệ quản trị cơ sở dữ liệu.
So sánh tương ứng giữa mô hình thực thể liên kết, mô hình quan hệ và hệ quản trị CSDL chúng ta có bảng sau:
Mô hình thực thể liên kết Mô hình quan hệ Các bảng trong hệ QTCSDL Thực thể Quan hệ Bảng
Thể hiện của thực thể Bộ Dòng hay bản ghi Thuộc tính Thuộc tính Cột hay trường
Bảng 1-3: So sánh tương ứng giữa mô hình thực thể liên kết, mô hình quan hệ và hệ quản trị CSDL Ví dụ:
- Mô hình thực thể liên kết Thể hiện của Thực thể thực thể Mặt hàng MH01 MH02 Mã Sach But Tên NSX 12/12/2014 12/10/2014 -
Biểu diễn quan hệ dưới dạng mô hình quan hệ :
Quan hệ MAT_HANG (MA, TEN, NSX) Các bộ: (MH01, SACH, 12/12/2014) (MH02, BUT, 12/10/2014) lOMoARcPSD| 36991220
Biểu diễn quan hệ dưới dạng bảng: Bảng MAT_HANG MA TEN NSX MH01 SACH 12/12/2014 MH02 BUT 12/10/2014
Bảng 1-4: Biểu diễn quan hệ dưới dạng bảng Chú ý: -
Các bộ trong một quan hệ không được trùng nhau -
Thuộc tính được xác định bởi tên, thứ tự của các thuộc tính trong quan hệ là không quan
trọng. Trong một quan hệ, tên các thuộc tính phải khác nhau (các miền giá trị của các
thuộc tính không nhất thiết khác nhau).
Các khái niệm về khóa Khoá chính
Khoá chính của một quan hệ (Primary Key-PK) là một hoặc một nhóm thuộc tính xác định duy
nhất một bộ trong quan hệ. Khoá chính của quan hệ là định danh của thực thể tương ứng. Trong
quan hệ các thuộc tính thuộc khoá chính được gạch chân và được gọi là các thuộc tính khoá.
Ví dụ: MAT_HANG (MA, TEN, NSX) – MA là khoá chính của quan hệ MAT_HANG.
Khi chọn khoá chính cần phải xem xét các tiêu chuẩn sau: khoá chính phải xác định được duy nhất
một bộ trong quan hệ, phải có số thuộc tính ít nhất, phải không thay đổi theo thời gian. Khoá ghép
Khóa ghép là khóa có từ hai thuộc tính trở lên.
Ví dụ: HANG(MA_HANG, MA_KHO, NGAY_NHAP, NGAY_XUAT) – khoá chính của quan
hệ HANGlà một khoá ghép gồm hai thuộc tính MA_HANG, MA_KHO.
Khóa ngoại
Là thuộc tính do con người đặt ra để làm khoá chính. Thuộc tính này không mô tả đặc điểm của
các đối tượng quan tâm mà chỉ có tác dụng để xác định duy nhất đối tượng đó.
Ví dụ: mã SV, số hoá đơn…Thông thường khi khoá chính có từ 3 thuộc trở lên người ta thường
đặt ra một khoá giả làm khoá chính để tiện lợi hơn trong việc truy vấn dữ liệu.
Các ràng buộc trong mô hình quan hệ là: lOMoARcPSD| 36991220 -
Ràng buộc thực thể: là một ràng buộc trên khoá chính. Nó yêu cầu khoá chính phải tối
thiểu, xác định duy nhất và không null. (Giá trị null tức là không có giá trị. Nó khác với
giá trị 0 hay dấu cách.) -
Ràng buộc tham chiếu (ràng buộc khoá ngoài): liên quan đến tính toàn vẹn của mối
quan hệ tức là liên quan đến tính toàn vẹn của khoá ngoài. Một ràng buộc tham chiếu
yêu cầu một giá trị khoá ngoài trong một quan hệ cần phải tồn tại là một giá trị khoá
chính trong một quan hệ khác hoặc là giá trị null.
b. Các dạng chuẩn Phụ thuộc hàm Khái niệm
Trong một quan hệ R, thuộc tính B phụ thuộc hàm vào thuộc tính A (hay thuộc tính A xác định
hàm thuộc tính B) ký hiệu A→B nếu với mỗi giá trị của thuộc tính A xác định một giá trị duy nhất của thuộc tính B.
Ví dụ: MÃ HÀNG→TÊN HÀNG MA_HANG TEN_HANG MH01 SACH MH02 BUT
Bảng 1-5: Bảng dữ liệu Hàng hóa
Phụ thuộc hàm giữa nhiều thuộc tính: thuộc tính B phụ thuộc hàm vào các thuộc tính A1 và A2 ký
hiệu{A1,A2} → B nếu với mỗi cặp giá trị của A1 và A2 xác định duy nhất một giá trị của B. Ví
dụ: {Số hoá đơn, Mã hàng}→ Số lượng
Bảng 1-6: Bảng dữ liệu Số hóa đơn- Mã hàng- Số lượng
Chú ý: A1→ {A2, A3} ≡A1→ A2 và A1→ A3
{A1, A2}→ A3 ≠ A1→ A3 và A2→ A3 lOMoARcPSD| 36991220
Các loại phụ thuộc hàm
Phụ thuộc hàm đầy đủ: Thuộc tính B gọi là phụ thuộc đầy đủ vào tập thuộc tính A (có từ 2 thuộc
tính trở lên) nếu nó chỉ phụ thuộc hàm vào A và không phụ thuộc hàm vào bất cứ tập con nào của
A. Ngược lại B gọi là phụ thuộc hàm bộ phận vào tập thuộc tính A.
Phụ thuộc hàm bắc cầu: Nếu có A1 → A2 và A2 → A3 thì A1 → A3. Khi đó A3 được gọi là phụ
thuộc bắc cầu vào A1. Theo quan niệm phụ thuộc hàm thì định nghĩa khoá như sau: Trong quan hệ
R, tập các thuộc tính K là khoá của quan hệ nếu có K → Bi với Bi là tất cả các thuộc tính còn lại. Các dạng chuẩn
Dạng chuẩn 1 (1NF)
Một quan hệ là ở dạng chuẩn 1 nếu toàn bộ các miền thuộc tính đều là các miền đơn và không tồn
tại nhóm thuộc tính lặp. Một thuộc tính A là thuộc tính lặp nếu với một giá trị cụ thể của khoá chính
có nhiều giá trị của thuộc tính A kết hợp với khoá chính này.
Ví dụ: Khoá chính là MÃ HÀNG. Nhóm thuộc tính lặp là NGÀY NHẬP và SỐ LƯỢNG. MA_HANG TEN NGAY_NHAP SO_LUONG NGAY_NHAP SO_LUONG MH01 SACH 11/11/2013 100 11/11/2014 200 MH02 BUT 10/10/2014 200 20/11/2014 200
Bảng 1-7: Bảng dữ liệu thông tin hàng nhập
Dạng chuẩn 2 (2NF)
Một quan hệ ở dạng chuẩn 2 nếu nó đã ở dạng chuẩn 1 và không tồn tại phụ thuộc hàm bộ phận vào khoá.
Ví dụ: Cho một quan hệ: R (A, B, C, D, E), Khoá chính là A,B Các phụ thuộc hàm: {A,B} →D;
A →C; (Phụ thuộc hàm bộ phận vào khoá) D →E
Chú ý: Quan hệ có khoá chính là một thuộc tính luôn ở dạng chuẩn 2
Dạng chuẩn 3 (3NF)
Một quan hệ ở dạng chuẩn 3 nếu nó đã ở dạng chuẩn 2 và không tồn tại thuộc hàm bắc cầu vào
khoá (hay phụ thuộc hàm giữa các thuộc tính không khoá). lOMoARcPSD| 36991220
Ví dụ: Cho một quan hệ: R (A, B, D, E) Khoá chính là A,B Các phụ thuộc hàm:
{A,B} → D; D → E (Phụ thuộc hàm giữa các thuộc tính không khoá)
1.2.2.4 Thiết kế logic CSLD
a. Cách tiếp cận để mô hình hóa dữ liệu
Có hai cách tiếp cận để mô hình hóa dữ liệu như sau:
Cách 1: Vẽ mô hình thực thể liên kết – Cách tiếp cận từ trên xuống (Top - down)
Để xây dựng mô hình thực thể liên kết cần xác định một cách trực giác các đối tượng quan trọng
mà một hệ thống cần phải lưu trữ như dữ liệu (đó là các thực thể) và xác định các thuộc tính mô tả
cho các thực thể đó cùng với quan hệ giữa các thực thể. Nếu áp dụng đúng các luật trong mô hình
thực thể liên kết thì ta sẽ có các quan hệ đã được chuẩn hoá.
Cách 2: Chuẩn hoá – Các tiếp cận từ dưới lên (Bottom - up)
Để chuẩn hoá cần nhóm tất cả các thuộc tính liên quan của hệ thống vào trong một quan hệ. Áp
dụng các luật chuẩn hoá để tách quan hệ đó thành các quan hệ có cấu trúc tốt hơn, giảm bớt dư thừa
dữ liệu. Mỗi cách tiếp cận đều có ưu điểm và nhược điểm riêng vì vậy trong thực tế người ta thường
phối hợp cả hai cách tiếp cận này để có được một mô hình dữ liệu chính xác nhất. Kết quả cuối
cùng của phần thiết kế logic CSDL là tập các bản ghi logic biểu diễn các quan hệ trong CSDL.
b. Chuyển đổi từ mô hình thực thể liên kết thành các bản ghi logic Các luật chuyển đổi
Mỗi thực thể trong mô hình thực thể liên kết trở thành một quan hệ.
Mỗi thuộc tính trong mô hình thực thể liên kết trở thành một thuộc tính trong quan hệ tương ứng.
Định danh thực thể trong mô hình thực thể liên kết trở thành khoá chính trong quan hệ
tương ứng. Nếu khoá chính không đáp ứng được các tiêu chuẩn về tính tối thiểu, tính xác
định duy nhất và tính ổn định thì đưa vào một khoá giả làm khoá chính.
Thi hành các liên kết thông qua việc đặt khóa ngoài vào quan hệ
Với liên kết 1-1: đặt khoá chính của một trong hai quan hệ vào quan hệ kia làm khoá ngoài.
Ví dụ: Với mô hình thực thể liên kết
Một mặt hàng chứa trong một kho
Một kho chứa 1 mặt hàng Ta có các quan hệ:
MAT_HANG (MA, TEN, NSX, MA_KHO)
KHO (MA_KHO, TEN_KHO, DIA_DIEM) lOMoARcPSD| 36991220
Với liên kết 1-N: đặt khoá chính của quan hệ đầu một vào quan hệ đầu nhiều làm khoá ngoài.
Với liên kết N-N: Cần tách thành quan hệ 1-N rồi mới chuyển thành quan hệ. Khi đó quan
hệ tương ứng với thực thể kết hợp sẽ có khoá ghép và mỗi thuộc tính khoá của quan hệ này là một khoá ngoài.
Một số liên kết đặc biệt: Tự liên kết
Ví dụ: Mỗi nhân viên có không hoặc một người quản lý (giám đốc không có người quản
lý nào). Mỗi nhân viên có thể quản lý 0, 1 hay nhiều nhân viên. Ta có một quan hệ:
Hình 1.13: Tự liên kết
NHÂN VIÊN (mã NV, họ tên, ngày sinh, giới tính, địa chỉ, mã NV quản lý). Trong đó Mã
NV quản lý là một mã NV nào đó.
Liên kết phụ thuộc
Ví dụ: Một mặt hàng có nhiều giá khác nhau trong những giai đoạn khác nhau. Ta có các quan hệ:
MẶT HÀNG (Mã hàng, Tên hàng)
GIÁ (Mã hàng, Ngày bắt đầu, đơn giá)
a. Chuẩn hóa quan hệ Các bước tiến hành
Bước 1: Từ một biểu mẫu (tài liệu xuất: hoá đơn, chứng từ,…) lấy ra một danh sách các
thuộc tính cho quan hệ chưa được chuẩn hoá (còn gọi là dạng chuẩn 0).
Mỗi tiêu đề trong biểu mẫu là một thuộc tính.
Bỏ qua phần đầu đề và phần dưới cùng (một số ghi chú, chữ ký …) của biểu mẫu.
Không lấy các thuộc tính được suy diễn từ những thuộc tính khác (như thành tiền= đơn
giá x số lượng) và các thuộc tính trình bày như ‘số thứ tự’ nếu có.
Bổ sung thêm một số thuộc tính định danh tương ứng với một số thuộc tính tên gọi chưa
có định danh nếu cần thiết. lOMoARcPSD| 36991220
Xác định nhóm thuộc tính lặp, các phụ thuộc hàm giữa các thuộc tính.
Bước 2: Chuẩn hoá về dạng chuẩn 1 (1NF): Tách nhóm thuộc tính lặp.
Tách các thuộc tính không nằm trong nhóm lặp thành một quan hệ (R1). Xác định khoá
chính của quan hệ này.
Các thuộc tính của nhóm lặp và khoá chính của quan hệ trên (R1) tạo thành một quan
hệ (R2). Xác định khóa chính cho quan hệ R2 (khoá chính của R2 sẽ là một khoá ghép
giữa khoá của R1 và một thuộc tính khác trong R2)
Ví dụ: R{A, B, C, D, E} và khoá là {A}. Tồn tại nhóm thuộc tính lặp {C,D} thì tách
thành 2 quan hệ R1{A, B, E} và R2{A,C,D}.
Bước 3: Chuẩn hoán về dạng chuẩn 2 (2NF): Loại bỏ phụ thuộc bộ phận vào khoá (chỉ
áp dụng với các quan hệ có khoá ghép)
Tách các thuộc tính tham gia vào phụ thuộc hàm được xác định bởi một phần của
khoá vào một quan hệ mới (R3). Khoá chính của quan hệ là thuộc tính xác định hàm.
Phần còn lại với khoá chính của quan hệ trên (R3) là một quan hệ giữ nguyên khoá
chính như quan hệ ban đầu.
Ví dụ: R{A, B, C, D} và khoá là {A,B}. Tồn tại phụ thuộc hàm A và C thì tách thành
2 quan hệ R1{A,C} và R2{A, B, D}.
Bước 4: Chuẩn hoá về dạng chuẩn 3 (3NF): Loại bỏ phụ thuộc hàm giữa các thuộc tính không khoá.
Tách các thuộc tính tham gia vào phụ thuộc hàm giữa các thuộc tính không khoá vào
một quan hệ mới (R4). Khoá chính của quan hệlà thuộc tính xác định hàm.
Phần còn lại và khoá chính của quan hệ trên (R4) là một quan hệ giữ nguyên khoá
chính của quan hệ ban đầu.
Ví dụ: R{A, B, C, D} mà khoá là {A,B}. Tồn tại phụ thuộc hàm C và D thì tách thành
2 quan hệ R1{C, D} và R2{A, B, C}
b. Hoàn thiện CSDL logic Một số nguyên tắc
Sau khi tiến hành theo hai hướng khác nhau: xây dựng mô hình thực thể liên kết và chuẩn hoá
dữ liệu chúng ta sẽ có hai tập bản ghi logic khác nhau của cùng một hệ thống. Khi đó cần phải
kết hợp lại để có một mô hình CSDL logic thống nhất cho hệ thống.
Một số nguyên tắc kết hợp:
Kiểm tra sự thống nhất về tên gọi của các quan hệ và các thuộc tính trong hai kết quả.
Nếu cùng tên những khác nghĩa thì phải đặt lại tên cho khác nhau. Nếu cùng nghĩa
nhưng khác tên thì phải đặt lại tên cho giống nhau.
Lấy tất cả các quan hệ khác nhau từ hai kết quả. Với hai quan hệ trùng nhau thì lấy tất
cả các thuộc tính có trong hai quan hệ từ hai kết quả. lOMoARcPSD| 36991220
1.2.2.5 Thiết kế vật lý CSDL
Phần thiết kế vật lý CSDL sẽ phụ thuộc vào một HQTCSDL mà chúng ta sẽ sử dụng để cài đặt
CSDL. Chúng ta cần chuyển từ các bản ghi logic với các thuộc tính thành các quan hệ được biểu
diễn dưới dạng bảng với các trường hay các cột được cài đặt trong một HQTCSDL cụ thể. a. Lập các bảng
Khi lập các bảng cần chú ý:
Với mối bảng cần chỉ rõ:
• Khóa chính (Có thể gồm một hay nhiều thuộc tính).
• Mô tả của tất cả các cột (trường).
Với mỗi cột (trường) cần phải có:
• Một tên duy nhất (trong bảng lưu giữ nó).
• Một mô tả ngắn gọn.
• Có kiểu dữ liệu riêng (ví dụ: integer, char, date, logical,… phụ thuộc vào
HQTCSDL cụ thể cài đặt CSDL)
• Một kích thước (có thể để giá trị mặc định).
• Chú ý: tên bảng, tên cột không nên quá dài, cần đủ nghĩa và thường không có dấu.
Các cột (trường) tùy chọn và các cột (trường) bắt buộc:
• Nếu cột là bắt buộc thì người sử dụng cần phải cung cấp một giá trị cho cột này
trong mỗi dòng (bản ghi) thêm vào bảng khi cập nhật CSDL. Cột sẽ cần phải được chỉ rõ là NOT NULL
• Nếu cột là tuỳ chọn thì nó có thể nhận giá trị null.
Chú ý: có thể lưu trữ một giá trị 0 cho một cột tuỳ chọn khi nó không có giá trị những cách này không phải là cách hay. b. Xác định khóa
Khoá chính và các khoá ngoài
• Hầu hết các HQTCSDL hiện đại cho phép chỉ rõ khoá chính, khoá ngoài khi định nghĩa các bảng.
• Nếu các HQTCSDL không cho phép định nghĩa tự động thì cần phải chỉ rõ. Khoá
chính cần phải duy nhất và not null. Khoá ngoài cần phải liên quan tới một thể hiện
của khoá chính đã có hoặc là nhận giá trị null.
Giá trị hợp lệ và giá trị mặc định: ví dụ một mã KH được định nghĩa nằm trong khoảng
từ 1 đến 1000 (giá trị hợp lệ). Giá trị mặc định cho số lượng bán là 1 (giá trị mặc định). lOMoARcPSD| 36991220
• Thuận lợi của việc chỉ rõ giá trị hợp lệ là nó sẽ được gắn vào trong tất cả các chương
trình khi lập trình. Đảm bảo sự thống nhất trong việc kiểm tra tính đúng đắn của dữ liệu.
• Giá trị mặc định có thể được sử dụng để giảm bớt việc gõ máy cho người sử dụng.
Nó giúp cho việc nhập dữ liệu nhanh hơn và giảm bớt lỗi. Khoá giả
• Để tăng tốc độ tìm kiếm, tiết kiệm không gian nhớ và giảm lỗi các khoá cần phải
ngắn. Nếu một vài khoá quá dài cần phải thay chúng bằng một khoá giả.
• Có hai vấn đề khi đưa ra một khoá giả:
Cần phải thiết lập một kỹthuật để sinh tự động các giá trị duy nhất cho khoá (sinh khoá tự động).
Cần phải cung cấp một ràng buộc duy nhất cho các định danh tự nhiên để
ngăn chặn việc lưu trữ một vài bản ghi cho cùng một thực thể có cùng một
dữ liệu nhưng chỉ khác nhau về giá trị khoá.
c. Thiết lập quan hệ
Khi thiết lập quan hệ giữa các bảng cần chú ý:
Quan hệ giữa các bảng được thiết lập bằng khóa ngoại.
Cần phải đảm bảo các rang buộc khóa ngoại sẽ được cài đặt. lOMoARcPSD| 36991220
1.2.3 Bài tập: Thiết kế CSDL kinh doanh nhỏ 1.2.3.1 Bài tập 1
Một công ty thương mại Y chuyên kinh doanh cách mặt hàng điện tử. Công ty nhập các mặt hàng
từ các nhà cung cấp khác nhau. Chi tiết về các mặt hàng gồm có: mã hang (duy nhất), tên hàng và các mô tả mặt hang.
Công ty cũng cần lưu giữ thông tin về các nhà cung cấp như tên, địa chỉ, điện thoại, fax.Mỗi nhà
cung cấp có một mã duy nhất. Mỗi nhà cung cấp có thể cung cấp nhiều mặt hang, nhưng mỗi mặt
hàng chỉ được cung cấp từ một nhà cung cấp.
Các mặt hàng được lưu giữ trong các kho. Mỗi kho hàng có một diện tích khác nhau và chỉ chứa một loại mặt hàng.
Công ty có nhiều cửa hàng đại lý để bán các mặt hàng. Hàng được cung cấp cho các cửa hàng thông
qua phiếu xuất, thông tin trên mỗi phiêu xuất cần có mã số cửa hàng nhận hàng, ngày xuất, thông
tin về các mặt hàng được xuất như: tên hàng, số lượng, đơn giá, thành tiền.
Yêu cầu: thiết kế cơ sở dữ liệu quản lý cho công ty Y?
Bước 1: Phân tích đặc tả cơ sở dữ liệu và đưa mô hình thực thể liên kết của hệ thống.
a. Xác định các thực thể, định danh thực thể và các thuộc tính mô tả Tên thực thể Định danh
Thuộc tính mô tả HÀNG Mã hàng
Tên hàng, Đơn giá, Số lượng NHÀ CUNG CẤP Mã MCC
Tên NCC, Địa chỉ, Điện thoại, Fax Số kho Diện tích KHO PHIẾU XUẤT Số phiếu
Ngày xuất, Số cửa hàng CỬA HÀNG Số cửa hàng Địa điểm
b. Xác định liên kết giữa các thực
- Một mặt hàng thể cần được cung cấp bởi một nhà cung cấp. Một nhà cung cấp cần cung
cấp một hoặc nhiều mặt hàng.
- Một mặt hàng cần được lưu giữ trong một kho. Mỗi kho lưu giữ 0 hoặc một mặt hàng.
- Một mặt hàng được xuất trong 0 hoặc một phiếu xuất. Một phiếu xuất có thể xuất một hoặc nhiều mặt hàng. lOMoARcPSD| 36991220
- Một cửa hàng nhận được 0, 1 hoặc nhiều phiếu xuất. Một phiếu xuất được xuất cho chỉ một cửa hàng.
c. Vẽ mô hình thực thể liên kết
Hình 1.14: Mô hình thực thể liên kết quản lý hàng hóa
Quan hệ n-n giữa hàng và phiếu xuất sẽ được tách thành 2 quan hệ 1-n với thực thể kết hợp Dòng phiếu xuất như sau:
Hình 1.15: Hai quan hệ 1-n giữa hàng-dòng phiếu xuất-phiếu xuất lOMoARcPSD| 36991220
Như vậy biểu đồ quan hệ thực thể sau khi tách ta được:
Bước 2: Chuyển mô hình thực thể liên kết thành các bản ghi logic, xác định được cấu trúc dữ liệu
Kết quả thiết kế logic CSDL của hệ thống công ty thương mại Y là:
NHÀ CUNG CẤP (MaNCC, TenNCC, Diachi, Dienthoai, Fax)
HÀNG (Mahang, TenHang, DonViTinh, Dongia, Soluong, Mota, MaNCC, Sokho) KHO (Sokho, Dientich, Mota)
PHIẾU XUẤT (SoPhieu, Ngayxuat, Socuahang)
DÒNG PHIẾU XUẤT (Mahang, Sophieu, Soluongxuat)
CỬA HÀNG (Socuahang, Diadiem, Mota) lOMoARcPSD| 36991220
1.2.3.1 Bài tập 2
Phát triển CSDL bán hàng cho một công ty nội thất A, biết một số các thông tin khảo sát hiện trạng
hệ thống thông tin của công ty A như sau:
Đây là một công ty nôi thất đã phát triển lâu năm, họ đã từng áp dụng công nghệ thông tin vào hệ
thống hiện tại của họ. Tuy nhiên hệ thống thông tin của họ dựa trên việc xử lý tập tin truyền thống
đã lỗi thời và lạc hậu. Chính vì thế công ty quyết định áp dụng phương pháp tiếp cận CSDL để
nâng cấp hệ thống hiện tại của họ.
Mô hình hệ thống thông tin hiện tại của họ:
The image part with rel ations hip I D rId33 was not found in the file.
Hình 1.17: Mô hình hệ thống thông tin công ty nội thất A
Có ba ứng dụng máy tính dựa trên phương pháp xử lý tập tin như trên hình: hệ thống đặt hàng, hệ
thống thanh toán và hệ thống tính lương. Trong đó các hệ thống này sử dụng các tập tin dữ liệu
quan trọng như: các tập tin lưu thông tin khách hàng, các tập tin về kiểm kê thanh toán, các tập tin
lưu dữ liệu đặt hàng của khách hàng, các tập tin lư dữ liệu của nhân viên.
Bước 1: Phân tích đặc tả cơ sở dữ liệu và đưa mô hình thực thể liên kết của hệ thống.
a. Xác định các thực thể, định danh thực thể và các thuộc tính mô tả
Từ thông tin khảo sát,có thể xác định được một số thực thể quản trọng của hệ thống: PRODUCT,
CUSTOMER, ORDER. Mô tả các thực thể:
- CUSTOMER: là một người hay một tổ chức đã đặt hàng hoặc có tiềm năng đặt hàng của công ty. lOMoARcPSD| 36991220
- PRODUCT: là sản phẩm của công ty, nó được khách hàng (CUSTOMER) đặt hàng.
- ORDER: là các giao dịch liên quan đến việc bán một hay nhiều sản phẩm của công ty cho
khách hàng, một giao dịch được xác định bởi một số giao dịch (thường là kế toán hoặc người bán hàng tạo).
- ITEM: là một mặt hàng, mặt hàng này có thể được cung cấp bởi một hoặc nhiều nhà sản
xuất. Ví dụ sản loại sản phẩm bàn học thì có nhiều nhà sản xuất phân phối.
- SUPPLIER: là một công ty cung cấp các loại sản phẩm cho công ty. b. Xác định liên kết
giữa các thực thể
Chú ý: khi xác định các quan hệ giữa các thực thể chúng ta cần phân tích kỹ đặc tả để xác định
chính xác các quan hệ. Ví dụ trong bài toán này, ta phân tích các quan hệ giữa các thực thể:
1. Một CUSROMER (khách hàng) có thể đặt nhiều ORDER(đơn hàng), ngược lại một đơn
hàng (ORDER) chỉ được đặt bởi chính xác một khách hàng. Có một số khách hàng có thể
có tiềm năng đặt hàng hoặc chưa kích hoạt đơn hàng hoặc hủy đơn hàng.
2. Một đơn hàng (ORDER) phải có một hoặc nhiều sản phẩm (PRODUCT). Một sản phẩm
(PRODUCT) có thể không nằm trong bất kỳ đơn hàng nào hoặc có thể nằm trong một hoặc nhiều đơn hàng.
c. Vẽ mô hình thực thể liên kết
Hình 1.18: Mối liên kết Customer-Order-Product
Quan hệ nhiều nhiều giữa 2 thực thể ORDER và PRODUCT được tách ra làm 2 quan hệ mộtnhiều,
trong đó thực thể ORDER LINE là thực thể quan hệ: lOMoARcPSD| 36991220
Hình 1.19: Mối quan hệ Order- Order line - Product
Như vậy sau khi tách ta được biểu đồ ERD sau
Hình 1.20: Biểu đồ ER sau khi phân tách các quan hệ
Bước 2: Chuyển mô hình thực thể liên kết thành các bản ghi logic, xác định được cấu trúc dữ liệu
CUSTOMER(CustomerID,CustomerName,CustomerAddress,CustomerCity,
CustomerState, CustomerPostalCode)
ORDER(OrderID, OrderDate,CustomerID) PRODUCT(ProductID, ProductDescription, ProductFinish, ProductStandardPrice)
ORDER_LINE(OrderLineID, OderID, ProductID, OdderedQuantity) lOMoARcPSD| 36991220
1.2.4 Đánh giá hiệu xuất thực thi và điều chỉnh CSDL
1.2.4.1. Xem hiệu xuất thực thi CSDL
Cần phải đánh giá việc thực thi CSDL, đó là tính hợp lý của thời gian đáp ứng các câu hỏi truy vấn.
Khi xem xét hiệu suất thực thi CSDL cần phải:
Chạy thử CSDL với một tập đủ lớn các bản ghi trong môi trường thực tế.
Mô phỏng một số người dùng cùng thực hiện một truy vấn trong CSDL đa người sử dụng.
Chạy trộn lẫn một số chức năng với thời gian quy định.
1.2.4.2. Điều chỉnh CSDL
Sau khi xem xét hiệu suất thực thi CSDL ta cần thực hiện tiếp các công việc sau:
Tạo các thủ tục sao lưu và phục hồi CSDL.
Tổ chức bảo trì và bảo mật cho CSDL.
Thiết lập các ràng buộc toàn vẹn cho CSDL.
Xác định không gian nhớ cho các bảng CSDL và các chỉ số.
Thiết lập vùng nhớ đệm cho CSDL
Phân cụm CSDL, tổ chức lưu trữ vật lý tối ưu cho CSDL trên đĩa.
Đảm bảo điều khiển tương tranh trong CSDL đa người sử dụng.
Tạo ra các khung nhìn của CSDL cho từng đối tượng người dùng.
Xác định sự phân tán dữ liệu và xử lý giữa các máy trạm (client) và máy chủ (server)
trong một hệ thống phân tán. lOMoARcPSD| 36991220 Chương 2
. Quản lý CSDL kinh doanh
2.1 Giới thiệu ngôn ngữ truy vấn có cấu trúc SQL
2.1.1 Khái niệm ngôn ngữ truy vấn có cấu trúc
SQL được tập đoàn IBM phát triển trong những năm 1970, nó được viện tiêu chuẩn quốc gia Hoa
Kỳ (ANSI) chứng nhận năm 1986 và tổ chức tiêu chuẩn hóa quốc tế (ISO) chứng nhận năm 1987.
Phiên bản tiếp theo của SQL được thông qua năm 1989 và 1992, phiên bản năm 1992 gọi là SQL-
92 hoặc ANSI-92 SQL, các phiên bản tiếp theo là SQL:99, SQL:2003, SQL:2006, SQL:2008.
Ngôn ngữ SQL không phải là ngôn ngữ lập trình hoàn chỉnh như java hay C#, nó được gọi là ngôn
ngữ con dữ liệu bởi vì các câu lệnh SQL không những cần thiết cho việc tạo và xử lý dữ liệu nó
còn được xử dụng ở nhiều cách khác nhau. Có thể nhúng tực tiếp SQL vào DBMS để xử lý hoặc
có thể nhúng các câu lệnh SQL và các chương trình ứng dụng client/server, hoặc có thể sử dụng
chúng trong các trương trình khai thác dữ liệu. Các câu lệnh SQL còn có thể thực thi trực tiếp trên
bất cứ công cụ lập trình nào.
2.1.2. Các nhóm lệnh SQL
Câu lệnh SQL được chia làm ba nhóm sau:
Ngôn ngữ định nghĩa dữ liệu (Data definition language_DDL): được sử dụng để tạo các
bảng, các quan hệ và các cấu trúc khác.
Ngôn ngữ thao tác dữ liệu (Data manipulation language_DML): được sử dụng để truy vấn,
thêm, sửa, xóa dữ liệu.
Các module lưu trữ lâu dài (SQL/Persistent stored modules_SQL/PSM): đây là các câu lệnh
mở rộng của SQL bằng cách mở rộng thêm khả năng lập trình thủ tục như các biến và các
câu lệnh điều khiển cung cấp một khuân khổ lập trình nhất định bên trong nền tảng SQL.
Trong phần này chúng ta chỉ dừng lại ở việc tìm hiểu và sử dụng ngôn ngữ thao tác dữ liệu DML
và ngôn ngữ định nghĩa dữ liệu (DLL cơ bản).
2.1.2.1 Ngôn ngữ định nghĩa dữ liệu (DDL)
Ngôn ngữ định nghĩa dữ liệu bao gồm một hệ thống ký hiệu mô tả các kiểu thực thể và mối liên hệ
giữa chúng theo một mô hình dữ liệu cụ thề. Ngôn ngữ định nghĩa dữ liệu được sử dụng khi thiết lOMoARcPSD| 36991220
kế CSDL hoặc sửa đổi thiết kế CSDL. Nó không sử dụng để truy xuất dữ liệu hoặc sửa đổi dữ liệu.
Có những câu lệnh mô tả cấu trúc vật lý theo thuật ngữ trìu tượng.
Hầu hết các hệ thống lưu trữ đều bao gồm các bảng (table), các khung nhìn (view), các chỉ mục
(index) và các đối tượng cơ sở dữ liệu khác khi một cơ sở dữ liệu được tạo. Trong một số trường
hợp, người dùng có thể không được cho phép tạo cơ sở dữ liệu trên các hệ thống bởi vì quyền tạo
CSDL phải được cấp phép bởi người quản trị hệ thống và người dùng muốn tạo CSDL phải được
cấp phép. Trong trường hợp này cú pháp câu lệnh tạo cơ sở dữ liệu là:
CREATE SCHEMA database_name; AUTHORIZATION owner_user id
Với câu lệnh này, cơ sở dữ liệu sẽ được quản lý bởi người dùng đã được xác thực.
Một số các câu lệnh CREATE trong ngôn ngữ định nghĩa dữ liệu: Câu lệnh Ý nghĩa câu lệnh CREATE SCHEMA
Được dùng để định nghĩa các vùng cơ sở dữ liệu mà một người dùng
cụ thể được quản lý. “Schema” là một phân vùng chứa các đối tượng
như: views, domains, constraints, assertions, character sets,collations, … CREATE TABLE
Định nghĩa một bảng và các trường của bảng trong CSDL CREATE VIEW
Định nghĩa một bảng logic từ một hoặc nhiều bảng hoặc “views”
Bảng 2-1: Một số các câu lệnh CREATE trong ngôn ngữ định nghĩa dữ liệu
a. Câu lệnh tạo bảng trong CSDL CREATE TABLE table_name ( column_name1
data_type(size) constraint_name, column_name2
data_type(size) constraint_name, column_name3
data_type(size) constraint_name, .... ); Trong đó:
table_name là trên của bảng trong CSDL
column_name: là tên các cột (trường) trong bảng
constraint_name: là các rang buộc của các trường SQL định nghĩa các rang buộc sau: lOMoAR cPSD| 36991220
NOT NULL: ràng buộc này chỉ ra rằng các cột này không được chứa các giá trị NULL
UNIQUE: ràng buộc này chỉ ra rằng các giá trị của cột phải là các giá trị duy nhất.
PRIMARY KEY: Chỉ ra đây là trường khóa chính, nó bao gồm cả ràng buộc NOT NULL và UNIQUE.
FOREIGN KEY: chỉ ra đây là trường khóa ngoại, nó sẽ tham chiếu đến một bảng khác trong CSDL.
CHECK: đảm bảo các giá trị của trường phải phù hợp với một điều kiện cụ thể nào đấy.
DEFAULT: chỉ ra giá trị mặc định của trường.
Ví dụ các câu lệnh tạo CSDL cho một công ty nội thất A CREATE TABLE Customer
(CustomerID NUMBER(11,0) NOT NULL,
CustomerName VARCHAR2(25) NOT NULL, CustomerAddress VARCHAR2(30), CustomerCity VARCHAR2(20), CustomerState CHAR(2),
CustomerPostalCode VARCHAR2(9),
CONSTRAINT Customer_PK PRIMARY KEY (CustomerID)); CREATE TABLE Order
(OrderID NUMBER(11,0) NOT NULL,
OrderDate DATE DEFAULT SYSDATE, CustomerID NUMBER(11,0),
CONSTRAINT Order_PK PRIMARY KEY (OrderID),
CONSTRAINT Order_FK FOREIGN KEY (CustomerID)
REFERENCES Customer_T(CustomerID)); CREATE TABLE Product
(ProductID NUMBER(11,0) NOT NULL,
ProductDescription VARCHAR2(50), ProductFinish VARCHAR2(20)
CHECK (ProductFinish IN ('Cherry', 'Natural Ash', 'White Ash',
'Red Oak', 'Natural Oak', 'Walnut')),
ProductStandardPrice DECIMAL(6,2), ProductLineID INTEGER,
CONSTRAINT Product_PK PRIMARY KEY (ProductID)); CREATE TABLE OrderLine
(OrderID NUMBER(11,0) NOT NULL, ProductID INTEGER NOT NULL, OrderedQuantity NUMBER(11,0), lOMoARcPSD| 36991220
CONSTRAINT OrderLine_PK PRIMARY KEY (OrderID, ProductID),
CONSTRAINT OrderLine_FK1 FOREIGN KEY (OrderID) REFERENCES Order_T(OrderID),
CONSTRAINT OrderLine_FK2 FOREIGN KEY (ProductID)
REFERENCES Product_T(ProductID));
b. Câu lệnh sửa bảng
Một bảng được tạo ra có thể được thay đổi bằng cách sử dụng câu lệnh ALTER trên một cột cụ thể
nào đó. Câu lệnh ALTER TABLE có thể được sử dụng để thêm, sửa, xóa một cột i vào một bảng đang tồn tại.
Cú pháp: ALTER TABLE table_name alter_table_action;
Trong đó table_name alter_table_action là một trong các trường hợp sau
ADD [COLUMN] column_definition : thêm một cột mới vào bảng
ALTER [COLUMN] column_name SET DEFAULT default-value: thay đổi giá trị mặc định của trường.
ALTER [COLUMN] column_name DROP DEFAULT: xóa giá trị mặc định của trường
ADD table_constraint: thêm ràng buộc Ví dụ: ALTER TABLE CUSTOMER_T
ADD COLUMN CustomerType VARCHAR2 (2) DEFAULT “Commercial”;
c. Câu lệnh xóa một bảng trong CSDL
Sử dụng câu lệnh DROP TABLE để xóa một bảng trong CSDL DROP TABLE Customer_T;
2.1.2.2 Ngôn ngữ thao tác dữ liệu (DML)
Ngôn ngữ thao tác dữ liệu DML (Data Manipulation Language) hay còn gọi là ngôn ngữ vấn tin
(Query Language) dùng để thao tác trên các quan hệ CSDL, bao gồm một số phép toán, các toán
hạng là các quan hệ và kết quả của các phép toán cũng là một quan hệ. Ngôn ngữ thao tác dữ liệu gồm 2 nhóm phép toán:
a) Nhóm các phép toán lưu trữ
Chèn thêm: Chèn vào CSDL từ vùng làm việc đệm chứa các thông tin về một bản ghi cụ thể. lOMoARcPSD| 36991220
Xoá: Xoá một bộ hay xoá một nhóm các bộ. Sửa
đổi: Sửa đổi giá trị của một số thuộc tính. b)
Nhóm các phép toán tìm kiếm gồm:
Phép chọn SELECT: Tạo ra một quan hệ mới, các bộ được rút ra một cách duy nhất từ các bộ
của quan hệ nguồn thoả mãn một tân từ xác định.
Phép chiếu PROJECT: Tạo ra một quan hệ mới, các thuộc tính được rút ra, hoặc được biến
đổi từ các thuộc tính của quan hệ nguồn, các bộ là các bộ của quan hệ nguồn bỏ đi những bộ trùng lặp.
Phép kết nối JOIN: Nhằm tạo ra một quan hệ mới, bằng cách nối nhiều quan hệ trên miền
thuộc tính chung. Các thuộc tính của quan hệ kết quả là các thuộc tính của các quan hệ thành
viên và các bộ là ghép nối các bộ của quan hệ nguồn có cùng chung giá trị thuộc tính chung.
2.2 Giới thiệu công cụ Microsoft Access trong quản lý CSDL kinh doanh
2.2.1. Tổng quan về Access
Access là một hệ quản trị cơ sở dữ liệu quan hệ (RDMS- Relational Database Management System),
rất phù hợp cho các bài toán quản lý vừa và nhỏ. Hiệu năng cao và đặc biệt dễ sử dụng do giao diện
giống các phần mềm khác trong bộ MS Office như MS Word, MS Excel.
Access còn cung cấp hệ thống công cụ phát triển khá mạnh đi kèm (Development Tools) giúp các
nhà phát triển phần mềm đơn giản trong việc xây dựng trọn gói các dự án phần mềm quản lý qui mô vừa và nhỏ.
MS Access 2010 cung cấp hệ thống công cụ rất mạnh, giúp người dùng nhanh chóng và dễ dàng
xây dựng chương trình ứng dụng thông qua query, form, report kết hợp với một số lệnh Visual Basic.
Trong Microsoft Access 2010, bạn có thể xây dựng cơ sở dữ liệu web và đưa chúng lên các
SharePoint site. Người duyệt SharePoint có thể sử dụng ứng dụng cơ sở dữ liệu của bạn trong một
trình duyệt web, sử dụng SharePoint để xác định ai có thể xem những gì. Nhiều cải tiến mới hỗ trợ
khả năng đưa dữ liệu lên web, và cũng cung cấp lợi ích trong việc xây dựng cơ sở dữ liệu trên máy đơn truyền thống.
Access 2010 giao diện người dùng cũng đã thay đổi. Nếu bạn không quen với Office Access 2007,
Ribbon và Cửa sổ Danh mục chính có thể là mới cho bạn. Thanh Ribbon này thay thế các menu và
thanh công cụ từ phiên bản trước. Cửa sổ Danh mục chính thay thế và mở rộng các chức năng của cửa sổ Database.
Và một điểm mới trong Access 2010, Backstage View cho phép bạn truy cập vào tất cả các lệnh áp
dụng cho toàn bộ cơ sở dữ liệu, như là thu gọn và sửa chữa, hoặc các lệnh từ menu File. lOMoARcPSD| 36991220
Các lệnh được bố trí trên các tab ở phía bên trái của màn hình, và mỗi tab chứa một nhóm các lệnh
có liên quan hoặc các liên kết. Ví dụ, nếu bạn nhấn New, bạn sẽ thấy tập hợp các nút cho phép bạn
tạo ra một cơ sở dữ liệu mới từ đầu, hoặc bằng cách chọn từ một thư viện của cơ sở dữ liệu các
mẫu thiết kế chuyên nghiệp.
2.2.2. Làm việc với các đối tượng trong Access
2.2.2.1 Các đối tượng trong Microsoft Access
Cơ sở dữ liệu là một tập hợp những số liệu liên quan đến một mục đích quản lý, khai thác dữ liệu
nào đó, CSDL trong Access là cơ sở dữ liệu quan hệ gồm các đối tượng chính: Tables, Querys,
Forms, Reports, Pages, Macros, Modules.
Đối tượng bảng (Table):là thành phần quan trọng nhất của tập tin cơ sở dữ liệu Access,
dùng để lưu trữ dữ liệu. Do đó đây là đối tượng phải được tạo ra trước. Bên trong một bảng,
dữ liệu được lưu thành nhiều cột và nhiều dòng.
Truy vấn (Queries): Query là công cụ để người sử dụng truy vấn thông tin và thực hiện
các thao tác trên dữ liệu. Người sử dụng có thể sử dụng ngôn ngữ SQL hoặc công cụ QBE
để thao tác trên dữ liệu.
Biểu mẫu (Forms): là công cụ để thiết kế giao diện cho chương trình, dùng để cập nhật
hoặc xem dữ liệu. Biểu mẫu giúp thân thiện hóa quá trình nhập, thêm, sửa, xóa và hiển thị dữ liệu.
Báo cáo (Reports): là công cụ giúp người dùng tạo các kết xuất dữ liệu từ các bảng, sau
đó định dạng và sắp xếp theo một khuôn dạng cho trước và có thể in ra màn hình hoặc máy in.
Tập lệnh (Macros): Macro là một tập hợp các lệnh nhằm thực hiện một loạt các thao tác
được qui định trước. Tập lệnh của Access có thể được xem là một công cụ lập trình đơn
giản đáp ứng các tình huống cụ thể.
Bộ mã lệnh (Modules): Là công cụ lập trình trong môi trường Access mà ngôn ngữ nền
tảng của nó là ngôn ngữ Visual Basic for Application. ðây là một dạng tự động hóa chuyên
sâu hơn tập lệnh, giúp tạo ra những hàm người dùng tự định nghĩa. Bộ mã lệnh thường dành
cho các lập trình viên chuyên nghiệp.
Công cụ để tạo các đối tượng trong Access được tổ chức thành từng nhóm trong tab Create của thanh Ribbon.
The image part with rel ations hip I D rId40 was not found in the file. lOMoARcPSD| 36991220
Hình 2.1: Tab Create Access
2.2.2.2 Thao tác với các đối
tượng trong Microsoft Access a. Tạo một đối tượng
Để làm việc với từng đối tượng của Microsoft Access, trên thanh công cụ ta chọn tab Create, sau
đó chọn đối tượng cần làm việc.
Ví dụ làm việc với bảng cơ sở dữ liệu, chọn Create→ Chọn Table
b. Thiết kế lại đối tượng
Nếu đối tượng đang đóng: click phải trên đối tượng cần thiết kế lại, sau đó chọn Design view
Cách khác: Chọn vào View → Design View
The image part with rel ations hip I D rId42 was not found in the file. lOMoARcPSD| 36991220
Hình 2.2: Mở giao diện Design để thiết kế bảng dữ liệu
c. Xem nội dung trình bày của một đối tượng
Nếu đối tượng đang mở ở chế độ Design View thì chọn vào nút View.
Nếu đối tượng đang đóng thì click phải chuột trên đối tượng cần xem và chọn Open. d. Xóa một đối tượng
Click phải chuột trên đối tượng cần xóa. Chọn Delete trên Menu Popup. Cách khác: Chọn
rồi nhấn phím delete e. Đổi tên một đối tượng
Click phải chuột lên đối tượng và chọn rename.
Cách khác: nhấn F2 sau đó nhập tên mới. f. Sao chép một đối tượng
Click phải chuột vào đối tượng cần sao chép.
Chọn copy để chép đối tượng vào clipboard.
Click nút Paste trên thanh Menu để chép vào cửa sổ database.
Nhập tên cho đối tượng sao chép.
Đối với kiểu đối tượng Table, ta có thể lựa chọn 1 trong 3 kiểu sao chép :
o Structure only: Sao chép cấu trúc. o Structure and data: Sao chép cấu trúc và dữ liệu.
o Append Data to Exiting Table: Thêm dữ liệu vào một bảng đang tồn tại.
The image part with rel ations hip I D rId43 was not found in the file.
Hình 2.3: Đặt tên bảng
g. Chép dữ liệu từ Microsoft Access sang ứng dụng khác
Có thể xuất dữ liệu sang cơ sở dữ liệu Access khác, hoặc Excel,Word, Pdf … Cách thực hiện:
- Chọn đối tượng muốn xuất sang ứng dụng khác. lOMoARcPSD| 36991220
- Chọn tab External Data.
- Trong nhóm công cụ Export, chọn loại ứng dụng mà bạn muốn xuất dữ liệu: Excel, Text
file, XML file, Word, Access…
Hình 2.4: Chép dữ liệu từ Access sang ứng dụng khác
- Click nút Browse… chỉ định vị trí xuất dữ liệu. -
Click OK hoàn tất việc export dữ liệu.
h. Chép dữ liệu từ ứng dụng khác vào cơ sở dữ liệu hiện thời (Import)
Microsoft Access hỗ trợ import dữ liệu từ các ứng dụng khác như: Excel, ODBC Database,
XML file, Access,… vào cơ sở dữ liệu hiện hành. Cách thực hiện:
- Chọn tab External Data trên thanh công cụ
- Trong nhóm lệnh Import & Link, chọn ứng dụng mà bạn muốn chép dữ liệu. -
Lần lượt làm theo các bước hướng dẫn của Access.
Chép dữ liệu từ Excel vào Access
- Trong cửa sổ làm việc của Access, chọn tab External Data, click nút Excel trong nhóm
lệnhImport & Link.
- Chọn tập tin Excel cần chép (Click nút Browse… để tìm tập tin Excel) → Open →OK,
sau đó thực hiện theo các bước hướng dẫn của Access.
- Chọn sheet chứa dữ liệu cần chép, → Next.
- Chọn dòng làm tiêu đề cho bảng, → Next.
- Chỉ định thuộc tính cho các field → Next.
- Chọn cách tạo khóa cho bảng.
o Let Access add primary key: Access tự tạo khóa. o Chose my own
primary key: Bạn chỉ định khóa.
o No primary key: Không tạo khóa.
- Nhập tên cho bảng → Finish. lOMoARcPSD| 36991220
- Access hiển thị thông báo cho biết hoàn tất quá trình import một file Excel thành một bảng trong Access.
- Chỉ định thuộc tính cho các field → Next.
- Chọn cách tạo khóa cho bảng. o Let Access add primary key: Access tự tạo khóa. o
Chose my own primary key: Bạn chỉ định khóa.
o No primary key: Không tạo khóa.
- Nhập tên cho bảng → Finish.
- Access hiển thị thông báo cho biết hoàn tất quá trình import một file Excel thành một bảng trong Access.
i. Chép dữ liệu từ cơ sở dữ liệu Access khác vào cơ sở dữ liệu hiện hành.
- Trong cửa sổ làm việc của Access, chọn tab External Data, click nút Access trong nhóm lệnh Import & Link.
- Chọn tập tin Access chứa dữ liệu cần chép (Click nút Browse… để tìm tập tin Access) → Open.
Hình 2.5: Mở file dữ liệu từ bên ngoài
- Chọn tùy chọn "Import table, query, form, report, macro and modules into the current
database" để chỉ định vị trí lưu trữ dữ liệu trong cơ sở dữ liệu hiện hành → OK. lOMoARcPSD| 36991220
Hình 2.6: Mở bảng dữ liệu
- Trong cửa sổ Import object, chọn đối tượng cần chép. Có thể chọn nhiều đồng thời đối
tượng hoặc chọn tất cả bằng cách click nút Select All → OK.
2.2.3. Bài tập: Thực hành tạo và quản lý CSDL với Microsoft Access
Trong phần này ta áp dụng tạo CSDL bài tập bài tập 2 với Microsoft Access.
2.2.3.1 Tạo cơ sở dữ liệu quản lý bán hàng của công ty nội thất A với Microsoft Access
Trong phần phân tích và thiết kế cơ sở dữ liệu bán hàng của công ty nội thất A, trong cơ sở dữ liệu có 4 bảng:
CUSTOMER(CustomerID,CustomerName,CustomerAddress,CustomerCity,
CustomerState, CustomerPostalCode)
ORDER(OrderID, OrderDate,CustomerID)
PRODUCT(ProductID, ProductDescription, ProductFinish, ProductStandardPrice)
ORDER_LINE(OrderLineID, OderID, ProductID, OderedQuantity) Mô
tả các bảng như sau: Tên bảng Các cột Kiểu dữ liệu CUSTOMER CustomerID Number CustomerName Text (50) CustomerAddress Text (50) CustomerCity Text (50) CustomerState Text (50) CustomerPostalCode Text (20) lOMoARcPSD| 36991220 ORDER OrderID Number OrderDate Date CustomerID Number PRODUCT ProductID Number ProductDescription Text (50) ProductFinish Text (50) ProductStandardPrice Currency ORDER_LINE OrderLineID Number OderID Number ProductID Number OderedQuantity Number
Bảng 2-2: Thiết kế cơ sở dữ liệu bán hàng của công ty nội thất A
a. Tạo CSDL trong Microsoft Access
- Bước 1: khởi động Microsoft Access.
- Bước 2: Tại cửa sổ khởi động của Access Click nút Blank Database
- Bước 3: Nhập tên cơ sở dữ liệu cần tạo vào trường file name, trong bài tập này ta đặt tên cơ
sở dữ liệu là SaleManager, chọn vào nút browse để chọn đường dẫn đến thư mục lưu trữ
cơ sở dữ liệu mà bạn muốn lưu.
The image part with rel ations hip I D rId47 was not found in the file.
- Bước 4: Chọn vào nút create, sau bước này ta đã tạo được một cơ sở dữ liệu tên là
SaleManager trong hệ quản trị cơ sở dữ liệu Microsoft Access.
b. Tạo các bảng Customer trong cơ sở dữ liệu SaleManager
Bước 1: Trong cửa sổ làm việc của Access, trên thanh Ribbon, click Creat, trong nhóm lệnh Table, click Table Design . lOMoARcPSD| 36991220
Hình 2.7: Giao diện Design bảng dữ liệu -
Trong cột Field Name: Định nghĩa các Fields trong bảng. -
Data Type: Chọn kiểu dữ liệu của Field tương ứng. -
Description: Dùng để chú thích ý nghĩa của Field. -
Field Properties: Thiết lập các thuộc tính của Field, gồm có hai nhóm:
• General: là phần định dạng dữ liệu cho Field trong cột Field name
• Lookup: Là phần quy định dạng hiển thị /Nhập dữ liệu cho Field -
Nhấn ctrl + s để lưu tên bảng. lOMoARcPSD| 36991220
Hình 2.8: Giao diện Design bảng Customer
Thực hiện tạo 3 bảng còn lại tương tự như tạo bảng Customer 2.3
Phân tích CSDL kinh doanh với Mocrosoft Access
Phần này sẽ tập trung vào việc khai thác CSDL kinh doanh trên hệ quản trị cở sở dữ liệu Microsoft Access.
2.3.1. Các kỹ thuật cơ bản
- Kỹ thuật sử dụng SQL - Kỹ thuật thống kê
- Kỹ thuật Pivot Tables và Pivot Charts 2.3.2. Sử dụng SQL
Với ký thuật sử dụng SQL, chủ yếu ta sử dụng nhóm ngôn ngữ thao tác dữ liệu DML để thực hiện
phân tích và khai thác thông tin trong cơ sở dữ liệu Microsoft Access. Trong phần này sẽ tìm hiểu
sâu hơn về ngôn ngữ thao tác dữ liệu (DML) và cach sử dụng chúng.
Như đã biết, ngôn ngữ thao tác dữ liệu gồm hai nhóm phép toán chính:
- Nhóm phép toán lưu trữ.
- Nhóm phép toán tìm kiếm.
Ta sẽ sử dụng hai nhóm phép toán này để phân tích và khai thác cơ sở dữ liệu kinh doanh. lOMoARcPSD| 36991220
2.3.2.1 Nhóm phép toán lưu trữ
a. Thêm dữ liệu vào bảng
Sau khi tạo bảng, ta cần nhập dữ liệu vào bảng trước khi thực hiện các truy vấn. Lệnh SQL sử dụng
để thêm dữ liệu vào bảng là INSERT. Có hai cách thêm dữ liệu vào bảng.
Cách 1: thêm dữ liệu vào bảng không xác định tên cột mà dữ liệu được chèn vào T (chú ý:
các giá trị dữ liệu phải theo thứ tự của các cột khi tạo bảng). Cú pháp: INSERT INTO table_name VALUES
(value1,value2,value3,...);
Ví dụ: lệnh thêm một dòng dữ liệu vào bảng Customer_T (chú ý: các giá trị dữ liệu phải
theo thứ tự của các cột khi tạo bảng)
INSERTINTO Customer_T VALUES
(001, ‘Contemporary Casuals’, ‘1355 S. Himes Blvd.’, ‘Gainesville’, ‘FL’, 32601);
Cách 2: thêm dữ liệu vào các cột xác định cùng với giá trị sẽ được thêm vào. Cú pháp: INSERT
INTO table_name (column1,column2,column3,...) VALUES
(value1,value2,value3,...); Ví dụ: INSERT INTO
Product_T(ProductID,ProductDescription,ProductFinish,ProductStandardP rice)
VALUES (1, ‘End Table’, ‘Cherry’, 175, 8);
b. Xóa dữ liệu trong bảng
Các dòng dữ liệu có thể bị xóa khỏi cơ sở dữ liệu. Để xóa các dòng dữ liệu trong bảng ta dùng câu lệnh DELETE. Cú pháp: DELETE FROM table_name WHERE
some_column=some_value;
Chú ý: câu lệnh WHERE sẽ xác định điều kiện các dòng dữ liệu bị xóa, nếu không có câu lệnh này,
khi thực thi lệnh DELETE thì tất cả các dòng dữ liệu trong bảng sẽ bị xóa.
Ví dụ: Công ty nội thất A không muốn quản lý các khách hàng khu vực Hawaii nữa, họ quyết định
xóa hết dữ liệu các khách hàng ở Hawaii. Câu lệnh SQL sẽ như sau: DELETEFROM Customer
WHERE CustomerState = ‘HI’
Nếu muốn xóa hết dữ liệu về khách hàng của công ty nội thất A. Ta xử dụng câu lệnh: lOMoARcPSD| 36991220 DELETEFROM Customer
c. Sửa dữ liệu trong bảng
Để sửa cơ sở dữ liệu chúng ta phải thông báo cho hệ quản trị cơ sở dữ liệu biết các hàng và các cột có liên quan. Cú pháp: UPDATE table_name SET
column1=value1,column2=value2,...
WHERE some_column=some_value; Chú ý:
Sau câu lệnh SET: thiết lập các giá trị sẽ thay đổi, ví dụ column1=value1 có nghĩa là thiết
lập giá trị của cột column1 có giá trị mới là value1.
Câu lện WHERE chỉ ra điều kiện để những dòng nào sẽ thay đổi giá trị. Cũng giống như
câu lệnh DELETE, nếu không có mệnh đề WHERE thì tất cả các dòng trong bảng sẽ thay đổi giá trị.
Ví dụ: trong cơ sở dữ liệu của công ty nội thất A, ông chủ yêu cầu thay đổi giá của sản
phẩm có ID = 7 thành 775. Câu lệnh được viết như sau: UPDATE Product
SET ProductStandardPrice = 775 WHERE ProductID = 7; lOMoARcPSD| 36991220
2.3.2.2 Nhóm phép toán tìm kiếm
Câu lệnh truy vấn dữ liệu trong SQL chính là câu lệnh SELECT, đây là câu lệnh được sử dụng phổ
biến nhất với mục đích tìm kiếm thông tin trong CSDL quan hệ. Thực hiện câu lệnh này sẽ trả về
một tập kết quả là các bản ghi (các dòng) từ một hoặc nhiều bảng. Các từ khóa liên quan đến câu lệnh SELECT gồm Từ khóa Mô tả SELECT
Lựa chọn danh sách các cột FROM
Danh sách các bảng hoặc các views chứa các cột được chọn trong SELECT WHERE
Là điều kiện để chọn các dòng dữ liệu và điều kiện kết nối các bảng với nhau. GROUP BY
Dùng để kết hợp các bản ghi có những giá trị liên quan với nhau thành các phần
tử của một tập hợp nhỏ hơn các bản ghi. HAVING
Dùng để xác định những bản ghi nào, là kết quả từ từ khóa GROUP BY, sẽ được lấy ra. ORDERBY
Dùng để xác định dữ liệu lấy ra sẽ được sắp xếp theo những cột nào.
Bảng 2-3: Nhóm câu lệnh tìm kiếm Cú pháp đơn giản: SELECT
column_name,column_name… FROM table_name; Hoặc
SELECT *FROM table_name;
Chú ý: Nếu dùng dấu * có nghĩa là chọn tất cả các cột có trong bảng
Ví dụ1: Với cơ sở dữ liệu của công ty nội thất A, hãy đưa ra danh mục các sản phẩm có giá nhỏ
hơn 275$. Câu lệnh như sau: SELECT
ProductDescription, ProductStandardPrice FROM Product WHERE
ProductStandardPrice < 275; lOMoARcPSD| 36991220
Ví Dụ 2:Với cơ sở dữ liệu của công ty nội thất A, Tìm địa chỉ của khách hàng có tên là ‘Home Furnishings’? SELECT
CUST.CustomerName AS Name, CUST.CustomerAddress FROM Customer AS Cust WHERE
Name = ‘Home Furnishings’;
Ngoài ra, trong câu lênh SELECT còn sử dụng các hàm trong SQL để tính toán. Ví dụ hàm AVG()
trả về giá trị trung bình của một cột có kiểu dữ liệu là số học.
Cú pháp: SELECTAVG(column_name) FROM table_name
Ví dụ:Với cơ sở dữ liệu của công ty nội thất A, tính giá trung bình của tất cả các sản phẩm có trong kho?
SELECTAVG (ProductStandardPrice) AS AveragePrice FROM Product;
Cú pháp đầy đủ trong cầu lênh SELECT như sau:
SELECT [DISTINCT], , ... FROM , , ...
[WHERE<điều kiện chọn> ]
[GROUP BY , , ...]
[ORDER BY | [ASC | | DESC ], ...]
[HAVING <điều kiện in kết quả>];
Tất cả các câu lệnh SELECT sau khi thực thi đều trả về kết quả là một bảng dữ liệu (tập hợp các
dòng dữ liệu). Ta có thể coi bảng kết quả này là nguồn cho câu lênh SELECT khác (có nghĩa là có
thể sử dụng câu lênh SELECT như một phần tử sau lệnh FROM).
Có 2 từ khóa đặc biệt thường được sử dụng trong câu lệnh SELECT là DISTINCT và *. Nếu
người dùng không muốn nhìn thấy các dòng dữ liệu trùng lặp nhau trong kết quả của câu lệnh truy
vấn thì sử dụng lệnh SELECTDISTINCT. Sử dụng từ khóa * sau lênh SELECT hoặc WHERE
mục đích để hiển thị tất cả các cột của bảng.
Sử dụng biểu thức trong cầu lênh SELECT
Câu lệnh cơ bản SELECT … FROM … WHERE có thể sử dụng với một số cách sau:
- Có thể tạo các biểu thức toán học trong câu lệnh SELECT hoặc sử dụng các hàm lợi ích mà
SQL cung cấp sẵn như SUM hoặc AVG… Các toán tử toán học thường sử dụng là: +, -, *
(nhân), / (chia). Các toán tử này được sử dụng với bất kì cột nào có kiểu dữ liệu số. lOMoARcPSD| 36991220
Ví dụ: Hãy liệt kê các sản phẩm của công ty nội thất A, giá và giá khi tăng 10% của từng sản phẩm của công ty. Câu lệnh:
SELECT ProductID, ProductStandardPrice, ProductStandardPrice*1.1 AS Plus10Percent FROM Product_T; Kết quả:
Sử dụng các hàm trong câu lênh SELECT
SQL chuẩn đinh nghĩa và phân loại các hàm có sẵn : hàm toán học, các hàm xử lý chuỗi, các hàng
xử lý ngày tháng và các hàm khác. lOMoARcPSD| 36991220
Các hàm toán học thường
MIN(): trả về kết quả là giá trị nhỏ nhất của cột dùng -
Cú pháp: SELECTMIN(column_name) FROM table_name;
MAX(): trả về kết quả là giá trị lớn nhất của cột -
Cú pháp: SELECTMAX(column_name) FROM table_name;
COUNT(): trả về số dòng của cột -
Cú pháp: SELECTCOUNT(column_name) FROM table_name; -
Chú ý: nếu viết SELECTCOUNT(*) FROM table_name; sẽ trả về
số dòng trong cả bảng CSDL
SUM(): trả về giá trị là tổng các giá trị của các dòng trong cột. -
Cú pháp: SELECTSUM(column_name) FROM table_name;
ROUND(): Hàm làm tròn -
Cú pháp: SELECTROUND (column_name) FROM table_name;
MOD(): hàm lấy phần dư
Các hàm xử lý chuỗi thường LOWER(): các ký tự thành chữ thường dùng
UPPER(): chuyển các ký tự thành chữ hoa
SUBSTR(“str”, vị trí, số ký tự): hàm cắt chuỗi str từ một vị trí . Các hàm ngày
tháng NEXT_DAY() : trả về ngày kế tiếp của ngày hiện tại thường dùng
SYSDATE(): trả về ngày hiện tại của hệ thống
YEAR(),MONTH(): trả về năm, tháng của một ngày cho trước Một số hàm phân tích thường dùng
TOP(n): trả về kết quả là n dòng đầu tiên.
- Cú pháp : SELECTTOP number FROM Table_name
Ví dụ1: Có bao nhiêu sản phẩm trong kho của công ty nội thất A
SELECTCOUNT (*) FROM Customer
Ví dụ 2: Tìm sản phẩm có giá nhỏ nhất trong kho của công ty lOMoARcPSD| 36991220
SELECTMIN (ProductStandardPrice) FROM Product;
Sử dụng các ký tự đại diện trong câu truy vấn
Trong một số trường hợp, chúng ta sử dụng các ký tự đại diện trong câu lệnh truy vấn sẽ rất có hiệu
quả. Ký tự đại diện có thể được sử dụng cả trong câu lênh where. Từ khóa LIKE thường được đi
kèm với ký tự đại diện, và thường được dùng để xử lý các trường dữ liệu có kiểu là ký tự.
Ví dụ: Tìm tất cả các sản phẩm có mô tả bắt đầu bằng chữ ‘desk’. SELECT * FROM PRODUCT
WHERE ProductDescription LIKE ‘% desk’;
Tìm tất cả các sản phẩm có mô tả kết thúc bằng chữ ‘desk’ SELECT * FROM PRODUCT
WHERE ProductDescription LIKE ‘desk%’;
Tìm tất cả các sản phẩm có mô tả chứa chữ ‘desk’’ SELECT * FROM PRODUCT
WHERE ProductDescription LIKE ‘%desk%’;
Sử dụng toán tử so sánh trong truy vấn dữ liệu
Chúng ta sử dụng toán tử so sánh trong câu lênh WHERE.
Ví dụ: Tìm các đơn hàng đặt từ 24/10/2010
SELECT OrderID, OrderDate FROM Order
WHERE OrderDate > ‘24-OCT-2010’; Kết quả:
Sử dụng giá trị NULL
Ví dụ: tìm tất cả các khách hàng mà không biết code của họ lOMoARcPSD| 36991220
SELECT * FROM Customer WHERE CustomerPostalCode ISNULL;
Sử dụng toán tử Boolean
Các toán tử logic hoặc boolean thường được dùng cùng với câu lệnh WHERE như: AND, OR,
NOT với một số mục đích sau:
- AND: kết nối hai hay nhiều điều kiện lại. Chỉ trả về kết quả khi tất cả các điều kiện đều đúng
- OR: kết nối hai hay nhiều điều kiện lại , trả về kết quả khi một trong các điều kiện là đúng.
- NOT: phủ định của một biểu thức.
Ví dụ: Liệt kê chi tiết tất cả các sản phẩm là “desk” và “table” có giá trên 300$
SELECT ProductDescription, ProductFinish, ProductStandardPrice FROM Productz
WHERE ProductDescription LIKE ‘%Desk’ OR
ProductDescription LIKE ‘%Table’
AND ProductStandardPrice > 300; Kết quả
The image part with rel ations hip I D rId52 was not found in the file.
Sử dụng IN và NOT IN cùng với danh sách các giá trị
Ví dụ: tìm tất cả các khách hàng sống ở TP Hồ Chí Minh, Hải Phòng, Hà Nội
SELECT CustomerName, CustomerCity, CustomerState FROM Customer
WHERE CustomerState IN (‘HCM’, ‘HNI, ‘HP’);
2.3.3. Các kỹ thuật thống kê
MS Acess cung cấp một số hàm tình toán với những dữ liệu số. Cho mục đích thống kê, một số
hàm được quan tâm đó là các hàm tính toán giá trị trung bình, median, mode. lOMoARcPSD| 36991220
Microsoft Access là một hệ quản trị CSDL cho phép thực hiện các chức năng tính toán với dữ
liệu số. Các câu truy vấn Access cũng phần nào đáp ứng được các mục đích khai thác, phân tích
cơ sở dữ liệu. Các kỹ thuật thống kê trong Acesscó phần khái quát hơn. Một số hàm tính toán cho
mục đích thống kê trong Acess như: mean (i.e., average) và standard deviation (một giới hạn số của các giá trị).
Access sẽ không được tự động tính toán giá trị trung bình. Cũng sẽ không tự động tính toán các
giá trị mode (giá trị thường xảy ra nhất trong một bộ dữ liệu). Để hiểu được các phép tính trong
Microsoft Access cho các giá trị median và mode, cần có kiến thức về Visual Basic và lập trình
SQLnâng cao. Hoặc để thuận tiện nhất cho mục đích thống kê, có thể lựa chọn Excel.
Phần này giới thiệu về hàm tính toán giá trị trung bình mean(avg) và hướng dẫn cách nhập dữ liệu
từ MS Acess sang Excel sau đó tính toán median và mode trong Excel. Tính toán giá trị Mean
Hàm avg (mean) trong MS Access sẽ tính toán giá trị trung bình của một mảng các giá trị. Hình
mô tả bên dưới minh họa cách tạo một ô trong báo cáo để tính giá trị trung bình.
The image part with rel ations hip I D rId53 was not found in the file. lOMoARcPSD| 36991220
Hình 2.9: Cách tạo một ô trong báo cáo để tính giá trị trung bình Chú
ý: Cú pháp cho hàm avg trong Access là=avg([tên trường]).
Hàm avg cũng có thể được gọi để tính toán giá trị trung bình tóm tắt trong 1 kết quả báo cáo từ
các câu truy vấn trong Acess. Màn hình bên dưới minh họa cho việc thiết kế và tính toán cả giá trị
tổng và giá trị trung bình của lượng bán ra trong một vùng.
The image part with rel ations hip I D rId55 was not found in the file. lOMoARcPSD| 36991220
The image part with rel ations hip I D rId56 was not found in the file.
Hình 2.10: Tính toán cả giá trị tổng và giá trị trung bình của lượng bán ra trong một vùng Tính toán Median và Mode
Cách 1: Using Visual Basic Programming
Có thể sử dụng Visual Basic và các câu truy vấn nâng cao để tính toán các giá trị Median và
Mode. Tuy nhiên để làm được điều này, người thực hiện tính toán phải có một kiến thức nhất
định về lập trình SQL cơ bản. lOMoARcPSD| 36991220
Cách 2: Sử dụng Microsoft Excel
So với Visual Basic và SQL nâng cao, Microsoft Excel tỏ ra phù hợp và tiện lợi hơn cho mục đích
tính toán thống kê, nó không yêu cầu kỹ năng đặc biệt nào.
Thực hiện các bước nhập dữ liệu từ Acess sang Excel và tính toán các giá trị median, mode (mean, everage): 1. Mở MS Excel.
2. Chọn Data tab/Get External Data.
3. Chọn From Access và chọn đến file dữ liệu muốn nhập.
4. Bản dữ liệu hoặc truy vấn sẽ được tải.
5. Nhập các hàm tính toán sau vào các ô:
Cho Median: =MEDIAN(CELL:CELL*)
Cho Mode: = MODE (CELL:CELL) Cho
Mean: = AVERAGE (CELL:CELL)
CELL là địa chỉ ô chứa dữ liệu tính toán.
Hình sau minh họa cho việc thiết kế, tính toán các giá trị trên:
The image part with rel ations hip I D rId58 was not found in the file. lOMoARcPSD| 36991220
Hình 2.11: Tính toán Median và Mode
2.3.4. Pivot Tables và Pivot Charts
PivotTable và PivotChart là những công cụ phân tích dữ liệu rất mạnh trong Excel. Chúng có thể
biến những con số dường như vô nghĩa trong một khối dữ liệu khổng lồ thành những con số có
nghĩa. PivotTable và PivotChart rất dễ sử dụng, nhưng chúng cũng có một số vấn đề phức tạp không thể tránh khỏi.
PowerPivot trong Excel có thể tích hợp dữ liệu từ nhiều nguồn khác nhau và sau khi đã tích hợp dữ
liệu, có thể cập nhật dữ liệu ở bất kì thời điểm nào. Một trong những nguồn dữ liệu thường được
“import” vào excel là dữ liệu nguồn là access databases (phiên bản 2003, 2007, 2010).
Đó là một số lý do cần thiết tìm hiểu về Pivot Tables và Pivot Charts. 2.3.4.1
Giới thiệu PivotTable và PivotChart
a. Tạo một báo cáo PivotTable đơn giản Giả
sử có bảng tính sau đây:
Hình 2.12: Bảng dữ liệu tổng hợp bán hàng lOMoARcPSD| 36991220
Đây là một trong những loại bảng tính mà có thể dùng để tạo ra một PivotTable. Dữ liệu thống kê
doanh thu của 3 cửa hàng (store) bán dụng cụ thể thao, đại diện cho 3 miền (region) trong một tuần
(từ ngày 06 đ n ngày 12/6/2005). Cột D là số khách hàng của từng loại dụng cụ thể thao, cột E là
tổng doanh thu, và các cột còn lại là doanh thu chi ti t của từng mặt hàng.
Đây là một số câu hỏi có thể phải trả lời cho bảng tính đó: -
Doanh thu của dụng cụ cắm trại (Camping) tại mỗi miền? -
Tại mỗi cửa hàng, ngày nào trong tuần là ngày đông khách nhất? -
Tại mỗi cửa hàng, mặt hàng nào bán được nhiều nhất? -
Ngày nào trong tuần (nói chung) là ngày bán nhất?
Để trả lợi cho những câu hỏi trên, cần tạo ra một PivotTable để thấy được tổng doanh thu mặt hàng Camping của mỗi miền.
Chọn một ô (bất kỳ ô nào) nằm ở trong vùng chứa dữ liệu muốn tạo PivotTable. Nhấn nút
PivotTable nằm trong nhóm Insert của thanh Ribbon:
The image part with rel ations hip I D rId60 was not found in the file.
Excel sẽ hiển thị hộp thoại Create PivotTable như hình sau:
The image part with rel ations hip I D rId61 was not found in the file. lOMoARcPSD| 36991220
Trong hộp thoại này, địa chỉ của dãy ô chứa dữ liệu (A2:K44) đã được nhập sẵn trong hộp
Table/Range. Nếu như các nút tùy chọn được chọn giống hệt trong hình: Select a table or range
và New Worksheet, nhấn OK để đóng hộp thoại này. Excel sẽ tạo một Sheet mới:
Hình 2.13: Create PivotTable trong excell
Đây là cái vỏ của PivotTable. Trong cái vỏ này: Ở bên trái là vùng báo cáo PivotTable, là vùng sẽ
hiển thị các yêu cầu sau khi đã hoàn tất một PivotTable. Ở bên phải là cửa sổ PivotTable Field
List, là nơi mà sẽ quyết định những dữ liệu nào sẽ được hiển thị trên PivotTable, và cách sắp xếp của chúng.
Trong hộp thoại PivotTable Field List, nhấn vào mục Region để chọn nó. Mục Region này tự
động xuất hiện trong vùng Row Labels của PivotTable Field List, đồng thời PivotTable cũng tự
động hiển thị tên của 3 miền: Midwest, Northeast và South trong côt A: lOMoARcPSD| 36991220
Hình 2.14: Tạo region
Nhấn vào Camping để chọn nó. Ngay lập tức, Sum of Caming xuất hiện trong vùng Values ở phía
cuối PivotTable Field List, đồng thời, ở cột B hiển thị tổng doanh thu mặt hàng Camping ứng với mỗi miền.
Cuối cùng, nhấn vào cái mũi tên nằm bên cạnh mục Sum of Camping trong khung Values, chọn
Value Field Settings trong danh sách mở ra.
The image part with rel ations hip I D rId64 was not found in the file. lOMoARcPSD| 36991220
Hình 2.15: Tạo camping
Hộp thoại Value Field Settings xuất hiện. Nhấn vào nút Number Format. Hộp thoại Format
Cells sẽ mở ra, chọn Currency. PivotTable sinh ra sẽ giống như hình sau:
The image part with rel ations hip I D rId65 was not found in the file.
Hình 2.16: Pivot được sinh ra Một
số khía cạnh khác cần quan tâm của PivotTable:
Khi một PivotTable được kích hoạt, hộp thoại PivotTable Field List sẽ xuất hiện. Những Field
được chọn vào trong báo cáo PivotTable sẽ được in đậm và có một dấu kiểm ở ngay bên cạnh, đồng
thời chúng cũng xuất hiện ở những vùng bên dưới của PivotTable Field List, cho biết vai trò của
chúng trong PivotTable. Để kích hoạt một PivotTable, nhấn vào một ô bất kỳ trong vùng báo cáo
PivotTable. Nếu không muốn kích hoạt nó, nhấn bất kỳ chỗ nào trong bảng tính.
Bên phải của tiêu đề Region trong vùng báo cáo (ô A3) có một nút mũi tên xổ xuống (drop-down
arrow). Nút này có chức năng tương tựchức năng AutoFilter. Nhấn vào đó có thể lựa chọn những
mục muốn xem thuộc danh sách Region, mà mặc định của nó là Select All (xem tất cả). Ví dụ,
muốn xem doanh thu dụng cụ của riêng miền Midwest, nhấn vào nút này, nhấn vào mục (Select
All) để tắt, rồi nhấn vào mục Midwest, kết quả như hình sau:
The image part with rel ations hip I D rId66 was not found in the file. lOMoARcPSD| 36991220
Hình 2.17: Chọn mục Region muốn xem
b. Tạo một báo cáo PivotTable với nhiều cột
Ví dụ ở phần trước chỉ là một trong những báo cáo PivotTable đơn giản nhất. Phần này trình bày
cách tạo một báo cáo PivotTable phức tạp hơn, có nhiều cột cũng như nhiều hàng hơn.
Dưới đây là dữ liệu của những cửa hàng cho thuê băng video:
Hình 2.18: Dữ liệu của cửa hàng cho thuê băng video
Các hàng dữ liệu trong bài này được thiết kế khác với ví dụ trên. Mỗi hàng trong bảng đại diện cho
một thể loại video của một cửa hàng (được ghi ở cột B). Con số ở cột Title là số lượng đầu phim
thuộc thể loại đó. Mục đích của chúng ta là tạo một PivotTable để trình bày những thông tin ở một dạng dễ xem hơn.
Các bước để tạo ra một cái vỏ của PivotTable, đã trình bày ở phần trên. Kết quả hộp thoại
PivotTable Field List như sau:
The image part with rel ations hip I D rId68 was not found in the file. lOMoARcPSD| 36991220
Hình 2.19: PivotTable Field List
Trong danh sách các Field sẽ có Store, Category và Titles. Hãy nhấn chọn cả ba. Excel sẽ làm các việc sau: -
Đem Store và Category vào trong hộp Row Labels -
Đem Tiltes vào trong hộp Values, với tên là Sum of Titles -
Trình bày các thông tin vào vùng báo cáo PivotTable Kết quả được minh họa ở hình sau đây:
The image part with rel ations hip I D rId69 was not found in the file.
Hình 2.20: Kết quả Pivot Table
Chọn lệnh Field Setting, đánh dấu vào các tùy chọn của tab Layout & Print giống như trong hình
sau đây; đổi tên trong ô A3 đang là RowLabels, thành Store. lOMoARcPSD| 36991220
Hình 2.21: Field setting
Trong trường hợp trên, store và category đang nằm chung trong một hàng, đều là Rows và Labels.
Nếu muốn Category là một cột riêng, là Column Labels ta có một cách là chuyển Row Labels
thành Column Labels. Có hai cách làm việc này: -
Trong hộp thoại PivotTable Field List, trong vùng Row Labels, nhấn vào nút mũi tên bên
cạnh mục Category, và chọn lệnh Move To Column Labels. -
Có thể "nắm" và "kéo" cái mục Category ra khỏi vùng Row Labels rồi "thả" nó vào vùng Column Labels.
Sau khi làm xong một trong 2 cách trên, PivotTable giống như sau:
The image part with rel ations hip I D rId71 was not found in the file. lOMoARcPSD| 36991220
Hình 2.22: Kết quả Pivot table sau khi sắp xếp hiển thị
So sánh với bảng dữ liệu ban đầu, PivotTable dễ đọc hơn nhiều. Nhìn vào bảng báo cáo này, có thể
so sánh số lượng đầu phim của từng thể loại video giữa các cửa hàng với nhau (xem theo cột), hoặc
so sánh số lượng đầu phim giữa các thể loại video của một cửa hàng với nhau (xem theo hàng).
Nhìn vào PivotTable, có thể trả lời ngay cửa hàng nào có nhiều phim hành động nhất.
Có thể thấy rằng: việc di chuyển một Field từ Row Labels sang Column Labels, làm cho PivotTable
dường như là xoay bảng dữ liệu từ dọc thành ngang. Chữ "xoay", trong tiếng Anh, là "pivot".
c. Tạo một PivotChart
Một PivotChart không khác biệt hơn so với một biểu đồ Excel, được tạo ra từ dữ liệu của một
PivotTable. Một PivotChart cũng giống như bất kỳ một biểu đồ nào trong Excel, những thao tác
với các biểu đồ, hoặc việc định dạng cho nó.
Nếu cần đến một PivotChart, có thể tạo ra PivotTable và PivotChart cùng một lần. Thay vì nhấn
vào nút PivotTable trong nhóm Insert trên thanh Ribbon, nhấn vào cái mũi tên nhỏ ở dưới và chọn
lệnh PivotChart. Khi tạo xong PivotTable, Excel cũng sẽ tư động tạo luôn một PivotChart đi kèm theo.
Sau đây là cách tạo ra một PivotChart từ PivotTable đã thực hành ở phần b, bài toán về các cửa hàng bán video.
1. PivotTable đang được kích hoạt. Nếu không muốn thấy cái hộp thoại PivotTable Field
List, tắt nó. Hộp thoại này có thể bật tắt bất cứ lúc nào bằng những nút này (trong nhóm
PivotTable/Options trên thanh Ribbon):
The image part with rel ations hip I D rId72 was not found in the file.
Hình 2.23: Nút tắt-bật PivotTable Field List
2. Nhấn nút PivotChart trong nhóm PivotTable|Options trên thanh Ribbon. Excel sẽ hiển thị
hộp thoại Insert Chart, như hình sau: lOMoARcPSD| 36991220
Hình 2.24: Hộp thoại Insert Chart
3. Chọn Column trong danh sách Templates ở bên trái, rồi nhấn vào biểu tượng thứ hai
(stacked column) ở hàng đầu tiên trong khung bên tay phải.
4. Nhấn OK để tạo biểu đồ.
Kết quả như hình bên dưới. Mỗi cửa hàng (Store) được đại diện bởi một "bar" trong biểu đồ, và
trong mỗi "bar" này chứa số lượng thể loại phim (Category) có trong cửa hàng đó, được phân biệt bởi các màu khác nhau.
The image part with rel ations hip I D rId74 was not found in the file. lOMoARcPSD| 36991220
Khi nhấn vào biểu đồ, tức là kích hoạt PivotTable, Excel sẽ hiển thị hộp thoại PivotChart Filter
Pane như ở hình trên. Có thể dùng Axis Fields và Legend Fields trong hộp thoại này để hiển thị
các dữ liệu, điều chỉnh PivotChart hiển thị hay không hiển thị biểu đồ của một loại dữ liệu nào đó.
Bất kỳ việc lọc dữ liệu nào được thực hiện trong hộp thoại này, cũng sẽ được áp dụng trong
PivotTable, và biểu đồ sẽ tự động vẽ lại theo những thay đổi trong PivotTable.
Ví dụ, khi nhấn vào Axis Fields, và chỉ chọn hai cửa hàng: Clarkville, WestEnd. Biểu đồ sẽ tự động vẽ lại như sau:
The image part with rel ations hip I D rId75 was not found in the file.
Hình 2.25: Kết quả PivotTable
2.3.4.2 Tìm hiểu nguồn dữ liệu của PivotTable
Có thể sử dụng nhiều loại dữ liệu để làm nguồn cho một PivotTable: -
Sử dụng dữ liệu từ một bảng tính Excel trong cùng một Workbook. lOMoARcPSD| 36991220 -
Sử dụng dữ liệu từ một bảng tính Excel trong một Workbook khác. -
Sử dụng dữ liệu kết hợp từ nhiều nguồn khác nhau nhưng có cấu trúc giống nhau (consolidate ranges). -
Sử dụng dữ liệu từ một báo cáo PivotTable khác.
a. Sử dụng dữ liệu của cùng một bảng tính Excel
Cách dễ nhất và cũng được nhiều người sử dụng nhất là sử dụng dữ liệu của một bảng tính Excel
để làm nguồn cho PivotTable. Dữ liệu này có thể nằm trong cùng một bảng tính, như đã được trình
bày ở bài phần a mục 2.3.4.1.
Mọi việc sẽ trở nên rất đơn giản khi tạo một PivotTable trong cùng bảng tính chứa dữ liệu nguồn.
Dữ liệu này phải được thiết kế theo dạng một List (trong Excel 2003), một Table (Excel 2010), với những yêu cầu sau: -
Hàng đầu tiên chứa tên các Field (hoặc tên của các cột). -
Hàng thứ hai và những hàng tiếp theo chứa dữ liệu. -
Không có hàng bỏ trống, nhưng có thể có những ô trống.
Nếu có một ô nằm ở đâu đó trong vùng dữ liệu nguồn này được chọn khi mở hộp thoại Create
PivotTable, Excel sẽ tự động xác định địa chỉ của toàn bộ vùng dữ liệu và nhập nó vào trong mục
Table/Range của hộp thoại. Nếu muốn xác định chính xác địa chỉ của vùng dữ liệu nguồn, có thể
chọn một trong ba cách sau:
1. Chọn một ô trong vùng dữ liệu trước khi mở hộp thoại. Địa chỉ của vùng dữ liệu sẽ được nhập vào tự động.
2. Nhập trực tiếp địa chỉ của vùng dữ liệu trong mục Table/Range của hộp thoại.
3. Sử dụng nút Select trong hộp thoại để chọn vùng dữ liệu. lOMoARcPSD| 36991220
Hình 2.26: Chọn vùng dữ liệu trong excel
Một cách hay để chỉ cho Excel biết vùng dữ liệu nằm ở đâu, là tạo một tên (Name) cho vùng dữ
liệu. Sử dụng Name thì tiện lợi hơn việc nhập địa chỉ hoặc chọn vùng dữ liệu mỗi khi muốn tham chiếu đến chúng.
Tuy nhiên, nếu sử dụng một Table của Excel 2010 để làm dữ liệu nguồn cho PivotTable, thì không
cần quan tâm đến Name, bởi chính bản thân Table đã có sẵn một cái tên, khi tạo nó. Và khi đã có
Name của dữ liệu nguồn, chỉ việc nhập trực tiếp cái tên này vào trong mục Table/Range của hộp
thoại Create PivotTable. Ví dụ, ở hình sau đây, dữ liệu nguồn của PivotTable là một dãy có tên là SalesData:
Hình 2.27: Tạo PivotTable dựa vào tên bảng
b. Sử dụng dữ liệu nguồn từ một bảng Excel khác
Nếu dữ liệu dùng trong PivotTable nằm trọn một bảng tính khác, các bước tạo PivotTable sẽ khác
phần a một chút. Cần xác định dãy ô (địa chỉ) chứa dữ liệu nguồn, và tên của workbook chứa dữ liệu nguồn.
Cách dễ nhất để thực hiện nhất, là mở đồng thời hai bảng tính. Một cái sẽ chứa PivotTable, một cái
chứa dữ liệu nguồn. Rồi làm theo các bước sau:
1. Chọn (kích hoạt) workbook sẽ tạo PivotTable.
2. Nhấn nút PivotTable trong tab Insert của Ribbon để mở hộp thoại Create PivotTable.
3. Chọn tùy chọn Select a Table or Range.
4. Nhấn nút Select nằm ở phía bên phải của khung Table/Range, một hộp thoại Create
PivotTable "nhỏ" như sau đây sẽ xuất hiện:
The image part with rel ations hip I D rId78 was not found in the file. lOMoARcPSD| 36991220
5. Nhấn Alt+Tab hoặc nhấn vào workbook chứa dữ liệu nguồn để kích hoạt nó. Sẽ thấy cái
hộp thoại Create PivotTable "nhỏ".
6. Chọn dãy chứa dữ liệu nguồn cho PivotTable. Địa chỉ của nó, bao gồm cả tên của workbook,
sẽ tự động nhập vào trong hộp thoại Create PivotTable "nhỏ"
Hình 2.28: Tạo PivotTable
7. Nhấn vào nút Select trên cửa sổ Create PivotTable “nhỏ” để chấp nhận vùng chọn và đóng
nó lại, quay về hộp thoại Create PivotTable "lớn".
8. Nhấn OK để quay về bảng tính với PivotTable vừa tạo.
Khi một PivotTable được "link" tới một bảng tính bên ngoài, chỉ có thể cập nhật (update) PivotTable
nếu đường link này tới bảng tính nguồn còn tồn tại. Khi bảng tính chứa dữ liệu nguồn bị đổi tên, bị
dời đi chỗ khác, hoặc bị xóa, sẽ không thể cập nhật PivotTable. Excel sẽ hiển thị một thông báo lỗi.
Việc tạo một PivotTable trong một bảng tính khác rất hữu ích trong trường hợp: -
Dữ liệu là một tập dữ liệu khổng lồ, cồng kềnh vì với một tập dữ liệu cồng kềnh như vậy
việc chạy trên cùng một bảng rất chậm dẫn đến việc phải đặt PivotTable trên bảng tính khác. -
Hoặc khi muốn thống kê dữ liệu trên nhiều nguồn khác nhau, có thể tạo một workbook
trung gian, chứa nhiều PivotTable và mỗi PivotTable sẽ link đến một nguồn dữ liệu riêng.
Chú ý: PivotTable không bao giờ tự “refresh” dữ liệu, nghĩa là nó không thể tự động cập nhật khi
dữ liệu nguồn thay đổi bất kể dữ liệu nguồn nằm trong cùng một workbook với PivotTable hay
nằm trong một workbook khác. Việc tính lại bảng tính cũng không cập nhật dữ liệu cho PivotTable.
Để cập nhật dữ liệu cho PivotTable phải thực hiện theo 2 cách:
1. Nhấn chuột phải vào PivotTable và chọn lệnh RefreshData.
2. Nhấn vào nút Refresh nằm trong PivotTable/Option trên Ribbon (chỉ thấy tab này khi đã kích hoạt PivotTable) . lOMoARcPSD| 36991220
c. Sử dụng nhiều dãy dữ liệu có cùng cấu trúc (Multiple Consolidation Ranges)
Thuật ngữ "Multiple Consolidation Ranges" (nhiều dãy dữ liệu có cùng cấu trúc), không phức tạp
như cái tên của nó. Tất cả chỉ đơn giản là tạo một PivotTable dựa trên hai hoặc nhiều bảng dữ liệu.
Những nguồn dữ liệu này có thể nằm trong cùng một bảng tính, hoặc nằm trong những bảng tính khác nhau.
Để sử dụng được Multiple Consolidation Ranges, cần sử dụng đến chức năng PivotTable and
PivotChart Wizard, một chức năng có trong những version trước của Excel. Trong Excel 2010,
chức năng này vẫn còn, nhưng bị ẩn đi. Để gọi nó ra, cần sử dụng những phím tắt dùng để gọi
PivotTable and PivotChart Wizard trong Excel 2003, đó là: Alt+D+P.
Chú ý: tất cả các phím tắt trên menu của Excel 2003 đều có thể sử dụng trong Excel 2010 cho dù
Ribbon của excel 2010 không giống với Ribbon của excel 2003.
Để sử dụng được Multiple Consolidation Ranges, bắt buộc các danh sách làm dữ liệu nguồn cho
PivotTable phải có cùng cấu trúc, có nghĩa là tất cả các tiêu đề cột (nằm trên hàng đầu tiên) và tiêu
đề dòng (nằm trong cột đầu tiên bên trái) phải giống hệt nhau. Nếu như có một cột hoặc một hàng
nào đó nằm riêng lẻ thì chúng sẽ không được tích hợp vào trong báo cáo PivotTable.
Dưới đây là ví dụ về các nguồn dữ liệu có thể sử dụng chức năng Multiple Consolidation Ranges
Hình 2.29: Nguồn dữ liệu có thể sử dụng chức năng Multiple Consolidation Ranges
Để khởi động "Wizard", nhấn tổ hợp phím Alt+D +P. Sau đó bước 1 là nhấn vào tùy chọn Multiple Consolidation Ranges:
The image part with rel ations hip I D rId82 was not found in the file. lOMoARcPSD| 36991220
Hình 2.30: Multiple Consolidation Ranges
Chọn Next để tiếp tục sang bước 2, ở bước này sẽ có hai tùy chọn: -
Create a single page field for me: để Excel tự động tạo ra một Page Field đơn, với mỗi
vùng dữ liệu nguồn là một mục (item) trong Page Field đó. Page Field có thể tạm hiểu như
là chức năng lọc "cấp 1" của PivotTable. Trong cửa sổ PivotTable Field Lists, Page Field
chính là những mục nằm trong khung Report Filter. Nếu không có, xem như Page Field =
All (xem tất cả các dữ liệu). -
I will create the page fields: Dùng tùy chọn này để tạo các Page Field riêng. Có thể tạo tối
đa là 4 Page Field, mỗi Page Field lọc ra một thành phần nào đó của các dữ liệu.
Tạo một trang tổng hợp duy nhất với chức năng Create a single page field for me
Xem lại bảng tính EmployeeData vừa trình bày ở phần trên, có 3 danh sách, mỗi danh sách là một
phương án tuyển dụng nhân viên cụ thể cho các bộ phận. Và cả 3 danh sách đều có cùng cấu trúc,
cũng như tiêu đề của các hàng, cột này là giống nhau. Để tạo một PivotTable dựa vào 3 danh sách
này, hãy sử dụng PivotTable and PivotChart Wizard theo các bước đã trình bày ở phần trước,
và khi đang ở trong bước 2, hãy đánh dấu vào tùy chọn Create a Single-Page Field, rồi nhấn Next
để sang bước 3, rồi theo các bước sau:
1. Nhấn vào nút Select ở bên phải hộp Range.
2. Chọn dãy A3:B9 trong bảng tính.
3. Nhấn lại vào nút Select để đưa dãy vừa chọn vào trong khung Range.
Chú ý: Nếu bỏ qua bước 1 mà chỉ cần dùng chuột chọn trực tiếp dãy A3:B9 trong bảng
tính, Excel sẽ tự hiểu là đã chọn nút Select rồi nên cũng bỏ qua luôn bước 3 này. Khi
kết thúc việc chọn dãy bằng chuột Excel sẽvào bước 4 sau đây.
4. Nhấn nút Add để thêm dãy vừa chọn vào trong danh sách All Ranges.
5. Lập lại bước 1 đến bước 4 để chọn và thêm vào danh sách All Ranges hai dãy D3:D9
và G3:G9. Tới đây, màn hình sẽ giống như sau:
The image part with rel ations hip I D rId83 was not found in the file. lOMoARcPSD| 36991220
Hình 2.31: Tạo một trang tổng hợp duy nhất với chức năng Create a single page field for me
6. Nhấn Next để sang Step 3.
7. Tại đây, có thể chọn New Worksheet để tạo PivotTable trong một Sheet mới, hoặc chọn
Existing worksheet và nhấn chuột vào nơi muốn tạo PivotTable ngay trong bảng tính này. 8. Click Finish.
Cuối cùng, một PivotTable như sau:
The image part with rel ations hip I D rId84 was not found in the file.
Hình 2.32: Tạo một trang tổng hợp duy nhất với chức năng Create a single page field for me
Trong PivotTable này, nhãn Column Labels có một mũi tên xổ xuống, có thể dùng nó để lọc ra cột
muốn xem. Trong trường hợp này, do các dữ liệu nguồn chỉ có một cột (Employees), nên nút này
không cần dùng đến, nhưng nếu các dữ liệu nguồn có nhiều cột, sẽ cần dùng đến nó. Nhãn Row
Labels cũng có một mũi tên xổ xuống, dùng để lọc ra những hàng muốn xem. Những nút xổ xuống
này là điểm đặc trưng mà có thể thấy trong tất cả các PivotTable.
Tạo một PivotTable có nhiều trang tổng hợp từ những dữ liệu nguồn có cùng cấu trúc
Trong công việc hằng ngày, nhất là khi phải thường xuyên tổng hợp những dữ liệu kiểu như: báo
cáo doanh thu quý I, báo cáo doanh thu quý II, báo cáo doanh thu quý III, v.v..., nên sử dụng
PivotTable, với tùy chọn thứ 2 khi đang ở trong bước 2 của PivotTable and PivotChart Wizard:
I will create the page fields. lOMoARcPSD| 36991220
Tùy chọn này rất thích hợp khi tổng hợp từ nhiều nguồn dữ liệu có cùng cấu trúc, mà những nguồn
dữ liệu đó có cùng tiêu đề, chỉ khác vài con số dùng để tổng hợp (theo quý, theo năm, theo tháng...),
chẳng hạn như bảng tính này:
The image part with rel ations hip I D rId85 was not found in the file.
Hình 2.33: Tạo một PivotTable có nhiều trang tổng hợp từ những dữ liệu nguồn có cùng cấu trúc
Đây là 4 bảng tổng hợp số học sinh đạt yêu cầu theo từng môn, và theo từng học kỳ trong hai năm
học 2005, 2006; cả 4 đều có chung tiêu đề Year ... Semester ...
Sử dụng PivotTable and PivotChart Wizard theo các bước 1 và 2 đã trình bày ở trên, và khi đang
ở trong bước 2, đánh dấu vào tùy chọn I will create the page fields, rồi nhấn Next để sang bước
3, rồi theo các bước sau:
1. Nhập dữ liệu nguồn thứ nhất (A2:B8) vào trong khung Range.
2. Nhấn nút Add để thêm nó vào trong danh sách All Ranges.
3. Lập lại hai bước trên ba lần, để thêm 3 khối dữ liệu còn lại (D2:D8, A11:B17, và
D11:E17) vào trong danh sách All Ranges. Tại thời điểm này, màn hình sẽ giống như hình sau:
The image part with rel ations hip I D rId86 was not found in the file. lOMoARcPSD| 36991220
Hình 2.34: Sử dụng tùy chọn I will create the page fields
4. Nhấn vào tùy chọn 2 ở phần How many page fields do you want?
5. Phần đang mờ mờ ở dưới cùng của hộp thoại PivotTable and PivotChart Wizard mở
ra 2 cái: Field one và Field two (chỉ có thể có tối đa 4 trang tổng hợp).
6. Tới đây, tạm dừng một chút để vạch ra một vài ý tưởng cho việc thống kê dữ liệu. Cần
có bao nhiêu trang tổng hợp? Hay nói cách khác, cần thống kê dữ liệu theo mấy tiêu
chí? Hãy nhìn vào tiêu đề của 4 dữ liệu nguồn. Ở đó có hai năm học: 2005 và 2006, và
hai học kỳ (semester): học kỳ 1 và học kỳ 2. Vậy, ý tưởng hay nhất, là thống kê theo
năm để lọc ra dữ liệu của từng năm, hoặc cả 2 năm; và đồng thời cũng thống kê theo
từng học kỳ, để lọc ra dữ liệu của từng học kỳ hoặc cả 2 học kỳ. Như vậy, cần có hai
trang tổng hợp, đó là lý do tại sao ở bước 4 trên đây, nhấn vào tùy chọn 2.
7. Trong danh sách All Ranges, chọn dãy đầu tiên: A2:B8, dãy này là số liệu của học kỳ1 năm 2002.
8. Nhập Year 2002 vào trong Field one.
9. Nhập Semester 1 vào trong Field two.
10. Chọn tiếp dãy thứ hai: D2:E8, dãy này là số liệu của học kỳ 1 năm 2003.
11. Nhập Year 2003 vào trong Field one.
12. Bởi vì trong Field two đã có sẵn Semester 1, nên thay vì nhập, có thể chọn nó từ nút mũi tên xổ xuống.
13. Chọn dãy thứ ba: A11:B17, dãy này là số liệu của học kỳ 2 năm 2002.
14. Chọn Year 2002 từ nút mũi tên xổ xuống cho Field one.
15. Nhập Semester 2 vào trong Field two.
The image part with rel ations hip I D rId87 was not found in the file. lOMoARcPSD| 36991220
16. Chọn dãy cuối cùng: D11:E17, chọn Year 2003 cho Field one và Semester 2 cho Field
two từ các nút mũi tên xổ xuống.
Nhấn Next để hoàn tất PivotTable và đặt nó ở trong cùng Sheet hay trong một Sheet khác. Kết quả
là một PivotTable tổng hợp tất cả số học sinh đạt yêu cầu của cả hai năm học 2002 và 2003, thống
kê chi tiết theo từng môn học:
The image part with rel ations hip I D rId88 was not found in the file.
Hình 2.35: PivotTable hoàn tất
d. Tạo một PivotTable sử dụng dữ liệu nguồn từ một báo cáo PivotTable đã có
Lựa chọn cuối cùng cho việc tạo một PivotTable là dựa vào một PivotTable hoặc một PivotChart
đã có. Nói cách khác, sử dụng Summary Data của một PivotTable đã có làm Raw Data cho báo cáo thứ hai.
Có hai cách để tạo một PivotTable là d a vào một PivotTable đã có: -
Nếu sử dụng Excel 2010, chỉ việc gọi lệnh tạo một PivotTable như vẫn thường làm, và chọn
một PivotTable có sẵn làm dữ liệu nguồn cho nó. -
Nếu sử dụng PivotTable and PivotChart Wizard, đánh dấu vào tùy chọn Another PivotTable
or PivotChart Report ở bước 1 (tùy chọn này chỉ xuất hiện khi bảng tính đã có chứa ít nhất
một PivotTable). Sau khi nhấn Next, một danh sách các PivotTable hoặc PivotChart đang
có, chọn một cái để làm dữ liệu nguồn.
e. Sử dụng dữ liệu từ tài nguyên khác
Excel cho phép sử dụng dữ liệu từ nguồn khác để tạo PivotTable. Có 2 cách làm việc này: -
Liên kết PivotTable với dữ liệu ngoài thông qua việc “import” chúng vào Excel. -
Import dữ liệu ngoài vào Excel, sau đó coi nó như một danh sách dữ liệu Excel bình thường. lOMoARcPSD| 36991220
Mục này sẽ giới thiệu cách liên kết tới nguồn dữ liệu ngoài.
Để tạo một báo cáo PivotTable mà liên kết tới dữ liệu ngoài, cần thực hiện các bước như sau:
1. Hiển thị hộp thoại Create PivotTable
2. Chọn lựa chọn Use an External Data Source
3. Chọn nút Choose Connection, Excel hiển thị hộp thoại Existing Connections
Hình 2.36: Chọn nguồn dữ liệu bên ngoài cho PivotTable
Thông tin hiển thị trên hộp thoại Existing Connections phụ thuộc vào hệ thống cụ thể. Có thể dùng
drop-down trên đầu hộp thoại để hiển thị các loại kết nối dữ liệu.
Đến đây có thể chọn một trong hai cách: -
Chọn kết nối dữ liệu muốn sau đó chon Open. -
Chọn Browse để chọn đường dẫn đế dữ liệu. Bước tiếp theo phụ
thuộc vào kết nối dữ liệu cụ thể đã chọn. f. Sử dụng dữ liệu từ một kết nối
dữ liệu đã tồn tại lOMoARcPSD| 36991220
Nếu muốn tạo PivotTable lấy dữ liệu nguôn la một cơ sở dữ liệu bên ngoai đã có sẵn, các bước thực hiện như sau:
1. Chọn tab Insert, trên thanh Ribbon chọn nút PivotTable. Excel sẽ hiển thị hộp thoại Create PivotTable
The image part with rel ations hip I D rId90 was not found in the file.
Hình 2.37: Tạo Pivot Table lOMoARcPSD| 36991220
2. Chọn Use an External Data Source, sau đó chọn nút Choose Connection để mở hộp
thoại Existing Connections
Hình 2.38: Mở hộp thoại Existing Connections
3. Chọn nguồn dữ liệu bạn muốn sử dụng.
4. Chọn OK để kết thúc.
Đến đây sẽ có hai trường hợp xảy ra, phụ thuộc vào nguồn dữ liệu. Nếu nguồn dữ liệu là một bảng
đơn lẻ, Excel sẽ tạo một PivotTable trống và hiển thị danh sách các trường PivotTable và bạn có
thể xử lý để thiết kết PivotTable.
Nếu dữ liệu có nhiều bảng, bạn phải chọ bảng mà bạn muốn sử dụng làm dữ liệu cho PivotTable,
sau đó chọn OK để tạo một PivotTable trống.
The image part with rel ations hip I D rId91 was not found in the file. lOMoARcPSD| 36991220
Hình 2.39: Chọn bảng bạn muốn làm dữ liệu nguồn cho PivotTable
2.3.5. Bài tập: Thực hành phân tích CSDL kinh doanh với Microsoft Access
Tạo một PivotTable từ Access Database
Một ưu điển của PivotTable là nó không giới hạn sử dụng dữ liệu trong một workbook, trên thực
tế người ta thường lấy dữ liệu từ một cơ sở dữ liệu. Trong phần này sẽ giới thiệt cách tạo một
PivotTable dựa vào dữ liệu trong cơ sở dữ liệu Microsoft Access.
Trong phần này, sẽ sử dụng cơ sở dữ liệu Northwind để làm ví dụ, dữ lấy dữ liệu trong bảng Product
Sales sử dụng cho PivotTable.
Hình 2.40: PivotTable sẽ sử dụng câu truy vấn Product Sales for 1997 Sau
khi khởi động Excel với worksheet trống, các bước làm tiếp như sau:
1. Click vào nút PivotTable trên thanh Insert ribbon để mở hộp thoại Create PivotTable.
2. Chọn lựa chọn Use an External Data Source.
3. Click vào nút Choose Connection để mở hộp thoại Existing Connections. lOMoARcPSD| 36991220
4. Click nút Browse for More để mở hộp thoại Select Data Source.
5. Chọn đến đường dẫn chứa file cơ sở dữ liệu Northwind.mdb
Hình 2.41: Tìm đến đường dẫn chứa file cơ sở dữ liệu Northwind.mdb
6. Click OK để hiển thị hộp thoại Select Table. lOMoARcPSD| 36991220
Hình 2.42: Chọn bảng sử dụng làm dữ liệu cho PivotTable
7. Trong danh sách các bảng chọn Product Sales for 2007
8. Click OK để quay trở lại hộp thoại Create PivotTable
9. Click OK để đóng hộp thoại Create PivotTable và tạo một PivotTable trống
The image part with rel ations hip I D rId95 was not found in the file. lOMoARcPSD| 36991220
Hình 2.43: PivotTable được tạo sử dụng câu truy vấn trong cơ sở dữ liệu Access
Có thể nhìn thấy 4 cột trong câu lệnh truy vấn của access trên danh sách các trường (góc phải phía
bên trên của cửa sổ Excel)
Hình 2.44: Danh sách các trường dữ liệu
Tiếp theo, thêm các trường này vào trong PivotTable trống vừa tạo ra để hiển thị dữ liệu theo ý
mình. Bởi vì có 4 trường, có nhiều cách hiển thị dữ liệu trên PivotTable. Sau đây là các bước tạo
một PivotTable hiển thị tổng doanh số bán hàng (ProductSales) theo nhóm sản phẩm (CategoryName):
1. Chọn trường CategoryName để thêm vào trong vùng Row Labels của PivotTable.
2. Tương tự chọn ShippedQuarter để thêm vào trong vùng Row Labels của PivotTable.
The image part with rel ations hip I D rId97 was not found in the file. lOMoARcPSD| 36991220
Hình 2.45: Trường CategoryName và ShippedQuarter được chọn trong vùng Row Labels
3. Trong vùng Row Labels, click vào mũi tên trên trường ShippedQuarter và chọn mục Move
to Column Labels để di chuyển trường này tới vùng Column Labels.
The image part with rel ations hip I D rId98 was not found in the file.
Hình 2.46: Chuyển trường ShippedQuarter sang vùng Column Labels
4. Tiếp theo, chọn trường ProductSales để chuyển nó tới vùng Values
5. Trong vùng Values, chọn mũi tên Sum of ProductSales để chọn mục Value Field Settings
từ popup menu, Excel sẽ hiển thị hộp thoại Value Field Settings
The image part with rel ations hip I D rId99 was not found in the file. lOMoARcPSD| 36991220
Hình 2.47: Hộp thoại Value Field Settings
6. Click nút Number Format để mở hộp thoại Format Cells.
7. Chọn định dạng Currency.
Hình 2.48: Định dạng tiền tệ
8. Click OK để đóng hộp thoại.
The image part with rel ations hip I D rId101 was not found in the file. lOMoARcPSD| 36991220
Hình 2.49: Một báo cáo PivotTable sau khi hoàn thành
Tài liệu tham khảo
[1]. Modern Database Management (10th Edition), Jeffrey A. Hoffer , Ramesh Venkataraman,
Heikki Topi, Prentice Hall, 2010
[2]. Database Processing : Fundamentals and Design and Implementation (12th Edition), David
Kroenke, David J. Auer, Prentice Hall, 2011
[3]. Moss, R. W. (2004) Strauss's handbook of business information: a guide for librarians,
students, and researchers. Wesport, CT: Greenwood Publishing Group, Inc. [4].
http://www.thuvien-it.net/home/tinhoc/view.asp?threadid=6498