Exam questions on Data Management.

University Degree Business and Administrative studies

Exam Title: Data Management

Question 1

Discuss the advantages of intranet technology over traditional approaches to information dissemination. (7 marks)

The term intranet began to be used in 1995.

An intranet is an internal privately accessible organizational network that can provide access to data across the enterprise, constructed using Internet technology and tools, and connected to the internet through a firewall.

Intranet is a corporate LAN (local area network) or WAN (wide area network) and it can rely on one of them or both. It is built on public domain operating systems, users, interfaces, application standards and networking standards. An open systems model for communications is required such as TCP/IP (the transmission control protocol/ internet protocol).

Intranets arose because companies realized that intranets offered several benefits over traditional information systems. A principal use of intranets has been to create on-line repositories of information that can be updated easily as often as required. Product catalogues, employee handbooks, telephone directories or benefits information can be revised immediately as changes occur. This “event driven” publishing allows organizations to respond more rapidly to changing conditions than traditional paper-based publishing which requires a rigid production schedule. Made available via intranet, documents always can be up-to-date, eliminating paper, printing and distribution costs.

Intranet is easy to implement and to use. Establishing an intranet is relatively straightforward technologically speaking. Most people are relatively familiar with using the concept of a web browser. Besides, the hardware, software and communication facilities required for constructing a corporate intranet are relatively inexpensive and the return of investment is grater than their cost as they will increase the productivity. Conservative studies of returns of investment (ROIs) from intranets show ROIs of 23% to 85% and some companies have reported ROIs of more than 1,000%. Finally, it is scalable and flexible. A corporate intranet is easily grown with changes in demand and use, and the adaptions in it are relatively easy to achieve.

Evaluate the potential for the use of internet technology within the Jeff Lloyd Leisure Centre described below: (10 marks)

With respects to our case, different types of intranet such as communications, integrating and catalogue ones could manage more business process electronically and enhance the organizational practice. The communications one will enable the leisure centre to link its site with other organizations’ sites for information sharing; for example, instead of displaying the promotional materials around the center or mailing them to their members, sales and marketing staff might use the intranet to track-manage-oversee and coordinate the activities of their sales force within the same organization or in collaboration with different ones. Latest promotions, updates in pricing, rebates, and information about customers or competitors can also be made available with the utilization of the intranet. Additionally, principal responsibilities of human resources department include keeping employees informed of company issues and providing information about employees’ records and benefits. Human resource can use intranets for online publishing of corporate manuals, job postings and internal job transfers, company telephone directories and training classes.

The integrating intranet will provide the chance to the leisure centre to shift from the status of having a large number of information systems/ communication systems with different interfaces to a common (browser) interface. The firm will have for example booking system, staffing system, finance and accounting system, sales and marketing system, and human resource system through the integrating intranet. This will result in increasing the access. Additionally, the catalogue intranet is useful for the company if wishing to provide access to large catalogue of service information. In this case, database required to efficiently handle large volumes of data. ...

This is a preview of the whole essay

Finally, the extranet would be beneficial for the company too. Extranet is an intranet that is also accessible in part to authorised people from outside the organisation. Various levels of accessibility provided via username/password mechanisms. The particular organisational network would be beneficial for good partnerships between businesses. For instance, if the leisure centre comprise a bar, then they could use the extranet for their suppliers for the proper equipment and for financial transactions too.

The catalogue intranet would enable the leisure center to present their services to their members.

These different types of Intranet could provide rich set of tools for creating collaborative environments in which the members of the organization can communicate-coordinate and enhance group decision-making better by exchanging ideas, sharing information and working together on common projects and assignments regardless of their physical location. These tools include email, fax, voice mail, teleconferencing, videoconferencing, data conferencing, groupware, chat systems, newsgroups and team ware. Managers can view employee resumes, business plans, and corporate regulations and procedures; they can retrieve sales data, review any desired document and call a meeting.

Discuss the differences between static and dynamic web pages. (8 marks)

The main difference between static and dynamic web pages is that the content in the static web pages doesn’t change unless the HTML file which defines the page it is changed. Whereas, in the dynamic web pages the content is generated each time the page is accessed. Responds to user input from the browser for example by returning data from a database query. Additionally, dynamic pages can be customised.

Finally, there are numerous ways of integrating web pages with databases. These are the following: Active Server Pages/ActiveX Data Access Objects

ASP allows dynamic, interactive web pages to be created on a web server running IIS (or PWS )

In this section we briefly mention some of the facilities available to integrate databases into the web environment. Microsoft Access provides 3 wizards for automatically generating HTML pages based on tables, queries, forms, or reports in the database:

Static pages: With this method, the user can export data to HTML format. This is the a basic facility with the obvious drawback that the HTML page can quickly become out of date and needs to be regenerated every time the base table change to remain current. The page uses standard HTML and can be used to with any browser. The user has some control over the appearance of the web page through the use of the HTML templates, files that consist of HTML commands describing the page’s layout. The templates can be used to insert company logos, graphics, and other elements.
Dynamic pages using Active Server Pages. With this approach the user can export data to an ”asp” file on the Web server that will store the ASP file.
Dynamic pages, using data access pages. Data access pages are web pages bound directly to the data in the database. Data access pages can be used like Access forms, except that these pages are stored as external files, rather than within the database or database project. Although the pages can be used within Access, they are primarily designed to be viewed by a web browser. Data access pages are written in dynamic HTML, an extension of HTML that allows dynamic objects as part of the web page. Unlike ASP files, a data access page is created within Access using a wizard or in Design view employing many of the same tools that are used to create Access forms.

Question 4

Give a brief description of your understanding of the term On-Line Analytic Processing.

On-Line Analytic Processing is the dynamic synthesis, analysis, and consolidation of large volumes of multi-dimensional data, Codd (1993). OLAP is a term that describes a technology that uses a multi-dimensional view of aggregate data to provide quick access to strategic information for purposes of advanced analysis. (Codd et al, 1995)

Furthermore, it enables users to gain a deeper understanding and knowledge about various aspects of their corporate data through fast, consistent, interactive access to a wide variety of possible views of the data. OLAP allows the user to view corporate data in such a way that it is a better model of the true dimensionality of the enterprise. While OLAP systems can easily answer “who?”and”what?” questions, it is their ability to answer “what if?” and “why?” type questions that distinguishes them from general-purpose querry tools. OLAP enables decision-making about future actions. A typical OLAP calculation can be more complex than simply aggregating data. Hence, the types of analysis available from OLAP range from basic navigation and browsing (slicing and dicing) to calculations, to more complex analyses such as time series and complex modeling.

OLAP is a method of representing multi dimensional data. In a relational database the data is held in a flat file, an OLAP cube can represent this data in a multi dimensional format

The following table gives a list of the revenue for 2001 for a property management company which has offices in different parts of the UK. The data is the result of a 3 field query in a relational database. How could this data be represented in a multidimensional database?

The ability to represent multi-dimensional views of corporate data is core requirement of building a “realistic” business model. A multi dimensional view of data provides the basis for analytical processing through flexible access to corporate data. Furthermore, the underlying database design that provides the multi-dimensional view of data should treat all dimensions equally. In other words, the database design should:

not influence the types of operations that are allowable on a given dimension or the rate at which these operations are performed;
Enables users to analyse data across any dimension at any level of aggregation with equal functionality and ease.
Support all multi-dimensional views of data in the most intuitive way possible.

OLAP systems should as much as possible hide users from the syntax of complex queries and provide consistent response times for all queries no matter how complex.

In our case the revenue data can fit into a three-field relational table, however, this data fits much more naturally into a two-dimensional matrix, with the dimensions being city and time (quarters). What differentiates the requirements for these representations are the queries that the end-user may ask. If the user simply poses queries like “what is the total annual revenue for each city?” then this involves retrieving multiple values and aggregating them.

Suppose that a further field Property Type is included as in the table below:

How could this data be represented in a multi dimensional format?

Consider the revenue data with an additional dimension namely property type. In this case, the data represents the total revenue generated by the sale of each type of property, by city, and by time (quarters). Again, this data can fit into a four-filed table. However, the data fits more naturally into a three dimensional cube. The cube represents data as cells in an array by associating the total revenue with the dimensions property type, city and time.

OLAP database servers use multi-dimensional structures to store data and relationships between data. Multi-dimensional structures are best visualised as cubes of data and cubes within cubes of data. Each side of a cube is a dimension.

Multi-dimensional are a compact and easy-to-understand way of visualising and manipulating data elements than have many inter-relationships.

The term slicing and dicing, or pivoting, refers to the ability to look at data from different viewpoints. Give an application of this in terms of the above data.

(New version: Discuss how could be represented in a multi dimension format?)

For example, one slice of the revenue data may display all revenue generated per type of property within cities. Another slice may display all revenue generated by branch office within each city. Slicing and dicing is often performed along a time axis in order to analyse trends and find patterns. ???????????

Explain the relationship of the OLAP cube and the structure of equivalent star schema.

Question 2

Bill Inmon, has defined a data warehouse as:

“A subject oriented, integrated time variant and non-volatile collection of data in support of management’s decision making process.”

(Case study for an insurance company) decide to build a data warehouse.

Discuss the various aspects of this definition in relation to the data held within insurance company.

Nowadays, organisations are focusing on ways to use operational data to support decision –making, as a means of regaining competitive advantage. Organisations need to turn their archives of data into a source of knowledge, so that a single integrated / consolidated view of the organisation’s data is presented to the user. A data warehouse was deemed the solution to meet the requirements of a system capable of supporting decision-making, receiving data from multiple operational data sources.

In this definition by Inmon (1993), the data is:

Subject Oriented

Warehouse is organized around major subjects of the enterprise (e.g. customers, products, sales) rather than major application areas (e.g. customer invoicing, stock control, product sales). This is reflected in the need to store decision-support data rather than application-oriented data.

Integrated

The data warehouse integrates corporate application-oriented data from different source systems, which often includes data that is inconsistent. The integrated data source must be made consistent to present a unified view of the data to the users.

Time Variant

Data in the warehouse is only accurate and valid at some point in time or over some time interval. Time-variance is also shown in the extended time that data is held, the implicit or explicit association of time with all data, and the fact that the data represents a series of snapshots.

Non Volatile

Data in the warehouse is not updated in real-time but is refreshed from operational systems on a regular basis. New data is always added as a supplement to the database, rather than a replacement. The database continually absorbs this new data, incrementally integrating it with the previous data.

The ultimate goal of data warehousing is to integrate enterprise-wide corporate data into a single repository from which users can easily run queries, produce reports, and perform analysis. In summary, data warehouse is data management and data analysis technology.

The role of the load manager

Load Manager

The load manager (also called the fronted component) performs all the operations associated with the extraction and loading of data into the warehouse. The data may be extracted directly from the data sources or more commonly from the operational data store. The operations performed by the load manager may include simple transformation of the data for entry into the warehouse.

Size and complexity will vary between data warehouses and may be constructed using a combination of vendor data loading tools and custom-built programs.

Small table for phone calls. Describe calculations, e.g. the duration of them.

It is the job of the load manager to ensure that all data is of the same type, in order to maintain integrity within the data warehouse. It may be that different operational sources hold the same data in different formats, as in the examples above. This data needs to be standardised.

List of the total duration cost.???

Calculations and transformations

It may be necessary to transform data or carry out calculations on the data in order to store it in a particular way in the data warehouse.

For example a mobile phone company may store data about its customers and the calls they make in the following format:

However rather than having the start and end time, they may hold the data as duration of call, therefore a calculation will have to be carried out, using the start time and end time. i.e. (end time – start time=duration).

The data may also be summarised in different ways, for example all calls made on a certain day may be summarised into a daily summary, or all calls made by a particular caller ID. In this way the data can be represented in different ways depending on the need of the decision maker.