Reference no: EM132992773

ITECH2004 Data Modelling - Spatial and Document Database Assignment

Learning Outcome 1: Interpretation of business rules from additional information for case study;

2. Knowledge acquisition with respect to Spatial and Document Databases:

a. Creation of a spatial database and a document database;

b. Creation of spatial data within that spatial database and creation of document data within the document database;

c. Querying of that spatial and document data.

Knowledge:

K4. Design a relational database for a provided scenario utilizing tools and techniques including ER diagrams, relation models and normalization

K5. Describe relational algebra and its relationship to Structured Query Language (SQL).

Skills:

S1. Interpret entity-relationship diagrams to implement a relational database.

S2. Demonstrate skills in designing and building a database application using a commercially available database management system development tool.

S3. Use a query language for data manipulation.

Application of Knowledge and Skills:

A1. Design and implement a relational database using a database management system.

A2. Utilise a query language tools and techniques to obtain data and information from a database.

Assignment Requirements

Overview
Students are expected to read the case study further details part A and then interpret that description in terms of implementing the changes to their existing Postgres database. Students then need to:

1. Install the PostGIS spatial database software (if not already completed during week 7);

2. Extend their own existing assignment 1 PostgreSQL relational database to a PostGIS spatial database;

3. Write SQL commands in pgAdmin4 to create spatial data in the extended database;

4. Write spatial queries using SQL;

Students need to read the case study further details part B and then:

5. Install the MongoDB document database software, MongoDBCompass GUI and associated software (if not already completed in week 8);

6. Understand the embedded schema proposed and understand the examples given in Appendix A:

7. Use MongoDBCompass or mongosh to:

a. Create a document database;

b. Create a collection in that document database;

c. Create documents within that collection;

d. Write MongoDB commands to populate the collection;

e. Write MQL to query the database.

It is a requirement of this assignment that students use Postgres/PostGIS and MongoDB for the database components of this assignment.

The submission must be presented in the format of a professional report. Further information is given in the Detailed Requirements and Marking Criteria sections of this document.

Case Study - Appliance Deliveries System - Further Information
The following information should be read in conjunction with the case study information from the first assignment.

You have decided after analyzing the information from assignment 1 and completing that work by implementing a database with test data (your work for assignment 1), that you should make additional changes to the original database you designed.

Part A

You believe that spatial information could be very important to the operations of AppliancesToYourDoor. They could use this information for customer analysis, delivery analysis and other parts of their business. To introduce the concept you decide to provide an example in the existing database. You suggest that the warehouse table should be populated with spatial data and as a starting point, you decide that an outline of the warehouse itself, the location of the nearest weighbridge and a location and elevation for the office of the warehouse is needed. You decide to implement these changes and then demonstrate some of the functionality available with this information and hope that AppliancesToYourDoor can then see the benefit in other areas of their business and would ask to you develop this spatial aspect further. At the moment though you are only going to concentrate on the warehouse as your spatial focus. To that end you determine, using Google Earth the office, warehouse and weighbridge points for warehouses for three of the eastern seaboard warehouses.

Part B

You also understand that AppliancesToYourDoor feel they have a data requirement that hasn't been met. Deliveries are conducted according to a schedule and this was explained in the case study for the first assignment. What was missed though was a record of the log of events that occur on a delivery. Drivers and jockeys do other things besides deliveries. They might, for example have mechanical issues, fill up with fuel, have other short breaks and meal breaks or they might contact the office about some issue or other. At present, each truck has a log/diary that the driver and jockey are supposed to fill in but some employees don't do this and some keep notes on their company supplied mobile phone instead. They also often take photos for installation purposes and for other information. AppliancesToYourDoor are frustrated about all the various bits and pieces of information that they need to access to know what happened on a delivery schedule and want some sort of standardized data repository that they can update and query.

You determine that this log of events would be a good candidate for recording in a document database. You also know of an example logging app that could be installed on each employee's company supplied mobile phone. This app allows real time download of the logged information but users can perform a bulk download at the end of the schedule if they need to. You know that you can configure the app to meet the needs of the logging but that is not your focus now. You want to understand the data needs of this functionality so you have collected some sample information of some delivery schedule logs and based on this have decided the data could be arranged and stored in the following manner:

