-1

I'm working with the Relational model so I don't really have sql code. I know that you can't directly assign multiple values to an attribute and the references need to be done on a new table, I am having a hard time understanding what values the new table needs to have in order to reference the other tables. For example I am working with a school database and I have the tables

   student
   teacher 
   classroom

I would like to assign multiple students to the classroom and create groups, so when I define the new table that creates the relationship between students and classroom, what attributes does it need to have? Only the foreign keys?

Any resources you can point me to are highly appreciated.

marti
  • 1
  • 1
  • It seems you are asking about `link tables` used to facilitate implementation of `many-to-many` relationships. See https://stackoverflow.com/questions/14978244/sql-two-tables-and-creating-a-link-table – PM 77-1 Dec 25 '21 at 23:46

1 Answers1

0

what attributes does it need to have? Only the foreign keys?

Well, that's up to you really. A thing you could add to such a relation is the time they entered the classroom, for example. Because it is directly related to this relation.

But if you don't have any additional attributes that are related to students and classrooms, it's fine if your new table ends up being only:

NewTable(_student_, room)
  student -> Students.id
  room    -> Classrooms.id

This would allow many students to be in the same room, but a student will not be able to be in multiple rooms at the same time.

The "groups" however, you need to elaborate on further. Depending on whether you must be in a group, or if it's optional. Can we be in different rooms but still in the same group? Can I be in multiple groups?

What I recommend you do, is to make a full overview of all entities and attributes (from the existing tables, but also the new ones you want to create). Then you need to establish all behaviors and properties, like: "Do students have to be in a group?". Furthermore, how are teachers connected to these classrooms? Is a teacher related to one classroom? And if so, are they related to all groups within that classroom?

So, unless you have established all of that, your question can't be answered, as there's too much speculation of what your domain and requirements are.

I would recommend doing a simple ER-diagram of your entities and attributes, and their relations.

  • Entities: Objects, things that can have properties. Examples: Students, Classrooms, Teachers.
  • Attributes: Info related to an individual entity (or a relation). Examples: names, age, seats.
  • Relations: Combinations of entities. Examples: Multiple students can be in a group, a teacher teaches a class.
  • Behaviors: Limitations and constraints. Examples: A student can only be in 1 room at a time, a classroom can be used by multiple classes but not at the same time.

Only at that point can we say "what attributes it needs to have".

So, give that a shot before asking here, because it is entirely dependent on that information, and you are the only one with that info right now. And I would say if any of these things is something you haven't decided on, that's what you must decide on long before writing SQL.

Best regards! ^_^

Chrimle
  • 490
  • 1
  • 4
  • 10
  • Hey! Thank you so much, sorry that my question was lacking... and you took your time to answer regardless, you did clarify part of my doubt but I agree a lot more more needs to be done. I really appreciate your time. – marti Dec 26 '21 at 02:40
  • Any time! And if you're not familiar with ER diagrams and FD's and such, it's still really important for you to ask those questions about how your system should behave. Either you come up with them yourself, or you ask the ones who asked for the database. I wish you best of luck! :) – Chrimle Dec 26 '21 at 02:44