<span class="text_page_counter">Trang 1</span><div class="page_container" data-page="1">

TRƯỜNG ĐẠI HỌC MỞ HÀ NỘI

<b>KHOA CÔNG NGHỆ THÔNG TIN</b>

<b>---BÁO CÁO BÀI TẬP LỚNMÔN: HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU</b>

<b><NHÓM 10>Đề tài: Quản Lý Nhân Viên</b>

<i><b> Giáo Viên Hướng dẫn: Nguyễn Thị Tâm Sinh Viên thực hiện: Đào Đức Lộc- 2010A02</b></i>

<i> Bùi Tuấn Anh-2010A02 Phí Minh Quân- 2010A02 Đặng Văn Công- 2010A02</i>

Hà Nội, năm 2021

</div><span class="text_page_counter">Trang 2</span><div class="page_container" data-page="2">

<b>PHÂN CÔNG VÀ KẾ HOẠCH THỰC HIỆN</b>

<b>HỌ VÀ TÊN NỘI DUNGĐÁNH GIÁGHI CHÚ</b>

Đào Đức Lộc Bùi Tuấn Anh Phí Minh Qn Đặng Văn Cơng

</div><span class="text_page_counter">Trang 3</span><div class="page_container" data-page="3">

MỤC LỤC

<b>I.Phát biểu bài toán...2</b>

1. Xây dựng mơ hình thực thể liên kết...2

2.Xây dựng mơ hình cơ sở dữ liệu quan hệ...2

3.Chuẩn hóa cơ sở dữ liệu của bài toán...2

<b>II.Các bảng cơ sở dữ liệu của bài toán:...2</b>

1. Xây dựng các bảng của bài toán...2

4. Tạo Trigger kiểm soát ràng buộc dữ liệu...2

5. Phân quyền cho các thành viên...2

6. Phân tán... 2

</div><span class="text_page_counter">Trang 4</span><div class="page_container" data-page="4">

<b>I.Phát biểu bài toán</b>

Để quản lý nhân viên được diễn ra ổn định, ta cần xây dựng một hệ thống quản lý nhân viên tốt, hiệu quả. Muốn quản lý được nhân viên ta cần tìm hiểu các hoạt động chính và các chủ thể cần quản lý.

Ta có:

Để tiện theo dõi và quản lý nhân viên, mỗi <b>nhân viên</b> cần có các thông tin sau: mã nhân viên, tên nhân viên, ngày sinh, địa chỉ,SĐT . Quy định mỗi nhân viên có một mã nhân viên, ngày vào làm ,độ tuổi u cầu là trên 18 tuổi

<b>Phịng ban cần có các thơng tin: mã phịng ban để xác định cùng với tên phòng</b>

ban và mã dự án mà phong ban phu trách

<b>Thời gian cơng tác cần có các thơng tin: Mã hợp đồng để phân biệt các hợp</b>

đồng, HĐLĐ phải chứa nhân viên , ngày bắt đầu nhậm chức và chức vụ của nhân viên

<b>Chức vụ cần có các thơng tin: Mã chức vụ phân loại với tên chức vụ</b>

<b>Lương cần có các thơng tin: Ngày hưởng,số ngày nghỉ, tạm ứng của nhân viên</b>

và bậc lương mà nhân viên đó được hưởng

<b>Hệ số lương: Hệ số lương là hệ số thể hiện sự chênh lệch tiền lương giữa các</b>

mức lương theo ngạch, cần có các thơng tin: Hệ số Lương, lương tương ứng

<b>Tiền phụ cấp: </b>Phụ cấp lương là khoản tiền trả cho người lao động ngoài tiền lương cơ bản nhằm trả đủ những hao phí lao động tăng thêm, cần có các thơng tin: Mức phụ cấp, phụ cấp tương ứng

</div><span class="text_page_counter">Trang 5</span><div class="page_container" data-page="5">

1. Xây dựng mơ hình thực thể liên kết

Theo bài tốn trên, chúng ta xác định được thực thể và thuộc tính:

<b>Nhân Viên: sMaNV, sTenNV, dNgaySinh, dNgayVaoLam, sDiaChi,sSodienthoaiPhòng Ban: sMaPB, sTenPB, sDiaDiem.</b>

<b>Thời gian công tác: dNgayBDnhamchuc.Chức Vụ: sMaCV,sTenCV.</b>

<b>Lương: dNgayHuong, dSongaynghi, fTamungHệ số lương: iHesoluong, fLuongtuongungTiền Phụ Cấp: iMucpgucap, fTientuongung</b>

Xác định liên kết giữa các thực thể:

