lOMoARcPSD|56395220

Unit 04 Database Design and Development 2024

database (ESOFT Metro Campus)

Scan to open on Studocu

Studocu is not sponsored or endorsed by any college or university

Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

ASSIGNMENT 01
DATABSE DESIGN & DEVELOPMENT

Gowsigan Pushpanathan
ESOFT METRO CAMPUS

Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)

 lOMoARcPSD|56395220

Higher Nationals - Summative Assignment Feedback Form

Student Name/ID P. Gowsigan / E127049

Unit Title Unit 04 – Database Design & Development

Assignment Number 1 Assessor
22/07/2024 Date Received 22/07/2024
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

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

P. GOWSIGAN DATABASE DESIGN E127049 2|Page

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Assessor Feedback:

Grade: Assessor Signature: Date:

Resubmission Feedback:
 Please note resubmission feedback is focussed only on the resubmitted work

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.

BTEC HN Summative Assignment Feedback Form

Issue Date: June 2021 Owner: HN QD
DCL1 Public (Unclassified) Version 1.0

P. GOWSIGAN DATABASE DESIGN E127049 3|Page

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Pearson Higher Nationals in

Computing
Unit 04: Database Design & Development
Assignment 01

P. GOWSIGAN DATABASE DESIGN E127049 4|Page

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

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. Avoid using page borders in your assignment body.
3. Carefully check the hand in date and the instructions given in the assignment. Late
submissions will not be accepted.
4. Ensure that you give yourself enough time to complete the assignment by the due date.
5. Excuses of any nature will not be accepted for failure to hand in the work on time.
6. You must take responsibility for managing your own time effectively.
7. 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.
8. Failure to achieve at least PASS criteria will result in a REFERRAL grade.
9. Non-submission of work without valid reasons will lead to an automatic RE FERRAL. You will
then be asked to complete an alternative assignment.
10. 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.
11. 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
12. Use word processing application spell check and grammar check function to help editing
your assignment.
13. 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.

P. GOWSIGAN DATABASE DESIGN E127049 5|Page

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

STUDENT ASSESSMENT SUBMISSION AND DECLARATION

When submitÝng evidence for assessment, each student must sign a declaration confirming that
the work is their own.
Student name: P. Gowsigan / E127049 Assessor name:

Issue date: 11/04/2024 Submission date: 22/07/2024 Submitted on: 22/07/2024

Programme: Pearson BTEC HND in Computing

Unit: Unit 04
Assignment number and title:
Database System for E-Space Solutions

Plagiarism
Plagiarism is a particular form of cheating. Plagiarism must be avoided at all costs and students
who break the rules, however innocently, may be penalised. It is your responsibility to ensure
that you understand correct referencing practices. As a university level student, you are
expected to use appropriate references throughout and keep carefully detailed notes of all your
sources of materials for material you have used in your work, including any material downloaded
from the Internet. Please consult the relevant unit lecturer or your course tutor if you need any
further advice.

Guidelines for incorporating AI-generated content into assignments:

The use of AI-generated tools to enhance intellectual development is permitted; nevertheless,

submitted work must be original. It is not acceptable to pass off AI-generated work as your own.

Student Declaration
Student declaration

I certify that the assignment submission is entirely my own work and I fully understand the
consequences of plagiarism. I understand that making a false declaration is a form of malpractice.

Student signature: E127049@esoft.academy Date: 22/07/2024

P. GOWSIGAN DATABASE DESIGN E127049 6|Page

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Unit 04: Database Design & Development

Assignment Brief
Student P. Gowsigan / E127049
Name/ID
Number
Unit Number and Title Unit 04 – Database Design & Development
Academic Year 2024/2025
Unit Tutor

Assignment Title Database System for E-Space Solution

11/04/2024
Issue Date
Submission Date 22/07/2024

Submission Format
The assignment submission is in the form of the following.
 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.
 Completed functional relational database. Final working version in a format
suitable to be run and assessed for functionality – this could be as project/solution
files or final compiled executable
 A full Testing document
 Technical and User instructional videos for successful use of the database. Use
appropriate software and submit in a suitable format
 written report to evaluate the database and its implementation.
The recommended word limit is 3,000 – 3,500 words, although you will not be
penalised for exceeding the total word limit.
All work must be supported with research and referenced using the Harvard referencing
system

Unit Learning Outcomes

Use an appropriate design tool to design a relational database system for a

LO1
substantial problem
Develop a fully-functional relational database system, based on an existing
LO2
system design.
LO3 Test the system against user and system requirements.
LO4 Produce technical and user documentation.

P. GOWSIGAN DATABASE DESIGN E127049 7|Page

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Transferable skills and competencies developed

P. GOWSIGAN DATABASE DESIGN E127049 8|Page

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Computing-related cognitive skills :

 Demonstrate knowledge and understanding of essential facts, concepts, principles and
theories relating to computing and computer applications
 Use such knowledge and understanding in the modelling and design of computer-based
systems for the purposes of comprehension, communication, prediction and the
understanding of trade-offs
 Recognise and analyse criteria and specifications appropriate to specific problems, and
plan strategies for their solutions
 Critical evaluation and testing: analyse the extent to which a computer-based system
meets the criteria defined for its current use and future development
 Methods and tools: deploy appropriate theory, practices and tools for the design,
implementation and evaluation of computer-based systems.

Computing-related practical skills :

 The ability to specify, design and construct reliable, secure and usable computer-based
systems
 The ability to evaluate systems in terms of quality attributes and possible trade-offs
presented within the given problem
 The ability to deploy effectively the tools used for the construction and documentation
of computer applications, with particular emphasis on understanding the whole process
involved in the effective deployment of computers to solve practical problems
 The ability to critically evaluate and analyse complex problems, including those with
incomplete information, and devise appropriate solutions, within the constraints of a
budget.

Generic skills for employability:

 Intellectual skills: critical thinking; making a case; numeracy and literacy
 Self-management: self-awareness and reflection; goal setÝng and action planning
 Independence and adaptability; acting on initiative; innovation and creativity
 Contextual awareness, e.g. the ability to understand and meet the needs of individuals,
business and the community, and to understand how workplaces and organisations are
governed.

P. GOWSIGAN DATABASE DESIGN E127049 9|Page

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Vocational scenario
With marking a new chapter of human history E-Space Solutions (Pvt.) Ltd decided to
colonize the planet Mars in 2040. The company decided to hire a database expert to
develop a Database Management system to assist in this project for decision making.
Therefore, the data should maintain accuracy & consistency throughout the data life
cycle. E-Space Solutions plan to collect the information of the people who are willing to
go to Mars. To colonize Mars, the company needs to think about the qualifications of
volunteers & how they can assist in the colonizing process on Mars. The people who
willing to go to Mars will be known as ‘Colonist.’ Each colonist will be identified with Mars
Colonization ID, Name with First Name, Middle Name, Surname, Date of Birth,
Qualification, Age, Earth Address, Gender, Contact No, Civil Status, number of members of
the family to go to Mars & etc. From a single family there can be only one colonist & the
colonist can decide who will go to the planet Mars with them. If desired, colonists can
take their whole family with them too. But they will be considered as Dependents of the
colonist. In the system the dependents are not needed to identify separately, they should
only need to identify according to the colonist. But the dependents details should be
recorded with their Name, Date of Birth, Age, Gender, Relationship to the colonist & etc.
E-Space Solutions planning to have develop space crafts which will be named E-Jets. Those
E-Jets will take the colonists & their families together to the Mars & the pilots will return
with empty E-Jet. Those E-Jet pilots will be well experienced astronomers who have a
number of space hours. E-Jets will identify with Jet unique code, number of passenger
seats, nuclear engine power, made year, weight, power source & etc. Company plan to
build three types of E-Jets & they are,
 Jets which powered by only nuclear engine
 Jets which are powered by Nuclear engine to go through the thick atmosphere
close to the Earth surface & then use Hydro Splitter engine to go through the
space with more than the speed of the sound & again it will use Nuclear engines
when landing on the Mars.
 Jets which are powered by Hydro-Nuc engine which use Nuclear power to split the
water molecules & generate a super hydro power for the jet. But this technology
will be high in cost, so at the initial level the company will have one of these only.

The astronomers who operate the E-Jets will have multiple qualifications in different
levels. Also, they will have designations according to their ranks in the company. The
pilots are assigned to the Jets rather than to the trips.
Those E-Jets will go multiple number of trips to Mars, with number of colonists & their
families. Every colonist & family member (if they go with the colonist) will get a single trip
to Mars & there’s no return. According to the company the trip table should be as follows.

P. GOWSIGAN DATABASE DESIGN E127049 10 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

TripID JetCode LaunchDate ReturnDate

