Assignment 2 – Spatial Data Management
Submission Method:
Paper copy to be submitted to the Departmental Office.
Digital copy to be submitted via Turnitin (as a zip file, including the
document and SQL scripts)
This assignment has two parts: you are required to implement the system you
designed in the first part of the assignment, as well as to carry out a small
piece of research into NoSQL databases OR to carry out research into 3D
functionality in GIS.
This assignment is worth 60% of the marks for the module. 30% of the marks
for this assignment are allocated to the research project (3D or NoSQL), and
70% to the physical database creation and query process.
Notes:
- An assignment is an independent piece of work
- If you have questions about this assignment please post them on Moodle:
- 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 expected
- 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.
Database Design and Creation
BE SURE TO CLEARLY STATE YOUR USER NUMBER IN YOUR ASSIGNMENT SO
THAT I CAN TEST YOUR QUERIES
Your answer should be structured according to the guidance below – failure to
do this will result in lost marks – you should include a clear table of contents
showing the page number for each of the required sections
Part A –Database Creation
For this part of the assignment:
a. 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 diagram
b. Once you have the logical model, you should then write SQL scripts to
create your tables and add the required constraints. The SQL scripts
should be created MANUALLY and submitted as part of the assignment,
and you must use the PostGIS database provided for this work.
c. Once you have created the tables, populate each table with a minimum
of THREE rows of data (you MUST use SQL to do this manually – you are
not permitted to import the data from other sources). The data
should be sufficient to allow you to test out the SQL for your listed
functional requirements.
d. Create and test the SQL statements for each of your functional
requirements.
You are required to hand in a report showing
1. Your conceptual UML diagram from Assignment 1 (make sure to copy the
diagram exactly as you submitted it).
2. The logical UML diagram derived from the conceptual diagram. Make
sure this diagram is derived directly from the conceptual diagram you
presented.
3. The SQL scripts you used to create and populate the tables (the tables
and data should also be created inside your PostgreSQL work area)
1
NB: Do not introduce any new entities or fields into the logical diagram, apart from those
specifically required by the translation process.
2
Note – there is no need to write any text to explain the different elements this report – just
be sure that you stick EXACTLY to what is asked for.
Page 3 of 4
For each table, you should include the entity on which it is based
alongside the SQL script. that you used to create the table and any
constraints. This should be done in a table as follows:
Entity Name CREATE
TABLE SCRIPT.
PRIMARY KEY
CONSTRAINT
FOREIGN KEY
CONSTRAINTS
UNIQUE
CONSTRAINTS
(From the
logical
diagram)
SQL SQL SQL SQL
4. The SQL showing the INSERT statements for each table, presented as a
table as shown here:
TABLE NAME SQL INSERT STATEMENTS
5. A map (created in QGIS) of any spatial data you have as part of this
exercise.
6. A 3D screen shot (from FME) of your 3D data, with appropriate
background mapping.
7. A table showing the list of Functional Requirements from Assignment 1
(unchanged) and the SQL you used to answer each requirement,
structured as follows:
Require-
ment #
Requirement
Description
List of
Table(s)
Involved
SPATIAL JOIN SQL QUERY Screenshot of results
from PG Admin
NB: The text here
should be identical
to that in Assignment
1
These should
correspond
to the
entities
listed in
Assignment 1
Yes/No
(identical
to
Assignment
1)
Yes/No
(Identical
to
Assignment
1)
The SQL
Query
required to
answer the
requirement
If the SQL failed insert
the error message. If
you were unable to meet
the requirement, leave
blank.
TOTALS
Remember that 3 join queries and 5 spatial queries are required.
NB: All SQL should be included in the report, but also uploaded as a separate
single text file so that the database creation process can be re-run from
scratch.
For Part A you will be assessed on your ability to create a database using all the
principles we covered in class over the course of this module and on how well
that database fits your original specification from Part 1 and answers the
functional requirements you set out.
Part B Option 1 – 3D GIS Support in GIS Software Packages
Write a short research piece (750 words) describing the support offered by ONE
commercial GIS package (e.g. MapInfo Professional or Geomedia 3D) for 3D
functionality. You should not use ArcGIS or ESRI products for this assignment.
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 (750 words) describing the support offered by ONE
NoSQL database for spatial functionality. You should not use MongoDB for this
assignment (as we have covered this in class). 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.
Part B Option 3 – Use of Spatial Databases in Asset Management
Write a short research piece (750 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.
Examples of issues you may wish to consider include (but are not limited to):
emerging Building Information Modelling techniques and trends in your answer
and how spatial databases would fit in this context; data protection issues such
as the emerging General Data Protection Regulation - how can databases and
spatial databases help meet the requirements of this regulation?
For Part B, you will be assessed on the clarity of your writing, appropriate use
of referencing as well as your ability to understand and document support
offered by your chosen software package.