Hello. I sat down to deal with the structure of the database "students". It all started with an incomprehensible job from where it was taken without input for the database (the teacher said that it was easy to understand what to do and how to do it). The first thing that confused me was which structure to choose for the following points:

  • find the average grade for each student;
  • find the maximum grade for each discipline;
  • find the average grade given by each teacher;
  • withdraw the minimum grade given by each teacher;
  • transfer each grade to a rating score (score, greater than 3 points, doubles);
  • count the number of different groups;
  • count the number of different apartments;
  • display the average grade, maximum mark, minimum mark for student with code 3;
  • count the number of good grades;
  • calculate the percentage of twos for each teacher;
  • display the number of estimates for which the condition is satisfied; "Score * 2 + 1> 10".

It became interesting from the very beginning: guess about the input data itself + tasks, apparently, were translated into a translator ("number of apartments").

I decided to create a database of the following structure:

  1. There will be exactly 5 tables: "students", "groups", "teachers", "disciplines", "marks";
  2. "Student - Group" links - "many to one"; "teacher - assessment" - "one to many"; "student - discipline" - "many to many"; the table "marks" connects students and disciplines, teachers and disciplines (a teacher can lead several disciplines, and one discipline can be taught by several teachers), one and the same teacher can lead to several students (students of different groups);

Here begins the most interesting. I deal with MySQL not for long (1 lab on the operator "SELECT"). There were two tables in the database for one-to-many communication - everything is clear. First, how to choose the right entities for connections, then - I know that it is logical to choose "marks", "groups" with "students", "students" to link to "discipline" with a coherent table, but, in principle, you can create a connection and between the entities "teachers" and "disciplines directly as" disciplines "with" groups "and in general. Another question related to how I relate the existing structure to the audience, or rather to the existing structure, add an audience (apartment)), because if you create a table "auditorium" and associate with the assessment of the assessment, for example, in the table "marks" there will be a lot of unnecessary links, and this do not.

Please give an answer to the question with the correct choice of entities, and about which table should be entered and with which fields to create it, with which to link. Understand me, I'm not lazy and not stupid, although not for me to judge. The lecturer conducting labs himself works only after graduation, in general, you yourself understand that you have to figure it out yourself, and when the task is incomplete, which is what it is all about. Below I cite the structure that has been developed so far: enter image description here

1 answer 1

I would solve such a (small, foreseeable, without the requirement of further expansion) task from the reverse - to write each request according to the task, and on the basis of them, entities and connections necessary and sufficient for the execution of requests to form.

find the average grade for each student

SELECT studentid, studentname, AVG(markvalue) avgmark FROM students JOIN marks ON students.studentid = marks.markid

find the maximum grade for each discipline;

SELECT subjectid, subjectname, MAX(markvalue) maxmark FROM subjects JOIN marks ON subjects.subjectid= marks.subjectid

find the average grade given by each teacher

SELECT teacherid, teachername, AVG(markvalue) avgmark FROM teachers JOIN marks ON teachers.teacherid= marks.teacherid

summarize these three queries. We need tables of students and teachers with fields for names and primary surrogate key. You need a table of subjects with fields for the name of the discipline and the primary surrogate key. We also need a table of marks with fields for foreign keys on the table students and teachers and subjects, for the evaluation value, for the primary surrogate key.

And so on ...

  • thanks for the advice, I will try - Muscled Boy
  • faced with a new question, let's say each item can be conducted in different audiences (for example, on different days or even on the same), there is a list of audiences and disciplines. We link them through the table "lesson" (idon) by audience id and item id. Moreover, each lesson has its own date. And in the table "mark" is the date of evaluation. Agree that some sort of nonsense is coming out, since “assessments” are connected with the “subject”, which in turn are connected through engagement with the audience. And it turns out that we are looking for an audience in which the rating was set by date from the "lessons" ??????? - Muscled Boy
  • You said to create a table for the task, but there it is not indicated which ones to choose different audiences: different from those in which they were exhibited (so logical, if you just take them from the list of groups, they will all be unique) or different ratings, or different in general? ??? - Muscled Boy
  • Please answer I really want to understand - Muscled Boy
  • It is enough to simply bind the assessment to the audience through a foreign key, for example, the "auditorium" table (id.auditorium), is it ???? But here a new question arises, are there many links in the "marks" table, I was told that a lot of links in one table are fraught with consequences. What would you do in this situation, what should I do? - Muscled Boy