Advanced level 2024 CASPA mock computer science 3
Advanced level 2024 CASPA mock computer science 3
TASK 1: DATABASE (20 Marks)
The database of a certain football association is structured as shown by the tables below
Player( PlayerID*, ClubID,Name, Position, MatchPlayed, Goals)
Fields Datatype size
PlayerID(primary key) Text 6 (Example 21A001)
ClubID Text 10 ( Example BARCA001)
Name Text 30
Position Number Short integer
MatchPlayed Number Long integer
Goals Number Long integer
Club( ClubID*, CoachID, Country, NoTitles, YearCreation)
Fields Datatype size
ClubID(primary key) Text 10 (Example BARCA001)
CoachID Text 10 ( Example CHEL001)
Country Text 30
NoTitles Number Short integer
YearCreation Date Day/Month/Year
Coach(CoachID*, ClubID, Name, Titles, Nationality, Age)
Fields Datatype size
CoachID(primary key) Text 10 (Example CHEL001)
ClubID Text 10 ( Example BARCA001)
Name Text 30
Titles Number Short integer
Nationality Text 20
Age Number Short Integer
In any DBMS of your choice help the football association to implement this database while performing the
following actions:
1. Create the above tables using your DBMS, write the corresponding SQL codes for each table in your
answer booklet. (5 marks)
2. Create a Relationship diagram involving the three tables, do a screen capture and paste in a Word
document called relationship. (3 marks)
3. Populate your tables to have five records each with information of your choice. (3 marks)
4. Write a query in SQL in your answer booklet that displays the names of players and their coaches
who scored above 100 goals and played less than 80 matches. (5 marks)
5. Create a form on the player table and save it as PlayerInput. (2 marks)
6. Create a report called highestgoal on the query written on question 4 above (2 marks)