The colonists will get a house from the Mars Colony. If the colonist going with the family,
will get a single house or if not with the family the single house will assign to four
colonists. The houses can be identified by unique Colony Lot Number, Number of Rooms,
Square feet of the house & any other relevant record that needed.
Also, each & every colonist will be assigned a job in the colonization process. Some of the
colonists will have multiple jobs. Those jobs can be Construction, Mechanical, Power
Generation, Medical, Security, Administration, Education, Research & Observation & etc.
One job will assign for multiple colonists.
Above details of the system will be input by the Data Entry Operators of the project.
Several reports will be generated by the System administrator such as Jet Detail report,
Trip Detail report with passengers, Colonists details with dependents & etc. Also, when
the project is ongoing the Colony Superintendent will generate Colony report with House
details & colonists, Job reports of the colonists & etc. The pilots can check the data of the
system according to the space craft trip information with the passenger details.
E-Space solution is hiring you as the Database expert to design & develop a solution for
the above scenario. In the System development level, the company doesn’t need the
report generated part initially, it will include in the next level of the development. But the
Database designs & system designs should include the reporting processes.

Assignment activity and guidance

Based on E-Space Solutions scenario you will need to:
Activity 1 – User Requirements and Logical Design
a. Identify the user and system requirements of the E-Space Solutions System.
b. According to the above requirements that identified, design a database and
design a relational database system using conceptual design (ER Model) with
including identifiers (primary Key) of entities and attributes, cardinalities,
participations of relationships.
c. 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 six interrelated tables.

P. GOWSIGAN DATABASE DESIGN E127049 11 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

d. 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 appropriate assumptions and related attributes
within the scope of the case study given
e. Design set of simple interfaces to input and output for the above scenario
using Wireframe or any interface-designing tool. (Those designs cannot be the
interface designs of visual studio.)
f. Evaluate the effectiveness of the given design (ERD and Logical design) in
terms of the identified user and system requirements.

Activity 2 – Physical Design

a. Develop a relational database system according to the ER diagram you

have designed (Use SQL DDL statements). Should use data validation when
develop the database.

b. Provide evidence of the use of a suitable IDE to create a simple interface to

insert, update and delete data in the database.

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

** Security mechanism should demonstrate with the necessary screen shots of the
steps that follows during creating user groups, access permissions in the Microsoft
SQL Sever.

d. Explain the usage of DML with below mentioned queries by giving at least
two examples per each case from the developed database.

Select / Where / Insert / Update / Between / In / Group by / Order by / Having

e. Assess the usage of the above SQL statements with the examples from the
developed database to prove that the data extracted through them are

P. GOWSIGAN DATABASE DESIGN E127049 12 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

meaningful & relevant to the given scenario.

P. GOWSIGAN DATABASE DESIGN E127049 13 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Activity 3 -Testing

a. Provide a suitable test plan to test the system against user & system
requirements.

b. Provide relevant test cases for the database you have implemented.

c. Assess how the selected test data can be used to improve the effectiveness of
testing.

Note : Learner need to give expected results in a tabular format & screenshots of the
actual results with the conclusion.

Activity 4 – Review and Maintenance

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

b. Produce a user manual (user guide) for the developed system. (This should include
all the forms that developed & proper guidance with step-by-step point format.)

c. Produce a technical documentation for the developed database system. All

developed forms with source code & suitable diagrams (Use case diagram, class
diagram, flow charts, DFD level 0 & 1) should be included in the technical
documentation.

d. Assess the developed database by suggesting future enhancements to ensure the

effectiveness of the system.

P. GOWSIGAN DATABASE DESIGN E127049 14 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Recommended Resources
Please note that the resources listed are examples for you to use as a starting point in your
research – the list is not definitive.

P. GOWSIGAN DATABASE DESIGN E127049 15 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Weblinks:
https://support.microsoft.com/en-GB (2022) Database design basics [online] Available at:
https://support.microsoft.com/en-us/ofÏce/database-design-basics-eb2159cf-1e30-401a-
8084-bd4f9c9ca1f5 [Accessed 1 August 2022]

https://www.guru99.com/ (2022) Database (Data) Testing Tutorial with Sample Test Cases
[online] Available at: https://www.guru99.com/data-testing.html [Accessed 1 August 2022]

https://www.guru99.com/ (2022) Database Design in DBMS Tutorial: Learn Data Modeling

[online] Available at: https://www.guru99.com/database-design.html [Accessed 1 August
2022]

https://www.integrate.io/ (2021) Complete Guide to Database Schema Design [online]

Available at: https://www.integrate.io/blog/complete-guide-to-database-schema-design-
guide/[Accessed 1 August 2022]

https://www.lucidchart.com/pages/ (2022) Database Structure and Design Tutorial [online]

Available at: https://www.lucidchart.com/pages/database-diagram/database-design
[Accessed 1 August 2022]

https://www.softwaretestinghelp.com/ (2022) Database Testing Complete Guide (Why,

What, And How To Test Data) [online] Available at:
https://www.softwaretestinghelp.com/databasetesting-process/ [Accessed 1 August 2022]

Journal articles:
Batra, D. & Davis, J. (1992). Conceptual data modelling in database design: similarities and
differences between expert and novice designers. International Journal of Man-Machine
Studies, Volume 37, Issue 1, 1992, pp. 83-101. https://doi.org/10.1016/0020-
7373(92)90092-Y.

Gunjal, B. (2003). Database System: Concepts and Design. Proceedings of 24th IASLIC–SIG-
2003.

Kaur, T. & Singh B. (2003). Testing of Databases. IJISET - International Journal of Innovative

P. GOWSIGAN DATABASE DESIGN E127049 16 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Science, Engineering & Technology, Vol. 1 Issue 6. ISSN 2348 – 7968

Kaur, Taranpreet & Sehra, Sumeet Kaur. (2015). Designing and Development of Database
Testing Tool. International Journal of Computer Applications (0975 – 8887) Volume 120 –
No.19. 14. doi:10.5120/21334-4330.

Kraleva, Radoslava & Kralev, Velin & Sinyagina, Nina & Koprinkova-Hristova, Petia &
Bocheva, Nadejda. (2018). Design and Analysis of a Relational Database for Behavioral
Experiments Data Processing. International Journal of Online Engineering (iJOE). 14. 117.
doi:10.3991/ijoe.v14i02.7988.

Letkowski, J. (2015). Doing database design with MySQL. Journal of Technology Research.
Volume 6.

Reading:
Captain, F. (2013) Six-Step Relational Database Design™: A step by step approach to
relational database design and development, 2nd edn, CreateSpace Independent
Publishing Platform
Hernandez, M. (2003) Database Design for Mere Mortals: A Hands-On Guide to Relational
Database Design, 2nd edn, Addison Wesley

Stephens, R. (2008) Begin Database Design W / WS (Wrox Programmer to Programmer),

1st edn, Jossey-Bass

HN Global:
HN Global HN Global (2021) Reading Lists. Available at:
https://hnglobal.highernationals.com/learning-zone/reading-lists

HN Global (2021) Student Resource Library. Available at:

https://hnglobal.highernationals.com/subjects/resource-libraries

HN Global (2021) Textbooks. Available at:

https://hnglobal.highernationals.com/textbooks

P. GOWSIGAN DATABASE DESIGN E127049 17 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Learning Outcomes and Assessment Criteria

Pass Merit Distinction

LO1 Use an appropriate design tool to design a relational
database system for a substantial problem

P1 Design a relational M1 Produce a comprehensive

database system using D1 Evaluate the effectiveness of the
design for a fully-functional
appropriate design tools & design in relation to user and system
system, which includes
techniques, containing at least requirements.
interface and output designs,
six interrelated tables, with
clear statements of user and data validations and data
system requirements normalisation.

LO2 Develop a fully-functional relational database system, based

on an existing system design

P2 Develop the database M2 Implement a fully

system with evidence of user functional database system,
interface, output and data which includes system security
validations, and querying
and database maintenance.
across multiple tables. LO2 & LO3
P3 Implement a query M3 Assess whether meaningful
language into the relational data has been extracted
database system. through the use of query tools D2 Evaluate the effectiveness of the
to produce appropriate database solution in relation to user
management information. and system requirements and
suggest improvements.
LO3 Test the system against user and system requirements

P4 Test the system against M4 Assess the effectiveness of

user and system the testing, including an
requirements. explanation of the choice of
test data used.

LO4 Produce technical and user documentation.

P5 Produce technical and user M5 Produce technical and user D3 Evaluate the database in terms of
documentation. documentation for a fully- improvements needed to ensure the
functional system, including continued effectiveness of the
data flow diagrams and system.
flowcharts, describing how the
system works.

P. GOWSIGAN DATABASE DESIGN E127049 18 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Grading Rubric

Grading Criteria Achieved Feedback

LO1 Define basic algorithms to carry out an

operation and outline the process of programming
an application.

