Page 1 of 5
Table of Contents
Entity-Relationship Diagrams –Adaptable Suburbs ……………………………… 2
Project Overview ………………………………………………………………………………………………………. 2
Research Outcomes …………………………………………………………………………………………………. 2
System Specification …………………………………………………………………………………………………. 3
Conceptual Diagram …………………………………………………………….. 4
Logical Diagram …………………………………………………………………. 5
Note – this example was used in a previous version of the course and should be
taken ‘as is’ (i.e. it may not match the current version of the course 100%)
Page 2 of 5
Entity-Relationship Diagrams –Adaptable Suburbs
Project Overview
Adaptable Suburbs set out to explore the influence of social interactions and
spatial movement on the economic vitality and adaptability of London’s outer
suburban town centres by studying its spatial evolution, built form change and
shifts in land use patterns since the 19th century in order to draw conclusions on
the factors involved in how high streets adapt and change over time.
The core objective of the project was to develop an understanding of the
workings of small scale, ‘below the radar’ economic and social sub-urban
activities – such as small groups of offices, workshops or shops at the edge of the
town centre. These activities play a vital role in the economic, social and
environmental sustainability of urban areas but since research and policy
intervention focus on regional centres and large projects, there is a lack of
knowledge about these activities, and therefore a lack of policy attention to
them. We have found evidence in our previous work on suburban town centres
that these elements operate at multiple scales – for example, that local centres
emerge through time as an outcome of both local travel to work patterns and
long distance movements. In particular, these activities are not sufficiently
understood in the context of local planning and economic decisions. By
integrating theories and methods from architecture, anthropology, history and
geography, we have created the tools to handle large data sets on street-level
activity within and around town centres and have gained extensive knowledge
and understanding to interpret the spatially related social/economic data in
order to address key questions about the future of the UK urban environment.
Research Outcomes
We were able to test a novel proposition about how centres of socio-economic
activity emerge through time. In order to do this we developed unique methods
for cartographic redrawing of historic street networks and building footprints for
the spatial description and analysis of morphological change over time using
space syntax and urban form analysis. We captured historic business directory
data for three periods from 1890 to 1960 and carried out a survey of current land
uses across four outer London cases: High Barnet, Loughton, South Norwood and
Surbiton to create a spatial database of land uses. The project developed a GIS-
based algorithm to capture in a single analytic framework all these spatially-
related data. This has allowed for a deep understanding of the nature of the high
street as it has changed over the past 130 years and we have published the results
in a variety of outlets, whilst analysis is planned to continue well beyond the life
of the project.
Page 3 of 5
We found that settlement at the urban fringe encompasses patterns of
settlement, economic patterns and housing stock that whilst being
characteristically suburban according to conventional measures of density or
distribution of land uses, nevertheless illustrate the fundamental linked nature
of the city’s larger and smaller centres into a single system. London’s suburbs,
though thought to be places where people live to simply commute into the city,
are entities in their own right. Whilst at the start of their emergence as suburban
settlements in the 19th century, these communities were largely self-sustaining,
today they constitute embedded entities within the wider urban network.
The analysis demonstrated the importance of adaptability: of the street
network, of the buildings and of the land uses within them. A butcher’s shop may
have been in that location for over 100 years, though the name or ownership may
have changed; thus, proving a continued need for this keystone establishment in
the community. Alternatively, depending on the building size and shape,
businesses can easily move in or out, contributing to the shifting diversity of non-
domestic activity in the area as it responds to social and economic change. The
analysis also highlighted how certain pathways in the street network lends
themselves to particular classes of activity – a form of path dependence that
evidently relates to how accessible they are to local residents.
System Specification
A University research project is investigating the sustainability of the town
centre areas for four suburbs of London, by looking at their current and historical
road network layout, buildings and non-residential activity (e.g. shops, schools,
churches, banks, doctors and so forth). Non-residential business types are
categorised into four types: banking and finance, shops, manufacturing and
‘other’. Data for four time periods – 1880, 1910, 1950 and 2013 is to be stored in
a database. As well as being interested in changes for each town centre, the
researchers are also interested in changes for each street segment within the
road network (where a segment is defined as the portion of the street between
one intersection and another).
Once captured, the data will allow the researchers to conduct analysis to answer
the following questions:
1. How many business points are within the boundary of each suburban town
centre?
2. How many business points are there in each category?
3. How many business points are there for each street segment?
4. How many business points are there along each street?
5. What is the total area of buildings in each category?
6. What is the total area of buildings along each street segment?
7. Are there any streets that don’t have segments?
Page 4 of 5
You can assume that:
– One building is only ever adjacent to one street segment (i.e. even if a
building is on a corner, it has a front door onto one segment)
– -All building polygons are sourced from either current Ordnance Survey
Mastermap OR by digitising historic map data
– All non-residential property information is sourced either through on
street survey (for 2013 data) or from historic business directories
– All street network data is sourced from Ordnance Survey ITN data (for
the current dataset) and by manually erasing non-existing street
segments from the current data to create historic road networks
– The four years given above are approximate and the actual date of the
historic maps or business directories may vary.
Conceptual Diagram
Page 5 of 5
Logical Diagram
SQL
– See separate script file
– All scripts are in one file (from 2018 onwards use separate files!)
– The file includes some general example SQL statements as well as the
SQL to answer the functional requirements
– The insert scripts for the Adaptable Suburbs example have not been
designed to run as one single script – you need to insert data into each
table separately