Phân tích thiết kế cơ sở dữ liệu cho Công ty Xi măng Bỉm Sơn

ến sự thành công của một hệ thống thông tin. Do vậy, nếu phân tích thiết kế cơ sở dữ liệu tốt ta sẽ thu được một hệ thống thông tin có hiệu quả cao. Tuy kiến thức và kinh nghiệm thực tế còn hạn chế nhưng chúng em mạnh dạn chọn đề tài: "Phân tích thiết kế cơ sở dữ liệu cho Công ty Xi măng Bỉm Sơn". Đề tài được chia thành 3 phần chính với nội dung: Phần A: Giới thiệu Lời mở đầu Phần B: Nội dung chính I. Mô tả hệ thống xây dựng II. Phân tích yêu cầu hệ thống III. Thiết kế cơ sở dữ liệu mức khái niệm IV. Thiết kế cơ sở dữ liệu mức lôgíc V. Tinh chỉnh thiết kế VI. Thiết kế cơ sở dữ liệu mức vật lý Phần C. Kết luận Lời cảm ơn Với đề tài phân tích thiết kế cơ sở dữ liệu cho Công ty xi măng Bỉm Sơn chúng em đã gặp rất nhiều khó khăn trong quá trình hoàn thiện bài tập của mình. Nhưng dưới sự hướng dẫn tận tình của giảng viên Trần Thị Xuân - là giáo viên bộ môn và sự nổ lực của các thành viên trong nhóm, sự nhiệt tình của các bạn sinh viên trong lớp đã giúp chúng em hoàn thành tốt đề tài này. Trong quá trình làm bài chúng em đã có được rất nhiều kiến thức cũng như kinh nghiệm cho bộ môn thiết kế cơ sở dữ liệu. Từ những bài giảng của giảng viên Trần Thị Xuân, những tài liệu tham khảo và những ý kiến đóng góp của các độc giả rất thiết thực, đầy đủ để có được những kết quả đó chúng em xin chân thành cảm ơn sự giúp đỡ, hướng dẫn của giảng viên Trần Thị Xuân đồng thời chúng em gửi lời cảm ơn tới các bạn độc giả đã nhiệt tình đóng góp ý kiến để chúng em hoàn thành đề tài của mình. Nhóm thực hiện đề tài: I. Mô tả hệ thống 1. Phòng giám đốc Có nhiệm vụ quản lý chung và theo dõi hoạt động kinh doanh của Công ty. Ban giám đốc có quyền quyết định đối với toàn thể Công ty và có quyền tác động đến tất cả các phòng ban trong Công ty. - Có thể sử dụng chương trình để xem danh sách theo từng phòng ban - Xem báo cáo thống kê lương theo từng phòng ban và toàn Công ty - Xem báo cáo thống kê tiền thưởng phạt của Công ty, các báo cáo thuế thu nhập, bảo hiểm xã hội, bảo hiểm y tế, phải nộp cho cơ quan chức năng. - Điều chỉnh hồ sơ nhân viên hoặc số liệu chấm công - Nhập xoá sửa chức vụ cho từng nhân viên trong Công ty 2. Phòng nhân sự Có nhiệm vụ quản lý nhân viên, theo dõi và tuyển dụng nhân viên khi Công ty có nhu cầu, thuyên chuyển công tác, chấm công nhân viên hàng tháng. Có thể sử dụng chương trình để tiến hành cập nhận hồ sơ nhân viên mới. Điều chỉnh mức lương, phòng ban của các nhân viên khi nhân viên được tăng lương hay chuyển đổi phòng ban. Truy xuất, theo dõi báo cáo, chấm công hàng tháng, để nắm được tình hình làm việc hàng tháng của từng nhân viên trong Công ty. Xem danh sách nhân viên theo từng phòng ban, thông tin chi tiết của từng nhân viên. Xoá các nhân viên trong Công ty. 3. Phòng kế toán Có nhiệm vụ tính lương cho các nhân viên trong Công ty Có thể sử dụng chương trình để tiến hành nhập số liệu chấm công hàng tháng của từng nhân viên trong Công ty, kể cả ngoài giờ. Thay đổi số liệu chấm công Tính lương tạm ứng và lương cuối tháng cho nhân viên Lập phiếu lương cho từng nhân viên trong Công ty Lập báo cáo thuế thu nhập, bảo hiểm xã hội, bảo hiểm y tế phải nộp hàng tháng cho các cơ quan chức năng. Tính thưởng phạt cho từng nhân viên theo quy định của Công ty Thống kê lương theo phòng ban Thống kê lương theo chức vụ 4. Phòng công đoàn Sử dụng chương trình để theo dõi hoạt động Đảng, Đoàn của Công ty Sử dụng chương trình để nhập xoá, sửa hồ sơ Đảng, Đoàn của từng nhân viên trong Công ty. Thống kế số lượng Đảng viên, Đoàn viên trong Công ty 5. Phòng marketing Giới thiệu sản phẩm, khảo sát thị trường, tìm thị trường tiêu thụ.v.v… 6. Phòng kế hoạch Xây dựng chiến lược II. Phân tích yêu cầu Ngươi đại diện cho Công ty xi măng Bỉm Sơn đã suy nghĩ về vấn đề này và trình bày tương đối rõ ràng như sau: Công ty chúng tôi muôn mở rộng phạm vi kinh doanh và đưa sản phẩm của công ty tới khách hàng, giới thiệu với khách hàng những đặc tính ưu việt cũng nhưng việc sử dụng xi măng theo các yêu cầu khác nhau các công việc đặt mua xi măng hiện nay vẫn được thường liên hệ qua điện thoại - không đáp ứng được nhu cầu giao dịch. Bởi vậy chúng tôi muốn khách hàng có thể xem chi tiết sản phẩm và đặt hàng qua mạng Internet. Khách hàng sẽ gọi điện tới công tý và nói với chúng tôi số hiệu xi măng, số lượng xi măng, loại xi măng mà khách hàng định mua. Nếu chúng tôi không đủ số lượng hàng cung cấp thì chúng tôi phải nhập thêm nguyên vật liệu và trì hoãn lại ngày giao hàng cho tới khi đủ số lượng hàng. Khi giao hàng chúng tôi muôn giao toàn bộ số lượng xi măng đã được yêu cầu trong cùng một lần, với hoá đơn bán hàng kê khai đầy đủ số xi măng đã bán, với mỗi loại xi măng sẽ được lưu lại trong hoá đơn như sau: Số hiêu xi măng, công ty sản xuất, giá, ngày sản xuất, năm.v.v... Công ty có các khách hàng thường xuyên là các đại lý, cửa hàng nằm trên nhiều địa điểm khách nhau. Với mỗi khách hàng chúng tôi sẽ lưu lại tên và địa chỉ số hiệu thẻ tín dụng trên danh sách. Đối với khách hàng mới thì phải liên hệ trước với chúng tôi để công ty tạo cho một tài khoản trước khi họ có thể sử dụng trang web của chúng tôi và tìm kiếm, đặt trực tuyến trên trang web. III- Thiết kế mức khái niệm. Toàn bộ những thông tin thu nhập được trong bước phát triển yêu cầu chúng tôi sử dụng để xây dựng thêm những mô tả đầu tiên về dữ liệu các ràng buộc trên các dữ liệu đó. Trong bước này chúng ta sẽ phải mô tả dữ liệu dưới dạng mô hình thực thể liên kết. Để xây dựng được mô hình thực thể liên kết chúng ta phải xác định được các kiểu thực thể, các thuộc tính danh, các liên kết. Từ những danh từ trong hệ thống ta xác định được các kiểu thực thể. Nhà cung cấp (Tên nhà cung cấp, Địa chỉ nhà cung cấp) Khách hàng (Mã khách, Tên khách, Địa chỉ khách) Sản phẩm (Mã sản phẩm, Tên sản phẩm, Số lượng mua) Vật liệu (Mã vật liệu, Tên vật liệu, Số lượng nhập, Đơn giá) Ba liên kết được hình thành: Nhập, đặt, sản xuất. Khách hàng và sản phẩm, Nhà cung cấp và vật liệu là những kiểu thực thể chúng ta có thể xác định được đầu tiên. 2 kiểu thực thể khách hàng và sản phẩm kết nối với nhau thông qua liên kết đơn đặt hàng. Với mỗi đơn đặt hàng những thông tin sau cần phải được lưu lại: Số hiệu đơn, Ngày đặt, Số lượng đặt. Tương tự 2 kiểu thực thể nhà cung cấp và vật liệu được kết nối với nhau thông qua hoá đơn nhập, với mỗi một hoá đơn nhập các thông tin sau cần được lưu lại: Số phiếu, Ngày nhập, Số lượng nhập. Khi ngày đặt hàng mang giá trị rỗng, tương ứng với thông tin cho biết là đơn đặt hàng chưa được hình thành khách hàng chưa nhận được sản phẩm. Ngay sau khi đơn đặt hàng được giao, giá trị trong ngày đặt hàng là xác định và khi đó thuộc tính ngày đặt hàng lấy giá trị mặc định là ngày hiện tại. Các ràng buộc toàn vẹn về dữ liệu là điều kiện xác định trên một lược đồ cơ sở dữ liệu nằm trong giới hạn các dữ liệu có thể được lưu trữ trong quan hệ mà nó mô tả. Để có thể tiến hành xây dựng các khai báo cho các thuộc tính của quan hệ ta phải xác định xem thuộc tính đó có phải là thuộc tính định danh không, những giá trị các thuộc tính có thể nhận được là gì?. - Các thuộc tính mã khách, tên khách, mã sản phẩm, tên sản phẩm, mã vật liệu, tên vật liệu là những thuộc tính nhận giá trị kiểu ký tự: char. - Các thuộc tính: Số hoá đơn, Số lượng là những thuộc tính kiểu Number. - Các thuộc tính: Ngày đặt, ngày nhập những thuộc tính kiểu ngày tháng và phải là ngày hiện tại. Đến đây chúng ta đã có một bản thiết kế đầu tiên. Nhưng một câu hỏi được đặt ra: Nếu khách hàng mua sản phẩm với khối lượng lớn để xây dựng nhưng số sản phẩm trong kho không đủ yêu cầu và nhà cung cấp đề nghị khách hàng đến vào ngày mai để lấy số sản phẩm còn lại. Vậy trong trường hợp đó thì xử lý như thế nào? Một ý kiến đưa ra: Chúng ta sẽ coi mỗi lần đặt sản phẩm là thể hiện cho mỗi một đơn đặt hàng và chúng sẽ có giá trị tại thuộc tính ngày đặt hàng khác nhau. Liệu rằng xử lý như vậy có được không. Tất nhiên là sẽ không hợp lý bởi vì khi ta kết nối giữa hai thực thể Khách hàng và Sản phẩm thì 2 thuộc tính Mã khách và Mã sản phẩm khi liên kết với nhau không thể cho ra duy nhất một đơn hàng trong Hoá đơn đặt. Như vậy bản thiết kế này chưa cho phép khách hàng đặt các đơn thiết kế này chưa cho phép khách hàng đặt các đơn hàng khác nhau cho cùng một loại sản phẩm trong những ngày khác nhau. Để giải quyết được vấn đề này chúng ta phải chuyển sang bước thiết kế tiếp theo. Đặt Makhach KHACHHANG Tenkhach Diachi Số hoá đơn Ngày đặt Số lượng đặt SANPHAM Ten SP Ma SP Sản xuất Nhập Tên NCC Nhà cung cấp Đ/C nhà CC Số phiếu Ngày nhập Số lượng nhập Vật liệu Ten VL Ma VL Đơn giá IV- Thiết kế cơ sở dữ liệu mức logic. Trong bước này chúng ta phải chuyển từ sơ đồ thực thể liên kết được xây dựng ở thiết kế cơ sở dữ liệu mức khái niệm sang mô hình quan hệ mức logic với việc tập trung vào các vấn đề chủ yếu: Dữ liệu được biểu diễn như thế nào? Bằng ngôn ngữ gì trong hệ quản trị cơ sở dữ liệu và xác định được các ràng buộc dữ liệu trong quan hệ tức là đưa ra được bảng ràng buộc dữ liệu. - Trước hết chúng ta phải chuyển các kiểu thực thể thông thường thành các quan hệ: + Tên của mỗi một kiểu thực thể chính là tên của quan hệ đó. + Các thuộc tính định danh là khoá chính của quan hệ đó. - Sau đó phải chuyển đổi liên kết giữa các kiểu thực thể đó liên kết. + Giữa khách hàng và sản phẩm là liên kết n-n nên ta phải thêm một kiểu thực thể mới bao gồm các thuộc tính liên kết và 2 thuộc tính khoá của 2 quan hệ Khách hàng và Sản phẩm tạo thành khoá chính của quan hệ mới và đóng vai trò là khoá ngoại lai. + Tương tự cho 2 kiểu thực thể Nhà cung cấp và Vật liệu trong quan hệ khách hàng: Khoá chính là Mã khách. +Trong quan hệ Sản Phẩm : khoá chính là Mã sản phẩm. ịQuan hệ hoá đơn bao gồm: khoá chính là: Số hoá đơn, và ngày đặt, số lượng đặt và hai khoá chính của 2 quan hệ trên đồng thời là khoá ngoại lai: Mã khách, Mã sản phẩm. Khoá chính ký hiệu: nét gạch chân dưới Khoá ngoại lai: nét gạch chân đứt Từ những phân tích trên chúng ta có đượn bảng ràng buộc dữ liệu giữa các quan hệ Để cài đặt bảng ràng buộc dữ liệu này Địa chỉ Tên khách Mã khách sản phẩm Mã sản phẩm Tên sản phẩm Vật liệu Phiếu nhập Khách hàng Hoá đơn đặt Nhà cung cấp Số lượng Mã khách Số hoá đơn Mã sản phẩm Ngày đặt Địa chỉ Tên nhà cung cấp Đơn giá Tên vật liệu Mã vật liệu Mã vật liệu Số lượng Số phiếu nhập Ngày đặt Tên nhà cung cấp V. Tinh chỉnh lược đồ Tại bước này chúng ta phải phân tích tập các quan hệ có thể gây ra sự dư thừa dữ liệu mà trong thiết kế cơ sở dữ liệu chúng ta phải hạn chế được điều này. Cuối cùng phải đưa nó về dạng chuẩn 3NF. - Ta thấy trong quan hệ khách hàng chỉ có một khoá duy nhất là Mã khách, và không có phụ thuộc hàng nào khác trên quan hệ này. Do vậy quan hệ khách hàng ở dạng chuẩn 3NF. - Quan hệ sản phẩm + Có một khoá duy nhất Mã sản phẩm + Không có phụ thuộc hàm nào khác trên quan hệ này nên nó ở dạng chuẩn 3NF - Quan hệ hoá đơn đặt Ta đã xác định ngoài khoá chính là Số hoá đơn còn có cặp khoá (Mã khách và Mã sản phẩm) vì vậy 2 phụ thuộc hàm sau là thoã mãn. Số hoá đơn Mã khách và Số hoá đơn Mã sản phẩm do vậy nó không phải ở dạng chuẩn 3NF => Để đưa quan hệ này về dạng 3NF chúng ta phải phân rã nó ra thành 2 quan hệ: HOADON (Số hoá đơn, Mã khách, ngày đặt) DONGHOADON (Số hoá đơn, Mã sản phẩm, Số lượng đặt) Như vậy 2 quan hệ trên đều ở dạng chuẩn 3NF * Câu lệnh SQL tạo 2 quan hệ: Create table HOADON (Sohoadon Number (2), Ngaynhap Date, Primary key (Sohoadon) Foreign key (Makhach) Refences KHACHHANG ) Diachi Tennhacungcap Diachi Tenkhach Makhach NHACUNGCAP Ngaydat KHACH HANG Ngaynhap Hoadon Hoadon Sohoadonnhap Sohoadon HOADONNHAP HOADONDAT Soluong Donghoadon Donghoadon Don gia Tenvatlieu Mavatlieu VATLIEU SANPHAM Soluong Sản xuất VI- Thiết kế cơ sở dữ liệu mức vật lý. Đây là bước thiết kế lại một phần cơ sở dữ liệu trong mức logic. Ngày đặt Số lượng Mã sản phẩm Mã khách Hoá đơn đặt sản phẩm Khách hàng Số hoá đơn Tên sản phẩm Mã sản phẩm Địa chỉ Tên khách Mã khách Ta có thể gộp về dạng phi chuẩn sau: Hoá đơn đặt Số lượng Địa chỉ khách Tên khách Mã sản phẩm Số hoá đơn sản phẩm Mã sản phẩm Tên sản phẩm Nhà cung cấp mong muốn rằng khách hàng của họ sẽ tìm kiếm sản phẩm thông qua mã sản phẩm trước khi đặt hàng. Việc đặt đơn hàng mua sản phẩm bao gồm: Chèn một bản ghi vào quan hệ HOá đơn và một hoặc nhiều bản ghi vào quan hệ dòng hoá đơn. Nếu số lượng sản phẩm yêu cầu có đủ trong cửa hàng, công việc giao hàng được chuẩn bị và ngày đặt hàng có trong quan hệ dòng hoá đơn được đặt lại giá trị tương ứng. Thêm vào đó, số lượng sản phẩm trong cửa hàng sẽ thay đổi theo thời gian, tăng lên khi nhà cung cấp nhập thêm sản phẩm và giảm đi khi khách hàng mua sản phẩm . Như vậy chúng ta phải bắt đầu bằng việc xem xét đến khi vấn đề tìm kiếm sản phẩm thông qua mã sản phẩm . Vì mã sản phẩm là trường khoá nên việc tìm kiếm trên nó sẽ trả lại nhiều nhất là một bản ghi. Như vậy để tăng tốc độ tìm kiếm khi khách mua của cửa hàng, chúng ta cũng phải tạo một chỉ số băm không phân cụm (unelustered hash index) trên trường mã sản phẩm. Tiếp theo chúng ta phải cân nhắc đến vấn đề cập nhật số lượng sản phẩm. Để cập nhật cho mỗi một sản phẩm, đầu tiên phải tìm đến bản ghi đó thông qua giá trị mã sản phẩm, tệp chỉ số trên sẽ giúp công việc tìm kiếm nhanh hơn. Với quan hệ khách hàng , mỗi một khách hàng mua được xác định bởi một giá trị định danh duy nhất. Do hầu hết các tìm kiếm trên khách hàng là dựa trên các giá trị này nên chúng ta quyết định tạo một tệp chỉ số băm trên trường mã khách để tăng tốc độ tìm kiếm. Sang đến quan hệ hoá đơn , chúng ta nhận thấy rằng có 2 loại truy vấn: Chèn thêm các đơn hàng mới và trả về các đơn hàng đã đặt. Cả hai truy vấn này đều sử dụng thuộc tính Số hoá đơn như là khoá tìm kiếm, do vậy chúng ta quyết định xây dựng tệp chỉ số trên nó. Tuy nhiên phải lựa chọn giữa 2 loại tệp chỉ số là: B-tree hay băm. Trong trường hợp này giá trị các đơn hàng tăng theo thời gian, tương ứng với các giá trị trong thuộc tính Ngày đặt, sắp xếp theo Số hoá đơn cũng hiệu quả như sắp xếp theo Ngày đặt, do vậy chúng ta quyết định lựa chọn loại B-tree phân cụm làm tệp chỉ mục cho trường Số hoá đơn. Trong quan hệ dòng Hoá đơn , hầu hết các thao tác trên quan hệ này là phép chèn, một số phép cập nhật trên thuộc tính ngày đặt, và phép liệt kê trên danh mục các sản phẩm đặt mua của một đơn hàng đã cho. Nếu quan hệ này được sắp xếp Số hoá đơn, khi đó tất cả các phép chèn đơn giản chỉ là việc thêm vào cuối quan hệ và do đó rất hiệu quả. Một tệp chỉ mục phân cụm B-tree nên được xây dựng cho giá trị này, tệp chỉ số này cũng làm tăng tốc độ cho câu lệnh tìm kiếm liệt kê các sản phẩm đã đặt mua của một đơn hàng nào đó. Với phép cập nhật giá trị trong thuộc tính ngày đặt chúng ta cần phải tìm kiếm trên giá trị của hai trường là Số hoá đơn và Mã sản phẩm. Tệp chỉ số trên Số hoá đơn xây dựng phía trên cũng hữu ích cho phép tìm kiếm này, tuy nhiên nếu chúng ta có một tệp chỉ số trên cả hai thuộc tính thì vẫn sẽ nhanh hơn. Tuy nhiên, đối với những truy vấn như tìm kiếm tất cả các đơn hàng của một khách hàng nào đó thì vẫn chậm, bởi khi đó hệ thống vẫn phải tìm kiếm trên cả hai loại quan hệ cũ và mới, nhưng việc tìm kiếm này là có thể chấp nhận được và nó ít khi xảy ra, thường là các báo cáo tháng, quý, năm của cửa hàng. Để cải đặt bẳng ràng buộc dữ liệu chúng ta phải sử dụng ngôn ngữ trong hệ quản trị SQL để biểu diễn dữ liệu. Tất cả các thuộc tính khoá (khoá chính, khoá ngoại lai, khoá duy nhất) đều được khai báo bởi các lệnh của ngôn ngữ SQL. + Primary key (khoá chính) ràng buộc này không cho phép 2 giá trị trùng nhau hoặc các giá trị NULL trên cùng một cột hoặc tập các cột đã được chọn. + Foreign key (khoá ngoại lai) ràng buộc này đòi hỏi mỗi giá trị phải được lấy từ bảng thuộc tính. Create table KHACH HANG (Makhach char (3), Tenkhach char (20), Điachi char (10), Primary key (Makhach) Create table SANPHAM (Masanpham char (3), Tensanpham char (10) Primary key (Masanpham) ) Create table HOADONDAT (Sohoadon Number (2), Soluong Number (4), Ngaydat Date, Primary key (Sohoadon, Makhach, Masanpham) Foreign key (Makhach) References KHACHHANG Foreign key (Masanpham) References SANPHAM ) Create table NHACUNGCAP (Tennhacungcap char (20), Diachi char (10), Primary key (Tennhacungcap) ) Create table VATLIEU (Mavatliêu char (10), Tenvatliêu char (20), Dongia Number (5), Primary key (Mavatliêu) ) Create table PHIEUNHAP (Sophieunhap Number (5), Ngaynhap date, Soluongnhap Number (10) Primary key (Sophieunhap, Mavatlieu, Tenvatlieu) Foreign key (Mavatlieu) References VATLIEU Foreign key (Tennhacungcap) References NHACUNGCAP Create table DONGHOADON (Sohoadon Number (2), Soluong Number (4), Ngaydat Date, Primary key (Sohoadon) Foreign key (Masanpham) References SANPHAM * Quan hệ nhà cung cấp có một khóa duy nhất là tên nhà cung cấp và không có phụ thuộc hàm nào khác trên quan hệ đó nên nó cũng là chuẩn 3NF. Quan hệ Vật liệu + Khóa chính duy nhất: Mã vật liệu + Không có phụ thuộc hàm nào khác => Nó ở dạng chuẩn 3NF Quan hệ phiếu nhập Ngoài khóa chính số phiếu nhập, quan hệ này còn chứa 2 khóa ngoại lai đồng thời là khóa chính: Mã vật liệu và tên nhà cung cấp. Vì vậy tồn tại 2 phụ thuộc hàm sau: Số phiếu nhập Mã vật liệu Số phiếu nhập Tên nhà cung cấp Nên nó ở dạng chuẩn 3NF => Đưa nó về dạng chuẩn 3NF Hóa đơn nhập (Số phiếu nhập, Mã vật liệu, Ngày nhập) Dòng hóa đơn nhập (Số phiếu nhập, Tên nhà cung cấp, Số lượng) * Các câu lệnh SQL tạo 2 quan hệ Create table HOADONNHAP (Sophieunhap Number (5), Ngaynhap Date, Primary key (Sophieunhap) Foreign key (Mavatlieu) Refences HOADONNHAP Create table DONGHOADONNHAP ( Sophieunhap Number (5), Ngaynhap Date, Primary key (Sophieunhap) Foreign key (Tennhacungcap) Refences DONGHOADONNHAP Mục lục Trang ._.