designing database for simple school management system

Question

I am designing a database for School management System.

My tables are:

1. students (student_id, student_name)

2. teachers ( teacher_id,  teacher_name)

3. courses  ( course_id,  course_name)

here the relation b/n students and courses is many-to-many i.e. one student can enroll to many courses and one course can be enrolled to many students.

and relation b/n teachers and courses is also many-to-many i.e. one teacher can teach  many courses and one course can be taught by many teachers.

My query is to show:

1. show the list of enrolled course by a student

2.show the list of courses taught by a teacher

3. show the list of students enrolled for a particular course

4. show the list of teachers assigned for a particular course

how to design a database for such query? What can be the possible entities for the above tables?

Answer 1

I think in answer all your question you need a junction table contains  info  as below

CREATE TABLE details (StudentID INT ,CourseID INT , TeacherID INT)

 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

I think in answer all your question you need a junction table contains  info  as below

CREATE TABLE details (StudentID INT ,CourseID INT , TeacherID INT)

 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

If this is a management system, the relations mentioned need to be recorded.

First, a TABLE for the instantiation of the courses into actual clasess, recording information such as teacher, time slot and related information:

Course_Class
----------------
Id (PK)
Course (FK courses)
Teacher (FK teachers)
UNIQUE(Course, Teacher)

The PK is an Id as a teacher can change mid-term. The UNIQUE CONSTRAINT will bar duplicate entries.

Each student is then enrolled in a Course_Class:

Student_Course_Class
--------------------------
Student (PK)(FK students)
Course_Class (PK)(FK Course_Class)

 

Answer 4

Your current design is extremely simplistic.  If this is a simple academic exercise and you don't really need to handle the real-life complications, you should state so.

In expend on Brian's comments, I will point out that you have forgotten a critical element to your design.  Namely - time ... and in 2 dimensions.  A course is scheduled to be taught for a specific calendar period.  An example would be Fall semester, 2011.  The specifics of the period can vary from school to school, location to location.  Another factor to consider is that a course might be offered for a specific period but yet might not be actually "taught" (for lack of a better term); the offer to teach is usually subject to a minimum number of students. 

The second time dimension is typically related to time of weekday.  That is, a course is taught during Period 1 - 8:00 am to 8:55am on Monday, Wednesday, and Friday. 

Given sufficient demand, you might have several teachers teaching the same course during the same period - as you might have the same teacher teaching the same course at different times

Often, there are relationships between courses.  For example, you cannot enroll in course 5 until you have successfully completed course 4.

A student may take the same course multiple times.  Failure to complete (or complete successfully) does happen.

A student can withdraw from school, can change schools, and enroll late in the calendar period.  There is critical information about the student missing from your design.

A school management system must handling grading, pass/fail, etc. 

Lastly, let us not forget that courses tend to need resources - that is, an actual classroom.  There are physical attributes about a given classroom that are important to know when assigning that room for use in a particular course. 

Answer 5

In the real world, students do courses and get results. You will need to record these, too.

Answer 6

I am designing a database for School management System.

My tables are:

1. students (student_id, student_name)

2. teachers ( teacher_id,  teacher_name)

3. courses  ( course_id,  course_name)

here the relation b/n students and courses is many-to-many i.e. one student can enroll to many courses and one course can be enrolled to many students.

and relation b/n teachers and courses is also many-to-many i.e. one teacher can teach  many courses and one course can be taught by many teachers.

My query is to show:

1. show the list of enrolled course by a student

2.show the list of courses taught by a teacher

3. show the list of students enrolled for a particular course

4. show the list of teachers assigned for a particular course

how to design a database for such query? What can be the possible entities for the above tables?

Answer 7

if u r making database abt school management system then these three entities aren't enough.

what about these entities.....

class, exam, result , payroll, sports, other staff etc etc