Database design

1, Relationship between multiple tables

1. Classification:

1. One to one (understanding):
   • Such as: person and ID card
   • Analysis: a person has only one ID card, and one ID card can only correspond to one person
2. One to many (many to one):
   • For example: departments and employees
   • Analysis: a department has multiple employees, and one employee can only correspond to one department
3. Many to many:
   • For example: students and courses
   • Analysis: a student can choose many courses, and a course can also be selected by many students

2. Realize the relationship:

1. One to many (many to one):
   *For example: departments and employees
   *Implementation method: establish a foreign key on one of the multiple parties and point to the primary key of one party.
   
2. Many to many:

• For example: students and courses
• Implementation method: the implementation of many to many relationship requires the help of the third intermediate table. The middle table contains at least two fields, which are used as the foreign keys of the third table and point to the primary keys of the two tables respectively
  

1. One to one (understanding):
   *Such as: person and ID card
   *Implementation method: one-to-one relationship. You can add a unique foreign key on either side to point to the primary key of the other party.
   
2. case
   

-- Create tourism route classification table tab_category
-- cid Tourism route classification, primary key, automatic growth
-- cname The name of tourist route classification is not empty and unique. The string is 100
CREATE TABLE tab_category (
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(100) NOT NULL UNIQUE
);
			
-- Create tour route table tab_route
/*
rid Tourist routes, primary key, automatic growth
rname The name of the tourist route is not empty and unique. The string is 100
price Price
rdate Launch date, time type
cid Foreign key, category
*/
CREATE TABLE tab_route(
	rid INT PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(100) NOT NULL UNIQUE,
	price DOUBLE,
	rdate DATE,
	cid INT,
	FOREIGN KEY (cid) REFERENCES tab_category(cid)
);

/*Create user table tab_user
uid User primary key, self growing
username The user name is 100 long, unique and non empty
password Password length: 30, non empty
name Real name length 100
birthday birthday
sex Gender, fixed length string 1
telephone Mobile number, string 11
email Mailbox, string length 100
*/
CREATE TABLE tab_user (
	uid INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(100) UNIQUE NOT NULL,
	PASSWORD VARCHAR(30) NOT NULL,
	NAME VARCHAR(100),
	birthday DATE,
	sex CHAR(1) DEFAULT 'male',
	telephone VARCHAR(11),
	email VARCHAR(100)
);

/*
Create favorites tab_favorite
rid Travel route id, foreign key
date Collection time
uid User id, foreign key
rid And uid cannot be repeated. Set composite primary key. The same user cannot collect the same line twice
*/
CREATE TABLE tab_favorite (
	rid INT, -- line id
	DATE DATETIME,
	uid INT, -- user id
	-- Create composite primary key
	PRIMARY KEY(rid,uid), -- composite keys 
	FOREIGN KEY (rid) REFERENCES tab_route(rid),
	FOREIGN KEY(uid) REFERENCES tab_user(uid)
);

2, Paradigm of database design

• Concept: some specifications that need to be followed when designing the database. To follow the requirements of the latter paradigm, you must first follow all the requirements of the former paradigm
• When designing relational database, we should comply with different specification requirements and design a reasonable relational database. These different specification requirements are called different paradigms. Various paradigms present sub specifications. The higher the paradigm, the smaller the database redundancy.
• At present, relational database has six paradigms: the first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), bath Codd paradigm (BCNF), the fourth paradigm (4NF) and the fifth paradigm (5NF, also known as perfect paradigm).
  

1. Existing problems

1. There is very serious data redundancy (duplication): name, department name and department head.
   

2. There are problems in adding data: when adding new departments and department heads, the data is illegal.
   

3. Delete the existing data of Zhang Wuji and the graduation data of Zhang Wuji

• Solving the above problems requires the second paradigm and the third paradigm.

2. Classification:

1. First normal form (1NF): each column is an indivisible atomic data item [it can be seen that the "system" in the above table does not conform to the first paradigm.]
2. Second normal form (2NF): on the basis of 1NF, the non code attribute must be completely dependent on the code (on the basis of 1NF, part of the functional dependence of the non main attribute on the main code is eliminated)
3. The third normal form (3NF): on the basis of 2NF, any non primary attribute does not depend on other non primary attributes (eliminating transitive dependency on the basis of 2NF)

3. Several concepts:

1. Functional dependency: A – > B. if the value of attribute A (attribute group) is used, the value of unique attribute B can be determined. B depends on A
   For example: student number – > name. (student number, course name) -- > score
2. Full functional dependency: A – > B. If A is an attribute group, the determination of B attribute value needs to depend on all attribute values in A attribute group.
   For example: (student number, course name) -- > score
3. Partial function dependency: a – > B. If a is an attribute group, the value of attribute B only depends on some values in attribute group A.
   For example: (student number, course name) – > name
4. Transfer function dependency: A – > b, B – > C If the value of the unique B attribute can be determined by the value of the A attribute (attribute group), and the value of the unique C attribute can be determined by the value of the B attribute (attribute group), then the C transfer function depends on A
   For example: student number – > department name, department name – > Dean
5. Code: if an attribute or attribute group in a table is completely dependent on all other attributes, this attribute (attribute group) is called the code of the table
   For example: the code in the table is: (student number, course name)
   ① Primary attribute: all attributes in the code attribute group
   ② . non primary attribute: the attribute of the over code attribute group

4. Problem solving

1. Using the second paradigm: divide the above table into two tables to solve the problem of very serious data redundancy
   
2. Using the third paradigm: solve the problems of data addition and data deletion
   

Backup and restore of database

• Backup: mysqldump -u user name - p password database name > saved path
• Restore:
  ① . log in to the database
  ② , create database
  
  ③ Use database
  
  ④ . execution documents. source file path