Relational Database System: Design, Implementation, and Testing

This report provides a comprehensive analysis and design of a relational database system, detailing various approaches to database creation and different database technologies. It covers the design and modeling of entities, attributes, and relationships within the database, specifically for Dominican College using MS Access. The report includes user documentation on various database elements such as tables in design and datasheet view, user interaction through forms and reports, query processing, and verification and validation through forms, dashboards, panels, and query reports. Furthermore, it discusses database testing methodologies, ER diagrams, normalization processes, implementation of SQL queries, and control mechanisms to ensure data integrity and security. The document also presents screenshots and explanations for user guidance, covering aspects like creating queries and reports, validation processes, and dashboard functionalities, offering a complete overview of the database development lifecycle.

Data Analysis and Design

Table of 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...............................7
1.3 Analyze different approaches to database design...............................................................................9
LO2 Be able to design and implement relational database systems...........................................................10
2.1 Design a relational database system to meet a given requirement....................................................10
ER DIAGRAM..................................................................................................................................10
NORMALIZATION..........................................................................................................................11
2.2 Build a relational database system based on a prepared design........................................................12
Tables Design View...........................................................................................................................12
Tables Datasheet View......................................................................................................................14
2.3 Apply a range of database tools and techniques to enhance the user interface.................................15
Forms.................................................................................................................................................15
Buttons..............................................................................................................................................18
...........................................................................................................................................................18
Reports..............................................................................................................................................18
Relationship.......................................................................................................................................21
Queries..............................................................................................................................................23
LO3 Be able to use manipulation and querying tools................................................................................24
3.1 Explain the benefits of using manipulation and query tools in a relational database system............24
Query Language................................................................................................................................24
Data Manipulation:............................................................................................................................24
3.2 implement a query language into the relational database system.....................................................27
Query 1..............................................................................................................................................27
Query 2..............................................................................................................................................28
Query 3..............................................................................................................................................30
Query 4..............................................................................................................................................32
Query 5..............................................................................................................................................33
Query 6..............................................................................................................................................34
Query 7..............................................................................................................................................35
Query 8..............................................................................................................................................37
3.3 Critically evaluate how meaningful data has been extracted through the use of query tools............39

LO4 Be able to test and document relational database systems.................................................................45
4.1 Critically review and test a relational database system....................................................................45
Questionnaire.....................................................................................................................................45
Testing strategies...............................................................................................................................46
4.2 Create documentation to support the implementation and testing of a relational database system...46
4.3 create user documentation for a developed relational database system............................................49
Screenshots of the dashboard.............................................................................................................49
Screenshots of all the panels..............................................................................................................51
How to create Queries?......................................................................................................................54
How to create Reports?......................................................................................................................54
4.4 Explain how verification and validation has been addressed...........................................................54
Phases of V- Model...........................................................................................................................54
Screenshots of the Validation done....................................................................................................55
4.5 explain how control mechanisms have been used............................................................................56
Conclusion.................................................................................................................................................58
References.................................................................................................................................................59
Appendix:..................................................................................................................................................60
List of Tables
Table 1- 1st normalization.........................................................................................................................11
Table 2- 2nd normalization........................................................................................................................11
Table 3- Questionnaire..............................................................................................................................45
Table 4- Test Case Documentation............................................................................................................46
List of Figures
Figure 1- ER Diagram...............................................................................................................................10
Figure 2- Student Design View.................................................................................................................12
Figure 3- Lecturer Design View................................................................................................................12
Figure 4- Course Design View..................................................................................................................13
Figure 5- Unit Design View......................................................................................................................13
Figure 6- Academic manager Design View...............................................................................................13
Figure 7- College Admin Design View.....................................................................................................13
Figure 8- Student Datasheet View.............................................................................................................14
Figure 9- Lecturer Datasheet View............................................................................................................14

