H


  : D10CN1  : 1021040024 


III. Cơ sở dữ liệu mức logic

IV. Thiết lập các phụ thuộc hàm và chuẩn hóa các lược đồ quan
hệ về dạng chuẩn 3

V. Thực hiện 10 câu truy vấn bằng ngôn ngữ SQL

I. 

.

- 
- 
-  II. 

1. NHANVIEN

Field Name
Data Type
MaNV
Text
HoTenNV
Text
MucLuong
Text
DCNV
Text
2. KHACHHANG

Field Name
Data Type
MaKH
Text
HoTenKH
Text
DCKH
Text
SDTKH
Int


Data Type
MaHD
Int
MaKH
Text
MaNV
Text
NgayLap
Date/Time HOADON
MaKH

NgayLap
MaNV

MaHD


CHITIETHD
MaHD
TongTien
MaTA
SoLuong
DonGia 5. THUCDON

Field Name
Data Type
MaTA
Text

THUCDON
DonViTinh
TenTA
DonGia
MaTA

Mô hình thực thể liên kết E-R

NHANVIEN
HOADON
KHACHHANG
CHITIETHD


III.   a.  -  ( A) MaKH
HoTenKH
DCKH
SDTKH -   B) MaHD
MaKH
MaNV
NgayLap -  (g C) MaHD
MaTA

b.  MaKH
HoTenKH
DCKH
SDTKH
MaHD
MaKH
MaNV
NgayLap



MaTA
TenTA
DonViTinh
DonGia c. - -SQL


2. 

r = 
(# > 20 tuoi)
(A))

Result= 
(TenKH)
(r)= 
(TenKH)
( 
(# > 20 tuoi)
(A))

3.  

r= 
.T#.DonGia>100000)
(E) Result= 
#)
(
DonGia>100000)
(E))

4. 

r= 

((DiaChi=) AND (NgayLap
(A  B))

7.   Result = 
(name)
( 
(HoaDon>4000000)
(B))

8. 

r = 
(NhanVien.N# > 20 tuoi)
(N)

Result= 
(TenNV)
(r)= 
(TenNV)
( 
(NhanVien.N# > 20 tuoi)
(D))

9. Li p  Result = 

BCD

.

  KHACHHANG 

- HOADON: MaHD(A), MaKH(B), MaNV(C), NgayLap(D)

2={AB,C,D} 

.

  HOADON 

- CHITIETHD: MaHD(A), MaTA(B), DonGia(C), SoLuong(D),
TongTien(E)

3={AB,BCDE} 3={A,B}

CHITIETHD 



CHITIETHD :

CHITIETHD={AB}

CHITIETHD_f(BCDECDE}

- NHANVIEN: MaNV(A), HoTenNV(B), SDTNV(C),


WHERE DCKH = NinhBinh AND TenKH=T

2. .

SELECT KHACHHANG.TenKH

FROM KHACHHANG

WHERE Tuoi > 20

3.  

SELECT THUCDON.TenTA.DonGia

FROM THUCDON

WHERE DonGia > 100000

4. 

SELECT KHACHHANG.TenKH, CHITIETHD.TongTien

FROM KHACHHANG, CHITIETHD

WHERE TongTien > 2000000

5.  

SELECT CHITIETHD.TongTien

9. Li p 

SELECT QUANLIHD.NgayLap

FROM QUANLIHD

WHERE MONTH(QUANLIHD.NgayLap) = 9

10. 

SELECT NHANVIEN.DCNV

FROM NHANVIEN

WHERE DCNV = HaNoi