Database design---detailed introduction of multiple tables and paradigms

Data design-multiple tables, paradigm

Multi-table introduction

  • One-to-one relationship
    such as: person and ID
  • One-to-many or many-to-one relationship
    For example: department and employee
  • Many-to-many relationship
    eg: students and courses

Many to one

Establish a foreign key on the more side and point to the lesser side

Insert picture description here

Many-to-many relationship

The realization of the many-to-many relationship requires the help of the third sheet: the intermediate table The
intermediate table contains at least two contents, which point to the primary keys of the two tables

Insert picture description here

One-to-one relationship realization

Add a foreign key on either side, point to the other side,
or make both sides gradually the same

Insert picture description here

Case study

Classification of tourist routes, tourist routes, users

details

旅游线路分类:cid name
旅游线路:rid、name、price
用户:id、usename、password

Multi-table relationship

旅游路线分类 和线路 :一对多
旅游线路 和用户:多对多   
Insert picture description here

Paradigm overview

Design the rules of the database to make the database design reasonable
classification : first normal form, second normal form, third normal form (normally there are three normal forms)

  • First normal form: each column is an indivisible atomic data item
  • Second normal form: In the first normal form and on the basis, non-code attributes must completely rely on candidate codes (eliminate the dependence of non-primary attributes on the main code)
  • Third normal form: On the basis of the second normal form, any non-primary attributes do not depend on other non-primary attributes (eliminate transitive dependence on the basis of the second normal form)

First normal form

每个但是单独的,例如在excel中,学生可以分为学号和姓名,必须在两列中

Second normal form

* 几个概念:
	1. 函数依赖 :A->B 如果用过A的属性,可以确定唯一B属性的值,则称A依赖于B
		例如:学号和姓名:学号被姓名依赖
		例如:(学号,课程名称)可以确定分数
	2.完全依赖:A->B 如果A是一个属性组,B中属性值,可以通过A来确定
		例如:学号和课程可以确定分数
	3.部分函数依赖:如果A是一个属性组,则B属性值值需要依赖A属性组中的部分属性
		例如:(学号,课程)->姓名,只要要学号就可以确定
	4,传递函数依赖:A->B,B->C:如果A可以确定为一B,B可以确定C,则A可以确定C
		例如;学号被姓名所依赖,姓名被班主任确认,所以学号被班主任依赖
	5.码:如果一个属性或者属性组A,可以被其他依赖,则称A为码
*第二范式:
	1.主属性:码
	2.非码属性:不是码的属性
	3.要求消除非码属性对主属性部份依赖
	**方法**:表的查分

Third normal form

	消除非主属性之间的依赖

Database backup and restore

1.命令行的方式
	备份语法:mysqldum -u用户名 -p密码 数据库名称>保存的路径
		例:mysqldum -uroot -proot stu>d://
	还原语法:
		* 登陆数据库
		* 创建数据库
		* 使用数据库
		* 执行文件
2.图形化工具的方式