Data Analysis and Design: Implementing a Relational Database System for Dominican College

DATA ANALYSIS AND DESIGN
1

Table of Contents
Introduction......................................................................................................................................3
LO1 - Task 1 Understand data models and database technologies.................................................4
A.C.1.1 Critically compare different data models.......................................................................4
A.C.1.2 Critically discuss the benefits and limitations of different database technologies.........5
A.C.1.3 Analyse different approaches to database design...........................................................9
Bottom To Up Database Approach:............................................................................................9
Inside To Out Database Design Approach:.................................................................................9
Mixed Strategy Database Design Approach:...............................................................................9
LO2 Task 2. Be able to design and implement relational database systems.................................10
A.C.2.1 Design a relational database system to meet given requirements provided in the given
case study...................................................................................................................................10
A.C.2.2- Build a relational database system for the database design........................................11
A.C.2.3 Apply a range of database tools and techniques to enhance the user interface to meet
the given case study requirements.............................................................................................13
A.C. 3.1 Explain the benefits of using manipulation and query tools in a relational database
system as per the requirements gave in the case study...............................................................17
A.C. 3.2 Implement a query language into the relational database system...............................19
A.C. 3.3 Critically evaluate how meaningful data has been extracted through the use of query
tools for the given case study.....................................................................................................20
LO4 -Task 4. Be able to test and document relational database systems......................................21
A.C. 4.1 Critically review and test the implemented relational database system......................21
A.C. 4.2 Create documentation to support the implementation and testing of the relational
database system.........................................................................................................................22
A.C. 4.3 Create user documentation for the developed relational database system..................23
A.C. 4.4 Explain how verification and validation have been addressed in the developed
relational database system.........................................................................................................24
A.C. 4.5 Explain how control mechanisms have been used in the developed relational database
system........................................................................................................................................25
Conclusion.....................................................................................................................................26
References......................................................................................................................................27
2

Introduction
The data analysis and design concepts are well analysed in this report. The different approaches
of database design and querying tools are discussed in it. The testing and documentation are also
performed in order to verify the developed relational database management system (RDBMS).
The Hudson's Bay Company (HBC) utilizes the database design concepts with the intention to
amplify their productivity and the database for Dominican College will also make that provides
effective storage to the course information and manages the information of different programs.
3

LO1 - Task 1 Understand data models and database technologies.
A.C.1.1 Critically compare different data models.
Data model support in effective representation of the company, Hudson's Bay Company (HBC)
which is Canadian retail business organization. The retail stores are maintained by this company.
To make interaction with the organizational data, data models are utilized. It is an integrated
gathering of concepts that help in describing and manipulating the information and relationships
among them. It organizes information for the several users.
There are different types of data models, the general classification is provided below:
Hierarchical Model:
It is a tree-like structure in which one to one or one to many relationships occurs in this model.
The parent-child relationship is presented. The integrity, security, simplicity and efficiency in
managing a large number of relationships are its major contributes. However, it amplifies
complexity and lacks in independence in structure.
Network Model:
The graph-like the structure of this model and has many to many relationships which are
followed by this model, therefore it could have several parents as well as children. The ease in
accessing information, security of data and effectively handling relationships makes it suitable.
Object-based Data Model:
The object’s features are described by the property termed as attributes, distinct objects are an
entity and there is a relationship exists among the entities. There are some kinds of object-based
models which entity-relationship, object-oriented, semantic and functional. The pictorial
representation of data is the E-R model.
Relational model:
This model has one to one, one to many and many to many relationships. The relational model is
the most popular. It has high capability and has eased in designing, implementing and
maintenance. The organisation uses this model for managing their data.
4

A.C.1.2 Critically discuss the benefits and limitations of different database technologies.
There are several benefits and pitfalls of database technologies used in several organisations that
are provided below:
Relational Database
This type of database applications stores data in a tabular form, these tables have an identifier
and also support Normalization. This type of database has ACID properties like Atomicity,
Consistency, Isolation, and durability. It supports distributed database and information are
relatively stored and accessed by keys and identifiers. The Hudson's Bay Company (HBC)
utilizes this form of database (Lawrence, 2014).
Types of Relational Database are:
Microsoft SQL Server
It is Microsoft developed Structured Query Language. It’s a product with a primary function of
storing and retrieving data according to the request of the application.
Benefits
 It’s a free SQL server which is utilised by just downloading and installing setup at our
system.
 It supports scalability feature also.
 It provides a 10GB database restriction but that’s a maximum size per database.
Therefore, it can have multiple databases that stores up to 10GB of data.
Limitations
 It’s database server used maximum 1GB memory.
 The maximum size limit of each relational database is 10GB.
 The relational database engine is not working to the lesser of socket or 4 cores.
MySQL
5

It is an open-source type database which provides pensative database management including to
the software. It is a stable, reliable to use and relatively powerful.
Benefits
 It is required one HD and 512MB RAM with 64-bit multiprocessor machine includes
RAID 10 and more than 16GB RAM.
 It assured user with 24X7 of process time and provides a large range of high-availability
solutions with important cluster servers.
Limitations
 It requires 32-bit pointers to storing row locations into the data file.
 It has an address limit also which is only 4GB of space.
Oracle database
It is a database management system which provides multiple level model feature. It is created
and maintained by Oracle. Uses of this database are running online transaction processing and it
can be used in data warehousing, and mixed database workloads.
Benefits
 It provides the flexibility of choosing the operating system to run the database.
 It can be upgraded in the future without losing any data.
Limitations
 The cloud database connection automatically disconnects after 30 minutes of idle time
and it cannot be changed.
 In Oracle database access to the host file system is disabled.
Non-Relational Database
Amazon Dynamo DB
6