Introduction
Organisation has realised the effectiveness of database management for data management. Typically, management of data is essential for management decision, and effectiveness of management decision is fundamental to improvement of business organisation.
For the enhancement of data management, a local amateur football club needs database program to keep personal data of its member in order to have the record of attendance of the club member, best club persons, and players.
The main objective of this paper is to design a database program for a local amateur football club.
A local amateur football club requires database for information storage for its players, administrators, coaches, and essentially to record attendance of its members at various social, and official functions. It should be noted that administration of a local amateur club wants to ensure effective record keeping because of its decision to offer life membership to players who play 250 games for over 10 years.
The rest of the paper is structured as follows:
The paper provides Entity Relationship diagram, which reveals visual presentation of the data in the electronic resources.
The paper also provides a relational model for database.
Additionally, the paper will use SQL script to create tables in the database.
Finally, the paper will provide five-business question, use SQL queries to answer them.
Entity Relationship diagram
This is a diagram that provides visual relationship of record keeping of players, social, administrators, and coaches. It should be noted that decision of club administration for the need of database is also to record attendance at various social and official functions, such as: the Annual General Meeting, Monthly committee meetings, Christmas party, casino night, selection meeting, spring carnival eve party, etc.
Moreover, the committee also wants to keep the records of all life members, best club persons, a list of a given player, and the roles they have played for the club.
Thus, ER diagram illustrates how database will record all members for effective record keeping.
Fig 1 reveals that an entity-relationship diagram (ERD) can be used to manage electronic resources for a system to manage electronic resources that the administration of an amateur football club wishes to use for its database management. Fig 1 shows the relationship of how data will manage between entities. ERD represents database table where records of specific entity is relation to each other. (Cornell University).
It is revealed from the diagram that all records of all members will stored in the database and retrieved as may be necessary by an administration in order ensure that all the attendance of all members are kept in the database.
While ER diagram reveals diagrammatic illustration of database of the club, relational model provides detail analysis of structure of database.
Relational Model
The relational model uses basic concepts to reveal basic concept in the database. This concept can be represented by table. Columns or field in the table identifies some attributes such as names, numbers of attendance, and so on. Typically, relational model is the most popular type of database system most organisations use to store, and retrieve data. Relational model uses relational database table to organise information in the database, and the main advantage of this table is that information can be stored and retrieved without reorganising the table, and table can have many records and each record in the table can have many fields for storing information. Sometimes, the tables in the relational model have relationship with each other. (techFAQ, 2009)
With relation to database of amateur football club, the main objective of database is to make record keeping for all its members. For example, administration wants to ensure that players that participate at 250 games for over 10 years will be awarded life membership.
Thus, the relational database that fits the club will consists of many tables where all tables will be related to record keeping of the club. For example, there will be table for players, Administrators, social, club committee, and coaches all the tables will be linked to record keeping in the database. Typically, all tables will contain members’ id numbers, names of each member, addresses of members, number of attendances of each member. To identify each record, every record has a unique key that make it to identify a record in the database. For program interface, relational model uses Standard Query Language (SQL). (techFAQ, 2009).
However, SQL can be used to create table in the database, next section examines the use of SQL script to create tables for the club.
Use of SQL Script to create tables
As discussed in the previous chapter, relational database makes use of table for arranging data in order, for storing and retrieving data in the database system. Typically, Standard Query Language (SQL) can be used to create table for database system for the club, and to create table using SQL, there are commands that must be followed. Thus using SQL for creating table for database for the club is illustrated as follows:
The table consists of personal data for players, coach, social, committee, administrators, and all the club members.
First to create table, there is need to create title for the table.
The following command is used to access correct database
DATABASE record attendance;
Thus, SQL commands to create table for the club are as follows:
CREATE TABLE club members
(lifememberid INTEGER NOT NULL,
lastname VARCHAR(25) NOT NULL,
firstname VARCHAR(25) NOT NULL,
reportsto INTEGER NULL);
CREATE TABLE address
(addressid INTEGER NOT NULL,
address Description VARCHAR(40) NOT NULL,
regionid VARCHAR(25) NOT NULL);
CREATE TABLE attendance record
(attendanceid INTEGER NOT NULL,
attendance in a week VARCHAR(40) NOT NULL,
attendance in a year VARCHAR(25) NOT NULL);
CREATE TABLE for club best person
(attendanceid INTEGER NOT NULL,
attendance in a week VARCHAR(40) NOT NULL,
attendance in a year VARCHAR(25) NOT NULL);
CREATE TABLE number of games of each player.
(gameid INTEGER NOT NULL,
games in 1 week VARCHAR(40) NOT NULL,
games in 1 year VARCHAR(25) NOT NULL; (Chappel, 2009)
Meanwhile after using SQL programming for creating table, it is necessary to illustrate how the table will look like when running the table in the database. The whole table in the database will be as follows.
Table 1: Attendance Table for life member.
Table 2: Record attendance for best club person.
Table 3: Record of games played by each player
However, it is necessary to ensure that there is need to create table in case of the need to alter tables in the future to add another attributes in the table, for example, the clubs may decide to create another department whish is not included in the table, and there is need to create table for new department in order to ensure future alteration without disorganise the table. Table future alteration is as follows:
ALTER TABLE new department
ADD name INTEGER NULL;
ADD address INTEGER NULL;
ADD attendance INTEGER NULL;
Business Questions and SQL queries to answer them
Thus, for the best operational database, there is need to include frequent question and answers. The questions and answers are as follows:
How many games can a player play in a year to get life membership?
CREATE DATABSE life members (game in a year is 250 VARCHAR(25) NOT NULL;
Why is the club taking attendance of the club members?
CREATE DATABSE club members (to keep record of each member VARCHAR(50) NOT NULL;
Why is the club taking attendance for the club best person?
CREATE DATABSE club person (to keep attendance of club person VARCHAR(50) NOT NULL;
What are the advantages of database for the club?
CREATE DATABSE advantage (to keep record of all club member and player VARCHAR(50) NOT NULL;
Is football club an amateur or professional?
CREATE DATABSE football (football club is an amateur VARCHAR(50) NOT NULL;
Conclusion
This paper reveals how an amateur football club has designed its database for keeping the attendances of all players, best club persons, and life members in the clubs. The essay reveals that with relational database, there will be proper management of records of all members in the club, and this will enhance administrative decisions on each member in the club.
List of references
Cornell University (nd), Report of the DLF Electronic Resource Management Initiative.
Appendix D: Entity Relationship Diagram for Electronic Resource Management.
Case study of amateur football club, Business school.
Chappel, M, (2009), Creating Database and Tables in SQL, Database Newsletter.
techFAQ, (2009), What is a Database. Web.