Pearson Higher Nationals in

Computing
Unit 04: Database Design & Development
Assignment 01

1
 Higher Nationals
Internal verification of assessment decisions – BTEC (RQF)
INTERNAL VERIFICATION – ASSESSMENT DECISIONS

Programme title BTEC HND in Computing

Assessor Internal Verifier

Unit 04: Database Design & Development
Unit(s)
Database Solution for Polly Pipe
Assignment title

Student’s name
List which assessment criteria Pass Merit Distinction
the Assessor has awarded.

INTERNAL VERIFIER CHECKLIST

Do the assessment criteria awarded match

those shown in the assignment brief? Y/N

Is the Pass/Merit/Distinction grade awarded

justified by the assessor’s comments on the Y/N
student work?
Has the work been assessed
Y/N
accurately?
Is the feedback to the student:
Give details:
• Constructive? Y/N
• Linked to relevant assessment criteria? Y/N
• Identifying opportunities for Y/N
improved performance?
• Agreeing actions? Y/N
Does the assessment decision need
Y/N
amending?

Assessor signature Date

Internal Verifier signature Date

Programme Leader signature (if required)
Date

Confirm action completed

Remedial action taken
Give details:

Assessor signature Date

Internal Verifier
Date
signature
Programme Leader
Date
signature (if required)

2
Higher Nationals - Summative Assignment Feedback Form
Student Name/ID

Unit Title Unit 04: Database Design & Development

Assignment Number 1 Assessor

Date Received
Submission Date
1st submission
Date Received 2nd
Re-submission Date
submission
Assessor Feedback:
LO1 Use an appropriate design tool to design a relational database system for a substantial problem

Pass, Merit & Distinction P1 M1 D1

Descripts

LO2 Develop a fully functional relational database system, based on an existing system design
Pass, Merit & Distinction P2 P3 M2 M3 D2
Descripts

LO3 Test the system against user and system requirements.

Pass, Merit & Distinction P4 M4 D2
Descripts

LO4 Produce technical and user documentation.

Pass, Merit & Distinction P5 M5 D3
Descripts

Grade: Assessor Signature: Date:

Resubmission Feedback:

Grade: Assessor Signature: Date:

Internal Verifier’s Comments:

Signature & Date:

* Please note that grade decisions are provisional. They are only confirmed once internal and external moderation has taken place and
grades decisions have been agreed at the assessment board.

3
Assignment Feedback
Formative Feedback: Assessor to Student

Action Plan

Summative feedback

Feedback: Student to Assessor

Assessor Date
signature

Student Date
signature

4
General Guidelines

1. A Cover page or title page – You should always attach a title page to your assignment. Use
previous page as your cover sheet and make sure all the details are accurately filled.
2. Attach this brief as the first section of your assignment.
3. All the assignments should be prepared using a word processing software.
4. All the assignments should be printed on A4 sized papers. Use single side printing.
5. Allow 1” for top, bottom , right margins and 1.25” for the left margin of each page.

Word Processing Rules

1. The font size should be 12 point, and should be in the style of Time New Roman.
2. Use 1.5 line spacing. Left justify all paragraphs.
3. Ensure that all the headings are consistent in terms of the font size and font style.
4. Use footer function in the word processor to insert Your Name, Subject, Assignment No,
and Page Number on each page. This is useful if individual sheets become detached for any
reason.
5. Use word processing application spell check and grammar check function to help editing
your assignment.

Important Points:

1. It is strictly prohibited to use textboxes to add texts in the assignments, except for the
compulsory information. eg: Figures, tables of comparison etc. Adding text boxes in the body
except for the before mentioned compulsory information will result in rejection of your
work.
2. Carefully check the hand in date and the instructions given in the assignment. Late
submissions will not be accepted.
3. Ensure that you give yourself enough time to complete the assignment by the due date.
4. Excuses of any nature will not be accepted for failure to hand in the work on time.
5. You must take responsibility for managing your own time effectively.
6. If you are unable to hand in your assignment on time and have valid reasons such as illness,
you may apply (in writing) for an extension.
7. Failure to achieve at least PASS criteria will result in a REFERRAL grade .
8. Non-submission of work without valid reasons will lead to an automatic RE FERRAL. You will
then be asked to complete an alternative assignment.

5
9. If you use other people’s work or ideas in your assignment, reference them properly using
HARVARD referencing system to avoid plagiarism. You have to provide both in-text citation
and a reference list.
10. If you are proven to be guilty of plagiarism or any academic misconduct, your grade could be
reduced to A REFERRAL or at worst you could be expelled from the course

6
 Student Declaration

I hereby, declare that I know what plagiarism entails, namely to use another’s work and to present
it as my own without attributing the sources in the correct form. I further understand what it
means to copy another’s work.

1. I know that plagiarism is a punishable offence because it constitutes theft.

2. I understand the plagiarism and copying policy of Edexcel UK.
3. I know what the consequences will be if I plagiarise or copy another’s work in any of the
assignments for this program.
4. I declare therefore that all work presented by me for every aspect of my program, will be my
own, and where I have made use of another’s work, I will attribute the source in the correct
way.
5. I acknowledge that the attachment of this document signed or not, constitutes a binding
agreement between myself and Pearson, UK.
6. I understand that my assignment will not be considered as submitted if this document is not
attached to the assignment.

Student’s Signature: Date:

(Provide E-mail ID) (Provide Submission Date)

7
Higher National Diploma in Computing
Assignment Brief
Student Name /ID Number

Unit Number and Title Unit 4: Database Design & Development

Academic Year

Unit Tutor

Assignment Title Data base system for Polly Pipe

Issue Date

Submission Date

IV Name & Date

Submission format

Part 1: The submission should be in the form of an individual written report written in a
concise, formal business style using single spacing and font size 12. You are required to make
use of headings, paragraphs and subsections as appropriate, and all work must be supported
with research and referenced using Harvard referencing system. Please also provide in-text
citation and bibliography using Harvard referencing system. The recommended word limit is
3,000–3,500 words, although you will not be penalised for exceeding the total word limit.
Part 2: The submission should be in the form of a fully functional relational database system
demonstrated to the Tutor; and an individual written report (please see details in Part 1
above).
Part 3: The submission should be in the form of a witness statement of the testing completed
by the Tutor; technical documentation; and a written report (please see details in Part 1
above).
Unit Learning Outcomes:

LO1 Use an appropriate design tool to design a relational database system for a substantial
problem.
LO2 Develop a fully functional relational database system, based on an existing system
design.
LO3 Test the system against user and system requirements.
LO4 Produce technical and user documentation.
Assignment Brief and Guidance:

8
Assignment brief

Installation Installation Installation Customer Equipment Types of Staff Period of

ID Type Name and Required Staff
Address assignm
ent

Polly Pipe is a water sports provider and installer based in Braintree, England. They need
you to design and implement a database that meets the data requirements. These
necessities are defined in this scenario and below are samples of the paper records that the
Polly Pipe preserves.

Polly Pipe is focused in placing aquariums at business customers. Customers can request
several installations, but each installation is tailor-made for a specific customer. Facilities
are classified by type. One or more employees are assigned to each facility. Because these
facilities are often very large, they can include carpenters and masons as well as water
installers. The facilities use equipment such as aquariums, air pumps and thermostats.
There can be multiple computers in a facility.

Below are examples of paper records that Polly Pipe currently maintains.

