代做FIT3176、代写XML Database、SQL设计代做、SQL编程语言调试

日期：2019-05-28 10:41

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 <specs> must be the root node for each of the XML documents.

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 2 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. 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 [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.

Page 3 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:

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.

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.

Page 4 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 5 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 <specs> must be the root node for each of XML documents.