Database Course Design -- Summary of "coffee tea" sales management system and database Course Design

Database Course Design -- Summary of "coffee tea" sales management system and database Course Design

The one-week database course design was finally completed to summarize some experiences and lessons learned. The basic knowledge should be kept in mind at all times. If the knowledge is not basic, how can we develop it? This is because we must first make certain goals, plans, and levels of implementation of things. In this regard, we must first have an impression in our mind, then we can use the learned knowledge points to give full play to it. Therefore, we must master the knowledge points firmly. During the course design process, we can find that the written database language runs a little bit, this problem occurs. Then, we searched for the source of the problem from the beginning to find out that there was a problem with the table's primary key and foreign key settings. After a long time, I suddenly realized what the primary key and foreign key are?

What are their definitions and functions ?, Only in this way can we find that the knowledge we have learned in the previous class is not used, but we do not know why.

Key: one or a group of columns that uniquely identify all rows in the table.
NULL values are not allowed for the primary key. There cannot be two rows with the same primary key value, so the primary key value always uniquely identifies a single row.
A table can have more than one key that uniquely identifies a row. Each key is called a candidate key. Only one candidate key can be selected as the primary key of the table. All other candidate keys are called backup keys. Although a table does not require a primary key, it is a good practice to define a primary key.
Foreign key (FK): one or more columns used to establish and enhance the link between two table data. You can create a link between two tables by adding one or more columns of the primary key values in the table to another table. This column becomes the foreign key of the second table.
For example, the student ID in the student table cannot be the primary key of the student table (because a student can have multiple rows of Score data), but the student ID in each row corresponds to the student ID in the student table, the student ID in the student table is the primary key of the student table, and the student ID in the student table is the foreign key of the student table. (Typical one-to-many relationship)

These situations also occur when writing SQL languages:

(1) When creating a new table, the English brackets were written as Chinese characters and the error was found for half an hour.

(2) After a new table is input for the first time, it cannot be input again for the second time.

(3) Note that the primary key cannot be null.

(4) the constraints are not clear during foreign key settings.

Through this course design, we found that we had never touched on a lot of the knowledge we needed. When searching for information online, we found that what we learned previously was just fur, there are still many things we don't know at all. At the same time, we also found that there are many things we have learned that we have not understood in place, can not be used flexibly in practice, and can not be used to solve problems very well. This requires a lot of practice on our own, through continuous self-study, we constantly discover problems, think about problems, and solve problems. In this process, we will have a deep understanding of what we have learned and can also learn a lot of practical things.

Create table bill (BILL_num char (10) primary key, BILL_time char (8) not null, BILL_paymoney char (10) not null, BILL_summoney char (10) not null, BILL_goodstype char (10) not null, BILL_unitprice char (10) not null, Customers_num char (10), MemberID char (11),) create table Member (MemberID char (11) primary key, MemberNAme char (10) not null, MemberSex char (2) not null, Memberphone varchar (12) not null, BILL_num char (10) not null, constraint MemberSex check (MemberSex = 'male' or MemberSex = 'female '), foreign key (BILL_num) references BILL (BILL_num) create table MERs (Customers_num char (10) primary key, customers_name char (10) not null, BILL_num char (10) not null, foreign key (BILL_num) references BILL (BILL_num) create table Goods (Goods_num char (4) primary key, goods_name char (10), Goods_stock char (10), BILL_num char (10), foreign key (BILL_num) references BILL (BILL_num) goinsert into BILL (BILL_num, BILL_time, BILL_paymoney, BILL_summoney, BILL_goodstype, BILL_unitprice, MemberID, Customers_num) values ('2016030', '2016030', '50', 'lanlan', '30', '2016030 ', 'null') insert into BILL (BILL_num, BILL_time, BILL_paymoney, BILL_summoney, BILL_goodstype, BILL_unitprice, MemberID, Customers_num) values ('123', '123', '123 ', '50', 'Char burning ', '35', '123', 'null') insert into BILL (BILL_num, BILL_time, BILL_paymoney, BILL_summoney, BILL_goodstype, BILL_unitprice, MemberID, Customers_num) values ('20160301', '20160301', '50', 'American ', '40', '20160301', 'null') insert into BILL (BILL_num, BILL_time, BILL_paymoney, BILL_summoney, BILL_goodstype, BILL_unitprice, MemberID, Customers_num) values ('000000', '000000', '000000', '50', 'note', '45 ', '123', 'null') insert into BILL (BILL_num, BILL_time, BILL_paymoney, BILL_summoney, BILL_goodstype, BILL_unitprice, MemberID, Customers_num) values ('123', '123 ', '200', '50', 'moca', '50', '100', 'null') insert into BILL (BILL_num, BILL_time, BILL_paymoney, BILL_summoney, BILL_goodstype, BILL_unitprice, memberID, Customers_num) values ('20160301', '20160302', '50', 'cappuccino ', '55', '123', 'null ') insert into BILL (BILL_num, BILL_time, BILL_paymoney, BILL_summoney, BILL_goodstype, BILL_unitprice, MemberID, Customers_num) values ('20160301', '20160301', '50 ', 'cappuccino ', '55', 'null', '000000') insert into BILL (BILL_num, BILL_time, BILL_paymoney, BILL_summoney, BILL_goodstype, BILL_unitprice, MemberID, Customers_num) values ('123', '123', '123', '50', 'cappuccino ', '55', 'null', '123') insert into Member (MemberID, memberNAme, MemberSex, Memberphone, BILL_num) values ('000000', 'Li yi', 'male', '000000', '000000') insert into MemberID, MemberNAme, MemberSex, memberphone, BILL_num) values ('123456', 'lily', 'female ', '1234568') insert into Member (MemberID, MemberNAme, MemberSex, Memberphone, BILL_num) values ('000000', 'Lee 3', 'male', '000000', '000000') insert into Member (MemberID, MemberNAme, MemberSex, Memberphone, BILL_num) values ('000000', 'lily', 'female ', '000000', '000000') insert into Member (MemberID, MemberNAme, MemberSex, Memberphone, BILL_num) values ('000000', 'lil5', 'male', '000000', '000000') insert into Member (MemberID, MemberNAme, MemberSex, Memberphone, BILL_num) values ('123', 'lily', 'female ', '123', '123') insert into Goods_num, Goods_name, Goods_stock, BILL_num) values ('1', 'lanlanlan', '10', '000000') insert into Goods (Goods_num, Goods_name, Goods_stock, BILL_num) values ('2', 'charcoal burning ', '10', '123') insert into Goods (Goods_num, Goods_name, Goods_stock, BILL_num) values ('3', 'American ', '10', '123 ') insert into Goods (Goods_num, Goods_name, Goods_stock, BILL_num) values ('4', 'latte ', '10', '000000') insert into Goods_num, Goods_name, Goods_stock, BILL_num) values ('5', 'moca', '10', '000000') insert into Goods (Goods_num, Goods_name, Goods_stock, BILL_num) values ('6 ', 'cappuccino ', '10', '123') insert into MERs (Customers_num, Customers_name, BILL_num) values ('01', 'wang yi', '123 ') insert into MERs (Customers_num, Customers_name, BILL_num) values ('02 ', 'wang 2', '123 ')

Lab result: member information:

Product Information:

Customer Information:

Bill information: