[Personal notes] Three relationships: one-to-one, one-to-many, and many-to-many ., Zhijintang: one-to-one

[Personal notes] Three relationships: one-to-one, one-to-many, and many-to-many ., Zhijintang: one-to-one

 

One-to-one: for example, a student corresponds to an ID card number and student archive;

One-to-many: A class can have many students, but one student can only be in one class;

Many to many: A class can have many students and many courses;

 

One-to-multiple relationship processing:

We use the relationship between students and classes to illustrate a one-to-many relationship handling method.
Suppose there is a basic table class table (class number, remarks ,......). Student table (student ID, name ),

Method 1:
Add a field (class number) to the student table. This field is used as the foreign key of the student table and is associated with the class number in the class table. The value of each student entity in the class number field indicates the class that the student belongs.

Method 2:
Add a new relational table to indicate the relationship between the student and the class. The relational table contains fields (student ID and class number ). The student ID corresponds to the class ID to indicate the student's class.

Sample Code:

-- Create a class table
Create table t_class (
C_id int primary key AUTO_INCREMENT, -- class id
C_name VARCHAR (20) not null unique -- class name
)

-- Delete a student table
Drop table t_student;

-- Create a student table
Create table t_student (
S_id int primary key AUTO_INCREMENT, -- primary key id
S_name VARCHAR (15) UNIQUE, -- name
S_sex VARCHAR (5) DEFAULT 'male', -- Gender
S_age INT, -- Age
S_class_id INT, -- class id

Use a foreign key to associate the primary key of the class table. Note: The foreign key of the table must be the primary key of another table.
Constraint foreign key (s_class_id) REFERENCES t_class (c_id)
)

-- Add data
Insert into t_student (s_name, s_sex, s_age)
VALUES ('Liu key', 'male', '20 ')
Insert into t_student (s_name, s_sex, s_age)
VALUES ('Liu lay', 'male', '22 ')
Insert into t_student (s_name, s_sex, s_age)
VALUES ('tang bohu ', 'male', '20 ')

Summary: In general, one-to-many relationships are handled in a method.

 

Multi-to-Multi-link processing:

 

In the many-to-many relationship, we need to add a new relational table. Add a course selection table based on the student table and curriculum to indicate the many-to-many relationship between the student and the course. In the Course Selection table, the attributes must include the student number and course number. (Student number, course number) This attribute set is exactly the keyword of the Course Selection table.

Sample Code:

-- Create a student table
Create table student (
T_id int primary key AUTO_INCREMENT, -- Student ID
T_name VARCHAR (20), -- Student name
T_age INT, -- Age
T_sex CHAR (4), -- Gender
T_date DATE,
T_enrolDate DATETIME,
T_class VARCHAR (20)
)
-- Create a class table
Create table class (
B _id INT PRIMARY KEY AUTO_INCREMENT,
B _name VARCHAR (20), -- Edit ID
B _bname VARCHAR (50), -- class name
B _start DATE -- date of the start time type
)
-- Tables Table
Create table grade (
C_id INT,
C_nameId INT, -- Student id type int
C_courseId VARCHAR (40), -- course number type varchar
C_coursegrade INT, -- course score type int
C_exam DATE -- test time type date
)
-- Course schedule
Create table course (
K_id VARCHAR (40), -- course number type varchar
K_name VARCHAR (40) -- Course name type varchar
)