Database Course Design--Summary of "Tea coffee" sales management system

The week-long database course design was finally completed, summarizing some experiences and lessons learned. Discover the basic knowledge or keep in mind, the knowledge is not the basis, what talk to develop? Because what to do is the first to have a certain goal, plan, and do things to achieve that level, this aspect must first have an impression in the brain, and then can use the knowledge point to play, so the knowledge point to grasp the firm, their own in the design course design process, found written database language a little run, There will be a problem with that. And then from the beginning to find the source of the problem, only to find the table's primary key and foreign key set up a problem. Then, after a while, suddenly realize what is the primary key and foreign key?

What is the definition and role of two? , it is found that the knowledge learned in the previous class is not used now, know it does not know why.

Key: A column or set of columns that uniquely identifies all rows in a table.
The primary key does not allow null values. 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, and each key is called a candidate key. Only one candidate key can be selected as the primary key for the table, and all other candidate keys are called alternate keys. Although a table does not require a primary key, it is a good practice to define a primary key.
Foreign KEY (FK): is one or more columns that are used to establish and strengthen links between two table data. You can create a link between two tables by adding one or more columns that hold the primary key values in the table to another table. This column becomes the foreign key of the second table.
For example, the school number in the score table cannot be the primary key of the score table (because a student can have multiple rows of results), but the student number in each line corresponds to the number of students in the student's table, and the school number in the students table is the primary key of the student table, then the student number in the table is the foreign key. (Typical one-to-many relationship)

There are several scenarios in which the SQL language is written:

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

(2) After the first entry of the new table data, the second time can not be entered again, has caused duplication.

(3) No note the primary key cannot be null.

(4) The constraint item is not clear when the foreign key is set.

Through this course design found that the need for a lot of knowledge we have not contacted, the Internet to find information when we learned that the only fur, there are many things we need to master we do not know. At the same time also found that there are a lot of things that have been learned we do not understand in place, can not be flexibly applied to the actual, can not be used to solve the problem, it is necessary to keep a lot of practice, through continuous self-study, constantly find problems, thinking problems, and then solve the problem. In this process we will have a deep understanding of the knowledge, but also can learn a lot of useful things.

