Dataset details: CRDA/39/DS/1

Data from the Lottery Awards Database was transferred to NDAD by the Department for Culture Media and Sport (DCMS) on an Iomega Zip Disk in the form of an Oracle Export dump file on 29 March 2001. Five items of dataset documentation were received separately as an email attachment on 23 April 2001 (for further details see the Dataset Documentation Catalogue).

The dataset consists of a snapshot of the DCMS Lottery Awards Database in March 2001 The dataset contains details of grants awarded using money raised by the National Lottery. It is based on grant award information provided to DCMS by the 15 Distributing Bodies between 1995 and 2001. Details of grants awarded up to and including 8 March 2001 are contained within the dataset. It may therefore be assumed that the snapshot was taken shortly after this date. Limited information about unsuccessful applications for lottery grants made between 1995 and 1997 is also available within the dataset.

The Oracle export file received from DCMS was loaded into Oracle8i Personal Edition Release 2 (8.1.6) for Windows 98) using the Oracle Import utility. (Import inserts the data objects extracted from one Oracle database by the Export utility (and stored in an Export dump file) into another Oracle database). The data was then exported using sql scripts to external comma-separated text files. The CSV files were then run through standard utilities to remove superfluous space at the end of each line and convert dos to unix line endings.

The dataset is open except for certain tables and fields, which are closed for 30 years. Details of closed tables and fields are given in the table catalogues (see Logical structure and schema).

The dataset comprises 25 tables, tables 1-5 contain data relating to awards although the AWAWDS table contains the bulk of the data. Of the remainder, tables 6-20 are lookup tables and tables 21-25 cover administration of the database. There are an additional 3 tables listed in CRDA/39/DD/4/2 ("AWDATA Current Server Table Definitions"), namely AWAWBEN ("Award's beneficiaries"), AWUPLOAD ("Used to inform users that an upload is taking place for a specific distributing body when performing a search"), and USERS_LOGGEDON ("Contains details of users currently logged on"). The AWAWBEN table is a link table between awards and beneficiaries. It was established from the Department that the distributing bodies do not currently give DCMS this information and that there is no mechanism for them to do so via an upload. The DCMS Lottery Division used to update the information manually but no longer do so and therefore deleted all the beneficiary information, which is why there is no data in the AWAWBEN table. The data in the latter two tables (ie AWUPLOAD and USERS_LOGGEDON) would be transitory (ie only apply for a specific session). Consequently these three tables have not been made available as part of this dataset. AWAREA and AWAREA_LOCN were not listed in CRDA/39/DD/4/2 but do contain data (and are present in a later (September 2002) version of the afore-mentioned document); these therefore have been preserved and made available along with the other tables.

The relationships between the tables have been derived from the 'Logical data model', see the Dataset Documentation Catalogue, reference CRDA/39/DD/4/1. In addition, a number of inter-table relationships which were not defined in the original database have been created by NDAD as a convenience to users: the relationships between AWAWDS and the lookup tables have been mirrored for AWFAPP and an additional link established between AWAWDS and AWPROJNAME but see Content validation below.

The field names and original field descriptions included in the Table Catalogues were taken from system documentation supplied by DCMS and their IT contractor, see the Dataset Documentation Catalogue, references CRDA/39/DD/3/1-2. A data model diagram showing the entities in the Lottery Awards Database and the relationships between them was also received (Dataset Documentation Catalogue, reference CRDA/39/DD/4/1).

The dataset comprises the following table(s):

During the processing of the dataset, a number of anomalies were identified in the data:

