Database design a simple database for a functional school

Question

So i am having a little trouble in coming up with the entity-relationships for my database however i have some of the design process done be that of minimum caliber. The database will be created so that students can have many courses many to many relationship (obvious i know). The database will need to keep track of homework and attendence daily. However, classes can be just one day of the week or many days.

advisors->(advisorid, firstname, lastname, phone , email)

students->(studentid,firstname, lastname, phone, email)

courses->(courseid, description, startdate, statetime, room)

studentscourses->(studentid, courseid)

---

Here i am stuck, i am thinking of creating a calendar table, but how will i correlate the data to the homework table and with attendance. If any suggestions that would be great criticism is welcome.

Answer 1

You need to create some more entities to achieve an efficient database design. One solution would be:

Attendance(id,student_id,course_id,date)

Homework(id, course_offering_id, date) -- course_offering_id: primary key of student courses

-- since many to many relation lies between homework and student
Homework_Student(id, homework_id, student_id)

Answer 2

U can expand the studentscourses table to add more columns like

Studentcourses->(studentid,courseid,attendence(bool),datetime,homework(text),previoushomework(bool))

attendence will be true if the student is present on that particular date and time at which the course is scheduled.

datetime-> the date and time of the course

homework-> this shows the homework that student gets

previoushomework-> this will be true when the student has completed the homework given the previous time

This is the simplest and most compact table design u can have with this project and you can also make other relational tables according to your needs

Hope that might solve your problem