P1 Define an algorithm and outline the process in building

an application

P2 Determine the steps taken from writing code to

execution.

M1 Analyze the process of writing code, including

the potential challenges faced.

D1 Evaluate the implementation of an algorithm in a

suitable language and the relationship between the written
algorithm and the code variant

P. GOWSIGAN DATABASE DESIGN E127049 19 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

LO2 Explain the characteristics of procedural,

object orientated and event-driven programming

P3 Discuss what procedural, object orientated and event

driven paradigms are; their characteristics and the
relationship between them.

M2 Compare the procedural, object-orientated and event

driven paradigms used in given source code of an
application

D2 Critically evaluate the source code of an application

that implements the procedural, object-orientated and
event-driven paradigms, in terms of the code structure and
characteristics.
LO3 Implement basic algorithms in code using
an IDE.

P4 Write a program that implements an algorithm using an

IDE.

P. GOWSIGAN DATABASE DESIGN E127049 20 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

M3 Enhance the algorithm written, using the features of

the IDE to manage the development process.

D3 Evaluate the use of an IDE for development of

applications contrasted with not using an IDE.

LO4 Determine the debugging process and explain

the importance of a coding standard

P5 Explain the debugging process and explain the

debugging facilities available in the IDE.

P6 Explain the coding standard you have used in your

code.

M4 Examine how the debugging process can be used to

help develop more secure, robust applications.

D4 Evaluate the role and purpose of a coding standard and

why it is necessary in a team as well as for the individual.

P. GOWSIGAN DATABASE DESIGN E127049 21 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Acknowledgement

First and foremost, I would like to express my special thanks of gratitude to the ESOFT Metro
Campus, who gave me the golden opportunity to do this wonderful HND assignment about
programming. Secondly, I would like to thank our subject programming lecturer for his guidance
and inspiration to successfully complete this assignment. And finally, an honorable mention goes to
our branch manager and our coordinator who supported me to complete the assignment on time.

Thank you
P. Gowsigan

HND in software Engineering ESOFT

Metro Campus - Kurunegala

P. GOWSIGAN DATABASE DESIGN E127049 22 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Table of Content

s
Activity -01...............................................................................................................................................20
Data...........................................................................................................................................................20
Database...................................................................................................................................................20
Types Of Databases.................................................................................................................................20
User and System Requirements for Database and Scenario.................................................................21
System Requirements for Scenario........................................................................................................22
ERD And Its Components.......................................................................................................................23
Types of Attributes..................................................................................................................................24
Relationships and Cardinalities:.............................................................................................................26
Entity Relation Diagram.........................................................................................................................27
Logical Database Diagram......................................................................................................................29
Analysis of Entities and Attributes.........................................................................................................29
Evaluating the effectiveness of the given ERD and Logical designing of the identified user and
system requirements for Scenario..........................................................................................................31
Overall Evaluation...................................................................................................................................33
Activity – 02.............................................................................................................................................34
Develop a Relational Database System According to the ER diagram Using SQL Query.................34
Security Mechanisms Implemented:......................................................................................................37
DML with below mentioned queries......................................................................................................40
Assess the usage of the above SQL statements with the examples from the developed database......42
Activity -03...............................................................................................................................................47
Definition of test plain.............................................................................................................................47
Test Plan for Mars Colonization DBMS................................................................................................48
Test Plan for Mars Colonization Table..................................................................................................52
Selected test data can be used to improve the effectiveness of testing.................................................54
Activity – 04.............................................................................................................................................56
Recommendations and Conclusion.........................................................................................................56
Produce a user manual (user guide) for the developed system.............................................................56
All developed forms with source code & suitable diagrams.................................................................66
Assess the developed database by suggesting future enhancements....................................................68
Conclusion................................................................................................................................................70

P. GOWSIGAN DATABASE DESIGN E127049 23 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Figure 1 - ER Diagram.......................................................................................................................................30
Figure 2 - Logical Database...............................................................................................................................31
Figure 3 - Create colonist Table.........................................................................................................................36
Figure 4 - Insert data in Table............................................................................................................................36
Figure 5 - Create dependent Table.....................................................................................................................36
Figure 6 - Insert data in dependent Table...........................................................................................................37
Figure 7 - Create EJet Table...............................................................................................................................37
Figure 8 - Insert data in EJet Table.....................................................................................................................37
Figure 9 - Create Astronomer Table..................................................................................................................38
Figure 10 - Insert data in Astronomer Table.......................................................................................................38
Figure 11 - Create Trip Table.............................................................................................................................38
Figure 12 - Insert data in Trip Table...................................................................................................................39
Figure 13 - Create Ejet_Astronomer Table.........................................................................................................39
Figure 14 - Insert data in Ejet_Astronomer Table..............................................................................................39
Figure 15 - Select query.....................................................................................................................................42
Figure 16 - Where query....................................................................................................................................42
Figure 17 - Insert query.....................................................................................................................................42
Figure 18 - Insert data........................................................................................................................................43
Figure 19 - Update query...................................................................................................................................43
Figure 20 - Between query.................................................................................................................................43
Figure 21 - In query...........................................................................................................................................43
Figure 22 - Group by query................................................................................................................................44
Figure 23 - Order by query................................................................................................................................44
Figure 24 - Having query...................................................................................................................................44
Figure 25 - Count query.....................................................................................................................................57
Figure 26 - Dashboard.......................................................................................................................................60
Figure 27 - Add details Form.............................................................................................................................61
Figure 28 - Add Trip Form................................................................................................................................64
Figure 29 - Update Trip Form...........................................................................................................................66
Figure 30 - Level 0 DFD Diagram.....................................................................................................................68
Figure 31 - Level 1 DFD Diagram.....................................................................................................................68
Figure 32 - Use Case Diagram...........................................................................................................................69
Figure 33 - Class Diagram.................................................................................................................................69

Table 1 - Colonist all query data........................................................................................................................45

Table 2 - Where query data................................................................................................................................46
Table 3 - Between query data.............................................................................................................................47
Table 4 - In query data.......................................................................................................................................47
Table 5 - Test plan.............................................................................................................................................54
Table 6 - Update table result..............................................................................................................................56
Table 7 - Successful data insert..........................................................................................................................57

P. GOWSIGAN DATABASE DESIGN E127049 24 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Activity -01

Data
Data is many sorts of information that are often formatted in a specific way. Programs and data are
the two main divisions of any software. Programs are collections of instructions used to change
data, and we already know what data is. To make working with data easier, we apply data science.
Data science is described as a field that combines mathematical expertise, programming know-
how, domain knowledge, scientific methods, algorithms, processes, and systems to extract useful
information and insights from both structured and unstructured data, then apply the information
obtained from that data to a variety of purposes and domains.

Database

A database is a structured collection of data that is stored and managed to enable easy retrieval,
insertion, updating, and deletion of data. Databases are used in various applications, ranging from
small-scale personal projects to large-scale enterprise systems. Here are some key points about
databases:
 Data: Raw facts and figures without context. For example, numbers, names, dates.
 Information: Data processed and organized to be meaningful. For example, a report
showing sales figures over time.
 Database Management System (DBMS): Software that provides an interface to interact
with the database, ensuring data is consistently organized and remains easily accessible.
Examples include MySQL, PostgreSQL, Microsoft SQL Server, and Oracle Database.

Types Of Databases

1. Relational Databases: Data is organized into tables (relations) consisting of rows and
columns. These databases use Structured Query Language (SQL) for database
management and queries. Examples: MySQL, PostgreSQL, SQLite, Microsoft SQL
Server

2. NoSQL Databases: Designed for unstructured data and can store data in various
formats like key-value pairs, documents, wide-columns, or graphs. Examples:
MongoDB (document-based), Redis (key-value store), Cassandra (wide-column store),

P. GOWSIGAN DATABASE DESIGN E127049 25 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Neo4j (graph-based).

3. Hierarchical Databases: Data is organized in a tree-like structure with parent-child

relationships. Example: IBM Information Management System (IMS).

4. Network Databases: Like hierarchical databases but allow more complex relationships
with multiple parent and child records. Example: Integrated Data Store (IDS).

5. Object-oriented Databases: Data is stored in objects, like object-oriented

programming. Example: Object DB.

User and System Requirements for Database and Scenario

User Requirements:

User requirements focus on what the users need from the database to accomplish their tasks
effectively.

 Colonists and Dependents:

o Add, view, update, and delete colonist and dependent information.
o Track relationships between colonists and their dependents.
 E-Jets and Pilots:
o Record details of E-Jets and their operational status.
o Track which pilots are assigned to which E-Jets.
o Manage pilot credentials and certifications.
 Trips:
o Schedule and manage trips, including departure and arrival details.
o Assign E-Jets and pilots to trips.
o Track trip history for performance and operational analysis.
 Houses:
o Assign and manage housing for colonists.
o Track house occupancy and capacity.
 Jobs:
o Maintain a list of jobs available for colonists.
o Assign jobs to colonists based on their skills and requirements.

P. GOWSIGAN DATABASE DESIGN E127049 26 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

System Requirements:

System requirements detail the technical needs and constraints of the database to support user
requirements.

 Data Integrity:
o Ensure data accuracy through constraints like primary keys and foreign keys.
o Implement validation rules to prevent incorrect data entry.

 Scalability:
o Ability to handle an increasing number of records as the colonization project grows.
o Efficient indexing to support quick data retrieval.

 Security:
o User authentication and authorization to protect sensitive data.
o Encryption of data at rest and in transit.

 Backup and Recovery:

o Regular data backups to prevent data loss.
o Recovery mechanisms to restore data in case of system failures.

 Performance:
o Optimize queries and database structure for fast performance.
o Implement indexing and caching where necessary.

 Usability:
o Provide an easy-to-use interface for data entry and retrieval.
o Ensure that the system is accessible to users with varying levels of technical expertise.

System Requirements for Scenario

The scenario involves managing data for a Mars colonization project by E-Space Solutions,
scheduled for 2040. This includes managing colonists, dependents, E-Jets, pilots, trips, houses,
and jobs. The system requirements for this scenario are:

 Database Management System (DBMS):

P. GOWSIGAN DATABASE DESIGN E127049 27 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

o Use a robust DBMS like MySQL, PostgreSQL, or Oracle to handle complex queries
and large datasets.
o Support for relational database structures to ensure data normalization and integrity.
 Data Storage:
o Sufficient storage capacity to handle data from multiple entities (colonists,
dependents, E-Jets, etc.).
o Expandable storage solutions to accommodate future data growth.
 Server Requirements:
o High-performance servers to manage database operations efficiently.
o Redundant servers for failover and high availability.
 Data Security:
o Implement access control to ensure only authorized users can access or modify data.
o Use SSL/TLS for secure data transmission.
 Data Backup:
o Regular automated backups to prevent data loss.
o Off-site backup storage for disaster recovery.
 Data Analytics:
o Integration with data analytics tools to analyse trip performance, colonist job
assignments, and other metrics.
o Support for reporting tools to generate insights and operational reports.
 Network Requirements:
o Reliable network infrastructure to ensure consistent access to the database.
o Secure network protocols to protect data from unauthorized access.
 User Interface:
o Develop user-friendly interfaces for data entry, management, and retrieval.
o Ensure cross-platform compatibility for desktop and mobile devices.

ERD And Its Components

Entity-Relationship Diagram is known as ERD. In the context of relational

databases, in particular, it is a visual representation used to model the structure of
a database system. ERDs show the entities (such as classes or tables), the
connections between them, and the characteristics related to them
Entity-Relationship Diagram is known as ERD. In the context of relational databases,
it is a visual representation used to model the structure of a database system. ERDs show the
entities (such as classes or tables), the connections between them, and the characteristics
P. GOWSIGAN DATABASE DESIGN E127049 28 | P a g e
a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

related to them.

1. Entity
Any single, recognizable, and distinct thing is considered an entity. It refers to entities
that are significant in and of themselves, such as people, groups, systems, data, or even
certain system components.

2. Attributes
Attributes are simply qualities. For instance, you can add attributes to each
column or cell in a database or spreadsheet to make your document unique. You can
select whether it should be a text field, a numeric field, or possibly a
computed field, with a value the program generates for you, as a generic
property.

Types of Attributes

• Simple attributes - Simple Attributes are distinct attributes that cannot be further
subclassified. To put it another way, it is also referred to as atomic attributes. A
student, for instance, is an entity made up of the properties Roll No., Age, and
Class. The Roll no property cannot be broken down into sub-attributes in this
case. Therefore, if the attribute cannot be divided further, it is a Simple Attribute

• Composite attributes - The term "Composite Attribute" refers to an attribute that

can be broken down into many components. We break down a
Composite Attribute into smaller components that make up simple attributes.
Assume Name is an attribute of the student entity, for instance. The Name
attribute can be divided into first name, middle name, and last name categories.
These qualities that are categorized as Simple qualities from the Composite
Attribute.

• Single valued attributes - Values that are used to describe the entity are stored in
attributes. Single Valued Attributes are those attributes that can only store one
value. These properties are unable to store multiple values. For instance, the
Employee entity's properties include the Employee ID, DOB, and Gender. An

P. GOWSIGAN DATABASE DESIGN E127049 29 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

employee only has one date of birth and one employee id, both of which are
distinct. Therefore, these properties can only store one value. As a result, they are
referred to as single valued attributes.

3. Relationships
Relationships represent the associations or connections between entities.
Relationships define how entities are related to each other and provide valuable insights
into the data model
Designing a relational database system for E-Space Solutions' Mars colonization project involves
several steps, including creating an Entity-Relationship (ER) model. The ER model will include
entities, attributes, primary keys, relationships, and their cardinalities and participations.
Here's a conceptual design using the ER model:
Entities and Attributes:
1. Colonist
o MarsColonizationID (Primary Key)
o FirstName
o MiddleName
o Surname
o DateOfBirth
o Qualification
o Age
o EarthAddress
o Gender
o ContactNo
o CivilStatus
o FamilyMembersCount

2. Dependent
o DependentID (Primary Key)
o MarsColonizationID (Foreign Key)
o Name
o DateOfBirth
o Age
o Gender
o RelationshipToColonist
P. GOWSIGAN DATABASE DESIGN E127049 30 | P a g e
a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

3. EJet
o JetUniqueCode (Primary Key)
o NumberOfPassengerSeats
o NuclearEnginePower
o MadeYear
o Weight
o PowerSource
o JetType

4. Astronomer
o AstronomerID (Primary Key)
o Name
o Qualification
o Designation
o SpaceHours

5. Trip
o TripID (Primary Key)
o MarsColonizationID (Foreign Key)
o JetUniqueCode (Foreign Key)
o AstronomerID (Foreign Key)
o TripDate

Relationships and Cardinalities:

1. Colonist - Dependent:
o One-to-Many (One colonist can have multiple dependents, each dependent belongs
to one colonist)
o Participation: Total (Every dependent must be associated with a colonist)
2. EJet - Astronomer:
o Many-to-Many (An EJet can have multiple astronomers assigned over time, and an
astronomer can be assigned to multiple EJets)

P. GOWSIGAN DATABASE DESIGN E127049 31 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

o Participation: Partial (Not every EJet needs to have an astronomer assigned at all
times)
3. Trip:
o A colonist can participate in one trip, and each trip involves one colonist
o An EJet can be used for multiple trips, and each trip involves one EJet
o An astronomer can pilot multiple trips, and each trip has one astronomer

Entity Relation Diagram

Below is a textual representation of the ER diagram.

Entities:
 Colonist (MarsColonizationID, FirstName, MiddleName, Surname, DateOfBirth,
Qualification, Age, EarthAddress, Gender, ContactNo, CivilStatus, FamilyMembersCount)

 Dependent (DependentID, MarsColonizationID, Name, DateOfBirth, Age, Gender,

RelationshipToColonist)

 EJet (JetUniqueCode, NumberOfPassengerSeats, NuclearEnginePower, MadeYear, Weight,

PowerSource, JetType)

 Astronomer (AstronomerID, Name, Qualification, Designation, SpaceHours)

 Trip (TripID, MarsColonizationID, JetUniqueCode, AstronomerID, TripDate)

Relationships:
 Colonist - Dependent (1) (MarsColonizationID in Dependent is a Foreign Key referencing
MarsColonizationID in Colonist)

 EJet - Astronomer (M) (Requires a junction table: EJetAstronomer (EJetID, AstronomerID))

 Colonist - Trip (1:1) (MarsColonizationID in Trip is a Foreign Key referencing

MarsColonizationID in Colonist)

 EJet - Trip (1) (JetUniqueCode in Trip is a Foreign Key referencing JetUniqueCode in EJet)

P. GOWSIGAN DATABASE DESIGN E127049 32 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

 Astronomer - Trip (1) (AstronomerID in Trip is a Foreign Key referencing AstronomerID in

Astronomer)

This ER model will help in creating a relational database schema, ensuring accuracy and
consistency throughout the data lifecycle. Each entity will have its own table in the database, with
appropriate primary and foreign keys to maintain relationships and enforce data integrity.

Figure 1 - ER Diagram

P. GOWSIGAN DATABASE DESIGN E127049 33 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Logical Database Diagram

Figure 2 - Logical Database

Analysis of Entities and Attributes

Colonist Entity:
 Attributes: MarsColonizationID (PK), FirstName, MiddleName, Surname,
