Catalogue description Metropolitan Police: Crime Statistics Database

Hardware: ICL 3960 mainframe. An ICL 2966 and an ICL 2988 were used for development work.

Operating System: VME, with an SCL-based interface.

Application Software: IDMSX (Integrated Database Management System), manufactured by ICL, was used to create the ME database. ICL's ReportMaster and the Office for Population Censuses and Surveys' Tabulation Utility (TAU) were used for the production of tables. TAU was phased out in 1991 and replaced with an in-house program. SPSS-X 3.0 was used for ad hoc enquiries. This package and ICL's QueryMaster replaced two tabulation packages called FIND and SDTAB which had been used with the MC System. QueryMaster was used for ad hoc enquiries of the database, monthly, year-to-date and historical files. ICL's Transaction Processing Management System (TPMS(X)) was used to manage G10's access to the database via the transaction processing service. By 1992 Microsoft Excel 3.0 was used by PIB to manipulate data downloaded from the ME database onto local PCs.

Logical structure and schema: The calendar year datasets for 1990-92 and the financial year datasets for 1992-93 and 1993-94 consist of two files for each dataset (a YTDEXTRACT file and a YTDNOCREXT file). The 'truncated' financial year dataset for 1994 consists solely of a YTDEXTRACT file. The financial year datasets for 1994-95 and 1995-97, by contrast, each consist of a single YTDFEXTRACT file holding all data on offences, arrests, victims and 'No Crime' allegations for that year. A further dataset consists of a file which holds the data in the ME database at the end of its operational life. The format of this file (DBEXTRACT) is essentially identical to the YTDEXTRACT files in 1994-95 and 1995-97.

A distinction should be made betwen the structure of the datasets - most of which originated in cumulative extract files archived each year - and the structure of the original IDMSX database. This is believed to have been a hierarchical database centered around tables holding data for different types of records, consisting of:

• 'A', recording details of arrests.
• 'B', recording details of offences not cleared up before being entered into the database.
• 'C', recording details of offences reported and cleared up before being entered into the database.
• 'D', recording clear-ups not matched to a previous offence record.
• 'V', recording details of crime victims.
• 'N', recording allegations classed as 'No Crime'.

In addition to tables for these types of records, the database also included files concerned with the validation of data loaded onto the database and files designed to assist the searching of the database.

How data was originally captured and validated: The ME Crime Statistics datasets are based on data extracted by G10 and PIB from paper crime reports completed by police officers and their supervisors. These reports (known as 'Form 478') recorded details of individual incidents reported to the police. Photocopies of crime report forms, authorised by supervising officers, were required to be sent to G10. Copies of Form 478 were also sent to other branches of the Metropolitan Police. Original forms were retained in 'crime books' at police stations according to whether they related to major crime, burglary, 'beat' crime, vehicle crime, or child protection teams.

Information from crime reports was encoded and input in G10's input section. At the time that the ME System was implemented in 1989, this local system was a Microdata 8000 series. The system replaced an earlier Datapad input system, installed in 1977. The Datapad system had produced paper tape which was sent to Department of Computing Services (DCS) for inputting to the MC Crime Statistics database, supplemented by manually produced punch cards at times of high workload or during processor failures. By contrast, under the Microdata system data was loaded onto magnetic tapes which were sent by G10 to DCS on a daily basis.

One major change that accompanied the development of ME was the introduction of a transaction processing service which allowed users in G10 to input, update, view and amend records; correct errors arising from the daily loading of data onto the database; and conduct ad hoc enquiries of the database and the monthly, year-to-date and historical files. An ICL DRS300 system consisting of a processor stack, printer and four terminals was installed. This was connected to the ME System running on an ICL 3960 at Jubilee House by a MICROLAN communications network. The Microdata system was also able to store up to eight months' worth of reports in its own local database.

Validation checks on the data were performed using the Microdata software at the time of inputting by G10. Further checks were carried out at the time that data was loaded onto the mainframe at DCS.

G10's successor, PIB, acquired a new inputting system based on networked PCs and a server. This system was connected to the mainframe running the database at Jubilee House.

Constraints on the reliability of the data: The datasets derived from the ME Crime Statistics System do not cover unreported crimes, or crimes and allegations reported to the police but not recorded by them, and will be vulnerable to changes in police recording practices and in the level of reporting to the police. Victimisation surveys such as the British Crime Survey (conducted by the Home Office) have been developed to measure the 'true' extent of crime.

All queries intended to produce meaningful statistical results from the YTDEXTRACT files should include selection by record type: a count of all the records in the file without specifying record type will not give the total of all the offences input to the ME System in the year in question.