Staff Management Record

Staff Number Name Type
SHA1 Dave Clark Plumber
SHA8 John Smith Installation Manager
SHA2 Freddy Davies Aquatics installer
SHA11 McCloud Aquatics installer
SHA23 Satpal Singh Plumber
SHA66 Winstn Kodogo Aquatics installer
SHA55 Alison Smith Brick Layer

Equipment Type Table

Type Equipment
Tanks 20 gallon tank, 50 gallon tank, 100
gallon tank, 200 gallon tank
Thermostats Standard, Super
Air Pumps Standard, Super
Filters Air driven, Undergravel

9
 234 Freshwater Oak Lee A. 2 air 1 x Carpenter From 1st
sun
Tropical House, 17 pumps 1 x Aquatics Septemb
Wroxton 200 gallons installer er 2012
Road, fish tank 1 x Electrician
Hertfordsh 1x
ire, H5 667 standard
thermostat
654 Freshwater Bayliss Sally 2 air 5x 1st June
Cold House, Dench pumps Carpenters 2005 –
Orange 200 gallons 1x 1st June
Street, fish tank Installation 2011
Kent, K7 Large Manager
988 Gravel Bag 1 x Aquatics
2x installer
standard 1 x Plumber
thermostat 3 x Labourers
s
767 Marine Eaglestone Perry 2 x 200 10 x From
Castle, Vanderru gallons fish Carpenters 30th June
Eaglestone ne tanks 2x 2012
, Kent 500 Wood Installation
panels Manager
1 x Aquatics
installer
1 x Plumber
3 x Labourers
943 Marine 23 Eric 2 air No staff required
Sackville Mackinto pumps
Street, sh 200 gallons
Wilts. W55 fish tank
1x
standard
thermostat
157 Freshwater Humbertso Perry 2 air 1 x Aquatics 1st
Tropical n Castle, Vanderru pumps installer Septemb
Kent, K8 ne 400 gallons er 2005
fish tank – 1st
3x Septemb
standard er 2012
thermostat
Instillation Management Form

Activity 1
1.1. Identify the user and system requirements to design a database for the above

10
 scenario and design a relational database system using conceptual design (ER Model)
by including identifiers (primary Key) of entities and cardinalities, participations of
relationships. Convert the ER Model into logical database design using relational
database model including primary keys foreign keys and referential Integrities. It
should contain at least five interrelated tables. Check whether the provided logical
design is normalised. If not, normalize the database by removing the anomalies.

(Note:-It is allowed to have your own assumptions and related attributes within the scope of the case
study given)

1.2. Design set of simple interfaces to input and output for the above scenario using
Wireframe or any interface-designing tool. Evaluate the effectiveness of the given design
(ERD and Logical design) in terms of the identified user and system requirements .

Activity 2
Activity 2.1
a. Develop a relational database system according to the ER diagram you have
created (Use SQL DDL statements). Provide evidence of the use of a suitable IDE to
create a simple interface to insert, update and delete data in the database.
Implement proper security mechanisms in the developed database.
Evaluate the database solution developed and its effectiveness with relevant to
the user and system requirements identified, system security mechanisms (EX: -
User groups, access permissions) and the maintenance of the database.
Activity 2.2
a. Explain the usage of DML with below mentioned queries by giving at least one
single example per each case from the developed database. Assess the usage of the
below SQL statements with the examples from the developed database to prove
that the data extracted through them are meaningful and relevant to the given
scenario.
Select/ Where / Update / Between / In / Group by / Order by / Having

Activity 3

11
Activity 3.1
Provide a suitable test plan to test the system against user and system requirements.
provide relevant test cases for the database you have implemented. Assess how the
selected test data can be used to improve the effectiveness of testing.
Note:- Learner needs to give expected results in a tabular format and screenshots of the actual results with
the conclusion

Activity 3.2
Get independent feedback on your database solution from the non-technical users and
some developers (use surveys, questioners, interviews or any other feedback collecting
method) and make recommendations and suggestions for improvements in a separate
conclusion/recommendations section.

Activity 4

Produce a technical documentation and a user guide for the developed database system.
Suitable diagrams diagrams (Use case diagram, class diagram, flow charts, DFD level 0 and
1) should be included in the technical documentation to show data movement in the
system.
Assess the developed database by suggesting future enhancements to ensure the
effectiveness of the system.

12
Grading Criteria Achieved Feedback

LO1 Use an appropriate design tool to design a relational

database system for a substantial problem

P1 Design a relational database system using appropriate

design tools and techniques, containing at least four
interrelated tables, with clear statements of user and
system requirements.
M1 Produce a comprehensive design for a fully functional
system that includes interface and output designs, data
validations and data normalization.

D1 Evaluate the effectiveness of the design in relation to

user and system requirements.

LO2 Develop a fully functional relational database system,

13
14
based on an existing system design

P2 Develop the database system with evidence of user

interface, output, and data validations, and querying across
multiple tables.

P3 Implement a query language into the relational

database system

M2 Implement a fully functional database system that

includes system security and database maintenance.

M3 Assess whether meaningful data has been extracted

using query tools to produce appropriate management
information.

LO3 Test the systems against user and system requirements

15
P4 Test the system against user and system requirements.

M4 Assess the effectiveness of the testing, including an

explanation of the choice of test data used.
LO2 & LO3
D2 Evaluate the effectiveness of the database solution in
relation to user and system requirements, and suggest
improvements.
LO4 Produce technical and user documentation

P5 Produce technical and user documentation.

M5 Produce technical and user documentation for a fully

functional system, including diagrams showing movement
of data through the system, and flowcharts describing how
the system works.

D3 Evaluate the database in terms of improvements

needed to ensure the continued effectiveness of the
system.

16
ACKNOWLEDGEMENT
I am proud that I took this assignment. Each and every learning outcomes and lectures improved
my knowledge on database. First and foremost I would like to thank [Link], lecturer at the
ESOFT Metro Campus, who helped me to understand the scenario and get a clear idea about it. I
would also like to thank the entire lecturers Esoft Gampaha branch, my friends and others who
helped me to complete the assignment successfully within the deadline. Finally, a special thanks to
my parents and god for their support, patience and encouragement they gave me to complete this
assignment successfully.

Thank you
adsadadfadf

1
Contents
Activity 1 ........................................................................................................................................... 7

1.1. Designing a Relational Database System ............................................................................ 8

1.1.1. The User and the System Requirements of the Polly Pipe System .............................. 8

1.1.2. Designing the ER Diagram .......................................................................................... 9

1.1.3. Designing the Relational Database Model ................................................................. 12

1.2. Designing a Fully Functional System ............................................................................... 15

1.2.1. Normalization ............................................................................................................ 15

1.2.2. Interfaces of the Polly Pipe System ........................................................................... 18

1.2.3. Validation Mechanisms ............................................................................................. 23

1.3. ERD vs User and System Requirements ........................................................................... 24

Activity 2 ......................................................................................................................................... 25

2.1. Developing The Database System..................................................................................... 25

2.1.1. SQL DDL Statements of the Polly Pipe System ........................................................ 25

2.1.2. Evidence of Insert, Update and Delete Queries of the System in Visual Studio ....... 28

2.1.3. Implementation of Validation Methods ..................................................................... 33

2.1.4. Example for Join Query ............................................................................................. 34

2.2. Implementing Query Language into the Relational Database System .............................. 35