Figure 10- Unit Datasheet View................................................................................................................14
Figure 11- Academic Manager Datasheet View........................................................................................14
Figure 12- Course Datasheet View............................................................................................................15
Figure 13- College Admin Datasheet View...............................................................................................15
Figure 14- Academic Manager Form View...............................................................................................16
Figure 15- College Admin Form View......................................................................................................16
Figure 16- Course Form View...................................................................................................................16
Figure 17- Lecturer Form View.................................................................................................................17
Figure 18- Student Form View..................................................................................................................17
Figure 19- Unit Form View.......................................................................................................................18
Figure 20- Academic Manager Table Report view....................................................................................19
Figure 21- College Admin Table Report view...........................................................................................19
Figure 22- Course Table Report view........................................................................................................20
Figure 23- Lecturer Table Report view......................................................................................................20
Figure 24- Student Table Report view.......................................................................................................20
Figure 25- Unit Table Report view............................................................................................................21
Figure 26- Relationship.............................................................................................................................22
Figure 27- Query Datasheet View.............................................................................................................23
Figure 28- Unit Table Data........................................................................................................................25
Figure 29- Count Output............................................................................................................................25
Figure 30- Course Table data....................................................................................................................26
Figure 31- Sum Output..............................................................................................................................26
Figure 32- MIN Output..............................................................................................................................26
Figure 33- MAX Output............................................................................................................................27
Figure 34- Query 1 SQL view...................................................................................................................27
Figure 35- Query 1 Design View...............................................................................................................27
Figure 36- Query 1 Result.........................................................................................................................28
Figure 37- Query 2 Part A SQL view........................................................................................................28
Figure 38- Query 2 Part A Design View....................................................................................................29
Figure 39- Query 2 Part A Result..............................................................................................................29
Figure 40- Query 2 Part B SQL View........................................................................................................29
Figure 41- Query 2 Part B Design View....................................................................................................30
Figure 42-Query 2 Part B Result...............................................................................................................30
Figure 43- Query 3 SQL............................................................................................................................31
Figure 44- Query 3 Design View...............................................................................................................31
Figure 45- Query 3 Result.........................................................................................................................32
Figure 46- Query 4 SQL............................................................................................................................32
Figure 47-Query 4 Design View................................................................................................................32
Figure 48- Query 4 Result.........................................................................................................................33
Figure 49- Query 5 SQL............................................................................................................................33
Figure 50-Query 5 Design View................................................................................................................33
Figure 51- Query 5 Result.........................................................................................................................34
Figure 52- Query 6 SQL............................................................................................................................34
Figure 53-Query 6 Design View................................................................................................................35
Figure 54- Query 6 Result.........................................................................................................................35
Figure 55- Query 7 SQL View..................................................................................................................35
Figure 56-Query 7 Design View................................................................................................................36

Figure 57- Query 7 Result.........................................................................................................................36
Figure 58- Academic Manager form to add, amend and delete.................................................................36
Figure 59- Query 8 SQL View..................................................................................................................37
Figure 60-Query 8 Design View................................................................................................................37
Figure 61- Query 8 Result.........................................................................................................................38
Figure 62- College Admin form to add, amend and delete........................................................................38
Figure 63- Query 1 Report.........................................................................................................................39
Figure 64- Query 2 Part A report...............................................................................................................40
Figure 65- Query 2 Part B report...............................................................................................................41
Figure 66- Query 3 report..........................................................................................................................41
Figure 67- Query 4 Report.........................................................................................................................42
Figure 68- Query 5 Report.........................................................................................................................42
Figure 69- Query 6 Report.........................................................................................................................43
Figure 70- Query 7 report..........................................................................................................................43
Figure 71- Query 8 report..........................................................................................................................44
Figure 72- Foreign Key Validation............................................................................................................47
Figure 73- Name Validation......................................................................................................................47
Figure 74- Contact Number Validation.....................................................................................................48
Figure 75- Email address Validation.........................................................................................................48
Figure 76- Academic_Manager Dashboard...............................................................................................49
Figure 77- College Admin Dashboard.......................................................................................................49
Figure 78- Course dashboard.....................................................................................................................49
Figure 79- Lecturer Dashboard..................................................................................................................50
Figure 80- Student dashboard....................................................................................................................50
Figure 81- Unit Dashboard........................................................................................................................51
Figure 82- Academic_manager panel........................................................................................................51
Figure 83- CollegeAdmin panel................................................................................................................52
Figure 84- Course panel............................................................................................................................52
Figure 85- Lecturer panel..........................................................................................................................52
Figure 86- Student panel............................................................................................................................53
Figure 87- Unit panel.................................................................................................................................53
Figure 88- Tutor Name Validation............................................................................................................55
Figure 89- Contact Number Validation.....................................................................................................55
Figure 90- Email Address Validation........................................................................................................55
Figure 91- Timing Validation....................................................................................................................55
Figure 92- Control Mechanism in Course Table........................................................................................56
Figure 93- Control Mechanism in Lecturer Table......................................................................................56
Figure 94- Control Mechanism in Student Table.......................................................................................56
Figure 95-Control Mechanism in Unit Table.............................................................................................56
Figure 96- Referential Integrity.................................................................................................................57

