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
Skills Graph
%%%%{init: {'theme':'neutral'}}%%%%
flowchart RL
mysql(("`MySQL`")) -.-> mysql/BasicKeywordsandStatementsGroup(["`Basic Keywords and Statements`"])
mysql(("`MySQL`")) -.-> mysql/DatabaseFunctionsandDataTypesGroup(["`Database Functions and Data Types`"])
sql(("`SQL`")) -.-> sql/BasicSQLCommandsGroup(["`Basic SQL Commands`"])
sql(("`SQL`")) -.-> sql/DataManipulationandQueryingGroup(["`Data Manipulation and Querying`"])
sql(("`SQL`")) -.-> sql/DataDefinitionandIntegrityGroup(["`Data Definition and Integrity`"])
mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("`External Code Execution`")
mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/database("`DB Function - Info Retrieval`")
mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("`Table Creation`")
mysql/BasicKeywordsandStatementsGroup -.-> mysql/drop_table("`Table Removal`")
mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("`Database Selection`")
mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("`Database Creation`")
mysql/BasicKeywordsandStatementsGroup -.-> mysql/drop_database("`Database Deletion`")
mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/int("`Integer Type`")
sql/BasicSQLCommandsGroup -.-> sql/create_table("`CREATE TABLE statements`")
sql/BasicSQLCommandsGroup -.-> sql/drop_table("`DROP TABLE statements`")
sql/DataManipulationandQueryingGroup -.-> sql/exists("`EXISTS condition`")
sql/DataDefinitionandIntegrityGroup -.-> sql/data_types("`Data Types`")
sql/DataDefinitionandIntegrityGroup -.-> sql/constraints("`Constraints`")
subgraph Lab Skills
mysql/source -.-> lab-301414{{"`Student Grade Management System`"}}
mysql/database -.-> lab-301414{{"`Student Grade Management System`"}}
mysql/create_table -.-> lab-301414{{"`Student Grade Management System`"}}
mysql/drop_table -.-> lab-301414{{"`Student Grade Management System`"}}
mysql/use_database -.-> lab-301414{{"`Student Grade Management System`"}}
mysql/create_database -.-> lab-301414{{"`Student Grade Management System`"}}
mysql/drop_database -.-> lab-301414{{"`Student Grade Management System`"}}
mysql/int -.-> lab-301414{{"`Student Grade Management System`"}}
sql/create_table -.-> lab-301414{{"`Student Grade Management System`"}}
sql/drop_table -.-> lab-301414{{"`Student Grade Management System`"}}
sql/exists -.-> lab-301414{{"`Student Grade Management System`"}}
sql/data_types -.-> lab-301414{{"`Student Grade Management System`"}}
sql/constraints -.-> lab-301414{{"`Student Grade Management System`"}}
end