FIT3176 Advanced Database Design Assignment 2 – Semester 1 2018 Designing and Storing Laptop Data in XML Database
Due Date: Friday 25th May 10 PM
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 <techsum> 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].
Page 1 of 5
- 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].
- 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.
[35 marks].
Part 2 XML in Oracle [30 Marks]
- Store your XML documents in a table with two columns
- 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.
- your xml laptop document as the other column.
- 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].
- After the data has been stored in your table, provide SQL and the result for the following
queries:
- 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.
- 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.
- 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).
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 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)
Page 4 of 5
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: 2880×1800 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 <techsum> must be the root node for each of XML documents.
Page 5 of 5