Introduction
The report will discuss on the various approaches of creating the database. The different technologies that
can be used for creating the database will be discussed. The database will be designed and the modelling
of the entities, attributes and relationships will be done. The database for the Dominican college will be
build on MS- Access. The report will present all the user documentation on different elements of the
database. The report will discuss on tables in design and datasheet view, the user interaction of the
database through the forms and reports, query processing, verification and validation through forms,
dashboards, panels and reports of the queries. The report will also discuss about the testing of the
database.

LO1 Understand data models and database technologies
1.1 critically compare different data models and schemas
A database display or an information demonstrate is graphical portrayal of how information is overseen in
a database. It denes the capacity, administration and association of information inside the database. Most
normally utilized database models are:
• Hierarchical Model
• Relational Model
• Network Model
Hierarchical Model: In this kind of model, one parent (additionally called root) can have at least one
youngsters. Control streams from guardians to kid’s elements.
Relational Model: in this sort of structure information is composed in social table
Network Model: In this model every element is associated with in excess of one element, shaping a
diagram sort of structure. Every element can be gotten to from in excess of one way.
1.2 critically discuss the benefits and limitations of different database technologies
MS access is the database developed the Microsoft for creation and designing of the database. No matter
it’s the small-scale business or the large-scale business.
Advantages of the MS access:
• Very easy to install no programming knowledge required.
• Can be easily implemented with many languages such as php, java, c# etc.
• It can store data in bulk and can save a lot of money as it comes in the package provided by the
Microsoft office.
• It can store up to 2GB of data at a time.
• It also provides the multi user support action
• It can be easily imported with any hardcore knowledge.
Limitations:
• The data limitation binds to 2GB beyond this cannot be handled by the MS Access.

• It stores all the data such as forms, reports queries and the data all in one file.
• Only 10 users can assess it at a time. Here user do bind as only 10 multiple users can use it at one
time.
Oracle is a database administration framework produced by SUN Microsystems. One of the real preferred
standpoint and in addition burden of oracle is that it is helpful just for expansive associations with
complex database prerequisites.
Subsequently focal points of Oracle are:
• Oracle bolsters expansive databases.
• It plays out every exchange independently and the resultant isn't noticeable to other exchange until the
point that it is finished, which makes database secure. It makes utilization of triggers and cursors making
programming simple.
• Can handle databases of any size and number of records.
Hindrance of Oracle:
• Only valuable for overseeing expansive applications can't be utilized with little associations.
• Price and equipment cost required to run it are high.
Microsoft Structured Programming Language or SQL is most broadly utilized question dialect with parcel
numerous points of interest:
• It is broadly utilized for overseeing social databases
• It is intelligent, simple to learn and get it
• It is versatile and can keep running on any sort of gadget like, pc's, workstations, centralized servers
and cell phones.
• It is dependable and has vigorous security instrument
• It can take a shot at customer server engineering model, by interfacing front end application with back
end database
• It can bolster question-based programming and can coordinate with larger amount programming
dialects like java by utilizing and API called JDBC (Java database availability)

1.3 Analyze different approaches to database design
Top Down Approach: This approach begins from a general element and moves to more particular
substance. We need to begin with a thought of what is required for the framework, its bland prerequisites
and after that move downwards towards the client's needs and necessities. For utilizing top down
approach, the examiner more likely than not point by point understanding about the framework, its
working and its assets. Database is checked for its qualities, and after that interface is composed as needs
be.
Bottom Up Approach: This approach is switch of best down approach, in a way that it begins from
particular and moves towards nonexclusive thought. The framework examiner, works upon the interface,
checking all screens, reports and structures and afterward moves downwards to check what ought to be
put away in the database. To start with the interface is made and after that database is produced in like
manner. A case of this approach can be specialists or doctors, who analyze the particular manifestations,
at that point construe the general infection causing those side effects.
Centralized Approach: This model or approach is valuable when there are tolerably less number of factors
in the association, in like manner a basic database will be developed. One framework or database director
can characterize the framework prerequisites, outline database diagram or reasonable plan, characterize
information imperatives and framework process and check that the created submit to association
objective.
Decentralized Approach: This approach influences utilization of complex database to structure and
number of factors. It is for the most part honed in vast associations. This is additionally valuable when the
framework prerequisite or issue is spread over numerous operational destinations. To deal with this
circumstance, a group of database framework engineers need to chip away at plan necessities, limitations,
sees, approvals, interface, different offices and so on. Diverse groups deal with various subsets or
modules in close coordination, at that point their work is incorporated together to accomplish the
framework objective.

