Bài 4 SQL Server căn bản - Database mẫu để học SQL Server

Để tiện cho việc học thì ta cần một database mẫu, và tất cả những thao tác sau này đều dựa trên database này, tên của nó là BikeStores (cửa hàng xe đạp).

Trước tiên bạn hãy xem qua cấu trúc danh sách các table đã nhé.

1. Cấu trúc database BikeStores

Hình ảnh dưới đâu là cấu trúc database BikeStores.

Được chia làm hai nhóm như sau:

• Nhóm bán hàng gồm (schema tên là sales): Customers, Staffs, Orders, Stores, Order_items
• Nhóm sản phẩm gồm (schema tên là production): Categories, Products, Stocks, Brands.

Một số chú ý về mô hình:

• Dấu sao (*) thể hiện cho khóa chính
• Các kí hiệu về mối liên hệ được thể hiện qua các đường kéo giữa các bảng. Mình sẽ không giải thích phần này nhé, nó là phần kiến thức căn bản.

2. Giải thích về các tables

Nếu bạn chưa hiểu cấu trúc ở trên thì hãy tham khảo phần giải thích sau.

table sales.stores

Đây là bảng chứa thông tin của cửa hàng, mỗi cửa hàng có tên, thông tin liên hệ như phone, email, và địa chỉ gồm: street, city, state, zip code. Khóa chính của bảng này là store_id.

CREATE TABLE sales.stores (

 store_id INT IDENTITY (1, 1) PRIMARY KEY,

 store_name VARCHAR (255) NOT NULL,

 phone VARCHAR (25),

 email VARCHAR (255),

 street VARCHAR (255),

 city VARCHAR (255),

 state VARCHAR (10),

 zip_code VARCHAR (5)

);

Table sales.staffs

Đây là bảng chứa thông tin nhân viên của cửa hàng, gồm các thông tin như first name, last name và các thông tin khác như email, phone.

Mỗi nhân viên sẽ làm việc tại một cửa hàng và mối liên hệ của nó chính là khóa ngoại staffs.store_id trỏ đến stores.store_id.

Mỗi nhân viên sẽ được quản lý bởi một trưởng nhóm, vì trưởng nhóm cũng là nhân viên nên sẽ có một khóa ngoại manager_id trỏ đến chính staff_id của bảng này.

Nếu một nhân viên bị đuổi và không làm việc tại một cửa hàng nào cả thì sẽ thiết lập active = 0.

CREATE TABLE sales.staffs (

 staff_id INT IDENTITY (1, 1) PRIMARY KEY,

 first_name VARCHAR (50) NOT NULL,

 last_name VARCHAR (50) NOT NULL,

 email VARCHAR (255) NOT NULL UNIQUE,

 phone VARCHAR (25),

 active tinyint NOT NULL,

 store_id INT NOT NULL,

 manager_id INT,

 FOREIGN KEY (store_id) 

        REFERENCES sales.stores (store_id) 

        ON DELETE CASCADE ON UPDATE CASCADE,

 FOREIGN KEY (manager_id) 

        REFERENCES sales.staffs (staff_id) 

        ON DELETE NO ACTION ON UPDATE NO ACTION

);

Table production.categories

Đâu là bảng chứa chuyên mục sản phẩm, ví dụ xe đạp địa hình, xe đạp người lớn, xe đạp trẻ em, ...

CREATE TABLE production.categories (

 category_id INT IDENTITY (1, 1) PRIMARY KEY,

 category_name VARCHAR (255) NOT NULL

);

Table production.brands

Đây là bảng chứa thương hiệu của sản phẩm, tức là thông tin về công ty sản xuất xe đạp.

CREATE TABLE production.brands (

 brand_id INT IDENTITY (1, 1) PRIMARY KEY,

 brand_name VARCHAR (255) NOT NULL

);

Table production.products

Đây là bảng quan trọng nhất, chứa danh sách của sản phẩm.

Mỗi sản phẩm sẽ có một nhà sản xuất (brand_id), đây là khóa ngoại trỏ đến bảng brands.

Mỗi sản phẩm sẽ thuộc một chuyên mục nào đó (category_id), đây là khóa ngoại trỏ đến bảng categories.

