Data Analysis Design for Dominican College: A Relational Database Management System

This assessment focuses on designing and developing a relational database management system (RDBMS) for Dominican College, a higher education institution located in northern New York City. The college seeks to automate its operations, including student course management, lecturer allocation, and record keeping, transitioning from a paper-based system to a more efficient and accessible digital platform. The project involves comparing different data models, designing a relational database schema, implementing SQL queries for data manipulation, and conducting thorough testing and validation to ensure the system meets the college's requirements. The assessment also includes documentation for implementation and maintenance, user guides, and a discussion of control mechanisms for data security and access.

Data Analysis Design

Table of Contents
Introduction.................................................................................................................................................3
Task1...........................................................................................................................................................4
1.1Comparison of Data model.....................................................................................................................4
1.2Benefits and Limit of database technologies..........................................................................................5
1.3Approach to design database.................................................................................................................6
Task2...........................................................................................................................................................8
2.1Design of relational DBMS against requirement.....................................................................................8
2.2Development of RDBMS.......................................................................................................................10
2.3Database tools to increase user interface............................................................................................11
Task3.........................................................................................................................................................12
3.1 Benefits of manipulation and query tool.........................................................................................12
3.2 Implementation of query language.................................................................................................13
3.3 Evaluation of how meaning full data has been extracted................................................................15
Task 4........................................................................................................................................................17
4.1 Review and testing of RDBMS..........................................................................................................17
4.2 Document to support implementation and maintenance...............................................................18
4.3 User Document................................................................................................................................24
4.4 Process of addressing verification and validation............................................................................31
4.5 Control mechanisms........................................................................................................................32
Conclusion.................................................................................................................................................34
References.................................................................................................................................................35

Introduction
The database management system is highly needed information management system that can automate
entire workability of any organization. This type of system is a highly advanced system which consist
sensitive or highly confidential information system. There are different type of information system
management available but implementation of system is totally depends upon requirement of the
system. As per this assessment the Dominican College requires a high level automated system to
manage their all three campuses and all students of first year, second year and third year including the
course offered by college and detail of lecture and lecturer. This college is located at the northern New
York City that offers numerous types of Bachelor programs in accounting, Computer Information System,
management related program is offered by the college. The database management system is most
important and economical way to efficiently process any organizational work. So it of course required
high level of development platform, tool and tricks along with high budget and time. Since the
Dominican college process their managerial and operational work using paper based. They
operate their operations using paper based technology that require great time, money and
effort investment. So they asked to design and develop a relational database management
system to automate their lectures, student course detail and management of record. The
objective of Dominican College is to manage the operation of data/information management
efficiently and extended the accessibility of college.

Task1
1.1Comparison of Data model
Data model in software development is an abstract model the enable to select a correct database
management tool, correct data and platform to develop a desired database application. It specially
handled storage and processing of databases. The data model is a special type of software that create
and offer logical map as well allows it model elements of data into records. Thus data model is generally
divided into two algorithms the first one is theory that is the formal explanation of structure of the data
and their uses without any technically complex terms and the second one is occurrence means
application of the database management application as per requirement or to meet with the
requirement. The different types of data model are as follows:
 Hierarchical data model
This is a data model that is allows to organize a database just like a tree structure. Here all
information is associated with their parent node and all child nodes must have a parent node. The
advantage of hierarchical data model is its simplicity because the structure of this model follow tree
like model where all information can easily retrieve. This type of model also allows protecting the
database completely. The second biggest advantage of this model is having a large number of one-
to-many relationships but this model also has some disadvantages like it is not easy to implement
when requirement or purpose is not clear. The database affect if one try to make change in single
row of any table. Here the basic database operations like insert, delete, update and amend become
more tedious job.
 Network data model
The network data model basically having two basic operations like records and sets. The first basic
construct included fields, set define as well as one too many relationship called records and owner.
The biggest advantage of network model is simplicity because it looks like hierarchical model and is
easy to implement. The network model encompasses the many to much relationship in between the
tables. It also offer faster database accessing ability and easier. The network model also requires
large set of pointers and then insertion, deletion and update of database is more tedious.
 Relational data model
This is widely used data model that is used to develop a database by incorporating relations and
tables. This type of model consist description called schema that is based on first order logic and
physical representation. Here the operation of database management and implementation is done
by using SQL queries and perform operation using some set of query like update, delete, create and
read. The most important property of this model is simplicity and fast access to the database. If a
user wants to make change in some table then whole database never affected but the
implementation of this model is more costly.
 ER data model
