We have prepared a challenge for you to complete where you will put all you have learnt in previous practicals to the test: Databases Practical 1 Setup, Databases Practical 2 Relationships and Databases Practical 3 Queries.
Setting the Scene ⚽
The 2022 FIFA World Cup is the twenty-second FIFA World Cup, an official football tournament, contested by the male national teams of member associations. Thirty-one national teams qualified for the world cup along with Qatar, the host country of this spectacular event. In the early group stages, all competing members get divided into eight equal groups, labelled from A – H. During the Group phase, each Team will play every other team in the group.
You will design a database system using Microsoft Access to store important data about the tournament. The system will also be used to:
Record the Team Scores at the end of each Fixture.
And which Players scored the Goals.
To succeed in the challenge, you will need to complete three practical tasks and fill in the courswork worksheet as you work.
Your Practical Assignment Starts Here
Open FIFAWorldCupQatar2022.accb in Microsoft Access
The database is available for download so that you can have a local copy on your machine.
Now, open it using Microsoft Access. You should remember how to do this from last time, however, we have copied the instructions hereunder just in case you need more support.
To open Microsoft Access, press the Windows Key and search for "Access".
From the toolbar at the top, navigate to File > Open, and browse to the file you just downloaded. It will most likely be in the Downloads folder.
When you select on FIFA World Cup Qatar 2022.accdb and click on Open you should get the view as depicted by the screenshot on the right.
Task 1 Inputting Data and Creating New Tables
For a warm-up exercise, we thought we could start you off with some straight forward data inputting. Use a double mouse click to open the data of the Groups table and the Teams table. Earlier in the year, FIFA had divided the qualified national teams in 8 groups as seen in the image on the right 👉.
Every national Team has Players. The screenshot on the right is a quick Google search for the national team players of Ecuador that are taking part in the tournament. The following is a screenshot of some information Google are keeping in their records. Here is how a single record is displayed for Enner Valencia.
You can see that his record includes: pictures of him in action, his name, his profession, and some interesting stats related to his performance in the tournament. I am not sure why the name of his spouse is there 🤷♀️.
The most exciting part of the tournament for us is that we can see our favourite players play a match which we formally refer to as a Fixture. A fixture is a set match between two national teams that takes place on a particular date and time. The tournament has many stages and right now, all Fixtures are in the Group Stage but this will change in a few weeks' time. We have included a screenshot of some records as presented by Google. Note that the records seem to be much simpler. At the time this post was being written a match was going on between Senegal ⭐ and the Netherlands 🌷.
Once you have created the tables by using Create > Table Design from the Function Ribbon, you should probably put some data in. This resource might help you add Players and this one can help you add data for the Fixtures taking place this week.
The basic setup is ready and you can start thinking about how to make the database more interesting by adding more data fields in the Table Design View. For instance, it might be a good idea to think about how we can store data about tournament statistics, similar to how Google is doing it!
Task 2 Configure Relationships
In this scenario the relationships are clear and we have to define them in our database using Database Tools > Relationships:
A number of Players are part of one national Team.
Multiple Teams are in a single Group.
In case you forgot which view we are referring to, we have included the following screenshot from Databases Practical 2 Relationships.
Task 3 Querying and Analysing Data
Database Management Systems like Microsoft Access are used most by programmers. We take the trouble of organising the data in this way so that we can efficiently filter out data we need and process it to create new and more useful information for the end-user. Without this aspect, databases would not have much use. Therefore, even though the hard work is over... this aspect of using databases is actually the most important!
However, before getting into Java we first need to design some useful filtering functions in Microsoft Access using their query-by-example feature. We have create a query that asks:
"Which country FIFA members from all teams qualified in the tournament?"
And finally, to close off the coursework we can experience something truly new!
We have created a program in Java that makes use of a library called UCanAccess that is able to connect to a Microsoft Access Database! Watch the video demonstration now.
Comments