MySQL Forums
Forum List  »  Newbie

Re: Help needed!
Posted by: Phillip Ward
Date: June 22, 2023 01:50AM

Not a pretty picture (we prefer plain text around here), but this should get you pretty close to what you need:

Each Entity (thing that can exist on its own) has its own Table - Group, Student, Teacher, Lesson, Experiment, Skill.


a STUDENT can only be in 1 GROUP but a GROUP can contain many STUDENTs

OK, so Group is an attribute of Student.

- ID

- Group_ID


a TEACHER can teach many GROUPs and a GROUP can have many TEACHERs

That's a "weak" Entity - entries in it cannot exist without matching entries in the other two.

- ID 

- Teacher_ID
- Group_ID


a TEACHER can teach many LESSONs but a LESSON can only have 1 TEACHER
a LESSON can only contain 1 EXPERIMENT but an EXPERIMENT can appear in many LESSONs

So both Teacher and Experiment are attributes of Lesson - no "weak" entities required here.

- ID 

- ID
- Teacher_ID
- Experiment_ID


a STUDENT can attend many LESSONs and a LESSON can contain many STUDENTs

Another "weak" Entity.

- Lesson_ID
- Student_ID


an EXPERIMENT can have many SKILLs and SKILLs can appear in many LESSONs

Experiments and Skills, OK.
Skills and Lessons, No. Not directly, anyway.

A Skill is part of an Experiment and that Experiment is part of a Lesson.
To "relate" a Skill to the Lessons(s) it appears in, you have to "travel through" the intervening Entities; Skill -> Experiment-Skill -> Lesson (by Experiment_ID).

- ID 

- Experiment_ID
- Skill_ID

This is the Complexity - and the Power - of Relational Databases.

Regards, Phill W.

Options: ReplyQuote

Written By
June 20, 2023 01:02PM
June 20, 2023 01:19PM
June 21, 2023 12:31AM
Re: Help needed!
June 22, 2023 01:50AM
June 23, 2023 12:22AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.