The Wine Cellar Database 5 Data Warehouse Task 15 Limitations of Relational Databases for The Wine Cellar Scenario 5 Data Warehouse and Their Support in Decision-Making 7 Issues in Designing and Imple

ADVANCED DATABASE CONCEPT ASSIGNMENT
MODULE NAME: ADVANCED DATABASE CONCEPT
Word Count: 5058
1 of 21

Table of Contents
Explanations and Recommendations for The Wine Cellar Database.....................4
Task 1............................................................................................................................5
Limitations of Relational Databases for The Wine Cellar Scenario...................5
Data Warehouse and Their Support in Decision-Making..........................................7
Issues in Designing and Implementing a Data Warehouse........................................8
Data Structure Model for The Wine Cellar Scenario.................................................9
Task 2..........................................................................................................................11
Distributed Databases and Potential Issues Before Final Design Decisions.....11
Relevance of Distributed Database for The Wine Cellar Organization and
Costs/Benefits...........................................................................................................13
Recommendation of Type of Distributed Database and Justifications....................14
Task 3..........................................................................................................................14
How Relational Databases Support Object Oriented Development?................14
Relational Databases Relevance for Multimedia Requirements in The Wine Cellar
Scenario....................................................................................................................15
Conclusion....................................................................................................................16
References....................................................................................................................18
2 of 21

INTRODUCTION
Data is the oil that runs the machinery of the present-day society. Data is everywhere,
and new is being generated every moment from many sources. Being able to store and
retrieve data in an efficient way is an essential requirement for the success of any
organisation. Also, different databases are optimised for different roles. Some may be
optimised for read-heavy operations, while others may be optimised for write-heavy
operations. Optimising one parameter entails a drawback in other parameters. Also,
the underlying structure of a database system has a significant bearing on the
applications. This paper deals with the new requirements of a (fictitious) company in
England, which is engaged in sourcing wine and accessories from all over the world
and selling them. The company has experienced growth in the recent years and has
opened branches throughout the country. However, the database handling is still
keeping in mind a single location. The consequence has been the delay and manual
effort required in compiling company-wide reports. In addition to the reporting
requirement, the company also wants to promote selected products via short videos on
a website. This paper details the various database technologies and provides
recommendations for the new scenarios of reporting and multimedia promotions
which have cropped up for The Wine Cellar.
3 of 21

Explanations and Recommendations for The Wine Cellar Database
Data is the new oil (Toonders 2014), and this implies a likewise importance to the
means for managing data. Data needs to be stored, edited, deleted, copied and
retrieved to be of relevance to the success of a business or an organisation. All of
these operations to the data need to be done in a performant way. If data is not
handled efficiently, then instead of becoming a competitive advantage and blooming
to its full potential, data becomes additional work without the benefits (Jones 2016).
The means of managing the data (including the data itself, paradigms, schemas, and
the software used) come under the term of databases (Gupta 2011, p.5). Databases are
thus a solution to our world's requirement for storing significant amounts of data and
retrieving it quickly and accurately (Ward and Dafoulas 2006, p.2). Databases are of
many types and have been evolving since their inception in the 1970s (Ward and
Dafoulas 2006, p.2), and many variants have been developed, each with its
advantages and shortcomings. Some of the types of databases are flat-file, relational,
distributed, NoSQL, and others (Data Warehouses 2012). It is not the case that one
type of a database management system (DBMS) is intrinsically better than another,
but each is suited to some particular scenarios. Since efficiency is an essential
requirement for the database, the scenario at hand needs to be considered before
selecting a particular database.
This paper is about the database requirements of a (fictitious) company that operates
in England. This company, The Wine Cellar is engaged in selling a selection of fine
wine, spirits and accessories. The company arranges wines and accessories to sell
from sellers all over the world. The company has experienced growth in the last few
years with the resulting expansion. The company now operates from many branches
within England. Each branch maintains its private database and submits a summary
file each week to the head office at the end of every week. This submission from silo-
like databases at each outlet does fulfil the requirements for the data at the head
office, but the turnaround time is very high. Currently, the individual files are
analysed individually, and the results are amalgamated. Also, this process is labour-
intensive, both at the branch and the head-office level. Given the growth and
4 of 21

expansion of The Wine Cellar and the future potential, timely and accurate data is
critical. The management will be better equipped to make company-wide strategic
decisions if the company-wide information is easily available at short notice. This
agility in data querying is something that the current solution cannot provide. Also,
the company would like to promote selected products by showcasing short videos via
a website. These two requirements will be attended to in the paper, in addition to
providing a theoretical knowhow of the database technologies relevant to the
discussion.
The Information Technology (IT) team of The Wine Cellar is skilled in relational
database design, and this skill-set served the company well until recently. However,
keeping in mind the recent growth and the future possibilities, the IT team will have
to expand their skill set to include some new technologies, or some new hires with the
relevant skills will have to be made. The company is more interested in training and
increasing the skills of the current team. This paper will look into these new
technologies and will suggest ways in which the existing experience of the team can
be utilised while developing new ones.
Task 1
Limitations of Relational Databases for The Wine Cellar Scenario
In the relational database paradigm, data is represented in the form of tables, and
relations among the tables help enforce data consistency (Rouse 2006). In the process
of developing relational database schema for a scenario, the entities to be represented
in the database are identified first. Then, the parameters of these entities are
recognised and finally the relations among them, so that there is no duplication of data
and any information about any entity is linked and can be traced. Such a paradigm
allows for naturally enforcing constraints. This system was proposed in the 70s by
E.F. Codd (Codd 1982, pp.109-117). Relational Database is implemented via tables,
columns, rows, primary keys, and foreign keys. In other words, tables represent
entities (e.g. products, customers), columns represent parameters about the entities
(e.g. product name, manufacturing date, volume). The rows represent one particular
5 of 21

instance of the entity (e.g. all the details of a single person), a primary key uniquely
identifies each row in a table (e.g. social security number), and foreign keys map one
row to another to implement relations.
The key point to note from the preceding discussion is the foundation of the paradigm
is relations, and indeed the relational database management system (RDBMS) are
based on mathematical theories (Duffy 2011). Thus these systems are good at
enforcing data consistency, both at the individual column level and in between tables.
However, such systems are traditionally centralised, and this has both advantages and
disadvantages. Till a few years, the centralised system was serving The Wine Cellar
well, but the geographical distribution of the sources of data to different outlets call
for another solution. By centralisation, we mean that the entire database system - the
software to manage the data, the computer hardware, and the data are located on a
single machine in a single location. This type of a concentrated architecture has some
advantages and disadvantages. As we will see in a moment, the centralised systems
serve the outlets of The Wine Cellar well but fail when it comes to company-wide
manoeuvring.
The current scenario of the company is that the outlets manage their data in a
centralised relational database system e.g. inventory, sales, tax, customer details and
other data. This centralisation serves them well as a centralised system has the
advantages of being faster, cheaper, and being easier to manage. However, this makes
interaction, specifically programmatic interaction with the head office a difficult
thing. The senior staff at the head office would be interested in knowing the up-to-
minute performance of their various branches and make strategic and tactical
decisions accordingly. However, as confirmed in the briefing for this paper, the
outlets submit a file each week which has to be manually analysed and results
compiled for the perusal of the senior staff. This process is cumbersome, slow and
prone to errors. In a nutshell, the centralisation of traditional relational database
systems and the consequent inability to allow access to multiple (physically separated)
parties is a limitation in the context of The Wine Cellar's growth. This situation is a
6 of 21