Page 1 of 10
Assignment 1 – Spatial Data Management
Submission Date:
Monday 10th December 2018 at 12:00 midday
Submission Method:
Digital copy to be submitted via Turnitin via the five links provided
o A single PDF or Word document of your report, maps, sketches
and essay
o Four separate SQL scripts
Note that I may also require you to use an additional online submission
process for the SQL scripts – I will let you know if this is the case.
This assignment is worth 100% of the marks for the module. Part A contributes
75% of the marks for this assignment, with Part B contributing 25%.
Notes:
– An assignment is an independent piece of work – your assignment should
not be identical to, or similar to, that of any other student or the work we
do in class. If two assignments are very similar we will follow UCL
procedures for plagiarism – see the guidelines here:
https://www.ucl.ac.uk/students/exams-and-assessments/plagiarism
– If you have questions about this assignment please post them on Moodle –
final deadline for questions is 5 days before submission:
– That way everyone is given the same information
– That way I remember what I’ve said to you and don’t mark you down for
doing something that I wasn’t expecting
– Any questions should be generic – as this is an assessment which will
gauge how much you’ve learned during the module I won’t be able to
solve very specific assignment-related problems for you.
– This is a digital submission – it is up to you to ensure that the files you
upload to Turnitin are not corrupt in any way (you might be able to do this
by downloading the uploaded files to check them)
Page 2 of 10
Part A – Database Design and Build (78%)
Overview
The part of the assignment involves the selection of a system (topic) of your
choice, which you will then describe and for which you will create a documented
UML E-R diagrams (CONCEPTUAL and LOGICAL). You will then create the physical
database for your system.
This component of the assignment is divided into four parts:
– A1 – Topic Description
– A2 – Conceptual Diagram and documentation
– A3 – Logical Diagram
– A4 – SQL
NB: You can start work on this assignment after the lectures in Week 2, when
we will cover Conceptual and Logical Diagrams. Do not leave it until the last
minute.
Part A1 – Topic Selection and Description (3 pages total including
system specification, sketch map and 2 tables)
Role: you should put yourself in the role of a ‘client’ who needs a database to
be developed for their work or other activity.
You are required to select a system (topic)1 and then write a short system
specification document that will be used by the database developer to design
and develop the database.
Your system may relate to your work, MSc degree, research or another area of
interest but MUST include at least two entities requiring spatial data storage
one of which must be a 3D entity (i.e. a spatial entity that has a volume2).
Additionally, it MUST include two spatially based functional requirements and
two requirements that need to join information from more than one entity to
be answered3.
1 To give you an idea of the types of topics that you might think about, in the past we have had
assignments relating to mountain rescue huts, zoos, cycle hire, logistics/delivery organisations,
lost property offices, forest maintenance, water supply companies and many more. It might be
a good idea to think about something you are familiar with – e.g. a hobby, something from
previous work experience/internship experience, a school or university club. This will help
your system to be more realistic. You may also find some case studies online using search
terms such as built asset management, GIS, key performance indicator, KPI. If you do use a
case study sourced online to provide ideas for Part A1, make sure to make this very clear in
your assignment so that you aren’t accused of plagiarism.
2 Your 3D entity MUST enclose a volume – for example a shop, warehouse, office building,
pipeline, reservoir, hut and so forth would meet this criterion.
Page 3 of 10
Once you have selected your system,
1. Write a system specification document (maximum length 1 page) for the
system. This should include an overview of what the system will do, and
who will use it.
2. Decide which entities you are going to include in your system – you should
not use entities similar to those we cover in class. Specifically, if you use
more than two entities from one of the example diagrams, marks will be
deducted. Include a table listing the entities in your system and indicating
whether they are spatial and 3D. This table should have the following
format:
Entity # Entity Name Spatial 3D
1 Bus Stands Yes Yes
2 Road Network Yes No
3 Cleaning Staff No No
… … … …
Totals Minimum of 5,
maximum 8
Minimum of 2 Minimum of 1
3. Draw a sketch map – on graph paper – of your site (if this is a real world
system simplify the map). You can use local coordinates or British
National Grid numbers.
4. Include a table with a list of 8 functional requirements that your system
will meet. You MUST clearly show which requirements involve spatial
queries and which involve joins. You should have at least 2 ‘advanced’
functions that take information from more than one entity and 2
additional ‘advanced’ functions that use different spatial operations – e.g.
distance, area, volume, contains/within, intersects, neighbour, overlap,
union, touch, perimeter calculation and so forth45. To demonstrate this
you should submit a table using the following format:
Requirement # Requirement Entity or
Entities
Required
Spatial
Query
Join Advanced
1 Which Bus Stands are along
the A1 road and who is
responsible for cleaning
them
3D_Stands
Cleaning Staff
Yes Yes Yes
2 What is the total surface
area of all Bus Stands
3D_Stands Yes Yes
… … … … …
TOTALS 8 N/A Minimum
of 2
Minimum
of 2
Minimum
of 4
4 A full list of the operations available in PostGIS can be found here:
http://postgis.net/docs/reference.html#Operators (Sections 8.9 – 8.11).
5 While you can use multi-entities in your spatial functions or spatial functionality in your
multi-entity functions you MUST have 4 separate ‘advanced’ functions in total. If you have
more, there is a chance for bonus points – see marking scheme below.
Page 4 of 10
Some hints for Part A1
Your system should not be too complex – limit yourself to between 5 and 8
entities in total.
Be careful about using very similar functional requirements. For example if
you have ‘find the average monthly water level in a given town’ and ‘find
the maximum monthly water level in a given town’ – these are very similar
so won’t score highly in the ‘advanced skills’ box.
Your functional requirements should be generic – so ‘find the monthly water
loss in the system for a given town’ would score better than ‘find the
monthly water loss in the system for London’ – generic requirements mean
that the system is able to grow – even though your initial system might be
designed for London, can you also show that it would work if used in
additional cities?
Part A2 – Conceptual Design (conceptual diagram, documentation,
rough notes – 6 pages including the diagram but excluding the
Appendix)
Role: you should put yourself in the role of a database designer and developer
who is bidding for the work described in the system specification. You need to
write a document that shows the client that you understand their system,
making sure that the document is not too technical as the client is not a
databases expert. As this is a bidding scenario you must stick to the required
document format or your bid will be automatically rejected.
To achieve this, you are required to carry out the following task for your selected
system:
1. Create a UML Entity-Relationship diagram for the database to be used with
your proposed system. The diagram should be CONCEPTUAL
2. Write a report providing full documentation for the diagram, structured
as follows (use separate headings for each one):
a. Descriptions for each entity and relationship, including cardinality
of relationships and of attributes and identifiers
b. Descriptions of any business rules and integrity constraints
associated with the diagram
c. Any derivations, information calculated from stored data or
mathematical formulae that are required to fulfil the system brief
d. Any assumptions made.
3. Appendix – Rough notes and sketches made whilst drafting the E-R
diagram.
All of the above should be presented under separate headings and sub headings
in the report.
Page 5 of 10
Some hints for Part A2:
When you have drafted your E-R diagram and report, be sure to double-check
that the proposed system actually answers the questions that are being asked
(meets the functional requirements).
Remember that the report you produce is aimed at management – be careful
to explain things very clearly and think about overall presentation of the
report.
The diagram MUST use the E-R diagram notation we used in class (i.e. UML).
Remember that this document should be aimed at a management reader so
be careful not to use terminology that may not be understood, and to write
clearly.
Assumptions need to be realistic – e.g. “a property never changes owner” is
not realistic, neither is “a train is only ever repaired by one person” (what
about holidays) or “a bus is only ever driven by one driver”
Even if you come from a country that regularly uses IDs or similar approaches
to identify people (or land parcels or other objects) you cannot assume that
these are never faked/forged/duplicated by mistake. No IDs should be
included in the conceptual diagram.
Part A2 should be no longer than 6 pages (managers don’t have time to read
long documents) – think carefully about how you can describe the required
information within this limit.
Part A3 – Logical Diagram (1 page, diagram only)
Role: you should put yourself in the role of a database designer and developer
who has won the work to develop this database (well done!) and must now
deliver the system.
Take the conceptual database diagram you created for the previous assignment
and transform it into a logical model, presenting this model as a second UML
diagram6. For this component of the assignment, you are required to:
1. Include the logical UML diagram derived from the conceptual diagram in
your report. Make sure this diagram is derived directly from the
conceptual diagram you presented
6 NB: Do not introduce any new entities or fields into the logical diagram, apart from those
specifically required by the translation process.
Page 6 of 10
Part A4 –Database Creation (1 table, 2 screen shots + 4 script files)
Role: you should put yourself in the role of a database designer and developer
who has won the work to develop this database (well done!) and must now
deliver the system.
For this part of the assignment:
1. Create an SQL script – that includes the SQL used to create the tables in your
system. Call this script: createtable_ucfsxxx.txt (where ucfsxxx is your UCL
username)
2. Create a separate SQL script for all the constraints. Call this script:
createconstraints_ucfsxxx.txt
3. Create a separate SQL script to populate each table with a minimum of THREE
rows of data. The data should be sufficient to allow you to test out the SQL
for your listed functional requirements. So that we can test your work
independently, your SQL must create ALL the data required from scratch –
don’t use any data that has been imported via QGIS / sourced from third
parties. The spatial data you create does not need to be very sophisticated
in terms of geometry complexity. Call this script insertdata_ucfsxxx.txt
4. Create an SQL script that includes the SQL that answers each of your
functional requirements7. Call this script
functionalrequirements_ucfsxxx.txt
5. Submit a table showing how the SQL for the CREATE TABLE scripts links to
the logical diagram, as follows.
Entity Create Table Script
Image taken from the logical diagram SQL
6. Include a map or screen shot (created in QGIS) of any spatial data you have
inserted.
7. Include a 3D screen shot (from FME) of your 3D data.
Some hints for part A4
The SQL scripts should be manually created – i.e. typed by you
You must use the PostGIS database provided for this work.
All the SQL should work in your schema – e.g. your create table scripts should be
similar to: create table ucfsxxx.buildings (……), your insert scripts insert into
ucfsxxx.buildings and queries select from ucfsxxx.buildings
Include comments in your scripts to make them easy to read – use — to mark the
comments
7 We will run the queries in this script one by one so we don’t expect this script to run as a single script.
Page 7 of 10
Marking Scheme for Part A
Marks will be awarded as follows:
Component Maximum
Mark out
of 78
Comment
Following instructions – i.e. using correct section
headings, providing separate SQL files, using the
correct schema name in your scripts, using the
correct number of entities, spatial entities,
functional requirements, joins, spatial entities,
inclusion of assumptions and derivations sticking to
the page limit.
3 Mark only awarded if ALL of the instructions are followed. 0 if
any of the instructions are not followed.
System description – including the originality of your
topic, which must not be the same or very similar to
that of any other student or to work we did in class.
7 Make sure you provide the tables listing the entities and
functional requirements exactly as specified above.
Conceptual Diagram 8 Must include all the required components and use the correct
notation.
Quality of the documentation – in particular the
clarity of your system description and whether you
are able to communicate to a non-technical
audience, but also the inclusion of realistic
assumptions and derivations.
4
Logical Diagram 5 Directly translated from the conceptual diagram.
SQL – CREATE TABLE 2 0 if the script does not run as an entire script OR you do not use
the correct schema.
SQL – CONSTRAINTS 2 0 if the script does not run as an entire script OR you do not use
the correct schema. This means that if your CREATE TABLE
script has not worked, you could also get 0 for this component.
SQL – INSERT DATA 5 0 if the script does not run as an entire script OR you do not use
the correct schema. This means that if your CREATE TABLE or
CONSTAINTS scripts have not worked, you could also get 0 for
this component.
Page 8 of 10
SQL – QUERIES and correspondence between the SQL
and the conceptual and logical diagram, does the
SQL actually meet the functional requirements. 8
queries in total.
20 2 marks deducted for each simple query that doesn’t work. 3
marks deducted for each spatial or multi-table query that
doesn’t work. The queries must also correspond directly to the
functional requirements set out in this assignment – i.e. there
should be a direct path from functional requirements through
conceptual and logical design and to physical database build. NB:
If the above DDL and DML scripts have not worked, you could
get 0 for this component.
Queries should not be hard coded to specific ID values as these
are arbitrary – e.g. select * from rooms where building_id = 3
would not count. You should use select * from rooms where
building_id = (select building_id from buildings where
building_name = ‘Chadwick’)8.
You should not include the ‘interim’ queries (i.e. the queries you
used to build up the final query) in your answer – each functional
requirement should be answered by a single query.
Map and FME visualisation 2
Use of advanced skills – e.g: use of JOIN queries
rather than sub queries to link data between two
tables; including more than two JOIN or spatial
queries to answer the functional requirements; using
multiple JOINs in a single query; use of indexing;
additional use of spatial queries/functionality; use
of SQL beyond that covered in class; level of
complexity/realism of the overall system.
20 You are NOT required to do any normalisation for this assignment
– the systems you are developing are too small. You are also not
required to develop triggers. If you create any indexes these
should be added to the CONSTRAINTS script.
8 For example, if the functional requirement states: ‘which station are most cycles taken from’ – you need to provide
the station name not the Station ID (the ID value is meaningless to a user of the system). Similarly a requirement that
states ‘Track where engineer 33 works’ is not helpful as no one knows who engineer 33 is. So a better option would be
‘track where engineer Joe Bloggs works’ and even better would be ‘Track which engineer works where’
Page 9 of 10
Part B – Research Essay (500 words, 22%)
Part B Option 1 – 3D GIS Support in GIS Software Packages
Write a short research piece (500 words) describing the support offered by ONE
commercial GIS package (e.g. ArcMap, ArcGIS Pro, MapInfo Professional or
Geomedia) for 3D functionality. You should make use of the literature available
for the software and compare the functionality on offer to that which would be
expected from a 2D GIS.
Part B Option 2 – Support for Spatial Functionality in NoSQL
Databases
Write a short research piece (500 words) describing the support offered by ONE
NoSQL database for spatial functionality. You should make use of the literature
available for the software and compare the functionality on offer to that which
would be expected from a spatially enabled relational database such as PostGIS.
Part B Option 3 – Use of Spatial Databases in Asset Management
Write a short research piece (500 words) describing existing and potential uses
of spatial databases within an Asset Management context. Your answer should
reference appropriate literature (journal articles citing case studies, articles
written by professional bodies regarding best practice in asset management,
published case studies) and clearly articulate how additional use of such
databases could enhance digital innovation in asset management practice.
Part B Option 4 – Spatial Databases and GDPR
Write a short research piece (500 words) providing some background to GDPR
and its requirements – what it is, who is impacted by GDPR and so forth. Explain
how using a spatial database could help an organisation meet its GDPR
requirements and discuss whether the location component of spatial data should
be considered as personal information in this context.
For Part B, you will be assessed on the clarity of your writing including: good use
of English, good essay structure, use of headings, paragraph structure, use of
relevant reference material, appropriate citation style, use of diagrams.
Page 10 of 10
Submission Checklist
Make sure you include the following in your submission:
A1 – Topic Selection and Description
Item to Check Week Taught
System specification Individual work – up to you to choose
and describe a system
Table listing entities Example of entities in Week 2,
additional examples on moodle
Sketch map Example in Week 2
Table listing functional requirements Example of FRs in Week 2 and
additional examples on moodle
A2 – Conceptual Diagram
Item to Check Week Taught
Conceptual diagram Example in Week 2 and additional
examples on moodle
Documentation for the diagram, with headings
as specified
Brief discussion in Week 2 – however up
to you to decide how best to describe
the diagram to a non-technical
audience
Appendix – rough drawings Created as you develop your diagram
A3 – Logical Diagram
Item to Check Week Taught
Logical diagram Example in Week 2 and additional
examples on moodle
A4 – Database Creation
Item to Check Week Taught
CREATE TABLE scripts Week 3 (basic SQL), 5 (spatial SQL)
CONSTRAINTS scripts Week 3
INSERT statement scripts Week 3 (basic SQL), 5 (spatial), 6 (3D)
Query scripts Week 3 (basic SQL), 5 (spatial), 6 (3D)
Table showing logical entity and
corresponding SQL script
Week 2 (logical entity) 3 (basic SQL), 5
(spatial SQL)
Map Week 5
FME screenshot Week 6
B – Essay
The advanced topics lectures (Week 7 onwards) may cover some related
material, but this is an individual piece of research so you can start work on it
immediately.