<b>Giữa 2 tập thực thể NhanVien </b>và <b>PhongBan có mối liên kết “thuộc” vì một PhongBanCó nhiều NhanVien </b>và mỗi <b>NhanVien chỉ thuộc một PhongBan</b>

=> Mối liên kết giữa 2 tập thực thể có kiểu liên kết là “ 1 – n ”.

<b>Giữa 2 tập thực thể NhanVien </b>và <b>ThoiGianCongTac </b>có mối liên kết “thỏa ước” vì một

<b>NhanVien </b>có các <b>chức vụ </b>vào các thời gian khác nhau => Mối liên kết giữa 2 tập thực thể có kiểu liên kết là “ 1 – n ”.

<b>Giữa 2 tập thực thể ChucVu </b>và <b>ThoiGianCongTac </b>có mối liên kết “chứa” vì một chỉ có

<b>một ChucVu </b>của một nhân viên được nhận vào thời gian khác nhau => Mối liên kết giữa 2 tập thực thể có kiểu liên kết là “1 – n”.

<b>Giữa 2 tập thực thể NhanVien và ChucVu</b> có mối liên kết “đảm nhiệm” vì 1 <b>NhanVienchỉ đảm nhiệm 1 ChucVu </b>

=> Mối liên kết giữa 2 tập thực thể có kiểu liên kết là “1 – 1”

<b>Giữa 2 tập thực thể NhanVien và Luongcó mối liên kết “hưởng” trong thời điểm khác</b>

nhau

=> Mối liên kết giữa 2 tập thực thể có kiểu liên kết là “1 – n”

</div><span class="text_page_counter">Trang 6</span><div class="page_container" data-page="6">

Giữa 2 tập thực thể <b>Lương </b>và <b>Hệ số lương </b>có mối liên kết “chứa” vì 1 mức Lương có nhiều hệ số lương

=> Mối liên kết giữa 2 tập thực thể có kiểu liên kết là “1 – n”

Giữa 2 tập thực thể <b>Lương </b>và <b>Tiền phụ cấp có mối liên kết “chứa” vì 1 mức Lương có</b>

nhiều hệ số phụ cấp

=> Mối liên kết giữa 2 tập thực thể có kiểu liên kết là “1 – n”

</div><span class="text_page_counter">Trang 7</span><div class="page_container" data-page="7">

<b>Mơ hình ER: </b>

2.Xây dựng mơ hình cơ sở dữ liệu quan hệ * Quy tắc với thực thể mạnh

<b>NhanVien (s MaNV</b> , sTenNV, dNgaySinh, dNgayVaoLam, sDiaChi,sSodienthoai).

<b>PhongBan (sMaPB, sTenPB, sDiaDiem).</b>

</div><span class="text_page_counter">Trang 8</span><div class="page_container" data-page="8">

<b>ThoiGianCongTac(sMaNV, sMaCV, dNgayBDnhamchuc)</b>

<b>Luong(sMaNV, iHesoluong, iMucphucap, dNgayhuong, fTamung, iSongaynghi)</b>

* Quy tắc với liên kết 1-1:

<b>NhanVien ( s MaNV</b> , sMaCV,sTenNV, dNgaySinh,dNgayVaoLam ,sDiaChi, sSodienthoai, sMaPB,).

= > Cơ sở dữ liệu cuối cùng:

<b>NhanVien ( s MaNV</b> , sMaCV,sTenNV, dNgaySinh,dNgayVaoLam ,sDiaChi, sSodienthoai, sMaPB,).

<b>ThoiGianCongTac(sMaNV, sMaCV, dNgayKT)</b>

<b>Luong(sMaNV, iHesoluong, iMucphucap, dNgayhuong, fTamung, iSongaynghi)ChucVu(sMaCV, sTenCV).</b>

<b>PhongBan (sMaPB, sTenPB).</b>

<b>TienPhuCap(iMucphucap, fTientuongung).HeSoLuong(iHesoluong, fLuongtuongung).</b>

3.Chuẩn hóa cơ sở dữ liệu của bài tốn

Từ mơ hình quan hệ ta suy ra các phụ thuộc hàm:

MaNV TenNV, NgaySinh, ĐiaChi, ĐienThoai, sMaPB, sMaCV

</div><span class="text_page_counter">Trang 9</span><div class="page_container" data-page="9">

sMaNV, sMaCV dNgayBDnhamchuc

sMaNV iHesoluong, iMucphucap, dNgayhuong, fTamung, iSongaynghi Từ các phụ thuộc hàm trên ta thấy:

