قواعد وتحليل بيانات

The assignment consists of a pdf file, an attached SQL file called “AppDownloadDB.sql”

CASE DESCRIPTION USN University has a research environment with a mobile app

store that allows students enrolled in any university worldwide to publish their

applications (App). For example, SVUstudents can publish their final mobile app

from MOB3000 in the SVU mobile app store, and they can receive feedback. User

details are securely stored in a database that includes information on the universities

where students are enrolled. Users can publish their applications and download those

created by others. They also have the ability to leave feedback and ratings, which are

tracked in the database along with download statistics. Apps are organized into various

categories, and many authors often have multiple apps across different sections,

reflecting a diverse range of interests and functionalities.

You will use the App download database as the source system to create a Business

Intelligence solution. Apps downloaded operational database is provided as an SQL file

called “AppDownload-DB.sql”. The ER diagram is the following:

The dimensional model has one fact table called AppsDownloadFACT and 4

dimensional tables: TimeDIM, CategoryDIM, LocationDIM, and UniversityDIM. The

primary keys and surrogate keys are written in bold font in Figure 2.

Part 1: Building the data warehouse using SQL

The aim of this part is to create the dimension and the fact tables and fill them with data

from the source database. These aims will be achieved using SQL and by carrying out

the following tasks:

1.1. Create a data warehouse dw. Provide the SQL code for this task.

1.2. Provide the SQL code that createsthe CategoryDIM table that belongs to the data

warehouse dw. The fields of this table are shown in Figure 2.

1.3. Fill the CategoryDIM table from the source database. Provide the SQL code for this

task.

1.4. Provide a screenshot of (1) the CategoryDIM table filled with data and (2) the SQL

code that displays the content of the table CategoryDIM.

1.5. Provide the SQL code that creates the UniversityDIM table that belongs to the data

warehouse dw. The fields of this table are shown in Figure 2.

1.6. Fill the UniversityDIM table from the source database. Provide the SQL code for this

task.

1.7. Provide a screenshot of (1) the UniversityDIM table filled with data and (2) the SQL

code that displays the content of the table UniversityDIM.

1.8. Provide the SQL code that creates the LocationDIM table that belongs to the data

warehouse dw. The fields of this table are shown in Figure 2.

1.9. Fill in the LocationDIM table from the source database. Provide the SQL code for

this task.

1.10. Provide a screenshot of (1) the LocationDIM table filled with data and (2) the SQL

code that displays the content of the table LocationDIM.

1.11. Provide the SQL code that creates the TimeDIM table that belongs to the data

warehouse dw. The fields of this table are shown in Figure 2.

1.12. Fill in the TimeDIM table from the source database. Provide the SQL code for this

task

1.13. Provide a screenshot of (1) the TimeDIM table filled with data and (2) the SQL code

that displays the content of the table TimeDIM.

1.14. Create the TempFact table, which is the fact stage table. Provide the SQL code for

this task.

1.15. Provide the SQL code to fill the TempFact table from the source database.

1.16. Add the surrogate keysto the staging table TempFact. Provide the SQL code for

this task.

1.17. Create the AppsDownloadFact table that belongs to the data warehouse dw.

Provide the SQL code for this task. The fields of this table are shown in Figure 2.

1.18. Provide the SQL code to fill the AppsDownloadFact table from the TempFact

table.

1.19. Provide a screenshot of (1) the AppsDownloadFact table filled with data and (2)

the SQL code that displays the content of the table AppsDownloadFact.

1.20. Provide all the SQL code in a file with the name “AppDownload-DW.sql”and

upload it on you pc