A Basketball Tournament System

Aim: to give you practical experience in using entity-relationship and relational modelling techniques.

Project Specification

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 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.

Club information

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.

Venue information

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.

Games schedule and results information

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.

What to submit

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:

• An entity relation diagram that models the problem which includes:
  • all entities, relationships (including names) and attributes,
  • primary (underlined) and foreign (italic) keys identified,
  • include cardinality and participation (optional / mandatory) symbols,
  • assumptions you have made, e.g. how you arrived at the cardinality / participation for those not mentioned or clear in the description, etc.
  • The E-R should be created as part of a Microsoft ® Word document and completed using the standards of this unit (crow’s feet).
• Relational data structures that translates your E-R diagram which includes:
  • relation names,
  • attribute names,
  • primary and foreign keys identified
  • for each relation show the level of Normalisation achieved, and for any not to Third Normal Form, explain why.
  • The data structures should be shown using the standards of this unit.
• A relational database schema that translates your relational data structures which includes:
  • table names,
  • column names and field types
  • primary and foreign keys identified
• Entity name should in upper case, names of attribute and relationship should be capitalized. All the primary keys should be underlined, and all the foreign keys should be italic.