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

This report details the design and implementation of a relational database system for Dominican College. It covers various data models, database technologies, and the design process using ER modeling and normalization. The report also includes a comprehensive analysis of the implemented database, including its features, validation, testing, and documentation. The database is designed to manage student, lecturer, and course information, enhancing the college's operational efficiency.

Data Analysis and Design

Table of Contents
Introduction...............................................................................................................................7
LO1 –Understand data models and database technologies......................................................8
A.C. 1.1. Critically compare different data models and schemas...........................................8
A.C. 1.2.Critically discuss the benefit and limitations of the different database
technologies.........................................................................................................................13
A.C.1.3 Analyse different approaches to database design two approaches........................20
LO2. Be able to design and implement relational database systems......................................24
A.C.2.1 Design a relational database system to meet a given requirements......................24
A.C.2.2- Build a relational database system for the database design..................................29
A.C.2.3 Apply a range of database tools and techniques to enhance the user interface....39
LO3 - Be able to use manipulation and querying tools............................................................44
A.C. 3.1 Explain the benefits of using manipulation and query tools in a relational
database system...................................................................................................................44
A.C.3.2 Implement a query language into the relational database system.........................49
A.C.3.3 Critically evaluate how meaningful data has been extracted through the use of
query tools for the given case study....................................................................................55
LO4 - Be able to test and document relational database systems..........................................56
A.C.4.1Critically review and test the implemented relational database system.................56
A.C.4.2Create documentation to support the implementation and testing........................58
A.C.4.3Create user documentation for the developed relational database system...........77
A.C.4.4 Explain how verification and validation has been addressed in the developed
relational database system..................................................................................................88
A.C.4.5 Explain how control mechanisms have been used in the developed relational
database system...................................................................................................................90
Conclusion................................................................................................................................91
1

References................................................................................................................................92
List of figures
Figure 1: Hierarchical model......................................................................................................8
Figure 2: Example of Relational model......................................................................................9
Figure 3: Example of Network Model......................................................................................10
Figure 4: Architecture of database schema.............................................................................11
Figure 5: File Based System......................................................................................................13
Figure 6: Example of RDMS......................................................................................................14
Figure 7: Example of Multimedia database.............................................................................15
Figure 8: Example of Object-oriented database......................................................................16
Figure 9: Distributed database.................................................................................................17
Figure 10: Data warehouse......................................................................................................18
Figure 11: Cloud computing.....................................................................................................19
Figure 12: Notation- ER diagram..............................................................................................21
Figure 13: Normalisation Process............................................................................................22
Figure 14: Example of trainee and organisation......................................................................23
Figure 15: Conceptual diagram................................................................................................28
Figure 16: Logical Diagram.......................................................................................................28
Figure 17: Academic details table............................................................................................29
Figure 18: Admin table.............................................................................................................29
Figure 19: BSc Accounting table...............................................................................................29
Figure 20: Course Details table................................................................................................29
Figure 21: CIS Table..................................................................................................................30
Figure 22: Learner details table...............................................................................................30
Figure 23: Lecturer type details...............................................................................................30
Figure 24: Management table..................................................................................................31
Figure 25: Student information table......................................................................................31
Figure 26: Validation on range of digit in Academic Table......................................................31
Figure 27: Not Null Validation in Course ID.............................................................................32
2

Figure 28: Text Validation on Name in academic details.........................................................32
Figure 29: Validation of not null on Student ID.......................................................................33
Figure 30: Validation of Not null on course ID.........................................................................33
Figure 31: Validation of data type on course ID......................................................................34
Figure 32: Validation of non-negative integer on year............................................................34
Figure 33: Validation of non-negative integer on Fees............................................................35
Figure 34: Validation on fees of non-negative integer............................................................35
Figure 35: Checkbox validation on Learner details Table........................................................36
Figure 36: Email format validation in Lecturer type table.......................................................36
Figure 37: Digit range validation on contact in Learner details table......................................37
Figure 38: Non-negative validation on Lecturer ID in Management table..............................37
Figure 39: Validation of E-Mail in Student info table...............................................................38
Figure 40: Contact Number validation on Student info table..................................................38
Figure 41: Academic details Table’s insertion form.................................................................39
Figure 42: Admin Table’s insertion form..................................................................................40
Figure 43: BSc. Accounting Table’s insertion form..................................................................40
Figure 44: CIS Table’s insertion form.......................................................................................40
Figure 45: Course details Table’s insertion form.....................................................................41
Figure 46: Learner details Table’s insertion form....................................................................41
Figure 47: Lecturer type Table’s insertion form......................................................................42
Figure 48: Management Table’s insertion form......................................................................42
Figure 49: Student Info Table’s insertion form........................................................................43
Figure 50: List of different commands used in SQL.................................................................44
Figure 51: Design view of Table Academic details...................................................................46
Figure 52: Tabular or datasheet view of Academic details table.............................................47
Figure 53: 1 Query result view of Query Course Details..........................................................47
Figure 54: SQL view of course detail query..............................................................................47
Figure 55: Report view of Course detail query........................................................................47
Figure 56: Insertion Form view of Student Info Table.............................................................48
Figure 57: SQL view of Query on Course details and academic manager...............................49
Figure 58: Datasheet view of result of query 1........................................................................49
Figure 59: Query 1 Relationship...............................................................................................49
3