2.2.1. Usage of DML ........................................................................................................... 35

2.3. System Security and Database Maintenance ..................................................................... 36

2.3.1. Security Mechanisms ................................................................................................. 36

2.4. Meaningful extraction of data ........................................................................................... 42

2.4.1. Usage of SQL Statement ............................................................................................ 42

Activity 3 ......................................................................................................................................... 44

3.1. Testing ............................................................................................................................... 44

3.1.1. Test plan ..................................................................................................................... 44

3.1.2. Test Plan of Polly Pipe System .................................................................................. 46

2
 3.1.3. Test cases ................................................................................................................... 48

3.2................................................................................................................................................. 50

3.2.1. Effectiveness of Testing ............................................................................................. 50

3.3. Evaluating the Effectiveness of the Database Solution ..................................................... 51

3.3.1. Evaluation from the Feedbacks .................................................................................. 51

Activity 4 ......................................................................................................................................... 54

4.1. Technical and User Documentation .................................................................................. 54

4.1.1. User Guide ................................................................................................................. 54

4.1.2. Technical Guide ......................................................................................................... 56

4.2. Technical Documentation for A Fully Functional System................................................ 57

4.2.1. Diagrams .................................................................................................................... 57

4.3 Ensuring Continued Effectiveness of Database System ................................................... 60

4.3.1. Future improvements ................................................................................................. 60

Self-Criticism .................................................................................................................................. 61

References ....................................................................................................................................... 63

Gantt Chart .................................................................................................................................... 64

3
Table of Figures
Figure 1:Polly Pipe ER Diagram ..................................................................................................... 11
Figure 2:Referential Integrity Diagram ............................................................................................ 14
Figure 3:Login Interface .................................................................................................................. 18
Figure 4:Main Form Interface .......................................................................................................... 18
Figure 5:CUSTOMER Interface ...................................................................................................... 19
Figure 6:EMPLOYEE Interface ...................................................................................................... 19
Figure 7: Installation Interface ......................................................................................................... 20
Figure 8: Equipment Interface ......................................................................................................... 20
Figure 9: PAYMENT _UNIT Interface ........................................................................................... 21
Figure 10: Installation Type Interface .............................................................................................. 21
Figure 11: EMPLOYEE _INSTALLATION Interface.................................................................... 22
Figure 12: EQUIPMENT _INSTALLATION Interface .................................................................. 22
Figure 13:SQL DDL Statement for CUSTOMER table ................................................................. 25
Figure 14:SQL DDL Statement for EMPLOYEE table................................................................... 25
Figure 15:SQL DDL Statement for EQUIPMENT table ................................................................ 26
Figure 16:SQL DDL Statement for INSTALLATION table ........................................................... 26
Figure 17SQL DDL Statement for INSTTALLATION table ......................................................... 27
Figure 18:SQL DDL Statement for PAYMENT table..................................................................... 27
Figure 21:Insertion C# Code ............................................................................................................ 28
Figure 22:Insert Data GUI ............................................................................................................... 28
Figure 23:Insert Data Table ............................................................................................................. 29
Figure 24:View data after inserting ................................................................................................. 29
Figure 25:Update data C# code........................................................................................................ 29
Figure 26:Update data GUI .............................................................................................................. 30
Figure 27:Update data table ............................................................................................................. 30
Figure 28:View data after updating ................................................................................................. 30
Figure 29:Delete data C# code ......................................................................................................... 31
Figure 30:Delete data GUI ............................................................................................................... 31
Figure 31:Delete data table .............................................................................................................. 32
Figure 32:View data after deleting................................................................................................... 32
Figure 33:Range Check .................................................................................................................... 33
Figure 34:Presence Check................................................................................................................ 33

4
Figure 35:Drop Down Box .............................................................................................................. 33
Figure 36:Type Check ...................................................................................................................... 34
Figure 37:Join query output ............................................................................................................. 34
Figure 38: SELECT command RESULTS ...................................................................................... 35
Figure 39:Data table before UPDATE ............................................................................................. 35
Figure 40:Data table after UPDATE................................................................................................ 35
Figure 41:Data table before DELETE ............................................................................................. 36
Figure 42:Data table after DELETE ................................................................................................ 36
Figure 43:Granting level permission step 1 ..................................................................................... 37
Figure 44::Granting level permission step 2 .................................................................................... 38
Figure 45:Granting level permission step 3 ..................................................................................... 39
Figure 46::Granting level permission step 4 .................................................................................... 39
Figure 47::Granting level permission step 5 .................................................................................... 40
Figure 48::Granting level permission final code ............................................................................. 40
Figure 49:Login interface ................................................................................................................ 41
Figure 50:Group By ......................................................................................................................... 42
Figure 51:Order By .......................................................................................................................... 42
Figure 52:Having ............................................................................................................................. 43
Figure 53:Between ........................................................................................................................... 43
Figure 54:Where .............................................................................................................................. 43
Figure 55:ACID Diagram ................................................................................................................ 45
Figure 56:Database test steps ........................................................................................................... 45
Figure 57:SELECTION test ............................................................................................................. 48
Figure 58:DELETION test ............................................................................................................... 48
Figure 59:UPDATING test .............................................................................................................. 48
Figure 60:INSERTION test ............................................................................................................. 49
Figure 61:Range check test .............................................................................................................. 49
Figure 62:Type check test ................................................................................................................ 49
Figure 63Duplicate primary key check test ..................................................................................... 50
Figure 64:Filled feedback form (evidence) ...................................................................................... 52
Figure 65:Non-technical user feedback questioner most selected answers ..................................... 53
Figure 66:Insert user guide .............................................................................................................. 54
Figure 67:Delete user guide ............................................................................................................. 55
Figure 68:Delete user guide ............................................................................................................. 55
5
Figure 69:Update user guide ............................................................................................................ 56
Figure 70:Use Case Diagram ........................................................................................................... 57
Figure 71:DFD Level 00 .................................................................................................................. 58
Figure 72:DFD Level 01 .................................................................................................................. 58
Figure 73:Flow Chart ....................................................................................................................... 59

Table of tables
Table 1:Primary Keys of Polly Pipe System .................................................................................... 12
Table 2:Foreign Keys of Polly Pipe System .................................................................................... 13
Table 3:Un-Normalized EMPLOYEE Table ................................................................................... 15
Table 4:EMPLOYEE table in 1NF Form ........................................................................................ 16
Table 5:EMPLOYEE Table in 2NF Form ....................................................................................... 17
Table 6:TRANSPORT_UNIT Table in 2NF Form ......................................................................... 17
Table 7:DEPOT Table in 3NF Form ............................................................................................... 17
Table 7:DEPOT Table in 3NF Form ............................................................................................... 17
Table 8:TRANSPORT_UNIT Table in 3NF Form ......................................................................... 17
Table 9:Validation Mechanisms ...................................................................................................... 24
Table 10:Recommendations and conclusions .................................................................................. 54

6
Activity 1
Introduction

Polly Pipe is a water sports provider and installer based in Braintree, England which has grown to
a level. Currently they are using a file system to maintain their day to day Installation and records
where they face many problems. Now they have requested to develop an automated system to
handle their day to day operational activities to meet their customer‟s demands. Following are
some of the risks/problems they currently face while using the file system.
1. Data redundancy
2. Data inconsistency
3. Complexity on data sharing
4. Unexpected errors during concurrent access to data
5. Too much time on searching a data
6. Lack of data integrity
7. Difficult to edit an old data
Database