DateOfBirth, Qualification, Age, EarthAddress, Gender, ContactNo, CivilStatus,
FamilyMembersCount.

P. GOWSIGAN DATABASE DESIGN E127049 34 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

 Dependencies: None apparent, all attributes relate directly to the colonist.

Dependent Entity:
 Attributes: DependentID (PK), MarsColonizationID (FK), Name, DateOfBirth, Age,
Gender, RelationshipToColonist.

 Dependencies: MarsColonizationID (FK) links to Colonist (One colonist can have

multiple dependents).

EJet Entity:
 Attributes: JetUniqueCode (PK), NumberOfPassengerSeats, NuclearEnginePower,
MadeYear, Weight, PowerSource, JetType.

 Dependencies: None apparent from the provided attributes.

Astronomer Entity:
 Attributes: AstronomerID (PK), Name, Qualification, Designation, SpaceHours.

 Dependencies: None apparent from the provided attributes.

Trip Entity:
 Attributes: TripID (PK), MarsColonizationID (FK), JetUniqueCode (FK),
AstronomerID (FK), TripDate.

 Dependencies: MarsColonizationID (FK) links to Colonist, JetUniqueCode (FK) links to

EJet, AstronomerID (FK) links to Astronomer.

Normalization Assessment:
First Normal Form (1NF):
All attributes are atomic (indivisible).

Second Normal Form (2NF):

All non-key attributes are fully functionally dependent on the primary key in their respective
entities.

Third Normal Form (3NF):

There are no transitive dependencies where non-key attributes depend on other non-key
attributes within the same entity.

P. GOWSIGAN DATABASE DESIGN E127049 35 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Anomalies and Normalization:

The provided logical design appears to be normalized up to 3NF:
 Each entity has a distinct primary key.

 Dependencies between entities are based on foreign keys.

 There are no calculated fields or redundant data.

 This the Staff Form Wireframe for Quiet Attic Films GUI.
 In here You can Insert, Update or Delete Property details.
 The Entered Details are stored in Staff Table of the database.
Evaluating the effectiveness of the given ERD and Logical designing of the
identified user and system requirements for Scenario
Evaluating the effectiveness of the given ERD and Logical designing of the identified user
and system requirements for Scenario

User Requirements Evaluation

1. Colonists and Dependents Management:

o ERD Effectiveness: The ERD includes Colonist and Dependent entities with clear
relationships. This meets the requirement to track relationships between colonists
and their dependents.
o Logical Design Effectiveness: The Dependent table has a foreign key (ColonistID)
referencing the Colonist table, ensuring that dependents are correctly associated with
their respective colonists.
2. E-Jets and Pilots Management:
o ERD Effectiveness: The ERD includes EJet and Pilot entities with a relationship
indicating which pilot is assigned to which E-Jet.
o Logical Design Effectiveness: The Pilot table includes a foreign key (JetID)
referencing the EJet table. This relationship is clearly defined, supporting the
management of pilots and their assigned E-Jets.
3. Trips Management:
o ERD Effectiveness: The Trip entity includes relationships with both EJet and Pilot,
allowing for comprehensive trip scheduling and management.
o Logical Design Effectiveness: The Trip table includes foreign keys (JetID and
PilotID) referencing the EJet and Pilot tables, respectively. This ensures that each

P. GOWSIGAN DATABASE DESIGN E127049 36 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

trip is associated with a specific E-Jet and pilot, meeting the requirement for detailed
trip management.
4. Housing Management:
o ERD Effectiveness: The House entity is included, with a relationship to the Colonist
entity, supporting the management of colonist housing.
o Logical Design Effectiveness: The Colonist table includes a foreign key (HouseID)
referencing the House table. This allows for effective tracking of house assignments
and occupancy.
5. Job Management:
o ERD Effectiveness: The Job entity and its relationship with the Colonist entity are
included, enabling job assignment and management.
o Logical Design Effectiveness: The Colonist table includes a foreign key (JobID)
referencing the Job table, ensuring that colonists can be correctly assigned to jobs
based on their skills and requirements.

System Requirements Evaluation

1. Data Integrity:
o ERD Effectiveness: The ERD effectively uses primary and foreign keys to enforce
data integrity and establish clear relationships between entities.
o Logical Design Effectiveness: The use of primary keys in each table and foreign
keys to define relationships ensures data accuracy and integrity.
2. Scalability:
o ERD Effectiveness: The design is modular, with entities and relationships that can
scale as the amount of data grows.
o Logical Design Effectiveness: Proper indexing of primary and foreign keys will
support efficient data retrieval, making the system scalable.
3. Security:
o ERD Effectiveness: The ERD does not explicitly address security, but the
separation of entities can aid in implementing security measures.
o Logical Design Effectiveness: Access control can be implemented at the database
level to restrict access to sensitive data. Encryption and secure protocols can be
applied to protect data.
4. Backup and Recovery:

P. GOWSIGAN DATABASE DESIGN E127049 37 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

o ERD Effectiveness: The ERD does not directly address backup and recovery, but
the structure allows for straightforward implementation of these features.
o Logical Design Effectiveness: Regular backups can be scheduled, and the relational
structure supports easy recovery of related data sets.
5. Performance:
o ERD Effectiveness: The ERD supports performance optimization by clearly
defining relationships, which can be indexed for faster queries.
o Logical Design Effectiveness: Proper indexing and query optimization can be
applied based on the logical design, ensuring fast and efficient data access.
6. Usability:
o ERD Effectiveness: The ERD is designed with clear relationships and entity
definitions, making it user-friendly for database administrators and developers.
o Logical Design Effectiveness: The logical design aligns with the ERD, making it
easy for users to understand and interact with the database. User-friendly interfaces
can be built on top of this structure.

Overall Evaluation

The given design (ERD and logical design) effectively meets the identified user and system
requirements for the Mars colonization project by E-Space Solutions. The entities and their
relationships are clearly defined, ensuring comprehensive data management and integrity. The
logical design translates the ERD into a structured database schema, supporting scalability,
security, performance, and usability. With proper implementation and maintenance, the design
will provide a robust foundation for managing the various aspects of the colonization project.

P. GOWSIGAN DATABASE DESIGN E127049 38 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Activity – 02

Develop a Relational Database System According to the ER diagram Using SQL Query

Colonist Table

Figure 3 - Create colonist Table

Figure 4 - Insert data in Table

P. GOWSIGAN DATABASE DESIGN E127049 39 | P a g e
a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Dependent Table

Figure 5 - Create dependent Table

Figure 6 - Insert data in dependent Table

EJet Table

Figure 7 - Create EJet Table

P. GOWSIGAN DATABASE DESIGN E127049 40 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Figure 8 - Insert data in EJet Table

Astronomer Table

Figure 9 - Create Astronomer Table

Figure 10 - Insert data in Astronomer Table

Trip Table

P. GOWSIGAN DATABASE DESIGN E127049 41 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Figure 11 - Create Trip Table

Figure 12 - Insert data in Trip Table

EJet_Astronomer Table

Figure 13 - Create Ejet_Astronomer Table

Figure 14 - Insert data in Ejet_Astronomer Table

P. GOWSIGAN DATABASE DESIGN E127049 42 | P a g e
a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Security Mechanisms Implemented:

1. Authentication and Authorization:

o Implement strong authentication mechanisms such as username/password or even

more secure methods like multi-factor authentication (MFA).

o Define roles (e.g., admin, user) and assign appropriate permissions based on these
roles.

2. Encryption:

o Encrypt sensitive data at rest (on disk) and in transit (during communication between
client and server). Use strong encryption standards (e.g., AES-256).

3. Data Masking:

o Mask sensitive data (e.g., personally identifiable information) when displaying or

processing it for non-authorized users.

4. Audit Logs:

o Maintain audit logs to track database access and modifications. Logs should include
details like user actions, timestamps, and IP addresses.

5. Database Backup and Recovery:

o Regularly backup the database and ensure backups are encrypted and stored securely.
Implement a recovery plan in case of data loss or breach.

6. Parameterized Queries:

o Use parameterized queries or stored procedures to prevent SQL injection attacks.

User Groups and Access Permissions:

1. User Roles:

o Define roles such as administrators, astronomers, and colonists. Assign specific

permissions to each role to control access to data and functionality.

P. GOWSIGAN DATABASE DESIGN E127049 43 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

2. Access Control:

o Limit access to sensitive data based on the principle of least privilege. Ensure users
can only access the data necessary for their roles.

3. Granular Permissions:

o Use granular permissions to restrict access at the row and column levels where
applicable. For example, astronomers may access trip details but not personal
information of colonists.

4. Role-Based Access Control (RBAC):

o Implement RBAC to simplify permission management and ensure adherence to

security policies.

Database Maintenance:
1. Patch Management:

o Regularly apply patches and updates to the database management system (DBMS) and
associated software to mitigate vulnerabilities.

