SIT103 – Data and Information Management (2018) Assessment Task 2 – Project Documentation and DB
Due Date: Monday, September 24 at 9am
Introduction
- This assessment is for students to develop the capacity to design and implement a database.
- This assessment requires students to identify business rules, create a data dictionary, createan Entity-Relationship diagram, and develop an Oracle database using SQL.
- This is an individual assessment task.
- The project documentation submitted should include business rules, data dictionaries, ERdiagrams, SQL source code such as AT2.sql, and SQL spool files such as AT2.txt. See ‘Assessment Task 2 – Submission Instruictions.pdf’ for details.
Unit Learning Outcomes
•
•
Of the three Unit Learning Outcomes (ULOs) of this unit SIT103, this assessment task will focus on the last two ULOs. These are:
o ULO 2 – At the end of this unit students will be able to evaluate data models and apply data
modelling techniques to capture the data aspects of real-world situations.
o ULO 3 – At the end of this unit students will be able to design and develop relational
databases by using SQL and a database management system.
The assessment of this task (Project Documentation and Database) will indicate whether students can partially attain these unit learning outcomes.
Instructions
- Read these instructions, the following scenario and tasks.
- Attempt as many tasks as possible.
- Place your name, ID and answers in your document.
- Please note that MS Word (docx) files, txt files, and SQL script files are expected to besubmitted, do not submit PDF files.
- As there will be several files that you will submit, you will place all files in a folder. You mightlike to name this folder AT2 – John Smith 215123456. You should then and ZIP that folder to produce a file such as AT2 – John Smith 215123456.zip. You will submit this ZIP file.
Page 1 of 6
Scenario
A1E is a fictitious retail company using www.a1e.com as their main URL. Imagine their web site providing customers with information related to many categories of electronic products: computers, televisions, audio receivers, mobile phones, cameras, and more. Also, there are many subcategories of products such as computers might be divided into Apple, Dell, HP, Microsoft, Lenova, and so on. In general, A1E uses a database containing data about products, categories, customers, and product reviews.
Please use the following table schemes. This is not a complete list as several additional tables are required to design and develop this database.
Product (PID, manufacturer, name, model, imageFilename, price)
Characteristic (CID, name, shortDescription) Feature (FID, name, shortDescription) Detail (DID, name, shortDescription) Category (CatID, name)
Subcategory (SubCatID, name)
Customer (CID, name, address, mobile)
Review (CID, PID, author, subject, text, recommendation, quality, value) Score (SID, value, description)
In addition to the above database information.
- Each product has many characteristic that are used to create a product overview. Eachcharacteristic can be used for many products, this would make it easy to find all products
with that kind of characteristic.
- Each product has many key features. Each feature can be used for many products, thiswould make it easy to find all product with that key feature.
- Each product has a list of details. Each detail can be used for many products. This wouldmake it easy to find all product with the same or similar details.
- Each product might belong to many categories. For example, a gaming desktop might belongto the computing category and also the gaming category. Each category might have many
products.
- Each product might belong to many subcategories. For example, a laptop might belong tothe laptops subcategory and also the gaming laptops subcategory. Each subcategory might
have many products.
- Each category can have many subcategories, and each subcategory might belong to manycategories.
- A1Es website allows customers to make reviews. There can be many reviews. Each customercan review many products, and each product can be reviewed by many customers.
- Each review must contain both quality and value scores that are provided by the reviewer. Afive point scoring system is used. Each numeric score is related to a small description of a few words such as:
- •1 very poor
- •2 poor
- •3 good
- •4 very good
- •5 excellent
Although this task is aimed at designing and developing a database, and not creating a web page. You might easily imagine that this database could be used in a larger application that retrieves data in order to create a web page such as https://www.jbhifi.com.au/palsonic/palsonic-tftv2410m-23-6- hd-led-lcd-tv-with-integrated-dvd-player/849839/
Page 2 of 6
Tasks
For the above scenario:
Task 1. Task 2. Task 3.
Task 4.
Determine the business rules for all tables including junction tables.
Develop a data dictionary for all tables including junction tables.
Develop an ER diagram for all tables including junction tables. Clearly label all entities, primary and foreign keys, relationship connectivity and cardinalities.
Develop an SQL script, say AT2.sql, to run on Deakin’s Oracle database which:
- (a) removes previous tables related to this question, e.g., drop …
- (b) creates these tables including junction tables, e.g., create …
- (c) inserts data into appropriate tables, e.g., insert …
- (d) uses the spool command to start recording to a file,e.g., spool /home/username/AT2.txt
(please replace username with your Deakin login name)
- (e) turns on the echo, e.g., set echo on;
- (f) displays the name and price of that has Wi-Fi, e.g., select …;
- (g) displays the following data about in the “4K Ultra HD TVs” subcategorywhere the price > $4000:
- manufacturer, name, image filename, and price, e.g., select …;
- name, and the average of the review quality and value scores, e.g., select …;
- the number of reviews, e.g., select …;
- (h) displays the following data about the Samsung Galaxy S9+ 256GB (Coral Blue):
- manufacturer, name, model, image filename, price, e.g., select …;
- average of the review quality and value scores, e.g., select …;
- the number of reviews, e.g., select …;
- all characteristics (name and description), e.g., select …;
- all key features (name and description), e.g., select …;
- all details (name and description), e.g., select …;
- all review data (subject, text, recommendation, quality, value), e.g., select …;
- (i) turns off the echo, e.g., set echo off;
- (j) turns off the spooling, e.g., spool off;
each product
each product
Page 3 of 6
Expected Output
'Task 4(f) - All products that have Wi-Fi'
NAME PRICE ------------------------------------------------------------ ------- Samsung Galaxy Note9 512GB (Midnight Black) 1799 Samsung Galaxy S9+ 256GB (Coral Blue) 1499 Apple iPhone X 256GB (Silver) 1829 Apple iPhone X 64GB (Space Grey) 1574 Apple iPad Pro 10.5-inch 256GB Wi-Fi (Silver) 1198 Apple iPad 32GB Wi-Fi + Cellular (Space Grey) [6th Gen] 669 Microsoft Surface Pro i7 1TB Tablet 3996 LG C8 77" 4K UHD AI Smart OLED TV 12996 Samsung Galaxy Tab S4 10.5" Wi-Fi 256GB (Fog Grey) 1174 Sony KDL32W660E 32" Full HD HDR Smart LED LCD TV 648 Sony X85F 75" 4K UHD Android LED TV 4496 Samsung NU8000 75" Series 8 Premium 4K UHD LED TV 3996
12 rows selected.
'Task 4(g) i - 4K Ultra HD TVs and price > $4000'
PID MANUFACTUR NAME IMAGE ------ ---------- ------------------------------ -------------------- -------
10 Sony Sony X85F 75" 4K UHD Android L /Images/247010.jpg 4496
11 LG LG C8 77" 4K UHD AI Smart OLED /Images/247555.jpg 12996
‘Task 4(g) ii – Average scores for 4K Ultra HD TVs and price > $4000’
PID Avg Quality Avg Value
------ ----------- ----------
11 4 3
10 5 4
‘Task 4(g) iii – Review count for 4K Ultra HD TVs and price > $4000’
PID REVIEWS
------ ----------
11 2
10 1
‘Task 4(h) i – Data about Samsung Galaxy S9+ 256GB (Coral Blue)’
MANUFACTUR NAME MODEL IMAGE PRICE ---------- --------------- --------------- -------------------- ------- Samsung Samsung Galaxy 1091004314 /Images/235672.jpg 1499
‘Task 4(h) ii – Average scores for Samsung Galaxy S9+ 256GB (Coral Blue)’
Avg Quality Avg Value
----------- ----------
4.5 4.5
‘Task 4(h) iii – Review count for Samsung Galaxy S9+ 256GB (Coral Blue)’
REVIEWS ———-2
Page 4 of 6
PRICE
‘Task 4(h) iv – Characteristics – Samsung Galaxy S9+ 256GB (Coral Blue)’
NAME SHORTDESCRIPTION -------------------- ----------------------------------------
The camera that radically slows down tim The camera that turns you into an emoji, The revolutionary camera that adapts lik The camera that can instantly read what The camera that responds to your voice c Capture every sharp detail with two adva The remarkable water-resistant phone: Ma Stereo speakers with Dolby Atmos surroun
Super Slow-mo AR Emoji Dual Aperture Live Translation Quick Command Dual Camera Water-resistant Stereo Speakers Multi Device Experie Share your screen, to your other Samsung
Infinity Display Intelligent Scan Performance Battery
13 rows selected.
See more. Hold less. The camera that recognises you instantly Our fastest Galaxy yet with cat. 18 tech Power through the day with a long-lastin
‘Task 4(h) v – Features – Samsung Galaxy S9+ 256GB (Coral Blue)’
NAME SHORTDESCRIPTION -------------------- ----------------------------------------
8 rows selected.
12MP Super Speed Dual Camera with Super 6.2" Infinity Display with Quad HD+ reso Stereo speakers with Dolby Atmos Water and dust resistant (IP68)[1] 256GB[2] storage
Secure iris and face recognition technol Fast wired and wireless charging[3] New 10nm Octa Core chip
‘Task 4(h) vi – Details – Samsung Galaxy S9+ 256GB (Coral Blue)’
NAME SHORTDESCRIPTION -------------------- ---------------------------------------- Resolution (Pixels) 2960 x 1440 Touchscreen TRUE USB (Type-C) Port 3.1 Video Frames Per Sec up to 960fps Wi-Fi 802.11 ac Wireless Charging Ty Qi A2DP TRUE Battery capacity (mA 3500 Bluetooth v5.0 Built-in flash TRUE Colour Blue Device screen size ( 6.2 Digital zoom 2 Display type Super AMOLED Expandable memory fo Micro SD card Expandable memory up 256 Face detection TRUE Front camera (MP) 8 GPS TRUE Headphone output (3. TRUE Internal memory 256GB Movie recording Ultra HD MP3 TRUE Network compatibilit 4G NFC TRUE
Page 5 of 6
Phone Operating Syst Android Phone Type Galaxy S9+ Processor Samsung Exynos 9 Octa-Core Processor Model Numb Exynos 9810 RAM (GB) 6 Rear Camera (MP) 12
31 rows selected.
‘Task 4(h) vii – Reviews about Samsung Galaxy S9+ 256GB (Coral Blue)’
SUBJECT TEXT REC QUALITY VALUE -------------------- ------------------------------ --- ------- ------- Great phone and good I bought this in store and it yes 5 4 Almost perfect samsu I bought this a few weeks ago yes 4 5
Page 6 of 6