The significance of certain values relating to Metropolitan Police divisions and subdivisions, Home Office and Metropolitan Police crime classifications, and several other indicator codes in the data remains unexplained by either the metadata or other documentation provided by the Metropolitan Police.

The ME coding system is extensive and exhaustive, and complex relationships exist between the Home Office (HO) and Metropolitan Police (CO) classifications of offences and other indicator codes in the records which are largely undocumented

Care should also be taken when interpreting fields with zero or blank values. In conception, the records in the YTDEXTRACT files and the DBEXTRACT file are effectively divided into four sections:

• 1. Data about the record (record type, dates reported and input).
• 2. Data about the offence reported.
• 3. Data about an arrest.
• 4. Data about a victim.

In the event that an offence is a clear-up, with one prisoner and one victim, all this information can be contained in a single record. Additional arrest or victim records will contain significant data in the section for offence details, and in one other section, according to the record type; information in the remaining sections will be redundant or blank. 'No crimes' in the YTDEXTRACT and the DBEXTRACT file appear as single records, with the 'no crime' type being indicated by the EXT-REC-TYPE and the NO-CRIME fields.

Validation performed after transfer: Details of the content and transformation validation checks performed by NDAD staff on each ME System dataset are contained in the catalogues of individual datasets.

The Crime Statistics System (ME) was designed to replace an earlier version of the Crime Statistics System known as MC. This system also consisted of a database and associated files maintained by the Department of Computing Services (DCS) and, as with the ME System, inputting and analyses of data from paper crime reports were carried out by G10. The replacement of MC by ME was prompted by DCS's desire in the mid-1980s to move its applications from a DME to a VME operating system, and to decommission an ICL 2966 mainframe computer at Jubilee House, Putney which was used for running the MC System. DCS and G10 took the opportunity of the move from DME to VME to revise the structure of the Crime Statistics database. The new database was intended to use ICL's Quickbuild products (ICL ReportMaster, ApplicationMaster, etc), and to reduce maintenance overheads by providing a means for quicker enhancements and amendments to software and inbuilt security through standardised utilities. At the same time, steps were taken to improve G10's ability to access and manipulate crime statistics data through the development of a transaction processing service.

Work on the conversion of DCS's DME applications to VME started in 1985 with two pilot projects involving data on traffic accidents and juvenile bureaux systems. In the following year DCS began work on designing a new Crime Statistics database. At that time the new database was scheduled to be operational by mid-1986. In the event, a series of delays and postponements meant that final implementation of ME does not appear to have occurred until late 1989. A service level agreement relating to the System was signed between G10 and DCS in March 1990. Delays were caused by the following factors: the need to develop new statistical tables as a result of the reorganisation of the Metropolitan Police into eight areas in 1986; changes in G10's requirements regarding the amount of data to be stored in the database; the need to test the new report writing and tabulation packages; and apparent communication difficulties between G10 and DCS. A working party of G10 and DCS officers met for the first time in May 1986 to co-ordinate the conversion of the Crime Statistics System.

It should be noted that the ME System was originally designed as an interim system which would meet G10's needs prior to the implementation of the Metropolitan Police's Crime Report Information System (CRIS). The aim of CRIS was to replace paper crime reports and the central inputting of crime data with a network of dispersed terminals, which would allow police officers to input details of crimes directly to a database and to conduct on-line searches of the data. Development of CRIS started in 1981 and continued throughout the 1980s (£22.3 million were spent on the development of CRIS between 1987/88 and 1992/93). A number of slippages occurred in the system's implementation date. By mid-1988 it was believed within DCS that CRIS would replace the ME System for the production of all new crime statistics in 1990 or 1991, after which ME would be used for about five years solely for access to historical data. In the event, field trials of CRIS did not start until 1992. The system began to be rolled out on an Area by Area basis within the Metropolitan Police beginning in 1994, and finally went live throughout the Metropolitan Police District on 1 October 1996. This meant that the ME System continued to be used for the recording of crime data and the production of crime statistics well beyond its intended lifespan.

As the development of CRIS progressed, G10 and later PIB became increasingly concerned to ensure that CRIS data and data generated on the ME System should be compatible during the transitional period when both systems would be operating simultaneously. During the initial stages of the introduction of CRIS, paper printouts of crime reports produced by divisions using CRIS were input to the ME System by PIB, though this proved to be labour intensive. By 1992 DCS had started work on developing software to allow for the electronic data interchange (EDI) of ME and CRIS data. This required certain modifications to the ME System: initially, a shift from a calendar year to a financial year basis for producing datasets; later, more substantial modifications to the ME database. New software for tabulating data and running ad hoc queries of the redesigned database appears to have been developed by the contractors working on the CRIS system. Some retrospective conversion of data generated on the older version of the database may also have occurred.