create a database

Scenario Title: Allotments The Allotments Organic Vegetable Society (AOVS) was formed in 2005 by a group of owners who rent out allotments in Kent and East Sussex. These allotments, called plots, are all roughly the same size. The location of each plot is identified by a three character code that starts with either a ‘C’ or a ‘T’ followed by two digits in the range ‘0’ ... ’9’. The vegetables they grow are harvested and sold to local retail outlets. At present each gardener’s produce is sold by only one of these outlets. Each gardener can rent many plots but they cannot rent more than one plot in any particular location. Each plot can be used to grow a range of vegetables and the total yearly yield for each vegetable per plot is recorded in kg. No more than 4 generic vegetable descriptions can be grown on any plot in any one calendar year. It is planned to implement a small database to record the activities of AOVS since it was founded: current records are being kept in a mixture of word-processed and spreadsheet files. The name of each retail outlet, outlet_name, uniquely identifies the outlet and is to be stored as a character string. A typical outlet is CCCU Students Union. Each outlet is to provide at least one of an e-mail address (e_mail) or an 11-digit landline telephone number (tel_no), which must start with a zero. Each gardener is allocated a unique 4-character code that cannot start with a ‘comma’, or a ‘full stop’ or any of the following characters: !, £, $, *, <, >, ", =. In addition a gardener’s name, address and telephone number are always stored. Each plot is given a unique identifier, plot#, which comprises 5 digits, the first of which cannot be 0. The rent charged for each plot depends on whether the plot is privately or council owned and on whether or not a shed and/or a convenient water supply is provided. The plot owner’s name and category Private or Council is to be stored and each owner is identified by a 3-character code where the first character is in the range ‘1’ ... ’9’, and the second/third characters are each in the range ‘A’ ... ’Z’. The vegetables grown in the plots are identified by the names (veg_id) used in standard Gardening Catalogues (e.g. Maris Piper, Winora, Parmex, etc.) except that they are always stored in upper case. Each of these vegetable names is associated with a generic vegetable description, e.g. potato, leek and carrot (for Maris Piper, Winora and Parmex vegetables respectively), which is always stored. The annual rent is subdivided into bands, each identified by a single capital letter. The minimum annual rent is currently £75. If the plot owner is a Council and there is no shed then the rent band is ‘A’. If the plot owner is private and there is a water supply then the rent band is ‘D’. If the plot owner is a Council and there is a shed but no water supply then the rent band is ‘B’. Similarly if the plot owner is private and there is no shed and no water supply then the rent band is ‘B’. In all other cases the rent band is ‘C’.