You are missing a table associating subjects and students (per point 2):
// student [student_id] takes subject [subject_id]
takes(student_id, subject_id)
Notice that each base table has an associated statement template for statements about the business situation, parameterized by column names--its (characteristic) predicate. The rows that make the predicate true go in the table. Notice that the table definition looks like a shorthand for the predicate.
// teacher [id] named [name] with email [email] teaches subject [subject_id]
teacher(id, name, email, subject_id)
// subject [id] named [name] is [description]
subject(id, name, description)
// student [id] named [name] lives at [location])
student(id, name, location)
// batch [id] at venue [venue] was taught by teacher [teacher_id] on date [date]
batch(id, venue, teacher_id, date)
// student-batch [id] reports student [student_id] being in batch [batch_id]
student-batch(id, student_id, batch_id)
// CHECK student [student_id] takes the subject that is taught by the teacher of batch [batch_id]
But the problem is occuring when assigning teacher to batches, in each batch only one particular subject is being taught, and also one teacher can not teach more than one subject.
Since you seem stumped about this I will derive it in terms of how we can reason for table, constraint and query design. One way to express the constraint you want seems to be the commented CHECK above.
To express any table, constraint or query in SQL we first decide on its predicate. Then we can convert the predicate to shorthand. Then we can convert the shorthand to SQL.
Predicate:
student [student_id] takes the subject that is taught by the teacher of batch [batch_id]
Using base table predicates:
FOR SOME k.*, t.*, b.* (
student_id = k.student_id AND batch_id = b.bid
AND student [k.student_id] takes subject [k.subject_id]
AND teacher [t.id] named [t.name] with email [t.email] teaches subject [t.subject_id]
AND batch [b.id] at venue [b.venue] was taught by teacher [b.teacher_id] on date [b.date]
AND [k.subject_id] = [t.subject_id]
AND [t.id] = [b.teacher_id])
Using shorthands:
FOR SOME k.*, t.*, b.* (
student_id = k.student_id AND batch_id = b.bid
AND takes(k.student_id, k.subject_id)
AND teacher(t.id, t.name, t.email, t.subject_id)
AND batch(b.id, b.venue, b.teacher_id, date)
AND k.subject_id = t.subject_id
AND t.id = b.teacher_id)
In a FROM each (possibly implicit) alias represents a table like the given base table name and/or subquery but with each column in its value & predicate renamed to alias.column.
We get the rows satisfying the AND of two predicates by JOINing the predicates' tables in SQL. If we want rows satisfying the AND of a condition then we use ON or WHERE in SQL.
A SELECT clause returns rows where FOR SOME values of dotted columns the returned (undotted) columns are equal to functions of dotted columns that satisfy the FROM predicate.
SQL: Replace statements by their tables, AND by JOIN or ON or WHERE, and outer FOR SOME & THERE EXISTS by SELECT:
SELECT t.student_id AS student_id, b.bid AS batch_id
FROM takes k JOIN teacher t JOIN batch b
WHERE k.subject_id = t.subject_id
AND t.id = b.teacher_id
AND student_id = t.student_id
AND batch_id = b.id
The table of rows satisfying the OR of two predicates is the UNION of their tables. For AND NOT we use EXCEPT (aka MINUS) (or a LEFT JOIN idiom). FOR SOME or THERE EXISTS over all columns can't be queried in SQL, but if we want to know whether there are rows satisfying a predicate then we can use EXISTS around a subquery with that predicate.
Suppose we want to constrain a base table so that every row satisfies a predicate on some columns. Ie FOR ALL columns IF they satisfy the base predicate THEN they satisfy the query predicate. Ie FOR ALL columns IF the row they form is in the base THEN it is in the query. So we require in SQL that NOT EXISTS (SELECT columns FROM base EXCEPT query). Or for each row in the base we require in SQL that EXISTS(query).
In Standard SQL you could CREATE ASSERTION CHECK(NOT EXISTS (SELECT student_id, batch_id FROM student-batch EXCEPT query)) or in a CREATE TABLE student-batch you could CHECK(EXISTS(query)). Unfortunately these are not supported by MySQL or most DBMSs. If you INSERT to student-batch after batch then you can require on trigger that EXISTS(query). Or you could add certain columns & composite FK (foreign key) constraints.
And need to get the information lets say batch's id, teacher's name, students' name for a particular day.
Now we are writing a query. We want rows where:
FOR k.*, t.*, b.*, s.*, sb.* (
batch = b.id AND teacher = t.name AND student = s.name
AND takes(k.student_id, k.subject_id)
AND teacher(t.id, t.name, t.email, t.subject_id)
AND batch(b.id, b.venue, b.teacher_id, b.date)
AND student(s.id, s.name, s.location)
AND student-batch(sb.id, sb.student_id, sb.batch_id)
AND k.subject_id = t.subject_id
AND t.id = b.teacher_id
AND s.id = k.student_id
AND sb.student_id = k.student_id
AND sb.batch_id = b.id
AND @date = b.date)
This looks like the constraint predicate with different return columns and added lines. The SQL is just as directly translated. We add a join with student to get student names. We add a join with student-batch because the constraint doesn't deal with it; the contexts using the constraint query check whether student-batch (student_id, batch_id) subrows are in it.
SELECT b.id AS batch, t.name AS teacher, s.name AS student
FROM takes k JOIN teacher t JOIN batch b JOIN student s JOIN student-batch sb
WHERE ... AND @date = date
You could try an ON version:
SELECT b.id AS Batch, t.name AS Teacher, s.name AS Student
FROM takes k
JOIN teacher t ON k.subject_id = t.subject_id
JOIN batch b ON t.id = b.teacher_id
JOIN ...
WHERE @date = b.date
