Higher Nationals

Internal verification of assessment decisions – BTEC (RQF)

INTERNAL VERIFICATION – ASSESSMENT DECISIONS
Programme title BTEC HND in Computing
Miss. Ozaniya Mr.G. Thanulagshan
Assessor Internal Verifier
Unit 04: Database Design & Development
Unit(s)
Database Solution for Polly Pipe
Assignment title
Fareed Mohammathu Fairoos
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
Y/N
• Identifying opportunities for
improved performance?
Y/N
• Agreeing actions?
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
signature Date

1|Page Database Design Development F.M.FAIROOS

Programme Leader
signature (if required) Date

2|Page Database Design Development F.M.FAIROOS

Higher Nationals - Summative Assignment Feedback Form
Student Name/ID Fareed Mohamathu Fairoos/00088038
Unit 04: Database Design & Development
Unit Title
Assignment Number 1 Assessor
2021-12-29 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.

Assignment Feedback

3|Page Database Design Development F.M.FAIROOS

Formative Feedback: Assessor to Student

Action Plan

Summative feedback

Feedback: Student to Assessor

Your lecturing method is Great and thank you.

Miss.Ozaniya
Assessor Date
signature
F.M. Fairoos 2021-12-29
Student Date
signature

4|Page Database Design Development F.M.FAIROOS

Pearson Higher Nationals in
Computing
Unit 04: Database Design & Development
Assignment 01

5|Page Database Design Development F.M.FAIROOS

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.
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

Student Declaration

6|Page Database Design Development F.M.FAIROOS

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:

E127765@esoft.academy 2021-12-29

7|Page Database Design Development F.M.FAIROOS

Higher National Diploma in Computing
Assignment Brief
Student Name /ID Number Fareed Mohammathu Fairoos
Unit Number and Title Unit 4: Database Design & Development

Academic Year 2021/22

Unit Tutor Miss.Ozaniya
Assignment Title Data base system for Polly Pipe

Issue Date 2021-07-31

Submission Date 2021-12-29
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|Page Database Design Development F.M.FAIROOS

Assignment brief

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|Page Database Design Development F.M.FAIROOS

 Installation ID Installation
Installation Customer Equipment Types of Staff Period of
Type Name and Required Staff
Address assignment
234 Freshwater Oak House, Lee A. sun 2 air pumps 1 x Carpenter From 1st
Tropical 17 Wroxton 200 gallons 1 x Aquatics September
Road, fish tank installer 2012
Hertfordshire 1 x standard 1 x Electrician
, H5 667 thermostat
654 Freshwater Bayliss Sally Dench 2 air pumps 5 x Carpenters 1st June
Cold House, 200 gallons 1 x Installation 2005 – 1st
Orange fish tank Manager June 2011
Street, Kent, Large Gravel 1 x Aquatics
K7 988 Bag installer
2 x standard 1 x Plumber
thermostats 3 x Labourers
767 Marine Eaglestone Perry 2 x 200 10 x Carpenters From 30th
Castle, Vanderrune gallons fish 2 x Installation June 2012
Eaglestone, tanks Manager
Kent 500 Wood 1 x Aquatics
panels installer
1 x Plumber
3 x Labourers
943 Marine 23 Sackville Eric 2 air pumps No staff required
Street, Wilts. Mackintosh 200 gallons
W55 fish tank
1 x standard
thermostat
157 Freshwater Humbertson Perry 2 air pumps 1 x Aquatics 1st
Tropical Castle, Kent, Vanderrune 400 gallons installer September
K8 fish tank 2005 – 1st
3 x standard September
thermostat 2012

Instillation Management Form

Activity 1
1.1. Identify the user and system requirements to design a database for the above 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
10 | P a g e Database Design Development F.M.FAIROOS
 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
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

11 | P a g e Database Design Development F.M.FAIROOS

 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 | P a g e Database Design Development F.M.FAIROOS

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,

based on an existing system design

13 | P a g e Database Design Development F.M.FAIROOS

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

14 | P a g e Database Design Development F.M.FAIROOS

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.

15 | P a g e Database Design Development F.M.FAIROOS

 Database Design & Development
Unit -04

Database Solution for Polly Pipe

Whom: Fareed Mohammathu Fairoos

Student ID: 00088038
Unite-04 Database Design & Development
HND in COM-52 Batch
In ESOFT Metro Campus

16 | P a g e Database Design Development F.M.FAIROOS

Acknowledgement
I am proud that I took this assignment every learning outcome and attending classes and
improved the Database Design & Development First, I would like to thank for Miss.Ozaniya
lecturer at the ESoft Metro Campus, Batticaloa who helped me to understand the scenario and
get a clear idea about it. I would also like to thank all the lecturers of ESoft, Batticaloa
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.

F.M. FAIROOS

17 | P a g e Database Design Development F.M.FAIROOS

Table of Content
Table of Contents
Acknowledgement....................................................................................................................16

Table of Content.......................................................................................................................17

List of Figures..........................................................................................................................20

List of Table.............................................................................................................................23

Activity-01...............................................................................................................................24

1.1 Identify the user and system requirements to design a database for the above 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 normalized. If not, normalize the database by
removing the anomalies...........................................................................................................24

1.1.1 Introduction of the company.......................................................................................24

1.1.2 Identify the user and system requirements to design a database for the above scenario.
..............................................................................................................................................25

1.1.2.1 User requirements.................................................................................................25

1.1.2.2 System requirements............................................................................................25

1.1.3 Entity relationship diagram (ERD)..............................................................................28

1.1.4 Entity Relationship Diagram for Polly Pipe Company...............................................31

1.1.5 Logical schema for Polly Pipe Company....................................................................31

1.1.6 Normalization..............................................................................................................32

1.1.7 Assess the effectiveness of the design.........................................................................37

1.1.8 Data Validation............................................................................................................37

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.............38

1.2.1 Graphical user interface design...................................................................................38

18 | P a g e Database Design Development F.M.FAIROOS

Activity 2.0...............................................................................................................................44

2.1Develop 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.. .44

2.1.1 SQL Commands..........................................................................................................45

2.1.1.1 Data Definition Language (DDL)........................................................................45

2.1.1.2 Data Manipulation Language...............................................................................51

2.1.1.3 Commands............................................................................................................59

2.1.1.4 Join Quarry...........................................................................................................63

2.2 Explain the Database Security Mechanism........................................................................65

Activity 3.0...............................................................................................................................67

3.1 Test Plan.............................................................................................................................67

3.1.1 Methods of Testing......................................................................................................67

3.1.2 Provide a suitable test plan to test the system against user and system Requirements.
..............................................................................................................................................68

3.1.3 Explain how the selected test data can be used to improve the effectiveness of testing.
..............................................................................................................................................71

3.1.4 Effectiveness of Testing..............................................................................................72

3.1.4.1 Test Efficiency.....................................................................................................72

3.1.5 Feedback......................................................................................................................73

3.1.5.1 Types of feedback................................................................................................73

3.1.5.2 Feedback form For Polly Pipe Users....................................................................75

3.1.5.3 Response for Polly Pipe Users.............................................................................76

Activity 04................................................................................................................................78

4.1 User Guide and Technical Documentation........................................................................78

19 | P a g e Database Design Development F.M.FAIROOS

4.2 Introduction........................................................................................................................79

4.2.1 System Requirements..................................................................................................79

4.2.2 Diagram.......................................................................................................................82

4.2.2.1 Use Case Diagram................................................................................................82

4.2.2.2 DFD Diagram.......................................................................................................85

4.2.2.3 Flow chart.............................................................................................................87

4.2.3 Advantages of Database System.................................................................................88

4.2.4 Suggestion for future enhancements to ensure the effectiveness of the system..........89

Conclusion................................................................................................................................90

Self-Criticism...........................................................................................................................91

Guntt Chart...............................................................................................................................92

References................................................................................................................................93

20 | P a g e Database Design Development F.M.FAIROOS

List of Figures
Figure 1 ER Diagram..............................................................................................................31
Figure 2 Logical schema........................................................................................................ 31
Figure 3 Login interface......................................................................................................... 38
Figure 4 Customer Interface...................................................................................................38
Figure 5 Employee Interface.................................................................................................. 39
Figure 6 Employee Type Interface.........................................................................................39
Figure 7 Equipment Interface.................................................................................................40
Figure 8 Equipment Type Interface........................................................................................40
Figure 9 Facility interface.......................................................................................................41
Figure 10 Installation Interface...............................................................................................41
Figure 11 Installation Type interface......................................................................................42
Figure 12 Payment Interface...................................................................................................42
Figure 13 Dashboard.............................................................................................................. 43
Figure 14 Visual Studio IDE..................................................................................................44
Figure 15 SQL Commands.....................................................................................................45
Figure 16 Customer Care Table..............................................................................................48
Figure 17 Installation Table....................................................................................................48
Figure 18 Installation Type Table...........................................................................................48
Figure 19 Employe Type table............................................................................................... 48
Figure 20 Equipment Table....................................................................................................48
Figure 21 Equipment Type Table...........................................................................................48
Figure 22 Facilities Table.......................................................................................................48
Figure 23 Payment Table........................................................................................................49
Figure 24 Employe Table.......................................................................................................49
Figure 25 Before ALTER Table in Customer.........................................................................49
Figure 26 Alter Table Query.................................................................................................. 49
Figure 27 After ALTER Table in Customer...........................................................................49
Figure 28 Before ALTER Table in Employe..........................................................................50
Figure 29 Alter Table Query.................................................................................................. 50
Figure 30 After ALTER Table in Employee..........................................................................50

21 | P a g e Database Design Development F.M.FAIROOS

Figure 31 Before Drop Table in Customer.............................................................................51
Figure 32 Drop Table Query.................................................................................................. 51
Figure 33 After Drop Table in Customer................................................................................51
Figure 34 Before Insert data in Customer Table.....................................................................53
Figure 35 Data insert in Customer Table................................................................................53
Figure 36 After Insert data in Customer Table.......................................................................54
Figure 37 Data Successfully Store in Customer Table is SQL...............................................54
Figure 38 Insert Coding..........................................................................................................54
Figure 39 Before Update data in Customer Table..................................................................55
Figure 40 Data Update in Customer Table.............................................................................55
Figure 41 After Update data in Customer Table.....................................................................56
Figure 42 Data Successfully Update in Customer Table is SQL............................................56
Figure 43 Update CODING....................................................................................................56
Figure 44 Before Delete data in Customer Table....................................................................57
Figure 45 Data Delete in Customer Table..............................................................................57
Figure 46 After Delete data in Customer Table......................................................................58
Figure 47 Data Successfully Delete in Customer Table is SQL.............................................58
Figure 48 Delete Coding........................................................................................................ 58
Figure 49 BETWEEN Command...........................................................................................59
Figure 50 Group by Command...............................................................................................59
Figure 51 Having Command.................................................................................................. 60
Figure 52 In Command...........................................................................................................60
Figure 53 Order by Command................................................................................................61
Figure 54 Select Command.................................................................................................... 61
Figure 55 Where Command................................................................................................... 62
Figure 56 Update Command...................................................................................................62
Figure 57 INNER JOIN..........................................................................................................63
Figure 58 LEFT JOIN............................................................................................................ 63
Figure 59 RIGHT JOIN..........................................................................................................64
Figure 60 FULL JOIN............................................................................................................64
Figure 61 Black Box Testing..................................................................................................67
Figure 62 Feedback Form.......................................................................................................75

22 | P a g e Database Design Development F.M.FAIROOS

Figure 63 Response Form -01................................................................................................ 76
Figure 64 Response Form -02................................................................................................ 76
Figure 65 Response Form -03................................................................................................ 76
Figure 66 Response Form -04................................................................................................ 77
Figure 67 Response Form -05................................................................................................ 77
Figure 68 Response Form -06................................................................................................ 77
Figure 69 Login Form............................................................................................................ 80
Figure 70 Dashboard Form.....................................................................................................81
Figure 71 Customer Page....................................................................................................... 81
Figure 72 System....................................................................................................................82
Figure 73 Use Case.................................................................................................................83
Figure 74 Actors.....................................................................................................................83
Figure 75 Relationships..........................................................................................................83
Figure 76 User Diagram.........................................................................................................84
Figure 77 Level 0................................................................................................................... 85
Figure 78 DFD Level 1...........................................................................................................86
Figure 79 DFD Level 1...........................................................................................................86
Figure 80 DFD Level 1...........................................................................................................87
Figure 81 Flow Chart..............................................................................................................87
Figure 82 Self-Criticism.........................................................................................................91
Figure 83 Guntt Chart.............................................................................................................92

List of Table

23 | P a g e Database Design Development F.M.FAIROOS

Table 1 Normalization............................................................................................................33
Table 2 2nd Normal Form......................................................................................................35
Table 3 Equipment................................................................................................................. 36
Table 4 Staff Table................................................................................................................. 36
Table 5 Equipment Type Table.............................................................................................. 36
Table 6 Equipment Type........................................................................................................ 36
Table 7 Customer................................................................................................................... 36
Table 8 Equipment Quantity...................................................................................................36
Table 9 Test Form.................................................................................................................. 68
Table 10 Feedback..................................................................................................................73
Table 11 Software requirements client PC.............................................................................79
Table 12 Hardware requirements client PC............................................................................79
Table 13 Hardware Requirements Client PC..........................................................................80

24 | P a g e Database Design Development F.M.FAIROOS

Activity-01

1.1 Identify the user and system requirements to design a

database for the above 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 normalized. If not, normalize the
database by removing the anomalies.

1.1.1 Introduction of the company

Every day, technology is evolving. Everyone wants it to significantly simplify their daily
lives compared to the conventional approach. In this instance, a business called "Polly pipe"
is a supplier and installation of water sports in Braintree, England. The business uses a file-
based paper records system to keep the records of its employees and customers. To keep their
records in this manner is a really irritating thing. They engaged me as a database developer to
create a database system to replace this conventional approach. The key advantage of paper
records systems is security and access control. However, it faces many more challenges than
a database system. Disaster recovery, for example, requires a large area to maintain and is
physically inaccessible. I intended to create a relational database system to keep the
company's information as a database system based on their needs. The database system must
then be tested to ensure that it is fully operational and meets Polly Pipe Company criteria.
The system and its functions should next be thoroughly documented and given to the
respectable individual in their organization. In this material, any potential future
developments are also addressed in detail.

25 | P a g e Database Design Development F.M.FAIROOS

1.1.2 Identify the user and system requirements to design a database for the
above scenario.
Computer requirements are descriptions of the services that a computer must deliver as well
as obstacles to its functioning. The program's prerequisites for usage and benefits are defined
by the regulations. No matter how brilliant the idea and design are, project failure might
occur if they are not clear, or the analysis is not done appropriately. Identifying requirements
refers to the process of identifying, assessing, documenting, and verifying these services and
controls.

1.1.2.1 User requirements

User requirements, also known as user needs, explain what the user accomplishes with the
system, such as what activities users must be able to complete. User needs are often
expressed in narrative form in a User Requirements Document (URD). User requirements are
often signed off by the user and utilized as the main input for establishing system
requirements. Identifying what the user genuinely wants a software product to achieve is a
critical and tough phase in the design process. This is because the user is often unable to
articulate all of their requirements and desires, and the information they supply may also be
inadequate, erroneous, and self-conflicting. The business analyst is responsible for properly
knowing what the consumer wants. As a result, user needs are often evaluated independently
from system requirements. The business analyst carefully evaluates user needs and builds and
publishes a set of high-quality system requirements, ensuring that the requirements fulfil
particular quality criteria (Parker, 2012)

1.1.2.2 System requirements

System requirements are the building elements that developers utilize to construct the system.
These are the conventional "must" sentences that define what the system "shall do." System
needs are characterized as either functional or supplementary. A functional requirement
outlines anything that a user requires to do their job. For example, a system may be needed to
input and publish cost estimates; this is a functional requirement. Supplemental or non-
functional requirements define any remaining needs that are not addressed by the functional
requirements. I like the phrase additional requirements over non-functional requirements;
who wants to be labelled non-functional Supplemental requirements are sometimes known as
quality of service requirements. The system design has a thorough strategy for accomplishing

26 | P a g e Database Design Development F.M.FAIROOS

functional requirements. The system design includes a thorough strategy for implementing
extra needs. The list below displays numerous sorts of additional requirements.

1.1.2.2.1 Functional requirements

A system's functional requirements specify what the system should perform. These needs are
determined by the kind of software being built, the intended users of the product, and the
organization's general approach to defining requirements. Functional requirements, when
stated as user requirements, should be articulated in normal language so that system users and
management can comprehend them. Functional system requirements are developed for
system developers and extend on user needs. They must thoroughly explain the system's
functions, inputs and outputs, and exceptions. Functional system requirements range from
basic needs that encompass what the system should perform to extremely particular
requirements that represent local methods of working or existing systems in an organization.
Here are some examples of functional requirements for the Mentcare system, which is used to
keep track of people seeking treatment for mental health issues (collegenote.net, 2021).

 A user must be able to search all clinic appointment listings.

 Each day, the system will create a list of patients who are anticipated to attend
appointments that day for each clinic.

 Each employee who uses the system must be identifiable by an eight-digit employee
number.