- Mọi thuộc tính đều là nguyên tố, giá trị các thuộc tính trên bộ là đơn trị, khơng có thuộc tính nào có giá trị được tính tốn từ một số thuộc tính khác.

Lược đồ quan hệ trên đã ở dạng chuẩn 1NF.

- Mọi thuộc tính khơng khóa của quan hệ đều phụ thuộc hàm đầy đủ vào khóa chính.

Lược đồ quan hệ trên đã ở dạng chuẩn 2NF.

- Mọi thuộc tính khơng khóa của quan hệ đều khơng phụ thuộc bắc cầu vào bất kì khóa chính của quan hệ.

Lược đồ quan hệ trên đã ở dạng chuẩn 3NF.

<i>Vậy lược đồ quan hệ đã được chuẩn hóa về dạng cao nhất – 3NF</i>

</div><span class="text_page_counter">Trang 10</span><div class="page_container" data-page="10">

<b>Mơ hình LK Database Diagrams</b>

</div><span class="text_page_counter">Trang 11</span><div class="page_container" data-page="11">

<b>II.Các bảng cơ sở dữ liệu của bài toán:</b>

1. Xây dựng các bảng của bài toán

<b>- tblNhanVien: sMaNV, sTenNV, dNgaySinh, sDiaChi, dNgayvaolam, sMaCV, </b>

STT Tên thuộc tính Kiểu dữ liệu Ràng buộc Ghi chú

<b>- tblChucvu: sMaCV, sTenCV</b>

STT Tên thuộc tính Kiểu dữ liệu Ràng buộc Ghi chú

<b>- tblPhongban: sMaPB, sTenPB</b>

STT Tên thuộc tính Kiểu dữ liệu Ràng buộc Ghi chú

<b>- tblHesoLuong: iHesoluong, fLuongtuongung</b>

STT Tên thuộc tính Kiểu dữ liệu Ràng buộc Ghi chú

</div><span class="text_page_counter">Trang 12</span><div class="page_container" data-page="12">

<b>- tblTienPhuCap:iTMucphucap, fTientuongung</b>

STT Tên thuộc tính Kiểu dữ liệu Ràng buộc Ghi chú

<b>- tblThoiGianCongTac: sMaNV, sMaCV, dNgayBD</b>

<b>- tblLuong: sMaNV, iHesoluong, iMucphucap, dNgayhuong, fTamung, iSongaynghi</b>

</div><span class="text_page_counter">Trang 13</span><div class="page_container" data-page="13">

