Assignment 2 - Semester 1 2018
Designing and Storing Laptop Data in XML Database
Task Weighting: 15%
Please note this is an individual assignment.
Monazon Co. want you to prepare a set of XML documents to describe their laptops for sale to be
displayed on their eCommerce website. These XML documents describe the technical details and the
current sale price of their laptops.
You are provided three sample laptops which include all required data in the Appendix. They also ask
you to collect the technical details and the current sale prices of two more laptops manufactured by
two different brands (e.g., DELL, Acer and/or HP) 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 laptop. 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 details and the current sale price of only one laptop. 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 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 neccessary 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 laptop 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].
3. Create an XML Schema for your laptop 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 laptop document (e.g., laptopno), and also create a
sequence to supply values for this identifier.
b. your xml laptop 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 laptops which include information about Average Battery Life (in hours).
The result should display laptop number, item model number, operating system and
average battery life.
b. Provide a report which lists all laptops that have their item weight greater than 1 kg. The
result should display laptop number, product description, price and item weight.
c. Provide a report which lists all laptops that have their screen resolutions of at least a full
HD. A full HD screen resolution or better must have at least 1920 pixels in length and 1080
pixels in width. The result should show laptop number, ram capacity, and the screen size
(in pixels) in the numerical format of L x W (e.g. 1920 x 1080).
A screenshot of the sample query output as follows:
d. Provide a report on laptops whose batteries are not a combination of “battery type” lithium
and “battery charge technology” ion. The result should show laptop number, item model
number, battery type (e.g., lithium, nickel metal hydride (NiMH), nickel cadmium (NiCad))
and battery charge technology (e.g., metal, polymer or ion for lithium batteries and
perhaps, other for other battery types) [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 3 of 5
Appendix
laptopno:100
Product Description: Google Pixelbook
URL: https://goo.gl/CDfecY
Price: $AUD 1325.70
Screen Size: 12.3 inches
Max Screen Resolution: 2400 x 1600
Processor: 3.3 GHz
RAM: 8 GB
Hard Drive: Flash Memory Solid State
Wireless Type: 802.11b, 802.11g, 802.11n
Number of USB 3.0 Ports: 2
Brand Name: Google
Item model number: GA00122-US
Operating System: Chrome
Item Weight: 1.09 pounds
Batteries: 1 Lithium ion batteries required. (included)
laptopno: 101
Product Description: ASUS Chromebook C202SA-YS02 11.6" Ruggedized and Water Resistant
Design with 180 Degree
URL: https://goo.gl/3WUfN9
Price: $USD 209.00
Screen Size: 11.6 inches
Max Screen Resolution: 1366*768 pixels
Processor: 1.6 GHz Intel Celeron
RAM: 4 GB DDR3L
Hard Drive: 16 GB emmc
Wireless Type: 802.11ac
Number of USB 3.0 Ports: 2
Average Battery Life (in hours): 10 hours
Brand Name: Asus
Item model number: C202SA-YS02
Operating System: Chrome
Item Weight: 0.99 kg
Batteries: 1 Lithium Polymer batteries required. (included)
laptopno: 102
Product Description: Apple 15" MacBook Pro(Newest Version)
URL:https://goo.gl/zXD6nr
Price: $USD 2549.00
Screen Size: 15 inches
Max Screen Resolution: 2880x1800 pixels
Processor: 2.9 GHz Intel Core i7
RAM: 16 GB DDR3 SDRAM
Hard Drive: 512 GB Flash Memory Solid State
Number of USB 3.0 Ports: 2
Average Battery Life (in hours): 10 hours
Brand Name: Apple
Item model number: MPTT2LL/A
Operating System: MacOS Sierra
Item Weight: 2.90 kg
Batteries: 1 Lithium Metal batteries required. (included)
The following figure shows an example how XML documents should be stored in Oracle DB.
*Note:
- The laptopno attribute should be generated automatically via a sequence.
- The XML tag must be the root node for each of XML documents.