1.1.2.2.2 Non-functional requirements

Non-functional requirements, as the name implies, are those that are not directly related with
the particular services provided by the system to its users. Non-functional requirements often
establish or limit system-wide features. They may be related to emergent system attributes
such as dependability, reaction time, and memory use. Alternatively, they may establish
limits on system implementation, such as the capabilities of I/O devices or the data
representations used in interfaces with other systems. Non-functional needs are often more
important than particular functional requirements. System users can typically discover
methods to work around a system feature that does not fully satisfy their demands. Failure to
satisfy a non-functional need, on the other hand, may render the whole system inoperable.
For example, if an aircraft system fails to achieve its reliability standards, it will not be
certified as safe for operation; if an embedded control system fails to fulfill its performance
requirements, the control functions will not work properly. While it is generally
27 | P a g e Database Design Development F.M.FAIROOS
straightforward to determine which system components execute certain functional needs (for
example, formatting components may implement reporting requirements), this is sometimes
more challenging with non-functional requirements. The execution of these criteria may be
dispersed across the system for two reasons:

 Non-functional needs may have an impact on a system's overall design rather than
specific components. To guarantee that performance requirements are satisfied in an
embedded system, for example, the system may need to be organized to reduce
communications between components.

 A single non-functional demand, such as a security need, may produce multiple

associated functional requirements that describe new system services that must be
developed in order for the non-functional requirement to be realized.

1.1.2.2.3 System requirements

 Software Requirements for Polly Pipe Server
1. Windows Server 2019
2. Microsoft SQL Server Management Studio
3. Microsoft Office 365

 Hardware requirements for Polly Pipe Server

1. Memory: 64GB GB DDR4 ECC
2. Processor: Intel® Xeon® E-2274G Processor (8M Cache, 4.00 GHz) to 4.90GHz.
3. Hard disk: 2TB HDD and 512 SSD

 Software Requirements for Polly Pipe Client PC

1. Windows 11
2. Microsoft Office 365

 Hardware Requirements Polly Pipe Client PC

1. Processor: Intel® Core™ i5-6500
2. Memory: 8GB
3. Hard Disk: 256GB HDD

28 | P a g e Database Design Development F.M.FAIROOS

1.1.3 Entity relationship diagram (ERD)
Entity Relationship Diagram (ER Diagram), often known as ERD, is a diagram that depicts
the relationship between entity sets contained in a database. In other words, ER diagrams aid
in explaining the logical structure of databases. Entities, attributes, and relationships form the
foundation of ER diagrams. ER Diagrams feature several symbols that employ rectangles to
represent entities, ovals to describe characteristics, and diamond shapes to show relationships.
An ER diagram seems to be a flowchart at first glance. However, the ER Diagram has
numerous specific symbols, and their meanings distinguish this model. The ER Diagram is
used to depict the entity framework architecture. (Peterson, 2021)
The database structure is shown by an entity relationship diagram. Entities (tables in
relational databases) and connections between tables are shown in the entity relationship
diagram. An entity relationship diagram is essential for a successful database architecture.
The essential components of an entity connection diagram are:

 Entities (tables) (tables)

 Attributes (table columns) (table columns)

 Relationships (relationships between tables) (relationships between tables)

Entity
A defined item that can have data saved about it, such as a person, object, idea, or event.
Consider entities to be nouns. A consumer, a pupil, a vehicle, or a product are some
examples. Typically shown as a rectangle.
Entity type: A set of distinguishable items, such as students or athletes, in which the entity is
the student or athlete. Other examples include consumers, automobiles, and merchandise.
Entity set: Similar to an entity type, but specified at a certain moment, such as students
enrolled in a class on the first day. Other instances include: Customers who bought autos last
month had them registered in Florida. A similar phrase is instance, which refers to the
individual person or automobile as an instance of the entity collection.
Entity classification: Entities are classified as strong, weak, or associative. A strong entity's
characteristics may be used to define it, but a weak entity cannot. An associative entity
connects entities (or elements) in a set.

29 | P a g e Database Design Development F.M.FAIROOS

Relationship
A property or characteristic of an entity. Often shown as an oval or circle.
The attribute is used to describe the property of an entity. Eclipse is used to represent an
attribute.
For example, id, age, contact number, name, etc. can be attributes of a student.
a. Key Attribute
The key attribute is used to represent the main characteristics of an entity. It represents a
primary key. The key attribute is represented by an ellipse with the text underlined.
b. Composite Attribute
An attribute that composed of many other attributes is known as a composite attribute. The
composite attribute is represented by an ellipse, and those ellipses are connected with an
ellipse.
c. Multivalued Attribute
An attribute can have more than one value. These attributes are known as a multivalued
attribute. The double oval is used to represent multivalued attribute.
For example, a student can have more than one phone number.
d. Derived Attribute
An attribute that can be derived from another attribute is known as a derived attribute. It can
be represented by a dashed ellipse.
For example, a person's age changes over time and can be derived from another attribute like
Date of birth.

Relationship
A relationship is used to describe the relation between entities. Diamond or rhombus is used
to represent the relationship.
Types of relationship are as follows:
a. One-to-One Relationship
When only one instance of an entity is associated with the relationship, then it is known as
one to one relationship.
For example, a female can marry to one male, and a male can marry to one female.

30 | P a g e Database Design Development F.M.FAIROOS

b. One-to-many relationship

When only one instance of the entity on the left, and more than one instance of an entity on
the right associates with the relationship then this is known as a one-to-many relationship.
For example, Scientist can invent many inventions, but the invention is done by the only
specific scientist.
c. Many-to-one relationship
When more than one instance of the entity on the left, and only one instance of an entity on
the right associates with the relationship then it is known as a many-to-one relationship.
For example, Student enrols for only one course, but a course can have many students.

d. Many-to-many relationship

When more than one instance of the entity on the left, and more than one instance of an entity
on the right associates with the relationship then it is known as a many-to-many relationship.
For example, Employee can assign by many projects and projects can have many employees.
(javatpoint, 2021)

31 | P a g e Database Design Development F.M.FAIROOS

1.1.4 Entity Relationship Diagram for Polly Pipe Company

Figure 1 ER Diagram

1.1.5 Logical schema for Polly Pipe Company

Figure 2 Logical schema

32 | P a g e Database Design Development F.M.FAIROOS

1.1.6 Normalization
The process of structuring data in a database is known as normalization. This comprises the
creation of tables and the establishment of linkages between those tables in accordance with
rules aimed to preserve the data while also making the database more adaptable by avoiding
duplication and inconsistent reliance. Redundant data consumes disk space and causes
maintenance issues. If data that exists in more than one location to be modified, the data must
be changed in all places in the same manner. A change in a customer's address is significantly
simpler to implement if the data is only saved in the Customers table and nowhere else in the
database.
A huge database designed as a single relation may result in data duplication. This data
repetition might lead to (avatpoint, 2022)

 Making relationships incredibly big.

 It is difficult to manage and update data since it requires searching several entries in
relation.

 Waste of disk space and resources.

 The chance of mistakes and inconsistencies grows.

 To address these issues, we should examine and deconstruct the relations with redundant
data into smaller, simpler, and well-structured relations that fulfill desired qualities.
Normalization is the process of decomposing relations into relations with fewer
properties.
First normal form (1NF)
A relation breaks the first normal form if it has composite or multi-valued attributes, or the
first normal form is met if the relation doesn't have any composite or multi-valued attributes.
If all of the attributes in a relation are solitary valued attributes, the connection is said to be in
first normal form.
 There is a table in 1 NF:
 Only Single Valued Attributes exist.
 The attribute domain stays the same.
 Every Attribute/Column has a distinct name.
 It is not important in whatever sequence the data are saved.

33 | P a g e Database Design Development F.M.FAIROOS

Table 1 Normalization

Installation Installation Installation CustomerID EID E_Qty STypeID S_Qty Period of