The ER data model is some special kind of model that enables to perform large set of operation
using some predefined rules. Here the tables are related to each other using relationship. The ER
model uses real life entities to design and develop database.

Comparison of data models are as follows:
Hierarchical Data
Model
Network Relational Data model ER Model
Easy for all type of
users
Easy for access in
record
Can use this model
using SQL
Very simple to design
and develop
Highest performance Easy to implement for
complex databases
Perform changes in
database using SQL
query easily
Offers graphical
representation
Predefined path More flexible and easy
to navigate
Very Flexible Offers easy conversion
of information and
data sets.
Faster access Simple and faster
access as comparison
to hierarchical data
model
Data duplicate not
allowed
Faster access and
duplicate data is not
possible
Allows for One to many
relations
Doesn’t allows for one
to many relation but
relationship
establishment is easy.
Easily update and
modified
Highly integrated with
relational model
Difficulties in managing
data in this data model
Perform changes in
database creates
trouble
Enhanced security on
entire database
Limited constraints and
notations
Difficult to retrieve
information from
lowest level of
database
Required large number
of adjustment in
pointer on performing
CRUD operation
Requires enough
budget on set up and
maintenance.
Data manipulation not
allowed in ER model
Database Schema
The schema defines entire set of entities and their attributes and provided logical view of the database.
Database schema shows how an entity is related to other entity. It also provides physical representation
of database, their structure that allows to easily understanding flow of data. It is of two types:
Physical Schema
Physical schema enables to understand how a data is retrieved from database and offer ability to
analyze the physical structure of database.
Logical Schema
It allows logical view over the database including table, forms, queries and views.
1.2Benefits and Limit of database technologies
The benefits and limit of the database technologies are as follows:
Benefits

 Allows for sharing of data, management of data and set up environment in well
formatted way.
 Offer enhanced security and privacy to the database system for better protection.
 Offers good data integration means if one table is affected then whole database is being
affected.
 Minimized data redundancy means it facilitate to manage repetition of data in large set
of database.
 Allows for easy update, amend and delete record from database.
Limitation
 Setup of database, selection of database development tool management of data storage
all these actions requires high investment.
 In larger firm there are different type of user are there because implementation and
complexity of data is higher.
The database system requires higher maintenance and also require tracking to protect from
undesired access.
1.3Approach to design database
There are several type of approaches available to design a relational database management
system. Few of them are as follows:
Centralized database design Approach
The centralized database design approach have several module that is related with the
database management system. This type of approach consist several departments and if the
entire operation is managed to the single point then the approach is known as centralized
database design approach. Here almost entire datasets are stored on single point and minimize
the problem of chance of losing data. This is the biggest advantage of this approach.
Decentralized database design Approach
In the decentralized database design Approach all the operation is done from different point
and data are managed separately. Decentralized database design Approach reduces the
complexities of the database design. This is the biggest advantage of this database design
Approach.
Top-Down database design Approach
Top-Down database design Approach is third approach of database design which use basic
concept to design database and provide basic design of work flow. In Top-Down database
design Approach last step is the final stage where information used in this approach is manage
after completing stages.

Bottom-Up database design Approach
Bottom-Up database design Approach is the advanced approach of database design. The
Bottom-Up database design Approach is starts after getting final result from design of layout.
The Bottom-Up database design Approach is used to design the relational database
management system completely as per defined requirement of the user.

Task2
2.1Design of relational DBMS against requirement
This is the assessment where I have to design the relational database management system as per the
defined requirement of Dominican college. So I have developed a relational database and named it
College_DB.accdb. The evidence of design of relational database are as follows:
Entity relationship diagram for the College_DB.accdb
Design of form
I have also designed forms and related sub form on the basis of gathered data. Using this design
of form later I will develop final form. First I have designed a form for the lecturer unit
allocation and then I have designed a form for student course allocation by the admin of the
Dominican college. All the designed forms are as follows:

