Database modeling of a banknote system

Question

I need to create a database for a banknote system, but I’m terrible with data modeling. I did the modeling but I don’t know if it’s correct. The fields are not yet definitive, I just want to know if the relationships are correct.

The specification of the problem is:

In the register of students should be informed the data: name, date birth, sex, address, city, state, social security, RG, telephone and CR. To report the grades you need to have the course data and disciplines. Each course has a name and a code, and can offer several disciplines. For each subject must be informed of the code, name and menu. Each period, a discipline may have several classes associated with it. It is worth remembering that a class belongs exclusively to a single discipline. A class can have several students enrolled and have a maximum number of students. One student may be enrolled in several classes. For each class it is necessary inform the grades of evaluation 1, evaluation 2 and the final average of the students who a make. A class can have more than one teacher, as well as a teacher can teach in more than one class. Some teacher data important, in this context, are: matricula, name, area of training, last qualification and contract status. From this information, create the tables, consider primary and foreign keys and enter information in tables.

I used the Staruml

Answer 1

I will reply as the requirement is quite clear. It is not a good requirement for the real world, but the goal should be to make the model according to the statement.

When doing something try to organize the information in the best way to better understand.

No register of students must be informed the data: name, date of birth, sex, address, city, State, CPF, RG, phone and CR.

So we have a table set there and the columns it should have. Note that it is only one table, your template has created two tables, and therefore unnecessary relationships for this model, it is very simple, create only one table, there is no reason to break into something that the requirement does not ask for. Unless you have a requirement to ask to make an improved model for use in the real world, then there would be a lot of things wrong with that model and it depends on each one’s assessment, nor go for it without much experience or if the goal is to make the teacher to make the criticism and to be able to see where it went wrong, then the mistake would be more important than to deliver right.

To inform the grades it is necessary to have the data of the course and the disciplines.

There are 3 tables defined there, one of them almost implicitly.

Each course has a name and a code, and can offer several disciplines.

Here details one of them and says that the relationship is "one for many", as you did.

Code, name and menu should be provided for each discipline.

Perfectly made.

At each period, a discipline may have several classes associated with it.

Here the requirement gets confused. What period is this? At no time is it spoken of more, so it has already complicated to resolve it properly. Maybe the period is confused with class, so he doesn’t talk about it anymore.

But at least it helps to understand the relationship of two new tables, turma and disciplina. There is also a flaw that says nothing about the class, so I will consider that only the code is appropriate, at least can argue in favor of this decision.

It is worth remembering that a class belongs exclusively to a single discipline.

Here it makes clear that these two tables have a "one to many" relationship, as did.

A class can have several students enrolled and has a maximum number of students. A student can be enrolled in several classes.

Here it shows that these two tables have "many to many" relationship, and did correctly. Some diagrams allow you to tell how many can be many, if the requirement talks about this then you should use a diagram that demonstrates this, something like 1:N.

In fact here the statement proves bad, if he wants this level of detail should give more information. Nowhere indicates what can be null relationship, ie, can be 0..1:N or can only be 1:N. The same goes for other relationships, so in this case I will consider that 0 can never occur, I think it is a good inference, but I can not sink by requirement deficiency at this point.

For each class it is necessary to inform the grades of the evaluation 1, of the evaluation 2 and the final average of the students that compose it.

Here models one of the tables in detail, nothing difficult and did correctly. And it seems to be the central table. It is not wrong to do different, but it is common to put it in the center and the other "orbiting" around it for visualization to become clearer. And here is the most serious error, where is the most important table of the model? And then there’s a complication because each grade relates to a subject and student at the same time. You cannot relate each note directly to the two at the same time, so you need an auxiliary table to tie the two tables together. I can’t say for the exercise, but that’s how you usually fix it. It may be a flaw in the statement, but it may be purposeful to be the main point you would have to think about. So I’ll leave that to improve. It has questions on the subject.

A class can have more than one teacher, just as a teacher can teach in more than one class.

Talk about a new table and how it relates to the aforementioned turma, being "many to many", as he did correctly.

Some important teacher data in this context are: enrollment, name, area of training, last qualification and hiring regime.

Here details one of the tables and did correctly, has no secret.

From this information, create the tables, consider the primary and foreign keys and enter information in the tables

I would like a more detailed model with typing and other information from each column. This is a key part of modeling. But I can’t tell if the exercise requires it, so if it doesn’t compel it’s right, but it’s an incomplete model.

Answer 2

I don’t understand your question. If it’s just to analyze relationships, I’d say it’s in a good way, just change the address table, unblock it into two more, and move some data to the student table. In case one table containing only the states, another with the cities and street and number in the table of users as well as phone, since it will not be a data shared among many or no user.