A database is a structured collection of data that can be readily accessed, maintained, and updated.
(Rouse, 2017)

Database Management System (DBMS)

A database management system is a collection of applications used to store and retrieve data. A
database management system (DBMS) is a collection of interconnected data and a set of tools for
storing and accessing that data in an efficient and effective manner. (SINGH, 2016)

Why to Use DBMS

1. To build software applications in a shorter period of time.

2. Data independence and effective data usage

7
 3. To ensure consistent data management.
4. To ensure the integrity and security of data.
5. For concurrent data access and data recovery after accidents.
6. To employ a simple declarative query language. (DBMS Tutorials, 2017)
1.1. Designing a Relational Database System
1.1.1. The User and the System Requirements of the Polly Pipe System
[Link]. User Requirements
User requirement - They discuss the issue domain, or the user's world. They specify the
consequences that must be attained. These consequences are the joint responsibility of the
software, hardware, and users. (Prechelt, 2014)

I have identified some user requirements for the Polly Pipe System based on the brief given. These
requirements should be considered while developing the system for Polly Pipe. The major user
requirements are as follows.

1. The Polly Pipe System shall register and maintain the customers
2. Customer shall request several installations.
3. Admin shall retrieve all the installations information.
4. The system shall generate monthly and weekly reports as needed.
5. The system shall help to manage the payment of the installations and employees.
6. The database should be secured
7. The time taken to process data and respond shall be less
8. The records shall exist till the admin wants it to exist
9. The system should be able to hold a large number of records

[Link]. System Requirements

