I have attached the assignment. Thank you
School of Engineering, Information Technology and Physical Sciences ITECH2004 – Data Modelling Assignment 2, Sem9 2021 (202117) __________________________________________________________________________________ CRICOS Provider No. 00103D ITECH2004_Assignment2_sem9_2021_202117_TKeogh Page 1 of 20 Assignment 2 – Spatial and Document Database Assignment Purpose and Learning Outcomes Purpose The purpose of the assignment is to provide students with the opportunity to apply knowledge and skills developed during the semester with particular reference to: 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. Learning Outcomes The learning outcomes directly assessed are: 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. School of Engineering, Information Technology and Physical Sciences ITECH2004 – Data Modelling Assignment 2, Sem9 2021 (202117) __________________________________________________________________________________ CRICOS Provider No. 00103D ITECH2004_Assignment2_sem9_2021_202117_TKeogh Page 2 of 20 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. Timelines and Expectations Marks: Assignment will be assessed based on a mark out of 60 The following information is a summary from your Course Description: Percentage Value of Task: 30% of the course marks Due: Friday, October 8th, 2021 at 4:00pm (this is an extension date from the original date of the 1st October, 2021) Minimum time expectation: 25 hours Students are required to complete the assignment individually. Students are expected to submit the required report and details (see below) to the submission box in their Moodle shell. 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: School of Engineering, Information Technology and Physical Sciences ITECH2004 – Data Modelling Assignment 2, Sem9 2021 (202117) __________________________________________________________________________________ CRICOS Provider No. 00103D ITECH2004_Assignment2_sem9_2021_202117_TKeogh Page 3 of 20 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 School of Engineering, Information Technology and Physical Sciences ITECH2004 – Data Modelling Assignment 2, Sem9 2021 (202117) __________________________________________________________________________________ CRICOS Provider No. 00103D ITECH2004_Assignment2_sem9_2021_202117_TKeogh Page 4 of 20 focus. To that end you determine, using Google Earth the office, warehouse and weighbridge points for warehouses for three of the eastern seaboard warehouses. The table below summarises this information (all coordinates are in (longitude, latitude (WSG-84 in decimal degrees), elevation in metres)*: Warehouse name State Office Point Weighbridge Start and End Line Points Warehouse Perimeter Points Laverton North Vic (144.781744, - 37.826901), 27m (144.749414, -37.825150), (144.749592, -37.825157) (144.781116, - 37.824855), (144.780843, - 37.826916), (144.782018, - 37.827019), (144.782320, - 37.824960) Campbelltown NSW (150.806228, - 34.055744), 76m (150.790276, -34.064902), (150.790578, -34.064934) (150.804330, - 34.055280), (150.804631, - 34.055905), (150.804980, - 34.055797), (150.805050, - 34.055945), (150.805475, - 34.055820), (150.805550, - 34.056002), School of Engineering, Information Technology and Physical Sciences ITECH2004 – Data Modelling Assignment 2, Sem9 2021 (202117) __________________________________________________________________________________ CRICOS Provider No. 00103D ITECH2004_Assignment2_sem9_2021_202117_TKeogh Page 5 of 20 Warehouse name State Office Point Weighbridge Start and End Line Points Warehouse Perimeter Points (150.806881, - 34.055598), (150.806447, - 34.054650) Logan Park QLD (153.193537, - 27.683932), 10m (153.185471,- 27.676645), (153.185533, -27.676771) (153.193238, - 27.682795), (153.193020, - 27.683750), (153.193568, - 27.683843), (153.193795, - 27.682894) * Note: normally when you are dealing with mapping coordinates, these are given in order of latitude =x and longitude=y. You should be aware though that in spatial databases like PostGIS, coordinates are expected/stored in longitude =x, latitude = y format so that is the order presented here. Also remember that the points provided identify actual places but this assignment is purely fictitious. AppliancesToYourDoor does not exist and the locations used are used for convenience only. 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 School of Engineering, Information Technology and Physical Sciences ITECH2004 – Data Modelling Assignment 2, Sem9 2021 (202117) __________________________________________________________________________________ CRICOS Provider No. 00103D ITECH2004_Assignment2_sem9_2021_202117_TKeogh Page 6 of 20 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:
, vehicle:, driver: