Aim: to give you practical experience in using entity-relationship and relational modelling techniques.
You are asked to design a relational database system for a Junior Basketball Tournament. You must design the database so that it can maintain the following information:
Series officials will be using the system to allocate match team schedules and game locations. There are at least two different venues for the games, and may be more in the future. Each venue has 2 or 4 courts available. For each official registered, information will be stored such as Name, Mobile phone number, and Email address. There will also be a password that they use to authorise themselves when logging into the system. Officials can be distinguished by their registration number.
There is also a registered list of referees who are available in this series. For each referee, information is stored including Name, Registration Number, Mobile Phone Number and level of accreditation (level 1 or 2). For each game, two referees are allocated, at least one referee must be a level 2.
Teams must be registered as part of a club. Each club may have multiple teams in each series. There are separate series organised for each age level and grading level. For example, in series “under12 Gold”, club ‘Eastern Eagles’ may enter 1 or more teams. Each team will be identified by a unique ID such as “EEagGold12A” This tag is usually a concatenation of the clubid (e.g. “EEag”), the grading level (e.g. “Gold”), the age group (e.g. under 12), and a unique letter [A..Z].
Required information for each club includes club name, club address and contact information (including phone, address, email) for each registered coach. Also, for each junior player, a player id, name, club, date of birth and team.
The valid age groupings in junior basketball are: under 12 years, under 14 years and under 16 years. Each has a code such as U12Y, U14Y, U16Y respectively. Within each age range, there are 3 levels of grading: RED, GREEN and GOLD.
There are a number of venues with courts available for junior basketball. For each location, an address, contact person and current status (either available or not available) is kept in the system. Each location has a unique venue number.
There is also a comment section in which adhoc remarks can be entered with an associated entrydate referring to specific events or dates unavailable at particular locations. Each such remark is stored with a date of entry and associated location number.
The system based on your database must be able to input a new draw scheduling games in the following format: Date, venue, court number, team1, team2, start time, referee1, referee2.
After each game, one of the referees will log in to the system and enter the game results. The results will be stored as summary statistics: Winning team, Total scored for each team. For each player, statistics are stored (for each game): total number of points scored, number of personal fouls.
You are required to submit a hard copy which contains all the required tasks and a soft copy through Moodle or on a disk submitted to your tutor (ask your tutor for further submission details).
You are to produce an entity-relationship design to represent the cricket series system described above. The design should be expressed in a consistent ER diagram notation and be submitted as a collection in a pdf file or word document which contains: