Blog

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’[email protected]’); INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName], [Email]) VALUES (N’1002′, N’Patricia McKenna’, N’PAM’, N’[email protected]’); INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName], [Email]) VALUES (N’2001′, N’Helen Bennett’, N’HB’, N’[email protected]’); INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName], [Email]) VALUES (N’2002′, N’Jaime Yorres’, N’JY’, N’[email protected]’); INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName], [Email]) VALUES (N’3001′, N’Rene Phillips’, N’RP’, N’[email protected]’); INSERT INTO [dbo].[Customers] ([CustomerID], [Name], [UserName], [Email]) VALUES (N’3002′, N’Liu Wong’, N’LW’, N’[email protected]’); — 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’[email protected]’, 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’[email protected]’, 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’[email protected]’, 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’[email protected]’, 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’[email protected]’, 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’[email protected]’, 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’[email protected]’, 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’[email protected]’, 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’[email protected]’); 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’[email protected]’); 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’[email protected]’); — 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]