Instructions :-
Write a document – e.g., plain text, Microsoft Word or using R Mark down – presenting the answers to the
questions listed below. You should present the answers in the same order as they are listed, each in a separate section of the document, including the code, the output and the textual component as required. The two parts below will contribute to the overall mark for this coursework,as follows:
GY7708 SQL Assignment 1 – UK.
• Part 1: 40%
– evidence of knowledge of principles of database design
– quality of the proposed database design
– correctness of the code compared to the proposed design
– quality of the coding style and commenting
– re producibility
• Part 2: 60%
– complexity of the analysis
– quality of the analytical treatment of the data
– correctness of the code compared to the proposed analysis
– quality of the interpretation of the results
– quality of the coding style and commenting
– reproducibility
Important: If you decide to create a git repository, the repository must be private, and you must follow
the instructions available in the Appendix 2. It is very important that you DO NOT upload any file containing your credentials (database connection details, username and password) to a git repository.
Submit your document through Black Board, using the form linked in the Assessment and Feedback
section. When writing up, remember that the submission should be anonymous. Do not include your
name in the document, and use your Student ID as the author instead. Note that the assignment must be
completed using the SQL programming language.
This assignment uses public sector information licensed under the Open Government Licence v3.0 from the Office for National Statistics and from the London Data store licensed under Creative Commons CC BY 4.0 License, as well as Open Street Map data from Geofabrik GmbH by Open Street Map Contributors licensed under ODbL 1.0 and data derived from Wiki data licensed under Creative Commons CC0 License.
Part 1:
As reported by the BBC, women are still severely underrepresented in public statues in the UK. The Public
Statues and Sculpture Association lists 122 public statues of women. The WikiData knowledge base only
lists 98 items described as statues of women in the UK, but many are not geolocated – i.e., the precise
latitude and longitude of the location are not available.
GY7708 SQL Assignment 1 – UK.
The GY7708_2021-22_Assignment_1–Statues-Wiki data.csv data set available in the data package on
Black baord was created from data available on Wiki data, including information about 54 public statues
listed and geolocated in Wiki Data as statues of women in the UK. It is a small but rather messy data set
including the following columns:
• statue: WikiData ID of the statue;
• statueLabel: name of the statue;
• inception: date of inception;
• lat: latitude of the place where the statue is located;
• lon: longitude of the place where the statue is located;
• place: WikiData ID of the place where the statue is located;
• placeLabel: name of the place where the statue is located;
• placeAdmin: administrative area containing the place where the statue is located;
• depicted: WikiData ID of the person depicted by the statue;
• depictedLabel: name of the person depicted by the statue;
• depictedAltLabel: alternative names of the person depicted by the statue;
• depictedDescription: description provided by WikiData of the person depicted by the statue;
• creator: WikiData ID of the person who created the statue;
• creator Label: name of the person who created the statue;
The data set clearly contains a wide range of redundancies that could create severe anomalies if the data are uploaded to a database as they are, as a single table. The aim of this part of the assignment is to test your understanding of the principles and practices of database design.
• Write up to 500 words describing what those issues are and suggest a structure that would avoid such
issues as far as possible, including table(s), table name(s), columns, types and constraints.
• Working on your database for this exercise:
– write the CREATE TABLE query (or queries) required to create the table(s) as you described in the discussion assigned above;
– write the INSERT queries necessary to insert the data in the table(s).
Part 2 :
Points of Interest (POIs) are a common feature in GIS and cartography, where the term is used to refer to
geographic entities that might be relevant or interesting for the user of a system or reader of the map. The term POI might be applied to different types of entities, from cafes to restaurants, from schools to hospitals.Their representation within a GIS or a map can range from a single point to a multi-polygon, depending on the type of entity and the scale at which they are represented. POIs are commonly understood as a marker of the urban activity present in an area, such as residential, commercial or industrial (see, e.g., Gao et al.2017)1. In this part of the assignment, you will explore the relationship between POIs and their surrounding environment.
This part of the assignment focuses on four tables (available in the shared sds27 database, see below). The first three are greater_london_osm_point, greater_london_osm_line, greater_london_osm_polygon. Those three tables are derived from Open Street Map (OSM) data. The tables contain point, line and polygon objects representing geographic entities in London, as detailed by OSM. The tables include columns representing geometries, IDs, names and objects’ classification, as described in the Map Features OSM wiki page.Within the OSM tables, objects such as university can be encoded as either points or polygons,where the OSM tag amenity has value university. Note that the Geofabrik data set encodes the tags in a slightly different way in the two tables, as in the example shown below. Further information on the OSM and its use of tags can be found on the OSM Wiki pages about Map Features.
• select * from greater_london_osm_points where other_tags = ‘”amenity”=>”university”‘;
• select * from greater_london_osm_polygons where amenity = ‘university’;
The fourth table is greater_london_loac, which contains the London Output Area Classification (LOAC).
The LOAC is a geodemo graphic classification specifically developed for London, similar to and developed
from the same variables used to create the 2011 Output Area Classification (OAC) that we have discussed
multiple times in past lectures. A map of the LOAC can be seen using the CDRC Mapmaker developed by
the Consumer Data Research Centre. A discussion of the LOAC can be found in the report included in the
data pack, or on the London Data store page about the LOAC.
The aim of this part of the assignment is to test your ability to use SQL – possibly in conjunction with other programming languages or tools – to execute a spatial analysis.
GY7708 SQL Assignment 1 – UK.
• Write an introduction (up to 500 words), proposing a spatial analysis aimed at describing the amount
of POIs of the type assigned to you in the borough assigned to you in the Appendix, as well as the urban environment in which they are placed. The analysis should use the four tables above and the SQL spatial functions and operators made available by Post GIS.
• Write the analysis using only SQL queries and include the results in the document. If the results include large tables, please limit the output shown in the document to only a few rows per query.Connect to the shared sds 27 database. Please, use only SELECT operations on the shared database. Do not update, alter, delete or drop information from any table. If you need to create tables, contact the module convener
• Write a final discussion (up to 300 words), presenting the final results of your analysis. In this section,
additional tools such as R, Python, QGIS or ArcGIS can be used to illustrate the final results.
ORDER This GY7708 SQL Assignment 1 NOW And Get Instant Discount
Read More :-
DAT0001B Using Numeracy Data & IT Assessment – Arden University UK.
