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 1 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.
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 2 of 10
case study sourced online to provide ideas for Part A1, make sure to make this very clear in
If you do use a
your assignment so that you aren’t accused of plagiarism.
Once you have selected your system,
1. Writeasystemspecificationdocument(maximumlength1page)forthe
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.
Include a table listing the entities in your system and indicating whether they are spatial and 3D. This table should have the following format:
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:
more than two entities from one of the example diagrams, marks will be
deducted.
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
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 3 of 10
Specifically, if you use
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. CreateaUMLEntity-Relationshipdiagramforthedatabasetobeusedwith 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 4 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 5 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. CreateanSQLscript-thatincludestheSQLusedtocreatethetablesinyour
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. CreateaseparateSQLscripttopopulateeachtablewithaminimumofTHREE 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.
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.
create table xxx.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 6 of 10
Entity
Create Table Script
Image taken from the logical diagram
SQL
All the SQL should work in your schema – e.g. your create table scripts should be
similar to:
ucfs
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 7 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 8 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 9 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
A4 – Database Creation
Item to Check
Week Taught
Logical diagram
Example in Week 2 and additional examples on moodle
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.
Page 10 of 10