Tables in the Queensland state soccer carnival database are normalized to the third normal form meaning that they do not exhibit repeating groups, functional dependencies and transitive dependencies. To demonstrate this we consider the tbl_player and tbl_score tables.
The tbl_Player table is created to hold information on the players participating in the Queensland state soccer carnival and it comprises of seven fields, namely, Player Number, Full Name, Address, Contact, Age, Medical Notes and Team Number. The Team Number field is a foreign key linking tbl_Player and tbl_Team.
In tbl_Player, the primary key is Player Number, which is also the only candidate key since Full Name, Address, Contact, Age, Medical Notes are not guaranteed to be unique. The Player Number field holds a unique number that is assigned to each player who is taking part in the tournament.
There will be one player full name, address, contact, age and medical Notes per player number. This means that player number functionally determines every other attribute in tbl_player and therefore, there are no repeating groups and thus the first normal form is met (Mendes, n.d, 1). There is a single simple candidate key (Player Number) and which is also the primary key; therefore, no partial dependencies are possible.
Player Full Name or any other field in tbl_Player apart from Player Number cannot be used to functionally determine any other attribute in the table since players can have the same full names, address, contact, age and medical notes and thus the second normal form is met (Mendes, n.d, 1).
There are no transitive dependencies between tbl_Player’s non-candidate keys (Full Name, Address, Contact, Age, Medical Notes) and thus the third normal form is met (Mendes, n.d, 1).
The tbl_Team table is created to hold information on the teams nominated for the Queensland state soccer carnival and it comprises of two fields, namely, Team Name and Team Colors. The tbl_Team table does not have any foreign key. In tbl_Team, the primary key is Team Name, which is also the only candidate key since Team Colors is not guaranteed to be unique.
The Team Name field holds the unique name of each of the teams participating in the tournament. There will be one team colour per team name. This means that team name functionally determines every other attribute in tbl_Team and therefore, there are no repeating groups and thus the first normal form is met (Mendes, n.d, 1).
There is a single simple candidate key (Team name) and which is also the primary key; therefore, no partial dependencies exist in the table.
Team Colour does not functionally determine any other attribute in the table since there are only two fields in tbl_Team and furthermore, it is not a candidate key field and thus the second normal form is met (Mendes, n.d, 1). No transitive dependencies exist in tbl_Team as there are only two fields in the table and thus the third normal form is met (Mendes, n.d, 1).
Implementation report
The Queensland state soccer carnival database comprises of a total of nine tables. Junction tables have been included in the Queensland state soccer carnival database to break down many-to-many relationships existing between certain relations.
The junction tables are tbl_Match&Team and tbl_Referee&Match that breakdown the many-to-many relationship between tbl_Match and tbl_Team tables and tbl_Referee and tbl_Match tables respectively.
In the implementation of the database in MS Access look up lists have been used to make the process of data entry more easy and accurate. Referential integrity has been enforced so that the integrity of the data held by the database is maintained.
Also as a measure of improving data integrity in the database, integrity constraints have been enforced varyingly in the database to ensure correct and accurate input of data in it.
Queries to the database follow the known structure of SQL queries, that is, starting with the Select clause followed it by the From clause and then by the Where clause then by the Group By clause then by the Having clause and then finally with the Order By clause (Garcia-Molina et al, 2000, 15).
Works Cited
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. <https://www.auckland.ac.nz/en.html>.