top of page

Design a Warehouse Database Using Star Schema Design | Warehouse Database Designing Help



Objective:

  • design a warehouse database using star schema design

  • load a data warehouse table using SQL against the production table


Background

Heather Sweeney Dodge is a home redesign company that specializes in home remodel. They give seminars around the country and sell books and video at the seminars. They track attendance and sales at the seminars in a database. They would like to analyze sales trends and find out which of their products is most popular and which of their important customers


Part 1: Create the HSD tables and load data

Download files

  • HSD CreateTablesWithData.sql, the production tables for the HSD (Heather Sweeney Dodge Company) and

  • HSDDW CreateTablesWithData.sql, the data warehouse tables


Part 2: Create the HSDDW (HSD Data Warehouse) tables

Execute the script HSD CreateTablesWithData script to create the production table and data.

Execute the script HSDDW CreateTablesWithData which create the warehouse tables and will loads the HSDDW tables with data from the HSD tables. Study the script carefully to understand how the data from HSD tables is copied and transformed and loaded into the HSDDW tables. This is known as ETL (Extract – Transform -Load). ETL may be done by sql scripts in simple cases (such as here). In more complex cases it needs special application programs to filter and “scrub” the data.


Part 3: Modifying the HSD_DW warehouse tables


1. Examine the sql statements in the HSDDW CreateTablesWithData.sql What transformations of data were made when HSD-DW was loaded with data? [Identify all the transformations, showing the tables and columns of the HSD data and how they are mapped into the HSD-DW database.


Create the SALEs_FOR_RFM table to the HSD-DW database using the create table statement below.RFM refers to “Recency, Frequency, Monetary Value” which are ways to identify important customers


create table sales_for_rfm (
     TimeId              int  not null, 
     CustomerId          int  not null,
     InvoiceNumber  int  not null, 
     PreTaxTotalSales Numeric(9,2) not null,
       constraint sales_for_rfm_pk 
      primary key(TimeId, CustomerId, InvoiceNumber),
     constraint srfm_timeline_fk foreign key (TimeId) 
      references timeline(TimeId)
        on update no action
        on delete no action,
     constraint srfm_customer_fk foreign key (CustomerId) 
      references customer(CustomerId)
        on update no action
        on delete no action
);


2. What data will be used to load the SALES_FOR_RFM fact able? Write the complete set of SQL statements necessary to load the data. Then run the insert statement to populate the SALES_FOR_RFM table. Your INSERT statement will be similar to the load product sales INSERT statement on line 124 of HSDDW CreateTablesWithData.sql



A query to summarize product units sold by Customer (CustomerName) , City, and Product(ProductName) and Year would be :


SELECT c.CustomerId, c.CustomerName, c.City, 
p.ProductNumber, p.ProductName,
t.Year,t.QuarterText, 
SUM(ps.Quantity) AS TotalQuantity
 FROM customer c, product_sales ps, product p,
      timeline t
WHERE c.CustomerId = ps.CustomerID 
      AND p.ProductNumber = ps.ProductNumber
      AND t.TimeId = ps.TimeID
GROUP BY c.CustomerId, c.CustomerName, c.City, 
        p.ProductNumber, p.ProductName, 
        t.QuarterText, t.Year
ORDER BY c.CustomerName, t.Year, t.QuarterText; 

In the above query, product_sales table is called the fact table; customer, timeline and product tables are called dimension tables.


3. Modify the above query to report the total dollar amount of each product for each year instead of the quantity sold for each product.Create an SQL View equivalent of the SQL query you wrote.Use your view and verify that the view returns the same number of rows in the same order as your sql SELECT


Define a PAYMENT_TYPE dimension table as


create table hsddw.payment_type (
   payment_type_id     int  not null primary key, 
   payment_type varchar(35) not null
);

and insert the following values


insert into payment_type values 
(1, 'VISA'), 
(2, 'MasterCard'),
(3, 'Cash'),
(4, 'American Express'),
(5, 'PayPal'),
(6, 'Check');

4. Modify the file HSDDW CreateTablesWithData.sql

  • add a column to the create product_sales on line 34 for payment_id.

  • modify the INSERT statement on line 124 to populate the product_sales table including the new payment_id column.

  • rerun the HSDDW CreateTablesWithData.sql script and verify that payment_id has been populated. You should have 48 rows in the product_sales table



HSD CreateTablesWithData.sql

DROP SCHEMA IF EXISTS hsd;
CREATE SCHEMA hsd;
USE hsd;

CREATE  TABLE CUSTOMER(
	CustomerID			Int				NOT NULL auto_increment,
	LastName			Char(25)		NOT NULL,
	FirstName			Char(25)		NOT NULL,
	EmailAddress		VarChar(100)	NOT NULL,
	EncryptedPassword	VarChar(50)		NULL,
	Phone				Char(12)		NOT NULL,
	StreetAddress		Char(35)		NULL,
	City				Char(35)		NULL DEFAULT 'Dallas',
	State				Char(2)			NULL DEFAULT 'TX',
	ZIP					Char(10)		NULL DEFAULT '75201',
	CONSTRAINT 			CUSTOMER_PK 	PRIMARY KEY(CustomerID),
	CONSTRAINT			CUSTOMER_EMAIL	UNIQUE(EmailAddress)
	);
	
CREATE  TABLE SEMINAR(
	SeminarID			Int				NOT NULL auto_increment,
	SeminarDate			Date			NOT NULL,
	SeminarTime			Time			NOT NULL,
	Location			VarChar(100)	NOT NULL,
	SeminarTitle		VarChar(100)	NOT NULL,
	CONSTRAINT 			SEMINAR_PK 		PRIMARY KEY(SeminarID)
	);

CREATE  TABLE SEMINAR_CUSTOMER(
	SeminarID			Int				NOT NULL,
	CustomerID			Int				NOT NULL,
	CONSTRAINT 			S_C_PK 			PRIMARY KEY(SeminarID, CustomerID),
	CONSTRAINT 			S_C_SEMINAR_FK  FOREIGN KEY(SeminarID)
							REFERENCES SEMINAR(SeminarID)
								ON UPDATE NO ACTION
								ON DELETE NO ACTION,
	CONSTRAINT 			S_C_CUSTOMER_FK    FOREIGN KEY(CustomerID)
							REFERENCES CUSTOMER(CustomerID)
								ON UPDATE NO ACTION
								ON DELETE NO ACTION
	);

CREATE  TABLE CONTACT(
	CustomerID			Int				NOT NULL,
	ContactNumber		Int				NOT NULL,
	ContactDate			Date			NOT NULL,
	ContactType			VarChar(30)		NOT NULL,
	SeminarID			Int				NULL,
	CONSTRAINT 			CONTACT_PK 		PRIMARY KEY(CustomerID, ContactNumber),
	CONSTRAINT			CONTACT_ContactType
							CHECK (ContactType IN ('Seminar', 'FormLetterSeminar',
								   'WebAccountCreation', 'WebPurchase',
								   'EmailAccountMessage', 'EmailSeminarMessage',
								   'EmailPurchaseMessage', 'EmailMessageExchange',
								   'PhoneConversation')),
	CONSTRAINT 			CONTACT_SEMINAR_FK FOREIGN KEY(SeminarID)
							REFERENCES SEMINAR(SeminarID)
								ON UPDATE NO ACTION
								ON DELETE NO ACTION,
	CONSTRAINT 			CONTACT_CUSTOMER_FK FOREIGN KEY(CustomerID)
							REFERENCES CUSTOMER(CustomerID)
								ON UPDATE NO ACTION
								ON DELETE NO ACTION
	);

CREATE  TABLE PRODUCT(
	ProductNumber		Char(35)		NOT NULL,
	ProductType			Char(24)		NOT NULL,
	ProductDescription	VarChar(100)	NOT NULL,
	UnitPrice			Numeric(9,2)	NOT NULL,
	QuantityOnHand		Int				NULL,
	CONSTRAINT 			PRODUCT_PK 		PRIMARY KEY(ProductNumber),
	CONSTRAINT			PRODUCT_ProductType
							CHECK (ProductType IN ('Video',
								   'Video Companion', 'Book'))
	);

CREATE  TABLE INVOICE(
    InvoiceNumber		Int				NOT NULL auto_increment,
	InvoiceDate			Date			NOT NULL,
	CustomerID			Int				NOT NULL,
	PaymentType			Char(25) 		NOT NULL DEFAULT 'Cash',
	SubTotal			Numeric(9,2)	NULL,
	Shipping			Numeric(9,2)	NULL,
	Tax					Numeric(9,2)	NULL,
	Total				Numeric(9,2)	NULL,	
    CONSTRAINT 			INVOICE_PK 		PRIMARY KEY (InvoiceNumber),
	CONSTRAINT			INVOICE_PaymentType
							CHECK (PaymentType IN ('VISA',
								'MasterCard', 'American Express',
								'PayPal', 'Check', 'Cash')),
	CONSTRAINT 			INVOICE_CUSTOMER_FK FOREIGN KEY(CustomerID)
							REFERENCES CUSTOMER(CustomerID)
								ON UPDATE NO ACTION
								ON DELETE NO ACTION
    ) auto_increment=35000;

CREATE  TABLE LINE_ITEM(
    InvoiceNumber		Int				NOT NULL,
	LineNumber			Int				NOT NULL,
	ProductNumber		Char(35) 		NOT NULL,
	Quantity			Int				NOT NULL,
	UnitPrice			Numeric(9,2)	NULL,
	Total				Numeric(9,2)	NULL,
	CONSTRAINT 			LINE_ITEM_PK 	PRIMARY KEY (InvoiceNumber, LineNumber),
	CONSTRAINT 			L_I_INVOICE_FK 	FOREIGN KEY(InvoiceNumber)
							REFERENCES INVOICE(InvoiceNumber)
								ON UPDATE NO ACTION
								ON DELETE CASCADE,
	CONSTRAINT 			L_I_PRODUCT_FK 	FOREIGN KEY(ProductNumber)
							REFERENCES PRODUCT (ProductNumber)
								ON UPDATE CASCADE
								ON DELETE NO ACTION
    );


*****   CUSTOMER DATA   ********************************************************/

INSERT INTO CUSTOMER VALUES(
	null, 'Jacobs', 'Nancy', 'Nancy.Jacobs@somewhere.com', 'nf46tG9E', '817-871-8123',
	'1440 West Palm Drive', 'Fort Worth', 'TX', '76110');
INSERT INTO CUSTOMER VALUES(
	null, 'Jacobs', 'Chantel', 'Chantel.Jacobs@somewhere.com', 'b65TG03f', '817-871-8234',
	'1550 East Palm Drive', 'Fort Worth', 'TX', '76112');
INSERT INTO CUSTOMER VALUES(
	null,'Able', 'Ralph', 'Ralph.Able@somewhere.com', 'm56fGH08', '210-281-7987',
	'123 Elm Street', 'San Antonio', 'TX', '78214');
INSERT INTO CUSTOMER VALUES(
	null,'Baker', 'Susan', 'Susan.Baker@elsewhere.com', 'PC93fEk9', '210-281-7876',
	'456 Oak Street', 'San Antonio', 'TX', '78216');
INSERT INTO CUSTOMER VALUES(
	null,'Eagleton', 'Sam', 'Sam.Eagleton@elsewhere.com', 'bnvR44W8', '210-281-7765',
	'789 Pine Street', 'San Antonio', 'TX', '78218');
INSERT INTO CUSTOMER VALUES(
	null,'Foxtrot', 'Kathy', 'Kathy.Foxtrot@somewhere.com', 'aa8tY4GL', '972-233-6234',
	'11023 Elm Street', 'Dallas', 'TX', '75220');
INSERT INTO CUSTOMER VALUES(
	null,'George', 'Sally', 'Sally.George@somewhere.com', 'LK8G2tyF', '972-233-6345',
	'12034 San Jacinto', 'Dallas', 'TX', '75223');
INSERT INTO CUSTOMER VALUES(
	null,'Hullett', 'Shawn', 'Shawn.Hullett@elsewhere.com', 'bu78WW3t', '972-233-6456',
	'13045 Flora', 'Dallas', 'TX', '75224');
INSERT INTO CUSTOMER VALUES(
	null,'Pearson', 'Bobbi', 'Bobbi.Pearson@elsewhere.com', 'kq6N2O0p', '512-974-3344',
	'43 West 23rd Street', 'Auston', 'TX', '78710');
INSERT INTO CUSTOMER VALUES(
	null,'Ranger', 'Terry', 'Terry.Ranger@somewhere.com', 'bv3F9Qc4', '512-974-4455',
	'56 East 18th Street', 'Auston', 'TX', '78712');
INSERT INTO CUSTOMER VALUES(
	null,'Tyler', 'Jenny', 'Jenny.Tyler@somewhere.com', 'Yu4be77Z', '972-233-6567',
	'14056 South Ervay Street', 'Dallas', 'TX', '75225');
INSERT INTO CUSTOMER VALUES(
	null,'Wayne', 'Joan', 'Joan.Wayne@elsewhere.com', 'JW4TX6g', '817-871-8245',
	'1660 South Aspen Drive', 'Fort Worth', 'TX', '76115');


/*****   SEMINAR   **************************************************************/

INSERT INTO SEMINAR VALUES(
	null,'2013-10-12', '11:00', 'San Antonio Convention Center',
	'Kitchen on a Budget');
INSERT INTO SEMINAR VALUES(
	null,'2013-10-26', '16:00', 'Dallas Convention Center',
	'Kitchen on a Big D Budget');
INSERT INTO SEMINAR VALUES(
	null,'2013-11-02', '08:30', 'Austin Convention Center',
	'Kitchen on a Budget');
INSERT INTO SEMINAR VALUES(
	null,'2014-03-22', '11:00', 'Dallas Convention Center',
	'Kitchen on a Big D Budget');
INSERT INTO SEMINAR VALUES(
	null,'2014-03-23', '11:00', 'Dallas Convention Center',
	'Kitchen on a Big D Budget');
INSERT INTO SEMINAR VALUES(
	null,'2014-04-05', '08:30', 'Austin Convention Center',
	'Kitchen on a Budget');


/*****   SEMINAR_CUSTOMER DATA   ************************************************/

INSERT INTO SEMINAR_CUSTOMER VALUES(1, 1);
INSERT INTO SEMINAR_CUSTOMER VALUES(1, 2);
INSERT INTO SEMINAR_CUSTOMER VALUES(1, 3);
INSERT INTO SEMINAR_CUSTOMER VALUES(1, 4);
INSERT INTO SEMINAR_CUSTOMER VALUES(1, 5);
INSERT INTO SEMINAR_CUSTOMER VALUES(2, 6);
INSERT INTO SEMINAR_CUSTOMER VALUES(2, 7);
INSERT INTO SEMINAR_CUSTOMER VALUES(2, 8);
INSERT INTO SEMINAR_CUSTOMER VALUES(3, 9);
INSERT INTO SEMINAR_CUSTOMER VALUES(3, 10);
INSERT INTO SEMINAR_CUSTOMER VALUES(4, 6);
INSERT INTO SEMINAR_CUSTOMER VALUES(4, 7);
INSERT INTO SEMINAR_CUSTOMER VALUES(4, 11);
INSERT INTO SEMINAR_CUSTOMER VALUES(4, 12);


/*****   CONTACT DATA   *********************************************************/

-- 'Nancy.Jacobs@somewhere.com'
INSERT INTO CONTACT VALUES(1, 1, '2013-10-12', 'Seminar', 1);
-- 'Chantel.Jacobs@somewhere.com'
INSERT INTO CONTACT VALUES(2, 1, '2013-10-12', 'Seminar', 1);
-- 'Ralph.Able@somewhere.com'
INSERT INTO CONTACT VALUES(3, 1, '2013-10-12', 'Seminar', 1);
-- 'Susan.Baker@elsewhere.com'
INSERT INTO CONTACT VALUES(4, 1, '2013-10-12', 'Seminar', 1);
-- 'Sam.Eagleton@elsewhere.com'
INSERT INTO CONTACT VALUES(5, 1, '2013-10-12', 'Seminar', 1);

-- 'Nancy.Jacobs@somewhere.com',
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(1, 2, '2013-10-15', 'EmailSeminarMessage');
-- 'Chantel.Jacobs@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(2, 2, '2013-10-15', 'EmailSeminarMessage');
-- 'Ralph.Able@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(3, 2, '2013-10-15', 'EmailSeminarMessage');
-- 'Susan.Baker@elsewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(4, 2, '2013-10-15', 'EmailSeminarMessage');
-- 'Sam.Eagleton@elsewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(5, 2, '2013-10-15', 'EmailSeminarMessage');


-- 'Nancy.Jacobs@somewhere.com',
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(1, 3, '2013-10-15', 'FormLetterSeminar');
-- 'Chantel.Jacobs@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(2, 3, '2013-10-15', 'FormLetterSeminar');
-- 'Ralph.Able@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(3, 3, '2013-10-15', 'FormLetterSeminar');
-- 'Susan.Baker@elsewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(4, 3, '2013-10-15', 'FormLetterSeminar');
-- 'Sam.Eagleton@elsewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(5, 3, '2013-10-15', 'FormLetterSeminar');

-- 'Kathy.Foxtrot@somewhere.com'
INSERT INTO CONTACT VALUES(6, 1, '2013-10-26', 'Seminar', 2);
-- 'Sally.George@somewhere.com'
INSERT INTO CONTACT VALUES(7, 1, '2013-10-26', 'Seminar', 2);
-- 'Shawn.Hullett@elsewhere.com'
INSERT INTO CONTACT VALUES(8, 1, '2013-10-26', 'Seminar', 2);

-- 'Kathy.Foxtrot@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(6, 2, '2013-10-30', 'EmailSeminarMessage');
-- 'Sally.George@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(7, 2, '2013-10-30', 'EmailSeminarMessage');
-- 'Shawn.Hullett@elsewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(8, 2, '2013-10-30', 'EmailSeminarMessage');

-- 'Kathy.Foxtrot@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(6, 3, '2013-10-30', 'FormLetterSeminar');
-- 'Sally.George@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(7, 3, '2013-10-30', 'FormLetterSeminar');
-- 'Shawn.Hullett@elsewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(8, 3, '2013-10-30', 'FormLetterSeminar');

-- 'Bobbi.Pearson@elsewhere.com'
INSERT INTO CONTACT VALUES(9, 1, '2013-11-02', 'Seminar', 3);
-- 'Terry.Ranger@somewhere.com'
INSERT INTO CONTACT VALUES(10, 1, '2013-11-02', 'Seminar', 3);

-- 'Bobbi.Pearson@elsewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(9, 2, '2013-11-06', 'EmailSeminarMessage');
-- 'Terry.Ranger@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(10, 2, '2013-11-06', 'EmailSeminarMessage');

-- 'Bobbi.Pearson@elsewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(9, 3, '2013-11-06', 'FormLetterSeminar');
-- 'Terry.Ranger@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(10, 3, '2013-11-06', 'FormLetterSeminar');


-- 'Ralph.Able@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(3, 4, '2014-02-20', 'WebAccountCreation');
-- 'Ralph.Able@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(3, 5, '2014-02-20', 'EmailAccountMessage');
-- 'Kathy.Foxtrot@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(6, 4, '2014-02-22', 'WebAccountCreation');
-- 'Kathy.Foxtrot@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(6, 5, '2014-02-22', 'EmailAccountMessage');
-- 'Sally.George@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(7, 4, '2014-02-25', 'WebAccountCreation');
-- 'Sally.George@somewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(7, 5, '2014-02-25', 'EmailAccountMessage');
-- 'Shawn.Hullett@elsewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(8, 4, '2014-03-07', 'WebAccountCreation');
-- 'Shawn.Hullett@elsewhere.com'
INSERT INTO CONTACT (CustomerID, ContactNumber, ContactDate,  ContactType)
	VALUES(8, 5, '2014-03-07', 'EmailAccountMessage');

-- 'Kathy.Foxtrot@somewhere.com'
INSERT INTO CONTACT VALUES(6, 6, '2014-03-22', 'Seminar', 4);
-- 'Sally.George@somewhere.com'
INSERT INTO CONTACT VALUES(7, 6, '2014-03-22', 'Seminar', 4);
-- 'Jenny.Tyler@somewhere.com'
INSERT INTO CONTACT VALUES(11, 1, '2014-03-22', 'Seminar', 4);
-- 'Joan.Wayne@elsewhere.com'
INSERT INTO CONTACT VALUES(12, 1, '2014-03-22', 'Seminar', 4);


/*****   PRODUCT DATA   *********************************************************/

INSERT INTO PRODUCT VALUES(
	'VK001', 'Video', 'Kitchen Remodeling Basics',14.95, 50);
INSERT INTO PRODUCT VALUES(
	'VK002', 'Video', 'Advanced Kitchen Remodeling', 14.95, 35);
INSERT INTO PRODUCT VALUES(
	'VK003', 'Video', 'Kitchen Remodeling Dallas Style', 19.95, 25);
INSERT INTO PRODUCT VALUES(
	'VK004', 'Video', 'Heather Sweeney Seminar Live in Dallas on 25-OCT-13', 24.95, 20);
INSERT INTO PRODUCT VALUES(
	'VB001', 'Video Companion', 'Kitchen Remodeling Basics', 7.99, 50);
INSERT INTO PRODUCT VALUES(
	'VB002', 'Video Companion', 'Advanced Kitchen Remodeling I',7.99, 35);
INSERT INTO PRODUCT VALUES(
	'VB003', 'Video Companion', 'Kitchen Remodeling Dallas Style', 9.99, 25);
INSERT INTO PRODUCT VALUES(
	'BK001', 'Book', 'Kitchen Remodeling Basics For Everyone', 24.95, 75);
INSERT INTO PRODUCT VALUES(
	'BK002', 'Book', 'Advanced Kitchen Remodeling For Everyone', 24.95, 75);
INSERT INTO PRODUCT VALUES(
	'BK003', 'Book', 'Kitchen Remodeling Dallas Style For Everyone', 24.95, 75);

/*****   INVOICE DATA    **************************************************/

/*****   Invoice 35000   **************************************************/
-- 'Ralph.Able@somewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2013-10-15', 3, 'VISA', 22.94, 5.95, 1.31, 30.20);
INSERT INTO LINE_ITEM VALUES(35000, 1, 'VK001', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35000, 2, 'VB001', 1, 7.99, 7.99);

/*****   Invoice 35001   **************************************************/
-- 'Susan.Baker@elsewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2013-10-25', 4, 'MasterCard', 47.89, 5.95, 2.73, 56.57);
INSERT INTO LINE_ITEM VALUES(35001, 1, 'VK001', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35001, 2, 'VB001', 1, 7.99, 7.99);
INSERT INTO LINE_ITEM VALUES(35001, 3, 'BK001', 1, 24.95, 24.95);

/*****   Invoice 35002   **************************************************/
-- 'Sally.George@somewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2013-12-20', 7, 'VISA', 24.95, 5.95, 1.42, 32.32);
INSERT INTO LINE_ITEM VALUES(35002, 1, 'VK004', 1, 24.95, 24.95);

/*****   Invoice 35003   **************************************************/
-- 'Susan.Baker@elsewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2014-03-25', 4, 'MasterCard', 64.85, 5.95, 3.70, 74.50);
INSERT INTO LINE_ITEM VALUES(35003, 1, 'VK002', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35003, 2, 'BK002', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35003, 3, 'VK004', 1, 24.95, 24.95);

/*****   Invoice 35004   ***************************************************/
-- 'Kathy.Foxtrot@somewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2014-03-27', 6, 'MasterCard', 94.79, 5.95, 5.40, 106.14);
INSERT INTO LINE_ITEM VALUES(35004, 1, 'VK002', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35004, 2, 'BK002', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35004, 3, 'VK003', 1, 19.95, 19.95);
INSERT INTO LINE_ITEM VALUES(35004, 4, 'VB003', 1, 9.99, 9.99);
INSERT INTO LINE_ITEM VALUES(35004, 5, 'VK004', 1, 24.95, 24.95);

