CCS341 Set3

B.E / B.Tech.
PRACTICAL END SEMESTER EXAMINATIONS, NOVEMBER/DECEMBER 2023
Fifth Semester
CCS341 - DATA WAREHOUSING
(Regulations 2021)
Time : 3 Hours Answer any one Question Max. Marks 100
Aim/Principle/Apparatus Tabulation/Circuit/Pr Calculation & Viva-Voce Record Total

required/Procedure ogram/Drawing Results
20 30 30 10 10 100
1. Build Data Warehouse/Data Mart for the following scenario.
The data warehouse contains 4 tables
1. Date dimension: contains every single date from 2006 to 2016.
2. Customer dimension: contains 100 customers.
3. Van dimension: contains 20 vans.
4. Hire fact table: contains 1000 hire transactions since 1st Jan 2011. It is a daily snapshot fact table so
that every day we insert 1000 rows into this fact table. So over time we can track the changes of total bill,
van charges, satnav income, etc.
create source tables and populate sample data
2. Design multi-demesional data models namely Star, Snowflake and Fact Constellation schemas for
banking enterprise
3. let’s import the daily levels of a river and the rainfall data from the basin, stored in a CSV file.
 Create a summary of the river_data.
 Create a plot of the RAIN and LEVEL.
 Plot the LEVEL for the year “2001.”
4. Using the iris data set and do the following.
1. Load the iris dataset
2. Convert the data type of sepal_length column
3. Transpose the data tables
Page 1 of 3
4. Sort the first column
5. Create a scatter plot for the data
6. Remove duplicate values
7. Group the variables and find the count, average and sum of each column
8. Fill the missing values if any
5. Consider the following cubes illustrating temperature of certain days recorded weekly:
Temperature 64 65 68 69 70 71 72 75 80 81 83 85
Week1 1 0 1 0 1 0 0 0 0 0 1 0
Week2 0 0 0 1 0 0 1 2 0 1 0 0
Perform OLAP operations on the above data
6. Using the Soybeans dataset, do the following
i. List the attribute names and types
ii. Number of records in each dataset
iii. Identify the class attribute
iv. Plot histogram
v. Determine the number of records in each class
7. Load labor dataset into Weka and run Apriori algorithm with different support and confidence values.
Study the rules generated.
8. Design dataware house for e-commerce application.
9. Apply Weka Tool to load the dataset and then pre-process it
10. Write a program to construct decision tree and perform cross-validation
11. Create sql schema definition for payroll management system
12. Plan dataware house architecture for school management system
13. Write schema definition query for Staff management system
Page 2 of 3
14. Assume that you have 3 dimensional data such as doctor, patient and time period. Create a OLAP cube
and do all the operations.
15. Design a normalized database schema to support OLTP operations for an e-commerce website. Model
the relationships between the main entities relevant to an e-commerce platform (e.g., customers,
products, orders, payments). Normalize the database schema to eliminate redundancy and maintain data
integrity. Use a database design tool or software (e.g., MySQL Workbench) to create the schema based
on your design.
16. Perform a data profiling by identifying the tables and data sources within a data warehouse and do
column profiling that includes data types, null values, unique values, value distribution, minimum and
maximum values and then identify data anomalies or inconsistencies in the data.
17. Create SQL scripts that include multiple SQL statements to demonstrate a transaction for an e-commerce
data (e.g., inserting an order and updating product quantities) and implement a transaction framework in
a database system (e.g., BEGIN TRANSACTION, COMMIT, ROLLBACK). Execute the SQL script to
simulate a transaction and test the impact of a rollback in case of an error.
18. Set up a scenario with multiple users or connections trying to access and update the same data
simultaneously for an e-commerce data. Show how isolation levels (e.g., READ COMMITTED,
SERIALIZABLE) work in managing concurrent access to maintain data consistency. Perform SQL
transactions and show how locks are acquired and released
19. Create a data warehouse for customer management system and perform all types of testing.
20. Design multi-demesional data models namely Star, Snowflake and Fact Constellation schemas for
furniture enterprise
Page 3 of 3

CCS341 Set3

Uploaded by

Copyright:

Available Formats

CCS341 Set3

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

CCS341 Set3

Uploaded by

Copyright:

Available Formats

B.E / B.Tech.

PRACTICAL END SEMESTER EXAMINATIONS, NOVEMBER/DECEMBER 2023

CCS341 - DATA WAREHOUSING

Time : 3 Hours Answer any one Question Max. Marks 100

Aim/Principle/Apparatus Tabulation/Circuit/Pr Calculation & Viva-Voce Record Total

1. Build Data Warehouse/Data Mart for the following scenario.

The data warehouse contains 4 tables

1. Date dimension: contains every single date from 2006 to 2016.

2. Customer dimension: contains 100 customers.

3. Van dimension: contains 20 vans.

create source tables and populate sample data

 Create a summary of the river_data.

 Create a plot of the RAIN and LEVEL.

 Plot the LEVEL for the year “2001.”

4. Using the iris data set and do the following.

1. Load the iris dataset

2. Convert the data type of sepal_length column

3. Transpose the data tables

5. Create a scatter plot for the data

6. Remove duplicate values

8. Fill the missing values if any

Perform OLAP operations on the above data

6. Using the Soybeans dataset, do the following

i. List the attribute names and types

ii. Number of records in each dataset

iii. Identify the class attribute

iv. Plot histogram

v. Determine the number of records in each class

8. Design dataware house for e-commerce application.

9. Apply Weka Tool to load the dataset and then pre-process it

10. Write a program to construct decision tree and perform cross-validation

11. Create sql schema definition for payroll management system

12. Plan dataware house architecture for school management system

13. Write schema definition query for Staff management system

You might also like