FIT3176 Advanced Database Design Assignment 2 – Semester 1 2019
Designing and Storing Mobile Phone Data in XML Database
Due Date: Friday 31st May 10 PM
Task Weighting: 15%
Please note this is an individual assignment.
MonashGSM Co. want you to prepare a set of XML documents to describe their mobile phones for sale to be displayed on their eCommerce website. These XML documents describe the technical details of their current stocks.
You are provided three sample mobile phones which include all required data in the Appendix. They also ask you to collect the technical details of two more mobile phones manufactured by two different brands (e.g., Huawei, Google and/or Nokia) to generate two more XML documents to be included in their XML database.
Assignment Tasks
Part 1 XML Creation [70 marks]
1. Create a set of XML documents containing technical details of each mobile phone. Note that the items listed in appendix A are the required content, the structure of the document is for you to determine, but you must be sure to include all the relevant data items. Each XML document must describe the technical only one mobile phone. You must have a minimum of 5 documents. The documents must contain a range of content/structures so as to test your schema fully. The XML tag
In creating your XML documents you are required to make use of attributes to demonstrate your XML skills. Also, you are required to provide necessary comments in your XML schema to explain what each line or a block of code does to help your markers understand your code [25 marks].
2. Create a tree structure diagram to show the structure of your XML mobile phone documents (for a sample diagram see the topic 8 studio work). Be sure to include your attributes (which you are required to have – see 1. above). This document will be submitted as a PDF, it is suggested you create it in LucidChart. [10 marks].
Page 1 of 5
3. Create an XML Schema for your mobile phone XML instances – validate each XML instance against this schema. You should ensure that you can maintain the highest level of control over the document structure and contents.
[35 marks]. Part 2 XML in Oracle [30 Marks]
4. Store your XML documents in a table with two columns
a. An identifier to act as a PK for each mobile phone document (e.g., phoneid), and also create a sequence to supply values for this identifier.
b. your xml mobile phone document as the other column.
c. The storage must be managed via a registered schema. You are required to use Binary XML storage for the XML component. Ensure you explicitly declare this, do not leave it to the Oracle defaults.
Provide the SQL commands which you used to store your data. [10 marks].
5. After the data has been stored in your table, provide SQL and the result for the following
queries:
a. Provide a list of mobile phones which include information about battery life with endurance rating. The result should display mobile phone id number, model, platform, and battery life with endurance rating in hours.
b. Provide a report which lists all mobile phones that have their item weight greater than 140g and less than 180g. The result should display mobile phone id number, WLAN information, available internal memory size(s) in GB and RAM size in GB.
c. Provide a report which lists all mobile phones that have their fast battery charging capacity of at least 20W. The result should show mobile phone id number, screen resolution (in pixels) in the numerical format of L x W (e.g. 1920 x 1080), and fast charging information.
Your designed schema structure must follow the
RUSSIAN DOLL approach. There will be ZERO MARKS awarded if your schema is not the
RUSSIAN DOLL design structure
Page 2 of 5
A screenshot of the sample query output as follows:
d. Provide a report on mobile phone(s) that would be available and were released between April 2018 and April 2019. The result should show phone id, URL, availability status with released year and month [20 marks].
A screenshot of the sample query output as follows:
Submission Requirements
Place the files from each task in a separate folder named task1, task2, task3, task4 and task5. If you wish, you may supply a readme.txt in the root folder.
Zip the full set of folders/files together into a single archive file. Your archive must be named using your authcate ID as authcateID-asst2.zip, for example: mlvie1-asst2.zip.
Submissions made after the due date will receive a deduction of 5 marks per day or part thereof (including weekends) as detailed in the unit guide.
Please ensure that you check your submission file if it can be opened by your markers. Failing to open your submitted zip file for marking will require you to resubmit it, which might incur late penalties.
To be awarded fulls mark for this section, your answer queries must make use of
Oracle XMLQuery and XMLTable constructs at least once in the above queries.
Page 3 of 5
Appendix
phoneid:1001
Model: Apple iPhone XS
URL: https://www.gsmarena.com/apple_iphone_xs-9318.php NETWORK Technology: GSM/CDMA/HSPA/EVDO/LTE Status: Available. Released 2018, September
Dimensions: 143.6 x 70.9 x 7.7 mm
Weight: 177 g
DISPLAY Size: 5.8 inches
Resolution: 1125 x 2436 pixels, 19.5:9 ratio (~458 ppi density) PLATFORM OS: iOS 12
MEMORY Internal: 64/256/512 GB, 4 GB RAM
WLAN: Wi-Fi 802.11 a/b/g/n/ac, dual-band, hotspot
BATTERY Charging: Fast battery charging 15W: 50% in 30 min Battery life: Endurance rating 72h
phoneid: 1002
Model: Samsung Galaxy S10 5G URL:https://www.gsmarena.com/samsung_galaxy_s10_5g-9588.php
NETWORK Technology: GSM/CDMA/HSPA/EVDO/LTE
Status: Available. Released 2019, April
Dimensions: 162.6 x 77.1 x 7.9 mm
Weight: 198 g
DISPLAY Size: 6.7 inches
Resolution: 1440 x 3040 pixels, 19:9 ratio (~502 ppi density)
PLATFORM OS: Android 9.0 (Pie); One UI
MEMORY Internal: 256/512 GB, 8 GB RAM
WLAN: Wi-Fi 802.11 a/b/g/n/ac/ax, dual-band, Wi-Fi Direct, hotspot
BATTERY Charging: Fast battery charging 25W (USB Power Delivery 3.0), Fast wireless charging 15W, Power bank/Reverse wireless charging 9W
phoneid:1003
Model: Motorola Moto G7 Plus URL:https://www.gsmarena.com/motorola_moto_g7_plus-9533.php
Network Technology:GSM/HSPA/LTE
Status: Available. Released 2019, March
Dimensions: 157 x 75.3 x 8.3 mm
Weight 176 g
DISPLAY Size: 6.2 inches
DISPLAY Resolution 1080 x 2270 pixels, 19:9 ratio (~405 ppi density)
PLATFORM OS: Android 9.0 (Pie)
MEMORY Internal: 64 GB, 4 GB RAM
WLAN: Wi-Fi 802.11 a/b/g/n/ac, dual-band, WiFi Direct, hotspot
BATTERY Charging: Fast battery charging 27W, Quick Charge 4, USB Power Delivery 3.0 Battery life: Endurance rating 59h
Page 4 of 5
The following figure shows an example how XML documents should be stored in Oracle DB.
*Note:
– The phoneid attribute should be generated automatically via a sequence.
– The XML tag
Page 5 of 5