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
diagram1.
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 showing2:
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.
BE SURE TO CLEARLY STATE YOUR USER NUMBER IN YOUR
ASSIGNMENT SO
THAT I CAN TEST YOUR QUERIES
� 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:
4. The SQL showing the INSERT statements for each table,
presented as a table as shown here:
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:
Entity Name CREATE TABLE SCRIPT.
PRIMARY KEY
CONSTRAINT
FOREIGN KEY
CONSTRAINTS
UNIQUE
CONSTRAINTS
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.
(From the
logical
diagram)
SQL SQL
SQL SQL
TABLE NAME
SQL INSERT STATEMENTS
Requir
e-
ment #
Requirement
Description
List of T
able(s)
Involved
SPATIAL
JOIN
SQL QUERY
Screenshot of
results from PG
Admin