Figure 60: SQL view of Query on Academic manager and lecturer.........................................49
Figure 61: Datasheet view of result of query 2........................................................................50
Figure 62: Query 2 Relationship...............................................................................................50
Figure 63: SQL view of Query on Admin and Student.............................................................50
Figure 64: Datasheet view of result of query 3........................................................................50
Figure 65: Query 3 Relationship...............................................................................................51
Figure 66: SQL view of Query on Student and course.............................................................51
Figure 67: Datasheet view of result of query 4........................................................................51
Figure 68: Query 4 Relationship...............................................................................................51
Figure 69: SQL view of Query on learner and academic manager..........................................52
Figure 70: Datasheet view of result of query 5........................................................................52
Figure 71: Query 5 Relationship...............................................................................................52
Figure 72: SQL view of Query on Course details and lecturer.................................................52
Figure 73: Datasheet view of result of query 6........................................................................52
Figure 74: Query 6 Relationship...............................................................................................53
Figure 75: SQL view of Query on Lecturer and course details.................................................53
Figure 76: Datasheet view of result of query 7........................................................................53
Figure 77: Query 7 Relationship...............................................................................................53
Figure 78: SQL view of Query on Admin and assigned courses...............................................53
Figure 79: Datasheet view of result of query 8........................................................................54
Figure 80: Query 8 Relationship...............................................................................................54
Figure 81: Relationship table...................................................................................................56
Figure 82: Validation on range of digit in Academic Table......................................................58
Figure 83: Not Null Validation in Course ID.............................................................................59
Figure 84: Text Validation on Name in academic details.........................................................59
Figure 85: Validation of non-negative integer on year............................................................60
Figure 86: Validation on fees of non-negative integer............................................................60
Figure 87: Email format validation in Lecturer type table.......................................................61
Figure 88: Digit range validation on contact in Learner details table......................................62
Figure 89: Non-negative validation on Lecturer ID in Management table..............................63
Figure 90: Validation of E-Mail in Student info table...............................................................64
Figure 91: Contact Number validation on Student info table..................................................65
4

Figure 92: SQL view of Query on Course details and academic manager...............................65
Figure 93: Datasheet view of result of query 1........................................................................65
Figure 94: SQL view of Query on Academic manager and lecturer.........................................66
Figure 95: Datasheet view of result of query 2........................................................................66
Figure 96: SQL view of Query on Admin and Student.............................................................66
Figure 97: Datasheet view of result of query 3........................................................................66
Figure 98: SQL view of Query on Student and course.............................................................66
Figure 99: Datasheet view of result of query 4........................................................................67
Figure 100: SQL view of Query on learner and academic manager........................................67
Figure 101: Datasheet view of result of query 5......................................................................67
Figure 102: Design view of Academic Details..........................................................................68
Figure 103: Design view of Admin...........................................................................................69
Figure 104: Design view of BSc. Accounting............................................................................70
Figure 105: Design view of CIS.................................................................................................71
Figure 106: Design view of Course details...............................................................................72
Figure 107: Design view of Learner details..............................................................................73
Figure 108: Design view of Lecturer Type................................................................................74
Figure 109: Design view of Management................................................................................75
Figure 110: Design view of Student info..................................................................................76
Figure 111: Academic details table..........................................................................................77
Figure 112: Admin table...........................................................................................................77
Figure 113: BSc Accounting table.............................................................................................77
Figure 114: Course Details table..............................................................................................78
Figure 115: CIS Table................................................................................................................78
Figure 116: Lecturer type details.............................................................................................78
Figure 117: Management table................................................................................................79
Figure 118: Student information table....................................................................................79
Figure 119: Learner details table.............................................................................................79
Figure 120: Academic details Table’s insertion form...............................................................80
Figure 121: Admin Table’s insertion form................................................................................80
Figure 122: BSc. Accounting Table’s insertion form................................................................80
Figure 123: CIS Table’s insertion form.....................................................................................81
5

Figure 124: Course details Table’s insertion form...................................................................81
Figure 125: Learner details Table’s insertion form..................................................................81
Figure 126: Lecturer type Table’s insertion form....................................................................82
Figure 127: Management Table’s insertion form....................................................................82
Figure 128: Student Info Table’s insertion form......................................................................83
Figure 129: SQL view of Query on Course details and academic manager.............................83
Figure 130: Report view of query 1..........................................................................................84
Figure 131: SQL view of Query on Academic manager and lecturer.......................................84
Figure 132: Report view of query 2..........................................................................................84
Figure 133: SQL view of Query on Admin and Student...........................................................85
Figure 134: Report view of query 3..........................................................................................85
Figure 135: SQL view of Query on Student and course...........................................................85
Figure 136: Report view of query 4..........................................................................................85
Figure 137: SQL view of Query on learner and academic manager........................................86
Figure 138: Report view of query 5..........................................................................................86
Figure 139: SQL view of Query on Course details and lecturer...............................................86
Figure 140: Report view of query 6..........................................................................................86
Figure 141: SQL view of Query on Lecturer and course details...............................................87
Figure 142: Report view of query 7..........................................................................................87
Figure 143: SQL view of Query on Admin and assigned courses.............................................87
Figure 144: Report view of query 8..........................................................................................87
Figure 145: V Model.................................................................................................................88
Figure 146: Access Control Mechanism with privacy..............................................................90
List of table
Table 1: Difference between different data model.................................................................10
Table 2: Data Dictionary...........................................................................................................26
6

Introduction
A platform of database is designed for the Dominican college to store, retrieve and handle
the large amount of data. This database will help in maintaining all the information on the
regular basis so that performance of the college can be enhanced and activities such as
exams and classes can be carried out easily. The information in this report presented here
includes the description of the practical part of relational database model. Here different
evidence will be provided in accordance with screenshots of the database. The first part of
the report explains about the various types of data models and database technologies.
Second part of the report contains the data with respect to design and implementation of
database. Third part of the report contains the details of manipulation and querying tools
used in the database. Last part of the report includes the testing and documentation of the
designed database.
7

LO1 –Understand data models and database technologies
A.C. 1.1. Critically compare different data models and schemas
Data models plays and key rule in finding solutions for different business sectors. There are
several models which can be used to get the appropriate solution. They are responsible for
the creation of logical structures which is basic structure of the database management
system.
Types of Data models
Hierarchical model: The structure has a format of hierarchy chain between different records
and attributes just like the parent and child relation.
Figure 1: Hierarchical model
Source: (DBMS Internals, 2018)
Advantages
 Structure is simple
 Design have high efficiency
 Information is isolated
 Security is high
