Overview
The Sports Report Information System (TSRIS) is an automated information system built on a relational database. Therefore, information in the system is stored in structures called relations (or tables), which are basically grids of rows known as records (or tuples) and columns known as fields (Garcia_Molina et al, 2000, 2 ). The relational database system drives the automation of the system though the use of special software for designing and implementing relational databases known as Relational Database Management System (RDBMS). The Sports Report Information System (TSRIS) is designed for the AllSports Sport Club specifically with the goal of ensuring that the club’s newsletter is prepared and distributed in time, information access is done with efficiency and present the club’s president with reports on members who have not edited the club’s newsletter. Thus, the statement of purpose for the relational database is “The AllSports Sport Club relational database holds information on the club’s members, activities and newsletters. It will be used for AllSports Sport Club data entry and generation of the club’s useful reports.”
There are eight main relations in the AllSports Sport Club relational database, namely, Member, Member Contact, Sport Activity, Newsletter, Article, Advert, Advertiser and Advertiser Contact. These relations are normalized to the third normal form thus their do not exhibit repeating groups, functional dependencies and transitive dependencies (Mendes, n.d, 1). The normalization to this extent is done to eliminate three common anomalies associated with poor relational database design, namely, modification, insertion and deletion anomaly. The first anomaly results in erroneous data in the relational database due to lack of consistency in modification of the data. The second anomaly results in an inability to enter new and needed data into the relational database due to incorrect fields and faulty relationships among the database’s relations. The third anomaly results in unwanted loss of needed data through deletion due to poorly constructed relations. The normalization additionally guarantees the future growth as well as expansion of the relational database and in effect the Sports Report Information System (TSRIS). In the design of the relations, certain business rules are assumed for the purpose of enhancing and maintaining data integrity.
In addition to the relational database, the Sports Report Information System (TSRIS) contains a user interface to aid in productive interaction between the system and its users. The user interface comprises of a switchboard, a RDBMS utility that enables easy navigation through the various database objects and two data entry screens, which are basically RDBMS objects called forms. A menu hierarchy for the user interface is also provided, which is a menu tree showing how all the input screens and sub‐menus of TSRIS system are grouped and linked together to the main menu. The switchboard as mentioned above enables easy navigation through the various database objects such as reports, which are the main outputs of the system. From the switchboard, the club’s president can launch and view two reports. The first is a detailed report listing advertisements grouped under the advertiser’s name and showing the total number of advertisements each has supplied. The second is an exception report showing members who have not yet edited any of the club’s newsletters.
System ERD
The schema for AllSports Sport Club relational database is shown in appendix A and gives a structural description of the data in the database. An entity-relationship diagram (or ERD) shows the entities that make up a relational database, their attributes and the relationships between them (About.com). The diagram in appendix B shows the entity-relationship diagram for the AllSports Sport Club relational database.
ERD assumptions
One of the underlying assumptions in the design of the ERD is that a single member can write many articles in one or more newsletters and many members can write one article in one or more newsletters. From this assumption, it can be assumed that all members of the club can write articles implying that they are possibly adults with a certain minimal level of education. Another underlying assumption is that an advertiser is allowed to place more than one advert in a single newsletter or in multiple newsletters. Another assumption is that a member of the club can participate in more than one sporting activity and in addition, records are kept of current members engaged in a given sporting activity. Another assumption is that the club’s member can have more than one postal or e-mail address. Another assumption is that advertiser’s in the club’s newsletters can have more than one postal or e-mail address. Another underlying assumption in the design of the ERD is that one newsletter can only have one editor. It is further the assumption that each of the members has editing abilities implying that the club’s members are possibly adults with a certain minimal level of education.
Third Normal Form Table Designs
The relational database schema shown in appendix A shows the third normal form table designs for the relations captured in the ERD in the figure in appendix B. In addition to these relations there is the Member & Sporting Activity table, which is a junction table that break the many-to-many relationship that exists between the Member and Sporting Activity tables.
Table keys
Member
The Member table holds information on the club’s members. The Member ID field is the only candidate key and thus, it is also the primary key. I have introduced this field into the table to capture unique identifications that are assigned to each of the members of the club. The secondary keys for this table are the Title of courtesy, First name and Last name fields as these cannot be used to identify a member uniquely. The Title of Courtesy field holds the various titles of courtesy e.g. Mr. Miss, Mrs Dr. Etc which are used as a show of respect when addressing someone. The First name and Last name fields hold a members first name and last name. Table 1 in appendix C shows a summary of the table keys of the member table.
Member Contact
The Member Contact table holds the contact information of the members of the club. Its creation has been necessitated by the fact that a member can have more than one postal address, e-mail address and phone number. The candidate key in this table is the Member Contact ID, which as well the primary key. The Member Contact ID field is a field I have introduced that holds unique identifications for records in the Member Contact table. It is of a numeric datatype and preferably of an auto-numbering format. The secondary keys in the table are Contact phone number, E-mail address and Postal address as these cannot be used to identify a record in the table uniquely. It is possible for two records to share the same Contact phone number, E-mail address or Postal address. The Member ID field is the foreign key as it links this table with the members table where one record in the members table is related to one or many records in the Member Contact table. Table 2 in appendix C shows a summary of the table keys of the member table.
Sporting Activity
The Sporting Activity table holds information on the various sporting activities the club is involved in. The candidate key and primary key in this table is the Activity ID as this the only field in the table that can uniquely identify a sporting activity. This field I have introduced it to the table to hold unique identifications for the various sporting activities in the club. The secondary key in the table is the Activity name, which holds the name of a given sporting activity. Table 3 in appendix C gives a summary of the table keys for the Sporting Activity table.
Member & Sporting Activity
As shown in the ERD there exists a many to many relationship between the Members table and the Sporting Activity table thus to break down this relationship the Member & Sporting Activity table is created. The primary key in this table is the Record number field, which is an introduced field that holds unique identifications of the records on the Member & Sporting Activity table. There are no secondary keys in this table as it is a junction table. The foreign keys in the table are the Member ID and Activity ID. Table 4 in appendix C gives a summary of the table keys for the Member & Sporting Activity table.
Advertiser
The advertiser table holds information on advertisers on the club’s newsletter. The candidate and primary key in this table is the Advertiser ID, which is a field I have introduced to hold unique identifications assigned to advertisers. The secondary key in this table is the Advertiser Name, as this field cannot be the primary key since it is not appropriate for identifying advertisers uniquely. Table 5 in appendix C gives a summary of the table keys for the Advertiser table.
Advertiser Contact
The Advertiser Contact table holds the contact information of advertisers in the club’s newsletter. Its creation has been necessitated by the fact that an advertiser can have more than one postal address, e-mail address and phone number. The candidate key in this table is the Adveriser Contact ID, which as well the primary key. The Advertiser Contact ID field is a field I have introduced that holds unique identifications for records in the Advertiser Contact table. It is of a numeric datatype and preferably of an auto-numbering format. The secondary keys in the table are Name of Person to Contact, Contact phone number, E-mail address and Postal address as these cannot be used to identify a record in the table uniquely. It is possible for two records to share the same Contact phone number, E-mail address or Postal address. The Advertiser ID field is the foreign key as it links this table with the Advertiser table where one record in the Advertiser table is related to one or many records in the Advertiser Contact table. Table 6 in appendix C shows a summary of the table keys of the member table.
Advert
This table holds information on the adverts posted on the club’s newsletter. The candidate and primary key is the Advert ID field, which is a field I have introduced to hold unique identifications assigned to adverts posted on the club’s newsletter. The secondary key in this table is the Advert title field, which holds the title of a given advert posted in any of the club’s newsletter. The foreign keys in this table are Advertiser ID and Newsletter ID. The Advertiser ID foreign key links the advert table with the advertiser table in such a way that one record in the Advertiser table is related to many records in the Advert table. The Newsletter ID foreign key links the advert table with the Newsletter table in such a way that one record in the Newsletter table is related to many records in the Advert table. Table 7 in appendix C shows a summary of the table keys of the Advert table
Article
This table holds information on the articles included on a particular edition of the club’s newsletter. The candidate and primary key is the Article ID field, which is a field I have introduced to hold unique identifications assigned to articles. The secondary key in this table is the Article title field, which holds the title of a given article included in any of the club’s newsletter. The foreign keys in this table are Member ID and Newsletter ID. The Member ID foreign key links the Article table with the Member table in such a way that one record in the member table is related to many records in the Article table. The Newsletter ID foreign key links the Article table with the Newsletter table in such a way that one record in the Newsletter table is related to many records in the Article table. Table 8 in appendix C shows a summary of the table keys of the Article table
Newsletter
This table holds information on the newsletters published by the club. The primary and candidate key in this table is the newsletter ID field, which is a field I have introduced to hold unique identifications assigned to Newsletters published by the club. The secondary key in this table is the Newsletter title field, which holds the title of a given newsletter published by the club. The foreign key in this table is the Member ID, which links it with the Member table in such a way that one record in the member table is related to many records in the Newsletter table. Table 9 in appendix C shows a summary of the table keys of the newsletter table
Sample Data
The tables in appendix D show sample data for the tables that make up the AllSports Sport Club relational database.
Works Cited
“Entity-relationship diagram.” About.com. n.p. (2011) Web.
Garcia_Molina, Hector, Ullman, D. Jeffrey, And Widom, Jennifer. Database System Implementation. Singapore: Addison Wesley Longman, 2000. Print.
Mendes Emilia. Normalization Rules: 1NF, 2NF, 3NF. Web.
Appendix
Appendix A
Schema for AllSports Sport Club relational database:
- Member (Member ID, Title of courtesy, First name, Last name)
- Member Contact (Member Contact ID, Contact phone number, E-mail address, Postal address, Member ID)
- Sporting Activity (Activity ID, Activity Name)
- Member & Sporting Activity (Record Number, Member ID, Activity ID)
- Advertiser (Advertiser ID, Advertiser Name)
- Advertiser Contact ( Advertiser Contact ID, Name of person to contact, Contact phone number, E-mail address, Postal address, Advertiser ID)
- Advert (Advert ID, Advert title, Advertiser ID)
- Article (Article ID, Title, Member ID)
- Newsletter (Newsletter ID, Title, Member ID)
Appendix B
Appendix C
Table 1: Summary of table keys in the Member table.
Table 2: Summary of table keys in the Member Contact table.
Table 3: Summary of table keys in the Sporting Activity table.
Table 4: Summary of table keys in the Member & Sporting Activity table.
Table 5: Summary of table keys in the Advertiser table.
Table 6: Summary of table keys in the Advertiser Contact table.
Table 7: Summary of table keys in the Advert table.
Table 8: Summary of table keys in the Article table.
Table 9: Summary of table keys in the Newsletter table.
Appendix D
Table 1: Sample data for the members table.
Table 2: Sample data for Member Contact Table.
Table 3: Sample data for Sporting activity Table.
Table 4: Sample data for Member & Sporting activity Table.
Table 5: Sample data for Advertiser Table.
Table 6: Sample data for Advertiser Table.
Table 7: Sample data for Advert Table.
Table 8: Sample data for Advertiser Table.
Table 9: Sample data for Advertiser Table.