LO2 Be able to design and implement relational database systems
2.1 Design a relational database system to meet a given requirement
The database has been designed through ER modelling.
ER DIAGRAM
Figure 1- ER Diagram
The diagram exhibits the following relationships:
 from student to collegeadmin- one-to-many relationship.
 from course to collegeadmin- one-to-many relationship.
 from course to student- one-to-one relationship.
 from lecturer to Academic_Manager- one-to-many relationship.
 from unit to Academic_Manager- one-to-many relationship.
 from course to lecturer- one-to-one relationship.
 from course to unit- one-to-many relationship

NORMALIZATION
1st normal form
Table 1- 1st normalization
Student Lecturer Course
StudentNo TutorID CourseID
Name TutorName CourseName
Contact Contact_No CourseFee
Address Address CampusName
Email_Address E-Mail_Address Unit
Course_Preferred Course_Preferred Level
CourseID CourseID StudentNo
2nd normal form
Here, the partial dependencies are removed.
Table 2- 2nd normalization
Student Lecturer Course Unit Academic_Manag
er
CollegeAdmi
n
StudentNo TutorID CourseID UnitID TutorID StudentNo
Name TutorName CourseName Unit UnitID CourseID
Contact Contact_No CourseFee Level Approval Approval
Address Address CampusNam
e
CourseI
D
Email_Address E-Mail_Address
Course_PreferredI
D
Course_PreferredI
D
3rd normal form
There are no transitive dependencies. Hence, it is already in 3rd normal form.

2.2 Build a relational database system based on a prepared design
Tables Design View
Student- This table is used to show the details of the students who have registered for the different
courses.
Figure 2- Student Design View
Lecturer: This table is used to show the details of the lecturers who have applied for teaching the different
courses.
Figure 3- Lecturer Design View

Course: This table is used to display the details about the various courses offered by the college.
Figure 4- Course Design View
Unit: The unit table displays the various units available in the courses offered by the college.
Figure 5- Unit Design View
Academic Manager: The academic manager table contains the details of the unit allocation to the
lecturers.
Figure 6- Academic manager Design View
College Admin: The college admin table contains the details of the course allocation to the students.
Figure 7- College Admin Design View

Tables Datasheet View
Figure 8- Student Datasheet View
Figure 9- Lecturer Datasheet View
Figure 10- Unit Datasheet View
Figure 11- Academic Manager Datasheet View

Figure 12- Course Datasheet View
Figure 13- College Admin Datasheet View
2.3 Apply a range of database tools and techniques to enhance the user interface
The user interface of the database can be enhanced by using forms and reports. Forms are designed in the
database for easy user interaction and filling up of the details. The forms are designed for the interaction
of user with the system.
Forms
Forms are used to fill in the input by the users. The forms contain data validations so that the user can
enter data in the correct format.
Figure 14- Academic Manager Form View

Figure 15- College Admin Form View
Figure 16- Course Form View
Figure 17- Lecturer Form View

Figure 18- Student Form View
Figure 19- Unit Form View
Buttons
The buttons are used for easy interaction and form input.

Reports
The reports are used for easy viewing of the data. The user can sort the data the way he wants to see the
data, i.e., ascending or descending. The reports facilitate the easy processing of data and identification of
patterns.
Figure 20- Academic Manager Table Report view
Add Record Button

Figure 21- College Admin Table Report view
Figure 22- Course Table Report view
Figure 23- Lecturer Table Report view

Figure 24- Student Table Report view
Figure 25- Unit Table Report view
Relationship
The relational schema of the database helps to represent the relation between the entities. It helps in
identification of the referential integrity constraints in the database.

Figure 26- Relationship
Relation of Student table to CollegeAdmin table: The StudentNo is the primary key in the Student table
which acts as a foreign key in the CollegeAdmin table.
Relation of Course table to CollegeAdmin table: The CourseID is the primary key in the Course table
which acts as a foreign key in the CollegeAdmin table.
Relation of Course table to Student table: The CourseID is the primary key in the Course table which acts
as a foreign key in the Student table as Course_PrefferedID.
Relation of Course table to Lecturer table: The CourseID is the primary key in the Course table which
acts as a foreign key in the Lecturer table as Course_PreferredID.
Relation of Lecturer table to Academic_Manager table: The TutorID is the primary key in the Lecturer
table which acts as a foreign key in the Academic_Manager table.
Relation of Unit table to Academic_Manager table: The UnitID is the primary key in the Unit table which
acts as a foreign key in the Academic_Manager table.
Relation of Course table to Unit table: The CourseID is the primary key in the Course table which acts as
a foreign key in the Unit table.

Queries
The queries are used for extracting particular results from the database.
Figure 27- Query Datasheet View

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
Query Language
Query Language is a simple language which is used for the extraction of specific data from the set of
structured data. The query is a simple language which is used for the identification of the patterns. Query
writing is not easy but there are certain commands which make the query writing easier. With the help of
query, the diversified data can be simplified into one.
Benefits of Query Language
The benefits of the query language are as follows:
 The input data is so diverse and vast and contains a lot of information. By implementing the
procedures, tablespaces, packages, security and triggers, the meaningful data can be obtained
from the set of data.
 A large amount of data can be fed at one time and results can be obtained simultaneously. The
output that is obtained is processed very quickly and thus, it takes very less time to present the
result.
 The data of Dominican College can be managed without any coding or special knowledge
requirement.
 The database can be developed very easily and the data mining becomes very easy when we
apply the queries.
Data Manipulation:
Data manipulation is referred to as modification of the data so that the database operations such as
insertion, alteration and deleting could be made easy. The data that is represented to the customer should
be of an advanced level and properly identify the objectives of the database. Thus, data manipulation
makes it easier for proper presentation of the data.
Benefits of Data Manipulation
The benefits of Data Manipulation are as follows:
 The data can be modified as per the user’s wish and also the type of information that the user
wants to display.
 The manipulation statements modify the data very easily.

 After the connection to the database is made, the user can interact with the database using theses
manipulation statements very easily.
 From a wide information that is sparse and meaningless, the manipulation language can be used
to mould it up to a more specific and meaningful information.
 The relationships in the databases are identified more easily using manipulative languages.
Functions that can be used in query language
Count(): This function is used to count the number of rows in the table.
For Example, in the Units table, if we have to count the total number of units, we can apply the query as:
SELECT count(UnitID) as Total_Number_of_Units
FROM Unit;
The output of the above query will be as follows:
Figure 28- Unit Table Data
Figure 29- Count Output

Sum(): It is used to find out the sum of the values of the particular column.
For example, if we want to find out the total of the course fees in the Course_Fee Column, we can write
the query as:
SELECT SUM(Course.CourseFee) As Total_of_three_courses
FROM Course;
Figure 30- Course Table data
The output that will be obtained is:
Figure 31- Sum Output
MIN(): It is used to find out the minimum value.
For example, if we want to display the minimum course fee, we can write the query as:
SELECT MIN(CourseFee) as Minimum_Course_Fee
FROM Course;
The output will be:
Figure 32- MIN Output
MAX(): It is used to find out the maximum value.
For example, if we want to display the maximum course fee, we can write the query as:
SELECT MAX (CourseFee) as Maximum_Course_Fee
FROM Course;
The output will be:

Figure 33- MAX Output
3.2 implement a query language into the relational database system
Here, all the queries given in the case study will be implemented.
Query 1
Requirement: College admin needs to list down Course Details and the unit details offered by the college.
SELECT Unit.UnitID, Unit.Unit, Course.CourseName, Course.CampusName
FROM Course INNER JOIN Unit ON Course.CourseID = Unit.CourseID;
Figure 34- Query 1 SQL view
Figure 35- Query 1 Design View

Figure 36- Query 1 Result
Query 2
Requirement: The academic manager needs to list down Lecturers those who teach various types of units
of courses and the learners assigned for a particular lecturer.
Part A
SELECT Unit.Unit, Lecturer.TutorName, Academic_Manager.Approval
FROM Unit INNER JOIN (Lecturer INNER JOIN Academic_Manager ON Lecturer.TutorID =
Academic_Manager.TutorID) ON Unit.UnitID = Academic_Manager.UnitID
WHERE (((Academic_Manager.Approval)=Yes));
Figure 37- Query 2 Part A SQL view

Figure 38- Query 2 Part A Design View
Figure 39- Query 2 Part A Result
Part B
SELECT Student.Name, Lecturer.TutorName
FROM ((((Course INNER JOIN CollegeAdmin ON Course.CourseID = CollegeAdmin.CourseID)
INNER JOIN Lecturer ON Course.CourseID = Lecturer.Course_PreferredID) INNER JOIN
Academic_Manager ON Lecturer.TutorID = Academic_Manager.TutorID) INNER JOIN Student ON
(Course.CourseID = Student.Course_PrefferedID) AND (Student.StudentNo =
CollegeAdmin.StudentNo)) INNER JOIN Unit ON (Course.CourseID = Unit.CourseID) AND
(Unit.UnitID = Academic_Manager.UnitID);
Figure 40- Query 2 Part B SQL View

Figure 41- Query 2 Part B Design View
Figure 42-Query 2 Part B Result
Query 3
Requirement: College admin should be able to view a particular student who has registered for a
particular course, units and the lecturers who teach those units.
SELECT Student.Name, Course.CourseName, Unit.Unit, Lecturer.TutorName

FROM ((((Course INNER JOIN CollegeAdmin ON Course.CourseID = CollegeAdmin.CourseID)
INNER JOIN Lecturer ON Course.CourseID = Lecturer.Course_PreferredID) INNER JOIN Student ON
(Student.StudentNo = CollegeAdmin.StudentNo) AND (Course.CourseID =
Student.Course_PrefferedID)) INNER JOIN Unit ON Course.CourseID = Unit.CourseID) INNER JOIN
Academic_Manager ON (Unit.UnitID = Academic_Manager.UnitID) AND (Lecturer.TutorID =
Academic_Manager.TutorID);
Figure 43- Query 3 SQL
Figure 44- Query 3 Design View

Figure 45- Query 3 Result
Query 4
Requirement: Students should be able to view the particular course, which they prefer to follow and also
the course fees.
SELECT Student.Name, Course.CourseName, Course.CourseFee
FROM Course INNER JOIN Student ON Course.CourseID = Student.Course_PrefferedID;
Figure 46- Query 4 SQL
Figure 47-Query 4 Design View

Figure 48- Query 4 Result
Query 5
Requirement: The academic manager should be able to list down all the learners who registered for a
particular course.
SELECT Course.CourseName, Student.Name
FROM Course INNER JOIN Student ON Course.CourseID = Student.Course_PrefferedID
ORDER BY Course.CourseName;
Figure 49- Query 5 SQL
Figure 50-Query 5 Design View

Figure 51- Query 5 Result
Query 6
Requirement: The lecturers should be able to list down course details and the units, the details of other
lecturers who teaches those units.
SELECT Lecturer.TutorName, Lecturer.Contact_No, Lecturer.Address, Lecturer.[E-Mail Address],
Unit.Unit, Course.CourseName, Course.CampusName
FROM ((Course INNER JOIN Lecturer ON Course.CourseID = Lecturer.Course_PreferredID) INNER
JOIN Unit ON Course.CourseID = Unit.CourseID) INNER JOIN Academic_Manager ON (Unit.UnitID =
Academic_Manager.UnitID) AND (Lecturer.TutorID = Academic_Manager.TutorID)
WHERE (((Academic_Manager.Approval)=Yes));
Figure 52- Query 6 SQL

Figure 53-Query 6 Design View
Figure 54- Query 6 Result
Query 7
Requirement: As per the request of Lecturers, academic manger should be able to add, amend and delete
unit allocation of lecturers.
SELECT Lecturer.TutorName, Course.CourseName AS Course_Preferred, Unit.Unit AS Unit_Preferred,
Academic_Manager.Approval
FROM ((Course INNER JOIN Lecturer ON Course.CourseID = Lecturer.Course_PreferredID) INNER
JOIN Unit ON Course.CourseID = Unit.CourseID) INNER JOIN Academic_Manager ON (Unit.UnitID =
Academic_Manager.UnitID) AND (Lecturer.TutorID = Academic_Manager.TutorID);
Figure 55- Query 7 SQL View

