微信客服：xiaoxionga100
微信客服：ITCS521
sql代写-CSE581
时间：2022-05-08
Shopping Database
Yisheng Yang
CSE581 Project 2
Introductions & Descriptions
1. One customer may have one or many phone numbers, each
phone number belong to a type. Also, one customer may have
one or more shipping/billing addresses and credit cards.
2. Customers can review products in orders they buy, they can
comment on the products and give them a score. Every time a
customer can only review one time on the order.
3. There are three prices of each product. One price is the
reference price of normal market which is in the ‘Products’ table.
The second one is the price offered by suppliers. The third one
is the price in stores which is often higher than the price offered
by suppliers.
4. As a customer, when he make an online order, he can choose
getting the package by himself without shipping .
5. One supplier may have one or many warehouses. Different
suppliers may store different products in the same warehouse.
6. One customer may have zero or many orders, some orders
maybe in store orders without shipping.
7. After buying products online, one customer can return products,
he can choose shipping or return products to stores directly.
Design ERD
Schema & Tables & Constraints & TestData
CREATE DATABASE Shopping
GO
USE Shopping;
-- this is used to record the customers' normal information including
ID, name, username(used for system login), email
CREATE TABLE [dbo].[Customers] (
[CustomerID] int NOT NULL,
[Name] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[UserName] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Email] varchar(50) COLLATE Chinese_PRC_CI_AS NULL
);
-- test data of customers
INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName],
[Email]) VALUES (N'1001', N'Mario Pontes', N'MP', N'vipmp@google.com');
INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName],
[Email]) VALUES (N'1002', N'Patricia McKenna', N'PAM',
N'vippam@126.com');
INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName],
[Email]) VALUES (N'2001', N'Helen Bennett', N'HB', N'viphb@qq.com');
INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName],
[Email]) VALUES (N'2002', N'Jaime Yorres', N'JY', N'vipjy@google.com');
INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName],
[Email]) VALUES (N'3001', N'Rene Phillips', N'RP',
N'viprp@google.com');
INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName],
[Email]) VALUES (N'3002', N'Liu Wong', N'LW', N'viplw@126.com');
-- create table used to record the addresses of customer,
-- one customer may have one or more addressess, the addresses of one
customer should be different
CREATE TABLE [dbo].[CustomerAddresses] (
[CustomerID] int NOT NULL,
[Address] varchar(100) NOT NULL
);
-- test data of customers' addresses
INSERT INTO [dbo].[CustomerAddresses] ([CustomerID], [Address]) VALUES
(N'1001', N'265, boulevard Charonne');
INSERT INTO [dbo].[CustomerAddresses] ([CustomerID], [Address]) VALUES
(N'1001', N'Calle Dr. Jorge Cash 321');
INSERT INTO [dbo].[CustomerAddresses] ([CustomerID], [Address]) VALUES
(N'1002', N'Rua da Panificadora, 12');
INSERT INTO [dbo].[CustomerAddresses] ([CustomerID], [Address]) VALUES
(N'2001', N'Gran Via, 1');
INSERT INTO [dbo].[CustomerAddresses] ([CustomerID], [Address]) VALUES
(N'2002', N'P.O. Box 555');
INSERT INTO [dbo].[CustomerAddresses] ([CustomerID], [Address]) VALUES
(N'3001', N'55 Grizzly Peak Rd.');
INSERT INTO [dbo].[CustomerAddresses] ([CustomerID], [Address]) VALUES
(N'3001', N'Smagsloget 45');
-- create table used to record the credit cards of customer,
-- one customer may have one or more credit cards, the credit cards of
one customer should be different
CREATE TABLE [dbo].[CustomerCreditCards] (
[CustomerID] int NOT NULL,
[CreaditCardID] varchar(50) NOT NULL
);
-- test data of customers' credit cardID
INSERT INTO [dbo].[CustomerCreditCards] ([CustomerID], [CreaditCardID])
VALUES (N'1001', N'2343243xc324323434');
INSERT INTO [dbo].[CustomerCreditCards] ([CustomerID], [CreaditCardID])
VALUES (N'1001', N'23878247284728473843');
INSERT INTO [dbo].[CustomerCreditCards] ([CustomerID], [CreaditCardID])
VALUES (N'1002', N'35432543543455453454');
INSERT INTO [dbo].[CustomerCreditCards] ([CustomerID], [CreaditCardID])
VALUES (N'2002', N'34534546456565666');
-- create table used to record the phone numbers of customer,
-- one customer may have one or more phone numbers, the phone numbers
of one customer should be different
-- each phone number belong to one type (home, business, cell)
CREATE TABLE [dbo].[CustomerPhones] (
[CustomerID] int NOT NULL,
[PhoneNumber] varchar(20) NOT NULL,
[PhoneType] varchar(10) NOT NULL
);
-- test data of customers' phone numbers
INSERT INTO [dbo].[CustomerPhones] ([CustomerID], [PhoneNumber],
[PhoneType]) VALUES (N'1001', N'423535666', N'home');
INSERT INTO [dbo].[CustomerPhones] ([CustomerID], [PhoneNumber],
[PhoneType]) VALUES (N'1001', N'534466346', N'business');
INSERT INTO [dbo].[CustomerPhones] ([CustomerID], [PhoneNumber],
[PhoneType]) VALUES (N'1001', N'645754576', N'cell');
INSERT INTO [dbo].[CustomerPhones] ([CustomerID], [PhoneNumber],
[PhoneType]) VALUES (N'1002', N'432644366', N'cell');
INSERT INTO [dbo].[CustomerPhones] ([CustomerID], [PhoneNumber],
[PhoneType]) VALUES (N'2001', N'235436465', N'home');
INSERT INTO [dbo].[CustomerPhones] ([CustomerID], [PhoneNumber],
[PhoneType]) VALUES (N'2001', N'892893834', N'business');
INSERT INTO [dbo].[CustomerPhones] ([CustomerID], [PhoneNumber],
[PhoneType]) VALUES (N'3001', N'334545656', N'cell');
-- this records the products' original information, this will be
referenced by other tables like 'StoreProducts', 'SupplierProducts',
'WarehouseProducts'
CREATE TABLE [dbo].[Products] (
[ProductID] int NOT NULL,
[ProductName] varchar(50) NOT NULL,
[Description] varchar(100) NULL,
[Price] money NOT NULL
);
-- test data of Products
INSERT INTO [dbo].[Products] ([ProductID], [ProductName],
[Description], [Price]) VALUES (N'1', N'Aniseed Syrup', N'12 - 550 ml
bottles', N'12.0000');
INSERT INTO [dbo].[Products] ([ProductID], [ProductName],
[Description], [Price]) VALUES (N'2', N'Tofu', N'40 - 100 g pkgs',
N'25.2500');
INSERT INTO [dbo].[Products] ([ProductID], [ProductName],
[Description], [Price]) VALUES (N'3', N'Genen Shouyu', N'24 - 250 ml
bottles', N'17.5000');
INSERT INTO [dbo].[Products] ([ProductID], [ProductName],
[Description], [Price]) VALUES (N'4', N'Pavlova', N'32 - 500 g boxes',
N'19.4500');
INSERT INTO [dbo].[Products] ([ProductID], [ProductName],
[Description], [Price]) VALUES (N'5', N'Carnarvon Tigers', N'16 kg
pkg', N'64.5000');
INSERT INTO [dbo].[Products] ([ProductID], [ProductName],
[Description], [Price]) VALUES (N'6', N'Filo Mix', N'16 - 2 kg boxes',
N'9.0000');
-- this record the products of customer wish to buy in the future.
CREATE TABLE [dbo].[WishList] (
[CustomerID] int NOT NULL,
[ProductID] int NOT NULL,
[CreateTime] datetime NOT NULL
);
-- test data of wish products list.
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'1001', N'1', N'2021-10-01 12:00:00.000');
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'1001', N'3', N'2021-09-10 10:00:00.000');
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'2001', N'4', N'2021-08-07 12:20:00.000');
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'2001', N'5', N'2021-08-09 13:00:00.000');
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'2001', N'6', N'2021-09-09 14:20:00.000');
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'2002', N'2', N'2021-10-03 15:30:00.000');
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'3001', N'1', N'2021-09-23 16:00:00.000');
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'3002', N'1', N'2021-10-06 09:00:00.000');
-- this record the history of in store purchases, this will be
referenced by 'PurchaseItems'
CREATE TABLE [dbo].[InStorePurchases] (
[PurchaseID] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CustomerID] int NOT NULL,
[PurchaseDate] datetime NOT NULL
);
-- test data of in store purchases
INSERT INTO [dbo].[InStorePurchases] ([PurchaseID], [CustomerID],
[PurchaseDate]) VALUES (N'20211103202208PUBN', N'2002', N'2021-11-03
20:22:08.000');
INSERT INTO [dbo].[InStorePurchases] ([PurchaseID], [CustomerID],
[PurchaseDate]) VALUES (N'20211123195028PPUB', N'3002', N'2021-11-23
19:50:28.000');
INSERT INTO [dbo].[InStorePurchases] ([PurchaseID], [CustomerID],
[PurchaseDate]) VALUES (N'20211201135523PXPU', N'1001', N'2021-12-01
13:50:00.000');
INSERT INTO [dbo].[InStorePurchases] ([PurchaseID], [CustomerID],
[PurchaseDate]) VALUES (N'20211202123525PXPU', N'1002', N'2021-12-02
12:35:25.000');
-- this record the details of in store purchases
CREATE TABLE [dbo].[PurchaseItems] (
[ItemID] int NOT NULL,
[PurchaseID] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[StoreID] int NOT NULL,
[SupplierID] int NOT NULL,
[WarehouseID] int NOT NULL,
[ProductID] int NOT NULL,
[UnitPrice] money NOT NULL,
[Quantity] int DEFAULT ((1)) NOT NULL,
[TotalPrice] money NOT NULL
);
-- test data of in store purchase details
INSERT INTO [dbo].[PurchaseItems] ([ItemID], [PurchaseID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'1', N'20211103202208PUBN', N'9101', N'6521',
N'8011', N'1', N'18.0000', N'2', N'36.0000');
INSERT INTO [dbo].[PurchaseItems] ([ItemID], [PurchaseID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'2', N'20211123195028PPUB', N'9201', N'6631',
N'8022', N'4', N'31.0000', N'3', N'93.0000');
INSERT INTO [dbo].[PurchaseItems] ([ItemID], [PurchaseID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'3', N'20211201135523PXPU', N'9301', N'6743',
N'8033', N'6', N'19.0000', N'1', N'19.0000');
INSERT INTO [dbo].[PurchaseItems] ([ItemID], [PurchaseID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'4', N'20211202123525PXPU', N'9302', N'6844',
N'8022', N'3', N'23.5000', N'2', N'27.0000');
-- this is used to record the history of in store products return
history
CREATE TABLE [dbo].[InStoreReturns] (
[ReturnID] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CustomerID] int NOT NULL,
[StoreID] int NOT NULL,
[SupplierID] int NOT NULL,
[WarehouseID] int NOT NULL,
[ProductID] int NOT NULL,
[Quantity] int NOT NULL,
[ReturnDate] datetime NOT NULL
);
-- the online orders placed by customer
CREATE TABLE [dbo].[OnlineOrders] (
[OrderID] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CustomerID] int NOT NULL,
[Status] varchar(10) COLLATE Chinese_PRC_CI_AS NULL,
[OrderDate] datetime NOT NULL,
[NeedShip] bit DEFAULT ((1)) NOT NULL
);
-- test data of online orders
INSERT INTO [dbo].[OnlineOrders] ([OrderID], [CustomerID], [Status],
[OrderDate], [NeedShip]) VALUES (N'20201103153020YHJK', N'1001',
N'delivered', N'2020-11-03 15:30:00.000', N'1');
INSERT INTO [dbo].[OnlineOrders] ([OrderID], [CustomerID], [Status],
[OrderDate], [NeedShip]) VALUES (N'20210917122020XGSU', N'1002',
N'shipped', N'2021-09-17 12:20:20.000', N'1');
INSERT INTO [dbo].[OnlineOrders] ([OrderID], [CustomerID], [Status],
[OrderDate], [NeedShip]) VALUES (N'20211207175010XGSU', N'3001',
N'ready', N'2021-12-07 17:50:10.000', N'1');
INSERT INTO [dbo].[OnlineOrders] ([OrderID], [CustomerID], [Status],
[OrderDate], [NeedShip]) VALUES (N'20211208134020HGYT', N'3002',
N'returned', N'2021-12-08 13:40:20.000', N'1');
INSERT INTO [dbo].[OnlineOrders] ([OrderID], [CustomerID], [Status],
[OrderDate], [NeedShip]) VALUES (N'20211209153020AXFD', N'1001',
N'ready', N'2021-12-09 15:30:20.000', N'1');
-- the products' details of online orders, one order will refer to one
or many different products in store
-- in this record, the products are not the normal products in
'Products' table, they are refer to the items in 'StoreProducts'
CREATE TABLE [dbo].[OrderItems] (
[ItemID] int NOT NULL,
[OrderID] varchar(50) NOT NULL,
[StoreID] int NOT NULL,
[SupplierID] int NOT NULL,
[WarehouseID] int NOT NULL,
[ProductID] int NOT NULL,
[UnitPrice] money NOT NULL,
[Quantity] int DEFAULT ((1)) NOT NULL,
[TotalPrice] money NOT NULL
);
-- test data of online order details
INSERT INTO [dbo].[OrderItems] ([ItemID], [OrderID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'1', N'20211209153020AXFD', N'9101', N'6521',
N'8011', N'1', N'18.0000', N'3', N'54.0000');
INSERT INTO [dbo].[OrderItems] ([ItemID], [OrderID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'2', N'20201103153020YHJK', N'9201', N'6631',
N'8022', N'4', N'31.0000', N'2', N'62.0000');
INSERT INTO [dbo].[OrderItems] ([ItemID], [OrderID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'3', N'20210917122020XGSU', N'9301', N'6743',
N'8033', N'6', N'19.0000', N'5', N'90.0000');
INSERT INTO [dbo].[OrderItems] ([ItemID], [OrderID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'4', N'20210917122020XGSU', N'9301', N'6844',
N'8011', N'1', N'18.0000', N'3', N'52.0000');
INSERT INTO [dbo].[OrderItems] ([ItemID], [OrderID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'5', N'20211207175010XGSU', N'9302', N'6844',
N'8022', N'3', N'23.5000', N'2', N'47.0000');
INSERT INTO [dbo].[OrderItems] ([ItemID], [OrderID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'6', N'20211208134020HGYT', N'9101', N'6521',
N'8011', N'2', N'30.0000', N'3', N'88.0000');
-- sometimes the online orders may be returned, so the information
should be recorded.
-- customer can choose if the online order need shipping.
-- Although this is online order, but I think some one may be go to the
store to get the products by himself directly (ex: near the store)
CREATE TABLE [dbo].[OnlineReturns] (
[ReturnID] varchar(50) NOT NULL,
[OrderID] varchar(50) NOT NULL,
[ReturnDate] datetime NOT NULL,
[NeedShip] bit DEFAULT ((1)) NOT NULL
);
-- some test data of the online returns
INSERT INTO [dbo].[OnlineReturns] ([ReturnID], [OrderID], [ReturnDate],
[NeedShip]) VALUES (N'20211209183923YUSAJG', N'20211209153020AXFD',
N'2021-12-09 18:30:00.000', N'1');
-- for each online orders, customer can review the products they bought
-- one customer can review only after ordering products, one type of
products in one order can be reviewed once.(Not every customer can
review)
CREATE TABLE [dbo].[Reviews] (
[ReviewID] int NOT NULL,
[OrderItemID] int NOT NULL,
[Score] float(53) NULL,
[Comment] varchar(200) COLLATE Chinese_PRC_CI_AS NULL,
[CreateTime] datetime NOT NULL
);
-- some test data of customer reviews.
INSERT INTO [dbo].[Reviews] ([ReviewID], [OrderItemID], [Score],
[Comment], [CreateTime]) VALUES (N'1', N'1', N'4.5', N'nice', N'2021-
12-01 12:30:00.000');
INSERT INTO [dbo].[Reviews] ([ReviewID], [OrderItemID], [Score],
[Comment], [CreateTime]) VALUES (N'2', N'2', N'5', N'very good',
N'2021-12-02 10:00:00.000');
INSERT INTO [dbo].[Reviews] ([ReviewID], [OrderItemID], [Score],
[Comment], [CreateTime]) VALUES (N'3', N'3', N'2.5', N'not so good',
N'2021-11-20 12:00:00.000');
INSERT INTO [dbo].[Reviews] ([ReviewID], [OrderItemID], [Score],
[Comment], [CreateTime]) VALUES (N'4', N'4', N'3', N'just so so',
N'2021-12-03 15:00:00.000');
INSERT INTO [dbo].[Reviews] ([ReviewID], [OrderItemID], [Score],
[Comment], [CreateTime]) VALUES (N'5', N'5', N'1', N'it is so ugly!!',
N'2021-12-01 18:00:00.000');
INSERT INTO [dbo].[Reviews] ([ReviewID], [OrderItemID], [Score],
[Comment], [CreateTime]) VALUES (N'6', N'6', N'4.5', N'good', N'2021-
12-02 17:30:00.000');
-- the base information of stores
CREATE TABLE [dbo].[Stores] (
[StoreID] int NOT NULL,
[Name] varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Address] varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Phone] varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Fax] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[Email] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[WebPage] varchar(50) COLLATE Chinese_PRC_CI_AS NULL
);
-- some test data of stores
INSERT INTO [dbo].[Stores] ([StoreID], [Name], [Address], [Phone],
[Fax], [Email], [WebPage]) VALUES (N'9101', N'Svensk', N'3400 - 8th
Avenue Suite 210', N'(503) 555-9931', N'234434646', N'vipsven@126.com',
N'www.svensk.com');
INSERT INTO [dbo].[Stores] ([StoreID], [Name], [Address], [Phone],
[Fax], [Email], [WebPage]) VALUES (N'9201', N'Mayumi', N'92 Setsuko
Chuo-ku', N'(06) 431-7877', N'234325356', N'vipmayumi@google.com',
N'www.Mayumi.com');
INSERT INTO [dbo].[Stores] ([StoreID], [Name], [Address], [Phone],
[Fax], [Email], [WebPage]) VALUES (N'9301', N'Biscuits', N'Kaloadagatan
13', N'031-987 65 43', N'245346466', N'vipbiscuits@126.com',
N'www.Biscuits.com');
INSERT INTO [dbo].[Stores] ([StoreID], [Name], [Address], [Phone],
[Fax], [Email], [WebPage]) VALUES (N'9302', N'Nouveaux', N'22, rue H.
Voiron', N'(089) 6547665', N'265328662', N'vipnouveaux@google.com',
N'www.Nouveaux.com');
-- if one online order need shipping, there must be one shipping record
referred to.
-- if you need shipping, you should choose the service type, start
address, end address and so on.
CREATE TABLE [dbo].[Shipping] (
[ShippingID] int NOT NULL,
[OrderID] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ServiceType] varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[StartAddress] varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[EndAddress] varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Fare] money NULL,
[ExpectedDate] datetime NOT NULL,
[ActualDate] datetime NULL
);
-- some test data of shipping.
INSERT INTO [dbo].[Shipping] ([ShippingID], [OrderID], [ServiceType],
[StartAddress], [EndAddress], [Fare], [ExpectedDate], [ActualDate])
VALUES (N'1', N'20201103153020YHJK', N'USPS', N'3400 - 8th Avenue Suite
210', N'265, boulevard Charonne', N'3.0000', N'2020-11-05
00:00:00.000', N'2020-11-06 00:00:00.000');
INSERT INTO [dbo].[Shipping] ([ShippingID], [OrderID], [ServiceType],
[StartAddress], [EndAddress], [Fare], [ExpectedDate], [ActualDate])
VALUES (N'2', N'20210917122020XGSU', N'FedEx', N'92 Setsuko Chuo-ku',
N'Rua da Panificadora, 12', N'5.0000', N'2021-09-17 00:00:00.000',
N'2021-09-17 00:00:00.000');
INSERT INTO [dbo].[Shipping] ([ShippingID], [OrderID], [ServiceType],
[StartAddress], [EndAddress], [Fare], [ExpectedDate], [ActualDate])
VALUES (N'3', N'20211207175010XGSU', N'UPS', N'Kaloadagatan 13', N'55
Grizzly Peak Rd.', N'4.0000', N'2021-12-10 00:00:00.000', N'2021-12-09
00:00:00.000');
INSERT INTO [dbo].[Shipping] ([ShippingID], [OrderID], [ServiceType],
[StartAddress], [EndAddress], [Fare], [ExpectedDate], [ActualDate])
VALUES (N'4', N'20211208134020HGYT', N'FedEx', N'22, rue H. Voiron',
N'Smagsloget 45', N'6.0000', N'2021-12-12 00:00:00.000', N'2021-12-13
00:00:00.000');
INSERT INTO [dbo].[Shipping] ([ShippingID], [OrderID], [ServiceType],
[StartAddress], [EndAddress], [Fare], [ExpectedDate], [ActualDate])
VALUES (N'5', N'20211209153020AXFD', N'UPS', N'Kaloadagatan 13', N'Rua
da Panificadora, 12', N'5.0000', N'2021-12-09 00:00:00.000', N'2021-12-
09 00:00:00.000');
-- this record the products in stores
-- Why need this? I assume that the 'Products' table records the normal
information of products, and the price in 'Products' table is just an
suggest price
-- The same product in differnet stores may have the differnet price,
also the quantity of product will be different.
-- The same type of products may come from different warehouses.
CREATE TABLE [dbo].[StoreProducts] (
[StoreID] int NOT NULL,
[SupplierID] int NOT NULL,
[WarehouseID] int NOT NULL,
[ProductID] int NOT NULL,
[Price] money NULL,
[Quantity] int NULL
);
-- the test data of products in store.
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9101',
N'6521', N'8011', N'1', N'18.0000', N'20');
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9101',
N'6521', N'8011', N'2', N'30.0000', N'50');
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9101',
N'6631', N'8022', N'3', N'23.0000', N'35');
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9201',
N'6631', N'8022', N'4', N'31.0000', N'18');
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9201',
N'6743', N'8033', N'5', N'96.0000', N'6');
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9301',
N'6743', N'8033', N'6', N'19.0000', N'9');
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9301',
N'6844', N'8011', N'1', N'18.0000', N'20');
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9302',
N'6844', N'8022', N'3', N'23.5000', N'5');
-- record the suppliers' information, the products in stores are
provided by suppliers
CREATE TABLE [dbo].[Suppliers] (
[SupplierID] int NOT NULL,
[Name] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Address] varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Phone] varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Fax] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[Email] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[WebPage] varchar(50) COLLATE Chinese_PRC_CI_AS NULL
);
-- some test data of suppliers
INSERT INTO [dbo].[Suppliers] ([SupplierID], [Name], [Address],
[Phone], [Fax], [Email], [WebPage]) VALUES (N'6521', N'Exotic Liquids',
N'49 Gilbert St', N'(171) 555-2222', N'233546464',
N'vipgst@google.com', N'www.vipgst.org');
INSERT INTO [dbo].[Suppliers] ([SupplierID], [Name], [Address],
[Phone], [Fax], [Email], [WebPage]) VALUES (N'6631', N'Tokyo Traders',
N'9-8 Sekimai Musashino-shi', N'(03) 3555-5011', N'643464344',
N'vipttkyo@126.com', N'www.ttkyo.com');
INSERT INTO [dbo].[Suppliers] ([SupplierID], [Name], [Address],
[Phone], [Fax], [Email], [WebPage]) VALUES (N'6743', N'Grandma Kelly''s
Homestead', N'707 Oxford Rd.', N'(313) 555-5735', N'(313) 555-3349',
N'vipgkfx@google.com', N'www.gkfx.com');
INSERT INTO [dbo].[Suppliers] ([SupplierID], [Name], [Address],
[Phone], [Fax], [Email], [WebPage]) VALUES (N'6844', N'Leka Trading',
N'471 Serangoon Loop, Suite #402', N'555-8787', N'1232353533',
N'viplaket@126.com', N'www.laket.com');
-- record the relationships of suppliers and products
-- also you will find that there is a warehouse in it, it is used to
record the product is stored in which warehouse.
CREATE TABLE [dbo].[SupplierProducts] (
[SupplierID] int NOT NULL,
[WarehouseID] int NOT NULL,
[ProductID] int NOT NULL,
[NumberAvailable] int NOT NULL,
[UnitPrice] money NULL
);
-- some test data of the relationships between suppliers, products,
warehouses.
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6521', N'8011',
N'1', N'50', N'11.0000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6521', N'8011',
N'2', N'100', N'21.3000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6521', N'8011',
N'3', N'90', N'15.0000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6631', N'8022',
N'3', N'200', N'15.2000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6631', N'8022',
N'4', N'180', N'17.0000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6743', N'8033',
N'5', N'300', N'53.0000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6743', N'8033',
N'6', N'580', N'7.0000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6844', N'8011',
N'1', N'300', N'11.2000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6844', N'8022',
N'3', N'290', N'14.9000');
-- the information of warehouses which used to store products for
suppliers.
CREATE TABLE [dbo].[Warehouses] (
[WarehouseID] int NOT NULL,
[Name] varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Address] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[Phone] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[Fax] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[Email] varchar(30) COLLATE Chinese_PRC_CI_AS NULL
);
-- some test data of warehouses.
INSERT INTO [dbo].[Warehouses] ([WarehouseID], [Name], [Address],
[Phone], [Fax], [Email]) VALUES (N'8011', N'Ma Maison', N'2960 Rue St.
Laurent', N'(514) 555-9022', N'235343543', N'vipmma@google.com');
INSERT INTO [dbo].[Warehouses] ([WarehouseID], [Name], [Address],
[Phone], [Fax], [Email]) VALUES (N'8022', N'Pasta Buttini', N'Via dei
Gelsomini, 153', N'(089) 6547665', N'(089) 6547667',
N'vippasta@126.com');
INSERT INTO [dbo].[Warehouses] ([WarehouseID], [Name], [Address],
[Phone], [Fax], [Email]) VALUES (N'8033', N'Refrescos', N'Av. das
Americanas 12.890', N'(11) 555 4640', N'1243233535',
N'vipavdas@google.com');
-- the relationships between warehouse and products, it is also used to
record the number of products about in stock, on the way, in return.
CREATE TABLE [dbo].[WarehouseProducts] (
[WarehouseID] int NOT NULL,
[ProductID] int NOT NULL,
[NumberInStock] int DEFAULT ((0)) NOT NULL,
[NumberOnWay] int DEFAULT ((0)) NOT NULL,
[NumberInReturn] int DEFAULT ((0)) NOT NULL
);
-- some test data about the information between warehouses and products
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8011',
N'1', N'100', N'50', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8011',
N'2', N'300', N'100', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8011',
N'3', N'60', N'0', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8011',
N'4', N'50', N'10', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8011',
N'5', N'500', N'200', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8011',
N'6', N'150', N'30', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8022',
N'1', N'200', N'20', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8022',
N'2', N'200', N'50', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8022',
N'3', N'250', N'70', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8022',
N'4', N'300', N'80', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8022',
N'5', N'100', N'0', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8022',
N'6', N'80', N'10', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8033',
N'1', N'70', N'10', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8033',
N'2', N'120', N'20', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8033',
N'3', N'200', N'0', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8033',
N'4', N'150', N'50', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8033',
N'5', N'300', N'20', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8033',
N'6', N'220', N'0', N'0');
-- define the primary key in table 'Customers'
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT [PK_Customers] PRIMARY KEY
CLUSTERED ([CustomerID]);
-- define the primary key in table 'CustomerAddresses'
ALTER TABLE [dbo].[CustomerAddresses] ADD CONSTRAINT
[PK_CustomerAddresses] PRIMARY KEY CLUSTERED ([CustomerID], [Address]);
-- define the primary key in table 'CustomerCreditCards'
ALTER TABLE [dbo].[CustomerCreditCards] ADD CONSTRAINT
[PK_CustomerCreditCards] PRIMARY KEY CLUSTERED ([CustomerID],
[CreaditCardID]);
-- define the primary key in table 'CustomerPhones'
ALTER TABLE [dbo].[CustomerPhones] ADD CONSTRAINT [PK_CustomerPhones]
PRIMARY KEY CLUSTERED ([CustomerID], [PhoneNumber]);
-- define the foreign key in table 'CustomerAddresses' on 'CustomerID'
which refer to table 'Customers' on field 'CustomerID'
ALTER TABLE [dbo].[CustomerAddresses] ADD CONSTRAINT
[FK_CustomerAddresses_CustomerID] FOREIGN KEY ([CustomerID]) REFERENCES
[dbo].[Customers] ([CustomerID]);
-- define the foreign key in table 'CustomerCreditCards' on
'CustomerID' which refer to table 'Customers' on field 'CustomerID'
ALTER TABLE [dbo].[CustomerCreditCards] ADD CONSTRAINT
[FK_CustomerCreditCards_CustomerID] FOREIGN KEY ([CustomerID])
REFERENCES [dbo].[Customers] ([CustomerID]);
-- define the foreign key in table 'CustomerPhones' on 'CustomerID'
which refer to table 'Customers' on field 'CustomerID'
ALTER TABLE [dbo].[CustomerPhones] ADD CONSTRAINT
[FK_CustomerPhones_CustomerID] FOREIGN KEY ([CustomerID]) REFERENCES
[dbo].[Customers] ([CustomerID]);
-- add a constraint on 'PhoneType' to make sure that the value will
only be 'business', 'cell', 'home'
ALTER TABLE [dbo].[CustomerPhones] ADD CONSTRAINT [CK_CustomerPhones]
CHECK ([PhoneType]='business' OR [PhoneType]='cell' OR
[PhoneType]='home');
-- define the primary key in table 'Products'
ALTER TABLE [dbo].[Products] ADD CONSTRAINT [PK_Products] PRIMARY KEY
CLUSTERED ([ProductID]);
-- define the primary key in table 'WishList'
ALTER TABLE [dbo].[WishList] ADD CONSTRAINT [PK_WishList] PRIMARY KEY
CLUSTERED ([CustomerID], [ProductID]);
-- define the foreign key in table 'WishList' on 'CustomerID' which
refer to table 'Customers' on field 'CustomerID'
ALTER TABLE [dbo].[WishList] ADD CONSTRAINT [FK_WishList_CustomerID]
FOREIGN KEY ([CustomerID]) REFERENCES [dbo].[Customers] ([CustomerID]);
-- define the foreign key in table 'WishList' on 'ProductID' which
refer to table 'Products' on field 'ProductID'
ALTER TABLE [dbo].[WishList] ADD CONSTRAINT [FK_WishList_ProductID]
FOREIGN KEY ([ProductID]) REFERENCES [dbo].[Products] ([ProductID]);
-- define the primary key in table 'OnlineOrders'
ALTER TABLE [dbo].[OnlineOrders] ADD CONSTRAINT [PK_OnlineOrders]
PRIMARY KEY CLUSTERED ([OrderID]);
-- add a constraint on field 'Status' to make sure the value is
'returned' or 'delivered' or 'shipped' or 'ready'.
ALTER TABLE [dbo].[OnlineOrders] ADD CONSTRAINT
[CK_OnlineOrders_Status] CHECK ([Status]='returned' OR
[Status]='delivered' OR [Status]='shipped' OR [Status]='ready');
-- define the primary key in table 'OrderItems'
ALTER TABLE [dbo].[OrderItems] ADD CONSTRAINT [PK_OrderItems] PRIMARY
KEY CLUSTERED ([ItemID]);
-- define the primary key in table 'OnlineReturns'
ALTER TABLE [dbo].[OnlineReturns] ADD CONSTRAINT [PK_ReturnOrders]
PRIMARY KEY CLUSTERED ([ReturnID]);
-- define the primary key in table 'InStorePurchases'
ALTER TABLE [dbo].[InStorePurchases] ADD CONSTRAINT
[PK_InStorePurchases] PRIMARY KEY CLUSTERED ([PurchaseID]);
-- define the primary key in table 'PurchaseItems'
ALTER TABLE [dbo].[PurchaseItems] ADD CONSTRAINT [PK_PurchaseItems]
PRIMARY KEY CLUSTERED ([ItemID]);
-- to make sure the number of quantity need to be returned must be
positive.
ALTER TABLE [dbo].[InStoreReturns] ADD CONSTRAINT
[CK_InStoreReturns_Quantity] CHECK ([Quantity]>(0));
-- define the primary key in table 'InStoreReturns'
ALTER TABLE [dbo].[InStoreReturns] ADD CONSTRAINT [PK_InStoreReturns]
PRIMARY KEY CLUSTERED ([ReturnID]);
-- define the primary key in table 'Reviews'
ALTER TABLE [dbo].[Reviews] ADD CONSTRAINT [PK_Reviews] PRIMARY KEY
CLUSTERED ([ReviewID]);
-- to make sure that one item in order should only be reviewed once.
CREATE UNIQUE NONCLUSTERED INDEX [IX_UNIQUE_OrderItem]
ON [dbo].[Reviews] (
[OrderItemID] ASC
);
-- define the primary key in table 'Shipping'
ALTER TABLE [dbo].[Shipping] ADD CONSTRAINT [PK_Shipping] PRIMARY KEY
CLUSTERED ([ShippingID]);
-- to make sure that the ServiceType in 'Shipping' must be 'UPS' or
'FedEx' or 'USPS'.
ALTER TABLE [dbo].[Shipping] ADD CONSTRAINT [CK_Shipping_ServiceType]
CHECK ([ServiceType]='UPS' OR [ServiceType]='FedEx' OR
[ServiceType]='USPS');
-- define the primary key in table 'Stores'
ALTER TABLE [dbo].[Stores] ADD CONSTRAINT [PK_Stores] PRIMARY KEY
CLUSTERED ([StoreID]);
-- to make sure the price of products in store must be positive.
ALTER TABLE [dbo].[StoreProducts] ADD CONSTRAINT
[CK_StoreProducts_Price] CHECK ([Price]>(0));
-- to make sure that the quantity of products in stores will not be
negative.
ALTER TABLE [dbo].[StoreProducts] ADD CONSTRAINT
[CK_StoreProducts_Quantity] CHECK ([Quantity]>=(0));
-- define the primary key in table 'StoreProducts'
ALTER TABLE [dbo].[StoreProducts] ADD CONSTRAINT [PK_StoreProducts]
PRIMARY KEY CLUSTERED ([StoreID], [SupplierID], [WarehouseID],
[ProductID]);
-- define the primary key in table 'Suppliers'
ALTER TABLE [dbo].[Suppliers] ADD CONSTRAINT [PK_Suppliers] PRIMARY KEY
CLUSTERED ([SupplierID]);
-- make sure the unit price of product should be positive.
ALTER TABLE [dbo].[SupplierProducts] ADD CONSTRAINT
[CK_SupplierProducts_UnitPrice] CHECK ([UnitPrice]>(0));
-- make sure that the available number of products must be positive
ALTER TABLE [dbo].[SupplierProducts] ADD CONSTRAINT
[CK_SupplierProducts_NumberAvailable] CHECK ([NumberAvailable]>=(0));
-- define the primary key in table 'SupplierProducts'
ALTER TABLE [dbo].[SupplierProducts] ADD CONSTRAINT
[PK_SupplierProducts] PRIMARY KEY CLUSTERED ([SupplierID],
[WarehouseID], [ProductID]);
-- define the primary key in table 'Warehouses'
ALTER TABLE [dbo].[Warehouses] ADD CONSTRAINT [PK_Warehouses] PRIMARY
KEY CLUSTERED ([WarehouseID]);
-- define the primary key in table 'WarehouseProducts'
ALTER TABLE [dbo].[WarehouseProducts] ADD CONSTRAINT
[PK_WarehouseProducts] PRIMARY KEY CLUSTERED ([WarehouseID],
[ProductID]);
-- define the foreign key in table 'InStorePurchases' on 'CustomerID'
which refer to table 'Customers' on field 'CustomerID'
ALTER TABLE [dbo].[InStorePurchases] ADD CONSTRAINT
[FK_InStorePurchases_CustomerID] FOREIGN KEY ([CustomerID]) REFERENCES
[dbo].[Customers] ([CustomerID]);
-- define the foreign key in table 'InStoreReturns' on '[StoreID],
[SupplierID], [WarehouseID], [ProductID]' which refer to table
'StoreProducts' on field '[StoreID], [SupplierID], [WarehouseID],
[ProductID]'
ALTER TABLE [dbo].[InStoreReturns] ADD CONSTRAINT
[FK_InStoreReturns_StoreProducts] FOREIGN KEY ([StoreID], [SupplierID],
[WarehouseID], [ProductID]) REFERENCES [dbo].[StoreProducts]
([StoreID], [SupplierID], [WarehouseID], [ProductID]);
-- define the foreign key in table 'InStoreReturns' on 'CustomerID'
which refer to table 'Customers' on field 'CustomerID'
ALTER TABLE [dbo].[InStoreReturns] ADD CONSTRAINT
[FK_InStoreReturns_CustomerID] FOREIGN KEY ([CustomerID]) REFERENCES
[dbo].[Customers] ([CustomerID]);
-- define the foreign key in table 'OnlineOrders' on 'CustomerID' which
refer to table 'Customers' on field 'CustomerID'
ALTER TABLE [dbo].[OnlineOrders] ADD CONSTRAINT
[FK_OnlineOrders_CustomerID] FOREIGN KEY ([CustomerID]) REFERENCES
[dbo].[Customers] ([CustomerID]);
-- define the foreign key in table 'OnlineReturns' on 'OrderID' which
refer to table 'OnlineOrders' on field 'OrderID'
ALTER TABLE [dbo].[OnlineReturns] ADD CONSTRAINT
[FK_OnlineReturns_OrderID] FOREIGN KEY ([OrderID]) REFERENCES
[dbo].[OnlineOrders] ([OrderID]);
-- define the foreign key in table 'OrderItems' on '[StoreID],
[SupplierID], [WarehouseID], [ProductID]' which refer to table
'StoreProducts' on field '[StoreID], [SupplierID], [WarehouseID],
[ProductID]'
ALTER TABLE [dbo].[OrderItems] ADD CONSTRAINT
[FK_OrderItems_StoreProducts] FOREIGN KEY ([StoreID], [SupplierID],
[WarehouseID], [ProductID]) REFERENCES [dbo].[StoreProducts]
([StoreID], [SupplierID], [WarehouseID], [ProductID]);
-- define the foreign key in table 'OrderItems' on 'OrderID' which
refer to table 'OnlineOrders' on field 'OrderID'
ALTER TABLE [dbo].[OrderItems] ADD CONSTRAINT [FK_OrderItems_Order]
FOREIGN KEY ([OrderID]) REFERENCES [dbo].[OnlineOrders] ([OrderID]);
-- define the foreign key in table 'PurchaseItems' on 'PurchaseID'
which refer to table 'InStorePurchases' on field 'PurchaseID'
ALTER TABLE [dbo].[PurchaseItems] ADD CONSTRAINT
[FK_PurchaseItems_PurchaseID] FOREIGN KEY ([PurchaseID]) REFERENCES
[dbo].[InStorePurchases] ([PurchaseID]);
-- define the foreign key in table 'PurchaseItems' on '[StoreID],
[SupplierID], [WarehouseID], [ProductID]' which refer to table
'StoreProducts' on field '[StoreID], [SupplierID], [WarehouseID],
[ProductID]'
ALTER TABLE [dbo].[PurchaseItems] ADD CONSTRAINT
[FK_PurchaseItems_StoreProducts] FOREIGN KEY ([StoreID], [SupplierID],
[WarehouseID], [ProductID]) REFERENCES [dbo].[StoreProducts]
([StoreID], [SupplierID], [WarehouseID], [ProductID]);
-- define the foreign key in table 'Reviews' on 'OrderItemID' which
refer to table 'OrderItems' on field 'ItemID'
ALTER TABLE [dbo].[Reviews] ADD CONSTRAINT [FK_Reviews_OrderItemID]
FOREIGN KEY ([OrderItemID]) REFERENCES [dbo].[OrderItems] ([ItemID]);
-- define the foreign key in table 'Shipping' on 'OrderID' which refer
to table 'OnlineOrders' on field 'OrderID'
ALTER TABLE [dbo].[Shipping] ADD CONSTRAINT [FK_Shipping_OrderID]
FOREIGN KEY ([OrderID]) REFERENCES [dbo].[OnlineOrders] ([OrderID]);
-- define the foreign key in table 'StoreProducts' on 'StoreID' which
refer to table 'Stores' on field 'StoreID'
ALTER TABLE [dbo].[StoreProducts] ADD CONSTRAINT
[FK_StoreProducts_StoreID] FOREIGN KEY ([StoreID]) REFERENCES
[dbo].[Stores] ([StoreID]);
-- define the foreign key in table 'StoreProducts' on 'SupplierID'
which refer to table 'Suppliers' on field 'SupplierID'
ALTER TABLE [dbo].[StoreProducts] ADD CONSTRAINT
[FK_StoreProducts_SupplierID] FOREIGN KEY ([SupplierID]) REFERENCES
[dbo].[Suppliers] ([SupplierID]);
-- define the foreign key in table 'StoreProducts' on 'WarehouseID'
which refer to table 'Warehouses' on field 'WarehouseID'
ALTER TABLE [dbo].[StoreProducts] ADD CONSTRAINT
[FK_StoreProducts_WarehouseID] FOREIGN KEY ([WarehouseID]) REFERENCES
[dbo].[Warehouses] ([WarehouseID]);
-- define the foreign key in table 'StoreProducts' on 'ProductID' which
refer to table 'Products' on field 'ProductID'
ALTER TABLE [dbo].[StoreProducts] ADD CONSTRAINT
[FK_StoreProducts_ProductID] FOREIGN KEY ([ProductID]) REFERENCES
[dbo].[Products] ([ProductID]);
-- define the foreign key in table 'SupplierProducts' on 'SupplierID'
which refer to table 'Suppliers' on field 'SupplierID'
ALTER TABLE [dbo].[SupplierProducts] ADD CONSTRAINT
[FK_SupplierProducts_SupplierID] FOREIGN KEY ([SupplierID]) REFERENCES
[dbo].[Suppliers] ([SupplierID]);
-- define the foreign key in table 'SupplierProducts' on 'WarehouseID'
which refer to table 'Warehouses' on field 'WarehouseID'
ALTER TABLE [dbo].[SupplierProducts] ADD CONSTRAINT
[FK_SupplierProducts_WarehouseID] FOREIGN KEY ([WarehouseID])
REFERENCES [dbo].[Warehouses] ([WarehouseID]);
-- define the foreign key in table 'SupplierProducts' on 'ProductID'
which refer to table 'Products' on field 'ProductID'
ALTER TABLE [dbo].[SupplierProducts] ADD CONSTRAINT
[FK_SupplierProducts_ProductID] FOREIGN KEY ([ProductID]) REFERENCES
[dbo].[Products] ([ProductID]);
-- define the foreign key in table 'WarehouseProducts' on 'WarehouseID'
which refer to table 'Warehouses' on field 'WarehouseID'
ALTER TABLE [dbo].[WarehouseProducts] ADD CONSTRAINT
[FK_WarehouseProducts_WarehouseID] FOREIGN KEY ([WarehouseID])
REFERENCES [dbo].[Warehouses] ([WarehouseID]);
-- define the foreign key in table 'WarehouseProducts' on 'ProductID'
which refer to table 'Products' on field 'ProductID'
ALTER TABLE [dbo].[WarehouseProducts] ADD CONSTRAINT
[FK_WarehouseProducts_ProductID] FOREIGN KEY ([ProductID]) REFERENCES
[dbo].[Products] ([ProductID]);
After created these script, tables like following:
Views
View: StoreProductInfos
This view is used to show the products and the price in each stores.
CREATE VIEW StoreProductInfos (StoreID, storeName, ProductID,
ProductName, Price)
AS
SELECT s.StoreID, s.Name AS storeName, p.ProductID, p.ProductName,
sp.Price
FROM Stores s
JOIN StoreProducts sp ON s.StoreID = sp.StoreID
JOIN Products p ON sp.ProductID = p.ProductID;
Test
View: CustomerInfos
This view is used to show the information of each customer with their
name, email, phone number, address.
CREATE VIEW CustomerInfos (CustomerID, Name, Email, PhoneType,
PhoneNumber, Address)
AS
SELECT c.CustomerID, c.Name, c.Email, cp.PhoneType, cp.PhoneNumber,
ca.Address
FROM Customers c
LEFT JOIN CustomerPhones cp ON c.CustomerID = cp.CustomerID
LEFT JOIN CustomerAddresses ca ON c.CustomerID = ca.CustomerID;
Test
View: CustomerOnlineOrders
This view is used to show the information of online orders by each
customer.
CREATE VIEW CustomerOnlineOrders (CustomerID, CustomerName, OrderID,
OrderDate, ProductName, Quantity)
AS
SELECT c.CustomerID, c.Name AS CustomerName, oo.OrderID, oo.OrderDate,
p.ProductName, oi.Quantity
FROM OnlineOrders oo
JOIN Customers c ON oo.CustomerID = c.CustomerID
JOIN OrderItems oi ON oo.OrderID = oi.OrderID
JOIN Products p ON oi.ProductID = p.ProductID;
Test
View: ProductReviews
This view is used to show all the reviews of each product.
CREATE VIEW ProductReviews (ProductID, ProductName, Score, Comment)
AS
SELECT p.ProductID, p.ProductName, r.Score, r.Comment
FROM Reviews r
JOIN OrderItems oi ON r.OrderItemID = oi.ItemID
JOIN Products p ON oi.ProductID = p.ProductID;
Test
Stored Procedures
SP: pAvgReviewScoreByProduct
This is used to calculate the average score achieved by the product
which user expect to.
CREATE PROC pAvgReviewScoreByProduct
@ProductID int
AS
SELECT AVG(r.Score) AS AVGScore
FROM Reviews r
JOIN OrderItems oi ON r.OrderItemID = oi.ItemID
JOIN Products p ON oi.ProductID = p.ProductID
WHERE p.ProductID = @ProductID;
Test
SP: pCustomerInfoById
This is used to get the information of customer by id.
CREATE PROC pCustomerInfoById
@CustomerID int
AS
SELECT c.CustomerID, c.Name, c.Email, cp.PhoneType, cp.PhoneNumber,
ca.Address
FROM Customers c
LEFT JOIN CustomerPhones cp ON c.CustomerID = cp.CustomerID
LEFT JOIN CustomerAddresses ca ON c.CustomerID = ca.CustomerID
WHERE c.CustomerID = @CustomerID;
Test
SP: pProductsSoldOnlineByStoreID
This is used to get the information the products sold in store.
CREATE PROC pProductsSoldOnlineByStoreID
@StoreID int
AS
SELECT s.StoreID, s.Name, p.ProductID, p.ProductName, SUM(oi.Quantity)
AS TotalSoldQuantity
FROM Stores s
JOIN OrderItems oi ON oi.StoreID = s.StoreID
JOIN Products p ON oi.ProductID = p.ProductID
WHERE s.StoreID = @StoreID
GROUP BY s.StoreID, s.Name, p.ProductID, p.ProductName;
Test
SP: pHighestShippingFareOrderByServiceType
This is used to get the highest shipping fare of order by the specified
service type.
CREATE PROC pHighestShippingFareOrderByServiceType
@ServiceType varchar(10)
AS
SELECT o.OrderID, s.Fare
FROM Shipping s
JOIN OnlineOrders o ON s.OrderID = o.OrderID
WHERE s.ServiceType = @ServiceType
AND s.Fare =
(SELECT MAX(s.Fare)
FROM Shipping s
JOIN OnlineOrders o ON s.OrderID = o.OrderID
WHERE s.ServiceType = @ServiceType);
Test
Functions
Func: fnAvgProductStorePrice
This function is used to calculate the average price of product
CREATE FUNCTION fnAvgProductStorePrice(@ProductID int)
RETURNS money
AS
BEGIN
DECLARE @AvgPrice money
SELECT @AvgPrice = AVG(Price) FROM StoreProducts WHERE ProductID =
@ProductID
RETURN @AvgPrice
END;
Test
Func: fnGetCustomerAddresses
This function is used to get the combined addresses of customer.
CREATE FUNCTION fnGetCustomerAddresses(@CustomerID int)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Address VARCHAR(1000)
SELECT @Address = STRING_AGG(Address, ' ,')
FROM Customers c
JOIN CustomerAddresses ca ON c.CustomerID = ca.CustomerID
WHERE c.CustomerID = @CustomerID
RETURN @Address
END;
Test
Func: fnHighestCustomerReviewScore
This function is used to collect the highest score of customer.
CREATE FUNCTION fnHighestCustomerReviewScore(@CustomerID int)
RETURNS FLOAT
AS
BEGIN
DECLARE @Score float
SELECT @Score = MAX(r.Score)
FROM Reviews r
JOIN OrderItems oi ON r.OrderItemID = oi.ItemID
JOIN OnlineOrders o ON oi.OrderID = o.OrderID
WHERE o.CustomerID = @CustomerID
RETURN @Score
END;
Test
Func: fnStoreProductsNumber
This function is used to statis the number of products in store.
CREATE FUNCTION fnStoreProductsNumber(@StoreID int)
RETURNS INT
AS
BEGIN
DECLARE @ProductsNumber INT
SELECT @ProductsNumber = COUNT(ProductID) FROM StoreProducts WHERE
StoreID = @StoreID
RETURN @ProductsNumber
END;
Test
TRIGGERS
Trigger: PurchaseItems_INSERT
This is used to make sure that the quantity of products in stores will
be right after purchased.
CREATE TRIGGER PurchaseItems_INSERT
ON PurchaseItems
AFTER INSERT
AS
UPDATE StoreProducts SET Quantity = Quantity - (SELECT Quantity
FROM inserted)
WHERE StoreID = (SELECT StoreID FROM inserted)
AND ProductID = (SELECT ProductID FROM inserted);
Before insert ‘PurchaseItems’, the quantity is 35
Then insert
After insert, the result is:
Trigger: InStoreReturns_INSERT
This is used to make sure that the quantity of products in stores will
be right after returned.
CREATE TRIGGER InStoreReturns_INSERT
ON InStoreReturns
AFTER INSERT
AS
UPDATE StoreProducts SET Quantity = Quantity + (SELECT Quantity
FROM inserted)
WHERE StoreID = (SELECT StoreID FROM inserted)
AND ProductID = (SELECT ProductID FROM inserted);
Before insert ‘InStoreReturns’, the quantity is :
Then insert:
After insert, the quantity is 20 which is added by 2.
Trigger: OrderItems_INSERT
This is used to make sure that the quantity of products in stores will
be right after online ordered.
CREATE TRIGGER OrderItems_INSERT
ON OrderItems
AFTER INSERT
AS
UPDATE StoreProducts SET Quantity = Quantity - (SELECT Quantity
FROM inserted)
WHERE StoreID = (SELECT StoreID FROM inserted)
AND ProductID = (SELECT ProductID FROM inserted);
Before insert ‘OrderItems’, the quantity is :
Then insert test data:
After insert, the quantity is 18 which minus 2.
Trigger: OrderItems_INSERT
This is used to make sure that the quantity of products in stores will
be right after online ordered.
TRANSACTIONS
This transaction insert the online order and the details of products
into table 'OnlineOrders' and 'OrderItems', which can make sure that
all the events success or failed then rollback to the original state.
BEGIN TRY
BEGIN TRAN;
INSERT INTO OnlineOrders VALUES ('20211127153020XHDJ', 1002, 'ready',
'2021-11-27 15:30:20', 1);
INSERT INTO OrderItems VALUES (10, '20211127153020XHDJ', 9101, 6521,
8011, 2, 30, 2, 60);
INSERT INTO OrderItems VALUES (11, '20211127153020XHDJ', 9101, 6631,
8022, 3, 23, 2, 46);
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH;
After this transaction, the data in table ‘OrderItems’ and ‘OnlineOrders’
are all inserted successful.
This tranaction is used to delete information of customer from differnt
tables, this must be in a transaction to make sure that all the
information of customer can be deleted successfully or failed then
rollback.
BEGIN TRY
BEGIN TRAN;
DELETE FROM CustomerPhones WHERE CustomerID = 3001;
DELETE FROM CustomerAddresses WHERE CustomerID = 3001;
DELETE FROM CustomerCreditCards WHERE CustomerID = 3001;
DELETE FROM OrderItems WHERE OrderID IN (SELECT OrderID FROM
OnlineOrders WHERE CustomerID = 3001);
DELETE FROM OnlineOrders WHERE CustomerID = 3001;
DELETE FROM WishList WHERE CustomerID = 3001;
DELETE FROM Customers WHERE CustomerID = 3001;
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH;
The deletion of table ‘Customers’ should be moved to the last because
of the foreign key referenced by ‘CustomerPhones’ ,
‘CustomerAddresses’, ‘CustomerCreditCards’ and orders.
After this transaction, the data of customer whose ‘CustomerID’ is
3001 in four tables are all cleared.
This transaction is used to make sure that when customer need return
products online, the steps of return and shipping should be inserted
all correct, if failed then rollback.
BEGIN TRY
BEGIN TRAN;
INSERT INTO OnlineReturns VALUES ('20211209183923YUSAJG',
'20211209153020AXFD', '2021-12-09 18:30:00', 1);
INSERT INTO Shipping VALUES (5, '20211209153020AXFD', 'UPS',
'Kaloadagatan 13', 'Rua da Panificadora, 12', 5.00, '2021-12-09 10:00',
'2021-12-09 15:00');
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH;
After finish this transaction, the result is OK, the data inserted into the
tables correct.
SCRIPTS (Create Users)
Create a user called 'test', he can not insert data into table
'Products', 'StoreProducts', 'WarehouseProducts', 'SupplierProducts'
CREATE LOGIN test WITH PASSWORD='test', DEFAULT_DATABASE=Shopping;
CREATE USER test FOR LOGIN test with PASSWORD = 'test' WITH
DEFAULT_SCHEMA=dbo;
EXEC sp_addrolemember 'db_owner', 'test';
DENY INSERT ON Products TO test;
DENY INSERT ON StoreProducts TO test;
DENY INSERT ON WarehouseProducts TO test;
DENY INSERT ON SupplierProducts TO test;
Create a user to be DBA. Then disable the user account.
create login dba with password='sqlstudy', default_database=Shopping;
create user dba for login dba with default_schema=dbo;
exec sp_addrolemember 'db_owner', 'dba';
alter login dba disable;
REMARK：
In this project, I try to abstract the business scene of the online
and in store shopping which contains many relationships. The process
is hard with difficulty, but after finishing the design of database, I
found it is full of joy to transfer the real business scenes into abstract
data structure. By creating some test data, I found some design may
have little mistakes, so I change part of the design and make the
database more appropriate.
To help querying easier, I make some views which capsulating the
usual queries into the virtual tables. To make some types of business
computation easier and help find some related information of entities,
the stored procedures and functions will work. In some cases, to help
keeping the data integrity and right relations, some data change may
be caused automatically, triggers may help working well.
As a manager of database, create new users to use database is
usual things. How to make it safe and appropriate is very important.
Consider the authentication, authorization and permissions properly
is also a very important thing.
Yisheng Yang
CSE581 Project 2
Introductions & Descriptions
1. One customer may have one or many phone numbers, each
phone number belong to a type. Also, one customer may have
one or more shipping/billing addresses and credit cards.
2. Customers can review products in orders they buy, they can
comment on the products and give them a score. Every time a
customer can only review one time on the order.
3. There are three prices of each product. One price is the
reference price of normal market which is in the ‘Products’ table.
The second one is the price offered by suppliers. The third one
is the price in stores which is often higher than the price offered
by suppliers.
4. As a customer, when he make an online order, he can choose
getting the package by himself without shipping .
5. One supplier may have one or many warehouses. Different
suppliers may store different products in the same warehouse.
6. One customer may have zero or many orders, some orders
maybe in store orders without shipping.
7. After buying products online, one customer can return products,
he can choose shipping or return products to stores directly.
Design ERD
Schema & Tables & Constraints & TestData
CREATE DATABASE Shopping
GO
USE Shopping;
-- this is used to record the customers' normal information including
ID, name, username(used for system login), email
CREATE TABLE [dbo].[Customers] (
[CustomerID] int NOT NULL,
[Name] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[UserName] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Email] varchar(50) COLLATE Chinese_PRC_CI_AS NULL
);
-- test data of customers
INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName],
[Email]) VALUES (N'1001', N'Mario Pontes', N'MP', N'vipmp@google.com');
INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName],
[Email]) VALUES (N'1002', N'Patricia McKenna', N'PAM',
N'vippam@126.com');
INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName],
[Email]) VALUES (N'2001', N'Helen Bennett', N'HB', N'viphb@qq.com');
INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName],
[Email]) VALUES (N'2002', N'Jaime Yorres', N'JY', N'vipjy@google.com');
INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName],
[Email]) VALUES (N'3001', N'Rene Phillips', N'RP',
N'viprp@google.com');
INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName],
[Email]) VALUES (N'3002', N'Liu Wong', N'LW', N'viplw@126.com');
-- create table used to record the addresses of customer,
-- one customer may have one or more addressess, the addresses of one
customer should be different
CREATE TABLE [dbo].[CustomerAddresses] (
[CustomerID] int NOT NULL,
[Address] varchar(100) NOT NULL
);
-- test data of customers' addresses
INSERT INTO [dbo].[CustomerAddresses] ([CustomerID], [Address]) VALUES
(N'1001', N'265, boulevard Charonne');
INSERT INTO [dbo].[CustomerAddresses] ([CustomerID], [Address]) VALUES
(N'1001', N'Calle Dr. Jorge Cash 321');
INSERT INTO [dbo].[CustomerAddresses] ([CustomerID], [Address]) VALUES
(N'1002', N'Rua da Panificadora, 12');
INSERT INTO [dbo].[CustomerAddresses] ([CustomerID], [Address]) VALUES
(N'2001', N'Gran Via, 1');
INSERT INTO [dbo].[CustomerAddresses] ([CustomerID], [Address]) VALUES
(N'2002', N'P.O. Box 555');
INSERT INTO [dbo].[CustomerAddresses] ([CustomerID], [Address]) VALUES
(N'3001', N'55 Grizzly Peak Rd.');
INSERT INTO [dbo].[CustomerAddresses] ([CustomerID], [Address]) VALUES
(N'3001', N'Smagsloget 45');
-- create table used to record the credit cards of customer,
-- one customer may have one or more credit cards, the credit cards of
one customer should be different
CREATE TABLE [dbo].[CustomerCreditCards] (
[CustomerID] int NOT NULL,
[CreaditCardID] varchar(50) NOT NULL
);
-- test data of customers' credit cardID
INSERT INTO [dbo].[CustomerCreditCards] ([CustomerID], [CreaditCardID])
VALUES (N'1001', N'2343243xc324323434');
INSERT INTO [dbo].[CustomerCreditCards] ([CustomerID], [CreaditCardID])
VALUES (N'1001', N'23878247284728473843');
INSERT INTO [dbo].[CustomerCreditCards] ([CustomerID], [CreaditCardID])
VALUES (N'1002', N'35432543543455453454');
INSERT INTO [dbo].[CustomerCreditCards] ([CustomerID], [CreaditCardID])
VALUES (N'2002', N'34534546456565666');
-- create table used to record the phone numbers of customer,
-- one customer may have one or more phone numbers, the phone numbers
of one customer should be different
-- each phone number belong to one type (home, business, cell)
CREATE TABLE [dbo].[CustomerPhones] (
[CustomerID] int NOT NULL,
[PhoneNumber] varchar(20) NOT NULL,
[PhoneType] varchar(10) NOT NULL
);
-- test data of customers' phone numbers
INSERT INTO [dbo].[CustomerPhones] ([CustomerID], [PhoneNumber],
[PhoneType]) VALUES (N'1001', N'423535666', N'home');
INSERT INTO [dbo].[CustomerPhones] ([CustomerID], [PhoneNumber],
[PhoneType]) VALUES (N'1001', N'534466346', N'business');
INSERT INTO [dbo].[CustomerPhones] ([CustomerID], [PhoneNumber],
[PhoneType]) VALUES (N'1001', N'645754576', N'cell');
INSERT INTO [dbo].[CustomerPhones] ([CustomerID], [PhoneNumber],
[PhoneType]) VALUES (N'1002', N'432644366', N'cell');
INSERT INTO [dbo].[CustomerPhones] ([CustomerID], [PhoneNumber],
[PhoneType]) VALUES (N'2001', N'235436465', N'home');
INSERT INTO [dbo].[CustomerPhones] ([CustomerID], [PhoneNumber],
[PhoneType]) VALUES (N'2001', N'892893834', N'business');
INSERT INTO [dbo].[CustomerPhones] ([CustomerID], [PhoneNumber],
[PhoneType]) VALUES (N'3001', N'334545656', N'cell');
-- this records the products' original information, this will be
referenced by other tables like 'StoreProducts', 'SupplierProducts',
'WarehouseProducts'
CREATE TABLE [dbo].[Products] (
[ProductID] int NOT NULL,
[ProductName] varchar(50) NOT NULL,
[Description] varchar(100) NULL,
[Price] money NOT NULL
);
-- test data of Products
INSERT INTO [dbo].[Products] ([ProductID], [ProductName],
[Description], [Price]) VALUES (N'1', N'Aniseed Syrup', N'12 - 550 ml
bottles', N'12.0000');
INSERT INTO [dbo].[Products] ([ProductID], [ProductName],
[Description], [Price]) VALUES (N'2', N'Tofu', N'40 - 100 g pkgs',
N'25.2500');
INSERT INTO [dbo].[Products] ([ProductID], [ProductName],
[Description], [Price]) VALUES (N'3', N'Genen Shouyu', N'24 - 250 ml
bottles', N'17.5000');
INSERT INTO [dbo].[Products] ([ProductID], [ProductName],
[Description], [Price]) VALUES (N'4', N'Pavlova', N'32 - 500 g boxes',
N'19.4500');
INSERT INTO [dbo].[Products] ([ProductID], [ProductName],
[Description], [Price]) VALUES (N'5', N'Carnarvon Tigers', N'16 kg
pkg', N'64.5000');
INSERT INTO [dbo].[Products] ([ProductID], [ProductName],
[Description], [Price]) VALUES (N'6', N'Filo Mix', N'16 - 2 kg boxes',
N'9.0000');
-- this record the products of customer wish to buy in the future.
CREATE TABLE [dbo].[WishList] (
[CustomerID] int NOT NULL,
[ProductID] int NOT NULL,
[CreateTime] datetime NOT NULL
);
-- test data of wish products list.
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'1001', N'1', N'2021-10-01 12:00:00.000');
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'1001', N'3', N'2021-09-10 10:00:00.000');
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'2001', N'4', N'2021-08-07 12:20:00.000');
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'2001', N'5', N'2021-08-09 13:00:00.000');
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'2001', N'6', N'2021-09-09 14:20:00.000');
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'2002', N'2', N'2021-10-03 15:30:00.000');
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'3001', N'1', N'2021-09-23 16:00:00.000');
INSERT INTO [dbo].[WishList] ([CustomerID], [ProductID], [CreateTime])
VALUES (N'3002', N'1', N'2021-10-06 09:00:00.000');
-- this record the history of in store purchases, this will be
referenced by 'PurchaseItems'
CREATE TABLE [dbo].[InStorePurchases] (
[PurchaseID] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CustomerID] int NOT NULL,
[PurchaseDate] datetime NOT NULL
);
-- test data of in store purchases
INSERT INTO [dbo].[InStorePurchases] ([PurchaseID], [CustomerID],
[PurchaseDate]) VALUES (N'20211103202208PUBN', N'2002', N'2021-11-03
20:22:08.000');
INSERT INTO [dbo].[InStorePurchases] ([PurchaseID], [CustomerID],
[PurchaseDate]) VALUES (N'20211123195028PPUB', N'3002', N'2021-11-23
19:50:28.000');
INSERT INTO [dbo].[InStorePurchases] ([PurchaseID], [CustomerID],
[PurchaseDate]) VALUES (N'20211201135523PXPU', N'1001', N'2021-12-01
13:50:00.000');
INSERT INTO [dbo].[InStorePurchases] ([PurchaseID], [CustomerID],
[PurchaseDate]) VALUES (N'20211202123525PXPU', N'1002', N'2021-12-02
12:35:25.000');
-- this record the details of in store purchases
CREATE TABLE [dbo].[PurchaseItems] (
[ItemID] int NOT NULL,
[PurchaseID] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[StoreID] int NOT NULL,
[SupplierID] int NOT NULL,
[WarehouseID] int NOT NULL,
[ProductID] int NOT NULL,
[UnitPrice] money NOT NULL,
[Quantity] int DEFAULT ((1)) NOT NULL,
[TotalPrice] money NOT NULL
);
-- test data of in store purchase details
INSERT INTO [dbo].[PurchaseItems] ([ItemID], [PurchaseID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'1', N'20211103202208PUBN', N'9101', N'6521',
N'8011', N'1', N'18.0000', N'2', N'36.0000');
INSERT INTO [dbo].[PurchaseItems] ([ItemID], [PurchaseID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'2', N'20211123195028PPUB', N'9201', N'6631',
N'8022', N'4', N'31.0000', N'3', N'93.0000');
INSERT INTO [dbo].[PurchaseItems] ([ItemID], [PurchaseID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'3', N'20211201135523PXPU', N'9301', N'6743',
N'8033', N'6', N'19.0000', N'1', N'19.0000');
INSERT INTO [dbo].[PurchaseItems] ([ItemID], [PurchaseID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'4', N'20211202123525PXPU', N'9302', N'6844',
N'8022', N'3', N'23.5000', N'2', N'27.0000');
-- this is used to record the history of in store products return
history
CREATE TABLE [dbo].[InStoreReturns] (
[ReturnID] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CustomerID] int NOT NULL,
[StoreID] int NOT NULL,
[SupplierID] int NOT NULL,
[WarehouseID] int NOT NULL,
[ProductID] int NOT NULL,
[Quantity] int NOT NULL,
[ReturnDate] datetime NOT NULL
);
-- the online orders placed by customer
CREATE TABLE [dbo].[OnlineOrders] (
[OrderID] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[CustomerID] int NOT NULL,
[Status] varchar(10) COLLATE Chinese_PRC_CI_AS NULL,
[OrderDate] datetime NOT NULL,
[NeedShip] bit DEFAULT ((1)) NOT NULL
);
-- test data of online orders
INSERT INTO [dbo].[OnlineOrders] ([OrderID], [CustomerID], [Status],
[OrderDate], [NeedShip]) VALUES (N'20201103153020YHJK', N'1001',
N'delivered', N'2020-11-03 15:30:00.000', N'1');
INSERT INTO [dbo].[OnlineOrders] ([OrderID], [CustomerID], [Status],
[OrderDate], [NeedShip]) VALUES (N'20210917122020XGSU', N'1002',
N'shipped', N'2021-09-17 12:20:20.000', N'1');
INSERT INTO [dbo].[OnlineOrders] ([OrderID], [CustomerID], [Status],
[OrderDate], [NeedShip]) VALUES (N'20211207175010XGSU', N'3001',
N'ready', N'2021-12-07 17:50:10.000', N'1');
INSERT INTO [dbo].[OnlineOrders] ([OrderID], [CustomerID], [Status],
[OrderDate], [NeedShip]) VALUES (N'20211208134020HGYT', N'3002',
N'returned', N'2021-12-08 13:40:20.000', N'1');
INSERT INTO [dbo].[OnlineOrders] ([OrderID], [CustomerID], [Status],
[OrderDate], [NeedShip]) VALUES (N'20211209153020AXFD', N'1001',
N'ready', N'2021-12-09 15:30:20.000', N'1');
-- the products' details of online orders, one order will refer to one
or many different products in store
-- in this record, the products are not the normal products in
'Products' table, they are refer to the items in 'StoreProducts'
CREATE TABLE [dbo].[OrderItems] (
[ItemID] int NOT NULL,
[OrderID] varchar(50) NOT NULL,
[StoreID] int NOT NULL,
[SupplierID] int NOT NULL,
[WarehouseID] int NOT NULL,
[ProductID] int NOT NULL,
[UnitPrice] money NOT NULL,
[Quantity] int DEFAULT ((1)) NOT NULL,
[TotalPrice] money NOT NULL
);
-- test data of online order details
INSERT INTO [dbo].[OrderItems] ([ItemID], [OrderID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'1', N'20211209153020AXFD', N'9101', N'6521',
N'8011', N'1', N'18.0000', N'3', N'54.0000');
INSERT INTO [dbo].[OrderItems] ([ItemID], [OrderID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'2', N'20201103153020YHJK', N'9201', N'6631',
N'8022', N'4', N'31.0000', N'2', N'62.0000');
INSERT INTO [dbo].[OrderItems] ([ItemID], [OrderID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'3', N'20210917122020XGSU', N'9301', N'6743',
N'8033', N'6', N'19.0000', N'5', N'90.0000');
INSERT INTO [dbo].[OrderItems] ([ItemID], [OrderID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'4', N'20210917122020XGSU', N'9301', N'6844',
N'8011', N'1', N'18.0000', N'3', N'52.0000');
INSERT INTO [dbo].[OrderItems] ([ItemID], [OrderID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'5', N'20211207175010XGSU', N'9302', N'6844',
N'8022', N'3', N'23.5000', N'2', N'47.0000');
INSERT INTO [dbo].[OrderItems] ([ItemID], [OrderID], [StoreID],
[SupplierID], [WarehouseID], [ProductID], [UnitPrice], [Quantity],
[TotalPrice]) VALUES (N'6', N'20211208134020HGYT', N'9101', N'6521',
N'8011', N'2', N'30.0000', N'3', N'88.0000');
-- sometimes the online orders may be returned, so the information
should be recorded.
-- customer can choose if the online order need shipping.
-- Although this is online order, but I think some one may be go to the
store to get the products by himself directly (ex: near the store)
CREATE TABLE [dbo].[OnlineReturns] (
[ReturnID] varchar(50) NOT NULL,
[OrderID] varchar(50) NOT NULL,
[ReturnDate] datetime NOT NULL,
[NeedShip] bit DEFAULT ((1)) NOT NULL
);
-- some test data of the online returns
INSERT INTO [dbo].[OnlineReturns] ([ReturnID], [OrderID], [ReturnDate],
[NeedShip]) VALUES (N'20211209183923YUSAJG', N'20211209153020AXFD',
N'2021-12-09 18:30:00.000', N'1');
-- for each online orders, customer can review the products they bought
-- one customer can review only after ordering products, one type of
products in one order can be reviewed once.(Not every customer can
review)
CREATE TABLE [dbo].[Reviews] (
[ReviewID] int NOT NULL,
[OrderItemID] int NOT NULL,
[Score] float(53) NULL,
[Comment] varchar(200) COLLATE Chinese_PRC_CI_AS NULL,
[CreateTime] datetime NOT NULL
);
-- some test data of customer reviews.
INSERT INTO [dbo].[Reviews] ([ReviewID], [OrderItemID], [Score],
[Comment], [CreateTime]) VALUES (N'1', N'1', N'4.5', N'nice', N'2021-
12-01 12:30:00.000');
INSERT INTO [dbo].[Reviews] ([ReviewID], [OrderItemID], [Score],
[Comment], [CreateTime]) VALUES (N'2', N'2', N'5', N'very good',
N'2021-12-02 10:00:00.000');
INSERT INTO [dbo].[Reviews] ([ReviewID], [OrderItemID], [Score],
[Comment], [CreateTime]) VALUES (N'3', N'3', N'2.5', N'not so good',
N'2021-11-20 12:00:00.000');
INSERT INTO [dbo].[Reviews] ([ReviewID], [OrderItemID], [Score],
[Comment], [CreateTime]) VALUES (N'4', N'4', N'3', N'just so so',
N'2021-12-03 15:00:00.000');
INSERT INTO [dbo].[Reviews] ([ReviewID], [OrderItemID], [Score],
[Comment], [CreateTime]) VALUES (N'5', N'5', N'1', N'it is so ugly!!',
N'2021-12-01 18:00:00.000');
INSERT INTO [dbo].[Reviews] ([ReviewID], [OrderItemID], [Score],
[Comment], [CreateTime]) VALUES (N'6', N'6', N'4.5', N'good', N'2021-
12-02 17:30:00.000');
-- the base information of stores
CREATE TABLE [dbo].[Stores] (
[StoreID] int NOT NULL,
[Name] varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Address] varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Phone] varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Fax] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[Email] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[WebPage] varchar(50) COLLATE Chinese_PRC_CI_AS NULL
);
-- some test data of stores
INSERT INTO [dbo].[Stores] ([StoreID], [Name], [Address], [Phone],
[Fax], [Email], [WebPage]) VALUES (N'9101', N'Svensk', N'3400 - 8th
Avenue Suite 210', N'(503) 555-9931', N'234434646', N'vipsven@126.com',
N'www.svensk.com');
INSERT INTO [dbo].[Stores] ([StoreID], [Name], [Address], [Phone],
[Fax], [Email], [WebPage]) VALUES (N'9201', N'Mayumi', N'92 Setsuko
Chuo-ku', N'(06) 431-7877', N'234325356', N'vipmayumi@google.com',
N'www.Mayumi.com');
INSERT INTO [dbo].[Stores] ([StoreID], [Name], [Address], [Phone],
[Fax], [Email], [WebPage]) VALUES (N'9301', N'Biscuits', N'Kaloadagatan
13', N'031-987 65 43', N'245346466', N'vipbiscuits@126.com',
N'www.Biscuits.com');
INSERT INTO [dbo].[Stores] ([StoreID], [Name], [Address], [Phone],
[Fax], [Email], [WebPage]) VALUES (N'9302', N'Nouveaux', N'22, rue H.
Voiron', N'(089) 6547665', N'265328662', N'vipnouveaux@google.com',
N'www.Nouveaux.com');
-- if one online order need shipping, there must be one shipping record
referred to.
-- if you need shipping, you should choose the service type, start
address, end address and so on.
CREATE TABLE [dbo].[Shipping] (
[ShippingID] int NOT NULL,
[OrderID] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ServiceType] varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[StartAddress] varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[EndAddress] varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Fare] money NULL,
[ExpectedDate] datetime NOT NULL,
[ActualDate] datetime NULL
);
-- some test data of shipping.
INSERT INTO [dbo].[Shipping] ([ShippingID], [OrderID], [ServiceType],
[StartAddress], [EndAddress], [Fare], [ExpectedDate], [ActualDate])
VALUES (N'1', N'20201103153020YHJK', N'USPS', N'3400 - 8th Avenue Suite
210', N'265, boulevard Charonne', N'3.0000', N'2020-11-05
00:00:00.000', N'2020-11-06 00:00:00.000');
INSERT INTO [dbo].[Shipping] ([ShippingID], [OrderID], [ServiceType],
[StartAddress], [EndAddress], [Fare], [ExpectedDate], [ActualDate])
VALUES (N'2', N'20210917122020XGSU', N'FedEx', N'92 Setsuko Chuo-ku',
N'Rua da Panificadora, 12', N'5.0000', N'2021-09-17 00:00:00.000',
N'2021-09-17 00:00:00.000');
INSERT INTO [dbo].[Shipping] ([ShippingID], [OrderID], [ServiceType],
[StartAddress], [EndAddress], [Fare], [ExpectedDate], [ActualDate])
VALUES (N'3', N'20211207175010XGSU', N'UPS', N'Kaloadagatan 13', N'55
Grizzly Peak Rd.', N'4.0000', N'2021-12-10 00:00:00.000', N'2021-12-09
00:00:00.000');
INSERT INTO [dbo].[Shipping] ([ShippingID], [OrderID], [ServiceType],
[StartAddress], [EndAddress], [Fare], [ExpectedDate], [ActualDate])
VALUES (N'4', N'20211208134020HGYT', N'FedEx', N'22, rue H. Voiron',
N'Smagsloget 45', N'6.0000', N'2021-12-12 00:00:00.000', N'2021-12-13
00:00:00.000');
INSERT INTO [dbo].[Shipping] ([ShippingID], [OrderID], [ServiceType],
[StartAddress], [EndAddress], [Fare], [ExpectedDate], [ActualDate])
VALUES (N'5', N'20211209153020AXFD', N'UPS', N'Kaloadagatan 13', N'Rua
da Panificadora, 12', N'5.0000', N'2021-12-09 00:00:00.000', N'2021-12-
09 00:00:00.000');
-- this record the products in stores
-- Why need this? I assume that the 'Products' table records the normal
information of products, and the price in 'Products' table is just an
suggest price
-- The same product in differnet stores may have the differnet price,
also the quantity of product will be different.
-- The same type of products may come from different warehouses.
CREATE TABLE [dbo].[StoreProducts] (
[StoreID] int NOT NULL,
[SupplierID] int NOT NULL,
[WarehouseID] int NOT NULL,
[ProductID] int NOT NULL,
[Price] money NULL,
[Quantity] int NULL
);
-- the test data of products in store.
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9101',
N'6521', N'8011', N'1', N'18.0000', N'20');
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9101',
N'6521', N'8011', N'2', N'30.0000', N'50');
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9101',
N'6631', N'8022', N'3', N'23.0000', N'35');
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9201',
N'6631', N'8022', N'4', N'31.0000', N'18');
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9201',
N'6743', N'8033', N'5', N'96.0000', N'6');
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9301',
N'6743', N'8033', N'6', N'19.0000', N'9');
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9301',
N'6844', N'8011', N'1', N'18.0000', N'20');
INSERT INTO [dbo].[StoreProducts] ([StoreID], [SupplierID],
[WarehouseID], [ProductID], [Price], [Quantity]) VALUES (N'9302',
N'6844', N'8022', N'3', N'23.5000', N'5');
-- record the suppliers' information, the products in stores are
provided by suppliers
CREATE TABLE [dbo].[Suppliers] (
[SupplierID] int NOT NULL,
[Name] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Address] varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Phone] varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Fax] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[Email] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[WebPage] varchar(50) COLLATE Chinese_PRC_CI_AS NULL
);
-- some test data of suppliers
INSERT INTO [dbo].[Suppliers] ([SupplierID], [Name], [Address],
[Phone], [Fax], [Email], [WebPage]) VALUES (N'6521', N'Exotic Liquids',
N'49 Gilbert St', N'(171) 555-2222', N'233546464',
N'vipgst@google.com', N'www.vipgst.org');
INSERT INTO [dbo].[Suppliers] ([SupplierID], [Name], [Address],
[Phone], [Fax], [Email], [WebPage]) VALUES (N'6631', N'Tokyo Traders',
N'9-8 Sekimai Musashino-shi', N'(03) 3555-5011', N'643464344',
N'vipttkyo@126.com', N'www.ttkyo.com');
INSERT INTO [dbo].[Suppliers] ([SupplierID], [Name], [Address],
[Phone], [Fax], [Email], [WebPage]) VALUES (N'6743', N'Grandma Kelly''s
Homestead', N'707 Oxford Rd.', N'(313) 555-5735', N'(313) 555-3349',
N'vipgkfx@google.com', N'www.gkfx.com');
INSERT INTO [dbo].[Suppliers] ([SupplierID], [Name], [Address],
[Phone], [Fax], [Email], [WebPage]) VALUES (N'6844', N'Leka Trading',
N'471 Serangoon Loop, Suite #402', N'555-8787', N'1232353533',
N'viplaket@126.com', N'www.laket.com');
-- record the relationships of suppliers and products
-- also you will find that there is a warehouse in it, it is used to
record the product is stored in which warehouse.
CREATE TABLE [dbo].[SupplierProducts] (
[SupplierID] int NOT NULL,
[WarehouseID] int NOT NULL,
[ProductID] int NOT NULL,
[NumberAvailable] int NOT NULL,
[UnitPrice] money NULL
);
-- some test data of the relationships between suppliers, products,
warehouses.
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6521', N'8011',
N'1', N'50', N'11.0000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6521', N'8011',
N'2', N'100', N'21.3000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6521', N'8011',
N'3', N'90', N'15.0000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6631', N'8022',
N'3', N'200', N'15.2000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6631', N'8022',
N'4', N'180', N'17.0000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6743', N'8033',
N'5', N'300', N'53.0000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6743', N'8033',
N'6', N'580', N'7.0000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6844', N'8011',
N'1', N'300', N'11.2000');
INSERT INTO [dbo].[SupplierProducts] ([SupplierID], [WarehouseID],
[ProductID], [NumberAvailable], [UnitPrice]) VALUES (N'6844', N'8022',
N'3', N'290', N'14.9000');
-- the information of warehouses which used to store products for
suppliers.
CREATE TABLE [dbo].[Warehouses] (
[WarehouseID] int NOT NULL,
[Name] varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Address] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[Phone] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[Fax] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[Email] varchar(30) COLLATE Chinese_PRC_CI_AS NULL
);
-- some test data of warehouses.
INSERT INTO [dbo].[Warehouses] ([WarehouseID], [Name], [Address],
[Phone], [Fax], [Email]) VALUES (N'8011', N'Ma Maison', N'2960 Rue St.
Laurent', N'(514) 555-9022', N'235343543', N'vipmma@google.com');
INSERT INTO [dbo].[Warehouses] ([WarehouseID], [Name], [Address],
[Phone], [Fax], [Email]) VALUES (N'8022', N'Pasta Buttini', N'Via dei
Gelsomini, 153', N'(089) 6547665', N'(089) 6547667',
N'vippasta@126.com');
INSERT INTO [dbo].[Warehouses] ([WarehouseID], [Name], [Address],
[Phone], [Fax], [Email]) VALUES (N'8033', N'Refrescos', N'Av. das
Americanas 12.890', N'(11) 555 4640', N'1243233535',
N'vipavdas@google.com');
-- the relationships between warehouse and products, it is also used to
record the number of products about in stock, on the way, in return.
CREATE TABLE [dbo].[WarehouseProducts] (
[WarehouseID] int NOT NULL,
[ProductID] int NOT NULL,
[NumberInStock] int DEFAULT ((0)) NOT NULL,
[NumberOnWay] int DEFAULT ((0)) NOT NULL,
[NumberInReturn] int DEFAULT ((0)) NOT NULL
);
-- some test data about the information between warehouses and products
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8011',
N'1', N'100', N'50', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8011',
N'2', N'300', N'100', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8011',
N'3', N'60', N'0', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8011',
N'4', N'50', N'10', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8011',
N'5', N'500', N'200', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8011',
N'6', N'150', N'30', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8022',
N'1', N'200', N'20', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8022',
N'2', N'200', N'50', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8022',
N'3', N'250', N'70', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8022',
N'4', N'300', N'80', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8022',
N'5', N'100', N'0', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8022',
N'6', N'80', N'10', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8033',
N'1', N'70', N'10', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8033',
N'2', N'120', N'20', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8033',
N'3', N'200', N'0', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8033',
N'4', N'150', N'50', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8033',
N'5', N'300', N'20', N'0');
INSERT INTO [dbo].[WarehouseProducts] ([WarehouseID], [ProductID],
[NumberInStock], [NumberOnWay], [NumberInReturn]) VALUES (N'8033',
N'6', N'220', N'0', N'0');
-- define the primary key in table 'Customers'
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT [PK_Customers] PRIMARY KEY
CLUSTERED ([CustomerID]);
-- define the primary key in table 'CustomerAddresses'
ALTER TABLE [dbo].[CustomerAddresses] ADD CONSTRAINT
[PK_CustomerAddresses] PRIMARY KEY CLUSTERED ([CustomerID], [Address]);
-- define the primary key in table 'CustomerCreditCards'
ALTER TABLE [dbo].[CustomerCreditCards] ADD CONSTRAINT
[PK_CustomerCreditCards] PRIMARY KEY CLUSTERED ([CustomerID],
[CreaditCardID]);
-- define the primary key in table 'CustomerPhones'
ALTER TABLE [dbo].[CustomerPhones] ADD CONSTRAINT [PK_CustomerPhones]
PRIMARY KEY CLUSTERED ([CustomerID], [PhoneNumber]);
-- define the foreign key in table 'CustomerAddresses' on 'CustomerID'
which refer to table 'Customers' on field 'CustomerID'
ALTER TABLE [dbo].[CustomerAddresses] ADD CONSTRAINT
[FK_CustomerAddresses_CustomerID] FOREIGN KEY ([CustomerID]) REFERENCES
[dbo].[Customers] ([CustomerID]);
-- define the foreign key in table 'CustomerCreditCards' on
'CustomerID' which refer to table 'Customers' on field 'CustomerID'
ALTER TABLE [dbo].[CustomerCreditCards] ADD CONSTRAINT
[FK_CustomerCreditCards_CustomerID] FOREIGN KEY ([CustomerID])
REFERENCES [dbo].[Customers] ([CustomerID]);
-- define the foreign key in table 'CustomerPhones' on 'CustomerID'
which refer to table 'Customers' on field 'CustomerID'
ALTER TABLE [dbo].[CustomerPhones] ADD CONSTRAINT
[FK_CustomerPhones_CustomerID] FOREIGN KEY ([CustomerID]) REFERENCES
[dbo].[Customers] ([CustomerID]);
-- add a constraint on 'PhoneType' to make sure that the value will
only be 'business', 'cell', 'home'
ALTER TABLE [dbo].[CustomerPhones] ADD CONSTRAINT [CK_CustomerPhones]
CHECK ([PhoneType]='business' OR [PhoneType]='cell' OR
[PhoneType]='home');
-- define the primary key in table 'Products'
ALTER TABLE [dbo].[Products] ADD CONSTRAINT [PK_Products] PRIMARY KEY
CLUSTERED ([ProductID]);
-- define the primary key in table 'WishList'
ALTER TABLE [dbo].[WishList] ADD CONSTRAINT [PK_WishList] PRIMARY KEY
CLUSTERED ([CustomerID], [ProductID]);
-- define the foreign key in table 'WishList' on 'CustomerID' which
refer to table 'Customers' on field 'CustomerID'
ALTER TABLE [dbo].[WishList] ADD CONSTRAINT [FK_WishList_CustomerID]
FOREIGN KEY ([CustomerID]) REFERENCES [dbo].[Customers] ([CustomerID]);
-- define the foreign key in table 'WishList' on 'ProductID' which
refer to table 'Products' on field 'ProductID'
ALTER TABLE [dbo].[WishList] ADD CONSTRAINT [FK_WishList_ProductID]
FOREIGN KEY ([ProductID]) REFERENCES [dbo].[Products] ([ProductID]);
-- define the primary key in table 'OnlineOrders'
ALTER TABLE [dbo].[OnlineOrders] ADD CONSTRAINT [PK_OnlineOrders]
PRIMARY KEY CLUSTERED ([OrderID]);
-- add a constraint on field 'Status' to make sure the value is
'returned' or 'delivered' or 'shipped' or 'ready'.
ALTER TABLE [dbo].[OnlineOrders] ADD CONSTRAINT
[CK_OnlineOrders_Status] CHECK ([Status]='returned' OR
[Status]='delivered' OR [Status]='shipped' OR [Status]='ready');
-- define the primary key in table 'OrderItems'
ALTER TABLE [dbo].[OrderItems] ADD CONSTRAINT [PK_OrderItems] PRIMARY
KEY CLUSTERED ([ItemID]);
-- define the primary key in table 'OnlineReturns'
ALTER TABLE [dbo].[OnlineReturns] ADD CONSTRAINT [PK_ReturnOrders]
PRIMARY KEY CLUSTERED ([ReturnID]);
-- define the primary key in table 'InStorePurchases'
ALTER TABLE [dbo].[InStorePurchases] ADD CONSTRAINT
[PK_InStorePurchases] PRIMARY KEY CLUSTERED ([PurchaseID]);
-- define the primary key in table 'PurchaseItems'
ALTER TABLE [dbo].[PurchaseItems] ADD CONSTRAINT [PK_PurchaseItems]
PRIMARY KEY CLUSTERED ([ItemID]);
-- to make sure the number of quantity need to be returned must be
positive.
ALTER TABLE [dbo].[InStoreReturns] ADD CONSTRAINT
[CK_InStoreReturns_Quantity] CHECK ([Quantity]>(0));
-- define the primary key in table 'InStoreReturns'
ALTER TABLE [dbo].[InStoreReturns] ADD CONSTRAINT [PK_InStoreReturns]
PRIMARY KEY CLUSTERED ([ReturnID]);
-- define the primary key in table 'Reviews'
ALTER TABLE [dbo].[Reviews] ADD CONSTRAINT [PK_Reviews] PRIMARY KEY
CLUSTERED ([ReviewID]);
-- to make sure that one item in order should only be reviewed once.
CREATE UNIQUE NONCLUSTERED INDEX [IX_UNIQUE_OrderItem]
ON [dbo].[Reviews] (
[OrderItemID] ASC
);
-- define the primary key in table 'Shipping'
ALTER TABLE [dbo].[Shipping] ADD CONSTRAINT [PK_Shipping] PRIMARY KEY
CLUSTERED ([ShippingID]);
-- to make sure that the ServiceType in 'Shipping' must be 'UPS' or
'FedEx' or 'USPS'.
ALTER TABLE [dbo].[Shipping] ADD CONSTRAINT [CK_Shipping_ServiceType]
CHECK ([ServiceType]='UPS' OR [ServiceType]='FedEx' OR
[ServiceType]='USPS');
-- define the primary key in table 'Stores'
ALTER TABLE [dbo].[Stores] ADD CONSTRAINT [PK_Stores] PRIMARY KEY
CLUSTERED ([StoreID]);
-- to make sure the price of products in store must be positive.
ALTER TABLE [dbo].[StoreProducts] ADD CONSTRAINT
[CK_StoreProducts_Price] CHECK ([Price]>(0));
-- to make sure that the quantity of products in stores will not be
negative.
ALTER TABLE [dbo].[StoreProducts] ADD CONSTRAINT
[CK_StoreProducts_Quantity] CHECK ([Quantity]>=(0));
-- define the primary key in table 'StoreProducts'
ALTER TABLE [dbo].[StoreProducts] ADD CONSTRAINT [PK_StoreProducts]
PRIMARY KEY CLUSTERED ([StoreID], [SupplierID], [WarehouseID],
[ProductID]);
-- define the primary key in table 'Suppliers'
ALTER TABLE [dbo].[Suppliers] ADD CONSTRAINT [PK_Suppliers] PRIMARY KEY
CLUSTERED ([SupplierID]);
-- make sure the unit price of product should be positive.
ALTER TABLE [dbo].[SupplierProducts] ADD CONSTRAINT
[CK_SupplierProducts_UnitPrice] CHECK ([UnitPrice]>(0));
-- make sure that the available number of products must be positive
ALTER TABLE [dbo].[SupplierProducts] ADD CONSTRAINT
[CK_SupplierProducts_NumberAvailable] CHECK ([NumberAvailable]>=(0));
-- define the primary key in table 'SupplierProducts'
ALTER TABLE [dbo].[SupplierProducts] ADD CONSTRAINT
[PK_SupplierProducts] PRIMARY KEY CLUSTERED ([SupplierID],
[WarehouseID], [ProductID]);
-- define the primary key in table 'Warehouses'
ALTER TABLE [dbo].[Warehouses] ADD CONSTRAINT [PK_Warehouses] PRIMARY
KEY CLUSTERED ([WarehouseID]);
-- define the primary key in table 'WarehouseProducts'
ALTER TABLE [dbo].[WarehouseProducts] ADD CONSTRAINT
[PK_WarehouseProducts] PRIMARY KEY CLUSTERED ([WarehouseID],
[ProductID]);
-- define the foreign key in table 'InStorePurchases' on 'CustomerID'
which refer to table 'Customers' on field 'CustomerID'
ALTER TABLE [dbo].[InStorePurchases] ADD CONSTRAINT
[FK_InStorePurchases_CustomerID] FOREIGN KEY ([CustomerID]) REFERENCES
[dbo].[Customers] ([CustomerID]);
-- define the foreign key in table 'InStoreReturns' on '[StoreID],
[SupplierID], [WarehouseID], [ProductID]' which refer to table
'StoreProducts' on field '[StoreID], [SupplierID], [WarehouseID],
[ProductID]'
ALTER TABLE [dbo].[InStoreReturns] ADD CONSTRAINT
[FK_InStoreReturns_StoreProducts] FOREIGN KEY ([StoreID], [SupplierID],
[WarehouseID], [ProductID]) REFERENCES [dbo].[StoreProducts]
([StoreID], [SupplierID], [WarehouseID], [ProductID]);
-- define the foreign key in table 'InStoreReturns' on 'CustomerID'
which refer to table 'Customers' on field 'CustomerID'
ALTER TABLE [dbo].[InStoreReturns] ADD CONSTRAINT
[FK_InStoreReturns_CustomerID] FOREIGN KEY ([CustomerID]) REFERENCES
[dbo].[Customers] ([CustomerID]);
-- define the foreign key in table 'OnlineOrders' on 'CustomerID' which
refer to table 'Customers' on field 'CustomerID'
ALTER TABLE [dbo].[OnlineOrders] ADD CONSTRAINT
[FK_OnlineOrders_CustomerID] FOREIGN KEY ([CustomerID]) REFERENCES
[dbo].[Customers] ([CustomerID]);
-- define the foreign key in table 'OnlineReturns' on 'OrderID' which
refer to table 'OnlineOrders' on field 'OrderID'
ALTER TABLE [dbo].[OnlineReturns] ADD CONSTRAINT
[FK_OnlineReturns_OrderID] FOREIGN KEY ([OrderID]) REFERENCES
[dbo].[OnlineOrders] ([OrderID]);
-- define the foreign key in table 'OrderItems' on '[StoreID],
[SupplierID], [WarehouseID], [ProductID]' which refer to table
'StoreProducts' on field '[StoreID], [SupplierID], [WarehouseID],
[ProductID]'
ALTER TABLE [dbo].[OrderItems] ADD CONSTRAINT
[FK_OrderItems_StoreProducts] FOREIGN KEY ([StoreID], [SupplierID],
[WarehouseID], [ProductID]) REFERENCES [dbo].[StoreProducts]
([StoreID], [SupplierID], [WarehouseID], [ProductID]);
-- define the foreign key in table 'OrderItems' on 'OrderID' which
refer to table 'OnlineOrders' on field 'OrderID'
ALTER TABLE [dbo].[OrderItems] ADD CONSTRAINT [FK_OrderItems_Order]
FOREIGN KEY ([OrderID]) REFERENCES [dbo].[OnlineOrders] ([OrderID]);
-- define the foreign key in table 'PurchaseItems' on 'PurchaseID'
which refer to table 'InStorePurchases' on field 'PurchaseID'
ALTER TABLE [dbo].[PurchaseItems] ADD CONSTRAINT
[FK_PurchaseItems_PurchaseID] FOREIGN KEY ([PurchaseID]) REFERENCES
[dbo].[InStorePurchases] ([PurchaseID]);
-- define the foreign key in table 'PurchaseItems' on '[StoreID],
[SupplierID], [WarehouseID], [ProductID]' which refer to table
'StoreProducts' on field '[StoreID], [SupplierID], [WarehouseID],
[ProductID]'
ALTER TABLE [dbo].[PurchaseItems] ADD CONSTRAINT
[FK_PurchaseItems_StoreProducts] FOREIGN KEY ([StoreID], [SupplierID],
[WarehouseID], [ProductID]) REFERENCES [dbo].[StoreProducts]
([StoreID], [SupplierID], [WarehouseID], [ProductID]);
-- define the foreign key in table 'Reviews' on 'OrderItemID' which
refer to table 'OrderItems' on field 'ItemID'
ALTER TABLE [dbo].[Reviews] ADD CONSTRAINT [FK_Reviews_OrderItemID]
FOREIGN KEY ([OrderItemID]) REFERENCES [dbo].[OrderItems] ([ItemID]);
-- define the foreign key in table 'Shipping' on 'OrderID' which refer
to table 'OnlineOrders' on field 'OrderID'
ALTER TABLE [dbo].[Shipping] ADD CONSTRAINT [FK_Shipping_OrderID]
FOREIGN KEY ([OrderID]) REFERENCES [dbo].[OnlineOrders] ([OrderID]);
-- define the foreign key in table 'StoreProducts' on 'StoreID' which
refer to table 'Stores' on field 'StoreID'
ALTER TABLE [dbo].[StoreProducts] ADD CONSTRAINT
[FK_StoreProducts_StoreID] FOREIGN KEY ([StoreID]) REFERENCES
[dbo].[Stores] ([StoreID]);
-- define the foreign key in table 'StoreProducts' on 'SupplierID'
which refer to table 'Suppliers' on field 'SupplierID'
ALTER TABLE [dbo].[StoreProducts] ADD CONSTRAINT
[FK_StoreProducts_SupplierID] FOREIGN KEY ([SupplierID]) REFERENCES
[dbo].[Suppliers] ([SupplierID]);
-- define the foreign key in table 'StoreProducts' on 'WarehouseID'
which refer to table 'Warehouses' on field 'WarehouseID'
ALTER TABLE [dbo].[StoreProducts] ADD CONSTRAINT
[FK_StoreProducts_WarehouseID] FOREIGN KEY ([WarehouseID]) REFERENCES
[dbo].[Warehouses] ([WarehouseID]);
-- define the foreign key in table 'StoreProducts' on 'ProductID' which
refer to table 'Products' on field 'ProductID'
ALTER TABLE [dbo].[StoreProducts] ADD CONSTRAINT
[FK_StoreProducts_ProductID] FOREIGN KEY ([ProductID]) REFERENCES
[dbo].[Products] ([ProductID]);
-- define the foreign key in table 'SupplierProducts' on 'SupplierID'
which refer to table 'Suppliers' on field 'SupplierID'
ALTER TABLE [dbo].[SupplierProducts] ADD CONSTRAINT
[FK_SupplierProducts_SupplierID] FOREIGN KEY ([SupplierID]) REFERENCES
[dbo].[Suppliers] ([SupplierID]);
-- define the foreign key in table 'SupplierProducts' on 'WarehouseID'
which refer to table 'Warehouses' on field 'WarehouseID'
ALTER TABLE [dbo].[SupplierProducts] ADD CONSTRAINT
[FK_SupplierProducts_WarehouseID] FOREIGN KEY ([WarehouseID])
REFERENCES [dbo].[Warehouses] ([WarehouseID]);
-- define the foreign key in table 'SupplierProducts' on 'ProductID'
which refer to table 'Products' on field 'ProductID'
ALTER TABLE [dbo].[SupplierProducts] ADD CONSTRAINT
[FK_SupplierProducts_ProductID] FOREIGN KEY ([ProductID]) REFERENCES
[dbo].[Products] ([ProductID]);
-- define the foreign key in table 'WarehouseProducts' on 'WarehouseID'
which refer to table 'Warehouses' on field 'WarehouseID'
ALTER TABLE [dbo].[WarehouseProducts] ADD CONSTRAINT
[FK_WarehouseProducts_WarehouseID] FOREIGN KEY ([WarehouseID])
REFERENCES [dbo].[Warehouses] ([WarehouseID]);
-- define the foreign key in table 'WarehouseProducts' on 'ProductID'
which refer to table 'Products' on field 'ProductID'
ALTER TABLE [dbo].[WarehouseProducts] ADD CONSTRAINT
[FK_WarehouseProducts_ProductID] FOREIGN KEY ([ProductID]) REFERENCES
[dbo].[Products] ([ProductID]);
After created these script, tables like following:
Views
View: StoreProductInfos
This view is used to show the products and the price in each stores.
CREATE VIEW StoreProductInfos (StoreID, storeName, ProductID,
ProductName, Price)
AS
SELECT s.StoreID, s.Name AS storeName, p.ProductID, p.ProductName,
sp.Price
FROM Stores s
JOIN StoreProducts sp ON s.StoreID = sp.StoreID
JOIN Products p ON sp.ProductID = p.ProductID;
Test
View: CustomerInfos
This view is used to show the information of each customer with their
name, email, phone number, address.
CREATE VIEW CustomerInfos (CustomerID, Name, Email, PhoneType,
PhoneNumber, Address)
AS
SELECT c.CustomerID, c.Name, c.Email, cp.PhoneType, cp.PhoneNumber,
ca.Address
FROM Customers c
LEFT JOIN CustomerPhones cp ON c.CustomerID = cp.CustomerID
LEFT JOIN CustomerAddresses ca ON c.CustomerID = ca.CustomerID;
Test
View: CustomerOnlineOrders
This view is used to show the information of online orders by each
customer.
CREATE VIEW CustomerOnlineOrders (CustomerID, CustomerName, OrderID,
OrderDate, ProductName, Quantity)
AS
SELECT c.CustomerID, c.Name AS CustomerName, oo.OrderID, oo.OrderDate,
p.ProductName, oi.Quantity
FROM OnlineOrders oo
JOIN Customers c ON oo.CustomerID = c.CustomerID
JOIN OrderItems oi ON oo.OrderID = oi.OrderID
JOIN Products p ON oi.ProductID = p.ProductID;
Test
View: ProductReviews
This view is used to show all the reviews of each product.
CREATE VIEW ProductReviews (ProductID, ProductName, Score, Comment)
AS
SELECT p.ProductID, p.ProductName, r.Score, r.Comment
FROM Reviews r
JOIN OrderItems oi ON r.OrderItemID = oi.ItemID
JOIN Products p ON oi.ProductID = p.ProductID;
Test
Stored Procedures
SP: pAvgReviewScoreByProduct
This is used to calculate the average score achieved by the product
which user expect to.
CREATE PROC pAvgReviewScoreByProduct
@ProductID int
AS
SELECT AVG(r.Score) AS AVGScore
FROM Reviews r
JOIN OrderItems oi ON r.OrderItemID = oi.ItemID
JOIN Products p ON oi.ProductID = p.ProductID
WHERE p.ProductID = @ProductID;
Test
SP: pCustomerInfoById
This is used to get the information of customer by id.
CREATE PROC pCustomerInfoById
@CustomerID int
AS
SELECT c.CustomerID, c.Name, c.Email, cp.PhoneType, cp.PhoneNumber,
ca.Address
FROM Customers c
LEFT JOIN CustomerPhones cp ON c.CustomerID = cp.CustomerID
LEFT JOIN CustomerAddresses ca ON c.CustomerID = ca.CustomerID
WHERE c.CustomerID = @CustomerID;
Test
SP: pProductsSoldOnlineByStoreID
This is used to get the information the products sold in store.
CREATE PROC pProductsSoldOnlineByStoreID
@StoreID int
AS
SELECT s.StoreID, s.Name, p.ProductID, p.ProductName, SUM(oi.Quantity)
AS TotalSoldQuantity
FROM Stores s
JOIN OrderItems oi ON oi.StoreID = s.StoreID
JOIN Products p ON oi.ProductID = p.ProductID
WHERE s.StoreID = @StoreID
GROUP BY s.StoreID, s.Name, p.ProductID, p.ProductName;
Test
SP: pHighestShippingFareOrderByServiceType
This is used to get the highest shipping fare of order by the specified
service type.
CREATE PROC pHighestShippingFareOrderByServiceType
@ServiceType varchar(10)
AS
SELECT o.OrderID, s.Fare
FROM Shipping s
JOIN OnlineOrders o ON s.OrderID = o.OrderID
WHERE s.ServiceType = @ServiceType
AND s.Fare =
(SELECT MAX(s.Fare)
FROM Shipping s
JOIN OnlineOrders o ON s.OrderID = o.OrderID
WHERE s.ServiceType = @ServiceType);
Test
Functions
Func: fnAvgProductStorePrice
This function is used to calculate the average price of product
CREATE FUNCTION fnAvgProductStorePrice(@ProductID int)
RETURNS money
AS
BEGIN
DECLARE @AvgPrice money
SELECT @AvgPrice = AVG(Price) FROM StoreProducts WHERE ProductID =
@ProductID
RETURN @AvgPrice
END;
Test
Func: fnGetCustomerAddresses
This function is used to get the combined addresses of customer.
CREATE FUNCTION fnGetCustomerAddresses(@CustomerID int)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Address VARCHAR(1000)
SELECT @Address = STRING_AGG(Address, ' ,')
FROM Customers c
JOIN CustomerAddresses ca ON c.CustomerID = ca.CustomerID
WHERE c.CustomerID = @CustomerID
RETURN @Address
END;
Test
Func: fnHighestCustomerReviewScore
This function is used to collect the highest score of customer.
CREATE FUNCTION fnHighestCustomerReviewScore(@CustomerID int)
RETURNS FLOAT
AS
BEGIN
DECLARE @Score float
SELECT @Score = MAX(r.Score)
FROM Reviews r
JOIN OrderItems oi ON r.OrderItemID = oi.ItemID
JOIN OnlineOrders o ON oi.OrderID = o.OrderID
WHERE o.CustomerID = @CustomerID
RETURN @Score
END;
Test
Func: fnStoreProductsNumber
This function is used to statis the number of products in store.
CREATE FUNCTION fnStoreProductsNumber(@StoreID int)
RETURNS INT
AS
BEGIN
DECLARE @ProductsNumber INT
SELECT @ProductsNumber = COUNT(ProductID) FROM StoreProducts WHERE
StoreID = @StoreID
RETURN @ProductsNumber
END;
Test
TRIGGERS
Trigger: PurchaseItems_INSERT
This is used to make sure that the quantity of products in stores will
be right after purchased.
CREATE TRIGGER PurchaseItems_INSERT
ON PurchaseItems
AFTER INSERT
AS
UPDATE StoreProducts SET Quantity = Quantity - (SELECT Quantity
FROM inserted)
WHERE StoreID = (SELECT StoreID FROM inserted)
AND ProductID = (SELECT ProductID FROM inserted);
Before insert ‘PurchaseItems’, the quantity is 35
Then insert
After insert, the result is:
Trigger: InStoreReturns_INSERT
This is used to make sure that the quantity of products in stores will
be right after returned.
CREATE TRIGGER InStoreReturns_INSERT
ON InStoreReturns
AFTER INSERT
AS
UPDATE StoreProducts SET Quantity = Quantity + (SELECT Quantity
FROM inserted)
WHERE StoreID = (SELECT StoreID FROM inserted)
AND ProductID = (SELECT ProductID FROM inserted);
Before insert ‘InStoreReturns’, the quantity is :
Then insert:
After insert, the quantity is 20 which is added by 2.
Trigger: OrderItems_INSERT
This is used to make sure that the quantity of products in stores will
be right after online ordered.
CREATE TRIGGER OrderItems_INSERT
ON OrderItems
AFTER INSERT
AS
UPDATE StoreProducts SET Quantity = Quantity - (SELECT Quantity
FROM inserted)
WHERE StoreID = (SELECT StoreID FROM inserted)
AND ProductID = (SELECT ProductID FROM inserted);
Before insert ‘OrderItems’, the quantity is :
Then insert test data:
After insert, the quantity is 18 which minus 2.
Trigger: OrderItems_INSERT
This is used to make sure that the quantity of products in stores will
be right after online ordered.
TRANSACTIONS
This transaction insert the online order and the details of products
into table 'OnlineOrders' and 'OrderItems', which can make sure that
all the events success or failed then rollback to the original state.
BEGIN TRY
BEGIN TRAN;
INSERT INTO OnlineOrders VALUES ('20211127153020XHDJ', 1002, 'ready',
'2021-11-27 15:30:20', 1);
INSERT INTO OrderItems VALUES (10, '20211127153020XHDJ', 9101, 6521,
8011, 2, 30, 2, 60);
INSERT INTO OrderItems VALUES (11, '20211127153020XHDJ', 9101, 6631,
8022, 3, 23, 2, 46);
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH;
After this transaction, the data in table ‘OrderItems’ and ‘OnlineOrders’
are all inserted successful.
This tranaction is used to delete information of customer from differnt
tables, this must be in a transaction to make sure that all the
information of customer can be deleted successfully or failed then
rollback.
BEGIN TRY
BEGIN TRAN;
DELETE FROM CustomerPhones WHERE CustomerID = 3001;
DELETE FROM CustomerAddresses WHERE CustomerID = 3001;
DELETE FROM CustomerCreditCards WHERE CustomerID = 3001;
DELETE FROM OrderItems WHERE OrderID IN (SELECT OrderID FROM
OnlineOrders WHERE CustomerID = 3001);
DELETE FROM OnlineOrders WHERE CustomerID = 3001;
DELETE FROM WishList WHERE CustomerID = 3001;
DELETE FROM Customers WHERE CustomerID = 3001;
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH;
The deletion of table ‘Customers’ should be moved to the last because
of the foreign key referenced by ‘CustomerPhones’ ,
‘CustomerAddresses’, ‘CustomerCreditCards’ and orders.
After this transaction, the data of customer whose ‘CustomerID’ is
3001 in four tables are all cleared.
This transaction is used to make sure that when customer need return
products online, the steps of return and shipping should be inserted
all correct, if failed then rollback.
BEGIN TRY
BEGIN TRAN;
INSERT INTO OnlineReturns VALUES ('20211209183923YUSAJG',
'20211209153020AXFD', '2021-12-09 18:30:00', 1);
INSERT INTO Shipping VALUES (5, '20211209153020AXFD', 'UPS',
'Kaloadagatan 13', 'Rua da Panificadora, 12', 5.00, '2021-12-09 10:00',
'2021-12-09 15:00');
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH;
After finish this transaction, the result is OK, the data inserted into the
tables correct.
SCRIPTS (Create Users)
Create a user called 'test', he can not insert data into table
'Products', 'StoreProducts', 'WarehouseProducts', 'SupplierProducts'
CREATE LOGIN test WITH PASSWORD='test', DEFAULT_DATABASE=Shopping;
CREATE USER test FOR LOGIN test with PASSWORD = 'test' WITH
DEFAULT_SCHEMA=dbo;
EXEC sp_addrolemember 'db_owner', 'test';
DENY INSERT ON Products TO test;
DENY INSERT ON StoreProducts TO test;
DENY INSERT ON WarehouseProducts TO test;
DENY INSERT ON SupplierProducts TO test;
Create a user to be DBA. Then disable the user account.
create login dba with password='sqlstudy', default_database=Shopping;
create user dba for login dba with default_schema=dbo;
exec sp_addrolemember 'db_owner', 'dba';
alter login dba disable;
REMARK：
In this project, I try to abstract the business scene of the online
and in store shopping which contains many relationships. The process
is hard with difficulty, but after finishing the design of database, I
found it is full of joy to transfer the real business scenes into abstract
data structure. By creating some test data, I found some design may
have little mistakes, so I change part of the design and make the
database more appropriate.
To help querying easier, I make some views which capsulating the
usual queries into the virtual tables. To make some types of business
computation easier and help find some related information of entities,
the stored procedures and functions will work. In some cases, to help
keeping the data integrity and right relations, some data change may
be caused automatically, triggers may help working well.
As a manager of database, create new users to use database is
usual things. How to make it safe and appropriate is very important.
Consider the authentication, authorization and permissions properly
is also a very important thing.
