Bài 2 Các đối tượng trong CSDL


Cùng tìm hiểu và cách tạo ra các đối tượng chính trong SQL: Database, Table, View ... 


Khái niệm về cơ sở dữ liệu

  • Database dùng để
    • Chứa các bảng, bảng ảo, thủ tục nội,…
    • Mỗi database có một danh sách các người dùng
      • Người dùng phải có quyền truy cập database
      • Có thể phân nhóm người dùng để cấp quyền
      • Tử phiên bản SQL Server 2000 hỗ trợ Application Role
  • Các database hệ thống Master, Model, Tempdb, msdb
  • Các database ví dụ AdventureWorks, AdventureWorkDWs

Khái niệm về cơ sở dữ liệu

Các tập tin vật lý lưu trữ cơ sở dữ liệu

  • Một database bao gồm tối thiểu hai file
    • .mdf: lưu trữ các đối tượng trong database như table, view, …
      • Có thể bổ sung thêm các tập tin lưu trữ khác
      • Tổ chức tốt các tập tin lưu trữ giúp tăng tốc độ xử lý
    • .ldf: lưu trữ quá trình cập nhật/thay đổi dữ liệu
      • Hỗ trợ phục hồi dữ liệu
      • Hỗ trợ backup/restore dữ liệu
  • Các thông số về kích thước
    • Initial size
    • File growth
    • Maximum file size

sql server database engine

Tạo database

USE master
GO
--Nếu đã tồn tại database QLBanHang rồi thì xóa đi và tạo mới
IF EXISTS (SELECT *  FROM MASTER.sys.DATABASES WHERE NAME=N'QLBanHang')
    --Xóa Database
    DROP DATABASE QLBanHang
GO
--Tạo database mới
CREATE DATABASE QLBanHang

Khái niệm về bảng

  • Bảng dùng để lưu trữ các thông tin của một đối tượng trong thực tế
    • Gồm có các dòng và các cột
    • Bảng trong CSDL thường có khoá chính (primary key)
    • Các bảng thường liên hệ với nhau bằng các mối quan hệ
    • Bảng được tạo trong các Schema (mặc định là schema dbo)
  • Bảng có thể có các ràng buộc (constraint), trigger

Các thuộc tính của bảng

  • Tên bảng
  • Tên cột
  • Kiểu dữ liệu
    • Độ dài dữ liệu
    • Số ký số lưu trữ
    • Số số lẻ lưu trữ
  • Thuộc tính trên cột
    • Allow null
    • Identity
    • Default value

Tạo cấu trúc bảng đơn giản

CREATE TABLE Tên_schema.Tên_bảng
(
Tên_cột1 Kiểu_dữ_liệu [NOT NULL] ,
Tên_cột2 Kiểu_dữ_liệu [NOT NULL] [, ...]
)

Ví dụ tạo bảng

CREATE TABLE vattu
(
    mavtu CHAR(4),
    tenvtu nvarchar(100) NOT NULL,
    dvtinh    nvarchar(10),
    phantram REAL,
    --Tạo khóa chính
    CONSTRAINT pk_vattu PRIMARY KEY(mavtu)
)

Khái niệm về bảng ảo

  • Được xây dựng từ câu truy vấn SELECT để hiển thị dữ liệu từ một hay nhiều bảng
    • Tập hợp dữ liệu, thể hiện cùng một dữ liệu nhưng theo nhiều cách khác nhau
    • Làm việc tương tự như một bảng nhưng không lưu trữ dữ liệu
    • Cho phép thêm/xoá/sửa
    • Bảo mật dữ liệu, bảo mật nội dung câu truy vấn dữ liệu
  • Một số hạn chế trong câu lệnh SELECT
    • Order By
    • Compute
    • Compute By

Tạo mới bảng ảo bằng CREATE VIEW

CREATE VIEW Tên_bảng_ảo
[(Tên_các_cột)]
[WITH ENCRYPTION]
AS Câu_lệnh_SELECT
[WITH CHECK OPTION]

Ví dụ tạo bảng ảo

CREATE VIEW vw_Vattu AS
SELECT * FROM vattu

Xem và cập nhật dữ liệu bảng ảo

  • Làm việc như một bảng thông thường sử dụng câu SELECT để xem dữ liệu Select * From vw_DonDH
  • Sử dụng INSERT/UPDATE để cập nhật dữ liệu
    • Chỉ có thể cập nhật vào một bảng
    • Để INSERT dữ liệu vào bảng, bảng ảo phải thỏa mãn các yêu cầu về khóa, ràng buộc khóa ngọai, các cột NOT NULL, các cột tính toán, order by, group by, distinct
  • Sử dụng Delete để xoá dữ liệu: bảng ảo tạo từ hai hay nhiều bảng không thể xoá
  • Có thể xây dựng các trigger trên bảng ảo

