Data Analysis and Design of Relational Database for Dominican College

Verified

Added on 2024/07/02

AI Summary

This report provides a comprehensive analysis and design of a relational database system, focusing on its application for Dominican College. It begins by comparing different data models and discussing the benefits and limitations of various database technologies, including relational and non-relational databases such as MySQL, SQL Server, MongoDB, and Neo4j. The report then explores different approaches to database design, such as top-down, bottom-up, inside-out, and mixed strategies. A relational database system is designed and implemented using phpMyAdmin, with tables for campus and units. The report also delves into enhancing the user interface with database tools and techniques, explaining the benefits of manipulation and query tools, and implementing a query language for data extraction. Finally, the report critically reviews and tests the implemented system, creates supporting documentation, and explains verification, validation, and control mechanisms. This detailed analysis aims to provide an effective storage and management solution for Dominican College's course information, enhancing productivity and data accessibility. Desklib provides similar solved assignments for students.

DATA ANALYSIS AND DESIGN
1

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

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

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

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

It’s a NoSQL fully managed a proprietary database that supports key value and document data
structure. It is developed by amazon.com.
Benefits
 It is useful for applications that need high production at any scale.
 It’s a fully managed, multi-region database and provides the many-master feature into the
database.
Limitations
 In this type of database, the limit of local secondary indexes is 5 and limit for global
secondary indexes per table are also 5.
 The length of the partition key is laying from 1 byte to 2048 bytes.
Mongo DB
It is a free open-source cross-platform and provides a feature of the document-oriented into a
database program. And it uses JSON as documents with their schemata (Elmasri & Navathe,
2016).
Benefits
 The data objects are stored inside a collection as separate documents instead storing data
into table form as a relational database.
 The most attractive advantage of Mongo DB is it store data in form of BSON, JSON,
Ruby hashes etc and it is capable of holding arrays and other documents.
Limitations
 The maximum BSON document size is 16GB.
 Database name in Mongo DB cannot differ by the case of characters that depicts that
database name has case sensitivity.
Neo4j
7

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

It is a type of database management system which is used for the graphical representation of the
database, it was developed by Neo4j, Inc. It shows how each individual entity connects with or is
related to others.
Benefits
 Rock-Solid Reliability for Mission-Critical related Production Applications means it’s the only
graph database which is identified with key analysts.
 Index-free adjacency makes read time shorten and even gets better according to the
complexity of data grows (Coronel & Morris, 2016).
Limitations
 Lack of high-performance concurrency.
 The lack of a well stabled and standard declarative language.
8

A.C.1.3 Analyse different approaches to database design
There are several Database Design Approaches which are presented below. The Hudson's Bay
Company (HBC) utilizes these approaches for the effectiveness of database and efficient
management of information (DuBois, 2014).
Top To Down Database Approach:
This process starts with the general concept and moves to the core concept. Example of the top to
down process is the entity-relationship model. It is applied to recognize lower level entities,
relationship, and connected attributes. In this process first, the starting point is a general concept
what is required for the system and then move on detail specification in a down way, how the
system will interact with them.
Bottom To Up Database Approach:
It began at the basic level of attributes which is the properties of the entities and relationships. In
this approach, the analyst will work backwards to sort out what data should be stored in the
database. Opposite of top to down approach it first recognizes the items and then adds them
together in the data sets (Elmasri & Navathe, 2016).
Inside To Out Database Design Approach:
It is also known as a centralized design process. It’s a type of special case of a bottom to up
process, but different thing is that its attention is going on a central group of core logic that is
very obvious and then spreading outside by focusing others in the vicinage of relative ones. This
type of process starts with the recognization of a group of major entities and then considers other
entities, relationships and adding with those first recognize.
Mixed Strategy Database Design Approach:
Another name of this approach is the decentralized design approach. It’s a mix-up of two
approaches, bottom to up database process, and top to down database process. Accordingly the
top to down approach, the requirements are partitioned. And similarly, from a bottom to up
approach, it defines the schema of partition. It is the best approach for use if having a wide
9

number of entities and typical relations upon which related typical operations are accomplished
(Lawrence, 2014).
LO2 Task 2. Be able to design and implement relational database systems
A.C.2.1 Design a relational database system to meet given requirements provided in the
given case study.
The relational DBMS is designed using phpMyAdmin. The dominican_college_database is
created along with the two tables.
Tables are campus and units.
Campus table depicts the program names and the name of campus with the program_id as the
primary key of the table.
campus (Program_id, Campus_name, Program)
units (Unit_name, Year, Program_id (FK))
Figure 1: Relation schema of Dominican College Database
10

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

A.C.2.2- Build a relational database system for the database design.
MySQL is the relational database technologies utilised for designing this database of Dominican
College.
Table name: Campus
Program_id (PK) Campus_name Program
1 North Computing Programs
2 South Accounting Programs
3 West Management Programs
Table name: Units
Unit_name Year Program_id (FK)
Computer Fundamentals 1 1
Computer architecture with
maths
1 1
introduction to database
design and development
1 1
introduction to programming 1 1
network fundamentals 1 1
web foundation 1 1 1
Business information systems
security
2 1
comp tutorial level 5 2 1
computer operating systems
and intermediate network
2 1
database principles 2 1
user interface design 2 1
introduction to software
engineering
3 1
advanced Networks 3 1
Human-Computer Interaction
Design
3 1
advance software engineering 3 1
data warehousing and mining 3 1
Professional Project 3 1
Financial Reporting 1 2
Management Accounting 1 2
financial decision making 1 2
quantitative methods for
accounting and finance
1 2
auditing and Professional 1 2
11

accounting practice
Financial Statement Analysis 2 2
Business Strategy 2 2
Professional Accounting
Practice
2 2
Financial Reporting 2 2
intermediate Management
Accounting
2 2
Business Law 2 3 2
Contemporary Issues in
Financial Reporting
3 2
Accountability and Auditing 3 2
Corporate Financial
Communication
3 2
Principles of Taxation 3 2
strategic Management 1 3
Global working 1 3
human resource management 1 3
theory and practice of
leadership
1 3
strategic aspects of
organizational performance
1 3
business ethics and employee
relations
1 3
international business 2 3
international business strategy 2 3
law in a management context 2 3
operational management 2 3
investment analysis 2 3
financial statement analysis 2 3
Principles of organizational
behaviour
3 3
international business
environment
3 3
financial marketing 3 3
marketing principles 3 3
marketing in operational
management
3 3
Internet marketing strategies 3 3
12