System requirement - They discuss the solution domain, or the realm of software logic. They
define what the program must accomplish (as opposed to the results that this may or may not
achieve in the user's environment). (Prechelt, 2014)

Similarly, Polly Pipe System too has some system requirements based on the brief given. These
requirements should be considered while developing the system for Polly Pipe company. They are
mentioned below.
8
 1. The Polly Pipe System must be able to work/run on the Windows 10 and other related
Windows operating systems (Software Requirement)
2. The machine where the Polly Pipe System is to be installed should have a processor and a
RAM which can handle a large database efficiently (Hardware Requirement)
3. A best paid antivirus software should be installed in the machine where the Polly Pipe
System is to be installed to protect the system from any cyber threats
4. The machine system should have a backup option to avoid any data loss.
5. The Polly Pipe System shall have a personal account for each customer
6. Admin shall have an option to access all the Installation details
7. Summary of the Installaions shall be generated Installation each week
8. The system shall generate the installations report for printing
9. Access to installations confirmation and payments shall be restricted to authorized users
only.

1.1.2. Designing the ER Diagram

[Link]. Entities and Attributes of Polly Pipe System

The Polly Pipe System must have a set of entities and some attributes related to the entities based
on the user and system requirements mentioned above. I have identified 8 major entities and some
attributes related to them as mentioned below.

Entity: CUSTOMER

Attributes: Cust_Id, Cust_Name, Cust_NIC, Cust_Address, Contact_No.

Entity: INSTALLATION
Attributes: InsID , Cus_Id, Installation_Type_ID, period .

Entity: EQUIPMENT
Attributes: EQU_Id, EQU_name , EQU_type ,EQU_qnty

9
Entity: PAYMENT
Attributes: Pay_Id, Cust_Id, InsID, Pay_Type,Pay_Date,Pay_Amount

Entity: EMPLOYEE
Attributes: Emp_Id,Emp_Name,Emp_NIC,Emp_Address,Emp_Type,Contact_No

Entity: INSTALLATION TYPE

Attributes: Installation_Type_ID, type

Entity: EMPLOYEE _INSTALLATION

Attributes: Emp_Id, InsID

Entity: EQUIPMENT_ INSTALLATION

Attributes: EQU_Id, InsID,Qnty

However, the attributes mentioned above are just an assumption and may differ and also be clearly
explained in the entity relationship diagram (ER Diagram).

10
[Link]. ER Diagram of Polly Pipe System

Figure 1:Polly Pipe ER Diagram

11
1.1.3. Designing the Relational Database Model
[Link]. Types of Keys in a Relational Database
A key is one or more qualities that, using the idea of determination, determine other attributes.
There are 5 major types of keys related to the relational database.

1. Super key - Each tuple in a relation is uniquely identified by a collection of

characteristics.
2. Candidate key - Similar to a super key, but does not include a subset of
characteristics that is a super key in and of it-self. To put it another
way, a "minimum super key."
3. Primary key (PK) - The Candidate Key has been chosen to uniquely identify all other
attribute values in any particular object. A primary key can have
many characteristics but cannot have NULL values.

4. Secondary key - A value-based attribute or collection of characteristics that

identifies a set of rows. Generally used just for data retrieval.

5. Foreign key (FK) - A table property or set of attributes whose values must either
match the primary key (PK) of another table or be NULL.
(FANDOM, 2017)

Primary keys in the Polly Pipe System

Table Primary Key(PK)

CUSTOMER Cust_Id
INSTALLATION InsID
INSTALLATION TYPE Installation_Type_ID
PAYMENT Pay_Id
EQUIPMENT EQU_Id
EMPLOYEE Emp_Id
Table 1:Primary Keys of Polly Pipe System

12
Primary keys in the Polly Pipe System

Table Foreign Key(FK)

CUSTOMER -

INSTALLATION InsID, Installation_Type_ID, Cust_Id

INSTALLATION TYPE -
PAYMENT -
CUS_PAYMENT Cust_Id, Pay_Id, InsID
EQUIPMENT -
EMPLOYEE -
EMPLOYEE_ INSTALLATION EQU_Id, InsID
EQUIPMENT_ INSTALLATION Emp_Id, InsID
Table 2:Foreign Keys of Polly Pipe System

13
[Link]. Relational Database of the Polly Pipe System
[Link].
CUSTOMER

Cust_Id Cust_Name Cust_NIC Cust_Address Contact_No

INSTALLATION
InsID Cus_Id Installation_Type_ID period

EQUIPMENT

EQU_Id EQU_name EQU_qnty

PAYMENT

Pay_Id Cust_Id InsID Pay_Type Pay_Date Pay_Amount

EMPLOYEE

Emp_Id Emp_Name Emp_NIC Emp_Address Emp_Type Contact_No

INSTALLATION_TYPE

Installation_Type_ID Type

INSTALLATION_EMPLOYEE

Emp_Id InsID

INSTALLATION_EQUPMENT

Eqp_Id Eqp_Qnty InsID

Figure 2:Referential Integrity Diagr

14
 1.2. Designing a Fully Functional System
1.2.1. Normalization
Normalization is a database design method that arranges tables to decrease data redundancy and
dependence. It splits big tables into smaller ones and connects them with connections. (GURU 99,
2017)

Normalization rules are divided into the following normal forms:

1. First Normal Form (1NF)

2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. BCNF

[Link]. First Normal Form (1NF)

For a table to be in the First Normal Form, it should satisfy the following 4 conditions

1. It should only contain attributes/columns with single (atomic) values.

2. Values recorded in a column must belong to the same domain.
3. Each column in a table should have a distinct name.
4. The sequence in which data is saved is irrelevant.
Ex:

EMPLOYEE table before normalization

Emp_Type Lorry Depot

Emp_Id Emp_Name Emp_Address Contact_No Emp_NIC
_No _Name
1001 Meera Kanfna 0772341567 954391875v Driver 0012 Kan
0758923456 893456782v Assistant 0031 Batt
1002 Harish Colombo
0112235748
1003 Chandane Gampaha 0779264890 943286721v Driver 0010 Col
0768923457 884371839v Assistant 0100 Batt
1004 Deepasika Gampaha
0652238756
Table 3:Un-Normalized EMPLOYEE Table

15
 This table is not in 1NF as the rule says “each attribute of a table must have atomic (single)
values”, the Emp_Mobile values for employees Harish & Deepika violates that rule.

To make the table complies with 1NF we should have the data as follows.

EMPLOYEE table after first normalization

EMPLOYEE

Emp_Type Lorry Depot

Emp_Id Emp_Name Emp_Address Contact_No Emp_NIC
_No _Name
1001 Meena Kanfna 0772341567 954391875v Driver 0012 Kan
1002 Harish Colombo 0758923456 893456782v Assistant 0031 Batt
1002 Harish Colombo 0112235748 893456782v Assistant 0031 Batt
1003 Chandane Gampaha 0779264890 943286721v Driver 0010 Col
1004 Deepika Gampaha 0768923457 884371839v Assistant 0111 Col
1004 Deepika Gampaha 0652238756 884371839v Assistant 0111 Col
Table 4:EMPLOYEE table in 1NF Form

[Link]. Second Normal Form (2NF)

For a table to be in the Second Normal Form it should satisfy the following conditions.

1. It should be in the First Normal form.

2. And, it should not have Partial Dependency.
Because each attribute has atomic values, the EMPLOYEE table is now in 1 NF. It is not,
however, in 2NF. To comply with 2NF, we can divide the table into two tables, as shown below.

EMPLOYEE table after Second Normalization

EMPLOYEE

Emp_Id Emp_Name Emp_Address Contact_No Emp_NIC Emp_Type

1001 Meena Kanfna 0772341567 954391875v Driver
1002 Harish Colombo 0758923456 893456782v Assistant
1002 Harish Colombo 0112235748 893456782v Assistant
1003 Chandane Gampaha 0779264890 943286721v Driver
1004 Deepika Gampaha 0768923457 884371839v Assistant
1004 Deepika Gampaha 0652238756 884371839v Assistant

16
Table 5:EMPLOYEE Table in 2NF Form

TRANSPORT_UNIT

Unit_Id Lorry_No Depot_Name

001 0012 Kan
002 0031 Batt
003 0010 Col
004 0111 Col
Table 6:TRANSPORT_UNIT Table in 2NF Form

[Link]. Third Normal Form 3NF

A table is said to be in the Third Normal Form when the following conditions are satisfied.

1. It is in the Second Normal form.

2. And, it doesn't have Transitive Dependency.
Both tables in this case are in 2NF. However, Depot Name is reliant on Lorry No, which is
dependent on Unit Id. This is a violation of the 3NF rule. The EMPLOYEE table, on the other
hand, is already in 3NF form, with no transitive dependencies.

To comply with 3NF, we must split the TRANSPORT UNIT table into two tables and eliminate
the transitive dependence.

TRANSPORT_UNIT table after third normalization

TRANPORT_UNIT DEPOT
Depot_Id Depot_Name
Unit_Id Lorry_No
101 Jaf
001 0012
102 Batt
002 0031
103 Col
003 0010
104 Col
004 0111
Table 7:DEPOT Table in 3NF Form
Table 9:TRANSPORT_UNIT Table in 3NF Form

Depot_Id Depot_Name
101 Jaf
102 Batt
104 Col
1.2.2. Interfaces of the Polly Pipe System
Login

Figure 3:Login Interface

MAIN FORM

Figure 4:Main Form Interface

18
CUSTOMER GUI

Figure 5:CUSTOMER Interface

EMPLOYEE GUI

Figure 6:EMPLOYEE Interface

19
Installation GUI

Figure 7: Installation Interface

Equipment GUI

Figure 8: Equipment Interface

20
PAYMENT UNIT GUI

Figure 9: PAYMENT _UNIT Interface

Installation Type GUI

Figure 10: Installation Type Interface

21
EMPLOYEE _INSTALLATION GUI

Figure 11: EMPLOYEE _INSTALLATION Interface

EQUIPMENT _INSTALLATION GUI

Figure 12: EQUIPMENT _INSTALLATION Interface

22
1.2.3. Validation Mechanisms
Validation is a computer-automated check that ensures the data entered is sensible and reasonable.
It does not verify the veracity of data. (BBC, 2014)

Validation Type How It Works Example Usage

Checks to see if the data

A number or date
Type check entered is of the expected
kind.

Ensures that the user may

only select a specified
Selecting one of the subjects of a
Drop down box choice from a list, limiting
course
the possibility of spelling
errors or undesired replies.

The final one or two digits

of a code are used to verify Bar code readers in supermarkets
Check digit
that the previous numbers use check digits
are valid.
A national insurance number is in
Verifies that the data is in
Format check the form ll 99 99 99 l where l is
the correct format.
any letter and 9 is any number
Verifies that the data is
A password which needs to be six
Length check neither too short nor too
letters long
lengthy.
Searches a table for suitable There are only seven possible days
Lookup table
values. of the week
Checks whether or not data In most database a key field cannot
Presence check
has been entered into a field. be left blank
Checks whether or not a
Number of hours worked must be
Range check value is inside the specified
less than 50 and more than 0
range.

23
 Looks up words in a
Spell check When word processing
dictionary
Table 10:Validation Mechanisms

1.3. ERD vs User and System Requirements

Databases are extremely important in today's technological world. Databases are essential
components of almost every system. As a result, we may argue that developing a system without a
database is nearly difficult. Logical database modelling is mostly used for obtaining information
for commercial purposes. It does not include the creation of a database, but the physical database
modelling is the process of creating databases. The ER diagram is crucial in the effective design of
databases.

The ER diagram I developed includes almost all the user and system requirements gathered from
the Polly Pipe Company. First and foremost, the main aim is to manage service and I have
implemented a form for each and every activity involved in it. Next is managing the reports.
Hence, I have created report generation buttons for daily and weekly report generation. Security is
one of the most important needs and it‟s provided to password login option and granting privileges
for different user types. The system I have developed s fully secured and that requirement has been
satisfied. Work details are also being able to retrieve easily by clicking buttons. Combined
information of Installation and payments are retrieved by using join queries.

As the ERD and logical design contain all the details that we need for designing a database can
implement meaningful information in the database. In my database almost all the attributes are
connected to each other so it‟s easy to gain meaningful information. Almost all the user
requirements are satisfied on the database so we can assume that my database is well designed and
executed.

Talking about system requirements system requirements, only a medium hardware configuration is
required so the cost of the hardware is less. Here there aren‟t any need for storing images or videos
so the capacity of memory used is less however, we can update this in future if [Link] using a
PC with a normal configuration large amount of money is saved and the system is developed cost
efficiently.

My opinion on my database is that it is very efficient and saves money.

24
Activity 2
2.1. Developing the Database System
2.1.1. SQL DDL Statements of the Polly Pipe System
DDL stands for Data Definition Language and Create, Alter and Drop are some of the examples of
DDL statements. Following are some SQL DDL statements of Polly Pipe System.

1. SQL DDL Statement for CUSTOMER table

Figure 13:SQL DDL Statement for CUSTOMER table

2. SQL DDL Statement for EMPLOYEE table

Figure 14:SQL DDL Statement for EMPLOYEE table

25
3. SQL DDL Statement for EQUIPMENT table

Figure 15:SQL DDL Statement for EQUIPMENT table

4. SQL DDL Statement for INSTALLATION table

Figure 16:SQL DDL Statement for INSTALLATION table

26
5. SQL DDL Statement for INSTALATION_EMPLYEE table

Figure 17SQL DDL Statement for INSTTALLATION table

6. SQL DDL Statement for PAYMENT table

Figure 18:SQL DDL Statement for PAYMENT table

27
2.1.2. Evidence of Insert, Update and Delete Queries of the System in Visual Studio
1. Inserting a data
C# code snippet of insertion of data into CUSTOMER table

Figure 19:Insertion C# Code

When we click the INSERT button in the Customer form a message box will pop up to confirm the
registration is successful.

Figure 20:Insert Data GUI

28
CUSTOMER table data in Polly Pipe database after INSERT operation

Figure 21:Insert Data Table

When we perform view operation after the insert operation the newly inserted data will appear as
shown below

Figure 22:View data after inserting

2. Updating a data
C# code snippet of updating of data into CUSTOMER table

Figure 23:Update data C# code

29
When we click the UPDATE button in the Customer form a message box will pop up to confirm
the updating is successful.

Figure 24:Update data GUI

CUSTOMER table data in Polly pipe database after UPDATE operation

When we perform view operation after the update operation the newly updates data will be
updated and appear as shown below

Figure 26:View data after updating

30
3. Deleting a data
C# code snippet of deletion of data into CUSTOMER table

Figure 27:Delete data C# code

When we click the DELETE button in the Customer form a message box will pop up to confirm
that the data is deleted successfully.

Figure 28:Delete data GUI

31
CUSTOMER table data in Polly pipe database after DELETE operation

Figure 29:Delete data table

When we perform view operation after deletion operation, the corresponding data will be deleted
and the table will appear as shown below

Figure 30:View data after deleting

32
2.1.3. Implementation of Validation Methods

1. Range check
This verifies that the data is inside a specific range and falls between an upper and lower
acceptable value.
In our system, NIC is an example of an attribute that can only have a certain number of digits,
therefore range check is used to define it.

Figure 31:Range Check

2. Presence check
Checks that the user has typed anything into the field, preventing them from leaving it empty by
accident. This check is utilized when defining all main keys since they cannot be empty, as well as
other characteristics that should not be null.

Figure 32:Presence Check

3. Drop down box

It helps to eliminate errors caused by spelling mistakes and undesired answers. Because there are
only three categories of customers in our system and we shouldn't pick a type other than those
three, we utilized a drop down box to select the customer type.

Figure 33:Drop Down Box

33
4. Type check
This function ensures that the data entered is of the expected kind. For instance, the name should
be of the varchar(string) type, and the contact number should be in integers.

Figure 34:Type Check

2.1.4. Example for Join Query

The following join query is used to retrieve payment information along with INSTALLATION
information and customer information.

SELECT * FROM (
(PAYMENT AS P INNER JOIN CUSTOMER AS C ON P.Cust_Id=C.Cust_Id )
INNER JOIN INSTALLATION AS I ON I.Cust_Id=C.Cust_Id);

Figure 35:Join query output

34
2.2. Implementing Query Language into the Relational Database System
2.2.1. Usage of DML
1. SELECT
SELECT * FROM EMPLOYEE

This command is used to retrieve data from the given table based on our requirements.
Following is the execution of the command, the result of data table.

Figure 36: SELECT command RESULTS

2. UPDATE
This command is used to update any information that is already stored in the table.

Data table before executing the update command

Figure 37:Data table before UPDATE

UPDATE EMPLOYEE SET Contact_No=009755453445 WHERE Emp_Id='SHA1'

The resulting data table after the command is executed. Here the corresponding data has been
updated.

Figure 38:Data table after UPDATE

3. DELETE

35
This command is used to delete any unwanted data from the existing data.

Data table before executing the deletion command.

Figure 39:Data table before DELETE

delete From CUSTOMER where Cust_Id='C004'

The results after executing the deletion command. Here the corresponding data has been deleted
from the table.

Figure 40:Data table after DELETE

2.3. System Security and Database Maintenance

2.3.1. Security Mechanisms
Database security refers to the set of procedures used to safeguard and secure a database or
database management software against unauthorized usage as well as harmful threats and assaults.
It is a comprehensive word that encompasses a wide range of procedures, technologies, and
techniques that assure database security.
A database administrator or other information security specialist is usually in charge of planning,
implementing, and maintaining database security.
Some of the ways database security is analyzed and implemented include
1. Restricting illegal access and usage through the implementation of robust and multifactor
access and data management measures.
2. Payment/stress testing and database capacity testing to verify that it does not fail under a
distributed denial of service (DDoS) attack or user overload.

36
 3. Theft and natural catastrophe protection for the database server and backup equipment
4. Examining current systems for known and undiscovered vulnerabilities and developing and
implementing a road map/plan to mitigate them(Database Security, 2017)

In my system I used two methods to ensure security

1. Creating user and granting level permissions

For this we have to launch the SQL Server Management Studio and connect with credentials that
have been granted the 'sa' role and continue doing the following steps.

Step 1:
Expand „Security‟, right-click on „Logins‟ and select „New Login‟

Figure 41:Granting level permission step 1

37
Step 38
:Enter a descriptive „Login name‟, here I have selected „Normal_User‟ as the login name

select „SQL Server authentication‟, and enter a secure password. On the bottom of the page select
the database Polly_pipe will be connecting to as the „Default database‟

Figure 42::Granting level permission step 2

38
Step 39
:
Select the „User Mapping‟ tab, check the box next to the desired database(Polli pipe), confirm the
role membership of whom do you want to give his privilege and click „OK‟

Figure 43:Granting level permission step 3

Step 4:
Click the „New Query‟ button and select the database we are connecting to Normal_User.
Type following query into the query window and execute.

Figure 44::Granting level permission step 4

39
Step 5:
Select and copy the query results into the query window.
Remove any tables or views you do not wish the "Normal_User" to have access to. Here I have
removed employee and payment tables because the payment details and the employee details are
sensitive information which only the admin should handle.

Figure 45::Granting level permission step 5

Then execute the query.

Figure 46::Granting level permission final code

40
2. Password login facility
For this I created a password login form for administrators and the data entry employees to login
based on their user account
Username = Admin
Password = 123

Figure 47:Login interface

41
2.4. Meaningful extraction of data
2.4.1. Usage of SQL Statement
1. Group By
The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM,
AVG) to group the result-set by one or more columns.

