Unanswered: New database design for my new project
Please can any one review my design before going for coding.
----Requirement for DB------
1.student and class relation : single student can enroll in a single class at a time, a class can have many students
2.class and exam relation : single class can have many exams(examid is formed based on the day of the exam) in a year, the same examid(nothing but a
date) that means same examid can be for more than one class.
3.The exam can have many subjects, and a subject can have many exams(i.e examid's).
4.Every student can have a marks in given subject, in given exam,in given class.
5.student and parent relation : single student can have many parents and single parent can have many students
6.subject and teacher relation: single teacher can teach many subjects and single subject can be tought by many teachers.
1.create a student(studentID,classID,sname...etc) table with studentID as Pkey,classID as Fkey
create a class(classID,...etc) table with classID as Pkey
2,3.create a class(classID,...etc) table with classID as Pkey
create a exam(examID,...etc) table with examID as Pkey
create a subject(subjectID,...etc) table with subjectID as Pkey
create a class_exam_subject(class_exam_subjectID,classID,
examID,subjectID) table with class_exam_subjectID as pkey,classID,examID,subjectID as Fkey
4.create a marks(studentID,class_exam_subjectID,marks...etc) table with studentID as Pkey
5.create a parent(parentID,pname.......etc) table with parentID as Pkey
create a student_parent(parentID,studentID) table with studentID,parentID as Fkeys
6.create a teacher(teacherID,...etc) table with teacherID as Pkey
create a class_subject_teacher(class_subject_teacherID,clas sID,
subjectID,teacherID) table with class_subject_teacherID as Pkey,classID,subjectID,teacherID as Fkey
Thanks for your quick replay, I've coded the tables yet, but i've given the requirements and pseudo code table creation above, I just want to be sure whether the no. of tables and the relations shown above are accurate or whether i need to do any modifications in relations.