2. Performance Monitoring:

o Monitor database performance to identify any anomalies that could indicate security
issues or potential breaches.

3. Database Health Checks:

o Conduct regular health checks to ensure data integrity and detect any unauthorized
changes or anomalies in the database.

4. Training and Awareness:

o Provide training to database administrators and users on security best practices and
policies. Raise awareness about potential threats and how to mitigate them.

Evaluation of Effectiveness:
1. Security Audits:

P. GOWSIGAN DATABASE DESIGN E127049 44 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

o Conduct periodic security audits and penetration testing to assess the effectiveness of
implemented security measures.

2. Compliance:

o Ensure compliance with relevant data protection regulations (e.g., GDPR, HIPAA) and
industry standards (e.g., ISO 27001) applicable to your database.

3. Incident Response Plan:

o Have an incident response plan in place to quickly respond to and mitigate security
breaches or data breaches.

By implementing these security mechanisms, defining appropriate user groups and access
permissions, and ensuring regular database maintenance, you can enhance the security of your
database and meet user and system requirements effectively. Regularly review and update these
measures to adapt to evolving security threats and organizational needs.

DML with below mentioned queries

1. Select

Figure 15 - Select query

2. Where

Figure 16 - Where query

P. GOWSIGAN DATABASE DESIGN E127049 45 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

3. Insert

Figure 17 - Insert query

Figure 18 - Insert data

4. Update

Figure 19 - Update query

5. Between

Figure 20 - Between query

P. GOWSIGAN DATABASE DESIGN E127049 46 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

6. In

Figure 21 - In query

7. Group by

Figure 22 - Group by query

8. Order by

Figure 23 - Order by query

9. Having

Figure 24 - Having query

P. GOWSIGAN DATABASE DESIGN E127049 47 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Assess the usage of the above SQL statements with the examples from the developed database

To assess the usage of the provided SQL statements in the context of the described Mars
colonization scenario, we can analyze each statement and determine how it fits into the database
structure and requirements. Here’s a breakdown of each SQL statement, its purpose, and the
relevant data extracted:

1. SELECT * FROM Colonist;

This query selects all the columns from the Colonist table.
Purpose:
To retrieve the complete information of all colonists. This is useful for getting a comprehensive
view of all individuals participating in the Mars colonization program.

Example Result:
Table 1 - Colonist all query data

MarsColon First Middle Surn DateOf Qualifi A EarthA Gen Conta CivilS FamilyMem
izationID Name Name ame Birth cation ge ddress der ctNo tatus bersCount

123
PhD in
1985- Earth St, 12345 Marrie
1 John A Doe Astroph 39 M 2
01-15 Earth 67890 d
ysics
City, EC
456
Earth
MSc in
Smit 1990- Ave, 09876
2 Jane B Enginee 33 F Single 0
h 11-30 Earth 54321
ring
Town,
ET
789
MSc in Earth
Mille 1989- 44556
11 Liam K Robotic 35 Ln, M Single 0
r 05-23 67788
s Earth
City, EC

Relevance:
This query provides a full list of colonists, which is fundamental for tracking who is part of the
mission and their qualifications, ages, addresses, etc.

2. SELECT * FROM Colonist WHERE CivilStatus = 'Married';

This query selects all columns from the Colonist table for those colonists who are married.
P. GOWSIGAN DATABASE DESIGN E127049 48 | P a g e
a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Purpose:
To identify all married colonists. This can help in planning family accommodation and
understanding the demographic of participants.

Example Result:
Table 2 - Where query data

MarsColoni FirstN Middle Surn DateOf Qualific Ag EarthA Gen Contac CivilSt FamilyMemb
zationID ame Name ame Birth ation e ddress der tNo atus ersCount
123
PhD in
1985- Earth St, 123456 Marrie
1 John A Doe Astroph 39 M 2
01-15 Earth 7890 d
ysics
City, EC

Relevance:
This helps in understanding which colonists are bringing their families and requires family
arrangements on Mars.

