Using the given a case study coder will be required to design and implement an object relational database in Oracle (has to be in ORACLE ISQL+).
Using the following case study for which you will be required to design and implement an object relational database in Oracle.
**AMBRIDGE NETBALL LEAGUE (ANL)**
**BASIC RULES OF NETBALL**
Netball is a game with two teams of 7 players. Each player is given a position to play with a set role:
GS ??" To score goals and to work in and around the circle with the GA
GA ??" to feed and work with GS and to score goals
WA ??" to feed the circle players giving them shooting opportunities
C ??" to take the centre pass and link the defence and the attack
WD ??" to look for interceptions and prevent the WA from feeding the circle
GD ??" to win the ball and reduce the effectiveness of the GA
GK ??" to work with the GD and prevent the GS from scoring goals
Only GS or GA can score goals.
The Ambridge Netball League wishes to set up a computerized system for recording league details and tracking games.
**FEES & AFFILIATION**
All players must be affiliated to play in the ANL, with the exception school teams (who are exempt) or new teams who are allowed one whole season before they must affiliate. The current fees are £27.50 per adult & £15.50 per junior U18 years. The leagues secretary needs to store basic information on each player ??" name, age, contact details and a photo of the player.
A player who is not affiliated may play as a guest player only once per season. The name of the player must appear on the results card with “GP?? marked next to her name. If she wishes to play again she must be affiliated before the match. No more than two guest players may be used in a team at a time.
Each team must register all players at the start of the season. The maximum amount of players that can be registered is 14 per season. Any team with unregistered players will lose their match by default. All guest players also need to be registered.
All teams must pay the league entry fee in advance to enter the league & receive their scorecards. This is currently £45 for all teams, with the exception of school teams who pay £5.00.
Any player who is named as ‘player of the match’ more than 3 times in a season is given a cash prize by the league.
**FACILITIES FOR PLAY **
Fixtures are played at a number of schools and community colleges. Players, officials and supporters must be able to access basic info on each location, ie address and telephone number, whether parking is available and a map showing the location of the site. The team shown as first on the fixtures list is responsible for ensuring that the correct facilities are available and are responsible for providing the match ball.
Fixtures are drawn up at the start of the season. There are three league levels: A, B and C. New teams are automatically placed in the bottom league.
Team score cards need to be registered at least one week before the start of the match, showing team name, date and players names in full together with their positions. At the end of the match the umpires must sign both completed cards, decide on the player of the match and forward the information to the league secretary.
**Points will be awarded as follows:
6 points for a win
4 points for a draw
3 points if within 5 goals of opponents score
2 points if within 50% of opponents score
1 point if less than 50% of opponents score
Any team canceling a match will be deducted 2 points & the opposing team will be awarded 6 points. A cancellation fee of £5 must be given to the league organizer before their next match; this fee will be given to the opposing team as compensation. If the match is cancelled within 24 hours then that team will have to pay the relevant umpires fees.
**APPOINTMENT OF UMPIRES****
**All teams must have a qualified umpire unless agreed with the league organizer. If a team continues to break this rule they may be fined or excluded from the league. All umpires must be affiliated to the Netball Umpiring Association, and will be monitored & assessments done in order to get consistency & improve the standard of umpiring.
The decision of an umpire shall be final and given without appeal whilst the match is in place. However, any match complaints or queries to be brought to the attention of the umpiring secretary in writing within 48 hours of the incident. The secretary will rule on the incident and will inform the teams concerned if any adjustments are to be made to the points gained for that match.
_What is required of this element of the assignment:_
a) A relational schema for the class diagram showing tables, types and integrity constraints.
b) Rationale for the design explaining how object-relational concepts and types will be incorporated into the design. You must use object types, tables and at least ONE collection type.
c) Documented set of objects, including attributes and methods (Create Object Table and TYPE statements).
d) At least One basic method and ONE complex methods developed in PL/SQL .
e) Relevant metadata and sample data with insert statements to illustrate how the database meets the requirements.
f) A critique of your design and a review of the effectiveness of using the object-relational approach for this application.
g) Any technical difficulties you have experienced in implementing the design.
The design must be object-relational and would be expected to include user-defined types, including simple and complex objects, object tables and collection types.
**Note??" you are only required to design the database, not the related Web site or middleware.**