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.
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/
For the above scenario:
Task 1. Determine the business rules for all tables including junction tables.
Task 2. Develop a data dictionary for all tables including junction tables.
Task 3. Develop an ER diagram for all tables including junction tables. Clearly label all entities, primary and foreign keys, relationship connectivity and cardinalities.
Task 4. Develop an SQL script, say AT2.sql, to run on Deakins 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 each product that has Wi-Fi, e.g., select ...;
(g) displays the following data about each product in the 4K Ultra HD TVs subcategory where the price > $4000:
i. manufacturer, name, image filename, and price, e.g., select ...;
ii. name, and the average of the review quality and value scores, e.g., select ...;
iii. the number of reviews, e.g., select ...;
(h) displays the following data about the Samsung Galaxy S9+ 256GB (Coral Blue):
i. manufacturer, name, model, image filename, price, e.g., select ...;
ii. average of the review quality and value scores, e.g., select ...;
iii. the number of reviews, e.g., select ...;
iv. all characteristics (name and description), e.g., select ...;
v. all key features (name and description), e.g., select ...;
vi. all details (name and description), e.g., select ...;
vii. 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;
'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 PRICE
------ ---------- ------------------------------ -------------------- -------
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
'Task 4(h) iv - Characteristics - Samsung Galaxy S9+ 256GB (Coral Blue)'
NAME SHORTDESCRIPTION
-------------------- ----------------------------------------
Super Slow-mo The camera that radically slows down tim
AR Emoji The camera that turns you into an emoji,
Dual Aperture The revolutionary camera that adapts lik
Live Translation The camera that can instantly read what
Quick Command The camera that responds to your voice c
Dual Camera Capture every sharp detail with two adva
Water-resistant The remarkable water-resistant phone: Ma
Stereo Speakers Stereo speakers with Dolby Atmos surroun
Multi Device Experie Share your screen, to your other Samsung
Infinity Display See more. Hold less.
Intelligent Scan The camera that recognises you instantly
Performance Our fastest Galaxy yet with cat. 18 tech
Battery Power through the day with a long-lastin
13 rows selected.
'Task 4(h) v - Features - Samsung Galaxy S9+ 256GB (Coral Blue)'
NAME SHORTDESCRIPTION
-------------------- ----------------------------------------
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
8 rows selected.
'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
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