- Copy the conceptual design for the operational database you achieved in Assignment 1 and paste it in this assignment. Correct errors and make necessary changes to the design in your assignment 1 as necessary. No mark is awarded to this step, but you must not miss this step as it sets up the context for your warehouse design. Without this context, the marker will not understand your solutions in the following steps.
- Identify an analytic subject for a warehouse and justify why such a subject is interesting to the decision making of your application. The subject would be related to key performance indicators in the application.
Discuss how the warehouse is subject oriented, integrated, nonvolatile, and time variant.
Note that as you have a specific database and have identified a specific subject, your discussion must refer to the concepts/tables in the application and their relationships to the subject.
Analytic subject : Count of customers v/s product utilization
Interest in this subject :This subject tells us that which product is most preferred by customers and which supplier supplies that product.
Warehouse related to subject :
Subject Oriented – This warehouse represents the core of this subject in the most easy way possible. We can easily get the answer to this subject using tables of warehouse.
Integrated – All the tables are constructed in such a manner that each table is easily relatable to any other table.
Non-volatile – The new data will be appended to tables. Each row has primary key in each table so that previous data is not replaced.
Time Variant – Based upon selection of refreshing date, this database can be easily updated on that time period and it won’t require refreshes in between. The refresh date will tell the time period when the database is updated.
- Answer this please Design a star schema for the warehouse to support the analysis of the subject and the dimension tables of the schema. Draw the schema in a diagram. Your diagram may be simple, but the concepts in the diagram must be correct.
Justify the reasons why the attributes in the dimensional tables and the fact table are necessary for analyzing the subject.
Answer this please Show sample data for the tables in the schema. The amount of sample data should be enough to demonstrate some concepts and logics and to help your description.
The names of your warehouse tables should start with “wh_”.
List correct SQL statements that create your warehouse tables. These must NOT be screenshots.
Correct the name of tables please
create table CUSTOMER_DIMENSION (
CustomerId integer,
ContactName VARCHAR (40),
Email VARCHAR (40),
address VARCHAR (40),
city VARCHAR (40),
postcode integer,
AddressType VARCHAR (40),
CorporationName VARCHAR (40),
Website VARCHAR (40),
ReturningCustomer VARCHAR (40),
PRIMARY key (CustomerId)
);
create table PRODUCT_DIMENSION (
ProductId integer,
Description VARCHAR (40),
PurchasePrice integer,
CustomerPrice integer,
ObseateProduct VARCHAR (40),
ODate date,
PRIMARY key (ProductId)
);
create table SUPPLIER_DIMENSION (
SupplierId integer,
SupplierName VARCHAR (40),
ContactName VARCHAR (40),
Address VARCHAR (40),
City VARCHAR (40),
Postcode integer,
AddressType VARCHAR (40),
PhoneNumber integer,
PhoneType VARCHAR (40),
PRIMARY key (SupplierId)
);
create table EVENT_DIMENSION (
EventId integer,
TypeOfEvent VARCHAR (40),
EventDate date,
EventTime time,
Customer VARCHAR (40),
NumberOfAttendees integer,
Location VARCHAR (40),
EventManager VARCHAR (40),
EventDescription VARCHAR (40),
PRIMARY key (EventId)
);
create table STAFF_DIMENSION (
StaffId integer,
StaffName VARCHAR (40),
Description VARCHAR (40),
TFN integer,
HourlyWage integer,
Supervised VARCHAR (40),
Salary integer,
DateOfJoining date,
DO_ date,
PenaltyRate integer,
Certification VARCHAR (40),
PRIMARY key (StaffId)
);
Create table EVENT_PLANNING_FACT (
PlanId integer,
RefreshSystemDate date,
EventId integer,
CustomerId integer,
ProductId integer,
SupplierId integer,
StaffId integer,
PRIMARY key (PlanId)
foreign key (StaffId) references STAFF_DIMENSION (StaffId)
foreign key (EventId) references EVENT_DIMENSION (EventId)
foreign key (SupplierId) references SUPPLIER_DIMENSION (SupplierId)
foreign key (ProductId) references PRODUCT_DIMENSION (ProductId)
foreign key (CustomerId) references CUSTOMER_DIMENSION (CustomerId)
);
- Design SQL statements (3.1) for loading data from your operational database (what you had designed in assignment 1) at the start of the warehouse and (3.2) for maintaining the warehouse data during the warehouse operation. For each statement, define when the statement shall be executed. The statements should also cover both the dimension tables and the fact table.
Answer this
Answer this(3.2)
Design SQL
Answer this(3.1)
Design SQL
The post conceptual design for the operational database appeared first on My Assignment Online.