RDBMS Design and Implementation: Dominican College Database Project

This project outlines the design and implementation of a relational database management system (RDBMS) tailored for Dominican College, addressing their need to automate the management of student records, course details, and lecturer information across three campuses. The project begins with a comparison of different data models, including hierarchical, network, relational, and ER models, ultimately justifying the selection of the relational data model for its simplicity and fast access. It then details the design of the database, named College_DB.accdb, within MS Access, including entity-relationship diagrams and form designs. The development process covers defining the purpose, developing tables, establishing relationships, and implementing SQL queries for data manipulation. Furthermore, the project discusses database tools to enhance the user interface and benefits of query tools. The project concludes with a review and testing of the RDBMS, along with documentation to support implementation and maintenance, addressing verification, validation, and control mechanisms.

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;