Assignment -

Purpose: To analyze an existing database design. To design a data warehouse and perform initial upload of data.

Part 1 - Use the following script to create tables on the Oracle 11g server

CREATE TABLE customers (

customer_id INTEGER CONSTRAINT customers_pk PRIMARY KEY,

first_name VARCHAR2(10) NOT NULL,

last_name VARCHAR2(10) NOT NULL,

dob DATE,

phone VARCHAR2(12)

);

CREATE TABLE product_types (

product_type_id INTEGER CONSTRAINT product_types_pk PRIMARY KEY,

name VARCHAR2(10) NOT NULL

);

CREATE TABLE products (

product_id INTEGER CONSTRAINT products_pk PRIMARY KEY,

product_type_id INTEGER

CONSTRAINT products_fk_product_types

REFERENCES product_types(product_type_id),

name VARCHAR2(30) NOT NULL,

description VARCHAR2(50),

price NUMBER(5, 2)

);

CREATE TABLE purchases (

Purchase_id INTEGER PRIMARY KEY,

product_id INTEGER

CONSTRAINT purchases_fk_products

REFERENCES products(product_id),

customer_id INTEGER

CONSTRAINT purchases_fk_customers

REFERENCES customers(customer_id),

quantity INTEGER NOT NULL,

purchase_date DATE NOT NULL

);

1. Use PowerDesigner to reverse engineer the script, and create a CDM (ERD) for the above database.

2. Prepare a physical model, create DDL script, and create tables. If necessary, introduce your own changes to the script. For example: create sequences, add necessary indexes. Create the final DDL Script.

3. Add data to test the next step. Add purchases for 2-3 month within 2 (or more) years.

4. Prepare SQL queries to list he following data:

a. For each product, a total # of products sold in all years and each month within the year. Use ROLLUP.

b. For each product, a total $ amount for products sold in all years and each month within the year. Use ROLLUP.

Part 2 - Design a data warehouse PURCHASES using a star model (or snowflake). Include facts related to number of products purchased and total amount of $ for products purchased. Use the following dimensions: time (month, year), product, customer (age group). Create a conceptual and physical model for the data warehouse using PowerDesigner (please prefix all tables with DW1). Print out your physical model. Generate the schema for Oracle and run the script to create appropriate tables. Load the data into the data warehouse using tables from part 1.

Prepare the following OLAP queries:

1. What was the total number of products sold in each year?

2. What was the month and year of the highest purchases in $?

3. What was the total number of products ordered by customers older than 65?

Part 3 - 1. Create an arff file for the following Shopping basket (you may change your data)

@relation SHOPPING

@attribute pencil {0,1}

@attribute magazine {0,1}

@attribute banana {0,1}

@attribute paper {0,1}

@attribute milk {0,1}

@attribute apple {0,1}

@attribute pear {0,1}

@data

0,0,1,0,1,1,1

0,0,1,0,1,1,0

1,1,0,1,0,0,0

1,0,1,1,0,0,0

1,0,0,1,0,0,0

1,0,0,1,1,0,1

0,1,1,0,1,1,0

1,1,1,0,0,0,1

1,0,1,1,1,1,0

0,0,1,0,1,1,1

Ru the associate rules Apriori algorithm and report your results:

a. What is minimum support reported? (****)

b. Minimum confidence? (****)

c. Best rules found? (****)

Attachment:- Assignment File.rar