Scenario A1E is a fictitious retail company using www.a1e.com as their main URL.

Question

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. Each characteristic 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, this would 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 would make 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 belong to 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 to the 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 many categories. • A1Es website allows customers to make reviews. There can be many reviews. Each customer can 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. A five 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-6hd-led-lcd-tv-with-integrated-dvd-player/849839/
Page 3 of 6




Tasks 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 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 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;

Explanation / Answer

Since the fictitious retail website is an e-commerce website, hence a lot of transactions will be taking place in an instance. Moreover, your database must provide manoeuvrability when implemented on your e-commerce website. Furthermore, your Database should have simple ordering system and it must ensure that all the transactions are performed smoothly.

MySQL is one such database that fits perfectly in an e-commerce environment. MySQL is good at tracking orders and even tracks other customer’s details. Even all the transactions are tracked and information is gathered during the process. Since your website is selling electronics goods, your database have to store information about the product. A structure is created based on product data and hence makes it easier to access the information.

So there will be following tables with following attributes:

Customer Orders: Name, Phone number, address, email, bank details

Product: Product ID, Price, Product categories

Review: Customer Name, Review and Ratings

So MySQL database can suits your requirement and serve you better performance for your retail website.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.