/*****   Invoice 35005   ***************************************************/
-- 'Sally.George@somewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2014-03-27', 7, 'MasterCard', 94.80, 5.95, 5.40, 106.15);
INSERT INTO LINE_ITEM VALUES(35005, 1, 'BK001', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35005, 2, 'BK002', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35005, 3, 'VK003', 1, 19.95, 19.95);
INSERT INTO LINE_ITEM VALUES(35005, 4, 'VK004', 1, 24.95, 24.95);

/*****   Invoice 35006   ***************************************************/
-- 'Bobbi.Pearson@elsewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2014-03-31', 9, 'VISA', 47.89,	5.95, 2.73, 56.57);
INSERT INTO LINE_ITEM VALUES(35006, 1, 'BK001', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35006, 2, 'VK001', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35006, 3, 'VB001', 1, 7.99, 7.99);

/*****   Invoice 35007   ***************************************************/
-- 'Jenny.Tyler@somewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2014-04-03', 11, 'MasterCard', 109.78, 5.95, 6.26, 121.99);
INSERT INTO LINE_ITEM VALUES(35007, 1, 'VK003', 2, 19.95, 39.90);
INSERT INTO LINE_ITEM VALUES(35007, 2, 'VB003', 2, 9.99, 19.98);
INSERT INTO LINE_ITEM VALUES(35007, 3, 'VK004', 2, 24.95, 49.90);

/*****   Invoice 35008   ***************************************************/
-- 'Sam.Eagleton@elsewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2014-04-08', 5, 'MasterCard', 47.89, 5.95, 2.73, 56.57);
INSERT INTO LINE_ITEM VALUES(35008, 1, 'BK001', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35008, 2, 'VK001', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35008, 3, 'VB001', 1, 7.99, 7.99);

/*****   Invoice 35009   ***************************************************/
-- 'Nancy.Jacobs@somewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2014-04-08', 1, 'VISA', 47.89,	5.95, 2.73, 56.57);
INSERT INTO LINE_ITEM VALUES(35009, 1, 'BK001', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35009, 2, 'VK001', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35009, 3, 'VB001', 1, 7.99, 7.99);

/*****   Invoice 35010   ***************************************************/
-- 'Ralph.Able@somewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2014-04-23', 3, 'VISA', 24.95,	5.95, 1.42, 32.32);
INSERT INTO LINE_ITEM VALUES(35010, 1, 'BK001', 1, 24.95, 24.95);

/*****   Invoice 35011   ***************************************************/
-- 'Bobbi.Pearson@elsewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2014-05-07', 9, 'VISA', 22.94,	5.95, 1.31, 30.20);
INSERT INTO LINE_ITEM VALUES(35011, 1, 'VK002', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35011, 2, 'VB002', 1, 7.99, 7.99);

