A bookshop needs to analyze its sales by product, geography and time. The original OLTP system...

Transcribed Image Text:

A bookshop needs to analyze its sales by product, geography and time. The original OLTP system is: Publisher Publisherid INT Publishemame VARCHAR(45) PublisherPhone INT URL VARCHAR(45) PublisherAddress VARCHAR(45) Customer CustomerEmail VARCHAR(45) CustomerName VARCHAR(45) CustomerPhone INT CustomerAddress VARCHAR(45) Book Author Author INT AuthorName VARCHAR(45) AuthorAddress VARCHA.... Title VARCHAR(45) ISBN VARCHAR(20) Year YEAR(4) Book ISBN VARCHAR(20) (FK) ShoppingBasketid INT (FK) Price FLOAT Shopping Baskets have Books ShoppingBasket ShoppingBasketid INT CustomerEmal INT (FK) count INT Publisherid INT (FK) Authorid INT (FK) Warehouses have Books Warehouse Book ISBN VARCHAR(20) (FK) code INT Warehouse code INT (FK) count INT WarehousePhone VARCHAR(45) WarehouseAddress VARCHAR(45) Q2: A bookshop has its sales database system. A detailed Entity Relationship Diagram (ERD) is attached. The bookshop wants to build a data warehouse to analyse its data: Design a normalized model (snowflake) schema - Design a denormalized model (Star) schema Decide which schema (star or snowflake) suits the bookshop better? A bookshop needs to analyze its sales by product, geography and time. The original OLTP system is: Publisher Publisherid INT Publishemame VARCHAR(45) PublisherPhone INT URL VARCHAR(45) PublisherAddress VARCHAR(45) Customer CustomerEmail VARCHAR(45) CustomerName VARCHAR(45) CustomerPhone INT CustomerAddress VARCHAR(45) Book Author Author INT AuthorName VARCHAR(45) AuthorAddress VARCHA.... Title VARCHAR(45) ISBN VARCHAR(20) Year YEAR(4) Book ISBN VARCHAR(20) (FK) ShoppingBasketid INT (FK) Price FLOAT Shopping Baskets have Books ShoppingBasket ShoppingBasketid INT CustomerEmal INT (FK) count INT Publisherid INT (FK) Authorid INT (FK) Warehouses have Books Warehouse Book ISBN VARCHAR(20) (FK) code INT Warehouse code INT (FK) count INT WarehousePhone VARCHAR(45) WarehouseAddress VARCHAR(45) Q2: A bookshop has its sales database system. A detailed Entity Relationship Diagram (ERD) is attached. The bookshop wants to build a data warehouse to analyse its data: Design a normalized model (snowflake) schema - Design a denormalized model (Star) schema Decide which schema (star or snowflake) suits the bookshop better?

Answer rating: 100% (QA)

To design a normalized model snowflake schema and a denormalized model star schema for the bookshops sales database system we need to analyze the enti... View the full answer