Design and create a relational database for a Student Management System (SMS) using SQL. The database should efficiently store and manage data on students, courses, instructors, and enrollments, enabling users to perform key administrative functions such as adding students, assigning courses, and generating dynamic reports through SQL queries.
- Create a well-structured relational database using SQL (DDL).
- Populate tables with sample data using SQL or CSV imports (DML).
- Perform dynamic SQL queries to generate administrative reports.
- Analyze relationships between students, courses, and instructors.
File/Folder | Description |
---|---|
/school_data |
Sample data for all five tables (Students, Departments, Courses, Instructors, Enrollments) |
/scripts/create_tables.sql |
SQL script to create all tables |
README.md |
This file |
- FK = Foreign Key
- PK = Primary Key
Column | Type |
---|---|
DepartmentID | INT, PK |
DepartmentName | VARCHAR(100) |
Column | Type |
---|---|
StudentID | INT, PK |
Name | VARCHAR(100) |
Gender | ENUM('Male','Female','Other') |
DOB | DATE |
DepartmentID | INT, FK |
Column | Type |
---|---|
CourseID | INT, PK |
CourseName | VARCHAR(100) |
DepartmentID | INT, FK |
Column | Type |
---|---|
InstructorID | INT, PK |
Name | VARCHAR(100) |
DepartmentID | INT, FK |
CourseID | INT, FK |
Column | Type |
---|---|
EnrollmentID | INT, PK |
StudentID | INT, FK |
CourseID | INT, FK |
EnrollmentDate | DATE |
To be continued..