CREATE TABLE production.products (
 product_id INT IDENTITY (1, 1) PRIMARY KEY,
 product_name VARCHAR (255) NOT NULL,
 brand_id INT NOT NULL,
 category_id INT NOT NULL,
 model_year SMALLINT NOT NULL,
 list_price DECIMAL (10, 2) NOT NULL,
 FOREIGN KEY (category_id) 
        REFERENCES production.categories (category_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (brand_id) 
        REFERENCES sales.brands (brand_id) 
        ON DELETE CASCADE ON UPDATE CASCADE
);

Table sales.customers

Bảng thông tin khách hàng, gồm các thông tin như: first name, last name, phone, email, street, city, state and zip code.

CREATE TABLE sales.customers (
 customer_id INT IDENTITY (1, 1) PRIMARY KEY,
 first_name VARCHAR (255) NOT NULL,
 last_name VARCHAR (255) NOT NULL,
 phone VARCHAR (25),
 email VARCHAR (255) NOT NULL,
 street VARCHAR (255),
 city VARCHAR (50),
 state VARCHAR (25),
 zip_code VARCHAR (5)
);

Table sales.orders

Bảng chứa các đơn hàng của khách hàng, gồm các thông tin của khách hàng và thông tin sản phẩm cần mua.

Mỗi đơn hàng sẽ có ba khóa ngoại đó là:

• customer_id: khoa ngoại trỏ đến bảng customers
• store_id: khóa ngoại trỏ đến bảng stores
• staff_id: khóa ngoại trỏ đến bảng staffs

CREATE TABLE sales.orders (
 order_id INT IDENTITY (1, 1) PRIMARY KEY,
 customer_id INT,
 order_status tinyint NOT NULL,
 -- Order status: 1 = Pending; 2 = Processing; 3 = Rejected; 4 = Completed
 order_date DATE NOT NULL,
 required_date DATE NOT NULL,
 shipped_date DATE,
 store_id INT NOT NULL,
 staff_id INT NOT NULL,
 FOREIGN KEY (customer_id) 
        REFERENCES sales.customers (customer_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (store_id) 
        REFERENCES sales.stores (store_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (staff_id) 
        REFERENCES sales.staffs (staff_id) 
        ON DELETE NO ACTION ON UPDATE NO ACTION
);

Table sales.order_items

Đây là bảng chứa thông tin quan trọng của từng sản phẩm trong của đơn hàng như số lượng, đơn giá một đơn hàng như: số lưuọng, giá bán.

Tại sao cần bảng này? Vì thực tế giá cả có thể thay đổi theo thời gian, hôm nay bán 200k, mai bán 300k là chuyện bình thường, vì vậy ta phải lưu giá lại để sau này biết là sản phẩm này đã bán ra bao nhiêu.

Nó có hai khóa chính đó là :

• order_id: khóa ngoại trỏ đến bảng orders
• product_id: khóa ngoại trỏ đến bảng products

Khóa chính gồm hai trường (order_id, item_id).

CREATE TABLE sales.order_items(
 order_id INT,
 item_id INT,
 product_id INT NOT NULL,
 quantity INT NOT NULL,
 list_price DECIMAL (10, 2) NOT NULL,
 discount DECIMAL (4, 2) NOT NULL DEFAULT 0,
 PRIMARY KEY (order_id, item_id),
 FOREIGN KEY (order_id) 
        REFERENCES sales.orders (order_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (product_id) 
        REFERENCES production.products (product_id) 
        ON DELETE CASCADE ON UPDATE CASCADE
);

Table production.stocks

Bảng thông tin về hàng tồn kho của sản phẩm, vì vậy nó có khóa ngoại là product_id trỏ đến bảng products.

CREATE TABLE production.stocks (
 store_id INT,
 product_id INT,
 quantity INT,
 PRIMARY KEY (store_id, product_id),
 FOREIGN KEY (store_id) 
        REFERENCES sales.stores (store_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (product_id) 
        REFERENCES production.products (product_id) 
        ON DELETE CASCADE ON UPDATE CASCADE
);

Đáng lẽ sẽ hướng dẫn các bạn cách load database mẫu tại đây luôn, tuy nhiên vì nội dung hơi dài nên sẽ dành nó ở một bài khác.