Reference no: EM133211067

Assignment 1: Advanced Database Concepts

Case Study Assessment: Eco-Warriors

Assignment Brief

As part of the formal assessment for the programme you are required to submit an Advanced Database Concepts assignment. Please refer to your Student Handbook for full details of the programme assessment scheme and general information on preparing and submitting assignments.

Learning Outcome 1: Demonstrate knowledge and a critical understanding of the major developments and emerging trends within the field.
Learning Outcome 2: Critically evaluate how relational databases can be used to support Multimedia and Object-Oriented Databases.
Learning Outcome 3: Apply a conceptual understanding of the issues involved in designing and implementing a data warehouse to a given scenario.
Learning Outcome 4: Accurately deploy established techniques to design, and justify, a suitable data model to meet the needs of a given scenario.
Learning Outcome 5: Identify and justify a database solution to a complex problem.

Case Study: Eco-Warriors

Protecting the environment matters. So does making money. Eco-Warriors are very good at doing both. This is how they do it...

The company works with local authorities, private businesses, members of the public and many other organizations to collect recyclable materials - not just the obvious things like clothes, paper, plastics, glass and metals but more specialized items like dead batteries (of every description, including car batteries), broken consumer electronics (old computers, TVs, DVD players etc.) and rubber (again, of every type including car and cycle tyres).

Materials can be sourced in a number of ways:

• Kerb-side collections from private residential homes working with the local authority rubbish trucks. The full trucks are taken directly to the company processing site and the selected materials removed and sorted. What is not wanted stays on the truck and goes to local authority landfill sites. The company pays each local authority a flat fee per truck, regardless of how much is taken. The private resident gets paid nothing and in fact has to pay the local authority a fee for the weekly collections.

• Contract collections from private businesses. Here the company works directly with individual third-party businesses and uses its own fleet of trucks to collect a pre-agreed range of waste materials which are then taken direct to the company processing site and the selected materials removed and sorted. Again, the client business gets paid nothing for these waste items and in fact has to pay the company a fixed monthly fee.

• Drop-ins from private individuals. The company allows the public to drop-off waste materials at their processing site at specified times each day - normally between 12.00 and 14.00 each day when the processing machinery is turned off. This is for health and safety reasons. Outside of that two-hour window, the public are not allowed on site - it is simply too dangerous. Again, no money is paid for any donated materials.

• Bulk processing contracts from government and other public sector agencies. Unlike members of the public or the average private business, some clients have enormous amounts of waste to process and recycle. Examples include the military, hospitals, universities etc. As ever, no money is paid for any donated materials and a fee is charged based on type and weight of materials collected.

• Very occasionally, another recycling company will pass on hard-to-process materials like batteries or computers because Eco-Warriors have the specialized technical processing facilities in-house. In this case, a fee is paid to the supplying company, based on type of material and weight.

So, what does the company do with all this waste material? It depends...

• Clothing and textiles only need sorting and washing before being sold to charities at a discount rate (normally 20% of the full retail value) - who then sell it on in their own domestic retail charity shops or send it overseas. The company uses industrial washing machines for this work.
• Metals need sorting, separating and often melting down into pure ingots and bars for sale to the metals industry. The company uses specialized scrap-metal processing equipment for this plus a furnace to melt the metals. This work is highly lucrative.
• Paper and cardboard is mixed with water and cleaning agents and turned into pulp before being reconstituted into clean paper and cardboard. The market for paper and packaging is huge.
• Glass is melted down in a specialized furnace and.turned into glass sheets of various dimensions. These are cut to size and sold to glass and glazing suppliers.
• Batteries - domestic and car - are technically very difficult to recycle but the company has the specialized knowledge and equipment in-house and the chemicals and components inside the batteries can be salvaged and re-used.
• Computers, TVs, DVDs and many other electronic devices contain a lot of electronic components - which contain a surprising amount of precious metals like gold and platinum. The extraction of such metals is time-consuming and technically difficult but the obvious financial advantage of acquiring such metals makes this worthwhile. This is Eco-Warriors specialism and it makes them a lot of money.
• Rubber in all its forms can be recycled quite easily by melting and refining it into rubber blocks of various sizes.

The company headquarters is in central London where the CEO and board of directors are based, along with all central business functions like IT, Legal, Accounting, HR, Sales etc. At present, the company are focused solely on London and the South East with a single very large processing centre in West London which services collections from all over London and the South East where they partner with 10 local authorities. The company have a total workforce of 200, spread across the headquarters, processing plant and collection staff/drivers.

The CEO is new and is very aggressive in his plans for rapid expansion - both across the UK but later perhaps even abroad - particularly in developing countries which lack the technical knowledge to do this work. The 5-year plan is to scale the currently privately-owned company up massively and then go public with a share listing on the London Stock Exchange.

This will mean external investors - particularly the large institutional investors - taking a very close interest in the operational and financial performance of the company and to satisfy these potential investors, the CEO and the board realize that they need serious help on capturing, storing, processing, analysing and presenting their data. They realize future success will be built on high-quality data.

The CEO and the board are also concerned about the following long-term issues:

• Satisfying the various governmental (UK, EU and otherwise) environmental and waste-processing agencies that they are fully compliant with all applicable regulations and directives. As they scale in size, their operations will be scrutinized further and again, this will mean ever more quality data will be needed. This may well mean integrating and standardizing their data formats with external agencies.

• Staying on top of IT developments and retaining appropriate high-level IT skills. Being based in London means that there is huge competition from other companies for the best IT talent. They need to future-proof their IT systems in case any key people leave.

• How to build a robust IT infrastructure that not only satisfies their own internal needs and that of external investors and government agencies, but will also grow and scale well as the company opens up new centres across the UK and in other countries. Distributed data will be a major feature.

• How to supplement their own internal data with external data sources so as to give the CEO and board a full 360-degree view of their business and the wider environment. This external data must be incorporated with their own data to give a full picture.

Task 1

Imagine that you are a data warehousing consultant. You have been asked to make a presentation to the chief executive and board of directors of the above company - who are not computing specialists - on the following technical issues, as they are seriously considering developing a data warehouse:

(a) Discuss the difference between an OLTP (on-line transaction processing) database and a data warehouse. The organization already has a substantial OLTP solution and the CEO needs convincing of what benefits investing in a data warehouse will bring to this organization.

(b) Explain, with the aid of a suitable diagram, the architecture of a data warehouse and how the various sources of data - internal and external to the organization
- that can be used to populate a data warehouse. The CEO is particularly concerned that existing day-to-day operations are unaffected by any new development and has heard the phrase ‘ETL' - he does not understand this process. Explain this to the CEO and board.

(c) Several members of the board, such as the Managing Director, Finance Director and Sales & Marketing Director have their own specific needs in terms of seeing the bigger picture and planning for the future. Explain how ‘data marts' may be a useful solution to solve this organization's strategic needs.

(d) The CEO and board have asked the IT Director to attend your presentation, who is an expert on OLTP database systems but knows little of data warehousing. Explain to the IT Director how the role of normalization differs between an OLTP database and a data warehouse before moving on to explain, with the aid of a suitable diagram, how ‘fact' tables and ‘dimension' tables can be used to develop ‘star' and ‘snowflake' schemas.

Task 2

The chief executive and board of directors of the above company now have an understanding of what a data warehouse is and what advantages it can bring them. However, your next task is to explain to them exactly how all that data held in their new data warehouse can be manipulated, analysed and presented so as to bring maximum business intelligence and thus competitive advantage. This means introducing two new techniques to your client...

(a) Explain the key concepts and architecture underpinning OLAP and the advantages and disadvantages of OLAP as compared with OLTP systems. What specific benefits will your client gain?

(b) With the aid of a suitably annotated diagram, explain what a ‘data cube' is and describe the six functions that may be applied to such a data cube in order to discover new business intelligence. How will these functions be used by your client?

(c) Data Mining is another BI technique. Explain how it differs from OLAP. Carefully explain the statistical approaches and models used in data mining, including how they are validated, and the specific steps the client's IT department must take to implement a data mining project.

Task 3

The CEO and IT Director of your client company are keen to ‘future-proof' their substantial investment in a new database system and to that end wish to incorporate the non-relational features of object-orientation and multi-media into their data platforms. As their database expert, they are looking to you for guidance.

(a) What is an ‘object' and what are the key features and benefits of using objects in a relational database? What differentiates an O/RDBMS from an OODBMS?

(b) Using appropriate annotated diagrams (such as UML class diagrams), develop a set of objects for this particular client.

(c) What does the term ‘multi-media' mean, what is a multimedia database management system (MMDBMS) and what are the components of a MMDBMS?

(d) Discuss some possible applications and types of multi-media for this client. As explained in the final task, the company are keen to expand across the globe and are thus particularly interested in geographical multi-media data. Explain what a GIS is.

Task 4
One of the key strategic goals of the board is corporate expansion. This will necessitate the opening of new regional offices, probably across several different countries. A major concern of the CEO is that the underlying IT infrastructure may not be able to support this ambitious business plan. You have been asked to brief the CEO and board on the opportunities and practicalities of implementing a distributed database system (DDBMS).

(a) With the aid of a suitably annotated diagram, explain the fundamental concepts, principles and architecture of a DDBMS.

(b) Describe and illustrate how the following DDBMS implementation techniques may be employed with this client:

• Replication (Active & Passive)
• Fragmentation & Partitioning
• Transaction Management and Two-Phase Commits