Disadvantages
 Complex implementation
 The design is difficult to manage
 Coding is difficult (Carroll, 2016).
8

Relational model: In the structure of this model, there are fix number of unique objects
having unique properties by which they can be identified easily.
Figure 2: Example of Relational model
Source: (Chari, 2016)
Advantages
 Data can be easily isolated
 Conceptual design is simple
 Design is simple and can be implemented easily
 Good quality system is developed
Disadvantages
 High maintenance cost
 Issues in design create great loss to information
Network Model: In the structure of this model, the records and attributes are linked in
incremental stage having some relationship such as one-to-many relationship.
9

Figure 3: Example of Network Model
Source: (Lucidchart, 2016)
Advantages
 Multiple relationships can be handled easily
 Design is simple and easily understandable
 Flexible structure having high data integrity
 Standard coding can only be used
Disadvantages
 Highly complex
 Designed structure are independent
Table 1: Difference between different data model
Features Hierarchical Model Relational Model Network Model
1. Design of
the data
structure
This model only support
the one-to-one and one-to-
many relationships
This model supports
all the three
relationship and they
are one-to-one, one-
to-many and many-
to-many.
The relationship
supported by this
model is many-to-
many.
2. Layout of
the data
structure
Layout of the structure is in
the form of parent child
Design layout is in the
form of table where
relation or links are
created
Layout of this design
is in the form of
incremental stages
3. Manipulatio It does not support query Several query In this, there is also
10

n of the data interface for the
manipulation of the data
interface is available
such as SQL which
provides
representation of
many features under
single query
the availability of the
querying language
such as CODASYL
(Conference on Data
System languages)
which supports the
manipulation
4. Retrieval of
the data
Command available for the
retrieval of the data is
complex in addition to
asymmetric
The statements used
for retrieving the
data from the system
is easy to understand
and implement
Statements used for
retrieval of data are
complex but easily
understandable
5. Integrity of
the
information
Integrity of data is not
maintained properly due to
inconsistency in design
The update of the
information does not
support anomalies
Anomalies are not
present in data
update
6. Removal of
the data
Delete anomalies can easily
be implemented
There is no effect of
the data deletion on
rest of the
information
The design of this
model has much
affect from delete
anomalies on rest of
the data (Sen, 2012).
Database schema
Figure 4: Architecture of database schema
Source: (Relationaldbdesign, 2017)
11

It contains three levels and they are:
1. External view: It includes the user views of the database. It is responsible to show
the user related information and rest of the information remain hidden.
2. Conceptual level: It is responsible for describing the structure of the developed
database but the physical storage structure us remain hidden. It is also known as
conceptual schema of high level data model which contains entities, data types,
relationships and constraints
3. Physical level: At this level, the physical schema is developed which explains the
structure of physical storage of the database. It contains all the details of the
database including the data path (Relationaldbdesign, 2017).
12

A.C. 1.2.Critically discuss the benefit and limitations of the different
database technologies
The different database technologies are explained here:
File-based: This system is the traditional based system which uses high amount of workforce
for the management. The data is stored simple in the simple paper and filing formats in the
computer system
Figure 5: File Based System
Source: (Senecac, n.d.)
Benefits:
 Less cost is required for the hardware and software
 Design is easily understandable
 Implementation is easy
 Less skilled workforce can handle it
Limitations
 Security is less
 Inconsistency in data
 Higher rate of data redundancy
 Speed decreases with the increase of data (Soopertutorials, 2009)
Relational database: This system has a specific format of tables which are engaged in the
storing of data. The system developed in correspondence to these tables is the relational
13

database system. Several tables are linked with each other with some pre-defined
relationship.
Figure 6: Example of RDMS
Source: (Teach-ict, n.d.)
Benefits
 Easily implemented
 Software can be used according to the need
 Query interface is available for the manipulation
 Easily understandable and highly flexible
Limitations
 Performance decreases with the increase of data
 Retrieval of data is slow
 It requires physical storage for data (Maxlogix, 2009).
Multimedia database: This database is used for the storage of the multimedia type of files
such as audio, video, images, text and so on.
14

Figure 7: Example of Multimedia database
Source: (Flylib, 2017)
Benefits
 Easy to manipulate
 Easy to implement
 Formats created are flexible
 Different data are separately placed
Limitations
 Requires large memory for storage
 Search and retrieval of data is difficult
 Redundancy is difficult to remove
 Difficult to configure (Angelfire, n.d.).
Object-oriented database: This type of database approaches the methodology of object
oriented paradigm which includes the focus of operations is on objects rather than on
procedure.
15

Figure 8: Example of Object-oriented database
Source: (ODBMS Facts, n.d.)
Benefits
 High capability of modeling
 Able to handle large number of data types
 Advanced expressive query language is used here
 Performance of the database is high
Limitations
 It is not a universal data model
 Experience and skills matters
 Lack of available standards
 Less security (THAKUR, 2018).
Distributed database: This type of database is developed in context to use of networking
and data is stored in various components in the entire network.
16

Figure 9: Distributed database
Source: (Murphy, 2016)
Benefits
 Easily accessible irrespective to the location of data
 Manipulation of data is easy
 Integrity is always maintained
 High security
Limitations
 Performance is reduces for remote data
 Traffic in network is high
 Optimization is hard (More Process, 2013).
Data warehousing (data-mars and data mining): Data is stored electronically into the
databases and provides an initial check to various businesses. This data can be analyzed and
reported whenever required.
17

Figure 10: Data warehouse
Source: (Walker, 2015)
Benefits
 Data retrieval is fast
 Identification of error is easy
 Integration is easy
Limitations
 Process is time consuming
 Compatibility is difficult
 Regular maintenance is required (Businessimpact, 2018).
Web-based technology (cloud computing)
This technology is related to the computer system based on internet connectivity and data
can be shared between remote computer systems via internet. It includes a connection of
large number of devices into a single network as described in the diagram below.
18

Figure 11: Cloud computing
Source: (SolutionDots, 2016)
Benefits
 Efficient in terms of cost
 Storage is huge
 Availability of backup and recovery
 Easy deployment
Limitations
 Highly prone to attack
 Technical issue may affect the functionality (Viswanathan, 2017)
19

A.C.1.3 Analyse different approaches to database design two approaches
Design approach decides the process of making the layout of the database. The process
developed is such that it must support the functions of the particular organisation. Two of
the approaches are Top-down and bottom up.
Top-down: In this approach, the next level can be identified easily and can easily aggregate
the operational activities. This approach is simple and backward looking process.
Bottom-up: This approach analyses the completed level and find the possibility of the risk in
the design. The frequency of the level can be identified with this approach. This approach is
complex and forward looking process (Chandana, 2017).
ERD modelling process using top-down approach
The best example of top-down approach is ER modelling. This approach is used for the
designing of the database. ER modelling is the logical diagram which contains entities,
relationships and attributes to represent the units at the organisation. This approach is
accurate in designing the model and helps in decreasing the data redundancy. With this,
stability and flexibility of the design will enhance (Bailey, 2017).
Notation- ER diagram
20

Figure 12: Notation- ER diagram
Source: (Dangtinbdshanoi, 2017)
Entity: It is something that is used to store the data such as objects, roles or events. They
can be differentiated by their properties or attributes.
Attributes: They are the properties of the entities and have specific values so that they can
be used to uniquely identify the entities.
Relationship: It is type of link connected between two entities so that their combination can
fulfil the user requirements. There are several of types of relationship known as mapping.
They are:
 One-to-one: In this, one object of entity is related to single object of another entity
through uniquely identified attribute.
 One-to-many: In this, one object entity is related to many objects of another entity
through uniquely identified attribute.
 Many-to-many: In this, many objects of an entity is related to single object of
another entity through uniquely identified attribute (Dangtinbdshanoi, 2017).
21

Bottom-up approach and normalisation process
The best example of the bottom-up approach is normalisation in which this approach
provides the best to generate the best form.
Process:
1. Identify all the fields that are required
2. Now identify the entities
3. Now assign the primary key to each entity
4. Distribute them into well-defined tables
5. Now develop the first normal form from un-normalised form by removing repetitive
groups
6. Now develop the second normal form by removing partial key dependencies.
7. Third normal is created from second form with the removal of non-key dependencies
Figure 13: Normalisation Process
Source: (Unn-isrd1, n.d.)
22

Figure 14: Example of trainee and organisation
Source: (Walker, 2015)
As all the techniques are discussed so M1 is achieved.
23

LO2. Be able to design and implement relational database systems
A.C.2.1 Design a relational database system to meet a given requirements
Relational database has been developed for the Dominican College in order to handle the
different operation. This database is developed with the help of MS Access and the database
is developed by creating the normalised form of the tables.
Purpose of the Normalisation: It helps in developing the tables in a better way. This makes
the database flexible so that any information can be retrieved easily. This also helps in
removing the issue of duplicity by providing more distributed forms of tables.
Un-normalised form: This contains the random group of tables organised in some group of
tables.
First normal form: This form is more advanced form of un-normalised form where repetitive
attributes are removed.
24

Second Normal form: This form is a more distributed form of first normal form and the
partial dependencies are removed.
25

Third Normal form: This form shows best distributed form which can be implemented for
the design of the database. Here the non-dependent keys are removed to form the third
normalisation form (Walker, 2015).
Data dictionary: This is a dictionary which contains the details of all the tables, their
attributes, data types and primary key. This is presented here in order to provide brief detail
of the tables at a particular place. Another reason of presenting this is that it is a evidence of
the implemented relational database.
Table 2: Data Dictionary
S. NO. Entity nam e Attributes Data type Primary key
1 Academic
details
Post_ID Number Post_ID
Name_info Text
Course_ID Number
2 Admin Admin_ID Number Admin_ID
Admin_Name Text
Course_ID Number
Stud_ID Number
Learner_ID Number
3 BSc.
Accounting
BUnit_ID Number BUnit_ID
BUnit_Name Text
Lecturer_ID Number
Course_ID Number
26

4 CIS CUnit_ID Number CUnit_ID
CUnit_Name Text
Lecturer_ID Number
Course_ID Number
5 Course Details Course_ID Number Course_ID
Course_Name Text
Year Number
Fees Currency
Location Text
6 Learner_details Learner_ID Number Learner_ID
Learner_Name Text
Unit_ID Number
Course_ID Number
Fees_paid Currency
Outsider Yes/No
7 Lecturer_type Lecturer_ID Number Lecturer_ID
Lecturer_name Text
Course_ID Number
Unit_ID Number
Email_L Text
Contact_L Text
8 Management MUnit_ID Number MUnit_ID
MUnit_Name Text
Lecturer_ID Number
Course_ID Number
9 Student_info Stud_ID Number Stud_ID
Stud_Name Text
Unit_ID Number
Course_ID Number
Post_ID Number
Email_S Text
Contact_S Text
27

Figure 15: Conceptual diagram
Figure 16: Logical Diagram
M2 is achieved with the use of normalization.
28

A.C.2.2- Build a relational database system for the database design
The database that has been developed for the Dominican College is presented here below.
Here are snapshots of the generated table and the validation that has been applied to the
table.
Table of the database are presented below with in addition with caption.
Figure 17: Academic details table
Figure 18: Admin table
Figure 19: BSc Accounting table
Figure 20: Course Details table
29

Figure 21: CIS Table
Figure 22: Learner details table
Figure 23: Lecturer type details
30

Figure 24: Management table
Figure 25: Student information table
Validation of the table:
Figure 26: Validation on range of digit in Academic Table
31

Figure 27: Not Null Validation in Course ID
Figure 28: Text Validation on Name in academic details
32

Figure 29: Validation of not null on Student ID
Figure 30: Validation of Not null on course ID
33