select count(Emp_Id),Emp_Type from employee group by Emp_Type

Results after using GROUP BY statement on EMPLOYEE table

Figure 48:Group By

2. Order By
The ORDER BY keyword is used to arrange the entries in a certain order. By default, it sorts in
ascending order. The DESC keyword is used to sort the entries in descending order. We may also
sort in ascending order by using ASC.

select * from EMPLOYEE order by Emp_Name desc

EMPLOYEE table after sorting by employee name in descending order

4
Figure 49:Order By

3. Having
The HAVING clause was added to SQL because the WHERE keyword could not be used with
aggregate functions.

SELECT COUNT(Emp_Id) as No_of_Employee , Emp_Type From EMPLOYEE

group by Emp_Type having count (Emp_Id) >1

42
Resulting table after using HAVING clause on CUSTOMER table.

2
Figure 50:Having

4. Between
The BETWEEN operator chooses values from a specified range. Numbers, text, and dates can all
be used as values. This operator is inclusive, which means it takes into account the start and finish
values.

SELECT * FROM PAYMENT WHERE Pay_Date BETWEEN '2/8/2021' AND '2/10/2021'

PAYMENT table after using BETWEEN operator on it.

Figure 51:Between

5. Where
To filter records, use the WHERE clause. It is used to extract just the entries that meet a predefined
criterion.

