mySQL 创建一个简易连锁公司数据库

1 Requirement

The system consists of three subsystems

1.1 Member Management

• Every customer should have a membership and only member can purchase the items from the store.
• To be a member, the customers should register with their personal information such as their member number, last name, first name, phone number and address where the purchased items should be delivered.

1.2 Stock Management

• Every item should have item number, name, description and the quantity that a store has currently in stock.
• A store has to record the supplied price (can be varied upon the supplying time) and the time when the item is delivered to the store.

1.3 Sales Management

• The list of sales order consists of several sales items, which has sales order number, customer, order and delivered date.
• An ordered (or sold) item in the sales order list has the information about its item id, name, quantity and sold price

2 Basic Process

3 Implementation

3.1 DDL

• Describe how to create each of your tables based on your SQL statements. And illustrate that some crucial information, for example, primary key and foreign keys. For example, why need you have one/many foreign keys in your table?

3.1.1 Member

CREATE TABLE `Member` (
  `Member_Id` varchar(45) NOT NULL,
  `First_Name` varchar(45) DEFAULT NULL,
  `Last_Name` varchar(45) DEFAULT NULL,
  `Phone` varchar(45) DEFAULT NULL,
  `Address` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`Member_Id`)
) 

3.1.2 Order

CREATE TABLE `Order` (
  `Order_Id` varchar(45) NOT NULL,
  `Custom_Id` varchar(45) DEFAULT NULL,
  `Delivery_Time` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`Order_Id`)
) 

3.1.3 Item

CREATE TABLE `Item` (
  `Item_Id` varchar(45) NOT NULL,
  `Name` varchar(45) NOT NULL,
  `Description` varchar(45) NOT NULL,
  PRIMARY KEY (`Item_Id`)
) 

3.1.4 SaleList

CREATE TABLE `SaleList` (
  `Order_Id` varchar(45) NOT NULL,
  `Item_Id` varchar(45) NOT NULL,
  `Price` varchar(45) NOT NULL,
  `Store_Id` varchar(45) NOT NULL,
  `Amount` varchar(45) NOT NULL,
  PRIMARY KEY (`Order_Id`,`Item_Id`)
) 

3.1.5 Price

CREATE TABLE `Price` (
  `Item_Id` varchar(45) NOT NULL,
  `Supply_Time` varchar(45) NOT NULL,
  `Price` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`Item_Id`,`Supply_Time`),
  CONSTRAINT `Item_Id ` FOREIGN KEY (`Item_Id`) REFERENCES `Item` (`Item_Id`)
) 

3.1.6 Store

CREATE TABLE `Store` (
  `Store_Id` varchar(45) NOT NULL,
  `Address` varchar(45) NOT NULL,
  PRIMARY KEY (`Store_Id`)
) 

3.1.7 Stock

CREATE TABLE `Stock` (
  `Store_Id` varchar(45) NOT NULL,
  `Item_Id` varchar(45) NOT NULL,
  `Quantity` varchar(45) NOT NULL,
  PRIMARY KEY (`Store_Id`,`Item_Id`),
  CONSTRAINT `Store_Id` FOREIGN KEY ( `Store_Id`) REFERENCES `Store` (`Store_Id`)
) 

3.2 DML

• Describe your SQL statements and their execution process one. Please highlight your statements.  Of course, you also present their results.

3.2.1 Query the number of all memberships

use BestGoods;
select * from Member; use BestGoods;
SELECT count(Member_Id) from Member;

3.2.2 Query average costs of all memberships

• calculate average costs(both of them are correct)

select @n:=count(Member_Id) from Member;   
select count(Member_Id) into @n from Member;

use BestGoods;
select @n:=count(Member_Id) from Member; 
select sum(Price*Amount/@n) from SaleList;

3.2.3 Query the number of items a membership purchased

use BestGoods;
SELECT  BestGoods.Order.Custom_Id , sum(Amount) FROM BestGoods.Order
inner join SaleList
on BestGoods.Order.Order_Id = SaleList.Order_Id
group by BestGoods.Order.Custom_Id

3.2.4 Query the number of items which are sold from a store

use BestGoods;
select Store_Id, sum(Amount) from SaleList
group by Store_Id; 

3.2.5 Use transactions to modify details of items, for example prices and descriptions

use BestGoods;
update Price set Price="new price" where Item_Id = "3";

3.2.6 Use transactions to modify information of memberships, for example, name and address

use BestGoods;
update Member set Address="ShanDong" where  Member_Id= "0001";

Conclusion

1. In the concepture Model stage, it was confusing at the beginning. It was not clear about the dependencies of different tables PK and FK. We can regularize all the relationships first, eliminating dependencies first. Then, according to the actual situation, add ER model one by one

2. While building a table, you should record variable names and data types in a timely manner, in such a way that forgetting them later causes extra time to correct errors

3. I didn't get to know mySQL until the end of the whole course, and I wasn't familiar with the use of mySQL Workbench at the beginning. In this process, it is faster to find out with classmates, because there is no amount of knowledge in this process, but to know how to use the software. Cooperation can save time

4. When manipulating data, there may be more than one syntax to achieve the same effect. Try it out to understand the language's description conventions