MySQL table design practice: Create a course schedule and student course selection schedule

MySQL table design practice: Create a course schedule and student course selection schedule

In the actual database design process, table design is one of the key links. This article will take creating a course schedule and student course selection schedule as an example to introduce the practical experience and skills of MySQL table design. We will use MySQL as the database management system and provide code examples.

1. Create a course schedule

The course schedule is a table that stores course information. We can create a course table using the following SQL statement:

CREATE TABLE course (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(10) NOT NULL,
  credits INT NOT NULL,
  department_id INT,
  CONSTRAINT fk_department
    FOREIGN KEY (department_id)
    REFERENCES department (id)
);

In the above code, we use the CREATE TABLE statement to create a table named course. The table contains the following fields:

• id: course ID, which is an integer type and is the primary key. We use the AUTO_INCREMENT keyword to make it auto-increment.
• name: Course name, a string type not exceeding 255 characters, and cannot be empty.
• code: Course code, a string type of no more than 10 characters, and cannot be empty.
• credits: course credits, integer type, and cannot be empty.
• department_id: ID of the department where the course is offered, an integer type. We set a foreign key constraint to associate it with the id field of department in another table.

2. Create student course selection table

The student course selection table is a table that stores student course selection information. We can use the following SQL statement to create a student course selection table:

CREATE TABLE student_course (
  id INT PRIMARY KEY AUTO_INCREMENT,
  student_id INT,
  course_id INT,
  grade FLOAT,
  CONSTRAINT fk_student
    FOREIGN KEY (student_id)
    REFERENCES student (id),
  CONSTRAINT fk_course
    FOREIGN KEY (course_id)
    REFERENCES course (id)
);

In the above code, we use the CREATE TABLE statement to create a table named student_course. The table contains the following fields:

• id: course selection ID, which is an integer type and is the primary key. We use the AUTO_INCREMENT keyword to make it auto-increment.
• student_id: student ID, integer type. We set a foreign key constraint and associate it to the id field of another table, student.
• course_id: Course ID, an integer type. We set a foreign key constraint and associate it to the id field of another table, course.
• grade: student’s course selection grade, which is a floating point number type.

3. Insert data

After creating the table structure, we can insert some sample data to test whether the table design is correct. Here is an insertion example of some sample data:

INSERT INTO course (name, code, credits, department_id)
VALUES ('数据库原理', 'CS101', 3, 1),
       ('计算机网络', 'CS201', 3, 1),
       ('操作系统', 'CS301', 4, 1),
       ('数据结构', 'CS401', 3, 1);

INSERT INTO student_course (student_id, course_id, grade)
VALUES (1, 1, 90),
       (1, 2, 85),
       (2, 1, 88),
       (3, 3, 92),
       (4, 4, 75);

In the above code, we use the INSERT INTO statement to insert data into the table. Specifically, we inserted information about four courses into the course table, and inserted records of five student course selections into the student_course table.

4. Query data

After the table design is completed, we can use the SELECT statement to query the data in the table. The following are examples of some common queries:

Query information about all courses:

SELECT * FROM course;

Query information about specified students’ course selection:

SELECT s.name AS student_name, c.name AS course_name, sc.grade
FROM student_course sc
JOIN student s ON sc.student_id = s.id
JOIN course c ON sc.course_id = c.id
WHERE s.id = 1;

The above example code demonstrates the JOIN statement. The three tables student_course, student and course are joined to query the course selection information of the student with student ID 1, and display the student's name, course name and grades.

Summary:

Through this practical case, we learned how to create a course schedule and student course selection table, and demonstrated the process of creating tables, inserting data, and querying data through code examples. In actual database design, reasonable table design can improve data storage efficiency and query performance. When designing the table structure, factors such as data relationships, data type selection, and foreign key constraint settings need to be considered to ensure data accuracy and consistency. I hope this article will be helpful to you in MySQL table design.

The above is the detailed content of MySQL table design practice: Create a course schedule and student course selection schedule. For more information, please follow other related articles on the PHP Chinese website!