数据库作业代写/database代写/sql代写：这是一个database设计方面的项目，包括ER图绘制，sql代写等典型的数据库设计工作
Page 1 of 4
Assignment 1 – Spatial Data Management
Page 2 of 4
– Database Design and Creation
Overvew
The first assignment on this module 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 diagram (CONCEPTUAL).
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 volume1
).
Additionally, it MUST include five spatially based functional requirements
and three requirements that need to join information from more than one
entity to be answered.
NB: The system should NOT be similar to the examples we covered in class.
Part A – Topic Description
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,
highlighting key entities in bold text (see Appendix 1 for an example).
2. 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 10 2 1
3. Include a table with a list of 10 functional requirements that your system
will meet. You MUST clearly show which requirements involve spatial
queries and which involve joins. You should use 3 join operations (i.e.
between two or more entities) and 5 different spatial operations – e.g.
distance, area, volume, contains/within, intersects, neighbour, overlap,
union, touch, perimeter calculation and so forth2
. To demonstrate this
you should submit a table using the following format:
1
In GIS, a 0-D entity is a point, a 1-D entity a line, a 2-D entity a polygon and a 3-D entity a
polyhedron. Your 3D entity MUST enclose a volume – for example a shop, warehouse, office
building, pipeline, hut and so forth would meet this criterion.
2
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).
Page 3 of 4
Requirement # Requirement Entity or
Entities
Required
Spatial
Query
Join
1 Which Bus Stands are
along the A1 road and
who is responsible for
cleaning them
3D_Stands
Cleaning Staff
Yes Yes
2 What is the total
surface area of all Bus
Stands
3D_Stands Yes
… … … … …
TOTALS 10 N/A 5 3
Part B – Conceptual Design
You are required to carry out the following task for your selected system:
Create a FULLY DOCUMENTED UML Entity-Relationship diagram for the
database to be used with your proposed system. The diagram should be
CONCEPTUAL
The report describing your diagram should be structured as follows:
a. The E-R diagram.
b. Full Documentation for the E-R diagram, including
i. Descriptions for each entity and relationship, including
cardinality of relationships and of attributes and identifiers
ii. Descriptions of any business rules and integrity constraints
associated with the diagram
iii. Any derivations, information calculated from stored data or
mathematical formulae that are required to fulfil the
system brief
iv. Any assumptions made.
c. Appendix 1 – 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 4
Some hints:
• The total assignment should be no longer than 8 pages, which does not
include any appendices.
• When you have drafted your E-R diagram and report, be sure to doublecheck
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).
• Your system should not be too complex – limit yourself to between 8 and 10
entities in total.
• You will be assessed on the quality of the documentation as well as on the
E-R diagram itself – 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.
• You should not use the examples we used in class or the example given
below as any part of your E-R diagram. Marks will be deducted from both
this assignment and assignment 2.
Appendix 1 – Example System Specification
London Transport Maintenance Ltd. is newly contracted to provide
maintenance services for transportation infrastructure in different boroughs in
London. They have identified a requirement for a system to track bus stop,
bus shelter and bus stand maintenance to ensure that this are carried out to
the correct specification in terms of their contract.
The terms bus stop, bus shelter and bus stand are defined as follows:
• Bus stop – a simple stop showing details of routes and route time-tables,
but not having any shelter.
• Bus shelter – a bus stop with an additional weather shelter.
• Bus stand – the start or end terminus of a route, with all features of a
bus shelter and additional facilities for drivers (3D).
Under the terms of their contract, they are required to inspect each bus stop,
stand or shelter on a weekly basis, and perform monthly cleaning of any
signage. They employ 20 cleaning staff to carry out this task, with each staff
member being assigned to specific bus stops – i.e. one person will always
maintain the same stops, stands or shelters. This team log any details of
required repairs on the central system. Each repair is assigned a priority of
normal, urgent or very urgent.
The resolution of any issues reported by the inspection and cleaning teams is
carried out by a maintenance team, which can be deployed to any stop, stand
or shelter as required. There are 10 staff on the maintenance team.