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

• f 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

• ther 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

• f 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

• f 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 !