ID Type Name and Staff
Address assignment
234 fresh water Oak House, 17 C01 E03 2 ST05 1 From 1st
tropical Wroxton Road, September
Hertfordshire, 2012
H5 667
234 fresh water Oak House, 17 C01 E01 1 ST03 1 From 1st
tropical Wroxton Road, September
Hertfordshire, 2012
H5 667
234 fresh water Oak House, 17 C01 E02 1 ST06 1 From 1st
tropical Wroxton Road, September
Hertfordshire, 2012
H5 667
654 fresh water Bayliss House, C02 E03 2 ST05 5 1st June 2005
Cold Orange Street, – 1st June
Kent, K7 988 2011
654 fresh water Bayliss House, C02 E01 1 ST02 1 1st June 2005
Cold Orange Street, – 1st June
Kent, K7 988 2011
654 fresh water Bayliss House, C02 E04 ST03 1 1st June 2005
Cold Orange Street, – 1st June
Kent, K7 988 2011
654 fresh water Bayliss House, C02 E02 2 ST01 1 1st June 2005
Cold Orange Street, – 1st June
Kent, K7 988 2011
654 fresh water Bayliss House, C02 ST07 3 1st June 2005
Cold Orange Street, – 1st June
Kent, K7 988 2011
767 Marine Eaglestone C03 E01 2 ST05 10 From 30th
Castle, June 2012
Eaglestone,
Kent
767 Marine Eaglestone C03 E05 500 ST02 2 From 30th
Castle, June 2012
Eaglestone,
Kent
767 Marine Eaglestone C03 ST03 1 From 30th
Castle, June 2012
Eaglestone,
Kent
767 Marine Eaglestone C03 ST01 1 From 30th
Castle, June 2012
Eaglestone,
Kent
767 Marine Eaglestone C03 ST07 3 From 30th

34 | P a g e Database Design Development F.M.FAIROOS

 Castle, June 2012
Eaglestone,
Kent
943 Marine 23 Sackville C04 E03 2
Street, Wilts.
W55
943 Marine 23 Sackville C04 E01 1
Street, Wilts.
W55
943 Marine 23 Sackville C04 E02 1
Street, Wilts.
W55
157 Freshwater Humbertson C03 E03 2 ST03 1 1st
Tropical Castle, Kent, K8 September
2005 – 1st
September
2012
157 Freshwater Humbertson C03 E01 1 1st
Tropical Castle, Kent, K8 September
2005 – 1st
September
2012
157 Freshwater Humbertson C03 E02 3 1st
Tropical Castle, Kent, K8 September
2005 – 1st
September
2012

Second Normal Form

On the idea of complete functional dependence, a second normal form (2NF) is founded.
Relations with composite keys, or those having a main key made up of two or more qualities,
fall under the second normal form. A relation with a single primary key attribute is

35 | P a g e Database Design Development F.M.FAIROOS

automatically in at least 2NF. The update anomalies may impact a relation that is not in 2NF.
In order to be in second normal form, a relation must be in first normal form and not have any
partial dependencies. If a relation has No Partial Dependency that is, if no non-prime attribute
that is, if no attribute that is not a component of any candidate key is reliant on any suitable
subset of any candidate key in the table, then the relation is in the 2NF category.
Installation Installation Name Period of Staff assignment Installation CustomerID
ID and Address Type
234 Oak House, 17 From 1st September 2012 fresh water C01
Wroxton Road, Tropical
Hertfordshire, H5
667
654 Bayliss House, 1st June 2005 – 1st June 2011 fresh water C02
Orange Street, Kent, Cold
K7 988
767 Eaglestone Castle, From 30th June 2012 Marine C03
Eaglestone, Kent
943 23 Sackville Street, Marine C04
Wilts. W55
157 Humbertson Castle, 1st September 2005 – 1st Freshwater C03
Kent, K8 September 2012 Tropical

Table 2 2nd Normal Form

Third Normal Form.

If a relation is both in second normal form and third normal form, there is no transitive
dependence for non-prime characteristics.

36 | P a g e Database Design Development F.M.FAIROOS

 Table 3 Equipment

Equipment
Table 4 Staff Table
EID Eq_Name Eq_TypeID
Staff
E1 20-gallon tank E01
Staff Number SName STypeID
E2 50-gallon tank E01
SHA1 Dave Clark ST01
E3 100-gallon tank E01
E4 200-gallon tank E01 SHA8 John Smith ST02
E5 400-gallon tank E01 SHA2 Freddy Davies ST03
E6 Standard E02 SHA11 McCloud ST03
E7 Super E02 SHA23 Satpal Singh ST01
E8 Standard E03 SHA66 Winstn Kodogo ST03
E9 Super E03 SHA55 Alison Smith ST04
E10 Air Driven E04
E11 Under gravel E04
Table 5 Equipment Type Table
Table 6 Equipment Type

Table 7 Customer
Customer Equipment Type
CustomerID C_Name Eq_TypeID Eq_Type
C01 Lee A. sun E01 Tanks
C02 Sally Dench E02 Thermostats Table
C03 Perry Vanderrune 8 E03 Air Pumps
C04 Eric Mackintosh E04 Filters
Equipment Quantity E05 Wood Panels

Equipment Quantity
Installation ID EID E_Qty
234 E03 2
234 E01 1
234 E02 1
654 E03 2
654 E01 1
654 E04 1
654 E02 2
767 E01 2
767 E05 1
943 E03 2
943 E01 1
943 E02 1
157 E03 2
157 E01 1

37 | P a g e Database Design Development F.M.FAIROOS

1.1.7 Assess the effectiveness of the design.
Above interface is designed in C# with the help of Microsoft visual studio. Every tables are
separated in each page and the main menu for the installation, staff were created. Back
button, Exit button and Main menu button were created to simplify the design. Those buttons
will helpful to users to navigate the pages for their needs. From the user requirements, users
can Update, Delete, Save and retrieve the information they add. Moreover they can view all
data from the specific table by these controls. From the system requirements, there are 8
tables including Payment, Installation, Staff, Equipment and Customer. For the ease of
access, users can navigate from one table to another by clicking back and main menu buttons.

1.1.8 Data Validation

Data validation is often brought up while talking about databases and is of highest
importance. Since information is regularly changed, deleted, questioned, or moved, valid data
is crucial. When fundamental data validation standards are adhered to, databases become
more valuable, consistent, and helpful to their users. Data validation is the part of a database
that ensures data consistency while using SQL. Data integrity is primarily determined by
constraints, referential integrity, and the capacity to delete and modify data. Check, unique,
not null, and main constraints are the four fundamental types of constraints in SQL. In order
to ensure that a statement on the data is correct for each row in a table, check constraints are
needed. The unique constraint ensures that no two rows will have the same values in any of
their columns. The not null constraint on a column specifies that data must exist in that
column. However, only one column in SQL may be used to satisfy the not null criterion. The
unique constraint and the not null constraint are combined to form the primary key constraint,
which makes sure that no two rows have the same values in the same columns and that each
column has data. Referential integrity, an important aspect of data integrity, is often
connected to the lookup table and the data table. Referential integrity is often preserved when
data is added, withdrawn, or modified. Insertions and updates to the data table's foreign key
column are prevented by referential integrity. Data that is not available in the lookup table
cannot be inserted into the foreign key column due to referential integrity. However,
referential integrity only allows for inserts and updates when the modified data is already
available in the lookup table. Referential integrity also prohibits changes and deletions when
the lookup table's information is absent from the data table's foreign key column.
(databasemanagement, 2022)

38 | P a g e Database Design Development F.M.FAIROOS

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.

1.2.1 Graphical user interface design

Login interface

Figure 3 Login interface

Customer Interface

Figure 4 Customer Interface

39 | P a g e Database Design Development F.M.FAIROOS

Employee Interface

Figure 5 Employee Interface

Employee Type Interface

Figure 6 Employee Type Interface

40 | P a g e Database Design Development F.M.FAIROOS

Equipment Interface

Figure 7 Equipment Interface

Equipment Type Interface

Figure 8 Equipment Type Interface

Facility interface
41 | P a g e Database Design Development F.M.FAIROOS
 Figure 9 Facility interface
Installation Interface

Figure 10 Installation Interface

Installation Type interface

42 | P a g e Database Design Development F.M.FAIROOS

 Figure 11 Installation Type interface

Payment Interface

Figure 12 Payment Interface

43 | P a g e Database Design Development F.M.FAIROOS

 Figure 13 Dashboard

44 | P a g e Database Design Development F.M.FAIROOS

Activity 2.0

2.1Develop 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.
IDE

Figure 14 Visual Studio IDE

Users of the Visual Studio IDE may quickly and properly create their code by navigating
the UI with ease. Developers also have access to a wide variety of debugging tools using the
Visual Studio IDE. These aid them in easily diagnosing and profiling issues. They may
deploy their apps with confidence knowing they have eliminated everything that could lead
to performance issues in this way. Additionally, the Visual Studio IDE functions as a testing
environment. Developers may use this to model how their apps will function in the target
settings and to make sure they function correctly after deployment. It's important to
remember that almost no service in the IDE Software category will be the perfect answer
able to satisfy the requirements of various business kinds, sizes, and sectors. It could be a
good idea to first read some Visual Studio IDE IDE Software reviews because certain
services might excel just in a very limited range of applications or be designed with a very
specific sort of industry in mind. Others may be designed with the intention of being simple
and straightforward, lacking the complex features that more seasoned users like.

45 | P a g e Database Design Development F.M.FAIROOS