2.2Development of RDBMS
The development of database for the Dominican College depends upon given artifacts and
selection of database development tools and technologies. Here I have selected MS Access
Database Development tool.
Development Platform
To outline an intelligent database for the school Dominican, above all else, I have picked the
Microsoft Access database. The explanation for picking this database is that the MS Access DB is
anything but difficult to introduce, coordinate and furthermore financially savvy. It gives the
advantageous stockpiling to putting away the database. It additionally offers the multiuser
bolster for the database and in future if the client needs to recreate the database or need any
adjustment in database than they can without much of a stretch imports their everything
information, so I have picked the MS Access database for building up the database
Define purpose
I have dissect all the contextual investigation to discover what sorts of information must be
store in the database and after that planning a harsh chart for the report, frames and what
tables is to be utilized as a part of the database.
Develop table
As per the information gathered should have been store in the database and structure planned,
I have made all the database tables with the pertinent name so client can without much of a
stretch perceive what information put away in what table.
Define Unique and primary key
I have recognized all the database tables with an interesting worth which is called essential key
in the database. The one of a kind esteem utilized as a part of setting up the connection
between the distinctive tables of the database, with the goal that I have utilized a remarkable
identifier key for every one of the tables to set up the connection between the database tables.
Establish relationship

When all the database tables are characterized with the one of a kind key or esteem, I have
composed a harsh graph of all the database tables and watch the connection between every
one of the tables so the client can get their particular outcome from the database.
Review
When whole database tables are made than now, I have checked every one of the things, work
process and procedure of the made database to discover any work process that still remain or
not working and afterward update that piece of the database. It is anything but difficult to
change the outline and procedure of the database at this phase before entering the information
in the database.
2.3Database tools to increase user interface
PHPMY Admin
PHPMY admin is outstanding amongst other stages permits to build up a database with the
expanded controlling component. It is open source and can deal with any measure of database.
MYDB
MYDB is a database advancement devices that permits to create server organization. It offers
less complex GUI and simple to utilize include that improves the capacity of designer and
clients. It is additionally open source, however, require enrollment.
MySQL Workbench
This is an instrument accessible for database designer, takes into account arrangement of the
server and it is unreservedly accessible for all kind of working frameworks. The administrator of
the database every now and again utilize this instrument.
DB Master
This is a device comprising vast arrangement of alternatives for improvement of database and
each Database administrator every now and again utilized this apparatus since they can play
out all sort of task utilizing this.

Task3
3.1 Benefits of manipulation and query tool
The benefits of manipulating and query tool in relational database management system are as
follows:
 Manipulation and Query tool allows retrieve important and useful database from the
large set of database.
 Manipulation and Query tool allows to protect and provide proper security over the
database. It protect sensitive datasets and offer accessibility to the database without
any problem of data redundancy.
 Manipulation and Query tool offer to establish relationship using key concept.
 Manipulation and Query tool applied to arrange data sets in well formatted way so that
by using query Dominican college can invoke any desire operation.
 Manipulation and Query tool offer several admin panel to the same database at single
instance.
 Manipulation and Query tool allows fast operation on update, delete and add any
information with the database.
 Manipulation and Query tool offer simple control and easy to understand user interface.
Insert Query
Insert into table-name values (data1, data2, data3, data4…..)
Update Query
Update table-name set column-name=value where condition;
Delete Query
Delete from table-name where condition;
Create Query
Create table name(s1_id varchar2(10), s2_name char(), age int());
Drop Query
Drop table table-name;

3.2 Implementation of query language
I have also implemented the query language to perform desired operation for Dominican
college newly proposed database.
Query1
Query2
Query3
Query4

Query5
Query6
Query7

Query8
3.3 Evaluation of how meaning full data has been extracted
Extricating of significant information from expansive gathering of database is more repetitive
undertaking, and can be called information mining. There is some inquiry dialects which

