Student Information Management System | MySQL Database

Introduction

In this project, you will learn how to create a student information management system using MySQL. This system includes two tables: student_info and student_score, with a foreign key relationship between them.

👀 Preview

MySQL [studentSys]> DESC student_info;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| Id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.002 sec)

MySQL [studentSys]> DESC student_score;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| Id    | int        | NO   | PRI | NULL    |       |
| score | varchar(4) | YES  |     | NULL    |       |
| Sid   | int        | YES  | MUL | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

🎯 Tasks

In this project, you will learn:

How to start the MySQL server and log into the MySQL terminal
How to create a database and tables using SQL statements
How to establish a foreign key relationship between two tables

🏆 Achievements

After completing this project, you will be able to:

Understand the concept of a relational database and how to create and manage tables
Implement a foreign key constraint to ensure data integrity between related tables
Apply your knowledge of SQL to build a simple student information management system

Start MySQL and Create the Database

In this step, you will learn how to start the MySQL server and create the studentSys database.

Start the MySQL server:
```
sudo /etc/init.d/mysql start
```
Log into the MySQL terminal:
```
mysql -uroot
```
Create a new file named studentSys.sql in the /home/labex/project directory.
In the studentSys.sql file, create the studentSys database:
```
DROP DATABASE IF EXISTS studentSys;
CREATE DATABASE IF NOT EXISTS studentSys CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
```
The DROP DATABASE IF EXISTS statement checks if the studentSys database already exists, and if it does, it deletes it. The CREATE DATABASE IF NOT EXISTS statement then creates the studentSys database with the specified character set and collation.

Create the student_info Table

In this step, you will learn how to create the student_info table.

In the studentSys.sql file, use the studentSys database:
```
USE studentSys;
```
Create the student_info table:
```
DROP TABLE IF EXISTS student_info;
CREATE TABLE student_info (
    Id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(10),
    PRIMARY KEY (Id)
);
```
The DROP TABLE IF EXISTS statement checks if the student_info table already exists, and if it does, it deletes it. The CREATE TABLE statement then creates the student_info table with two columns: Id and name. The Id column is set as the primary key and is set to auto-increment.

Create the student_score Table and Establish the Foreign Key Relationship

In this step, you will learn how to create the student_score table and establish a foreign key relationship between the student_score table and the student_info table.

In the studentSys.sql file, create the student_score table:
```
DROP TABLE IF EXISTS student_score;
CREATE TABLE student_score (
    Id INT NOT NULL,
    score VARCHAR(4),
    Sid INT,
    PRIMARY KEY (Id),
    FOREIGN KEY (Sid) REFERENCES student_info(Id)
);
```
The DROP TABLE IF EXISTS statement checks if the student_score table already exists, and if it does, it deletes it. The CREATE TABLE statement then creates the student_score table with three columns: Id, score, and Sid.

The PRIMARY KEY (Id) statement sets the Id column as the primary key for the student_score table.

The FOREIGN KEY (Sid) REFERENCES student_info(Id) statement establishes a foreign key relationship between the Sid column in the student_score table and the Id column in the student_info table. This means that the values in the Sid column of the student_score table must match the values in the Id column of the student_info table.
In the MySQL prompt, run the following command to execute the studentSys.sql script:

SOURCE ~/project/studentSys.sql

After completing these steps, you have created the studentSys database, the student_info table, and the student_score table, and established a foreign key relationship between the two tables.

MySQL [studentSys]> DESC student_info;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| Id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.002 sec)

MySQL [studentSys]> DESC student_score;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| Id    | int        | NO   | PRI | NULL    |       |
| score | varchar(4) | YES  |     | NULL    |       |
| Sid   | int        | YES  | MUL | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.

Student Grade Management System