CREATE TABLE BILL (bill_num char) primary key, Bill_time char (8) Not NULL, Bill_paymoney char (TEN) not N   ULL, Bill_summoney char (TEN) is not NULL, Bill_goodstype char (TEN) is not NULL, Bill_unitprice char (TEN) is not NULL, Customers_num Char (Ten), MemberID char (one),) CREATE TABLE Member (MemberID char (one) primary Key,membername char (TEN) N OT Null,membersex char (2) not null,memberphone varchar (a) not Null,bill_num char (TEN) not NULL, constraint membersex ch Eck (membersex= ' male ' or membersex= ' female '), foreign Key (Bill_num) references BILL (bill_num)) CREATE TABLE Customers (customers_ Num CHAR (TEN) primary key, Customers_name char (TEN) not NULL, Bill_num char (TEN) NOT NULL, foreign key (bill_num ) References BILL (bill_num)) CREATE TABLE Goods (Goods_num char (4) primary key, Goods_name char (TEN), Goods_stoc K Char (Ten), Bill_num char (TEN), foreign Key (Bill_num) references Bill (bill_num)) Goinsert into Bill (Bill_num, bill_t Ime,bill_paymoney, bill_sUmmoney, Bill_goodstype, Bill_unitprice, Memberid,customers_num) VALUES (' 2014010101 ', ' 20140101 ', ' 100 ', ' 50 ', ' Blue Mountains ', ' + ', ' 20140101001 ', ' Null ') insert into BILL (Bill_num, Bill_time,bill_paymoney, Bill_summoney, Bill_goodstype, Bill_ UnitPrice, MemberID, Customers_num) VALUES (' 2014010102 ', ' 20140101 ', ' 100 ', ' 50 ', ' char ', ' + ', ' 20140202002 ', ' Null ') Insert into BILL (Bill_num, Bill_time,bill_paymoney, Bill_summoney, Bill_goodstype, Bill_unitprice, memberid,customers _num) VALUES (' 2014010103 ', ' 20140101 ', ' 100 ', ' 50 ', ' American ', ' + ', ' 20140310003 ', ' Null ') insert into BILL (Bill_num, Bill_ Time,bill_paymoney, Bill_summoney, Bill_goodstype, Bill_unitprice, MemberID, Customers_num) VALUES (' 2014010104 ', ' 20140101 ', ' 100 ', ' 50 ', ' latte ', ' bill_num ', ' 20140404004 ', ' Null ') insert into BILL (, Bill_time,bill_paymoney, Bill_ Summoney, Bill_goodstype, Bill_unitprice, MemberID, Customers_num) VALUES (' 2014010105 ', ' 20140101 ', ' 100 ', ' 50 ', ' Mocha ', ' ' 20141001005 ', ' Null ') insert into BILL (Bill_num, Bill_time,bill_paymoney, Bill_summoney, BILl_goodstype, Bill_unitprice, MemberID, Customers_num) VALUES (' 2014010106 ', ' 20140101 ', ' 100 ', ' 50 ', ' cappuccino ', ' 55 ', ' 20141101006 ', ' Null ') insert into BILL (Bill_num, Bill_time,bill_paymoney, Bill_summoney, Bill_goodstype, Bill_ UnitPrice, MemberID, Customers_num) VALUES (' 2013010101 ', ' 20140101 ', ' 100 ', ' 50 ', ' cappuccino ', ' + ', ' Null ', ' 2014110106 ') Insert into BILL (Bill_num, Bill_time,bill_paymoney, Bill_summoney, Bill_goodstype, Bill_unitprice, MemberID, Customers _num) VALUES (' 2013010102 ', ' 20140101 ', ' 100 ', ' 50 ', ' cappuccino ', ' ', ' Null ', ' 2014110106 ') insert into Member (MemberID, Membername,membersex,memberphone,bill_num) VALUES (' 20140101001 ', ' Lee ', ' Male ', ' 15832578651 ', ' 2014010101 ') insert INTO Member (Memberid,membername,membersex,memberphone,bill_num) VALUES (' 20140202002 ', ' Li er ', ' female ', ' 15832578652 ', ' 2014010102 ') insert into Member (memberid,membername,membersex,memberphone,bill_num) VALUES (' 20140310003 ', ' Lie triple ', ' Male ', ' 15832578653 ', ' 2014010103 ') insert into Member (memberid,membername,membersex,memberphone,bill_num) VALUES (' 20140404004 ', ' John Doe ', ' female ', ' 15832578654 ', ' 2014010104 ') insert into Member (Memberid,membername,membersex,memberphone,bill _num) VALUES (' 20141001005 ', ' Lee ', ' Male ', ' 15832578655 ', ' 2014010105 ') insert into Member (Memberid,membername,membersex, Memberphone,bill_num) VALUES (' 20141101006 ', ' Li Liu ', ' female ', ' 15832578656 ', ' 2014010106 ') insert into Goods (Goods_num, Goods_  Name, Goods_stock, Bill_num) VALUES (' 1 ', ' Blue Mountains ', ' ten ', ' 2014010101 ') insert Into Goods (Goods_num, Goods_name, Goods_stock, Bill_num) VALUES (' 2 ', ' charcoal burning ', ' ten ', ' 2014010102 ') insert into Goods (Goods_ Num, Goods_name, Goods_stock, Bill_num) VALUES (' 3 ', ' American ', ' ten ', ' 2014010103 ') insert into Goods (Goods_num, Goods_name, Goods_stock, Bill_num) VALUES (' 4 ', ' latte ', ' ten ', ' 2014010104 ') insert into Goods (Goods_num, Goods_name, Goods_stock, Bill_ num) VALUES (' 5 ', ' Mocha ', ' ten ', ' 2014010105 ') insert into Goods (Goods_num, Goods_name, Goods_stock, Bill_num) VALUES (' 6 ', ' Cappuccino ', ' ten ', ' 2014010106 ') insert into Customers (Customers_num,customerS_name,bill_num) VALUES (' 01 ', ' Wang Yi ', ' 2013010101 ') insert into Customers (customers_num,customers_name,bill_num) values (' 02 ', ' King II ', ' 2013010102 ')

Experimental results: Member information:

Product Information:

Customer Information:

Billing information:

Database Course Design--Summary of "Tea coffee" sales management system