/*****   Invoice 35012   ***************************************************/
-- 'Shawn.Hullett@elsewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2014-05-21', 8, 'MasterCard', 54.89, 5.95, 3.13, 63.97);
INSERT INTO LINE_ITEM VALUES(35012, 1, 'VK003', 1, 19.95, 19.95);
INSERT INTO LINE_ITEM VALUES(35012, 2, 'VB003', 1, 9.99, 9.99);
INSERT INTO LINE_ITEM VALUES(35012, 3, 'VK004', 1, 24.95, 24.95);

/*****   Invoice 35013   ***************************************************/
-- 'Ralph.Able@somewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2014-06-05', 3, 'VISA', 47.89,	5.95, 2.73, 56.57);
INSERT INTO LINE_ITEM VALUES(35013, 1, 'VK002', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35013, 2, 'VB002', 1, 7.99, 7.99);
INSERT INTO LINE_ITEM VALUES(35013, 3, 'BK002', 1, 24.95, 24.95);

/*****   Invoice 35014   ***************************************************/
-- 'Jenny.Tyler@somewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2014-06-05', 11, 'MasterCard', 45.88, 5.95, 2.62, 54.45);
INSERT INTO LINE_ITEM VALUES(35014, 1, 'VK002', 2, 14.95, 29.90);
INSERT INTO LINE_ITEM VALUES(35014, 2, 'VB002', 2, 7.99, 15.98);

/*****   Invoice 35015   ***************************************************/
-- 'Joan.Wayne@elsewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2014-06-05', 12, 'MasterCard', 94.79, 5.95, 5.40, 106.14);
INSERT INTO LINE_ITEM VALUES(35015, 1, 'VK002', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35015, 2, 'BK002', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35015, 3, 'VK003', 1, 19.95, 19.95);
INSERT INTO LINE_ITEM VALUES(35015, 4, 'VB003', 1, 9.99, 9.99);
INSERT INTO LINE_ITEM VALUES(35015, 5, 'VK004', 1, 24.95, 24.95);

/*****   Invoice 35016   ***************************************************/
-- 'Ralph.Able@somewhere.com'
INSERT INTO INVOICE VALUES(
	null,'2014-06-05', 3, 'VISA', 45.88,	5.95, 2.62, 54.45);
INSERT INTO LINE_ITEM VALUES(35016, 1, 'VK001', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35016, 2, 'VB001', 1, 7.99, 7.99);
INSERT INTO LINE_ITEM VALUES(35016, 3, 'VK002', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35016, 4, 'VB002', 1, 7.99, 7.99);


/***************************************************************************/


HSDDW CreateTablesWithData.sql

/* HSD Data Warehouse star schema table */

DROP SCHEMA IF EXISTS hsddw;
CREATE SCHEMA hsddw;
USE hsddw;

create table timeline(
	timeid 		int 		not null auto_increment primary key,
	date   		date 		not null,
	monthid 	int		not null,
	monthtext 	char(15) 	not null,
	quarterid 	int		not null,
	quartertext 	char(10) 	not null,
	year		char(10)	not null
);

create table customer (
	customerid	int		not null primary key,
	customername	varchar(75)	not null,
	emaildomain	varchar(100)	not null,
	phoneareacode	char(6)		not null,
	city		char(35)	 null,
	state		char(2)		 null,
	zip		char(10)	 null
);

create table product (
	productnumber	char(35)	not null primary key,
	producttype	char(25)	not null,
	productname	varchar(75)	not null
);

create table product_sales (

		timeid		int		not null,

		customerid	int		not null,

		productnumber	char(35)	not null,

		quantity	int		not null,

		unitprice	numeric(9,2)	not null,

		total		numeric(9,2)	null,

	constraint product_sales_pk primary key (timeid, customerid, productnumber),

	constraint timeline_fk foreign key(timeid) references timeline(timeid) 
	
	 on delete no action on update no action,

       constraint customerid_fk foreign key(customerid) references customer(customerid) 
		
         on delete no action on update no action,

      constraint product_fk foreign key(productnumber) references product(productnumber)
	
        on delete no action	on update no action 


);


/*  months table used when loading timeline table */
create table months (
	monthid		int		not null,
	monthtext	char(15)	not null,
	quarterid	int		not null,
	quartertext	char(10)	not null
);

Insert Record


insert into months values
(1, 'January', 1, 'Qtr1'),
(2, 'February', 1, 'Qtr1'),
(3, 'March', 1, 'Qtr1'),
(4, 'April', 2, 'Qtr2'),
(5, 'May', 2, 'Qtr2'),
(6, 'June', 2, 'Qtr2'),
(7, 'July', 3, 'Qtr3'),
(8, 'August', 3, 'Qtr3'),
(9, 'September', 3, 'Qtr3'),
(10, 'October', 4, 'Qtr4'),
(11, 'November', 4,'Qtr4'),
(12, 'December', 4, 'Qtr4');

/*  load the timeline table from invoice data */

insert into hsddw.timeline (date, monthid, monthtext, quarterid, quartertext, year) 

Test by Select:

select distinct a.invoicedate, month(a.invoicedate), b.monthtext, b.quarterid, b.quartertext, year(a.invoicedate)

from hsd.invoice a, hsddw.months b where month(a.invoicedate) = b.monthid; 

/* load the hsddw customer table from hsd customer */

insert into hsddw.customer 

(customerid, customername, emaildomain, phoneareacode, city, state, zip)

Select Record for test data

select customerid, concat( trim(lastname), ' ', trim(firstname)), substring(emailaddress, locate('@', emailaddress)+1),   
         substring(phone,1,3), city, state, zip 

from hsd.customer;

/* load hsddw product table from hsd product */

insert into hsddw.product select productnumber, producttype, productdescription from hsd.product;

/* load product sales */ 
/* multiple sales of the same product to the same customer on same date are combined */

insert into hsddw.product_sales 

 (timeid, customerid, productnumber, quantity, unitprice, total) 

Select Record for test data

select  c.timeid, a.customerid, b.productnumber, sum(b.quantity), min(b.unitprice), sum(b.total)
 
from  hsd.invoice a, hsd.line_item b, hsddw.timeline c  
 
where a.invoicenumber = b.invoicenumber and a.invoicedate=c.date
 
group by c.timeid, a.customerid, b.productnumber;



To get the solution of above problem you can contact with us at:


We are also providing other database related help with an affordable price. Here you get plagiarism free work by experience professionals and expert.

bottom of page