Figure 31: Validation of data type on course ID
Figure 32: Validation of non-negative integer on year
34

Figure 33: Validation of non-negative integer on Fees
Figure 34: Validation on fees of non-negative integer
35

Figure 35: Checkbox validation on Learner details Table
Figure 36: Email format validation in Lecturer type table
36

Figure 37: Digit range validation on contact in Learner details table
Figure 38: Non-negative validation on Lecturer ID in Management table
37

Figure 39: Validation of E-Mail in Student info table
Figure 40: Contact Number validation on Student info table
38

A.C.2.3 Apply a range of database tools and techniques to enhance the user
interface
There are various set of database tools and techniques that can enhance the user interface.
The relational database has been created using the platform of MS Access. It is a user
friendly and easily understandable platform. The different techniques that have been
researched to increase the user interface are:
 Consistency: The consistency of each button and blocks makes the database user
friendly
 Validations: The applied validations to the forms restrict the user from inserting
wrong value to the form.
 Use of effective approach: Use of normalisation for the development of the tables
made the database unique and the data redundancy is reduced (Pappas, 2017).
 Analysing the user requirements: Reading the case effectively provides a range of
requirements and these requirements must be considered while developing the
database
Forms: The snapshots of the forms are given below with their captions. Forms are used to
insert the data in the table. It contains button of add record, delete record and undo record.
Figure 41: Academic details Table’s insertion form
39

Figure 42: Admin Table’s insertion form
Figure 43: BSc. Accounting Table’s insertion form
Figure 44: CIS Table’s insertion form
40

Figure 45: Course details Table’s insertion form
Figure 46: Learner details Table’s insertion form
41

Figure 47: Lecturer type Table’s insertion form
Figure 48: Management Table’s insertion form
42

Figure 49: Student Info Table’s insertion form
D1 is achieved in context to ‘form’ screenshots as they show result of the implemented
database.
43

LO3 - 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.
Query and Manipulation: This is the form of statements that has been required to retrieve
and modify some specific data from the database. The requirement is according to the need
of the staff members of the Dominican college. The results of the query statements can be
viewed in the form of reports. This makes the database more informational system that
reduces the time of any operation.
There are several types of tools such as SQLite, SQL, DBeaver, DBComparer and so on. I have
selected the SQL or Structured Query Language.
Benefits of the using SQL:
 Highly portable language and has ability to run on other devices
 Available in low cost and can be used for big platforms ‘
 Learning of this language is easy and queries can also be developed easily
 Key words of this language match to the real world key words
 Allows view of different views in the MS Access (Lang, 2016).
Figure 50: List of different commands used in SQL
Source: (Lang, 2016)
44

Benefits of using MS Access:
45

 Easy availability
 High portability
 Requires less space
 Learning is simple
 Provides different set of views such as datasheet view, design view, forms and so on.
The evidence of different views is shown below with their captions.
Figure 51: Design view of Table Academic details
46

Figure 52: Tabular or datasheet view of Academic details table
Figure 53: 1 Query result view of Query Course Details
Figure 54: SQL view of course detail query
Figure 55: Report view of Course detail query
47

Figure 56: Insertion Form view of Student Info Table
48

A.C.3.2 Implement a query language into the relational database system
SQL or structured query language is used for developing the query for the relational
database of the Dominican College. This will helps the various staff members to retrieve the
information according to the need. The academic manager can easily retrieve the
information related to the lecturers and students. Similarly, admin is able to view student
and course information with the help of this language.
Steps followed: Select the query from the database to view the result. Right click on tab and
select SQL view to see the SQL statements. The snapshot of various queries developed for
the database is given below.
Query 1: Academic manager wants to retrieve the course details including their fees.
Figure 57: SQL view of Query on Course details and academic manager
Figure 58: Datasheet view of result of query 1
Figure 59: Query 1 Relationship
Query 2: Academic Manager wants to retrieve the details of lecturers.
49

Figure 60: SQL view of Query on Academic manager and lecturer
Figure 61: Datasheet view of result of query 2
Figure 62: Query 2 Relationship
Query 3: Admin wants to see the details of the students and their courses
Figure 63: SQL view of Query on Admin and Student
Figure 64: Datasheet view of result of query 3
50

Figure 65: Query 3 Relationship
Query 4: Students wants to see their details of course
Figure 66: SQL view of Query on Student and course
Figure 67: Datasheet view of result of query 4
Figure 68: Query 4 Relationship
Query 5: Academic manager wants retrieve the information of the learners applied for a
particular course
51

Figure 69: SQL view of Query on learner and academic manager
Figure 70: Datasheet view of result of query 5
Figure 71: Query 5 Relationship
Query 6: Query for lecturer and their units
Figure 72: SQL view of Query on Course details and lecturer
Figure 73: Datasheet view of result of query 6
52

Figure 74: Query 6 Relationship
Query 7: Academic manager need to retrieve the information of lecturer and their personal
details
Figure 75: SQL view of Query on Lecturer and course details
Figure 76: Datasheet view of result of query 7
Figure 77: Query 7 Relationship
Query 8: This query is on the assigned courses and the students
Figure 78: SQL view of Query on Admin and assigned courses
53

Figure 79: Datasheet view of result of query 8
Figure 80: Query 8 Relationship
M3 is achieved with the use of technical language throughout the report.
54

A.C.3.3 Critically evaluate how meaningful data has been extracted through
the use of query tools for the given case study.
Query tool used for database- SQL or Structured Query Language
This query language helps in accessing the information with the help of set of statements
within less duration of time. It does not require to physically accessing the entire table but
helps in generating the result automatically. This will help in enhancing the performance of
the college operations such as assigning classes and courses to the lecturers and students
respectively.
SWOT Analysis of the SQL: This analysis will generate the important points in terms of
strengths, weakness, opportunities and threats related to uses tool for the database of
Dominican College.
Strengths Opportunities
 Language is portable to any device
 Learning this language is easy
 Available at lower cost
 Highly skilled persons are not
