Data Base 2 Professor Carlo Vaccari Ainiwaer Aihemaiti - PowerPoint PPT Presentation

Data Base 2 Professor Carlo Vaccari  Ainiwaer Aihemaiti  E_mail:anwarsunboy@gmail.com

Main Contents  Topics of this course  Practical Work  Conclusions

Topics of this course  Main Purpose of DW  Database Design  Data Warehouses  Areas of application  Extraction, Transformation and Loading

Main Purpose of DW  Through collecting information from various sources ,integrate and reorganize the information , finally makes them available for analysis and evaluations aimed at planning and decision making in various fields

Data Base Design  Data Base-a collection of DATA, used to represent information of interest to an information system, managed by a DBMS.  Database Management System-DBMS: Software system able to manage collection of data(large, shared, persistent)and to ensure their reliability and privacy

Phases of Database Design Conceptual design:  The purpose of this is to represent the informal requirements of an application in terms of a conceptual schema that refers to a conceptual data model. Logical design  This consists of the translation of the conceptual schema defined in the preceding phase, into the logical schema of the database that refers to a logical data model. Physical design  In this phase, the logical schema is completed with the details of the physical implementation (file organization and indexes) on a given DBMS. The product is called the physical schema and refers to a physical data model.

Data Warehouse  Data warehousing is a process used to optimize business function. It is a storage structured that allows users to extract and retrieve necessary data.  A Data Warehouse is a collection of data that supports decision making and having the following characteristics: ● oriented to the subject of interest ● integrated and consistent ● representative of the temporal evolution ● non-volatile

Areas of application  Commerce (sales analysis and claims, control of shipments and inventories, customers relationship)  Manufacturing (control of production costs, support suppliers and orders)  Financial Services (risk analysis and credit card fraud detection)  Transport (fleet management)  Telecommunications (analysis of the flow of calls and customer profiling)  Health (analysis of admissions and discharges, cost center accounting)

Extraction, Transformation and Loading

Practical Work

Download and Install Pentaho Data Integration. ◆ servername=oracle.unicam.it ◆ DB Name=UGOV ◆ portnumber=1521 ◆ username=POLZONETTI ◆ password:******

Analyze University DB Design (From table P09_AD_GEN to table P12_DOM_CT) SELECT *FROM ESSE3_UNICAM.P11_AD_PIANI WHERE STU_ID BETWEEN 1 AND 2000 SELECT *FROM ESSE3_UNICAM.P11_AD_REG WHERE STU_ID BETWEEN 1 AND 2000 SELECT *FROM ESSE3_UNICAM.P11_AD_SCE WHERE STU_ID BETWEEN 1 AND 2000

Analyze University DB Design (From table P09_AD_GEN to table P12_DOM_CT) ◆ A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted. ◆ Before creating the foreign key, I create Indexes for each foreign key: create unique index ad_id on P09_AD_GEN(ad_id); ◆ Then I create the foreign keys: ALTER TABLE P09_AD_CDS ADD FOREIGN KEY (ad_id) REFERENCES P09_AD_GEN(ad_id) ON DELETE CASCADE;

Upload the five Tables ◆ Server name=survey.cs.unicam.it ◆ DB name =esse3 ◆ Port number=3306 ◆ User name=esse3 ◆ Password=esse3

Example: In this example I provide a report to make a comparison between the total number of students for 5 courses from 2007 to 2011. I have used the following query to make this report: select count(P04_MAT.STU_ID) as 'Total Students',P04_MAT.AA_ISCR_ID as 'Academic Year',P06_CDS.DES as Corso from P04_MAT,P06_CDS where P06_CDS.CDS_ID=P04_MAT.CDS_ID and (P06_CDS.TITOLO='COMPUTER SCIENCE' or P06_CDS.TITOLO='FISICA' or P06_CDS.TITOLO='CHIMICA' or P06_CDS.TITOLO='DESIGN' or P06_CDS.TITOLO='BIOTECNOLOGIE' ) and AA_ISCR_ID between 2007 and 2011 group by P06_CDS.DES,AA_ISCR_ID order by AA_ISCR_ID To do this report I need to P04_MAT table that contain the information of all registered the students STU_ID , RIGESTER YEAR,DIPARTMENT CODE... ,and P06_CDS table that contain the information of all the courses provided by the university. using P06_CDS.CDS_ID,P04_MAT.CDS_ID I can connect P06_CDS and P04_MAT

Comparison between the numbers of students in five different courses from 2007 to 2011

Common used tools in DW ◆ Pantaho : Pentaho is innovative low cost business analytic tool (BI Tool), which helps you to visualize and analyze data in form of business information. Through Pentaho you can build interactive Reports & Dashboards to discover the trends and anomalies in business. ◆ Mysql: MySQL is a open source Relational Database Management System. MySQL is very fast reliable and flexible Database Management System. It provides a very high performance and it is multi threaded and multi user Relational Database management system. ◆ Pentaho Reporting: Pentaho Reporting Community Edition (CE) which includes the Pentaho Report Designer, Pentaho Reporting Engine, Pentaho Reporting SDK and the common reporting libraries shared with the entire Pentaho BI Platform.

Conclusion ◆ Through this course , I have studied knowledge about : ● How to use Pentaho ,Pentaho Report designer and Mysql . ● The basic concepts about Database Design and Data Warehouse ● The steps of creating Data warehouse ● Through practical work , I have improved my skills of solving problems in Database design and Data warehouse

Thank you !