2.1.1 SQL Commands
 SQL statements are directives. It is used for database communication. Additionally, it is
utilized to carry out certain duties, activities, and data inquiries.

 Table creation, data addition, table deletion, table modification, and user permission
setting are just a few of the many activities that SQL is capable of. (javatpoint, 2021)

Types of SQL Commands

There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.

Figure 15 SQL Commands

2.1.1.1 Data Definition Language (DDL)

 DDL changes the structure of the table like creating a table, deleting a table, altering a
table, etc.

 All the commands of DDL are auto committed that means it permanently save all the
changes in the database.
Here are some commands that come under DDL:
 CREATE
 ALTER
 DROP
 TRUNCATE

46 | P a g e Database Design Development F.M.FAIROOS

CREATE
In the data warehouse, you may add new items using the CREATE statement. Tables,
schemas, views, and functions are the most typical objects produced using this command.
Running Generate statements has less danger than using the DROP, ALTER, and
TRUNCATE commands since you can always delete what you create. It's important to have a
clear idea of the column names and data types you intend to use when creating tables and
views using the CREATE command. Although establishing tables and views using the
CREATE command may be tedious and repetitious, particularly if the schema objects have
several columns, it is an efficient approach to add new objects to a database. The actual data
that goes into a table may be added using DML INSERT statements and/or a transformation
tool like dbt after it has been created.
The generic syntax to use the CREATE command is as follows:
CREATE <database object type> <database object name>;

Creating a table using the CREATE statement may look a something like this:
CREATE TABLE prod.jaffle_shop.jaffles (
id varchar(255),
jaffle_name varchar(255)
created_at timestamp,
ingredients_list varchar(255),
is_active Boolean );
ALTER
Using the ALTER DDL command, you can change an object in your database that already
exists. By "change", we specifically mean you can:

 Columns in views and tables may be added, deleted, and renamed.

 rename a table or view

 Change the view's or table's structure

 and more!

The generic syntax to use the ALTER command is as follows:

ALTER <database object type> <database object name>;

47 | P a g e Database Design Development F.M.FAIROOS

To alter a table’s column, you may do something like this:
ALTER TABLE customers rename column last_name as last_initial;

DROP
The DROP signals. The riskiest DDL statement a person can perform. one that has to be
handled carefully at all times. An executed DROP command will fundamentally delete that
item from the data warehouse. Tables, views, schemas, databases, users, functions, and more
may all be dropped. To warn you about the consequences of removing a table, view, or
schema before it is actually dropped, certain data warehouses, like Snowflake, let you apply
constraints to DROP statements. Since raw source tables are often your first point of truth in
practice, we advise against removing them. To dump database items, typically your database
user must have the appropriate rights.
The syntax to use the DROP command is as follows:
DROP <database object type> <database object name>;
You can drop your customer table like this:
DROP TABLE customers;

TRUNCATE
A table's whole row structure will be deleted using the TRUNCATE command while the
underlying table structure is preserved. Only database table objects are eligible for the
TRUNCATE command. Unlike DROP commands, TRUNCATE statements only remove the
data from a table, not the table itself, from the database.
The syntax to use the TRUNCATE command is as follows:
TRUNCATE TABLE <table name>;
You can truncate your jaffle_shop’s payments table by executing this statement:
TRUNCATE TABLE payments;

48 | P a g e Database Design Development F.M.FAIROOS

2.1.1.1.1 Create Table

Customer Care Table

Figure 16 Customer Care Table

Installation Table

Figure 17 Installation Table

Installation Type Table

Figure 18 Installation Type Table

Employe Type table

Figure 19 Employe Type table

Equipment

Figure 20 Equipment Table

Equipment Type

Figure 21 Equipment Type Table

Facilities

Figure 22 Facilities Table

49 | P a g e Database Design Development F.M.FAIROOS

Payment

Figure 23 Payment Table

Employe Table

Figure 24 Employe Table

2.1.1.1.2 ALTER
Adding new attribute to Customer table in mailaddress.

Figure 25 Before ALTER Table in Customer

Figure 26 Alter Table Query

Figure 27 After ALTER Table in Customer

50 | P a g e Database Design Development F.M.FAIROOS

Adding new attribute to Employee table in E-mail address.

Figure 28 Before ALTER Table in Employe

Figure 29 Alter Table Query

Figure 30 After ALTER Table in Employee

51 | P a g e Database Design Development F.M.FAIROOS

 2.1.1.1.3 DROP

Figure 31 Before Drop Table in Customer

Figure 32 Drop Table Query

Figure 33 After Drop Table in Customer

2.1.1.2 Data Manipulation Language

The operations used to add, remove, and update data in a database are collectively referred to
as data manipulation language, or DML. A DML is often a sublanguage of a larger language,
such as SQL a DML includes some of the operators in the language. Given that certain users
may do both read and write selection, choosing read-only data is linked and sometimes also
regarded as a DML component. Structured Query Language, sometimes known as SQL, is a
common language for manipulating data that is stored in a relational database. You can use
SQL to construct databases and execute operations on existing ones. The necessary

52 | P a g e Database Design Development F.M.FAIROOS

operations are performed using SQL using commands like Create, Drop, Insert, etc. Data
Manipulation Language is referred to as DML. represents a group of computer languages that
are specifically used to modify the database, including:

 To create, read, update, and remove data, use CRUD operations.

 use the verbs INSERT, SELECT, UPDATE, and DELETE.

 DML instructions are often a component of a more comprehensive database language,

like SQL (structured query language). To handle data in that language, these DMLs
may have a particular syntax.
The two basic categories of DML are declarative programming, sometimes known as non-
procedural programming, and procedural programming. The majority of SQL queries,
including those dealing with manipulating data already stored in the database, are part of the
DML, or Data Manipulation Language.

Examples of DML Commands:

 INSERT is a database command that adds new or updated data.
 UPDATE: A database operation that modifies or updates existing or current data to a
more recent value.
 DELETE: A database command used to remove values or other data from the active
table.
A crucial aspect of data management is database administration. After the databases are put
up, they need to be monitored and maintained. Based on the performance necessary to
maintain

respectable response times in the database queries that users run to extract information from
the data contained in them, database administrators will carry out this action. (satoricybe,
2021)

53 | P a g e Database Design Development F.M.FAIROOS

2.1.1.1.1 Insert

Customer Table (data insert, update, Delete)

Figure 34 Before Insert data in Customer Table

Figure 35 Data insert in Customer Table

54 | P a g e Database Design Development F.M.FAIROOS

 Figure 36 After Insert data in Customer Table

Figure 37 Data Successfully Store in Customer Table is SQL

Figure 38 Insert Coding

55 | P a g e Database Design Development F.M.FAIROOS

2.1.1.1.2 Update

Figure 39 Before Update data in Customer Table

Figure 40 Data Update in Customer Table

56 | P a g e Database Design Development F.M.FAIROOS

 Figure 41 After Update data in Customer Table

Figure 42 Data Successfully Update in Customer Table is SQL

Figure 43 Update CODING

57 | P a g e Database Design Development F.M.FAIROOS

2.1.1.1.3 Delete

Figure 44 Before Delete data in Customer Table

Figure 45 Data Delete in Customer Table

58 | P a g e Database Design Development F.M.FAIROOS

 Figure 46 After Delete data in Customer Table

Figure 47 Data Successfully Delete in Customer Table is SQL

Figure 48 Delete Coding

59 | P a g e Database Design Development F.M.FAIROOS

2.1.1.3 Commands

BETWEEN Command

Figure 49 BETWEEN Command

Group by Command

60 | P a g e Database Design Development F.M.FAIROOS

 Figure 50 Group by Command

Having Command

Figure 51 Having Command

In Command

Figure 52 In Command

61 | P a g e Database Design Development F.M.FAIROOS

Order by Command

Figure 53 Order by Command

Select Command

62 | P a g e Database Design Development F.M.FAIROOS

 Figure 54 Select Command
Where Command

Figure 55 Where Command

Update Command

Figure 56 Update Command

63 | P a g e Database Design Development F.M.FAIROOS

2.1.1.4 Join Quarry
The SQL Joins clause is used to combine records from two or more tables in a database. A
JOIN is a means for combining fields from two tables by using values common to each.
Different types of Joins are
 INNER JOIN
 LEFT JOIN
 RIGHT JOIN
 FULL JOIN

INNER JOIN
Only relevant data from the two connected tables will be retained by the inner join. The
table that results from an INNER JOIN will be the green highlighted area below where the
two tables overlap, if you see the two tables as a Venn diagram (Magoni, 2021)

Figure 57 INNER JOIN

LEFT OUTER JOIN in SQL
The unconnected data from the left (first) table will be retained by the LEFT OUTER JOIN,
also known as the Left Join. The resultant table, which contains both the
common/overlapping area and the remaining portion of the left circle, is the green
highlighted part of a Venn diagram with two circles.