3. INSERT INTO Colonist (MarsColonizationID, FirstName, MiddleName, Surname,

DateOfBirth, Qualification, Age, EarthAddress, Gender, ContactNo, CivilStatus,
FamilyMembersCount) VALUES (11, 'Liam', 'K', 'Miller', '1989-05-23', 'MSc in
Robotics', 35, '789 Earth Ln, Earth City, EC 12321', 'M', '4455667788', 'Single', 0);
This statement inserts a new record into the Colonist table.
Purpose:
To add a new colonist to the database.
Relevance:
This action updates the database with new participants, which is essential for keeping the
database current and accurate.

P. GOWSIGAN DATABASE DESIGN E127049 49 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

4. UPDATE Colonist SET Qualification = 'PhD in Astrophysics' WHERE

MarsColonizationID = 1;
This query updates the qualification of the colonist with MarsColonizationID = 1.
Purpose:
To correct or update the qualifications of a colonist.
Relevance:
Maintains accurate records of colonist qualifications, which are crucial for assessing their
suitability for the mission.

5. SELECT * FROM Trip WHERE TripDate BETWEEN '2024-03-01' AND '2024-05-

01';
This query selects all columns from the Trip table for trips scheduled between March 1, 2024,
and May 1, 2024.
Purpose:
To identify trips happening in a specific date range.
Example Result:
Table 3 - Between query data

TripID JetCode TripDate ColonistID NumberOfPassengers

101 EJ-001 2024-03-15 1 5
102 EJ-002 2024-04-20 2 3

Relevance:
Helps in trip scheduling and resource allocation, ensuring that all trips are planned and
managed effectively.

6. SELECT * FROM Colonist WHERE MarsColonizationID IN (1, 3, 5);

This query selects all columns from the Colonist table for colonists with specific IDs.
Purpose:
To retrieve information for specific colonists.

Example Result:
Table 4 - In query data

P. GOWSIGAN DATABASE DESIGN E127049 50 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

MarsColoni FirstN Middle Surn DateOf Qualific Ag EarthA Gen Contac CivilSt FamilyMemb
zationID ame Name ame Birth ation e ddress der tNo atus ersCount
123
PhD in
1985- Earth St, 123456 Marrie
1 John A Doe Astroph 39 M 2
01-15 Earth 7890 d
ysics
City, EC
321
Earth
Brow 1992- BSc in Blvd, 234567
3 Alice C 32 F Single 0
n 07-22 Biology Earth 8901
Metropo
lis, EM
987
Earth
MSc in
1978- Rd, 345678 Marrie
5 Robert D White Enginee 45 M 1
10-10 Earth 9012 d
ring
Village,
EV

P. GOWSIGAN DATABASE DESIGN E127049 51 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Activity -03

Definition of test plain

This is the test plan for
testing the smart movers’
company is used in our
POS application to
provide the current prices
of the items. There are
other databases used by
our application
e.g. inventory database
but these other databases
are out of scope of this
test.
P. GOWSIGAN DATABASE DESIGN E127049 52 | P a g e
a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Based on the function and

structure of a database,
DB testing can be
categorized into three
categories
Definition of test plain

This is the test plan for testing the Mars Colonization DBMS is used in our DBMS application to
provide the current prices of the items. There are other databases used by our application. inventory
database but these other databases are out of scope of this test. Based on the function and structure
of a database, DB testing can be categorized into three categories.

Structural Database Testing

It deals with table and column testing, schema testing, stored procedures and views testing,
checking triggers, etc.

Functional Testing
It involves checking functionality of database from user point of view. Most common type of
Functional testing are White box and black box testing.

Non-functional Testing
It involves load-testing, risk testing in database, stress testing, minimum system requirements, and
deals with the performance of the database.

Structural Database Testing

Structural database testing involves verifying those components of database, which are not exposed
to end users. It involves all the components of repository, which are used to store the data and are
not changed by the end users. Database administrators with good command over SQL stored
procedures and other concepts normally perform this testing. Discussed are the common
components tested with respect to Structural Testing
P. GOWSIGAN DATABASE DESIGN E127049 53 | P a g e
a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Schema / Mapping Testing

It involves validating the objects of front-end application with database object mapping. In Schema
Testing Sometimes it happens that the end user application objects are not correctly mapped or
compatible with database objects. Therefore, checking the validation of the various scheme formats
associated with the databases is required. It is required to find the unmapped objects in database,
like tables, views, columns etc. is required. There are various tools in the market that can
be used to perform object mapping in schemas.

Functional Testing
Functional testing is performed keeping in mind an end-user point of view; whether the required
transactions and operations run by the end-users meet the business specifications.

Black Box Testing

Black Box Testing involves verifying the integration of database to check the functionality. The test
cases are simple and are used to verify incoming data and outgoing data from the function.
Various techniques such as cause-effect graphing technique, equivalence partitioning and boundary-
value analysis is used to test the functionality of the database. (tutorialspoint.com.
2018)

Test Plan for Mars Colonization DBMS

1. Introduction
 Project Overview: Development of a DBMS to manage data for the Mars colonization
project, ensuring accuracy and consistency.

 Objectives: Verify that the system meets all user and system requirements, ensuring the
integrity and reliability of the data.

2. Scope
 In-Scope: Testing of all functionalities related to colonist data management, dependent
data recording, E-Jet information management, trip management, and user interface.

 Out-of-Scope: Testing of hardware components, third-party integrations not specified in

the requirements.

3. Test Objectives

P. GOWSIGAN DATABASE DESIGN E127049 54 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

 Verify data accuracy and consistency throughout the data life cycle.

 Ensure proper identification and management of colonists and their dependents.

 Validate the accurate recording of E-Jet details and trip information.

 Confirm the system meets user expectations and system requirements.

4. Test Environment
 Hardware: Servers, client machines, and network infrastructure.

 Software: DBMS, application software, operating systems, and relevant utilities.

 Data: Test data sets mimicking real-world scenarios including colonist details, dependent
details, E-Jet specifications, and trip schedules.

5. Test Cases
5.1 Colonist Management
 TC-01: Create a new colonist with all required details.

 TC-02: Update colonist information.

 TC-03: Delete a colonist record.

 TC-04: Validate unique Mars Colonization ID generation.

 TC-05: Ensure only one colonist per family rule is enforced.

5.2 Dependent Management

 TC-06: Add dependents to a colonist record.

 TC-07: Update dependent information.

 TC-08: Validate dependent relationship to colonist.

5.3 E-Jet Management

 TC-09: Record details of a new E-Jet.

 TC-10: Update E-Jet specifications.

 TC-11: Validate unique Jet unique code generation.

P. GOWSIGAN DATABASE DESIGN E127049 55 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

 TC-12: Categorize E-Jets by type (Nuclear engine, Hydro Splitter, Hydro-Nuc).

5.4 Trip Management

 TC-13: Schedule a new trip to Mars.

 TC-14: Assign colonists and dependents to a trip.

 TC-15: Ensure no return trip is scheduled for colonists.

 TC-16: Verify trip details (date, E-Jet, colonists, and dependents).

5.5 User Interface and Usability

 TC-17: Verify form validations for colonist and dependent entry.

 TC-18: Ensure clear error messages for incorrect data entry.

 TC-19: Test navigation and accessibility of the system.

5.6 Data Integrity and Security

 TC-20: Check data consistency across different modules.

 TC-21: Test data backup and recovery processes.

 TC-22: Ensure user authentication and authorization.

6. Test Data
 Sample Data Sets: Create test data for colonists, dependents, E-Jets, and trips to
simulate real scenarios.

7. Test Schedule
 Planning Phase: Define requirements, create test cases (Week 1-2)

 Setup Phase: Configure test environment, prepare test data (Week 3)

 Execution Phase: Run test cases, document results (Week 4-6)

 Reporting Phase: Analyze test results, report defects, and provide feedback (Week 7)

 Closure Phase: Validate fixes, perform regression testing, and finalize reports (Week 8)

8. Resources
 Testers: Assign roles to testers for different modules.

P. GOWSIGAN DATABASE DESIGN E127049 56 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

 Tools: Use tools for test management, defect tracking, and data generation.

 Documentation: Maintain detailed documentation of test cases, results, and defects.

9. Risk Management
 Potential Risks: Data inconsistency, incomplete requirements, technical issues.

 Mitigation Plans: Regular reviews, clear communication channels, contingency plans.

10. Approval
 Sign-Off: Obtain approval from project stakeholders before and after the test execution.

By following this test plan, E-Space Solutions (Pvt.) Ltd. can ensure the DBMS developed for the
Mars colonization project meets all the necessary requirements, providing a reliable and efficient
system for managing colonist and trip data.

P. GOWSIGAN DATABASE DESIGN E127049 57 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Test Plan for Mars Colonization Table

Table 5 - Test plan

P. GOWSIGAN DATABASE DESIGN E127049 58 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Test Case ID 1A Test Case Loging to the admin account

Description
Created By Mark Reviewed By Version 2.1

QA Tester’s Log

Tester's Name Mark Date Tested 19-July-2024 Test Case Pass

(Pass/Fail/Not
Executed)

S# Prerequisites: S# Test Data

1 Access to system 1

Test 1.Logging to the admin account

Scenario

Step # Step Details Expected Results Actual Results Pass / Fail / Not executed /
Suspended

1 Enter valid username Logging to admin Logging to admin account Pass

& password account
2 Enter valid username Logging to admin Unsuccessful logging to admin Fail
& invalid password account account
3 Enter invalid Logging to admin Unsuccessful logging to admin Fail
username & valid account account
password

Test Case ID 2A Test Case Register new user

Description
Created By Mark Reviewed By Version 2.1

QA Tester’s Log

Tester's Name Mark Date Tested 19-July-2024 Test Case Pass

(Pass/Fail/Not
Executed)
P. GOWSIGAN DATABASE DESIGN E127049 59 | P a g e
S# a
Prerequisites: S# Test Data
1 Access to system Downloaded by
1 Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Selected test data can be used to improve the effectiveness of testing

To assess the effectiveness of the test data provided, we need to execute the SQL scripts in a
database environment and compare the actual results with the expected results. This process ensures
that the data insertion and constraints are working correctly.
Expected Results
1. Table Creation

All tables should be created successfully without any errors.

2. Data Insertion

o Colonist Table: Insertion of 10 records.

o Dependent Table: Insertion of 10 records.

o EJet Table: Insertion of 10 records.

o Astronomer Table: Insertion of 10 records.

o Trip Table: Insertion of 10 records.

o EJet_Astronomer Table: Insertion of 6 records.

Expected Results in Tabular Format

Table Expected Rows Actual Rows Comments
Colonist 10 Check if all colonists are inserted
Dependent 10 Check if all dependents are inserted
EJet 10 Check if all jets are inserted
Astronomer 10 Check if all astronomers are inserted
Trip 10 Check if all trips are inserted
EJet_Astronomer 6 Check if all assignments are inserted
Table 6 - Update table result
Screenshots of the Actual Results

To gather actual results, run the following SQL queries to count the number of rows in each table
after inserting the data:

P. GOWSIGAN DATABASE DESIGN E127049 60 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Figure 25 - Count query

Verify Data Insertion
Execute the SELECT COUNT(*) queries and capture the actual results.
Compare Expected and Actual Results
Compare the actual results with the expected results to determine if the test data has been inserted
correctly.
Conclusion
If the actual results match the expected results, the test data is considered valid, and the database
schema and constraints are functioning as intended. If there are discrepancies, review the SQL
scripts for any issues and correct them.

Example Conclusion
Table 7 - Successful data insert

Table Expected Rows Actual Rows Comments

Colonist 10 10 Data insertion successful
Dependent 10 10 Data insertion successful
EJet 10 10 Data insertion successful
Astronomer 10 10 Data insertion successful
Trip 10 10 Data insertion successful
EJet_Astronomer 6 6 Data insertion successful

P. GOWSIGAN DATABASE DESIGN E127049 61 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Activity – 04

Recommendations and Conclusion

To gather independent feedback:
 Conduct surveys, interviews, or workshops with non-technical users (like project managers,
astronauts, potential colonists) and developers.

 Gather insights on usability, data accuracy concerns, and specific functionality needs.

 Document feedback comprehensively and categorize it into enhancement opportunities (e.g.,

user interface improvements, additional data validation rules).

 Implement suggested improvements iteratively to refine the DBMS for optimal usability and
functionality.

By following these steps, you can create a robust DBMS tailored to E-Space Solutions' needs for
the Mars colonization project, ensuring data accuracy and supporting informed decision-making
throughout the mission lifecycle.

Produce a user manual (user guide) for the developed system

1. Introduction
The MCDBMS is designed to maintain the accuracy and consistency of data throughout the
colonization project. The system helps manage information about colonists, their dependents, E-
Jets, and trip schedules.
2. System Requirements
 Operating System: Windows 10 or later, macOS 10.15 or later

 RAM: 8GB or more

 Hard Disk: 500MB of free space

 Database: MySQL 8.0 or later

3. Installation Guide
1. Download the Setup File

o Obtain the MCDBMS setup file from the official E-Space Solutions website.

2. Run the Installer

P. GOWSIGAN DATABASE DESIGN E127049 62 | P a g e
a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

o Double-click the setup file to begin the installation process.

3. Follow the On-Screen Instructions

o Complete the installation by following the prompts.

4. Database Configuration

o Configure the database connection settings as per your environment.

4. Logging In
1. Open the Application

o Launch the MCDBMS application.

2. Enter Credentials

o Input your username and password.

3. Click Login

o Press the 'Login' button to access the main menu.

5. Main Menu Overview

The main menu provides access to various modules of the system:
 Colonist Management

 Dependent Management

 E-Jet Management

 Trip Scheduling

 Logout

6. Forms and Data Entry

Colonist Registration
1. Navigate to Colonist Management

o Click on 'Colonist Management' from the main menu.

P. GOWSIGAN DATABASE DESIGN E127049 63 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Figure 26 - Dashboard

2. Add New Colonist

o Click 'Add New Colonist'.

3. Fill in the Details

o Mars Colonization ID

P. GOWSIGAN DATABASE DESIGN E127049 64 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

o First Name

o Middle Name

o Surname

o Date of Birth

o Qualification

o Age

o Earth Address

o Gender

o Contact No

o Civil Status

o Number of Family Members

4. Submit

o Click 'Submit' to save the colonist details.

P. GOWSIGAN DATABASE DESIGN E127049 65 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Figure 27 - Add details Form

Dependent Registration
1. Navigate to Dependent Management

o Click on 'Dependent Management' from the main menu.

2. Add New Dependent

o Click 'Add New Dependent'.

3. Fill in the Details

o Select Colonist ID

o Name

o Date of Birth

P. GOWSIGAN DATABASE DESIGN E127049 66 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

o Age

o Gender

o Relationship to Colonist

4. Submit

o Click 'Submit' to save the dependent details.

E-Jet Registration
1. Navigate to E-Jet Management

o Click on 'E-Jet Management' from the main menu.

2. Add New E-Jet

o Click 'Add New E-Jet'.

3. Fill in the Details

o Jet Unique Code

o Number of Passenger Seats

o Nuclear Engine Power

o Made Year

o Weight

o Power Source

o Jet Type (Nuclear Engine, Nuclear & Hydro Splitter Engine, Hydro-Nuc Engine)

4. Submit

o Click 'Submit' to save the E-Jet details.

Trip Scheduling
1. Navigate to Trip Scheduling

o Click on 'Trip Scheduling' from the main menu.

P. GOWSIGAN DATABASE DESIGN E127049 67 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

2. Add New Trip

o Click 'Add New Trip'.

3. Fill in the Details

o Trip ID

o E-Jet Code

o Departure Date

o Colonist ID

o Number of Dependents

4. Submit

o Click 'Submit' to save the trip details.

Figure 28 - Add Trip Form

P. GOWSIGAN DATABASE DESIGN E127049 68 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

7. Data Management
Viewing Records
1. Select Module

o Navigate to the desired module (Colonist, Dependent, E-Jet, Trip).

2. View List

o The list of records will be displayed.

Updating Records
1. Select Record

o Click on the record you wish to update.

2. Edit Details

o Modify the necessary details.

3. Submit

o Click 'Submit' to save the changes.

P. GOWSIGAN DATABASE DESIGN E127049 69 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Figure 29 - Update Trip Form

Deleting Records
1. Select Record

o Click on the record you wish to delete.

P. GOWSIGAN DATABASE DESIGN E127049 70 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

2. Delete

o Click 'Delete' and confirm the deletion.

8. Reports
1. Navigate to Reports

o Click on 'Reports' from the main menu.

2. Select Report Type

o Choose the type of report you need (e.g., Colonist List, Trip Schedule).

3. Generate Report

o Click 'Generate' to produce the report.

4. Export/Print

o Export the report in the desired format (PDF, Excel) or print it.

9. Troubleshooting
 Login Issues

o Ensure the username and password are correct.

o Contact support if you forgot your credentials.

 Data Entry Errors

o Verify all required fields are filled correctly.

o Ensure data formats (e.g., date) are correct.

 System Crashes

o Restart the application.

o Contact technical support if the issue persists.

10. Support
For further assistance, contact E-Space Solutions technical support at:
 Email: support@espace-solutions.com
P. GOWSIGAN DATABASE DESIGN E127049 71 | P a g e
a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

 Phone: +94 715859889

All developed forms with source code & suitable diagrams

Data Flow Diagram (DFD)

Level 0 DFD
Level 0 DFD provides a high-level overview of the system processes and data flow.

Figure 30 - Level 0 DFD Diagram

Level 1 DFD
Level 1 DFD breaks down the main processes into sub-processes for detailed understanding.

Figure 31 - Level 1 DFD Diagram

P. GOWSIGAN DATABASE DESIGN E127049 72 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Use Case Diagram

The use case diagram represents the interactions between the users (actors) and the system.

Figure 32 - Use Case Diagram

Class Diagram
The class diagram provides a detailed view of the system's classes, attributes, methods, and
relationships.

Figure 33 - Class Diagram

P. GOWSIGAN DATABASE DESIGN E127049 73 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

Assess the developed database by suggesting future enhancements

The author uses testing method for maintain the system. Author tests the system by three weeks.
Author checked the data insert correctly to the database, search function is working, update
functions are working, delete function are working perfect and the GUI performance. Check all the
calculation are working correctly month by month.

Database system for any company is the base. Information, company documents, important
certificates, communications and what not is done on computers. The entire company
depends upon the IT department to maintain the systems of the company. There is a cloud storage
system. Therefore, the author can develop the system to web base system. If something happens to
the database, then data in the cloud are safe. Then system develop to automatic backup. If the
system does not work today, then it can work with yesterday back up data.

1. Normalization and Data Integrity

 Colonist Table

o Age Calculation: The Age column is redundant if DateOfBirth is available. You can
calculate age dynamically using a query instead of storing it. This avoids
inconsistencies.

o Address: Consider splitting EarthAddress into separate columns (e.g., StreetAddress,

City, State, ZipCode) for better data management and querying.

 Dependent Table

o Age Calculation: Similar to the Colonist table, Age can be calculated dynamically
from DateOfBirth.

 EJet Table

o Power Source: Add constraints or an enumeration table for PowerSource to ensure

consistency in values.

2. Indexes and Performance

P. GOWSIGAN DATABASE DESIGN E127049 74 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

 Indexes: Add indexes on foreign key columns (MarsColonizationID, JetUniqueCode,

AstronomerID) in Trip and EJet_Astronomer tables to improve join performance and query
speed.

3. Referential Integrity and Constraints

 Cascade Delete: Ensure that the cascading delete behavior aligns with your business rules.
For example, when a Colonist is deleted, all related Dependents and Trips should be
appropriately handled.

 Unique Constraints: Consider adding unique constraints where applicable, such as on the
ContactNo field in the Colonist table if each contact number should be unique.

4. Additional Features and Tables

 Address Table: Create a separate Address table to handle various types of addresses (e.g.,
EarthAddress, MarsAddress) and link it to Colonist and other entities.

 Jet Maintenance: Add a table to track maintenance records for jets, including maintenance
dates, details, and costs.

 Astronomer Availability: Track astronomer availability to ensure that scheduling trips takes
into account their availability.

 Trip Status: Add a status field to the Trip table to track the state of trips (e.g., Scheduled,
Completed, Cancelled).

5. Data Types and Conventions

 Date Formatting: Ensure consistent date formatting across the database. Use DATE type for
all date fields.

 String Lengths: Review and adjust the lengths of VARCHAR fields according to expected
maximum sizes. For instance, JetType and PowerSource may have shorter maximum lengths.

6. Security and Privacy

 Sensitive Data: Ensure that sensitive data (e.g., contact numbers) is protected through
encryption or hashing if needed.

 Access Control: Implement proper access controls and user roles to manage who can view or
modify data.

P. GOWSIGAN DATABASE DESIGN E127049 75 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)
 lOMoARcPSD|56395220

7. Documentation and Comments

 Schema Documentation: Document the schema with comments on tables and columns to
clarify their purpose and any constraints applied.

 Change Management: Establish a versioning system for schema changes to track

modifications and maintain data integrity.

Conclusion

This assignment has been written and processed to my knowledge. I have deserved when studying
Database in HND in Computing. Furthermore, this assignment consists of solutions which
have been described with the problems stated with scenario of given assignment brief and
explained by lecturer. Solutions which have been stated with proper evidence and some other criteria
of actions should be taken from the side of the assignment scenario creators should be convinced to
apply the solution immediately to turn on the compatibility of lab facility to set up new environment
and atmosphere of programming and designing as well as document modeling such as word
processing systems. Moreover, this assignment has been affiliated to the terms and conditions which
have been described with the rules have been learned from classroom and existing
assignment documentation. And solution which has been described evaluated from some users and
some staffs of my institution to get positive feedback regarding the solution, and I hope there will be
some modifications and re-configurations would be available in future for increasing the
performance of the system configuration management systems with the assignment brief.

P. GOWSIGAN DATABASE DESIGN E127049 76 | P a g e

a
Downloaded by Ali abbas shah (aliabbas.55723@gmail.com)