ĐỀ THI MÔN LẬP TRÌNH SQL SERVER

Thời gian 75 phút

Apriori Algorithm – Giải thuật Apriori

Yêu cầu: Sử dụng giải thuật Apriori Algorithm tìm luật kết hợp association rules để xây dựng cho hệ thống gợi ý sản phẩm recommender system cho cơ sở dữ liệu sau.

Script Data

1. Dữ liệu bảng Khách Hàng: Table Customer

CustomerId FullName Gender Address
1Ramesh1Ahmedabad
2Khilan0Delhi
3Kaushik0Kota
4Chaitali1Mumbai
5Hardik1Bhopal
6Komal0MP
7Muffy1Indore

2. Dữ liệu bảng Sản Phẩm: Table Product

ProductId ProductName Price Quantity ImageUrl Description
1Bread5.000150Bread.png
2Milk45.00057Milk.png
3Grapes15.00092Grapes.png
4Diapers92.000363Diapers.png
5Beer25.00099Beer.png
6Banana15.00067Banana.png
7Tomato5.500990Tomato.png
8Potato8.700780Potato.png

3. Dữ liệu bảng Đơn Hàng: Table Invoice

InvoiceIdCustomerIdAddedDate
122018-12-11
232018-12-11
352018-12-11
442018-12-11
512018-12-11
672018-12-11
762018-12-11
832018-12-11
952018-12-11

4. Dữ liệu bảng Chi Tiết Đơn Hàng: Table InvoiceDetail

InvoiceIdProductIdPriceQuantity
115.0003
1245.0002
1315.0004
1492.0007
215.0008
2245.0001
2492.0004
315.0005
3245.0006
4245.0002
4315.0003
4492.0008
5245.0004
5315.0005
6315.0006
6492.0007
7245.0003
7492.0004
8525.0001
8615.0001
975.5009
988.7004

Hướng dẫn:

Bước 1

Khai báo độ min support @minsup = 2

Bước 1: (1.5 điểm)

Tạo bảng Associated1 với cấu trúc như sau:

Tên cộtKiểu dữ liệuMô tả
IdINTMã đơn hàng – InvoiceId
Item1INTMã sản phẩm - ProductId

Dữ liệu bảng Associated1

IdItem1
11
12
13
14
21
22
24
31
32
42
43
44
52
53
63
64
72
74
85
86
97
98

Thống kê bảng Associated1 theo Item1

Item1Support
13
26
34
45
51
61
71
81

Thực hiện xóa với độ Support nhỏ hơn @minsup

IdItem1
85
86
97
98

Bước 3: (2 điểm)

Tạo bảng Associated2 với cấu trúc như sau:

Tên cộtKiểu dữ liệuMô tả
IdINTMã đơn hàng – InvoiceId
Item1INTMã sản phẩm - ProductId
Item2INTMã sản phẩm - ProductId

Dữ liệu bảng Associated2

IdItem1Item2
112
113
123
114
124
134
212
214
224
312
423
424
434
523
634
724

Thống kê bảng Associated2 theo Item1, Item2

Item1Item2Support
123
131
233
142
244
343

Thực hiện xóa với độ Support nhỏ hơn @minsup

IdItem1Item2
113

Bước 4: (2.5 điểm)

Tạo bảng Associated3 với cấu trúc như sau:

Tên cộtKiểu dữ liệuMô tả
IdINTMã đơn hàng – InvoiceId
Item1INTMã sản phẩm - ProductId
Item2INTMã sản phẩm - ProductId
Item3INTMã sản phẩm - ProductId

Dữ liệu bảng Associated3

IdItem1Item2Item3
1123
1124
1134
1234
2124
4234

Thống kê bảng Associated3 theo Item1, Item2, Item3

Item1Item2Item3Support
1231
1242
1341
2342

Thực hiện xóa với độ Support nhỏ hơn @support

IdItem1Item2Item3
1123
1134

Bước 5: (2 điểm)

Viết thủ tục lấy ra danh sách các sản phẩm liên quan đến 1 sản phẩm GetRelatedProducts(@productId INT)

Ví dụ 1:

EXEC GetRelatedProducts @productId = 1;

Kết quả

ProductIdProductNamePriceQuantityImageUrl
2Milk4500057Milk.png
4Diapers92000363Diapers.png

Ví dụ 2:

EXEC GetRelatedProducts @productId = 4;

Kết quả

ProductIdProductNamePriceQuantityImageUrl
1Bread5000150Bread.png
2Milk4500057Milk.png
3Grapes1500092Grapes.png

Bước 6: (2 điểm)

Viết thủ tục lấy ra danh sách các sản phẩm liên quan đến 2 sản phẩm GetTwoRelatedproducts(@product1Id INT, @product2Id INT)

Ví dụ 1:

EXEC GetTwoRelatedProducts @product1Id = 1, @product2Id = 2;
or
EXEC GetTwoRelatedProducts @product1Id = 2, @product2Id = 1;

Kết quả

ProductIdProductNamePriceQuantityImageUrl
4Diapers92000363Diapers.png

Ví dụ 2:

EXEC GetTwoRelatedProducts @product1Id = 2, @product2Id = 4;
or
EXEC GetTwoRelatedProducts @product1Id = 4, @product2Id = 2;

Kết quả

ProductIdProductNamePriceQuantityImageUrl
1Bread5000150Bread.png
3Grapes1500092Grapes.png

Nộp bài

Lưu tên tập tin là HoTen.sql ví dụ: NguyenPhanChiThanh.sql

Gửi bài vào email: refactortop@gmail.com