{_id: <scheduleID>, vehicle:<vehicleID>,
driver:<the driver's employeeID>, drivername:<the driver's full name>, jockey:<the jockey's employeeID>, jockeyname:<the jockey's full name>,
deliverytype:<the description of the type of delivery i.e.one of "Suburban", "Inner Regional', Regional Outer", "Regional Remote" and "Regional Extreme">,

date:<the date of the schedule>,

warehouse:<the warehouseID where the delivery schedule started>, departuretime:<time departed warehouse>, departurecomment:<any comments at start of journey>, finishtime:<time returned to warehouse>,
finishcomment:<optional information about how schedule went or issues encountered>, logdetails: <an array of event objects for the schedule showing what happened>
[starttime:<the start time of the event>,

finishtime:< the finish time of the event>, eventdescription:<information about event>, eventlocation:<where the event happened>,
eventtype: <one of "break", "delivery", "mishap" or "other">, eventpictures:<array of pictures>[],
delivereditems:<an array of what was delivered> [delivereditem:<description of delivered item>
delivereditemtype:<one of "Refrigerator", "Washing Machine", "Microwave", "Dryer", "Oven", "Cooktops", "Air Conditioner", "Television", "Other">
deliveredqty:<the number of these items delivered> installationtime:<the number of minutes required to install, if any>],
deliveryattempt:<a value of "Success" or "Failure" for an attempted delivery>]

}

You now have an opportunity to model the schedule log information and test out if what happens can be stored in the DDBMS.

Detailed Requirements

This assignment is an individual assignment. It is a requirement of this assignment that students use PostGIS for the spatial database components and MongoDB for the document database components.

Students should submit a report that follows the format of a business/professional report and contain, at a minimum, a Title Page, Table of Contents, Executive Summary and References (if cited) and the following content with appropriate identifying headings:

A. The spatial database tasks (you should use the Spatial Reference ID (SRID) of 4326 for your spatial data):

1. Three pgAdmin 4 GUI screen shots of SQL and output for adding spatial columns:

a. geom_office POINT of 3 dimensions;

b. geom_warehouse POLYGON of 2 dimensions;

c. geom_weighbridge LINESTRING of 2 dimensions;

2. Nine pgAdmin 4 GUI screen shots of SQL and output for adding spatial column data for:

a. Laverton North warehouse office location;

b. Laverton North warehouse polygon;

c. Laverton North weighbridge line;

d. Campbelltown warehouse office location;

e. Campbelltown warehouse polygon;

f. Campbelltown weighbridge line;

g. Logan Park warehouse office location;

h. Logan Park warehouse polygon;

i. Logan Park weighbridge line;

3. Seven pgAdmin 4 GUI screen shots of SQL and output for the following queries/output:

a. Show the name of each of the warehouses with their office and warehouse coordinates. Use the St_AsText function to show the coordinates in a readable (longitude and latitude) format;

b. Show the name of each warehouse with their office and warehouse coordinates. If you have correctly entered your data, you should have the geometry viewer eye available in the column headings for your office and warehouse columns. Highlight one of the warehouse cells (move your mouse pointer to the cell and click it). Then select the geometry viewer eye in the heading (next to the padlock) to change your view to a plan/map view of the warehouse in the OpenStreetMap # app. To return to the view of the output, select the Data Output option. Select another warehouse cell and select the geometry viewer eye. The query in the Query Editor will remain the same whilst you are changing your view. You should provide a screen shot of the SQL and Geometry Viewer view for the warehouse that is not a rectangle;

# (A geometry viewer was added in v3.3 of pgAdmin4 but it only works for an SRID of 4326

(OpenStreetMap uses the WGS 84 Spheroid), so we have used that SRID in this assignment. For more information on this see Alger, 2018).

c. Show the name and the centre point (use the ST_asText and ST_Centroid functions for this) of each factory in degrees of longitude and latitude;

