Database Design and Creation



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

a. Take the conceptual database diagram you created for the previous project 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 project, 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 project 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 project 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 project 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



(From the logical diagram)

SQL

SQL



SQL

SQL



TABLE NAME



SQL INSERT STATEMENTS





Require- ment #

Requirement Description

List of T able(s) Involved



SPATIAL



JOIN



SQL QUERY



Screenshot of results from PG Admin

NB: The text here should be identical to that in project 1

These should correspond to the entities listed in project 1

Yes/No (identical to project 1)

Yes/No (Identical to project 1)

The SQL Query required to answer the requirement

If the SQL failed insert the error message. If you were unable to meet the requirement, leave blank.

TOTALS



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.