• During checking of the converted data, a number of errors in the address data in the AWAWDS table were detected. DCMS have confirmed that these occurred because of a problem with the system operated by some of the distributing bodies. They have also explained that, with regards to addresses, the database is used mainly for answering queries on electoral wards, local authorities and regions. The address lines are not validated on upload and only the first address line is mandatory. Many of the anomalous records have an entry in ADDR_LINE_3 which relates to a different part of the country to the other address fields. For instance, the record with AWARD_ID = SB-94-00052 relates to the West Pier in Brighton but ADDR_LINE_3 is 'Totnes'. DG-95- 01465 relates to Stebbing in Essex but ADDR_LINE_3 is 'Station Road Haworth'. The latter entry occurs in ADDR_LINE_3 in 15 records (ranging from Armagh to Winchester) and it seems at least possible that this has arisen because entries have been carried over from a previous record. Other examples of this sort of anomaly: HF-97-01439 (this is Somerset House but ADDRESS_LINE_3 is Kemble Drive), RF-98-01039, HF-97-01448, HF-97-01449, HF-99- 00160/1, HF-99-00168/1, HF-99-00143/1. Of the anomalies, of this type, inspected, the DISTRIB_ID was usually HM (the Heritage Lottery Fund). DCMS state that many of these anomalies are not present in the current (October 2002) copy of the database, presumably because these awards have been overwritten with new uploads. They did however find some awards which have Station Road and a town name in ADDR_LINE_3, some of which are valid and some of which result from errors in the upload files.
• There are 1795 records with just E in ADDR_LINE_1; the remaining address fields are in many cases somewhat 'odd' (many have the postcode repeated in ADDR_LINE_2). These records relate to Distributing body AE (Arts Council of England). DCMS state that their current (October 2002) copy of the database contains 858 records with just E in ADDR_LINE_1. They inspected one of the upload files used and stated that the errors detected in records with DISTRIB_ID AE are the results of errors in the upload file (for example, for award id 3-515, E is the project location and the post code is NG1 1FH; these are then repeated in address lines 1 and 2.)
• A number of records in AWPROJNAME have the AWARD_ID in a different format to the AWARD_ID in what appears to be the matching record in AWAWDS eg the extension to the title of AAE/1/000236637 appears to be recorded in the AWPROJNAME table with AWARD_ID AA236637 and MG/1/000238611 in AWAWDS appears to relate to MG238611 in AWPROJNAME. AAE/1/000240354 in AWAWDS has records AA240354 and AAE/1/000240354 in AWPROJNAME. Of the 67999 records in AWPROJNAME, 6405 do not have a matching record in AWAWDS; most, if not all, of these appear to be caused by the afore-mentioned differences in the format of the AWARD_ID. In addition, there are a number of records (8384) in AWPROJNAME which have PROJ_NAME_LONG blank. This was queried with DCMS but they were unable to provide an explanation since in their current copy of the database, there are, they state, no blank values in PROJ_NAME_LONG. AWARD_IDs 4631 and 4869 have 2 records in AWPROJNAME with PROJ_NAME_LONG both blank.
• A small number of records contain 'odd' characters; these were present in the Oracle dump file - as received from the Department and viewed via Oracle (Oracle8i Personal Edition Release 2 (8.1.6) for Windows 98)). These include the records in AWAWDS with ELECTORAL_WARD = 'Felin-f¿ch': AWARD_ID = 971108, SG/1/000234691, SG/1/000300267, AAW/1/000250320, 9833, 96186, 95506, 9529, SG/1/000225323, AAW/1/000243302. When viewed via Oracle on receipt of the data from DCMS, these were displayed as 'Felin-f¿ch' but appear on the DCMS web-site as Felin-ffch. The equivalent entry in the AWEWRD table is also displayed as 'Felin-f¿ch'. There are 108 records in AWAWDS with ¿ in the PROJ_NAME. There are 23 records in AWPROJNAME in which PROJ_NAME_LONG contains the character ¿ and 3 (2463, 2464, 3235 all DISTRIB_ID MC) in which it contains the þ character eg 2463 has "... There will also be þThe Rugby Experienceþ museum..."; on the DCMS web-site, it displays as "... There will also be ¶The Rugby Experience÷museum ..."
• There are 4 regions (Overseas, UK Wide, England & Wales Wide, Great Britain Wide) listed in AWRGN which are not in AWLAUY (these regions do not appear to be present in the REGION field in AWAWDS either).
• 3 records have no entry in the DESCRIPTION field in AWLOOKUP (so for type 'MP' there is no explanation for the code B, similarly for code B type MS (MP Salutation) and code B type MH (MP House).
• All records have 'N' in the LETTER_STATUS field in AWAWDS. This field was designed to indicate whether a letter has been sent to the appropriate MP but the Department have explained that they do not use this facility.
• The 'Physical Design Specification' (CRDA/39/DD/3/2) suggests that COUNTY is the key to the AWNICTY table and acts as a link to the primary key of AWLAUY but this appears to be incorrect. (In any case, note that DCMS have confirmed that the table AWNICTY is redundant).
• In AWERR, the ERR_ID is repeated at the beginning of the ERR_TEXT in all of the 122 records except for one (ERR_ID = 110)
• Although the AWFAPP table is very similar to the AWAWDS table and therefore the relationship between the former and the lookup tables have been duplicated for AWFAPP, there are some discrepancies which result in the linking not working for some records. For example in the LOCAL_AUTHORITY in AWFAPP there are entries such as 'Argyll & Bute', 'Dumfries & Galloway', 'City of Glasgow', 'Westminster, City of' whereas the AWLAUY lookup table (and AWAWDS) has 'Argyll and Bute', 'Dumfries and Galloway', 'Glasgow City', 'Westminster'. Similarly AWFAPP has UK_CONST = 'Galloway and Upper Nithsdale' whereas the equivalent entry in the AWUKCN table is 'Galloway & Upper Nithsdale'.

Spot checks, comparing the values of specific fields, were carried out to compare the transformed data against the data in the Oracle database. In addition, each table was checked to ensure that the overall number of records and fields remained the same. The sums of all the numeric fields were confirmed to be identical in the Oracle data and the transformed data (ie in the CSV files). No discrepancies were detected between the original and transformed data. Some further checks were carried out by searching for particular records on the DCMS web-site. All records searched for were found and the amount of the award was identical but occasionally the date of the award was noted to be different (usually the difference was not large but for instance AWARD_ID EOSL1999242 has AWARD_DATE = 02-02-1999 but on the DCMS web-site the date is 01/02/00. It is presumed that this arises because details of this record have been corrected via an upload since the data was transferred to NDAD in March 2001).