Cập nhật dữ liệu qua bảng ảo sử dụng trigger INSTEAD OF

  • Bảng ảo có nhiều hạn chế khi thực hiện cập nhật dữ liệu
    • Group By, Order By, Distinct
    • Thiếu cột khoá
    • Ràng buộc toàn vẹn
  • SQL Server cung cấp loại trigger INSTEAD OF
    • Cơ chế tương tự như trigger thông thường
    • Mở rộng khả năng cập nhật, tính toán dữ liệu, đặc biệt với bảng ảo
    • Xem thêm phần TRIGGER

Bài tập: Các đối tượng trong CSDL

Tạo các đối tượng trong CSDL sau:

Danh mục Vật Tư (VATTU)

Tên cột Ý nghĩa Kiểu Độ rộng
MaVtu Mã vật tư Char 4
TenVTu Tên vật tư VarChar 100
DvTinh Đơn vị tính VarChar 10
PhanTram Tỷ lệ phần trăm Real  

create table vattu sql

Danh mục Nhà Cung Cấp (NHACC)

Tên cột Ý nghĩa Kiểu Độ rộng
MaNhaCC Mã nhà cung cấp Char 3
TenNhaCC Tên nhà cung cấp NVarChar 100
DiaChi Địa chỉ NVarChar 200
DienThoai Điện thoại NVarChar 20
go
create table nhacc
(
	manhacc char(3),
	tennhacc	nvarchar(100) not null,
	diachi	nvarchar(200),
	dienthoai	nvarchar(20),
	constraint pk_nhacc primary key(manhacc)
)

Đơn đặt hàng (DONDH)

Tên cột Ý nghĩa Kiểu Độ rộng
SoDh Số đơn đặt hàng Char 4
NgayDh Ngày đặt hàng DateTime  
MaNhaCC Mã nhà cung cấp Char 3
go
create table dondh
(
	sodh char(4),
	ngaydh	datetime,
	manhacc char(3),
	constraint pk_dondh primary key(sodh)
)

Chi tiết Đơn đặt hàng (CTDONDH)

Tên cột Ý nghĩa Kiểu Độ rộng
SoDh Số đơn đặt hàng Char 4
MaVTu Mã vật tư Char 4
SlDat Số lượng đặt hàng Int  
GO
CREATE TABLE ctdondh
(
	sodh CHAR(4),
	mavtu	CHAR(4),
	sldat INT,
	CONSTRAINT pk_ctdondh PRIMARY KEY(sodh,mavtu)
)

Phiếu nhập hàng (PNHAP)

Tên cột Ý nghĩa Kiểu Độ rộng
SoPn Số phiếu nhập hàng Char 4
NgayNhap Ngày nhập hàng Datetime  
SoDh Số đơn đặt hàng Char  
go
create table pnhap
(
	sopn	char(4) ,
	ngaynhap datetime default  getdate(),--Ngày ngập sẽ bằng ngày hiện tại
	sodh	char(4),
	constraint pk_pnhap primary key(sopn)
)

Chi tiết nhập hàng (CTPNHAP)

Tên cột Ý nghĩa Kiểu Độ rộng
SoPn Số phiếu nhập hàng Char 4
MaVTu Mã vật tư Char 4
SlNhap Số lượng nhập hàng Int  
DgNhap Đơn giá nhập hàng Money  
go
create table ctpnhap
(
	sopn char(4),
	mavtu	char(4),
	slnhap int,
	dgnhap	money,
	constraint pk_ctpnhap primary key(sopn,mavtu)
)

Phiếu xuất hàng (PXUAT)

Tên cột Ý nghĩa Kiểu Độ rộng
SoPx Số phiếu xuất Char 4
NgayXuat Ngày xuất hàng Datetime  
TenKh Tên khách hàng NVarchar 100
go
create table pxuat
(
	sopx char(4),
	ngayxuat	datetime default getdate(),--Ngày xuất bằng ngày hiện tại
	tenkh	nvarchar(100),
	constraint pk_pxuat primary key(sopx)
)

Chi tiết xuất hàng (CTPXUAT)

Tên cột Ý nghĩa Kiểu Độ rộng
SoPx Số phiếu xuất Char 4
MaVTu Mã vật tư Char 4
SlXuat Số lượng xuất hàng Int  
DgXuat Đơn giá xuất hàng Money  
go
create table ctpxuat
(
	sopx char(4),
	mavtu	char(4),
	slxuat int,
	dgxuat money,
	constraint pk_ctpxuat primary key(sopx,mavtu)
)

Tồn kho (TONKHO)

Tên cột Ý nghĩa Kiểu Độ rộng
NamThang Năm tháng Char 6
MaVTu Mã vật tư Char 4
SLDau Số lượng tồn đầu kỳ Int  
TongSLN Tổng số lượng nhập trong kỳ Int  
TongSLX Tổng số lượng xuất trong kỳ Int  
SLCuoi Số lượng tồn cuối kỳ, cột Formula Int  
GO
CREATE TABLE tonkho
(
	namthang	CHAR(6),
	mavtu	CHAR(4),
	sldau	INT,
	tongsln	INT,
	tongslx	INT,
	slcuoi AS sldau+tongsln-tongslx,
	CONSTRAINT pk_tonkho PRIMARY KEY(namthang,mavtu)
)

Sử dụng câu lệnh INSERT INTO VALUES để thêm các mẩu tin vào bảng

nhập dữ liệu cho bảng nhà cung cấp

INSERT INTO nhacc
VALUES('C01', N'Lê Minh Trí', N'54 Hậu Giang Q6 HCM' ,'8781024')
INSERT INTO nhacc
VALUES('C02', N'Trần Minh Thạch', N'145 Hùng Vương Mỹ Tho', '7698154')
INSERT INTO nhacc
VALUES('C03', N'Hồng Phương', N'154/85 Lê Lai Q1 HCM', '9600125')
INSERT INTO nhacc
VALUES('C04', N'Nhật Thắng', N'198/40 Hương Lộ 14 QTB HCM', '8757757')
INSERT INTO nhacc
VALUES('C05', N'Lưu Nguyệt Quế',N'178 Nguyễn Văn Luông Đà Lạt','7964251')
INSERT INTO nhacc
VALUES('C07',N'Cao Minh Trung', N'125 Lê Quang Sung Nha Trang', N'Chưa có')
GO

nhập dữ liệu cho bảng nhà cung vật tư

--Table Vattu
INSERT INTO vattu
VALUES('DD01',N'Đầu DVD Hitachi 1 đĩa',N'Bộ','40')
INSERT INTO vattu
VALUES('DD02', N'Đầu DVD Hitachi 3 đĩa', N'Bộ','40')
INSERT INTO vattu
VALUES('TL15', N'Tủ lạnh Sanyo 150 lit', N'Cái', '25')
INSERT into vattu
VALUES('TL90', N'Tủ lạnh Sanyo 90 lit', N'Cái', '20')
INSERT INTO vattu
VALUES('TV14', N'Tivi Sony 14 inches', N'Cái', '15')
INSERT INTO vattu
VALUES('TV21', N'Tivi Sony 21 inches', N'Cái', '10')
INSERT INTO vattu
VALUES('TV29' ,N'Tivi Sony 29 inches', 'Cái' ,'10')
INSERT INTO vattu
VALUES('VD01', N'Đầu VCD Sony 1 đĩa',N'Bộ', '30')
INSERT INTO vattu
VALUES('VD02',N'Đầu VCD Sony 3 đĩa', N'Bộ', '30')
GO

nhập dữ liệu cho bảng đơn đặt hàng và phiếu nhập

--Table Dondh
INSERT INTO dondh
VALUES('D001', '01/15/2009', 'C03')
INSERT INTO dondh
VALUES('D002', '01/30/2009', 'C01')
INSERT INTO dondh
VALUES('D003' ,'02/10/2009' ,'C02')
INSERT INTO dondh
VALUES('D004', '02/17/2009' ,'C05')
INSERT INTO dondh
VALUES('D005' ,'03/01/2009', 'C02')
INSERT INTO dondh
VALUES('D006', '03/12/2009' ,'C05')
GO


--Table Pnhap
INSERT INTO pnhap
VALUES('N001', '01/17/2009', 'D001')
INSERT INTO pnhap
VALUES('N002', '01/20/2009', 'D001')
INSERT INTO pnhap
VALUES('N003', '01/31/2009', 'D002')
INSERT INTO pnhap
VALUES('N004','02/15/2009', 'D003')
GO

nhập dữ liệu cho bảng chi tiết đơn đặt hàng và chi tiết phiếu nhập

--Table ctdondh
INSERT INTO ctdondh
VALUES('D001', 'DD01', '10')
INSERT INTO ctdondh
VALUES('D001' ,'DD02' ,'15')
INSERT INTO ctdondh
VALUES('D002' ,'VD02', '30')
INSERT INTO ctdondh
VALUES('D003' ,'TV14' ,'10')
INSERT INTO ctdondh
VALUES('D003' ,'TV29', '20')
INSERT INTO ctdondh
VALUES('D004' ,'TL90' ,'10')
INSERT INTO ctdondh
VALUES('D005' ,'TV14', '10')
INSERT INTO ctdondh
VALUES('D005', 'TV29' ,'20')
INSERT INTO ctdondh
VALUES('D006' ,'TV14', '10')
INSERT INTO CTDONDH
VALUES('D006' ,'TV29' ,'20')
INSERT INTO CTDONDH
VALUES('D006' ,'VD01' ,'20')
GO


--Table ctpnhap
INSERT INTO ctpnhap
VALUES('N001', 'DD01', '8' ,'2,500,000')
INSERT INTO ctpnhap
VALUES('N001' ,'DD02' ,'10' ,'3,500,000')
INSERT INTO ctpnhap
VALUES('N002', 'DD01', '2', '2,500,000')
INSERT INTO ctpnhap
VALUES('N002' ,'DD02' ,'5', '3,500,000')
INSERT INTO ctpnhap
VALUES('N003', 'VD02', '30', '2,500,000')
INSERT INTO ctpnhap
VALUES('N004', 'TV14', '5', '2,500,000')
INSERT INTO ctpnhap
VALUES('N004' ,'TV29' ,'12', '3,500,000')
GO

nhập dữ liệu cho bảng phiếu xuất

--Table pxuat
INSERT INTO pxuat
VALUES('X001', '01/17/2009', N'Nguyễn Ngọc Phương Nhi')
INSERT INTO pxuat
VALUES('X002', '01/25/2009', N'Nguyễn Hồng Phương')
INSERT INTO pxuat
VALUES('X003', '01/31/2009', N'Nguyễn Tuấn Tú')
GO

nhập dữ liệu cho bảng chi tiết phiếu xuất

--Table ctpxuat
INSERT INTO ctpxuat
VALUES('X001', 'DD01', '2', '3,500,000')
INSERT INTO ctpxuat
VALUES('X002' ,'DD01', '1' ,'3,500,000')
INSERT INTO ctpxuat
VALUES('X002' ,'DD02', '5', '4,900,000')
INSERT INTO ctpxuat
VALUES('X003', 'DD01' ,'3' ,'3,500,000')
INSERT INTO ctpxuat
VALUES('X003' ,'DD02', '2', '4,900,000')
INSERT INTO ctpxuat
VALUES('X003' ,'VD02', '10' ,'3,250,000')
GO

nhập dữ liệu cho bảng tồn kho

--Table tonkho
INSERT INTO tonkho
VALUES('200901' ,'DD01', '0', '10', '6')
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200901', 'VD02', '0' ,'30' ,'10' )
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'DD01' ,'4' ,'0', '0')
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'DD02' ,'8' ,'0', '0' )
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200901' ,'DD02', '0', '15' ,'7' )
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'VD02', '20', '0' ,'0' )
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'TV14', '5', '0', '0')
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'TV29' ,'12' ,'0', '0' )
GO

Đối tượng bảng ảo (view)

  • Tạo view có tên vw_CTPNHAP bao gồm các thông tin sau: số phiếu nhập hàng, mã vật tư, số
    lượng nhập, đơn giá nhập, thành tiền nhập
     
  • Tạo view có tên vw_CTPNHAP_VT bao gồm các thông tin sau: số phiếu nhập hàng, mã vật tư, tên vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập
  • Tạo view có tên vw_CTPNHAP_VT_PN bao gồm các thông tin sau: số phiếu nhập hàng, ngày nhập hàng, số đơn đặt hàng, mã vật tư, tên vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập
  • Tạo view có tên vw_CTPNHAP_VT_PN_DH bao gồm các thông tin sau: số phiếu nhập hàng, ngày nhập hàng, số đơn đặt hàng, mã nhà cung cấp, mã vật tư, tên vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập
  • Tạo view có tên vw_CTPNHAP_loc bao gồm các thông tin sau: số phiếu nhập hàng, mã vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập. Và chỉ liệt kê các chi tiết nhập có số lượng nhập > 5
  • Tạo view có tên vw_CTPNHAP_VT_loc bao gồm các thông tin sau: số phiếu nhập hàng, mã vật tư, tên vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập. Và chỉ liệt kê các chi tiết nhập vật tư có đơn vị tính là Bộ
  • Tạo view có tên vw_CTPXUAT bao gồm các thông tin sau: số phiếu xuất hàng, mã vật tư, số lượng xuất, đơn giá xuất, thành tiền xuất
  • Tạo view có tên vw_CTPXUAT_VT bao gồm các thông tin sau: số phiếu xuất hàng, mã vật tư, tên vật tư, số lượng xuất, đơn giá xuất
  • Tạo view có tên vw_CTPXUAT_VT_PX bao gồm các thông tin sau: số phiếu xuất hàng, tên khách hàng, mã vật tư, tên vật tư, số lượng xuất, đơn giá xuất
  • Sửa view vw_CTPXUAT_VT_PX để bổ sung thêm cột thành tiền xuất
  • Xem nội dung câu lệnh SELECT của view vw_CTPXUAT
  • Xoá view vw_CTPXUAT

Bài viết tiếp theo

Chia sẽ bài viết :