sql代写 Assignment 1 – Spatial Data Management

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 (75%)

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.

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):

  1. Descriptions for each entity and relationship, including cardinality of relationships and of attributes and identifiers
  2. Descriptions of any business rules and integrity constraints associated with the diagram
  3. Any derivations, information calculated from stored data or mathematical formulae that are required to fulfil the system brief
  4. 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

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:

Page 7 of 10

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

A2 – Conceptual Diagram

A3 – Logical Diagram

A4 – Database Creation

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