SQL DATABASE: Below is said normalized (BCNF) data model that will need to be denormalized for a hypothetical online retailer that is designed to...
This question has been answered
Question

SQL DATABASE:

I need help denormalizing an entity-relationship data model that will improve the performance of an ERD created based of a narrative describing the online retailer's processes.

assignment5picture.png

Below is said normalized (BCNF) data model that will need to be denormalized for a hypothetical online retailer that is designed to support operations:




Key processes include:


Every time a package is shipped, a mailing label must be generated that requires the customer's contact information to be extracted from the database.


This is accomplished with the following SQL query using customerId = 133 as an example):


SELECT C.firstName, C.lastName, C.address1, C.address2, PC.city, PC.state, PC.postalCode FROM Customer C INNER JOIN PostalCode PC ON C.postalCode = PC.postalCode WHERE C.customerId = 133;


Customers may post & read reviews (like in amazon's website). The ProductReview table that's in the ERD model now has over a million unique reviews. Another function may include writing a query that will draw the ratings from the pool of reviews and give a potential customer "ratings" for any given product.


an example of what I have stated above:


SELECT AVG(likeProduct * 1.0) AS likePercentage FROM ProductReview WHERE productId = 4321;



Lastly, this "online retailer" in hypothetical terms will have multiple vendors for any given product that they sell on their website. To be able to maximize selling products at the best profit margin available.


Here's an example of a query that is able to help MGMT decide which vendor to buy from:



SELECT productId, description, minPrice = (SELECT MIN(price) FROM ProductVendor WHERE productId = P.productId) FROM Product P WHERE productId = 7741;



Again, I am trying to denormailze the stated ERD model above, to improve the efficiency of the online retailer. I need help finding ways or having examples to improve this model.


Thanks!

Answered by Expert Tutors
Get unstuck

426,507 students got unstuck by Course
Hero in the last week

step by step solutions

Our Expert Tutors provide step by step solutions to help you excel in your courses