Figure 56-Query 7 Design View
Figure 57- Query 7 Result
The academic manager can amend, add and delete the allocation through the forms.
Figure 58- Academic Manager form to add, amend and delete

Query 8
Requirement: College admin should be able to assign courses for students as well as amend, delete course
allocation details.
SELECT Student.Name, Course.CourseName AS Course_Preferred, CollegeAdmin.Approval
FROM (Course INNER JOIN CollegeAdmin ON Course.CourseID = CollegeAdmin.CourseID) INNER
JOIN Student ON (Student.StudentNo = CollegeAdmin.StudentNo) AND (Course.CourseID =
Student.Course_PrefferedID);
Figure 59- Query 8 SQL View
Figure 60-Query 8 Design View

Figure 61- Query 8 Result
The College admin can amend, add and delete the allocation through the forms.
Figure 62- College Admin form to add, amend and delete

3.3 Critically evaluate how meaningful data has been extracted through the use of
query tools
The justification of the queries can be given by generating the reports of the queries.
Figure 63- Query 1 Report
This report displays the units and related course details. Here the user gets to know about all the units and
courses.

Figure 64- Query 2 Part A report
This report displays the units and the allotted lecturers.

Figure 65- Query 2 Part B report
This report displays the learners who are assigned to the lecturers.
Figure 66- Query 3 report
This report displays the students who have registered for the courses and the units related along with their
lecturers.

Figure 67- Query 4 Report
This report displays the courses preferred by students along with the course fee.
Figure 68- Query 5 Report
This report displays the courses and the students who have registered for the course.

Figure 69- Query 6 Report
This report displays the lecturers who teach the particular units along with the course details.
Figure 70- Query 7 report

Figure 71- Query 8 report

LO4 Be able to test and document relational database systems
4.1 Critically review and test a relational database system
The database can be reviewed through techniques such as taking feedback from the users. Dominican
college can review the database by taking feedback from different users. For this, a questionnaire can be
prepared and then the database can be reviewed. The rating has been done out of 5.
Questionnaire
Table 3- Questionnaire
SNO Question User 1 User 2 User 3
1 Has the database has correctly served the purpose of
Dominican college?
4 5 5
2 Has is the design of the tables? 4 3 4
3 How is the user interface of the database? 5 4 3
4 Is the relationship between the tables justified? 5 5 5
5 Are the forms working properly? 5 5 5
The reviews that can be prepared on the basis of the questionnaire are as follows:
Informal Review: The database can be informally reviewed by the people who belong from the non-
technical background. According to this, the database fulfills all the requirements.
Formal Review: The database can be formally reviewed on the basis of the atomicity of the values,
objectivity and the productivity of the database.
Technical Review: The database can be technically reviewed on the basis of the normalization of the
tables, relationships among the tables, forms, validation and query processing.

Testing strategies
The testing strategies that can be implemented are as follows:
Black Box testing: Black box testing is done for the structure of the database. In the database, we can see
that the database consists of tables, forms, queries, relationship and reports. The database is properly
structured.
Manual testing: The manual testing can be done for the input and the output. For example, in the forms,
we can manually enter the values and check for the output. We see that every form is working and data is
properly inserted.
White Box testing: The white box testing can be done for the query optimization and proper execution.
Performance testing: The performance testing can be done in the cases of execution of the queries.
4.2 Create documentation to support the implementation and testing of a relational
database system
Table 4- Test Case Documentation
SNO Test case plan Expected result Actual result comment
1 Foreign key validation If there are no
records for the
primary key, no
record can be
inserted with the
same as a foreign
key and error will
be generated.
Error is generated. Implemented
successfully.
2 The name of the lecturer or
student can’t consist of a
number.
An error message
is generated if
number is
inputted.
Error is generated. Implemented
successfully.
3 The contact of a person can be
only 10 digits.
An error message
is generated if it
exceeds.
Error is generated. Implemented
successfully.
4 The email should be in a valid
format.
An error message
should be
generated.
Error is generated. Implemented
successfully.

Screenshots
Figure 72- Foreign Key Validation
Figure 73- Name Validation

