Data Analysis and Design for Dominican College Course Management System

This report presents a comprehensive analysis and design of a relational database system for Dominican College's course management. It explores various data models, schemas, and database technologies, critically comparing their benefits and limitations. The report delves into the design and implementation of a relational database system, including ERD diagrams, normalization, and user interface enhancements. It further examines the use of manipulation and querying tools for extracting meaningful data, along with testing and documentation procedures to ensure system quality and user satisfaction.

Data Analysis and
Design
Student Name:
Student ID:

Contents
Introduction...............................................................................................................................6
LO1 Understand data models and database technologies........................................................7
1.1 Critically compare different data models and schemas...................................................7
1.2 Critically discuss the benefits and limitations of different database technologies.......12
1.3 Analyse different approaches to database design.........................................................14
LO2 Be able to design and implement relational database systems.......................................15
2.1 design a relational database system to meet a given requirement...............................15
2.2 Build a relational database system based on a prepared design...................................19
2.3 Apply a range of database tools and techniques to enhance the user interface..........23
LO3 Be able to use manipulation and querying tools..............................................................27
3.1 Explain the benefits of using manipulation and query tools in a relational database
system...................................................................................................................................27
3.2 Implement a query language into the relational database system that has been
implemented........................................................................................................................29
3.3. Critically evaluates how meaningful data has been extracted through the use of query
tools for the given case study...............................................................................................38
LO4 Be able to test and document relational database systems............................................42
4.1 critically reviews and tests the implemented relational database system....................42
4.2 Create documentation to support the implementation and testing of a relational
database system...................................................................................................................43
4.3 Create user documentation for a developed relational database system.....................47
4.4 Explain how verification and validation has been addressed........................................54
4.5 Explain how control mechanisms have been used........................................................55
Conclusion................................................................................................................................58
References................................................................................................................................59
1

List of Figures
Figure 1 Network Data Model....................................................................................................7
Figure 2 Hierarchal Data Model.................................................................................................8
Figure 3 Relational Data Model..................................................................................................9
Figure 4 Physical Schema.........................................................................................................10
Figure 5 Logical Schema...........................................................................................................11
Figure 6 Conceptual Schema....................................................................................................11
Figure 7 Relationship diagram.................................................................................................16
Figure 8 Conceptual Diagram...................................................................................................16
Figure 9 Logical Diagram..........................................................................................................17
Figure 10 Academic Manager Table.........................................................................................19
Figure 11 Academic Manager Datasheet view........................................................................19
Figure 12 Admin Table.............................................................................................................20
Figure 13 Admin Datasheet view.............................................................................................20
Figure 14 Campus Table...........................................................................................................20
Figure 15 Campus Datasheet view...........................................................................................20
Figure 16 Course Table.............................................................................................................20
Figure 17 Course Datasheet view............................................................................................21
Figure 18 Lecturer Table..........................................................................................................21
Figure 19 Lecturer Datasheet View..........................................................................................21
Figure 20 Student Table...........................................................................................................21
Figure 21 Student Datasheet View..........................................................................................22
Figure 22 Units Table...............................................................................................................22
Figure 23 Units Datasheet view...............................................................................................22
Figure 24Units Datasheet view................................................................................................23
Figure 25 Student Table...........................................................................................................23
Figure 26 Relationship..............................................................................................................24
Figure 27 Academic manager form..........................................................................................24
Figure 28Admin form...............................................................................................................25
2

Figure 29 Academic manager report.......................................................................................25
Figure 30 Admin report............................................................................................................26
Figure 31 Query1 design view..................................................................................................29
Figure 32 Query1 output..........................................................................................................29
Figure 33 Query1 Report view.................................................................................................30
Figure 34 Query2 design view..................................................................................................31
Figure 35 Query2 Output.........................................................................................................31
Figure 36 Query2 Report view.................................................................................................31
Figure 37 Query3 Design view.................................................................................................32
Figure 38 Query3 Output.........................................................................................................32
Figure 39 Query3 Report view.................................................................................................32
Figure 40 Query4 design view..................................................................................................34
Figure 41 Query4 output..........................................................................................................34
Figure 42 Query4 Report view.................................................................................................34
Figure 43 Query5 design view..................................................................................................35
Figure 44 Query5 Output.........................................................................................................35
Figure 45 Query5 Report view.................................................................................................36
Figure 46 Query6 Design view.................................................................................................37
Figure 47 Query6 output..........................................................................................................37
Figure 48 Query6 report view..................................................................................................37
Figure 49 Academic Manager Query.......................................................................................38
Figure 50 Output of Academic Manager Query.......................................................................38
Figure 51 admin query.............................................................................................................38
Figure 52 Output of Admin query............................................................................................39
Figure 53 Courses Query..........................................................................................................39
Figure 54 Output of Course Query...........................................................................................39
Figure 55 Lecturer Query.........................................................................................................40
Figure 56 Output of Lecture Query..........................................................................................40
Figure 57 Student Query..........................................................................................................41
Figure 58 Output of Student Query.........................................................................................41
Figure 59 Test 1........................................................................................................................44
Figure 60 test-2........................................................................................................................44
3

Figure 61 test-3........................................................................................................................44
Figure 62 test 4.........................................................................................................................45
Figure 63 test 5.........................................................................................................................45
Figure 64 test 6.........................................................................................................................46
Figure 65 Academic manager form..........................................................................................47
Figure 66 Academic-manager report.......................................................................................48
Figure 67 Admin Form..............................................................................................................48
Figure 68 Academic report.......................................................................................................49
Figure 69 Course Form.............................................................................................................49
Figure 70 Course Report..........................................................................................................50
Figure 71 Campus Report.........................................................................................................50
Figure 72 Campus Report.........................................................................................................51
Figure 73 Lecturer Form...........................................................................................................51
Figure 74 Lecturer Report........................................................................................................52
Figure 75 Student Form...........................................................................................................52
Figure 76 Student Report.........................................................................................................52
Figure 77 Units Form................................................................................................................53
Figure 78 Units Report.............................................................................................................53
Figure 79 Validation in Lecturer Table.....................................................................................54
Figure 80 Academic manager control mechanism..................................................................55
Figure 81 academic manager control mechanism...................................................................56
Figure 82 lecture table control mechanism.............................................................................57
4

List of Tables
Table 1 Benefits and Limitation of Different Technologies......................................................12
Table 2 First normal form.........................................................................................................17
Table 3 Second normal form....................................................................................................18
Table 4 Third normal form.......................................................................................................18
Table 5 feedback......................................................................................................................42
Table 6 Test cases.....................................................................................................................43
5

Introduction
System Analysis and Design is an entire procedure of given framework breaking down. This
report presents key issues of a relational database, data models with their advantages and
disadvantages and giving a best attainable answer for a given issue. It supports the
proposed framework with everyday future advancements. This report presents
development and designing of the database for Dominican College that is located in
northwest of new your city. In designing of database, Conceptual and logical ER is designed.
In development, tables, forms, query, and report are developed.
In this project, there are four sections in this whole project, in the first section, I will discuss
data models such as relational, hierarchal and network data models, data schema, their
approaches, data designs. In the second section, I will discuss the relational requirements,
user documentation, verification, and normalization. In the third section, I will discuss data
manipulation, query languages and, data maintenance. In the last or the fourth section, I will
make the test plans, feedback form, test cases, and user documentation.
6

LO1 Understand data models and database technologies
1.1 Critically compare different data models and schemas
A database exhibits the clever design of information, including the associations and flaws
that choose how data can be made more secure. Database models are built according to the
database principles and design requirements. Most data models can be addressed by
database graph.
Types of Data Models
Network Data Models
A database demonstrates the consistent structure of a data, including the requirements that
decide how data can be put away. Single database models are designed for considering the
views and ideas that have more extensive information. It can be addressed by running
database.
Figure 1 Network Data Model
Pros: -
 It mainly gives many to many relationships.
 It is easy to access and flexible compared to hierarchal model.
7

Cons: -
 In this whole database structure becomes very complex.
 If the structure changes to the database are very difficult.
Hierarchal Data Models
A hierarchal database is a prototype that uses a relationship type that is one to many. Tree
structure is used by data models that associate different components parts to one "parent",
"proprietor", or fundamental record.
Figure 2 Hierarchal Data Model
Pros: -
 It allows the addition and deletes in new information and data.
 It is easing to work.
Cons: -
 It can be slow while the search for information on the lower entity.
 It can only model one too many relationships.
Relational Data Models
The most unquestionably comprehended model, the social model sort's information into
tables, by and large, called relations, every single one of which contains areas and lines.
Each part records a normal for the substance that is esteem, postal locale, or birth date. A
trademark characteristic is picked as a basic key that can be specified to in various tables.
8

Each tuple that is section fuses data around an event of the substance being referred to a
representative. The model includes associations between those tables, including, one-to-
many, and many-to-one relations (Lucidchart, 2017).
Figure 3 Relational Data Model
Pros: -
 It is simple and easy to use for database users to understand.
 It also provides data accessing.
Cons: -
 It gives machine performance while using the relational database model.
 It is easy to use and design.
Data Schemas
A database design addresses the setup of all or part of a social database. It exists in visual
and virtual of conditions known as genuineness restrictions that speak to a database. These
formulas are imparted in a data definition, for instance, SQL. As a major part of a data word
reference, a database layout that relates to each other, including tables, sees, set away
systems, and the atmosphere is the farthest point from that point.
Physical Schema
It can be described as the diagram of a database at its physical level. In this level, it is
conveyed how data is secured in squares of limit. Physical data indicate addresses how will
the model be functioned in the database. A physical database show exhibits each table
9

structure, including fragment name, section data composes, portion restrictions,
fundamental key, remote key, and associations between tables.
Functionality of a physical data demonstrate include:
 For differentiating connection among tables foreign keys are used.
 Considering client necessities renormalization may occur.
 Physical contemplations may cause the physical information model to be
inconsistent.
 RDBMS is used for diverse the Physical information model. For instance, information
composes for a segment that is MySQL and SQL Server (1keydata, 2017).
Figure 4 Physical Schema
Logical Schema
It can be characterized as the outline of the database at its intelligent level. In this level, the
software engineers work. A genuine data show delineates the data in however much detail
as could be normal in the database.
Functionality of Logical Schema:
 Includes all elements and connections among them.
 All characteristics of every element are determined.
 For every substance, the essential key is indicated.
 At this level, Normalization happens (1keydata, 2017).
10

Figure 5 Logical Schema
Conceptual Schema
Calculated information differentiates the most abnormal connections among the
substances.
Functionality of Conceptual Schema:
 Vital elements and the connections among them are included.
 Quality could not be determined.
 Essential keys are not indicated (1keydata, 2017).
Figure 6 Conceptual Schema
11

1.2 Critically discuss the benefits and limitations of different database technologies
DBMS is the programming application that interacts with users and the database for
investigating information. A DBMS permits the alteration, creation, modification, and
organization of databases. The database structure is used for a gathering of records. This
information can be gotten to in those records through the DBMS. The DBMS is responsible
for the database's interior multifaceted nature in the application.
Benefits of the DBMS:
 Sharing of data in the organization is much better and improved.
 Data is much secured due to DBMS.
 Data integrity done is more effective.
 Data is easy to access.
 End-user productivity is increased.
 Data redundancy is minimized.
 Data inconsistency can be minimized.
Table 1 Benefits and Limitation of Different Technologies
Database Technologies Advantage Disadvantage
File-Based Database  It reduces the data
redundancy.
 It improves the data
security.
 It takes more time to
design.
 It is difficult to
design.
Relational Database  It is well established
and flexible.
 It is easy to
understand
 It is lack of support
for the complex base.
 It is more
complicated in real-
world
representations.
Multimedia Database  It is easy to use
 It makes work more
interesting
 It is not always easy
to configure.
 It is not compatible.
Object-Oriented Database  It has good scalability
 It is simple and
powerful data model
 It creates the
problem when
deleting all data in
bulk.
 It reduces its
flexibility.
Distributed Database  It can be more  It is more
12

available.
 It can be faster.
complicated to
manage.
 It has data integrity
exposure
Data Warehousing  It has fast
exploration.
 It is designed to
handle the very large
datasets.
 It can be costlier to
maintain.
 It is inflexible.
Web-Based (Cloud
Computing)
 It increases data
safety
 It improves
performance
 It has limited
features.
 It requires a constant
internet connection.
13

1.3 Analyse different approaches to database design
There are numerous ways to deal with the plan of a database as given beneath:
Bottom-up Database Approach:
This approach begins a major level of traits that are properties of the elements and
connections. It at that point consolidates or adds to these deliberations, which are
assembled into relations that speak to kinds of elements and connections between
elements. New relations among element writes might be included as the plan advances.
Top-down Database Approach:
This approach starts with the advancement of the information models that contains
abnormal state reflections. At that point, the progressive best down refinement is
connected to distinguish bring down level elements, connections, and the related qualities.
The Entity Relationship display is a case of best down approach and is more reasonable for
the outline of complex databases.
M1
I have applied the top-down approach to designing this database because it is an easy
approach. The top-down approach is more secure and easy to access.
14

LO2 Be able to design and implement relational database systems
2.1 design a relational database system to meet a given requirement
A relational database is the fundamental blueprint of information to be put away and in
social database information is put away in the lines and section that is the table
configuration. In the social database connections are produced among the forbidden
information. In the social database, we can perform different undertakings in a table like an
insert, control, Update, erasure and more capacities. The social database gives the
usefulness of the information to be put away.
A portion of the upsides of the social database is that it is anything but difficult to utilize,
gives simple access to information gives different approvals and make information more
secure (McFadyen, R., 2015).
ERD
A graphical representation of data is done using a diagram called entity-relationship diagram
of an object and their association to each other, ordinarily, use for registering as to the
association of information inside databases. They reflect the syntactic structure, with
substances as things and relationship as verbs.
Attributes: Every element is portrayed with a few traits. Qualities are the components which
demonstrate the nature of the substance. It is encased in overshadow.
Entities: The genuine element whose relationship is characterized is called elements. It
stores information. It is encased in a rectangle.
Relationships: It exists between different substances characterized by some essential key.
All elements have a connection among them. It can be of a type that is one to many,
numerous to many, numerous to one (Lucidchart, 2017).
15

Figure 7 Relationship diagram
Figure 8 Conceptual Diagram
16

Figure 9 Logical Diagram
First Normal Form (1NF): -
It is an organized approach of decomposing tables to remove redundancy and unwanted
character like insert, update, delete. It is a multi-process that put the table format, removes
all duplicate data from the relation tables.
Normalisation is mainly used for
1. Remove redundancy data.
2. Confirm the data dependency make sense.
Table 2 First normal form
Student Staff Campus
student_ID lecture_ID Camp_ID
S_name L_add Location
S_add L_name Course offer
S_email L_email Course_ID
S_contact Skills Course_name
A_manage
r ID
Course_fees
admin_ID Unit_ID
Level
Unit_name
17

Second Normal Form
 A database is in a second ordinary frame on the off chance that it fulfills the
accompanying conditions:
 It is necessary to be a first normal form for data to be in the second normal form
 A partial dependency is checked in this normal form.
Table 3 Second normal form
Student Staff Campus Unit
student_I
D
lecture_ID Camp_ID Unit_ID
S_name L_add Location Level
S_add L_name course offer Unit_name
S_email L_email Course_ID
S_contact Skills course_name
A_manager
ID
course_fees
admin_ID
Third Normal Form
 A Database is in a third ordinary frame on the off chance that it fulfills the
accompanying conditions:
 It is necessary to be a second normal form for data to be in the third normal form
 Transitive functional dependency is not allowed (1keydata, 2017).
Table 4 Third normal form
Student Lecturer Campus
student_ID lecture_ID Camp_ID
S_name L_address Location
S_address L_name course offer
S_email L_email Course
S_contact Skills Course_ID
Unit Academic course_name
unit_ID A_manager ID course_fees
Level Admin
Unit_nam
e
admin_ID
18

19

2.2 Build a relational database system based on a prepared design
Design View of Tables
Design view of the table represents the number of column in the table and its data type of
format like Number, text and more (Tutorialspoint, 2017).
Figure 10 Academic Manager Table
Figure 11 Academic Manager Datasheet view
20

Figure 12 Admin Table
Figure 13 Admin Datasheet view
Figure 14 Campus Table
Figure 15 Campus Datasheet view
Figure 16 Course Table
21

Figure 17 Course Datasheet view
Figure 18 Lecturer Table
Figure 19 Lecturer Datasheet View
Figure 20 Student Table
22

Figure 21 Student Datasheet View
Figure 22 Units Table
Figure 23 Units Datasheet view
23

2.3 Apply a range of database tools and techniques to enhance the user interface
There are seven major components of Microsoft Access: These components are
implemented in the database to enhance the user interface. The tables and forms are
designed to insert the data and store the data in the Dominican college database. The
database tool that is used to store data in the database, there is some database tool are
given below:
 Tables
Figure 24Units Datasheet view
Figure 25 Student Table
 Relationship: The relationship tool is present the relationship between the entities of
the Dominican college database.
24

Figure 26 Relationship
 Queries
 Form
The forms are designed for users to insert the data into the database:
Figure 27 Academic manager form
25

Figure 28Admin form
 Reports: The reports are designed to search specific data from the Dominican college
database.
Figure 29 Academic manager report
26

Figure 30 Admin report
 Macros
 Modules
Features of Ms- Access
 Perfect for clients.
 Less demanding than customer server database to use.
 Prepared layouts for standard clients to make and distribute information.
 Permits building Web databases easily.
 An easy to understand.
 Enables designers to make custom arrangements.
 Enables clients to Report View (Wong, H., 2001).
M2
I have successfully implemented and designed the database using MS-Access technique. I
have achieved the M2 successfully in task 2.2.
27

LO3 Be able to use manipulation and querying tools
3.1 Explain the benefits of using manipulation and query tools in a relational database
system
Data Manipulation: - It is data modification that includes updates, deletes and alters. It
demonstrates information to customers in a more critical way, considering the code in an
item program, page, or data planning described by a customer.
Benefits of Data Manipulation:
 Stored data can be modified with the data manipulation statements in the database.
 With the data manipulation, users are able to specify the required data.
 It has many flavors and the capabilities to show the relations between the vendors of
Dominican college database.
 It also provides the interaction between the human and database.
Query Language: - The utilization of databases is mind-boggling. They give a technique for
recouping records or parts of records and performing diverse checks already demonstrating
the results. The interface by which such controls are shown is known as the inquiry language
(Slamecka, 2017).
Benefits of Query language:
 With the query language, a large amount of data can be store and able to search the
specific data from the database.
 It manages the Dominican college database system without any standard code
requirements.
Benefits of query tool:
 By using the query tool, the user can easily store the data and search the data from
the Dominican college database.
 The developer can easily develop the Dominican college database with the design
the tables, forms, query, and report.
28

 Also implement the trigger, packages, security, tablespaces, and procedures to
provide the comprehensive information.
Aggregate Function: -
It is a function where the estimations of different columns are sorted as a contribution on
specific conditions to shape an estimation of more considerable size. It includes following:
 Average () = average of data is obtained.
 Count () = number of rows are obtained.
 Maximum () = largest value from data is obtained.
 Minimum () = minimum value from data is obtained.
 Sum () = total of data is obtained.
29

3.2 Implement a query language into the relational database system that has been
implemented.
Query1
Write a query so the College admin can view the list of Course Details and the unit details
offered by the college (Support.office, 2017).
Solution:
SELECT Admin.admin_ID, Course.course_ID, Course.course_name, Course.course_fees,
Units.unit_ID, Units.Level, Units.unit_name FROM (Course INNER JOIN Units ON Course.
{course_ID} = Units.{course_ID}) INNER JOIN Admin ON Course.{course_ID} = Admin.
{course_ID};
Figure 31 Query1 design view
30

Figure 32 Query1 output
Figure 33 Query1 Report view
Query 2
Write a query for the academic manager so he can see the Lecturers the individuals who
instruct different kinds of units of courses and the students assigned to the specific lecturer.
Solution:
SELECT {Acamedic manager}.amanager_ID, {Acamedic manager}.lecture_ID, {Acamedic
manager}.unit_ID, Student.student_ID, Student.S_name, Lecturer.L_name, Units.unit_name
FROM Units INNER JOIN ((Lecturer INNER JOIN Student ON Lecturer.{lecturer_ID} = Student.
{lecturer_ID}) INNER JOIN {Acamedic manager} ON Lecturer.{lecturer_ID} = {Acamedic
manager}.{lecture_ID}) ON Units.{unit_ID} = {Acamedic manager}.{unit_ID};
31

Figure 34 Query2 design view
Figure 35 Query2 Output
Figure 36 Query2 Report view
Query 3
Write a query for Students so they can view the particular course and its details like course
fees which they want to choose and proceed further.
Solution:
SELECT Student.S_name, Course.course_name, Course.course_fees FROM Course INNER
JOIN Student ON Course.{course_ID} = Student.{course_ID};
32

Figure 37 Query3 Design view
Figure 38 Query3 Output
33

Figure 39 Query3 Report view
Query 4
Write a query for College admin who should be able to view particular student details who
has registered in a specific course, units and the tutor who teaches the respective units.
Solution:
SELECT Admin.admin_ID, Student.S_name, Course.course_ID, Course.course_name,
Units.unit_name, Lecturer.L_name FROM Student INNER JOIN (((Course INNER JOIN
Lecturer ON Course.{course_ID} = Lecturer.{course_ID}) INNER JOIN Units ON Course.
{course_ID} = Units.{course_ID}) INNER JOIN Admin ON Course.{course_ID} = Admin.
{course_ID}) ON Student.{student_ID} = Admin.{student_ID};
34

Figure 40 Query4 design view
Figure 41 Query4 output
Figure 42 Query4 Report view
Query 5
35

Write a query for the academic manager should be able to see all the students who have
registered for the specific course.
Solution:
SELECT Student.S_name, Course.course_name FROM Course INNER JOIN Student ON
Course.{course_ID} = Student.{course_ID};
Figure 43 Query5 design view
Figure 44 Query5 Output
36

Figure 45 Query5 Report view
Query 6
Write a query for the lecturers who can view the course details, the units and the details of
other lecturers teach the respective unit.
Solution:
SELECT Lecturer.lecturer_ID, Lecturer.L_name, Course.course_name, Course.course_fees,
Units.unit_name, Units.Level FROM (Course INNER JOIN Units ON Course.{course_ID} =
Units.{course_ID}) INNER JOIN Lecturer ON Course.{course_ID} = Lecturer.{course_ID};
37

Figure 46 Query6 Design view
Figure 47 Query6 output
Figure 48 Query6 report view
38

3.3. Critically evaluates how meaningful data has been extracted through the use of query
tools for the given case study.
To search the specific data, implement the query tool. With query tool or a query language
the user implement their specific requirement and get the output according to it. Here some
implemented queries are given with their outputs. These queries are implemented in MS
Access tool.
Implemented queries to search specific data form the Dominican College database:
Figure 49 Academic Manager Query
Figure 50 Output of Academic Manager Query
In this query, we can see the academic manager id, lecture id, unit id, student name, last
name and unit name. By getting this information the academic manager sees which lecturer
teaches different units and the students allocated for the lecturer.
Figure 51 admin query
39

Figure 52 Output of Admin query
In this query, we can see the admin id, course id, course name, course fees, unit od, level
and unit name, by this information the admin can see all the details about the courses
name, unit name, and course fees.
Figure 53 Courses Query
Figure 54 Output of Course Query
40

In this query, we can see admin id, student name, course name, unit name, last name. by
getting this output the college admin can see that which student register in the course and
which lecturer teach those courses.
Figure 55 Lecturer Query
Figure 56 Output of Lecture Query
In this query we can see, lecturer id, last name, course name, course fees, unit name, and
level. By getting this output, in this the lecturer can see this query, and able to list down the
details and units.
41

Figure 57 Student Query
Figure 58 Output of Student Query
In this query, we can see student name, course name, course fees. In this output, the
student can able to see the course name and their fees.
M3
I have used the query tools for extracting the correct information from the relational
database; I have used the SQL queries for extracting the meaningful data (Pantziarka, P.,
2000)
42

LO4 Be able to test and document relational database systems
4.1 critically reviews and tests the implemented relational database system
To test the Dominican College database, take feedback from the users. This designed
database is easy to use and learn. To learn the database, Dominican College has to provide
the training to their staff. To store the data in the database, the tables are designed and to
insert the data in the database, the forms are designed. The queries and report are designed
to find the specific data from the database. The user feedbacks are designed to test the
Dominican College database according to the given requirements:
Table 5 feedback
Question User 1
Is the database being easy to use? Yes
How often would you use this database? Always
Are you satisfied with this database? Yes
Is the database security to use? Yes
Please give the overall rating to this
database?
9 (out of 10)
Question User 2
Is the database being easy to use? Not much
How often would you use this database? Randomly
Are you satisfied with this database? Don’t know
Is the database security to use? Yes
Please give the overall rating to this
database?
7 (out of 10)
Question User 3
Is the database being easy to use? Yes
How often would you use this database? Sometimes
Are you satisfied with this database? Yes
Is the database security to use? Yes
Please give the overall rating to this
database?
8 (out of 10)
43

4.2 Create documentation to support the implementation and testing of a relational
database system.
In this section, test cases are presented to test the implemented Dominican College
database:
Table 6 Test cases
Sr. No. Test Plan Expected
Outcome
Actual Outcome Remark
1. Value enter in it
should be numeric
If not, the
numeric error
should occur
Error occurred. Successfully
implemented
2 Value enter should
be of currency format
If a value other
than currency
format is
entered error
should occur.
Error occurred. Successfully
implemented
3 Campus id value
should be number
format
If the value
entered is not
numeric error
should occur.
Error occurred. Successfully
implemented.
4 If Id is left null The id cannot be
null; hence error
should occur.
Error occurred Successfully
implemented.
5 The id cannot be
same for two or more
entries
If Id is duplicate
the error should
occur.
Error occurred Successfully
implemented.
6 The value entered
should be incorrect
format
If Value entered
is in the wrong
format, the
error should
occur.
Error occurred Successfully
implemented.
44

Figure 59 Test 1
Figure 60 test-2
Figure 61 test-3
45

Figure 62 test 4
Figure 63 test 5
46

Figure 64 test 6
47

4.3 Create user documentation for a developed relational database system
User Documentation: -
This is required from the client perspective to comprehend the database. In this, each
related data with respect to database tasks like inquiry, refresh, embed and erase the
information in the database. It removes tables information that needs to be removed, it is
required to select a question. So, clients are any individual who can access database. So, the
plan perspective of structures appears for every table of course management database.
(Techscribe, 2009).
Figure 65 Academic manager form
48
Details of
Academic
manager

Figure 66 Academic-manager report
Figure 67 Admin Form
49
Details of
admin

Figure 68 Academic report
Figure 69 Course Form
50
Details of
Course

Figure 70 Course Report
Figure 71 Campus Report
51
Details of
Campus

Figure 72 Campus Report
Figure 73 Lecturer Form
52
Details of
Lecturer

Figure 74 Lecturer Report
Figure 75 Student Form
Figure 76 Student Report
53
Details of
Student
Details of Units

Figure 77 Units Form
Figure 78 Units Report
54

4.4 Explain how verification and validation has been addressed
Verification
In this method different and huge amount of data is checked and the inconsistency of data
is verified. In data verification accuracy of data is also checked. It helps in finding that the
data transferred from one computer to another is same or is being changed. Proofreading is
the type of data verification. It is the verification that guarantees security of data.
Validation
It is done on the data for checking the quality of the data to check the data is useful and
meaningful or not. The validation is done with the help of data dictionary. Validation
guarantees correctness, fitness, consistency and various other factors of data (Gigl, 2017).
Figure 79 Validation in Lecturer Table
In this table I have applied validation rules ‘is not null’, it means that the table column is
unique, and the user must have to fill this column (Anon, 2017).
55
Validation
applied

4.5 Explain how control mechanisms have been used.
In this section, the control mechanisms are presented in database via screenshots. The
control mechanisms are implemented in Dominican database to ensure the security,
integrity, and availability of data. There are various types of control mechanism to
implement in the database: DAC (Discretionary Access Control), MAC (Mandatory Access
control), and RBAC (Role based access control).
Figure 80 Academic manager control mechanism
In the above table, the field size should be in long integer type, indexed should be not in
duplicate because field is primary key and text-align should be in general.
56

Figure 81 academic manager control mechanism
In the table, the field size should be in long integer type, indexing can be duplicate because
the field is not a primary key and text should be generally aligned.
57

Figure 82 lecture table control mechanism
In the above table, the field size should be in character format of value 255, indexing can be
duplicate because the field is not a primary key and the control on IME mode is null
(Techmahindra, 2017).
58

Conclusion
In this whole project I have discussed how the data analysis and design, there are four
section in the project, I have discussed data models, data schema, approaches, designs,
normalisation, verification and validation, data manipulation, query languages, data
maintenance such as insert, delete, update, Control Mechanism, Relational Requirement,
User Documentation, in this project I have made the ERD diagram, test cases, Feedback
form, and Recommendation. I have successfully designed and implemented the course
management database for the DOMINICAN COLLEGE using MS-Access. I have also designed
the different queries for extracting meaningful data from the database.
59

References
 1keydata 2017. Physical Data Model. [Online] 1keydata. Available at:
https://www.1keydata.com/datawarehousing/physical-data-model.html [Accessed
10 Mar. 2018].
 1keydata 2017. Third Normal Form (3NF) - Database Normalization. [Online]
1keydata. Available at: https://www.1keydata.com/database-normalization/third-
normal-form-3nf.php [Accessed 10 Mar. 2018].
 1keydata 2018. Conceptual Data Model. [Online] 1keydata. Available at:
https://www.1keydata.com/datawarehousing/conceptual-data-model.html
[Accessed 10 Mar. 2018].
 1keydata. 2017. Logical Data Model. [Online] Available at
https://www.1keydata.com/datawarehousing/logical-data-model.html [Accessed 10
Mar. 2018].
 Lucidchart 2017. What is a Database Model. [O] Lucidchart. Available at:
https://www.lucidchart.com/pages/database-diagram/database-models [Accessed
10 Mar. 2018].
 Lucidchart 2017. What is an Entity Relationship Diagram. [Online] Lucidchart.
Available at: https://www.lucidchart.com/pages/er-diagrams [Accessed 10 Mar.
2018].
 Slamecka, V. 2017. Information processing. [Online] Encyclopedia Britannica.
Available at: https://www.britannica.com/technology/information-
processing#ref212132 [Accessed 10 Mar. 2018].
 Techmahindra 2017. Types of Access Control Mechanisms. [Online] Techmahindra.
Available at:
https://www.techmahindra.com/sites/blogs/types_of_access_control_mechanisms.
aspx [Accessed 10 Mar. 2018].
 Anon, 2017. What is Database Testing, How to do it and write test cases?. [Online]
Available at: http://www.techbeamers.com/database-testing/ (edited) [Accessed 10
Mar. 2018].
60

 Gigl 2017. Data validation & verification – Greenspace Information for Greater
London. [Online] Gigl. Available at: http://www.gigl.org.uk/data-validation-
verification/ [Accessed 10 Mar. 2018].
 Techopedia, 2017. What is a Logical Data Model (LDM)? - Definition from
Techopedia. [Online] Techopedia.com. Available
at:https://www.techopedia.com/definition/30599/logical-data-model-ldm [Accessed
10 Mar. 2018].
 Wong, H., 2001. Database Design Methodologies. [Online] Cs.cityu.edu.hk. Available
at: http://www.cs.cityu.edu.hk/~helena/cs34622000B/DBDesign.pdf [Accessed 10
Mar. 2018].
 McFadyen, R. ,2015. Relational Databases and Microsoft Access. 2nd ed. [ebook]
Canada, pp.9-50. Available at:
http://www.acs.uwinnipeg.ca/rmcfadyen/CreativeCommons/Relational
%20Databases%20and%20Microsoft%20Access%20V2.0.pdf [Accessed 10 Mar.
2018].
 Support.office, 2017. Create a simple select query. [Online] Support.office. Available
at: https://support.office.com/en-us/article/Create-a-simple-select-query-de8b1c8d-
14e9-4b25-8e22-70888d54de59 [Accessed 10 Mar. 2018].
 Tutorialspoint, 2017. MS Access Create Tables. [Online] www.tutorialspoint.com.
Available at:
https://www.tutorialspoint.com/ms_access/ms_access_create_tables.htm
[Accessed 10 Mar. 2017].
 Thakur, D. n.d. What is DBMS? Advantages and Disadvantages of DBMS.. [Online]
Ecomputernotes. Available at: http://ecomputernotes.com/fundamental/what-is-a-
database/advantages-and-disadvantages-of-dbms [Accessed 10 Mar. 2018].
 Access, 2015. How to Design a Query in Microsoft Access - Access Programmers.
[Online] Access-programmers.com. Available at:
https://access-programmers.com/how-to-design-a-query [Accessed 10 Mar. 2018].
61