Subject Code & Title :- 7BUIS1OW_CW1 SQL Lite
Assessment type :- Assignment
Task : 1.1 Task Description
Problem specification:
Design a data mart solution for the European Centre for Disease Prevention and Control (ECDC)to monitor the daily number of new reported COVID-19 cases and deaths, Intensive care unit cases and vaccination progress across the EU/EEA area.Information regarding of new reported COVID-19 cases and deaths, Intensive care unit cases and vaccination progress is logically described with the of a UML class diagram shown in Figure-1.Primay keys are denoted as {PK} and the foreign keysare followed with {FK}.
7BUIS1OW_CW1 SQL Lite Assignment – UK.
Figure-1. Relational database schema derived from ECDC
The European health authorities would liketo build a strategic data martto answer some frequent queries the managers would like to answer are the following.
1.For each region and month report the COVID-19 cases and deaths Intensive care unit cases as reported by different sources and vaccination rates for each vaccine per target group.
2.For each country and quarter report the COVID-19 cases and deaths Intensive care unit cases and vaccination rates per target group
3.For each country and year report the COVID-19 cases and deaths Intensive care unit cases and vaccination rates for the plus 60+ group
Design :-
Considering the designed data mart and its cardinality decide whether and which materialized views are
convenient to improve response time of the frequent queries (consider all the frequent queries 1-3).
Please also consider attached information provided by European Centre for Disease Prevention and Control (ECDC) regarding
1. Variable_Dictionary_VaccineTracker
2. Variable_Dictionary_and_Disclaimer_hosp_icu_all_data
3. Variable_Dictionary_and_Disclaimer_variant-data
7BUIS1OW_CW1 SQL Lite Assignment – UK.
Task Deliver ables :-
1.Based on the integrated relational schema in Figure-1 design a data warehouse model (DFM); in particular the designed data mart must promptly answer to all the frequent queries ‘1-3’.
i. Build the Attribute Tree from the integrated relational schema
ii. Build the Fact Schema from Attribute Tree
2.Map the DFM model to a logical model (i.e. relational). Clearly display the main fact table(s) and dimensions.
3.Implement the above logical as a working data warehouse schema under MySQL/SQLITE or any other
suitable DBMS. Provide the DDL statements to create the proposed data-warehouse schema.
4.Considering the designed data warehouse and its cardinalities decide whether and which materialized
views are convenient to improve response time of the frequent queries (consider all the frequent queries).Explain the reasons for your choices
5.Provide and implement a materialised view(s) to answer the directors frequent queries ‘1-3’
1.For each region and month report the COVID-19 cases and deaths Intensive care unit cases as reported by different sources and vaccination rates for each vaccine per target group.
2.For each country and quarter report the COVID-19 cases and deaths Intensive care unit cases and vaccination rates per target group.
3.For each country and year report the COVID-19 cases and deaths Intensive care unit cases and vaccination rates for the plus 60+ group
Marks for queries 5.1-5.3, will be awarded as follows: 60% for correct query formulation and 40% for
appropriate display of results.
1.2 Conditions:
1. The report must express your own conclusions and findings.
2. The overall size of your report is restricted to 20 pages, in total. Reports exceeding the 20 pages limit will be subject to a penalty of 7% out of 50.
3. Your report should be referenced if necessary.
Marking Scheme :-
Due to the nature of the assessment candidates may come up with more than one equally correct solutions.Thus marks will be allocated as follows
1.Design a conceptual data warehouse model (DFM); in particular, the designed data warehouse must promptly answer to all the frequent queries ‘1-3’.
i. Build the Attribute Tree from the integrated relational schema
a.Construction of the Attribute Tree
b.Pruning of the Attribute Tree
ii. Build the Fact Schema (DFM) from Attribute Tree
a.Identification of Dimensions
b.Identification of Facts
c.Identification of Measures
d.Overall Correctness of delivered DFM model
2.Map the DFM model to a logical model (i.e. relational). Clearly display the main fact table(s) and dimensions.
Mapping of Dimensions
Mapping of Facts
Identification of Measures
3.Implement the above logical as a working data warehouse schema under SQLITE/MySQL/R/Python or any other suitable DBMS. Provide the DDL statements to create the proposed data-warehouse schema.
Implementation of Dimensions
Implementation of Facts
Implementation of Measures
4.Considering the designed data warehouse and its cardinalities decide whether and which materialized views are convenient to improve response time of the frequent queries (consider all the frequent queries).Explain reasons for your choices
Calculation of Fact table(s) size [2.5 Marks]
Matrix Specification, including involved queries against Group BY and where clauses
Justified choice of Materialised views
7BUIS1OW_CW1 SQL Lite Assignment – UK.
5.Provide and implement a materialised view(s) to answer the directors frequent queries ‘1-3
1.For each region and month report the COVID-19 cases and deaths, Intensive care unit cases as reported by different sources and vaccination rates for each vaccine per target group.
2.For each country and quarter report the COVID-19 cases and deaths Intensive care unit cases and vaccination rates per target group.
3.For each country and year report the COVID-19 cases and deaths Intensive care unit cases and vaccination rates for the plus 60+ group.
Marks for 5.1-5.3, will be awarded as follows: 60% for correct query formulation and 40% for appropriate display of results
ORDER This 7BUIS1OW_CW1 SQL Lite Assignment NOW And Get Instant Discount
Read More :-