Figure 74- Contact Number Validation
Figure 75- Email address Validation

4.3 create user documentation for a developed relational database system
Screenshots of the dashboard
Figure 76- Academic_Manager Dashboard
Figure 77- College Admin Dashboard
Figure 78- Course dashboard

Figure 79- Lecturer Dashboard
Figure 80- Student dashboard

Figure 81- Unit Dashboard
Screenshots of all the panels
Figure 82- Academic_manager panel

Figure 83- CollegeAdmin panel
Figure 84- Course panel
Figure 85- Lecturer panel

Figure 86- Student panel
Figure 87- Unit panel

How to create Queries?
 Go to Create.
 Go to Query Design.
 Select the tables for which you want to create query.
 Select the columns which you want to show.
 Save the query and run.
How to create Reports?
 Select the query or table for which you want to create report.
 Go to create.
 Go to report.
 Design the report.
 Save and run.
4.4 Explain how verification and validation has been addressed
The V- Model has been used for the verification and validation.
Phases of V- Model
The different phases of the V- Model are as follows:
Requirement Analysis: This phase facilitates the communication between the client and the organization.
Here the client’s needs undergo validation.
System Design: The database design is done suiting the system as well as client’s needs. The system test
cases are also defined.
Architectural Design: The design should cover all the modules according to the functionalities.
Module Design: The unit testing is performed in this phase so that the faults could be checked.
Coding Phase: The coding is done in this phase based on the database design.

Screenshots of the Validation done
Figure 88- Tutor Name Validation
Figure 89- Contact Number Validation
Figure 90- Email Address Validation
Figure 91- Timing Validation

4.5 explain how control mechanisms have been used.
The control mechanisms that have been used are data integrity and referential integrity.
Figure 92- Control Mechanism in Course Table
Figure 93- Control Mechanism in Lecturer Table
Figure 94- Control Mechanism in Student Table
Figure 95-Control Mechanism in Unit Table

Figure 96- Referential Integrity

Conclusion
In this report, we discussed about the different database schemas. Both top down and bottom up approach
of modelling the database were discussed. The different approaches were explained according to the
design of the database. The database was designed using the modelling of the database, identification of
entities, attributes and relationships. The database was build and the user documentation was provided for
the tables, forms, queries and the reports. The validation and verification were also discussed.

References
 Foote, K 2016, A Review of Different Database Types: Relational versus Non-Relational –
DATAVERSITY, [online] DATAVERSITY, Available at: http://www.dataversity.net/review-pros-
cons-different-databases-relational-versus-non-relational/ [Accessed 23 Mar. 2018].
 Köhler, W 2015, T-SQL SSRS: SQL Theory: Top-down Vs Bottom-up Database Design, [online]
Masud-ahmed-ssrs.blogspot, Available at: http://masud-ahmed-ssrs.blogspot.in/2015/02/sql-theory-
top-down-vs-bottom-up.html [Accessed 23 Mar. 2018].
 Morgan, J 2015, Task 1 - The uses of and tools/techniques used in databases, [online] Prezi,
Available at: https://prezi.com/os2f36fve_af/task-1-the-uses-of-and-toolstechniques-used-in-
databases/ [Accessed 23 Mar. 2018].
 Tutorials Point 2018, SDLC V-Model, [online] Available at:
https://www.tutorialspoint.com/sdlc/sdlc_v_model.htm [Accessed 20 May 2018].
 wikiHow 2018, How to Create Action Queries in Microsoft Access, [online] Available at:
https://www.wikihow.com/Create-Action-Queries-in-Microsoft-Access [Accessed 20 May 2018].
 Trica, A n.d, Filtered - The Importance of Documentation in Software Development, [online] Filtered,
Available at: https://filtered.com/blog/post/project-management/the-importance-of-documentation-in-
software-development [Accessed 23 Mar. 2018].
 Creately 2017, Database Modeling Tutorial Covering Basics of Database Design, [online] Creately
Blog, Available at: https://creately.com/blog/diagrams/database-modeling-basics/ [Accessed 23 Mar.
2018].
 Woodward, I 2008, Data validation & verification – Greenspace Information for Greater London,
[online] Gigl. Available at: http://www.gigl.org.uk/data-validation-verification/ [Accessed 23 Mar.
2018].

Appendix: