数据库代写:FIT3176 Advanced Database Design Assignment 2

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

  1. 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].
  2. 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]

  1. Store your XML documents in a table with two columns
    1. 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.
    2. your xml laptop document as the other column.
    3. 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].

  2. After the data has been stored in your table, ​provide SQL and the result​ for the following

    queries:

    1. 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.
    2. 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.
    3. 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