47-Online Shop Database-Order Data Operation

Item Description

In the context of the rise of e-commerce, establishing and utilizing the Internet to open up sales channels, helping enterprises adjust commodity structure in time, helping distributors to open information portal of goods sources become an effective solution to the problem of poor information flow. E-commerce is conducive to the transformation of business mechanism, the establishment of modern enterprise system, and the improvement of sales level and competitiveness of enterprises. We have achieved a series of online shopping services, such as direct browsing of goods, purchasing goods, creating orders, viewing new products, special prices, hot sellers, etc. on the Internet, message, customer management, commodity management, commodity data search, order management, commodity classification management, announcement/feedback management, etc. This project is to implement the order information form Orders, commodity information forms goods, etc. Operation of Customs table.

The operation requirements of the online store system database are as follows:

1) Database EshopDB.

2) Order information table Orders, the table structure is shown in table J2-48-1.

Table J2-47-1 Orders table

3) Goods commodity information table, the table structure is shown in table J2-47-2.

Table J2-47-2 Goods table

4) Customer information table Customs, table structure as shown in table J2-47-3.

Table J2-47-3 Customs table

5) Establishing associations between information tables, Goods_of the Orders table The ID is associated with the ID of the Goods table; CustomsID of Orders table and C_of Customs ID association.

6) Table Goods basic data is shown in table J2-47-4.

Table J2-47-4 Goods table base data

(1) Task description

Task 1: Create Online Shop System Database EshopDB in SQL Language

1) The primary database file has an initial value of 10MB and a maximum of 20MB, increasing by 5MB.

2) The initial value of the log file is 5MB, up to 10MB, increasing by 10%.

IF DB_ID('EshopDB') IS NOT NULL DROP DATABASE EshopDB
GO
CREATE DATABASE EshopDB
ON PRIMARY
(
	NAME=EshopDB,
	FILENAME='D:\xxxx\EshopDB.mdf',
	SIZE=10MB,
	MAXSIZE=20MB,
	FILEGROWTH=5MB
)
LOG ON
(
	NAME=EshopDB_log,
	FILENAME='D:\xxxx\EshopDB_log.ldf',
	SIZE=5MB,
	MAXSIZE=10MB,
	FILEGROWTH=10%
)

Task 2: Create order information table Orders, commodity information table goods, customer information table Customs in SQL language

1) Create database tables according to the provided table J2-47-1, J2-47-2, J2-47-2 structure, with primary and foreign keys.

CREATE TABLE Goods
(
	ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	GoodsName NVARCHAR(100) NOT NULL,
	Price MONEY NOT NULL,
	BoughtNum INT NOT NULL,
)
CREATE TABLE Customs
(
	C_ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	C_Name NVARCHAR(20) NOT NULL,
	C_Address NVARCHAR(100) NOT NULL,
	C_Phone NVARCHAR(50),
	C_Tel NVARCHAR(20) NOT NULL,
	C_EMail NVARCHAR(100),
)

CREATE TABLE Orders
(
	O_ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	Goods_ID INT NOT NULL,
	B_Num INT NOT NULL,
	CustomsID INT NOT NULL,
	FOREIGN KEY(Goods_ID) REFERENCES Goods(ID),
	FOREIGN KEY(CustomsID) REFERENCES Customs(C_ID),
)

Task 3: Operate order information table Orders, commodity information table goods, customer information table Customs in SQL language

1) Find out the user name that "lenovo" purchased.

2) Delete the purchase record of "HTC".

3) Count how many users have purchased mobile phones.

INSERT INTO goods VALUES('lenovo',799.00,10),
('Vivo',1299.00,50),
('HTC',1589.00,20),
('nokia',1899.00,30)


SELECT C_Name FROM Customs WHERE C_ID IN (SELECT CustomsID FROM Orders WHERE Goods_ID=(SELECT ID FROM Goods WHERE GoodsName='lenovo'))

DELETE FROM Orders WHERE Goods_ID=(SELECT ID FROM Goods WHERE GoodsName='HTC')

SELECT COUNT(DISTINCT(CustomsID)) FROM Orders