permits to extricate particular sort of information frame the databases help to get moment
rundown and information that limit the endeavors. Extricating the information isn't any simple
approach on the grounds that there are a few same kind of data or information introduce inside
the database with having a similar id yet utilizing an appropriate question for an assignment
limited the information of complexities. Using the below listed technique one can extract
valuable data.
DDL
It helps to gather important data from different data source and arrange in well structure
format. DDL play important role to develop object within the system.
DML
The DML is stands for Data manipulation language offers extensive number of question dialects
and furthermore the orders which help to separate and control some specific data from vast
accumulation of database. Utilizing them the administrator of the school can remove data of
any understudy, teacher and course detail from the social database administration framework.
Dominican college admin can use following syntax to extract meaningful data.
Syntax to delete the data:
DELET from Table-name where condition;
Syntax to insert the data:
INSERT INTO target [(field1[, field2[, …]])] VALUES (value1[, value2[, …])
Syntax to select the data
SELECT field1, field2 ….fieldn table_name1, table_name2…
[where clause]
[OFFSET M] [LIMIT N]

Task 4
4.1 Review and testing of RDBMS
Now Dominican college have a database system which can consist entire data set and all type of
information of college in arranged way. Means I have built up a course administration
framework to oversee courses, allot the individual courses to the speakers and furthermore tell
the understudies for that. As a database advisor I have built up a full responsive database
framework with satisfying all the prerequisite. Presently before convey the created framework I
have performed different sorts of testing to convey best ideal arrangement which makes fulfill
the worry. The testing systems and strategy of testing which I have used to test this
arrangement is as take after’s:
 Functionality- Performed testing on current database's all part and each capacity based
on proposed prerequisite and I have discovered that it is more alluring and ready to deal
with every one of the information of school inside single framework. All the inquiry
works is very much created and capacities according to their sort in a split second. The
charge for any activities results same not surprisingly.
 Performance- Student of all the year can get to the unified framework and
administrator of the school can without much of a stretch offer all the course detail and
in addition unit to them on constant with keeping up the timetable. Means all the work
is experience limited printed material which implies the brought together framework
spares a great deal of time and increment the proficiency of every one outcome in -
expanded rate of general profitability. The school ends up unified so making report on
activities occurred few example which increment the energy and workability of the
staffs viably.
 Security- To guarantee the new framework is secured and can ready to ensure the
whole database framework on client and in addition administrator board. As
remembering issue of information taking and misrepresentation cases I have make this
framework with keeping up full on security. Above all else there is a login board to verify
and approve the client implies if the client never make access without rectify subtle

elements. The administrator can constrain regions of access and furthermore without
earlier consent staffs can never make duplicate or erase anything from the database.
 Responsiveness testing- All the designed forms and sub forms are as per expectation
and performed well on entering data.
 Validation testing- I have validate forms and reports by entering wrong details and
found that forms and report generate error or respective message.
4.2 Document to support implementation and maintenance
To support the implementation and maintenance team I have developed a report so that they
will never face on time of implementing new feature or on time of installations of Microsoft
Access platform on college Operating Systems.
Tables
The concern has to select a table and select design view by right click on specific table. This
process is applicable for all table.
tbl_student
tbl_lecturer

tbl_course
tbl_level

tbl_unit
Queries

When a concern want to write a query or wants to make update in query then a user has to
select a query and then click on SQL view.
Query1
Query2
Query3
Query4

Query5
Query6
Query7

Query8

4.3 User Document
This is the user document by which a user can navigate within entire database and view table,
result of query and forms. By using this guide a user can easily learn how to use new system of
college.
Table
A user has to select a particular table and tap two time to open a table. A user can assign course
to student or lecturer using this database. Or can perform other desired actions. The same
process of running of table applicable for all other tables.
tbl_student
tbl_lecturer

tbl_course
tbl_level

tbl_unit
Queries

A user has to tap two time on any query to perform desired action and same will be applicable
for all.
Query1
Query2

Query3
Query4

Query5
Query6

Query 7
Query 8

4.4 Process of addressing verification and validation.
The process of addressing verification and validation are as follows:

System Verification
 Measure addressed or not addressed requirements.
 Starts on initiation of SDLC initiation.
 Review on developed design and sketch.
 Request for verification related work from college.
 Entire development work is depends over verification.
 Verify technical and user support document to fulfil requirements of the college.
Validation
 Validation process is done on the basis of aims and objectives.
 It is initiate at last of system development life cycle.
 Validation allows to validate all type of requirements.
 It also help to define new type of requirements.
 Validate entire system development work individually.
4.5 Control mechanisms
This is a methodology permits to make controls on assortment of substances like workers,
understudies, speakers, IT staffs, participation and numerous more inside the Dominican
College. Inside the database arrangement of Dominican school there are different sort of
elements made like grounds, course, unit, teachers, understudies, understudy points of interest
and some more. All these are controlled utilizing different sorts of control instrument to make
them legitimately more useful. This component likewise influence the center working and
business capacity of this school.
 Planning- Planning stage provide ability to create a plan to boost revenue of the college
and take important steps.
 Leading- The college team regularly check workability of employees and encourage to
make improvement in their workability if needed.
 Return on investment- The College has aim to enhance throughput of entire
organisation by efficiently manage all operations and transactions become competitive
organization in learning. So the control mechanism allows to communicate in better way
to manage the information and perform operation within entire organization.
 Controlling- Consistently check staffs to avoid conflict issues.

All the control mechanism can efficiently use to boost workability and effectively increase
revenue of the college. Means these control mechanism will play vital role to perform desired
action in most effective way. The college will use the control mechanism in following way to
manage and enhance the college workability.
Inference control
Utilizing this control component the administrator can stay away from unapproved exposure of
school, understudy or teacher data. Under this the administrator can likewise control over the
every single recorded inquiry inside databases. This instrument permits to persistently look and
track the entire framework and gets to without going anyplace.
Access control
Used to determine security and privacy policy to validate access of staffs and provide protection
from vulnerabilities.
 The admin staff of the college can make mark absent when a teacher staff is not
present.
 Database admin can perform all desired operations.
 Admin can add new staff and assign course using this system.
Flow Control
Flow control mechanism permits to control stream of information or data inside the social
database framework. So by utilizing this the administrator can restrain the stream and control
over the stream of data which permits the administrator to just give the expected information
to the individual concern.

Conclusion
The development of the database work is successfully done by me using MS Access database
development tool. The whole assessment is based on research and development of MS Access. I have
successfully designed and developed database system for the Dominican College. Using this system. To
complete this assessment I have go through online resource to learn development system by
capturing all required. To successfully complete this assessment I have first go through analysis
of requirement for Dominican college database and gather all information. Then make a
comparison in between data models and design an entity relationship diagram. The developed
database, design forms and develop Query using concept of inner Join and outer join.

References
Techopedia.com. (2017). What is Data Manipulation Language (DML)? - Definition from Techopedia.
[online] Available at: https://www.techopedia.com/definition/1179/data-manipulation-language-dml
[Accessed 6 Jun. 2017].
Pling.org.uk. (2017). Relational Databases and Query Languages. [online] Available at:
http://www.pling.org.uk/cs/rdq.html [Accessed 6 Jun. 2017].
Kapetanios, E. and Groenewoud, P. (2017). Query Construction through Meaningful Suggestions of
Terms. [online] Dl.acm.org. Available at: http://dl.acm.org/citation.cfm?id=652303 [Accessed 6 Jun.
2017].
Gupta, Y. (2017). Why should we test a Relational Database. [online] Software Testing Genius. Available
at: http://www.softwaretestinggenius.com/why-should-we-test-a-relational-database [Accessed 6 Jun.
2017].
database, V. (2017). Verification of SQL database. [online] Stackoverflow.com. Available at:
https://stackoverflow.com/questions/4105508/verification-of-sql-database [Accessed 6 Jun. 2017].
Studytonight.com. (2018). Database Models in DBMS | Studytonight. [online] Available at:
https://www.studytonight.com/dbms/database-model.php [Accessed 8 May 2018].
Lucidchart. (2018). What is a Database Model. [online] Available at:
https://www.lucidchart.com/pages/database-diagram/database-models [Accessed 8 May
2018].
Foote, K., Foote, K. and Foote, K. (2018). A Review of Different Database Types: Relational
versus Non-Relational - DATAVERSITY. [online] DATAVERSITY. Available at:
http://www.dataversity.net/review-pros-cons-different-databases-relational-versus-non-
relational/ [Accessed 8 May 2018].
Practice.geeksforgeeks.org. (2018). What are the different approaches for Database Design ? |
Practice | GeeksforGeeks. [online] Available at:
https://practice.geeksforgeeks.org/problems/what-are-the-different-approaches-for-database-
design [Accessed 8 May 2018].
Database Management. (2018). Database Design Strategies. [online] Available at:
http://databasemanagement.wikia.com/wiki/Database_Design_Strategies [Accessed 8 May
2018].
Support.office.com. (2018). Create a simple select query. [online] Available at:
https://support.office.com/en-us/article/create-a-simple-select-query-de8b1c8d-14e9-4b25-
8e22-70888d54de59 [Accessed 8 May 2018].
Support.office.com. (2018). Create a form in Access. [online] Available at:
https://support.office.com/en-us/article/create-a-form-in-access-5d550a3d-92e1-4f38-9772-
7e7e21e80c6b [Accessed 8 May 2018].