64 | P a g e Database Design Development F.M.FAIROOS

 Figure 58 LEFT JOIN

RIGHT OUTER JOIN in SQL

The data from the second table that is unrelated to the first table will be retained by the
RIGHT OUTER JOIN, or simply Right Join. Imagine it as a Venn diagram with two
concentric circles; the table is the green-highlighted portion that contains both the
overlapping portion and the remaining portion of the right circle.

Figure 59 RIGHT JOIN

FULL OUTER JOIN in SQL

The FULL OUTER JOIN may be compared to a Left Join and Right Join combined. Both
tables' rows will be retained in their entirety, and any blank spaces will be filled in with
NULL. Imagine it as a Venn diagram with two circles; the table that results is the green
highlighted portion that contains everything, including the overlapped area, the left circle,
and the right circle.

Figure 60 FULL JOIN

65 | P a g e Database Design Development F.M.FAIROOS

2.2 Explain the Database Security Mechanism
You can protect your databases with basic recommended practices even though there are
many different ways to data security. With the aid of these database security best practices,
you may increase data protection while reducing your exposure to risk. These strategies may
be used alone, but they function best when combined to guard against a variety of situations
that might jeopardize database security. (Clawson, 2021)
Physical database security
The actual hardware that is used to store, manage, and alter data must not be disregarded. No
matter whether the database server is located on-site or in the cloud, physical security
involves securing the room where it resides. Additionally, security personnel will be
watching over physical access to that equipment. The implementation of database backup and
disaster recovery plans in the event of a natural disaster is a critical component of this best
practice. Additionally, it's crucial to avoid hosting web servers and apps on the same server as
the database that the company wishes to keep safe. In order to ensure data security even if a
system's physical storage is stolen or hacked, it is also important to have data "encrypted at
rest," as is indicated below.
Web applications and firewalls
At the perimeter layer, web applications and firewalls are recommended practices for
database security. A database firewall stops unauthorized users from connecting to your IT
network through the internet; firewalls are an essential need for safeguarding your critical
data. Application access control software helps safeguard web applications that communicate
with databases. This database security mechanism controls who can access online
applications and how they may do so, comparable to access control lists. The same
advantages of standard firewalls are also provided by firewalls for specific online apps.
Database encryption
Because it is used where the data reside in the database, data encryption is one of the best
database security techniques. Organizations may, however, encrypt data both in transit and at
rest to ensure its security as it moves across their IT systems. When data is encrypted, it is
changed such that it only makes sense when it is decoded using the right keys. Because of
this, even if someone were to obtain encrypted data, it would be useless to them. Maintaining
database encryption is essential for protecting data privacy and may help with IoT security.

66 | P a g e Database Design Development F.M.FAIROOS

Manage passwords and permissions
Maintaining database security requires managing passwords and permissions. Dedicated
security personnel or IT teams are often in charge of this responsibility. This recommended
practice sometimes requires access control lists. Organizations may use a variety of
techniques to manage passwords, including the use of dual- or multiple-factor authentication
protocols and limiting the length of time users have to enter their credentials. The access and
permissions lists must, however, be updated often as a result of this approach. Although it
might take some time, the outcomes are worthwhile. Look for "as a service" solutions and
federated identification and authentication wherever feasible to lower risk.
Isolate sensitive databases.
If important databases are segregated, database security is exceedingly difficult to breach.
Unauthorized users may not even be aware that sensitive material exists, depending on how
the isolation measures are used. To isolate critical databases and make sure they don't seem to
be on a specific user's network, software-defined perimeters are a handy tool. This method
makes it difficult for lateral movement attacks to seize control of databases; it also works
well against zero-day attacks. One of the most effective methods to reinforce database
security at the access level is through isolation techniques. Competitive isolation solutions
include this strategy together with encryption and key management for database layer
security.
Change management.
It is necessary to outline the steps that must be taken to protect databases during change as
part of the change management process, preferably in advance. Mergers, acquisitions, or just
new users having access to diverse IT resources are examples of changes. For safe access to
databases and the programs that use them, it is vital to record the modifications that will be
made. Along with their data flows, it's crucial to identify all the IT systems and applications
that will make use of that database.
Database auditing
Regularly reviewing the log files for databases and the programs that use them is often
required for database audits. This data shows who used what app or repository, when they did
so, and what they did there. By notifying database administrators in time, timely audits may
help decrease the overall effect of breaches if there is illegal access to data. Organizations
have more time to inform any affected customers and minimize harm if they can respond to

67 | P a g e Database Design Development F.M.FAIROOS

data breaches more quickly. The last line of defense is database auditing, which offers
centralized control over database security

Activity 3.0

3.1 Test Plan

Test Plan
The strategy, goals, timetable, estimate, deliverables, and resources needed to carry out
testing on a software product are all described in detail in a test plan. The effort necessary to
verify the application's quality is determined with the aid of the test plan. The test manager
carefully monitors and manages the software testing process according to the test plan,
which serves as a guide. (Hamilton, 2021).

3.1.1 Methods of Testing

1. Black Box Testing
The strategy, goals, timetable, estimate, deliverables, and resources needed to carry out
testing on a software product are all described in detail in a test plan. The effort necessary to
verify the test is estimated by the test plan. Black box testing includes evaluating a system
without being aware of how it operates inside. A tester inputs data and monitors the output
produced by the system being tested. This allows for the identification of the system's
reaction time, usability difficulties, and reliability concerns as well as how the system reacts
to anticipated and unexpected user activities. Because it tests a system from beginning to
finish, black box testing is a potent testing method. A tester may imitate user action to check
if the system fulfills its promises, much as end users "don't care" how a system is
programmed or designed and expect to get a suitable answer to their requests. A black box
test assesses every important subsystem along the route, including the UI/UX, database,
dependencies, and integrated systems, as well as the web server or application server
(imperva, 2021)
 It is simple and is performed in the early stages of development.
 Cost of developing test-cases is less as compared to white-box testing.

68 | P a g e Database Design Development F.M.FAIROOS

 Figure 61 Black Box Testing
2. White Box Testing
White Box Testing deals with the internal structure of the database and the specification
details are hidden from the users. It involves the testing of database triggers and logical
views, which are going to support database refactoring It conducts module testing for
database functions, triggers, views, and SQL queries, among others. This form of testing
verifies database table structures, data models, database schema, etc. It verifies Referential
integrity rules. It selects table default settings to ensure database consistency.
White-box testing can detect coding problems, allowing internal database bugs to be
eliminated. (tutorialspoint, 2021).

3.1.2 Provide a suitable test plan to test the system against user and system
Requirements.
Table 9 Test Form

Testing From

Project Name – Poly Pipe Water DBMS

Test Case ID: 01 Test Name – Unit Testing

Date – 2021-10-12 Time – 1.00 PM

ID Test Expected Results Actual Results Pass or Fail

01 Using the right username and Start the machine and system opened, Pass
password, log in to the system. display the dashboard dashboard displayed

02 Use the erroneous username and System cannot access System is not Pass
password to get into the system. the System permitted to access the
System

03 Put the information in the system. System Displays System Displays Pass
Inserted Messages Successful Message
Successfully Insertion

04 System data should be updated. The system displays The system displays Pass
updates that were updates that were
successful. successful.

05 the system's data should be deleted. The system displays The system displays Pass
correctly. Discard deleted
69 | P a g e D a t a b a s e messages
D e s i g n D e v e communications.
lopment F.M.F AIROOS
Test Input Data Output Result Result
C.NO Data

01 Log in Insert Testing

Process username successful
and
password

02 Show After enter Testing

Main Insert successful
Menu username
and
password
system show
dashboard

70 | P a g e Database Design Development F.M.FAIROOS

03 Insert Insert Testing
Details records successful

04 Update Upd Testing

details ate successful
reco
rds

71 | P a g e Database Design Development F.M.FAIROOS

 05 Delete Del Testing
details ete successful
reco
rds

3.1.3 Explain how the selected test data can be used to improve the
effectiveness of testing.
Will frequently verify a database system ensure that the most item that is sent is without a
doubt devoid of errors, flaws, and errors. This will aid in minimizing a variety of issues that
might arise during a client's vacation. The Client won't have to deal with these issues until the
database framework is created, which is impractical since it would obstruct the completion of
the assignment for that day and make workflow difficult for that representative. As a result,
system testing has to be carried out. Every framework can be made better, and the one that
was created is no exception. The program's functionality in an inquiring environment will be
assessed by testing with various data kinds, looking for duplicate data input, and examining
how the system manages large amounts of data input. However, the testing serves to validate
the idea and is suitable for use in a test setting.

72 | P a g e Database Design Development F.M.FAIROOS

3.1.4 Effectiveness of Testing
As successfully testing is conducted or a goal is attained that satisfies a client demand is
referred to as test effectiveness. The SDLC (Software Development Life Cycle) begins with
the requirements gathering phase, during which the development team creates the software
application based on the SRS (Software Requirements Specification) and FRD (Functional
Requirements Document) that were created. At the same time, the testing team creates test
cases from the SRS and FRD documents. The process of developing test cases, carrying them
out, and determining their efficacy begins as soon as development is finished. A defect could
be legitimate or not. While invalid flaws must be closed or disregarded, legitimate ones must
be addressed in the program or product. Therefore, theoretically, it is determined as a
percentage of the number of legitimate faults rectified in software program divided by the
total of the defects injected plus the total of the defects escaped.

3.1.4.1 Test Efficiency

The most effective technique to use the software project's resources to accomplish an
organizational objective is called test efficiency (for example, number of projects to be
completed in that particular year). When developing a software product, an organization's
internal process called "test efficiency" assesses how effectively various resources are being
used, such as time, hardware, people, test team knowledge, etc. The proportion of in-house or
on-site alpha testing faults divided by the total of alpha testing and beta testing (off-site)
defects is used to determine test efficiency in mathematics. The testing process known as
"alpha testing" is carried out on-site by the project's testing team, which is tasked with
properly vetting the product before it is made accessible to customers or end users. End
customers are given access to the product once internal alpha testing is over so they may test
it, check it for flaws, and provide their insightful input. In a perfect world, there wouldn't be
any flaws that the end user would notice when beta testing since any real flaws would have a
negative impact on the project team's ability to test the product on site.

73 | P a g e Database Design Development F.M.FAIROOS

3.1.5 Feedback
Feedback is a phenomenon that happens when a system's output is utilized as input again as a
link in a cause-and-effect chain. This modifies system variables, leading to altered output
and, therefore, altered feedback, which may be advantageous or detrimental. Feedback is
important and beneficial when a system must be aware of the output in order to perform
better or deliver on a specified result. But for a system that doesn't need feedback, like an
audio system, feedback is often undesirable. Think of a microphone and speaker system as an
example. When the sound from the speakers (output) is caught up by the microphone (input),
it causes negative feedback and an extremely high-pitched sound.

Table 10 Feedback

3.1.5.1 Types of feedback

Feedback may be given for a variety of reasons and in a variety of ways. Feedback may be
given as a single thing, such as unofficial comments on a student's understanding of a subject
during class, or as a mix of numerous entities, such as formal, formative comments from
peers on a first-stage assessment job. Every sort of feedback has a role to play in promoting
and maximizing student learning, thus courses should, wherever feasible, provide chances for
a variety of feedback types. (federation, 2021)

Informal feedback: Informal feedback is something that arises spontaneously in the present
or during activity, therefore it may happen at any time. Therefore, in order to successfully

74 | P a g e Database Design Development F.M.FAIROOS

encourage, coach, or advise students in daily management and decision-making for learning,
informal feedback calls for the development of rapport with them. This might take place in a
real classroom, over the phone, online, or in a virtual setting.

Formal feedback: A formal feedback procedure is laid out and regularly arranged. Formal
feedback, which is often related to assessment activities, typically includes things like
marking criteria, competences, or standard accomplishment. It is documented for both the
student and the organization as proof.

Formative feedback: Formative assessment aims to keep track of student learning and give
continual feedback that both students and teachers may utilize to enhance their instruction.
Therefore, it is important to provide formative comments early in the course, before final
exams. Students who get formative comments are better able to learn from their errors and
avoid repeating them. Before students may advance, or feel capable of continuing, to the next
step of the evaluation, feedback may sometimes be necessary.

Summative feedback: Summative assessments are intended to evaluate student learning by

comparing it to a benchmark or standard at the conclusion of a unit of teaching. Summative
feedback thus includes extra constructive criticism on how the work may be improved as well
as comprehensive remarks pertaining to certain elements of their work and a clear
explanation of how the mark was calculated using the given criteria.

Student peer feedback: It's no longer necessary for professors to be the sole subject-matter
experts in a course. Students may learn to provide high-quality feedback, which is highly
appreciated by peers, with some basic education and regular coaching. Regular chances for
students to provide and receive peer evaluation enhances their educational experiences and
strengthens their professional skill set.

75 | P a g e Database Design Development F.M.FAIROOS

3.1.5.2 Feedback form For Polly Pipe Users

Figure 62 Feedback Form

76 | P a g e Database Design Development F.M.FAIROOS

3.1.5.3 Response for Polly Pipe Users

Figure 63 Response Form -01

Figure 64 Response Form -02

Figure 65 Response Form -03

77 | P a g e Database Design Development F.M.FAIROOS

 Figure 66 Response Form -04

Figure 67 Response Form -05

Figure 68 Response Form -06

78 | P a g e Database Design Development F.M.FAIROOS

Activity 04

4.1 User Guide and Technical Documentation

Polly Pipe

Users Guide and Technical Information

79 | P a g e Database Design Development F.M.FAIROOS

4.2 Introduction
The goal of this Users' Guide is to illustrate the fundamental features of the Poly Pipe
Database system. You may follow the instructions in this users' handbook to set up, manage,
and utilize the database system. How to use the system routinely is described in this text. This
user manual also includes instructions on how to do some basic tasks and a list of the
recommended system requirements required for the program to function properly.

4.2.1 System Requirements

1. Software Requirements

Table 11 Software requirements client PC

Component Requirement
Operating system Windows 10 Pro

SQL SQL server management studio 2019 (64 bit) or above

NET Framework .NET Framework 4.5.1

Office Microsoft 365

2. Server Hardware Requirements

Table 12 Hardware requirements client PC

Component Requirement

Hard disk 512GB SSD and 2TB HDD

Intel(R)Iris(R) Plus Graphics (1366x768), Generic PnP monitor or More

Monitor
definition Monitors

Installed RAM 16.00 GB or above

System type 64-bit operating system, x64-based processor

Processor speed 1.30GHz -1.50 GHz or above

Processor Intel(R) Core (TM) i7-1065G7 CPU or i5 CPU

80 | P a g e Database Design Development F.M.FAIROOS

3. Hardware Requirements Client PC
Table 13 Hardware Requirements Client PC
Component Requirements
Memory (RAM) 8GB
Hard Disk 500 HDD
Processor Intel® Core™ i5-6500
Network 10/100/1000 Mbit/s) Ethernet

Login Form

Figure 69 Login Form

Step 01 – In the text boxes labeled "Username" and "Password," enter the user's name and
password. When the proper information is entered, the Login page appears.
Step 02 – A Login page will appear.

81 | P a g e Database Design Development F.M.FAIROOS

Dashboard Form

Figure 70 Dashboard Form

The user clicks the option in the dashboard. Through the choice on the Strip, users may visit
the pages.

Customer Page

Figure 71 Customer Page

82 | P a g e Database Design Development F.M.FAIROOS

 Input Customer Name
 Input Customer Address
 Input Customer NIC Number
 Input Customer Mobile Number
 If you want to Insert the record, click the save button
 If you want to Change any kind of details Click the records and change after click the
update button
 If you want to delete any records, click the delete button
 Request that the provide their customer id and details to display the information.

4.2.2 Diagram

4.2.2.1 Use Case Diagram

In UML, a use case diagram is a behavior or dynamic diagram. Utilizing actors and use cases,
use case diagrams simulate a system's functioning. Use cases are a collection of tasks,
services, and duties that the system must carry out. In this sense, a "system" is anything that
is being created or run, like a website. The "actors" are individuals or groups acting in
accordance with predetermined roles inside the system. (smartdraw, 2021)

Basic Use Case Diagram Symbols and Notations

System
Use cases should be arranged in a rectangle to define the bounds of your system. Place the
actors outside the restrictions of the system.

Figure 72 System

83 | P a g e Database Design Development F.M.FAIROOS

Use Case
Use ovals to represent use scenarios. Put verbs that describe the functions of the system in the
ovals.

Figure 73 Use Case

Actors
Users of the system are actors. Label the actor system with the actor archetype whenever one
system acts as the actor of another system.

Figure 74 Actors

Relationships
Explain the connections between an actor and a use case using only one line. Use "uses" or
"extends" labeled arrows to indicate links between use cases. A "uses" connection denotes
that two use cases are required for the completion of a job. Alternative possibilities under a
particular use case are indicated by a "extends" connection.

Figure 75 Relationships

84 | P a g e Database Design Development F.M.FAIROOS

User Diagram for Polly Pipe

Figure 76 User Diagram

