Data Management代写、代做SQL、代写SQL语言程序、代做UCL

日期：2018-11-24 10:55

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 (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. 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.