1 / 47

Database Systems Applications

Database Systems Applications. Data Warehousing. Data Mining. Web. Applications. OLAP. Data Warehousing. Warehouse Management. Extraction Transformation Loading. Query Management. Operational Data. Star. Snowflake. Components. Schema. Starflake. Dimensional Access. Diverse

prewitt
Download Presentation

Database Systems Applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database SystemsApplications Data Warehousing Data Mining Web Applications OLAP

  2. DataWarehousing Warehouse Management Extraction Transformation Loading Query Management Operational Data Star Snowflake Components Schema Starflake Dimensional Access Diverse Data Sources Characteristics Volume of Data

  3. Data Warehouse - Definition • A data warehouse is a type of contemporary database system designed to fulfill decision-support needs • Differs from a conventional decision-support database in a number of ways: • Volume of data. A data warehouse is likely to hold far more data than a decision-support database. Volumes of the order of over 400 Giga-bytes of data are commonplace. • Diverse Data Sources. The data stored in a warehouse is likely to have been extracted from a diverse range of application systems, only some of which may be database systems. These systems are described as data sources. • Dimensional Access. A warehouse is designed to fulfill a number of distinct ways (dimensions) in which users may wish to retrieve data. This is sometimes referred to as the need to facilitate ad-hoc query.

  4. Data Warehouse - Features • Inmon defines a data warehouse as being a subject-oriented, integrated, time-variant, and non-volatile collection of data used in support of management decision-making • Subject-Oriented. A data warehouse is structured in terms of the major subject areas of the organisation such as, in the case of a university, students, lecturers and modules rather than in terms of application areas such as enrolment, payroll and time-tabling. • Integrated. A data warehouse provides a data repository which integrates data from different systems with data frequently in different formats. The objective is to provide a unified view of data for users. • Time-variant. A data warehouse explicitly associates time with data. Data in a warehouse is only valid for some point or period in time. • Non-volatile. The data in a data warehouse is not updated in real-time. Instead, it is refreshed from data in operational systems on a regular basis. A consequence of this is that the management of data integrity is not a critical issue for data warehouses.

  5. Data Warehouses/Data Marts Purchases Database Supermarket Chain HQ Data Warehouse Stores data Staff data Data Mart = smaller scale data warehouse

  6. Benefits of Data Warehousing • A data warehouse provides a single manageable structure for decision-support data • A data warehouse enables organisational users to run complex queries on data that traverses a number of business areas • A data warehouse enables a number of business intelligence applications such as on-line analytical processing and data mining

  7. Challenges of Data Warehousing • Knowing in advance the data users require and determining the ownership and responsibilities in terms of data sources • Selecting, installing and integrating different hardware and software required to set up the warehouse. The large volume of data needed in terms of a data warehouse requires large amounts of disk space. This means that estimation of storage volume is a significant activity • Identifying, reconciling and cleaning existing production data and loading it into the warehouse. The diverse sources of data feeding a data warehouse introduces problems of design in terms of creating a homogenuous data store. Problems are also introduced in terms of the effort required to extract, clean and load data into the warehouse • Managing the refresh or update process. Once a warehouse is established a clear scheme of effectively managing the high volume of complex, ad-hoc queries needs to be produced.

  8. Steps in Building a Data Warehouse • Users specify information needs • Analysts and users create a logical and physical design • Sources of data are identified in operational systems, external sources etc. • Source data is scrubbed, extracted and transformed • Data is transferred and loaded into the warehouse periodically • Users are given access to the warehouse data • The warehouse is maintained in terms of changing requirements

  9. Components of a Data Warehouse Relational Database Warehouse Management Query Tools Data Warehouse Network Database Extract, Transform, Load OLAP Tools Indexed Files Data Mining Tools

  10. Forms of Data in a Data Warehouse • Detailed Data. This comprises the detailed production data. Usually, detailed data is not stored on-line but is aggregated on a periodic basis • Summarised Data. Data in the warehouse is normally summarised or aggregated to speed up the performance of queries. The data may be lightly summarised or highly summarised. Summarised data needs to be updated periodically when the detailed data is refreshed. • Meta-data. Data about data is needed to enable the extraction, transformation and loading processes by mapping data sources to the warehouse schema. Meta-data is also used to automate the production of summary data and to facilitate query management. • Archive Data. Data needs to be periodically archived from the warehouse to prevent the database growing too large for its platform. Normally, this is done on the basis of some retention period established for data. • Backup Data. Just as in conventional databases, detailed, summary and meta-data needs to be backed up regularly in order to recover from failure.

  11. Designing the Data Warehouse Schema • Star Schema • Snowflake Schema • Starflake Schema

  12. A Star Schema Student Staff studentNo studentName studentDOB studentAddress staffNo staffName schoolName schoolLocation StudentAssessment studentNo staffNo assessmentNo assessmentResult Assessment assessmentNo assessmentTitle dateSet dateReturned moduleNo moduleName moduleLevel

  13. A StarFlake Schema Student Staff studentNo studentName studentDOB studentAddress staffNo staffName schoolName schoolLocation StudentAssessment studentNo staffNo assessmentNo assessmentResult ModuleAssessment Assessment assessmentNo moduleNo Module assessmentNo assessmentTitle dateSet dateReturned moduleNo moduleName moduleLevel

  14. Summary - Data Warehousing • A data warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data used in support of management decision-making. • A data mart is a small data warehouse, designed usually for a particular business area. • Data warehouses and data marts deliver support decision-support applications in organisations. • Data warehousing projects are large-scale development projects. • The components of a data warehouse include: Production Data, Extraction, Transformation and Loading Functions, Warehouse Management Functions, Query Management Functions. • Three specialist forms of schema are relavant to data warehousing applications: star, snowflake and starflake schemas.

  15. OLAP Client-Server Generic Dimensionality Consistent Reporting Performance Sparse Matrix Handling Accessibility Multi-User Cross-Dimensional Operations Rules Transparency Data Manipulation Multi-dimensional Conceptual View Flexible Reporting Pivoting Dimensions & Aggregation Levels MOLAP ROLAP Drilling Down Operations Consolidation Servers MQE

  16. Definition • OLAP is a term coined by Tedd Codd to define an architecture that supports complex analytical operations such as consolidation, drilling-down and pivoting: • Consolidation. Also referred to as rolling-up, consolidation comprises the aggregation of data such as modules data being aggregated into courses data and courses data being aggregated into schools or departments data. • Drilling-Down. This is the opposite of consolidation and involves revealing the detail or dis-aggregating data such as breaking-down school-based data into that appropriate to particular courses or modules. • Pivoting. This operation, sometimes referred to as ‘slicing and dicing’ comprises the ability to analyse the same data from different viewpoints, frequently along a time axis. For example in the university domain, one slice may be the average degree grade per course within a school. Another slice may be the average degree grade per age band within a school.

  17. Multi-Dimensional Data Course Computer Science Software Engineering Information Systems Year/ Semester 97/1 97/2 98/1 98/2 99/1 54% 54% 54% 54% 54% 54% 54% 54% 54% 54% 50% 47% 52% 48% 53% 54% Degree Level MSc 54% 54% 49% 58% 54% 56% BSc

  18. Codd’s Rules for OLAP Tools • Multi-dimensional conceptual view • Transparency • Accessibility • Consistent Reporting Performance • Client-server Architecture • Generic Dimensionality • Dynamic Sparse Matrix Handling • Multi-User Support • Unrestricted Cross-dimensional Operations • Intuitive Data Manipulation • Flexible Reporting • Unlimited Dimensions and Aggregation Levels

  19. OLAP Servers • Three different types of OLAP tool may run on the OLAP server : • Multi-dimensional OLAP (MOLAP). MOLAP tools use data structures founded in array technology and utilise efficient storage techniques to optimise disk management through sparse data management. Typically such tools demand a close coupling between the presentation and application layer of the three-tier architecture. Requests are issued from access tools in the presentation layer to the MOLAP server. The MOLAP server periodically refreshes its data from base relational and/or legacy systems • Relational OLAP (ROLAP). Sometimes called multi-relational OLAP, this has been the fastest growing area of OLAP tools. ROLAP utilises connection to existing RDBMS products through the maintenance of a meta-data layer. This meta-data layer allows the creation of multiple multi-dimensional views of two-dimensional relations. Some ROLAP products maintain enhanced query managers to enable multi-dimensional query, others emphasise the use of highly-denormalised database designs (typically a ‘star’ schema) to facillitate multi-dimensional access • Managed query environment (MQE). MQE systems are an intermediate form between ROLAP and OLAP servers. They provide limited capability to access either RDBMS directly or through using an intermediate MOLAP server. When interacting with the MOLAP server data will be delivered in the form of a datacube to the desktop where it is stored and can be accessed locally

  20. MOLAP Server MOLAP Server Access Tools Relational Databases and/or Legacy Systems 54% 54% Results 54% 54% 49% 49% Load Request

  21. ROLAP Server Relational Database Server Results Results Request SQL

  22. Managed Query Environment Relational Database Server Results SQL MOLAP Server 54% 54% Results 54% 54% 49% Request 49% Load

  23. Summary - On-line Analytical Processing • OLAP involves the multi-dimensional analysis of large volumes of data. • OLAP applications tend to utilise special-purpose multi-dimensional data structures. Such data structures are frequently visualised as cubes or cubes of cubes with each side of a cube being a dimension. • OLAP systems support complex analytical operations such as consolidation, drilling-down and pivoting. • Codd formulated twelve rules for selecting tools for OLAP. • There are three main categories of OLAP tool: multi-dimensional OLAP, relational OLAP, managed query environment.

  24. DataMining Data Visualisation Scalability Variety of Techniques Tools Data Preparation & Import Deviation Detection Link Analysis Database Segmentation Techniques Predictive Modelling

  25. Definition • Data mining is the process of extracting previously unknown data from large databases and using it to make organisational decisions. There are a number of features to this definition: • Data mining is concerned with the discovery of hidden, unexpected patterns of data. • Data mining usually works on large volumes of data. Frequently large volumes are needed to produce reliable conclusions in relation to data patterns. • Data mining is useful in making critical organisational decisions, particularly those of a strategic nature.

  26. Data Mining Operations or Techniques • Data Mining is implemented in terms of a number of operations or techniques • Four main operations associated with data mining: • predictive modelling • database segmentation • link analysis • deviation detection • Each operation may be implemented using a number of different techniques or algorithms. Since each particular technique has its own strengths and weaknesses, data mining tools may offer a choice of techniques to implement an operation:

  27. Predictive Modelling Predictive Modelling is an attempt to model the way in which humans learn. An existing data set is analysed to form a model of its essential characteristics. This model is developed using a supervised learning approach consisting of two phases. The first, training phase, involves building a model using a large sample of data known as the training set. The second, testing phase, involves trying out the model on new data. Predictive modelling is associated with the techniques of classification and value prediction. Classification involves determining a class for each row in a database.This may be done using decision trees or neural networks.

  28. Database Segmentation Database segmentation involves partitioning the database into a number of segments or clusters. Each segment comprises a set of rows having a number of properties in common. Segments are built and refined using a process of unsupervised learning.

  29. Decision Tree applicant gender = male no yes Business Studies applicant age > 20 no yes Software Engineering Computer Science

  30. Link Analysis Link analysis attempts to establish associations between individual records in the data set. Such associations are frequently represented as rules in a technique known as associations discovery. For instance, an analysis of data collected on student progression may identify a rule such as, if a computing student passes a first year programming module then in 40% of cases he or she will gain upper second class degree.

  31. Deviation Detection Deviation detection involves identifying so-called outliers in the population of data - i.e., those that deviate from some norm. Deviation detection can be detected by statistical and visualisation techniques such as linear regression.

  32. Data Mining Tools • Data preparation and import facilities. The data mining tool should be capable of importing data from the target environment. • Selection of data mining operations and techniques. The data mining tool should provide a range of algorithms for pattern detection. • Product scalability and performance. The tool should be capable of handling sufficient volumes of data and should offer satisfactory levels of performance in terms of data manipulation operations. • Facilities for data visualisation. The tool should be capable of presenting a number of different graphical representations of patterns detected.

  33. Summary - Data Mining • Data mining is the process of extracting previously unknown data from large databases and using it to make organisational decisions. • There are four main operations associated with data mining: predictive modelling, database segmentation, link analysis and deviation detection. • Each operation may be implemented using different algorithms. • A particular software vendor may offer a number of algorithms to implement an operation.

  34. Databases andWeb HTML Web Browser Hypermedia Static Report Publishing Query Publishing Web URL Domain Name Application Publishing Web-enabled Applications HTTP TCP/IP Internet Packet-Switching Server-side Includes CGI JDBC/JSQL Scripting Languages Construction Approaches Vendor Initiatives

  35. Technical Infrastructure of the Internet Packet-Switched Networks TCP/IP HTTP URLs Domain Names

  36. Packet-Switching Network Sub-Network Router Router Sub-Network

  37. TCP/IP Sender Receiver Identical Message Application Application Identical Packet Transport (TCP) Transport (TCP) Identical Datagram Internet Protocol (IP) Internet Protocol (IP) Network Identical Frame Network Communications Network

  38. Universal Resource Locators Server IP Address Domain Name Server Other Network IP Address IP Address IP Address

  39. Domain Names Sub-Domain Domain Type Country Code

  40. Fundamental Components of the Web Web Server Web Client Internet HTTP over TCP/IP network HTTP over TCP/IP network HTML Document HTML Document Browser

  41. Hypertext/Hypermedia Linear text (eg., novel) Hierarchical text (eg., textbook) Network text (Hypertext)

  42. HTTP Response Client Server Connection Request Close

  43. Web Browser Internet Web Site Access Device Browser

  44. Three-Tier Client-Server Architecture Web Server Database Server Web Client Request Data HTML Document HTML Document Browser

  45. Types of Networked Database Applications • Static Report Publishing • In this type of networked database application the DBMS generates a report, static form (a display only form), or response to a query in HTML format and posts this onto the web site. The traffic in this type of application is one-way, from the server to the client. The user provides no data to the application. • Query Publishing • In this type of networked database application a HTML form is generated containing text boxes in which the user may enter criteria for a query. Once the form is submitted a request is made to the DBMS which returns matching data or an error. • Application Publishing • In this type of networked database application the interfaces (both data entry and reports) are all web based. This is clearly a web-based emulation of the traditional information technology system architecture. However, such applications currently suffer from three major problems:

  46. Approaches to Building Networked Database Applications • Common Gateway Interface (CGI) • Server-side Includes • JDBC, JSQL • Scripting languages such as Javascript and Vbscript. • Vendor initiatives such as Microsoft’s Active Server Pages

  47. Summary - Networked Databases • The internet is a set of inter-connected computer networks distributed around the globe • The facilities provided by the World-Wide Web are the ones most readily associated with the concept of the Internet at the current time • The primary components of the Web comprise: Hypertext Transfer Protocol (HTTP), HyperText Markup Language (HTML), Universal Resource Locators, Browsers. • Web-based applications have encouraged thin-client or networked computing proposals. • Database systems are important in the context of managing large-scale web aplications through the use of dynamic pages. • Three types of applications for the use of database systems within Web applications are report publishing, query publishing, application publishing. • Different approaches for building networked database applications include: CGI, JDBC, Active Server Pages.

More Related