Relational Database Implementation: Report on Basketball Tournament
You must implement the entity relationship model provided in the model solution to Assignment 1. The model solution will be released on the course website approximately TWO (2) weeks after the Assignment 1 due date.
The recommended tool for this assignment is Microsoft Access, version 2003 or later. One reason for recommending Microsoft Access is that your course lecturer will be able to provide support in the use of this tool. If you choose to use a different DBMS, then there is no guarantee that your lecturer will be able to provide support. Note: If you choose not to use Microsoft Access, you must contact your lead lecturer as soon as possible before you start work on the assignment. The remainder of this document assumes you are using Access.
In implementing the ERD provided, you must complete the following tasks:
• Map the ERD into a set of relations in at least Third Normal Form (3NF). You must ensure that your relations meet 3NF. There is no need to show your working.
• Select any two (2) of your relations from the previous step and for each of them:
• List all the functional dependencies present in the relation
• Demonstrate that the relation meets Third Normal Form (3NF), for example: STUDENT (StudentID, Name, Address, DateOfBirth)
The primary key is StudentID; this is the only candidate key since Name is not guaranteed to be unique (two different students may have the same name). Address is also not guaranteed to be unique. There will be one student name per Student ID, since only one name is recorded for each student. There will be one address per Student ID; even though a student may have multiple address (e.g. a home address and a work address), the case study states that there is only a need to record the home address. There will be only one date of birth per Student ID. This means that Student ID functionally determines every other attribute in the table; therefore there are no repeating groups. There is a single simple candidate key, therefore no partial dependencies are possible. Name cannot be used to functionally determine any other attribute in the table since two different students may have the same name; likewise for address and date of birth, therefore there are no transitive dependencies in the table. Therefore it meets the requirements of first, second and third normal form.
• Create all the relations in a Microsoft Access database. Consider each attribute in every table and make appropriate choices regarding data types and sizes, indexes, required/not required, and validation rules. Your choices should be appropriate for each attribute and should support data integrity. (Note: see the Data Integrity section below for specific data integrity requirements.)
• Create relationships as appropriate. Enforce referential integrity for all appropriate relationships in the database. Explore the possibility of cascade update and/or delete options.
• Review the default indexes created by Access for each table. You should ensure that the indexes follow the guidelines given in the set textbook.
• Populate the database with sample data. You must include sufficient sample data to test your queries and report(s). You should aim for at least 5 or 6 rows in every table. Please note that the expected result of query questions may depend on the actual sample data populated.
Data integrity
You are required to implement integrity constraints within the database to enforce the following requirements:
For the table Coach:
• All fields must be entered.
• BlueCard number must be an 6-digit number.
• Contact phone can’t be null.
For the table PlayedIn:
• All fields must be entered.
• The only valid values to indicate the match outcome must be “won”, “lost”, or “draw”.
• Score can’t be null.
For the table Earning:
• All fields must be entered.
• Prize must be not less than $100.
Create queries to answer the following information requests. Note: Do not use the Access query builder (QBE) to create your queries – you should type the queries manually using SQL view.
Although it is optional to justify the effectiveness of your SQL statement for the request, the marking process of MS Access queries for information requests may also consider your justification of the effectiveness for your queries in the implementation report if you provide them. (Expected results using sample data for queries depend on your actual sample data populated in the database . The following results of queries are only examples. It is important that your implementation must reflect the correct business logic.)
1. How many matches did each tournament have? Show the tournament’s title and start date, as well as the number of matches of each tournament.
|
Title |
|
StartDate |
NumOfMatch |
|
2011 Challenge Cup |
1/03/2011 |
2 | |
|
2012 Telstra Cup |
2/01/2012 |
2 | |
|
2013 CQU Cup |
5/01/2013 |
11 | |
Q2- Query
FirstNameLastName Phone NumRef
|
Grace |
Duke |
4908798 |
6 |
Q3- Query
| Team Name | Num Played |
|
Glenmore |
3 |
|
Helios |
4 |
|
NorthStar |
2 |
Q4- Query
|
StadiumName |
Street |
City |
State |
PostCode |
FacilityDes |
NumMatches |
|
Rocky Sport |
17 Farm street |
Rockhampton |
QLD |
4702 |
Various fields for different |
2 |
|
Center |
|
|
|
|
sports |
|
|
Fun Sports Club |
76 Lavdar ave. |
Rockhampton |
QLD |
4700 |
Limited seat for viewer |
2 |
|
Telstra Stadium |
19 North Road |
Rockhampton |
QLD |
4700 |
Rugby and basketball fields |
3 |
|
CQ Basket |
202 Harris road |
Rockhampton |
QLD |
4701 |
Basketball field only |
4 |
|
Center |
|
|
|
|
|
|
Q5- Query
|
TeamName |
FoundedDate |
CoachName |
Prize |
Title |
|
Leopard |
10/11/2002 |
Kim Jackson |
$5,000.00 |
2012 Telstra Cup |
Q6- Query
|
SponsorName |
Total |
|
CQU |
$11,000.00 |
|
Education Queensland |
$11,000.00 |
Answer:
Report on Basketball Tournament Database
Functional Dependencies in two Relations
Two relations Tournament and Match are being described here. Following is the structure of Tournament table-
Tournament (TournamentID, Title, StartDate)
Title à TournamentID
<p >There is possibility that two tournaments can have same title, therefore title functionally depends upon TournamentID.
StartDate à TournamentID
There is possibility that two tournaments start on same date, therefore StartDate functionally depends upon TournamentID.
Following is the structure of Match table-
Match (MatchID, StartDateTime, RefereeID, TournamentID)
StartDateTime à MatchID
There is possibility that two matches may start at same date time at different stadium, therefore StartDateTime functionally depends upon MatchID.
RefereeID à MatchID
There is possibility that the referee works for different matches, therefore RefereeID functionally depends upon MatchID.
TournamentID à MatchID
There may be more matches in one tournament; therefore TournamentID functionally depends upon MatchID.
(Janalta Interactive Inc. 2015)
Demonstration of Normalisation in two Relations
All relations in the database are in 3 NF form because all are following the rules of 3 NF form. The third normalization form is satisfied in the relations in the database by following the following rules-
1. There is not any column which is duplicate in any table.
2. There are different tables related to different data.
3. There is relation in all related tables.
4. All the columns depend upon the primary key.
The Tournament and the Match relations will demonstrate the 3 NF form –
The table Tournament has the primary key ‘TournamentID’ and all the fields depend upon the ‘TournamentID’. It uniquely identifies the record in the relation. Every field except ‘TournamentID’ cannot be unique in the Tournament table. Therefore there is only one candidate key and there is no partial dependency in the relation and the relation ‘Match’ references the ‘TournamentID’ of the relation ‘Tournament’. It enforces the referential integrity for the relation ‘Tournament’.
The relation ‘Match’ has the primary key ‘MatchID’. All the other attributes are dependent upon the ‘MatchID’. Every field except ‘MatchID’ cannot be unique in the Match table. Therefore there is only one candidate key and there is no partial dependency in the relation.
All the other relations are also satisfying the above 3 NF form rules as per the above two relations. (teach-ict.com n.d.)
(About.com 2015)
Limitations in the Assignment
Some points are missing in the assignment. For example –
1. The queries of outer joins are missing in the assignment.
2. There is lack of complex queries in the assignment.
3. There is lack of creating the forms in the assignment.
Learning during the Assignment
1. The assignment is giving the complete working knowledge about database. After completing this assignment, I learn more about the reports, queries and integrity constraints of MS Access.
2. The queries are very useful for general database. I am feeling very confident now in database.
3. There are hints given for the report in the assignment, I found the right way to create the report by the given hints. Therefore I did not go in the wrong direction to create the report.
References
[1] Janalta Interactive Inc. 2015, Functional Dependency, Viewed on 15th Jan 2015
<https://www.techopedia.com/definition/19504/functional-dependency>
[2] About.com 2015, Third Normal Form (3NF), Viewed on 15th Jan 2015
<https://databases.about.com/od/administration/l/bldef_3nf.htm>
[3] teach-ict.com n.d., Third Normal Form, Viewed on 15th Jan 2015
< https://www.teach-ict.com/as_a2_ict_new/ocr/AS_G061/315_database_concepts/normalisation/miniweb/pg9.htm>
Buy Relational Database Implementation: Report on Basketball Tournament Answers Online
Talk to our expert to get the help with Relational Database Implementation: Report on Basketball Tournament Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
