Hello. Please help in creating a database. The bottom line is this: there will be a small script where you can find out the grades for the subjects (simple, it will not be used, such a task :)) Each has his own record number, when entering grades from the drop-down menu (select), the correct scoresheet student is selected and the grade number is entered. But each student has a lot of grades (i.e. there are several grades for one record book). Approximately presented it this way (MS SQL).

Table students:

create table students ( stud_id bigint primary key, stud_name char(200), stud_fam char(254) );

Table with estimates

 CREATE TABLE ocenki ( stud_id bigint foreign key references students(stud_id), predmet char(254), ocenka integer check (ocenka in (0,1,2,3,4,5)) default(0) );

What will the code look like in MySQL?

    2 answers 2

    In general, SQL is the same for all subd, in this case the query is correct for Mysql as well (of course, you must first select the database :))

    I do not understand another ...

    Bigint is a large integer. The range with the sign of -9223372036854775808 to 9223372036854775807, is there really SO students? : D

    Just int

    • Thank you :) But he will allow the STUD_ID value to be repeated in the table with the estimates? Those. ------ Stud_id = 1 Predmet = matan ocenka = 5 ------ Stud_id = 1 Predmet = fizika ocenka = 4 And so on. In the end it should be: image.kz/v3d8ca13ff473ce5dd95d096580f34cc1 ( screenshot ) - boztaev
    • Make a separate int primary key for the students table, and just enter stud_id at index. And in general, as far as I understand the task - in the students table you need to add a field for the record number (of course, the uniqe key), and you need to link the rating table with the record number and not with the student id. PS: do not waste your respect points, just vote for. - Zowie
    • Well) it is assumed that Stud_id will be a record number :) It does not repeat and is perfect :) it will be driven in by the search. I enter 081028 and it prints estimates select predmet, ocenka from ocenki where stud_id = '$ value'
    • The student and the record-book are different entities .... You get porridge in the table ... The student ID is actually the record ID, so it’s wrong to do this (although many experienced specialists create this mess), of course, from the point of view of the amount of stored data That's right, this is already a "high" argument, you can do it the way you wanted, but rename it somehow stud_id so that it is clear that this is a record number and not a student id. And bigint has nothing (I hope you already understood this :)) - Zowie
    • yes) thanks to bigint)) - boztaev

    I would recommend to have a separate table for items, and to put id_subject in the Estimates. In addition, for the same subject put several ratings in different semesters. So what else would you need a semester number? student id + semester + item_-jointly-unique fields.