SELECT * FROM PAYMENT WHERE Pay_Type = 'Cash'

Resulting table after the usage of WHERE clause on PAYMENT table

Figure 52:Where

43
Activity 3
3.1. Testing
3.1.1. Test plan
A software test plan explains what will happen, how long it will take, who will perform it, what
will be done to it, and what we expect to happen as a result. Consider it a highly comprehensive
method of testing a piece of software to ensure that we have covered all the bases. There are
numerous more formal procedures in the entire software testing process, but the plan is where we
start.

The plan is critical since it describes the testing procedure. The strategy has been split down into
digestible chunks so that we can deal with each stage of the procedure. It also serves as a record of
our goals, allowing us to look back and evaluate how we fared.

An Example of a Test Plan

The exact format of a test plan will vary from need to need, but you can bet that there are some
features you will find in most any plan. Here are some of those features:

1. Introduction
2. Objectives
3. Scope
4. Strategy
5. Requirements
6. Risks
7. Schedule
8. Roles/Resources
9. Procedures
10. Musts/Must-Not‟s
11. Milestones/Sign-Offs
(Meinecke, 2016)

44
Why database test is required?

1. Data Mapping
2. ACID properties validation

Figure 53:ACID Diagram

3. Data integrity:
4. Business rule conformity

The general test process for DB testing is not very different from any other application.

The following are the steps to test a database:

Step 1: Prepare the environment

Step 2: Run a test
Step 3: Check test result
Step 4: Validate according to the expected results
Step 5: Report the findings to the respective stakeholders

Figure 54:Database test steps

Usually, SQL queries are used to develop the tests. The most commonly used command is
“Select”.

45
Apart from Select, SQL has 3 important types of commands:

1. DDL: Data definition language

2. DML: Data manipulation language
3. DCL: Data control language

(Database Testing, 2018)

3.1.2. Test Plan of Polly Pipe System

Test
Case Short description of testing Steps follow Expected results
ID
Click the log in The main form must
button after entering appear.
Log in with the correct username and
the correct username
1 password.
and password in the
relevant textboxes.

Click the log in Message box have to

button without show the message
Log in with empty fields. entries both “Empty field detected.
2
username and Please fill up all the
password fields. fields”.

Message box have to

Login with wrong
Login with incorrect username or show the message
username and
3 password or both. “Incorrect username or
password entries.
password”.

The information should

be saved in the
Select the insert
database and the
4 Insert information into a form. option after filling
message box must
the form.
show a message
“Inserted successfully!”

46
 The information should
be deleted from the
Press delete button
database and the
5 Delete information in a form. after selecting a
message box must
particular row.
show a message “Data
Deleted successfully!”
The corresponding
information should be
Press the update
updated in the database
button after
6 Update information in a form. and the message box
changing any
must show a message
information
“Updated
successfully!”

All the details of the

View all the information of the
corresponding form
Installation/payment/Customer/Equipment Press the view
7 must be viewed in the
unit. button.
data grid view in the
form.

47
3.1.3. Test cases
1. SELECT a table
SELECT * FROM CUSTOMER

Expected Output: Displaying the CUSTOMER table

Obtained Output: The CUSTOMER table is displayed

Figure 55:SELECTION test

2. DELETE a record in the table

DELETE FROM EMPLOYEE where Emp_Id ='SHA67'

Expected Output: Deleting the record of customer ID SHA67

Obtained Output: The record deleted successfully

Figure 56:DELETION test

3. UPDATE a record in the table

UPDATE EMPLOYEE SET Contact_No='02152345288' where Emp_Id = 'SHA11'

Expected Output: Updating the corresponding data

Obtained Output: Data updated successfully

Figure 57:UPDATING test