--Bang thoi gian cong tac CREATE TABLE thoigiancongtac(

ALTER TABLE dbo Luong . ALTER COLUMN sMaNV NVARCHAR(20) --Bang tien phu cap

CREATE TABLE TienPhuCap( iMucphucap INT NOT NULL,

</div><span class="text_page_counter">Trang 14</span><div class="page_container" data-page="14">

ALTER TABLE dbo NhanVien . ADD CONSTRAINT FK_Nhanvien_chucvu FOREIGN KEY(sMaCV) REFERENCES dbo.ChucVu sMaCV( ),

CONSTRAINT FK_Nhanvien_PhongBan FOREIGN KEY(sMaPB) REFERENCES dbo PhongBan sMaPB. ( )

ALTER TABLE dbo thoigiancongtac . ADD CONSTRAINT

FK_thoigiancongtac_nhanvien FOREIGN KEY(sMaNV)REFERENCES dbo NhanVien sMaNV. ( ),

</div><span class="text_page_counter">Trang 38</span><div class="page_container" data-page="38">

Sau khi:

</div><span class="text_page_counter">Trang 39</span><div class="page_container" data-page="39">

Câu 5.

--Câu 5: Cập nhật tên phòng ban thì mã phịng ban của bảng nhân viên cũng thay đổi CREATE TRIGGER pd_phong

</div><span class="text_page_counter">Trang 40</span><div class="page_container" data-page="40">

DECLARE @MaPhgCu nvarchar( ),9 @MaPhgMoi nvarchar( )9 SELECT @MaPhgCu = sMaPB FROM DELETED

SELECT @MaPhgMoi = sMaPB FROM INSERTED

If ( EXISTS (select sMaPB from NhanVien Where sMaPB @MaPhgCu = ) ) UPDATE NHANVIEN SET sMaPB @MaPhgMoi=

WHERE sMaPB = @MaPhgCu END

END Dữ liệu ban đầu

</div><span class="text_page_counter">Trang 41</span><div class="page_container" data-page="41">

Dữ liệu sau khi update

Câu 6:

--Câu 6: xóa Phịng ban thì nhân viên của phịng ban đó cũng bị xóa create trigger x_phongban PhongBanon

for delete as begin

declare @mapb nvarchar( )20 select @mapb sMaPB = from Deleted

if ( EXISTS (select sMaNV from NhanVien Where sMaPB @mapb= ) ) delete NhanVien where sMaPB @mapb =

end

</div><span class="text_page_counter">Trang 42</span><div class="page_container" data-page="42">

Dữ liệu ban đầu

Dữ liệu sau khi xóa

</div><span class="text_page_counter">Trang 43</span><div class="page_container" data-page="43">

5. Phân quyền cho các thành viên

--Tạo User cho các thành viên

create login daoducloc with password= '123456789' go

create login buituananh with password= '123456789'

</div><span class="text_page_counter">Trang 44</span><div class="page_container" data-page="44">

create user ducloc for login daoducloc create user tuananh for login buituananh create user minhquan for login phiminhquan create user vancong for login dangvancong grant select on [dbo].[NhanVien] to public grant select on [dbo].[PhongBan] to public grant select on [dbo].[ChucVu] to public

--Quyền insert,update,delete khachhang User ducloc grant insert,update,delete on Nhanvien duclocto go

--Quyền insert,update,delete nhanvien User tuananh grant insert,update,delete on [dbo].[ChucVu] tuananhto go

--Quyền insert,update,delete Luong User minhquan grant insert,update,delete on [dbo].[Luong] minhquanto go

--Quyền insert,update,delete phong ban User vancong grant insert,update,delete on [dbo].[PhongBan] vancongto go

--Thu Hồi

--Thu hồi quyền xóa Nhanvien revoke delete on Nhanvien from ducloc go

6. Phân tán

5.1 Thực hiện phân tán ngang bảng tblKhachHang sao cho khách hàng có địa chỉ tại Ha Noi vào trạm 1 còn địa chỉ khác vào trạm 2

-- Tạo bảng tblKhachHang bên PhanTan Tram1

</div><span class="text_page_counter">Trang 45</span><div class="page_container" data-page="45">

Create Table tblkhachhangtram1( sMaKH varchar(10) Not Null Primary Key,

-- Tạo bảng tblKhachHang bên PhanTan_Tram2 Create Table tblkhachhangtram2(

sMaKH varchar(10) Not Null Primary Key,

-- Gán Tên cho Link đến Trạm2

create synonym KHtram2 for Link.Quanlybanhang dbo tblkhachhangtram2. . -- Insert dữ liệu vào bảng trạm 1

insert into btl.dbo tblkhachhangtram1 sMaKH sTenKH sGioiTinh sDiachi sDienThoai. ( , , , , ) select from * tblKhachHang where sDiachi = N'Ha Noi'

--- Insert dữ liệu vào bảng trạm 2

insert into KHtram2 select from * tblKhachHang where sDiachi != N'Ha Noi'

</div><span class="text_page_counter">Trang 46</span><div class="page_container" data-page="46">

-- Tạo bảng sản phẩm bên DB PhanTan_Tram1 Create Table tblSanPhamtram1(

sMaSP varchar(10) Not Null Primary key, sTenSP nvarchar(30),

sMaLoaiSP varchar( ),10 sMaNCC varchar(10), )

-- Tạo bảng tblKhachHang bên PhanTan_Tram2 Create Table tblSanPhamtram2(

sMaSP varchar(10) Not Null Primary key, iSoLuong int,

fGiaHang float )

-- Gán Tên cho Link đến Trạm2

create synonym KHtram2 for Link.Quanlybanhang dbo tblkhachhangtram2. . -- insert dữ liệu vào trạm 1

insert into btl.dbo tblSanPhamtram1 smaSP stenSP smaloaiSP smancc. ( , , , ) select smaSP stenSP smaloaiSP, , ,smancc from tblsanpham

-- insert dữ liệu vào trạm 2

insert into SPtram2 select smaSP,isoluong,fgiahang from tblsanpham -- Dữ liệu bên trạm 1

-- Dữ liệu bên trạm 2

</div><span class="text_page_counter">Trang 47</span><div class="page_container" data-page="47">

--- 5.3 tạo thủ tục thực hiện phân tán ngang bảng tblKhachHang sao cho khách hàng có địa chỉ tại Bắc Giang vào trạm 1 còn lại vào trạm 2

create procedure ThutucKH

insert into KHtram2

values (@ma @ten @gioitinh, , ,@diachi @dienthoai, )

</div><span class="text_page_counter">Trang 48</span><div class="page_container" data-page="48">

-- Dữ liệu bên trạm 1

-- Dữ liệu bên trạm 2

</div>