Bilkent University
Department of Computer Engineering
CS 353
Database Management Systems Project
Final Report
Football Database System
14.05.2018
Section 1 / Group 10
Shamil Ibrahimov 21300322
Mehmet Eren Turanboy 21302586
Solehjon Ruziboev 21500283
Demir Topaktaş 21501635
Course Instructor
Ercument Cicek
1
Table of contents
1. Project Description………………………………………………………………………..3
2. Contribution of Group Members………………………………………………………..4
3. Final E/R Diagram………………………………………………………………………….5
4. Relational Schemas……………………………………………………………………….6
4.1 Admin………………………………………………………………………………………..6
4.2 Player………………………………………………………………………………………...6
4.3 Director………………………………………………………………………………………6
4.4 Coach………………………………………………………………………………………...6
4.5 Fan…………………………………………………………………………………………….7
4.6 Agent………………………………………………………………………………………….7
4.7 Club………………………………………………………………………………………......7
4.8 League………………………………………………………………………………………..7
4.9 Game………………………………………………………………………………………….7
4.10 Stats…………………………………………………………………………………………8
4.11 League_Game……………………………………………………………………………..8
4.12 League_Club……………………………………………………………………………....8
4.13 Plays………………………………………………………………………………………...8
4.14 Subscribe…………………………………………………………………………………..9
4.15 Transfer_Offer……………………………………………………………………………..9
4.16 Contract………………………………………………………………………………….....9
4.17 Country……………………………………………………………………………………..9
5. Implementation Details…………………………………………………………………..10
6. Advanced Database Structures………………………………………………………...11
6.1.1 Views……………………………………………………………………………………...11
6.1.2 Recent Matches View…………………………………………………………………..11
6.1.3 Subscribed Clubs Transfers View…………………………………………………...11
6.1.4 Ongoing Transfers View……………………………………………………………....12
6.1.5 Ongoing Contract Extensions View………………………………………………...12
6.2 Reports……………………………………………………………………………………..13
6.2.1 Top Scoring Players…………………………………………………………………...13
6.2.2 Top Fouling
Players…………………………………………………………………….13
6.2.3 Most Expensive Transfers…………………………………………………………….13
7. User’s Manual……………………………………………………………………………….14
7.1 Login Page…………………………………………………………………………………14
7.2 Guest Page………………………………………………………………………………...14
7.3 Agent Page………………………………………………………………………………...15
7.4 Coach Page………………………………………………………………………………..16
7.5 Player Page ……………………………………………………………………………….16
7.6 Admin Pages……………………………………………………………………………...17
7.7 Fan Pages………………………………………………………………………………….22
7.8 Director Pages…………………………………………………………………………….27
2
1. Project Description
Football Management System is a web application designed to maintain the information
around the football world and also to provide football club directors and agents to make
various types of transactions. The application has several features which according to user
can or can’t be accessed. There are some common features of the website which can be
accessed by everyone and some by only specific users.
There are totally seven type of users, guest, fan, agent, coach, player, admin and director.
All users except guests need to login to the system in order to use it. All users can view
information about the clubs, transfer news which shows only completed transfers, players,
and matches which displays the home and away clubs with the number of goals scored
each. On top of that each user can use the search engine which given a search text
searches for clubs, players, coaches and displays the information table like structure.
The guest user can only read the information and cannot make any actions in the website.
These users don’t have any special features.
The fan once logged in to the system goes to the homepage where favorite team’s
information is shown. Fans have three additional features, subscriptions, countries, leagues.
In subscriptions page the fan user can view the clubs to which he/she is subscribed to. In
countries page, the list of countries where the subscribed teams are playing listed. The
league page lists all leagues of subscribed clubs. Similarly, all other common features are
customized according to fans subscriptions. Fan can also subscribe to and unsubscribe from
clubs.
The agent’s homepage lists all the clients. Also agent users have two additional features,
manage transfers and manage contracts. In these sections agents can accept or reject
transfer offers or contract extension requests. Agents are also capable of creating new
contract extension requests for their clients.
The coaches homepage shows the informations about him and his club. Coaches have
additional feature, players transfers, where the completed transfers of his players are listed.
The player user views information about himself in the homepage. Similar to coach users it
can view players transfers, all the completed transfers of his previous and future teammates.
The admin user can create leagues, all type of users, clubs and games. In each of these
windows the admin fills out all the required information and creates a tuple in database.
Admin also has countries and leagues features which simply list all countries and leagues.
The director’s homepage displays information about his team. Similar to agent users
directors have features manage transfers and manage contract where they can accept or
reject transfer offers and contract extension requests. Directors can also request transfer
offers and contract extensions for a specific player.
3
2. Contributions of Group Members
● Solehjon Ruziboev
Completed Fan, Director and Agent user pages and their features. Took part in
developing a database and search engine. Conducted a test and debug of final
version of the project.
● Demit Topaktas
Completed Admin, Player and Coach user pages and their features. Took part in
developing a database. Conducted a test and debug of final version of the project.
● Shamil Ibrahimov
Completed Guest user page and their features. Also developed a search engine for
all users. Took part in developing a database and filling the initial data. Conducted a
test and debug of final version of the project.
● Mehmet Eren Turanboy
Designed the UI of all the pages. Took part in filling the initial data of the database.
Conducted a test and debug of final version of the project.
4
3. Final E/R Diagram
5
4. Relation Schemas
4.1 Admin
Relational Model
Admin(username,password)
4.2 Player
Relational Model
layer (ID, username,password, name,surname,age, salary,nationality, position,birthdate,
P
agent_ID)
FOREIGN KEY: agent_ID REFERENCES Agent(ID)
4.3 Director
Relational Model
irector (ID, username,password, name,surname,age, salary,nationality,birthdate,
D
club_ID)
FOREIGN KEY: club_ID references Club(ID)
4.4 Coach
Relational Model
Coach (ID, username,password, name,surname,age, salary,nationality,birthdate,
AgentID,ClubID)
FOREIGN KEY: AgentID REFERENCES Agent(ID)
FOREIGN KEY: ClubID REFERENCES Club(ID)
6
4.5 Fan
Relational Model
Fan (ID, username,password, name,surname,favTeamID)
FOREIGN KEY: favTeamID references Club(ID)
4.6 Agent
Relational Model
Agent (ID, username,password, name,surname,age, salary,nationality,birthdate)
4.7 Club
Relational Model
Club(ID,name,transfer_budget,annual_wage_budget,city,establishment_date,value,stadium)
4.8 League
Relational Model
League(ID,name,start_date,end_date,countryName)
4.9 Game
Relational Model
Game(ID,start_time,end_time,stadium,game_date,home_teamID,away_teamID)
FOREIGN KEY: home_teamID REFERENCES Club(ID)
FOREIGN KEY: away_teamID REFERENCES Club(ID)
7
4.10 Stats
Relational Model
Stats(time,action,type,gameID,playerID)
FOREIGN KEY: gameID REFERENCES Game(ID)
FOREIGN KEY: playerID REFERENCES Player(ID)
4.11 League_Game
Relational Model
League_Game (leagueID,gameID)
FOREIGN KEY: leagueID REFERENCES League(ID)
FOREIGN KEY: gameID REFERENCES Game(ID)
4.12 League_Club
Relational Model
League_Club (leagueID, clubID)
FOREIGN KEY: leagueID REFERENCES League(ID)
FOREIGN KEY: clubID REFERENCES Club(ID)
4.13 Plays
Relational Model
Plays (clubID,playerID,startDate,endDate)
FOREIGN KEY: clubID REFERENCES Club(ID)
FOREIGN KEY: playerID REFERENCES Player(ID)
8
4.14 Subscribe
Relational Model
Subscribe(fanID,clubID)
FOREIGN KEY: fanID REFERENCES Fan(ID)
FOREIGN KEY: clubID REFERENCES Club(ID)
4.15 Transfer_Offer
Relational Model
Transfer_Offer(ID,price,transferDate,status,playerID,fromDirectorID,toDirectorID)
FOREIGN KEY: playerID REFERENCES Player(ID)
FOREIGN KEY: fromDirectorID REFERENCES Director(ID)
FOREIGN KEY: toDirectorID REFERENCES Director(ID)
4.16 Contract
Relational Model
Contract (playerID,directorID,agentID,bonus,expirationDate,status)
FOREIGN KEY: playerID REFERENCES Player(ID)
FOREIGN KEY: directorID REFERENCES Director(ID)
FOREIGN KEY: agentID REFERENCES Agent(ID)
4.17 Country
Relational Model
Country(name)
9
5. Implementation Details
The project is mainly composed of three parts database, backend and frontend.
We have used MariaDB relational database in our project. In order to create the tables and
dependencies we used Java. The initial data was also added using Java and it was filled
manually.
The backend part of the project is mainly consisted of PHP code. In order to operate
between pages we used global arrays which PHP provides, such as session or post. We
have used arrays in order to pass the current user type and the details of that user types so
that we can show relevant information in each of the pages. As mentioned before some
pages are shared between users thus we needed to know which user is logged in.
The error handling part of the backend was done primary using Javascript alerts. Everytime,
the user types wrong password, director tries to create wrong transfer offer or any other
incorrect action from users is stopped, and users are notified through Javascript alerts.
The frontend of the project and the User Interface was designed using bootspring library. It
has built in designs and styles of various forms which was very convenient to use.
During the implementation phase we have faced a lot of issues. One of the most challenging
issues was making a transfer market work. The problem was in number of participants in this
action, to be exact three, two directors and an agent. After a long hours of discussion we
came up with a conclusion to use states as a way of understanding the current status of
transfer offers. Another issue we have faces was caused by global variable post of PHP. The
issue was that post once some of its values are set were literally impossible (we tried
everything) to reset which were causing some queries to re-run and modify the data in a
wrong way. We have solved this problem by modifying the query such that it runs only when
needed and fortunately we got over it.
6. Advanced Database Structures
6.1 Views
We have not created specifically view structures, however we have achieved a way of
creating a view in other way.
10
6.1.1 Recent Matches View
6.1.2 Subscribed Clubs Transfers View
6.1.3 Financial Detail View
11
6.1.4 Ongoing Transfers View
6.1.5 Ongoing Contract Extensions View
12
6.2 Reports
6.2.1 Top Scoring Players
6.2.2 Top Fouling Players
6.2.3 Most Expensive Transfers
13
7. User’s Manual
7.1 Login Page
User can Log In as a Fan, Agent, Director, Coach, Player or Admin based on their already
ready accounts. However, if User does not have any account he/she can use the system as
a Guest.
7.2 Guest Page
14
Guest can see the matches, clubs, transfer news and players. Besides, guest user can
search for the specific club, player or coach. Also, clubs, transfer news, matches and
players can be viewed by all the users.
7.3 Agent Page
As a Home Page, Agent sees his clients when he/she enters the system.
15
To be able to make a completed transfer agent has to confirm with player’s director. Manage
Transfers page is for the agent’s clients request. He can accept or reject the request. Also
he can see the completed transfers.
Manage Contracts page of the agent shows the requests from to director for a specific player
to extend the contract of that player with the team. Again, he can accept or reject the
requests.
In manage contract page, Agent can send a contract extension request for his players to
directors. In this case directors can accept or reject the request.
7.4 Coach Page
When the user enter as a coach he can see his team and its information. In Players Transfer
section coach can see his players transfer news.
16
7.5 Player Page
In player page every information regard to him is shown such as Name, Surname, Age and
etc. He can also see his team mates transfer news and matches and so on.
7.6 Admin Pages
Admin can look for countries,leagues,clubs,transfer news,matches and players by clicking
menu items and can search for anything in search section. Admin can also log out from
his/her account. This functionality is common for all following admin pages.
17
Admin Countries Page
Admin can see all countries.
Admin Leagues Page
Admin can see all leagues.
Admin Clubs Page
18
Admin can see all clubs.
Admin Transfer News Page
Admin can see all transfer news in this page.
19
Admin Matches Page
Admin Players Page
Admin can see all players in this page.
20
Admin Create League Page
Admin can create league in this page. He/she can input name of the league, start date of
the league and end date. Moreover, he/she can specify country of the league.
Admin Create Club Page
21
Admin can create club in this page. He/she has to input name,transfer budget,annual wage
budget,stadium, value, league,city and establishment date of the club in order to create club.
Admin Create Account Page
Admin can create account(player,director,agent,coach) in this page. He/she has to
input name,surname,age,salary,nationality,birthdate and password of the account.
He/she can specify the type of the account(coach,player,director,agent). For
example, if type is player he/she can specify club,agent and position of the player
account.
Admin Create Game Page
dmin can create game in this page. He/she has to input league of the game,start
A
time, end time,stadium,date,home club,away club and stats in order to create a
game. In stats, he/she can specify time,action,type and player. Action must be
number; 0 means goal and 1 means card. Type means card’s type; 0 means
22
red,etc.. Admin must be input name of the player such as Cristiano in player section.
Admin can also add more stats to the game.
7.7 Fan Pages
Fan Main Page
In fan main page, fan can see his/her favorite team details. Fan can see
coach,stadium,city,establishment date and league of his/her favorite team. Fan can
also recent matches and players of his/her favorite team.
Fan Clubs Page
23
In clubs page, fan can see all clubs details such as name,city and value. He/she can
subscribe to other clubs in this page.
Fan Subscriptions Page
Fan can see his/her subscriptions in this page.
Fan Countries Page
24
Fan can see countries of his/her subscribed teams in this page.
Fan Leagues Page
Fan can see leagues of his/her subscribed clubs and favorite club.
Fan Matches Page
25
Fan can see matches of his/her subscribed clubs and favorite club.
Fan Transfer News Page
Fan can see transfers of his/her subscribed clubs and favorite club.
Fan Players Page
26
Fan can see his/her subscribed clubs players and also players of his/her favorite team.
Fan Settings Page
Fan can edit his/her profile in this section. He/she can specify his/her new name,new
surname,new username,password and favorite team.
7.8 Directors Pages
Director Main Page
27
Director can see his/her club details. He can see coach,stadium,city,establishment date
and leagues of his/her club. Moreover, he can see recent matches and financial details of
the club in this page.
Director Transfer Offers Page
In manage transfers page, director can see his/her players transfer offers from other
directors. Here, status shows that agent of Lionel Messi accepted the offer. And director can
accept the offer by clicking buttons in action section.
Director Transfer Requests Page
28
Director can see his/her requests to other players in clubs. Director can see status and
price of requests. Moreover, director can cancel requests.
Director Create Transfer Offer Page
Director can create transfer offer to other players in this page. He/she has to specify
name,surname and offered value in order to create transfer offer.
Director Manage Contracts Page
Director can see contracts in this page. Director can cancel his/her request for extending
contract.
29
Director Extend Contracts Page
Director can extend contract of his/her player in this page. Director has to specify name of
player,surname,bonus and expiration date of the extended contract.
30
