Hi

I am designing a mysql database.

I am having trouble with the relationships and the best way to implement the database.

Also I want the database to show the player with the highest goals.

Any help would be nice

so what have you got so far
what entities do you perceive
what relationships do you see
what other outcomes do you need other than

Am I correct with the relationships?


above it's the only outcome I need.

that will probably work for the group stage, but not for later on in a tournament such as the World Cup.
don't see how or where you intend storing the results
don't see how or where you intend storing who was in the team, bearing in minf that each team can take many players, not all of whom play.
don't see who is playing in a specific game
don't see how you identify who scored, and therefore no way to identify the one clear requirement
depending on your assignment there may well be more than one stadium used in any one tournament, although I'm not clear as to why you need to associate stadiums at he tournament levelk

This the ERD I made couple of weeks ago

After all your question I have figured out I need to add more attributed.

Which tables do I need more attribute

its your coursework
after if you have figured out you need more attributes then presumably you have also figured out where those attributes should go
think back to the requirment
does your model support that requirement
if you think it does then I'd suggest you create those entities put some test data in and see if the model adequately supports the requirement

I don't known how to modify the table to achieve the following:

storing players in team
and Identifying who scored the goal

I can't seen to get my head around it.


Its not homework. but problems at the end of the chapter in Database second edition.

In the competition table I will include the group stages , Round of 16, Quarter-finals, Semi-finals and Final.

The group table will hold all the teams in each groups.


I hope this helps

think about what you are asking
storing players in a team...
to me that would suggest 3 tables
a table of players
a table for teams
a table associating players with a team, a so called intersection table

as goals in themselves are a separate event / entity, they require a separate table
so you could identify who scored the goal in that table, presumably you would also want other stuff such as the fixture ID, and possibly also the time the goal occurred

Hi

After keeping mind what you have told me I redesign the erd.

Please check and let me known what you think

your team entity is odd, seems to have a lot of derived data (such as number of..., goal... & points... thats generally frowned on, but can be OK if there are performance reasons to store it that way

examine your own ERD
if you are modelling something like the world cup then the group isn't correct, as its my understanding that after the initial group matches (the mini league format) successful teams then proceed to a knockout format so group is no longer relevant or used.
your goal entity records who scored in a competition, but it doenst' tell you in which match

the entity for competition is either misnamed or incorrect
the competition is, say the world cup, within the world cup competition you have many fixtures.

one thing you can do is work through your entities, put in some tests data and see if it supports your requirements. you don't need to do this in a db, writing down the data with pen and paper is often good enough especially with so few tables


dont' understand the itme 'result'
you could express that as team A score & team b score

most db's use a composite value of date and time... depends on the db that should be datatype date or datetime

thanks for you help,

I have my own ideas of how a football model could be developed, but thats not pertinent here. what is , is how you develop your model.

I think you will learn more from applying some test data with your current design and understand if it will meet your design requirements, and if not where the problems are.

OK ok ok o k

no
I think you will benefit more from the exercise if you work through the design you have

add some test data (if you prefer on paper) and then see if your current design is able to support the specified outcomes.
this process is about you developing your model. if you keep getting someone to validate your attempts the amount you will learn is reduced. even worse you will get their idea of what is required, which may well not be appropriate for your requiremements

most people in the DB community can usually 'see' the tables without having to go through this process, unless its a large system often with many designers all needing to understand the overall model.