d. Show the name, area of each factory in metres squared (label this column "Areas(m2)") and its perimeter in metres (label this column as "Perimeter(metres)") . You will need to transform (ST_Transform) your SRID for Laverton North to 7855 and to 7856 for Campbelltown and Logan Park;

e. Show the name of the warehouse, the length in metres of its accompanying weighbridge - label as "Weighbridge length(metres)" - and the distance between the office and the weighbridge (in metres) - label as "Distance from Office to Weighbridge". Again, you will need to transform your SRID for Laverton to 7855 and to 7856 for Campbelltown and Logan Park;

f. Using one of the spatial relationship functions, show which factory office/s i.e. which points, are within the boundary of its own factory (look at slides 33-35 of the week 7 lecture slides for an idea on how to do this);

g. Show the sphere distance and the spheroid distance measurements in kilometres between the Campbelltown and Logan Park factory offices (look at slide 43 of the week 7 lecture slides for an idea on how to do this);

B. The document database tasks:

1. You need to create a MongoDB database called itech2004yourStudentIDDB and a collection named schedulelog within that database. Information about how to create a database and collection are given in the SEITPS ITECH2004 MongoDB Installation Guide V1.0 and also in the course textbook.

2. Load the document information from the commands in Appendix A of this document and include a MongoDBCompass screen shot of the loaded documents (expand all arrays and objects) after you have completed this;

3. Show the mongoDB commands to insert the following information into your database collection (you will only receive half marks if you do not provide the commands but instead construct the data and import/insert manually into the collection) :

a. Create a new schedule document that has an id of 7001. The vehicle with a registration of "QAS

127" is assigned to it, you are the driver (your employee number is "800652") and your lecturer is the jockey (their employee number is "100089"). The schedule is for a "Suburban" delivery type, the schedule date is 9/8/2021, starting at the Laverton North warehouse (V01) and you drive out of the warehouse at 9:00am in the morning.

b. You create a log entry for the first delivery of two microwaves which occurs at 9:15 to a construction site at 25 Thompson St Caroline Springs. The delivery is over in 5 minutes; no installation is necessary.

c. You take two pictures on your company supplied mobile phone of the microwaves in the lunchroom of the building site and record their ids in the log;

d. Both are Panasonic models called the Lunchroomer (P22_LR) and you update the log entry with this information;

e. You then update the log with the finish time, tick success and you are on to the next delivery;

f. You create another log entry for the second delivery of one refrigerator and a washing machine to 67 Fielding Crescent, WaterGardens. You arrive at 9.45.

g. You need to do a bit of installation at this place so you take your time to be careful and not damage any goods. When you are finished you take two pictures on your company supplied mobile phone one of the refrigerator and the other of the washing machine installed in the premises. You make a note of this in the log.

h. The refrigerator is a Westinghouse 605L SSFD model and it takes you 15 minutes to install. The washing machine is a Miele twin tub MTT-18 model and you take 15 minutes to install that also. You record the items installed and the time it took to install them. You update the log with the finish time, tick success and you are ready for your next delivery.

i. Disaster strikes. You realise you have a flat tyre and you need to change to the spare. You take a photo, and ask the jockey to start with changing the flat tyre while you make an entry in the log with the time of the event, a description about the flat tyre, a location (it's a bit embarrassing as you are outside the house you just delivered to) and an event type of "mishap".

j. You help change the flat tyre and when you have finished you take another photo and then update the log entry with the photo details and a finish time.

4. Provide screenshots of the mongosh shell with the command and successful running of the following

queries/requirements:

a. Change the drivername on the document with _id = 7000 to your name and the corresponding employeenumber to your student number;

b. Show all information in the schedulelog for a document with _id = 7001;

c. Show the event log details for any event that is of type, "mishap";

d. Show the event log details for any event where the delivered quantity of a particular appliance at the same address was greater than 1.

e. A mistake was made in the schedulelog on schedule _id = 7000 and the delivery event to the Stawell St. Ballarat East address. The Panasonic Microwave was not delivered so you need to pull it out of the array.

Attachment:- Data Modelling.rar