تفاصيل المشروع

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

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

نقاط الجدية مشروع جاد