85 | P a g e Database Design Development F.M.FAIROOS

4.2.2.2 DFD Diagram
A data flow diagram (DFD) shows how information moves through any system or process.
It displays data inputs, outputs, storage locations, and routes between each destination using
predefined symbols such rectangles, circles, and arrows as well as brief text descriptions.
Data flow diagrams may be as basic as hand-drawn process overviews or more complex,
multi-level DFDs that gradually delve deeper into the data handling process. They may be
used to model a new system or analyze an existing one. A DFD, like the greatest diagrams
and charts, can often "express" things graphically that are hard to describe verbally. They
are appropriate for both technical and nontechnical audiences, from developers to CEOs.
That explains why DFDs are still so widely used today. Today, they are less useful for
visualizing interactive, real-time, or database-oriented software or systems, even if they still
function well for data flow software and systems. (Lucidchart, 2021).

Level - 0

Figure 77 Level 0

86 | P a g e Database Design Development F.M.FAIROOS

Level-01

Figure 78 DFD Level 1

Figure 79 DFD Level 1

87 | P a g e Database Design Development F.M.FAIROOS

 Figure 80 DFD Level 1

4.2.2.3 Flow chart

A flowchart is a visual depiction of a process. It was developed in computer science as a

representational tool for algorithms and logical programming, but it has now expanded to be
used in all other sorts of processes. These days, flowcharts are crucial for showing
information and supporting arguments. They aid in the visualization of complicated processes
or make the structure of issues and activities clear. A flowchart may also be used to outline a
project or procedure that has to be put into action. (visual-paradigm, 2021)

Figure 81 Flow Chart

88 | P a g e Database Design Development F.M.FAIROOS

4.2.3 Advantages of Database System
Teams need a solution that enables them to coordinate everything, including monitoring who
is using the system and where they are accessing it from, as effective data management
means a consolidated view of data. A database management system enhances the security,
integration, compliance, and performance of a business. (Pizzo, 2021)
improved data sharing and data security
Database management solutions enable users to safely, efficiently, and swiftly transfer data
within an organization. A data management system offers quicker access to more accurate
data by quickly responding to database requests. End users, such as salespeople, may shorten
sales cycles and improve the precision of their sales prospecting.
Effective data integration
By making it simple to show how activities in one area of the company influence other areas,
using a database management system will help you create a more comprehensive view of
your operations. Previously entirely manual tasks may now be totally automated and more
precisely completed. The ideal DBMS will provide flexible integration options to normalize
data from many sources, eliminate duplicates, segment and enhance data sets, and create
unique processes.
Consistent, reliable data
When matching data is found in various locations within an organization, data inconsistency
results. For instance, one group has a client's accurate phone number, but another has the
correct email address. You may be certain that an accurate picture of the data is provided
across your business by utilizing an appropriate database management system and data
quality tools.
Data that complies with privacy regulations
Database management solutions provide a more effective framework for enforcing security
and privacy regulations. Companies may manage privacy and data security centrally by
orchestrating data in a uniform way, which helps consolidate their record-keeping systems
and lowers the chance of regulatory infractions.
Increased productivity
Because a strong DBMS enables individuals to spend more time on high-value tasks and
strategic projects and less time cleaning data and manually scrubbing lists, using a DBMS
often leads in enhanced productivity.

89 | P a g e Database Design Development F.M.FAIROOS

4.2.4 Suggestion for future enhancements to ensure the effectiveness of the
system.
Maintaining the company’s-built system is your obligation as a system analyst, thus you must
foresee any improvements and any dangers. As a result, the suggestions below are given.

Using cloud-based technology

It's crucial to seek a tool that can find and categorize vulnerabilities across databases since
detection capabilities are usually needed to satisfy regulatory compliance requirements.
A cloud-based system that, if it is housed in the cloud, would automatically recommend
methods to repair any vulnerabilities that are detected, is an excellent notion to offer as a
system analyst. With no restrictions on the amount of data that may be saved, cloud storage is
trustworthy, speedy, and secure.

Making use of business intelligence

Businesses may more completely analyze employee behavior and company performance with
the help of BI, and then adjust their development initiatives as necessary.

Utilizing alert messages

Alert messages may be used to tell the user of noteworthy events like a possible danger, a
recent achievement, crucial information, or a warning.

Consistently use Windows authentication

offers unified management of SQL Server principals through Active Directory and supports
integrated password policy enforcement, including difficult validation for strong passwords,
password expiry, and account lockout.

90 | P a g e Database Design Development F.M.FAIROOS

Conclusion
Working on this project was enjoyable for me as a systems analyst. In the above case, it
should also create a management system for the firm Poly Pipe. In order to do this, it must
first assess the user's needs and receive a comprehensive description of the organization. The
system then has to be developed using the information acquired. A logical diagram and an ER
diagram were used for this. Using the visual studio program, it is then able to build the
database and establish the crucial system interfaces.
To ensure the system's smooth functioning once it had been programmed in C#, a test
strategy and test cases had to be developed. A user guide and technical documentation were
also produced after gathering additional information about the system from user comments,
and the system was then given to the customer. Additionally, confidence and the capacity for
clear thinking have increased as a result of this achievemen

91 | P a g e Database Design Development F.M.FAIROOS

Self-Criticism
As a HND student, I find more struggles to survive from this new environment. I worked
hard to achieve my assignment criteria perfectly, as my schedule plan. My very first goal is to
complete and submit all of the assignments on time.
Figure 82 Self-Criticism
Strength Weakness

Supportive and friendly lectures

More Distractions

Willing to learn new things

Poor time management

Supportive classmates
Not doing tasks on time

Never give up on hard times

92 | P a g e Database Design Development F.M.FAIROOS

Guntt Chart

Figure 83 Guntt Chart

93 | P a g e Database Design Development F.M.FAIROOS

References

avatpoint, 2022. [Online]

Available at: https://www.javatpoint.com/dbms-normalization
[Accessed 18 11 2021].
Clawson, C., 2021. [Online]
Available at: https://www.sumologic.com/blog/what-is-database-security/#:~:text=Database
%20security%20refers%20to%20the,various%20applications%20that%20access%20it.
[Accessed 01 12 2021].
collegenote.net, 2021. [Online]
Available at: https://www.collegenote.net/curriculum/software-engineering-csit/54/312/
[Accessed 13 11 2021].
databasemanagement, 2022. [Online]
Available at: https://databasemanagement.fandom.com/wiki/Data_Validation
federation, 2021. [Online]
Available at: https://federation.edu.au/staff/learning-and-teaching/teaching-practice/
feedback/types-of-feedback
[Accessed 12 12 2021].
Hamilton, T., 2021. [Online]
Available at: https://www.guru99.com/what-everybody-ought-to-know-about-test-
planing.html
[Accessed 03 12 2021].
imperva, 2021. [Online]
Available at: https://www.imperva.com/learn/application-security/black-box-testing/
[Accessed 05 12 2021].
javatpoint, 2021. [Online]
Available at: https://www.javatpoint.com/dbms-er-model-concept
[Accessed 16 11 2021].
javatpoint, 2021. [Online]
Available at: https://www.javatpoint.com/dbms-sql-command
[Accessed 22 11 2021].
Lucidchart, 2021. [Online]
Available at: https://www.lucidchart.com/pages/data-flow-diagram
[Accessed 23 12 2021].

94 | P a g e Database Design Development F.M.FAIROOS

Magoni, I., 2021. [Online]
Available at: https://www.freecodecamp.org/news/sql-join-types-inner-join-vs-outer-join-
example/
[Accessed 28 11 2021].
Parker, J., 2012. [Online]
Available at: https://enfocussolutions.com/business-user-and-system-requirements/
[Accessed 10 11 2021].
Peterson, R., 2021. [Online]
Available at: https://www.guru99.com/er-diagram-tutorial-dbms.html
[Accessed 15 11 2021].
Pizzo, M., 2021. [Online]
Available at: https://pipeline.zoominfo.com/operations/6-benefits-of-using-database-
management-systems-dbms
[Accessed 25 12 2021].
satoricybe, 2021. [Online]
Available at: https://satoricyber.com/glossary/dml-data-manipulation-language/
[Accessed 26 11 2021].
smartdraw, 2021. [Online]
Available at: https://www.smartdraw.com/use-case-diagram/
[Accessed 18 12 2021].
tutorialspoint, 2021. [Online]
Available at: https://www.tutorialspoint.com/what-are-the-components-of-er-diagrams-in-
dbms#
[Accessed 06 12 2021].
visual-paradigm, 2021. [Online]
Available at: https://www.visual-paradigm.com/tutorials/flowchart-tutorial/
[Accessed 24 12 2021].

95 | P a g e Database Design Development F.M.FAIROOS