required
 Provides easy manipulation of the
information
 Supports integration of system
 Sources are available easily
 Equal opportunities to both highly
skilled and lower skilled
Weakness Threats
 Accessibility of the Interface is
difficult
 Modifications leads to changes in
database
 Complex query leads to issues in
results
 Internet connection leads to
breaches
 Risk of vulnerability at the servers
 Technical issues at the devices
 Stolen information can decrease the
value of college in the city
(Reichwein, 2014).
D1 is achieved with the use of queries and produced results.
55

LO4 - Be able to test and document relational database systems
A.C.4.1Critically review and test the implemented relational database
system
The relational database developed for the Dominican College includes the nine entities
namely Academic details, admin, learner info, Student info, course details, management,
BSc. Accounting, lecture type and CIS. The relationship developed between these entities is
shown below.
Figure 81: Relationship table
Review on RDMS: Relational database system is implemented here with the help of MS
Access. This database helps the college staff in managing the regular activities such as
assigning classes. It also helps the academic manager and admin to manage the information
of the lecturers, students and learners from the outsiders. It also supports accessibility to
multiple users at the same time (Soltesz, n.d.). The speed of retrieving data from this kind of
database is high. It requires regular maintenance in order to perform good every time.
Testing of designed database: Tables developed at the database should be tested on the
basis of its property and validations applied. There are several types of testing methods and
they are:
56

a. Testing of tables: This includes the checks of their design view and checks on
attributes.
b. Testing of insertion form: In this testing, forms are tested by inserting the data
through them. This also performs checks on validations applied to the forms to
restrict the users in entering wrong data.
c. Testing of navigation: In this, the check is on single and double click including the
opening of the tables
d. Testing of relationships: This involves successful appearance results from queries on
tables which validates the true relationship
e. Testing of buttons: The check is related to the proper adding, deleting and undo the
inserted data.
D3 is achieved as critical thinking is stated on deigned database.
57

A.C.4.2Create documentation to support the implementation and testing
The test cases developed for validating the features of the designed database is specified
here in addition their snapshots. I have tested the validations presented here.
Test Case
Test Case 1 Expected outcome Actual outcome Result
Enter negative value
in Post ID of
Academic details
Pop up will appear
showing insertion of
data between 0 to 9
Appeared Pass
Figure 82: Validation on range of digit in Academic Table
Test Case 2 Expected outcome Actual outcome Result
Block is left blank in
course ID in Admin
table
Pop up will appear
showing insertion of
value
Appeared Pass
58

Figure 83: Not Null Validation in Course ID
Test Case 3 Expected outcome Actual outcome Result
Name of the
Academic table is
left blank
Pop up appear
showing insertion of
data
Appeared Pass
Figure 84: Text Validation on Name in academic details
59

Test Case 4 Expected outcome Actual outcome Result
Insert negative value
in Year
Message box appear
showing year can’t
ne negative
Appeared Pass
Figure 85: Validation of non-negative integer on year
Test Case 5 Expected outcome Actual outcome Result
Insertion of negative
value in Fees paid
Message box appear
showing fees can’t
be negative
Appeared Pass
Figure 86: Validation on fees of non-negative integer
60

Test Case 6 Expected outcome Actual outcome Result
Insertion of email id
without having
proper format
Message box appear
showing insertion of
valid email ID
Appeared Pass
Figure 87: Email format validation in Lecturer type table
61

Test Case 7 Expected outcome Actual outcome Result
Insert text in contact
of lecturer
Error message
appear showing
insertion of values
within 0 to 9.
Appear Pass
Figure 88: Digit range validation on contact in Learner details table
62

Test Case 8 Expected outcome Actual outcome Result
Insert negative value
in Lecturer ID
Pop up will appear
showing ID cannot
be negative
Appeared Pass
Figure 89: Non-negative validation on Lecturer ID in Management table
63

Test Case 9 Expected outcome Actual outcome Result
Insertion of email id
without having
proper format
Message box will
appear showing
insertion of valid
email ID.
Appeared Pass
Figure 90: Validation of E-Mail in Student info table
64

Test Case 10 Expected outcome Actual outcome Result
Insert text input in
Contact of Student
info
Message box appear
showing number
between 0 to 9
Appeared Pass
Figure 91: Contact Number validation on Student info table
Testing of queries
Query 1:
Figure 92: SQL view of Query on Course details and academic manager
Figure 93: Datasheet view of result of query 1
Query 2:
65

Figure 94: SQL view of Query on Academic manager and lecturer
Figure 95: Datasheet view of result of query 2
Query 3:
Figure 96: SQL view of Query on Admin and Student
Figure 97: Datasheet view of result of query 3
Query 4:
Figure 98: SQL view of Query on Student and course
66

Figure 99: Datasheet view of result of query 4
Query 5:
Figure 100: SQL view of Query on learner and academic manager
Figure 101: Datasheet view of result of query 5
Testing of data sheet through design view:
67

Figure 102: Design view of Academic Details
68

Figure 103: Design view of Admin
69

Figure 104: Design view of BSc. Accounting
70

Figure 105: Design view of CIS
71

Figure 106: Design view of Course details
72

Figure 107: Design view of Learner details
73

Figure 108: Design view of Lecturer Type
74

Figure 109: Design view of Management
75

Figure 110: Design view of Student info
76

A.C.4.3Create user documentation for the developed relational database
system
Documentation of the developed database includes the details of every table that has been
generated. This will help in storing the data of the college, lecturers, and courses and so on
at the college.
Table 1
Figure 111: Academic details table
Table 2
Figure 112: Admin table
Table 3
Figure 113: BSc Accounting table
Table 4
77
Table view
having data in
columns

Figure 114: Course Details table
Table 5
Figure 115: CIS Table
Table 6
Figure 116: Lecturer type details
78

Table 7
Figure 117: Management table
Table 8
Figure 118: Student information table
Table 9
Figure 119: Learner details table
Forms
79

Figure 120: Academic details Table’s insertion form
Figure 121: Admin Table’s insertion form
Figure 122: BSc. Accounting Table’s insertion form
80
Entries are filled
here in the
blocks
Entries are filled
here in the
blocks
Entries are filled
here in the
blocks

Figure 123: CIS Table’s insertion form
Figure 124: Course details Table’s insertion form
Figure 125: Learner details Table’s insertion form
81
This is for undo
any data
This is to delete
the record
Entries are filled
here in the
blocks
Entries are filled
here in the
blocks
This is the check
box

Figure 126: Lecturer type Table’s insertion form
Figure 127: Management Table’s insertion form
82
Data are
inserted here in
the blocks
These blocks are
columns to the
tables

Figure 128: Student Info Table’s insertion form
Documentation of queries and their reports
Query 1:
Figure 129: SQL view of Query on Course details and academic manager
83
Data are
inserted from
here for the
tables

Figure 130: Report view of query 1
Query 2:
Figure 131: SQL view of Query on Academic manager and lecturer
Figure 132: Report view of query 2
Query 3:
84

Figure 133: SQL view of Query on Admin and Student
Figure 134: Report view of query 3
Query 4:
Figure 135: SQL view of Query on Student and course
Figure 136: Report view of query 4
85

Query 5:
Figure 137: SQL view of Query on learner and academic manager
Figure 138: Report view of query 5
Query 6:
Figure 139: SQL view of Query on Course details and lecturer
Figure 140: Report view of query 6
Query 7:
86

Figure 141: SQL view of Query on Lecturer and course details
Figure 142: Report view of query 7
Query 8:
Figure 143: SQL view of Query on Admin and assigned courses
Figure 144: Report view of query 8
87

A.C.4.4 Explain how verification and validation has been addressed in the
developed relational database system.
For the verification and validation of the developed database for the database, there are
several test models that can be used. Some models are V model, Black model, and White
model and so on. In the V shape model, there are several stages which needs to be
implemented. The right hand stages are done for the verification process and the left hand
process is on the right hand side in the diagram. Verification is done before the
implementation of the database and validation is done is after the implementation of the
database.
Figure 145: V Model
Source: (FeedBurner, 2016)
Black box testing: This testing is done to test the software and in this there is no need of
internal structure of program and performed by testers. Programming details are not
required. System testing and acceptance testing are also done. It involves functional testing
also such as insertion of data from insertion forms.
White Box testing: This also test the software but with the help of knowledge in internal
structure of the database. This test is performed by software developers as the knowledge
88

of programming is required. Unit testing and integration testing is done here which includes
the validation check such as non-negative value test and null value test (Software Testing
Class, 2014).
D3 is achieved with the demonstration of validation and verification of the database.
89

A.C.4.5 Explain how control mechanisms have been used in the developed
relational database system.
Control mechanisms are responsible for controlling the control of rules and security across
the database so that information is kept secured in it. They are also known as access control
mechanisms. They restrict the access to particular service. It also controls the software that
are currently running on the systems.
Figure 146: Access Control Mechanism with privacy
Source: (CareerProjects, 2014)
I have researched two types of control mechanisms and they are:
a. Discretionary Access control: Under this control, the system can be accessed by the
owner of the Dominican College and is the only person who has the right such
information.
b. Mandatory Access control: Under this control, the information is handled by the
operating system of the computer. This is done in accordance to the security label
created inside the operating system. Owners and user have no access under this.
c. Role based Access control: Under this model, the access is distributed under the
privilege of role assigned to the staff members of the college such as academic
manager can only the information related to studies not the registration part (Rana,
2011).
90

Conclusion
This is to conclude that the report has been developed in context to the relational database
developed for the Dominican College. This report contains the information regarding the
different data model and schema of the database in addition to benefits and limitations of
the database technologies. Later the database has been designed with the help of ER
modelling and normalization. Tools and techniques of the relational database are also
explained. In the third part, the manipulation and querying tools are explained in addition to
the representation of different views of database. Lastly, the database has been tested
successfully and the documentation is done for better understanding of the users.
91

References
 Angelfire n.d. Benefits of using Multimedia Database. [online] Available at:
http://www.angelfire.com/wi3/mdb/benefits.htm [Accessed 24 Mar. 2018].
 Businessimpact 2018. The Pros & Cons of Data Warehouses. [online] Available at:
http://businessimpactinc.com/blog/the-pros-cons-of-data-warehouses/ [Accessed 24
Mar. 2018].
 CareerProjects 2014. Accuracy-Constrained Privacy-Preserving Access Control
Mechanism for Relational Data. [online] Available at:
http://careerprojects.in/accuracy-constrained-privacy-preserving-access-control-
mechanism-for-relational-data/ [Accessed 24 Mar. 2018].
 Chari, S. 2016. Database management system. [online] Slideshare. Available at:
https://www.slideshare.net/Shashikumar_chari/database-management-system-
67446272 [Accessed 24 Mar. 2018].
 Dangtinbdshanoi 2017. Er Diagram Symbols 2 Summary of Er Diagram Notation Er
Diagram Symbols Pdf. [online] Available at: http://dangtinbdshanoi.com/er-diagram-
symbols/er-diagram-symbols-2-summary-of-er-diagram-notation-er-diagram-symbols-
pdf/ [Accessed 24 Mar. 2018].
 Flylib, 2017. Chapter 5: Distributed Multimedia Database Systems. [online] Available
at: https://flylib.com/books/en/1.537.1.39/1/ [Accessed 24 Mar. 2018].
 Lang, T. 2016. Chapter 7 Introduction to Structured Query Language (SQL). [online]
Slideplayer. Available at: http://slideplayer.com/slide/6849421/ [Accessed 24 Mar.
2018].
 Lucidchart 2016. What is a Database Model? [online] Available at:
https://www.lucidchart.com/pages/database-diagram/database-models [Accessed 24
Mar. 2018].
 Maxlogix. 2009. Advantages and Disadvantages of using relational databases. [online]
Available at: http://maxlogix.blogspot.in/2009/09/advantages-and-disadvantages-of-
using.html [Accessed 24 Mar. 2018].
92

 More Process 2013. Advantages and disadvantages of distributed database. [online]
Available at: http://www.moreprocess.com/database/sql/advantages-and-
disadvantages-of-distributed-database [Accessed 24 Mar. 2018].
 Murphy, B. 2016. Distributed Databases. [online] Slideplayer. Available at:
http://slideplayer.com/slide/5822377/ [Accessed 24 Mar. 2018].
 ODBMS Facts n.d. Know the Difference-Learn How Object Oriented Database is
different from RDBMS. [online] Available at:
http://www.odbmsfacts.com/articles/object-oriented_databases/ [Accessed 24 Mar.
2018].
 Rana, M. 2011. Types of Access Control Mechanisms. [online] Techmahindra. Available
at:https://www.techmahindra.com/sites/blogs/types_of_access_control_mechanisms.
aspx [Accessed 24 Mar. 2018].
 Relationaldbdesign 2017. Three Schema Architecture Logical schema and physical
schema, user view. [online] Available at:
https://www.relationaldbdesign.com/relational-database-design/module3/three-
schema-architecture.php [Accessed 24 Mar. 2018].
 Senecac n.d. THE DATA HIERARCHY. [online] Available at:
https://cs.senecac.on.ca/~timothy.mckenna/SYS364/FILINTRO.htm [Accessed 24 Mar.
2018].
 Software Testing Class 2014. Difference between Black Box Testing and White Box
Testing. [online] Available at: http://www.softwaretestingclass.com/difference-
between-black-box-testing-and-white-box-testing/ [Accessed 24 Mar. 2018].
 SolutionDots 2016. What is Cloud ERP & How it Can Raise your Business Graph?
[online] Available at: https://solutiondots.com/blog/cloud-erp/ [Accessed 24 Mar.
2018].
 Soopertutorials 2009. Advantages and Disadvantages of Flat Database File System.
[online] Available at: http://www.soopertutorials.com/technology/databases/3297-
advantages-disadvantages-flat-database-file-system.html [Accessed 24 Mar. 2018].
 Teach-ict n.d. Relational databases and flat file databases. [online] Available at:
http://www.teach-ict.com/gcse_new/databases/flatfile_relational/miniweb/pg2.htm
[Accessed 24 Mar. 2018].
93

 THAKUR, D. 2018. What is Object Oriented Database (OODB)? Advantages and
Disadvantages of OODBMSS. [online] Ecomputernotes. Available at:
http://ecomputernotes.com/database-system/adv-database/object-oriented-
database-oodb [Accessed 24 Mar. 2018].
 Unn-isrd1. n.d. Normalisation. [online] Available at: http://unn-
isrd1.newnumyspace.co.uk/legacy/is701/content/is701s1wk11.php [Accessed 24 Mar.
2018].
 Viswanathan, P. 2017. What You Should Know Before Moving to the Cloud. [online]
Lifewire. Available at: https://www.lifewire.com/cloud-computing-explained-2373125
[Accessed 24 Mar. 2018].
 Walker, J. 2015. Top 5 data warehouses on the market today. [online] Monitis Blog.
Available at: http://www.monitis.com/blog/top-5-data-warehouses-on-the-market-
today/ [Accessed 24 Mar. 2018].
 FeedBurner, 2016.V-Model (Verification and Validation Model). [online] Software-
testing-tutorials-automation. Available at: http://www.software-testing-tutorials-
automation.com/2016/06/v-model-verification-and-validation.html [Accessed 24 Mar.
2018].
 Soltesz, D., n.d. The Advantages of a Relational Database Management System.
[online] Techwalla. Available at: https://www.techwalla.com/articles/the-advantages-
of-a-relational-database-management-system [Accessed 24 Mar. 2018].
 Reichwein, B., 2014. SWOT Analysis. [online] Betterevaluation. Available at:
http://www.betterevaluation.org/en/evaluation-options/swotanalysis [Accessed 24
Mar. 2018].
 Sen, P., 2012. Comparison between hierarchical model, network model and relational
model. [online] Newtutorial. Available at:
http://newtutorial2012.blogspot.in/2012/07/comparison-between-hierarchical-
model.html [Accessed 24 Mar. 2018].
 Carroll, L., 2016. Chapter 2 Data Model Database Systems: Design, Implementation,
and Management, Sixth Edition, Rob and Coronel. [online] Slideplayer. Available at:
http://slideplayer.com/slide/9018821/ [Accessed 24 Mar. 2018].
94

 Walker, R. 2015. 5.2.2 ERD and Normalisation. [online] Richwalkerske. Available at:
http://richwalkerske.co.uk/2015/07/12/5-2-2-erd-and-normalisation/ [Accessed 24
Mar. 2018].
 Pappas, C. 2017. 9 Tips To Create An Effective User Interface Design. [online] ELearning
Industry. Available at: https://elearningindustry.com/tips-create-effective-user-
interface-design [Accessed 24 Mar. 2018].
 Bailey, O. 2017. ERD: 19 / 1 / Entity-Relationship (ER) Modeling. ER Modeling is a top-
down approach to database design. Entity Relationship (ER) Diagram. [online]
Slideplayer. Available at: http://slideplayer.com/slide/10328234/ [Accessed 24 Mar.
2018].
 DBMS Internals, 2018. Hierarchical Data Model. Retrieved 24 March 2018, from
http://www.dbmsinternals.com/database-fundamentals/basic-architecture/
hierarchical-data-model/Chandana, 2017. Top-down Approach Vs. Bottom-up
Approach. [online] Simplilearn. Available at: https://www.simplilearn.com/top-down-
approach-vs-bottom-up-approach-article [Accessed 24 Mar. 2018].
95