48
4. INSERT data into the table
INSERT INTO EMPLOYEE VALUES ('SHA55','Alison Smith','9875642515','Sackville Street','Brick
Layer','97584582525')

Expected Output: Inserting the data successfully

Obtained Output: Data inserted Successfully

Figure 58:INSERTION test

5. Range check test

INSERT INTO EMPLOYEE VALUES ('SHA55002500000','Alison Smith','9875642515','Sackville
Street','Brick Layer','97584582525')

Expected Output: Not accepting the data inserted

Obtained Output: Statement termination

Figure 59:Range check test

6. Type check test

INSERT INTO PAYMENT VALUES (652,'C003','INFWG',02/05/)

Expected Output: Cannot insert INTEGER data in place of DATE type data

Obtained Output: Incompatible data type, error message received

Figure 60:Type check test

7. Inserting a duplicate primary key

49
INSERT INTO CUSTOMER VALUES ('C001','Lee A. sun','9858785785','Oak House17 Wroxton
Road','09666854112')

Expected Output: Cannot insert data for a duplicate primary key

Obtained Output: Violation of primary key message received

Figure 61Duplicate primary key check test

3.2.
3.2.1. Effectiveness of Testing
Every company has a database of their important data which must be preserved carefully for a
certain period of time or for the life time. These data may be confidential ones or may play an
important role in their day to day activities. It is told that the data of a company is a valuable asset
for them and should be maintained carefully. It is the duty of the database developer to protect and
take safety precautions in order to avoid any unwanted risks. Testing provides the concrete
feedback required to identify defects. Therefore, certain type of tests should be done on the
database created by the developer for a system. Talking about Polly Pipe System, I have done
some tests as mentioned in the previous section (3.1.1) just to make sure that none of my codes
have any errors and to know whether there are any chances for any unexpected errors or
breakdowns during the execution of the system.

The Polly Pipe System is more about storing and retrieving the stored data and doesn‟t have much
calculation parts, so I decided to test mainly on SELECT, INSERT, DELETE and UPDATE
queries which are the most used ones in my system. This test helped me to make sure there aren‟t
any errors while registering a new customer or employee and retrieving details about them and the
Payment operation. I tried adding a certain number of data into my database to check the capacity
of my database. I also did some tests to make sure all my key constraints are correct and runs
smoothly; else there could be difficulties in retrieving data from two or more tables at the same

50
time. So I tried adding duplicate data for the same primary key value to check all my constraints.
The type check test helped to make sure no one can enter wrong type of information into the
database. The range check I did, helped me to ensure any data which isn‟t in the specified range is
added into the database.

These tests helped me to present my system and database without any errors and to make it a well-
designed one. All the tests are really effective and helped me to make my system a robust and error
free one.

3.3. Evaluating the Effectiveness of the Database Solution

3.3.1. Evaluation from the Feedbacks
After finishing the system development and tests it‟s is important to find out whether the system
we developed satisfies the targeted people‟s expectations. Getting feedbacks from others are the
best way to evaluate our system. There are a number of ways to get feedbacks from people and
some of them are mentioned below.

1. Surveys
2. Questioner
3. Interviews
The main target users of the Polly Pipe System are non-technical users however to make the
system more efficient we also need some tips from the technical side as well. So I chose two
methods from those mentioned above, each to get feedbacks from each type of users.

1. Questioner
The first method I selected was questioner. Customer satisfaction surveys can help us find out
what people think of our system. When we listen to our targeted users, we can make decisions that
build a more efficient and interactive system. As our target users are non-technical users, I decided
to give some questioner to the students from other batch in E-Soft and get their feedback.
Therefore, I prepared a set of questions and answers and asked them to fill it. From their response I
got some ideas to enhance my system.

The questions I prepared and an evidence of the feedback form filled by a student are as follows.

51
.

Figure 62:Filled feedback form (evidence)

After analysing the feedbacks, the answers in the red font are found to be the most selected
answers by the non-technical users

52
 Figure 63:Non-technical user feedback questioner most selected answers

As per the feedback by most of the students, I made some changes in the interface of my system in
order to make it more user friendly

2. Interviews
The next method selected was an [Link] I mentioned above, I also needed some technical
feedbacks hence, and I decided to have an interview with some of my lecturers regarding the
system I developed. From their feedback I made some changes in order to make my system more
efficient one of the points they mentioned is to have a button to get daily/weekly evaluation of
Payment made and I created daily and weekly report generation buttons.

Finally, I was able to get feedbacks from both the technical and non-technical users and it helped
me to evaluate and update my system in an effective way.

53
Recommendation Conclusion Type of user
Change the colours and style
Make changes in the user
of the interface to make it Non-technical user
interface
more user friendly
Add another button to get Create a button to create daily
daily/weekly evaluation of and weekly reports of Technical user
Payment. Payment made
Table 11:Recommendations and conclusions

Activity 4
4.1. Technical and User Documentation
4.1.1. User Guide
Insertion of data

Figure 64:Insert user guide

To insert any data into the database the user must fill all the details asked in the form, and after
filling the form the user should press the SUBMIT button as shown above.

54
Viewing the data

Figure 65:Delete user guide

To view the data stored in the database the user must press the VIEW button as shown above.

Deletion of data

Figure 66:Delete user guide

To delete any data that exist in the database the user must select the view button and then, select
the corresponding row he/she wants to delete and then press the DELETE button as shown above

55
Updating the data

Figure 67:Update user guide

To update any data in the database the user must fill the data which he wants to update and
mention the corresponding id for which the user wants to update and then should press the
UPDATE button as shown above.

4.1.2. Technical Guide

To develop this system, I used C# as the language. Microsoft Visual Studio is the IDE I used to
develop my system interfaces and code. For the database development I use SQL and I also used
SQL server to run the database. These soft wares helped me to develop an efficient system for
Polly Pipe Company with database to satisfy their needs.

Roles and responsibilities

Admin – can access the whole database and can insert, delete or update any data as needed. This
role is recommended to be given to the manager of the company

Normal user- the access is restricted. This role can be given for data entry employee.

Security

SQL is a secured type of language and data cannot be easily stolen by any one.

We also use password login facility, so the data is protected

56
System requirements

A machine with a minimum requirement of windows 7 with a medium capacity hard disk is
needed.

System features

3. Insert data
4. Update data
5. Delete data
6. View data
7. Create reports
8. Encryption

4.2. Technical Documentation for a Fully Functional System

4.2.1. Diagrams
1. Use Case Diagram

Figure 68:Use Case Diagram

[Link] Unit-04 Database Design & Development 57
2. Data Flow Diagram
DFD Level 0

PPS

Figure 69:DFD Level 00

DFD Level 01

Figure 70:DFD Level 01

58
3. Flow Chart

Figure 71:Flow Chart

59
4.3 Ensuring Continued Effectiveness of Database System
4.3.1. Future improvements
As time runs the both the business and technical environment evolve, as a result our system may
get useless or in efficient if it doesn‟t adapt to those changes on time. For our system to sustain we
should make some improvements time to time. Talking about changes, the main thing is the
software and hardware changes. Our system should be updated at least once 6 months to adapt
those [Link] time passes, the main problem we could face is the capacity of data, because of
some safety precautions and legal issues we should preserve our data without deleting so, and for
this we should have a more efficient backup option. The backup is also needed to avoid data loss.
Sometime some customers might be currently inactive, creating a new account after they become
active after years will add additional storage space, so we shouldn‟t delete any data, but can
improve the capacity of our hard disk.

In future our system should be able to be accessed from several devices simultaneously. For this
we should update our system into an online system connected with internet and should also
improve some security features. We should also add some more privileges to number of users‟
types so that a set of employees can work with or access the system. Updating it into an online
system also helps real time transactions and backups so that our system would work more
efficiently.

Following these improvements would ensure continued effectiveness of the Polly Pipe System and
the system would sustain for a long time.

60
Self-Criticism
I was able to complete the assignments before the deadline because of the concern and interest
towards the subject. There were some factors that affected my completion of the assignment, some
were the strengths and some were weakness.

Strengths

1. Starting the assignment on time

2. Defining my own deadlines to complete the tasks
3. Motivation I got from my lecturers and friends
Weakness

1. Time management
2. Misunderstanding of the concept of some tasks
At the beginning I struggled to do my assignments and faced some difficulties after the guidance
and the help of the above mentioned strengths finally I was able to complete my assignment on
time. I learnt to fix my own deadlines for finishing each task and to finish the final assignment as
well. Now I have a good knowledge on doing assignments.

61
CONCLUSION
This assignment has been written and processed up to my knowledge I got while studying
Database Design and Development in HND of Computing. Furthermore, this assignment consist
solutions which have been described with the problems stated in the scenario given in the
assignment brief and explained by the lecturer.
Solutions which have been stated with proper evidence and some other criteria of actions should be
taken from the side of the assignment creators.
Moreover, this assignment has been affiliated to the terms and conditions which have been
described with the rules that have been learned from our class room and existing assignment
documentations. Some solutions have been described and evaluated from some users and some
staffs of my institution to get positive feedback regarding the solution, and I assure that there will
be some modifications and re-configurations in the system to increase the performance of the
system configuration and the management of the system related to the assignment brief.

62
References
BBC, 2014. BBC. [Online]
Available at: [Link]
[Accessed 26 07 2021].

Database Testing, 2018. Software Testing Help. [Online]

Available at: [Link]
[Accessed 27 07 2021].

DBMS Tutorials, 2017. W3Schools. [Online]

Available at: [Link]
[Accessed 25 07 2021].

GURU 99, 2017. Guru99 - What is Normalization? 1NF, 2NF, 3NF & BCNF with Examples.
[Online]
Available at: [Link]
[Accessed 21 07 2021].

Meinecke, L., 2016. [Link]. [Online]

Available at: [Link]
[Link]
[Accessed 27 07 2021].

Prechelt, L., 2014. Software Engineering Stack Exchange. [Online]

Available at: User requirements talk about the problem domain, the world of the user. They
describe what effects need to be achieved. These effects are the combined responsibility of the
software, the hardware, and the users.
[Accessed 21 07 2021].

Rouse, M., 2017. TechTarget. [Online]

Available at: [Link]
[Accessed 15 08 2021].

SINGH, C., 2016. DBMS Tutorial. [Online]

Available at: [Link]
[Accessed 16 08 2021].

63
Gantt chart

64