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.

Contribute Materials

Your contribution can guide someone’s learning journey. Share your documents today.

DATA ANALYSIS AND DESIGN
1

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.

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

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.

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

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.

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

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

Paraphrase This Document

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

There are some design aspects for which techniques are utilised for improving and enhancing the
user interface design patterns.
With the effective colours, buttons, white spaces, letter spacing, hover controlling and may more.
With the implementation of these varied changes and implementing effective tools, the user
interfaces can be improved.
The enhancement in interface help in attaining effective interaction with the users. It supports in
increasing user-friendliness and readability improved with the accurate spacing (Schwartz, et. al.,
2012).
16

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.

LO3 - Task 3. Be able to use manipulation and querying tools.
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.
Query help in efficient management of database without any requirement of code. SQL stands
for a structured query language that is utilised for retrieving, manipulating and building the
databases. The classification of SQL statements is DDL (Data definition language) and DML
(Data manipulation language).
Data Definition Language (DDL): These are the SQL statements that modify and builds
structure of schema or tables in database.
Like:
Create Command
CREATE TABLE campus
(
Program_id INTEGER PRIMARY KEY,
Campus_name TEXT,
Program TEXT
);
Alter Command
It is used for making the alterations. Here in the database, Program_id is made Foreign key for
the table units through taking reference to campus table.
ALTER TABLE units
ADD FOREIGN KEY (Program_id)
REFERENCES campus (Program_id)
Drop Command
Drop table units are utilised for deleting the structure of table.
Data Manipulation Language (DDL): With intention to manage the information within the
schema of the database, the DDL is utilised.
17

Select Command
It is used for data retrieval from the database.
Example:
SELECT * from units;
Insert Command
This command is used for inserting values into the database.
INSERT INTO campus (‘Program_id’, ‘Campus_name’, ‘Program’) Values (1, ‘North’,
‘Computing Programs’)
18

A.C. 3.2 Implement a query language into the relational database system.
The query language is implemented for the database management. The MySQL query language
has been used for this database of Dominican college.
DDL command is utilised for the database creation and table formation.
Database: dominican_college_database
CREATE DATABASE dominican_college_database
Table: units
CREATE TABLE units
(
Unit_name VARCHAR (250) PRIMARY KEY,
Year INTEGER,
Program_id
);
DML command is used for making insertions and performing other operations.
INSERT INTO units
(‘Unit_name’, ‘Year’, ‘Program_id’) VALUES (‘Computer Fundamentals’, 1, 1)
19

Paraphrase This Document

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

A.C. 3.3 Critically evaluate how meaningful data has been extracted through the use of
query tools for the given case study.
The query tools help in creation and extraction of information. The query language used for this
assignment is MySQL. The relational database effectively manages and stores the values.
It is majorly utilised for managing RDBMS. The high interactivity and easy to understanding
queries help in enhancing user interface. It ensures the data consistency and provides effective
storage to data of the Dominican college.
The query tools provide efficient extraction and retrieval of data. The DDL and DML query
utilised help in performing several operations (Schwartz, et. al., 2012).
20

LO4 -Task 4. Be able to test and document relational database systems.
A.C. 4.1 Critically review and test the implemented relational database system.
The testing of RDBMS is done in order to verify that the consistency of data. It is required to test
and perform validation that right input is taken in order to generate the desired outcome. The
attribute type should be taken correctly and the error generates when the type is wrong.
The relational database system is reviewed effectively and test plans are made which are
implemented in order to validate the database system.
The error arises when length is provided to the TEXT type variable. The TEXT type column
cannot be made as primary key. Therefore, we have changed its type to Varchar (250), the length
is provided along with the type.
The requirements of performing testing are provided below:
 As several numbers of students and people are associated with this database.
 The security is provided with intention to prevent unauthorized access to data.
 It is checked and verified for all default values.
 The proper storage and data consistency are checked.
 The synchronization in backend and frontend operation is checked through performing
testing operations.
21

A.C. 4.2 Create documentation to support the implementation and testing of the relational
database system.
With intention to support the testing of the RDBMS, the documentation is created. This database
of Dominican college constitutes of different tables,
Table: Campus (Program_id (PK), Campus_name, Program)
Table: Units (Unit_name (PK), Year, Program_id (FK))
The relationship between the tables is one to many with which the campus table is linked to the
unit table. The Program_id is the primary key for the campus table and foreign key for the units
table.
22

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.

A.C. 4.3 Create user documentation for the developed relational database system.
The document is made for the developed Dominican College database that includes the different
tables and attributes. The course information and details of programs are presented in the
database. The query help in selecting, inserting and deleting the records (Krishnamurthy, et. al.,
2014).
The database is created having name dominican_college_database.
Further, tables are created,
Tables:
Campus (Program_id (PK), Campus_name, Program)
Units (Unit_name (PK), Year, Program_id (FK))
The operations like insertion are performed with intention to feed the course details that could be
modified anytime with the help of modifying, alter and other commands.
In the above relational database system, there are significant points which should be noted:
 Units table has Unit_name as the primary key.
 Campus table has the program_id as the primary key.
 The program_id is the foreign key for the units table.
 There is a relation exists between the tables which is one to many between the campus
table to unit table (Kim, et. al., 2012).
23

A.C. 4.4 Explain how verification and validation have been addressed in the developed
relational database system.
There is difference between validation and verification which are presented below:
Verification Validation
1. The delivery of all functionality and
ensuring the design of project is
performed with verification.
2. It is performed in the starting while
initiating the development cycle.
3. There are four parts of verification that
are reviewing, meeting, inspection and
walkthrough (Krishnamurthy, et. al.,
2014).
4. It is performed in order to check that
whether it is fulfilling specified design
needs of the project.
5. Cost involved is less.
6. It checks the specification of
requirements.
1. The target of college and the fulfilment
of the requirements of users are assured
with the process of validation.
2. It is performed at the end of cycle and
performed after verification.
3. The functional, as well as non-
functional techniques, are associated
with this method.
4. The actual utilisation of the project
should be fulfilled and it is performed
to ensure that users and college
requirements are fulfilling with the
project or not.
5. High requirement of budget when the
error generated in the project.
6. It ensures the consistency and accuracy
of the final product or system.
24

A.C. 4.5 Explain how control mechanisms have been used in the developed relational
database system.
There are methods which are utilised for controlling several variables in the organisation.
Control mechanisms support in ensuring proper functioning and influence the basic functions of
the College.
Planning: It has been planned in the database that how the strategies are applied with intention to
provide accurate details to the users.
Leading: The leading is required as the college admin support in listing the Course details as well
as Unit details.
Organizing: Productivity and profitability is the major target of companies, and this college
needs to amplify its reputation through effective organising of data.
Controlling: Control measures are taken that each program should have units. There should not
be similarity in the units of different year (Chickerur, et. al., 2015).
25

Paraphrase This Document

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

Conclusion
It has been concluded with this assignment that the database help in effective retrieval and
storage of huge amount of information. The SQL queries had been utilised for storing and
managing the data. The Dominican College Database has been provided and presented in
relational form constitutes of rows and columns.
26

References
 Chickerur, S., Goudar, A., & Kinnerkar, A. (2015). Comparison of relational database
with document-oriented database (mongodb) for big data applications. In 2015 8th
International Conference on Advanced Software Engineering & Its Applications
(ASEA) (pp. 41-47). IEEE.
 Coronel, C., & Morris, S. (2016). Database systems: design, implementation, &
management. Cengage Learning.
 DuBois, P. (2014). MySQL Cookbook: Solutions for Database Developers and
Administrators. " O'Reilly Media, Inc.".
 Elmasri, R., & Navathe, S. (2016). Fundamentals of database systems. London: Pearson.
 Kim, W., Reiner, D. S., & Batory, D. (Eds.). (2012). Query processing in database
systems. Springer Science & Business Media.
 Krishnamurthy, S., Thombre, N., Conway, N., Li, W. H., & Hoyer, M. (2014). U.S.
Patent No. 8,812,487. Washington, DC: U.S. Patent and Trademark Office.
 Lawrence, R. (2014). Integration and virtualization of relational SQL and NoSQL
systems including MySQL and MongoDB. In Computational Science and Computational
Intelligence (CSCI), 2014 International Conference on (Vol. 1, pp. 285-290). IEEE.
 Schwartz, B., Zaitsev, P., & Tkachenko, V. (2012). High performance MySQL:
optimization, backups, and replication. " O'Reilly Media, Inc.".
27