RAE database table schemas

These are the 'schemas' of the tables used in the rae data scripts. They shown in the form used to create them in MySQL.

rae_raw

This has the complete data in one table. It is 'unnormalised' that is it has not been optimised to remove repeated or redundant information. For example it contains the full name of the institution, which is repeated for every unit of assessment within the institutaions. Also the full name of each unit of assessment is repeated everytime it occurs.

This table has one row for each assessed unit of assessment in each institution. that is it will have a row for Computer Science in Lancatser, a row for Psychology in Lancaster, a row for Computer Science in Glasgow etc. As well as the institutions name and HESA code (inst_name, HESAcode), it includes the name and code for the unit of assessment (UOA_name, UOA), the research rating (1,2,3b,3a,4,5,5*) of the unit (rating), the proportion of staff entered (prop) and the number of staff entered (RAS_FTE). There are also a few fields that are not used in this example (MSL, MS_NAME and flagged), and a numeric i dentifier for the table row (id).

CREATE TABLE rae_raw ( HESAcode varchar(8), inst_name varchar(80), UOA varchar(4), UOA_name varchar(80), MSL varchar(8), MS_name varchar(80), rating varchar(4), prop varchar(4), RAS_FTE float, flagged varchar(100), id PRIMARY KEY auto_increment );

rae_res

This is identical to rae_raw except that has been partially 'normalised'. Instead of putting the full named of the institution and unit of assessment in each row of the table, only the HESA code of the institution and the UOA code are included. these can then be looked up in the tables rae_uoa and rae_inst below. The unused elements have also been separate into another table not listed here.

CREATE TABLE rae_res ( id int(11) PRIMARY KEY auto_increment, HESAcode varchar(8), UOA varchar(4), rating varchar(4), prop varchar(4), RAS_FTE float );

rae_uoa

This is just a lookup table giving the name of each unit of assessment. For example UOA 35 is Geography. (See complete list of UOA names).

CREATE TABLE rae_uoa ( UOA varchar(4) PRIMARY KEY, UOA_name varchar(80) );

rae_inst

Similar lookup list for institutions, but this also includes additional informnation that is about the institution as a whole: RAS_FTE and NS_FTE, which areabout the total number of staff counted in the RAE, and R_1 to R_5s which give the number of units of assessment graded 1, 2, 3b, 3a, 4, 5 and 5*. This was calculated from the raw rae data when this table was created.

CREATE TABLE rae_inst ( HESA_code varchar(8) PRIMARY KEY, name varchar(80), RAS_FTE float, NS_FTE float, R_1 float, R_2 float, R_3b float, R_3a float, R_4 float, R_5 float, R_5s float, );