lOMoARcPSD|46108701

(E189241)database - DDD
assignment

database design development (ESOFT Metro Campus)

Scan to open on Studocu

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Studocu is not sponsored or endorsed by any college or university

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Higher Nationals - Summative Assignment Feedback Form

Student Name/ID W.Sithmi Sandalika
Unit Title Unit 04 – Database Design & Development

Assignment Number 1 Assessor

Date Received 1st

Submission Date submission
13/11/2024 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.

Sithmi sadalika Database Design and Development

E189241 1

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

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 board.
external moderation has taken place and grades decisions have been agreed at the assessment

BTEC HN Summative Assignment Feedback Form

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

Sithmi sadalika Database Design and Development

E189241 2

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

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.

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: Assessor name:
W.Sithmi Sandalika

Issue date: Submission date: Submitted on:

24/04/2024 13/11/2024 13/11/2024

Sithmi sadalika Database Design and Development

E189241 3

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Programme: Pearson BTEC HND in Computing

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

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

II. 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: Date: 13/11/2024

Unit 04: Database Design & Development

A. Assignment Brief
Student Name/ID W.Sithmi Sandalika
Number

Unit Number and Title Unit 04 – Database Design & Development

Academic Year 2024/2025

Sithmi sadalika Database Design and Development

E189241 4

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Unit Tutor

Assignment Title Database System for E-Space Solution

24/04/2024
Issue Date
Submission Date 13/11/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.

Transferable skills and competencies developed

Computing-related cognitive skills :

Sithmi sadalika Database Design and Development

E189241 5

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

• 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
tradeoffs

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

Sithmi sadalika Database Design and Development

E189241 6

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Vocational scenario

Sithmi sadalika Database Design and Development

E189241 7

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

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.

Sithmi sadalika Database Design and Development

E189241 8

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

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.

Sithmi sadalika Database Design and Development

E189241 9

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

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
Sithmi sadalika Database Design and Development
E189241 10

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

meaningful & relevant to the given scenario.

Sithmi sadalika Database Design and Development

E189241 11

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Sithmi sadalika Database Design and Development

E189241 12

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

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.

Sithmi sadalika Database Design and Development

E189241 13

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

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.

Sithmi sadalika Database Design and Development

E189241 14

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

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-schemadesignguide/
[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/00207373(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
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
Sithmi sadalika Database Design and Development
E189241 15

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

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

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

LO2 Develop a fully-functional relational database system, based LO2 & LO3
on an existing system design

Sithmi sadalika Database Design and Development

E189241 16

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

P2 Develop the database M2 D2 Evaluate the effectiveness of the

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

LO3 Test the system against user and system requirements

P4 Test the system against

user
and system M4 Assess the effectiveness of
requirements. the testing, including
an

explanation of the choice of test

data used.

LO4 Produce technical and user documentation.

P5 Produce technical and user

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

1.1

Contents
I. Plagiarism..................................................................................................................................................................
II. Student Declaration..................................................................................................................................................
A. Assignment Brief...................................................................................................................................................
B. Learning Outcomes and Assessment Criteria......................................................................................................
Sithmi sadalika Database Design and Development
E189241 17

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

1.1...............................................................................................................................................................................
1.2 Activity 1...............................................................................................................................................................
III. Activity 2.................................................................................................................................................................
IV. Activity 3................................................................................................................................................................
V. Activity 4.................................................................................................................................................................
Produce a user manual (user guide) for the developed system..........................................................................
1. Normalization of Database Tables..................................................................................................................
2. Optimizing Indexes.........................................................................................................................................
3. Data Security Enhancements..........................................................................................................................
4. Backup and Disaster Recovery Plans..............................................................................................................
5. Scalability Considerations...............................................................................................................................
6. Data Quality and Validation............................................................................................................................
7. Improved Reporting and Analytics.................................................................................................................
8. Automated Maintenance and Monitoring......................................................................................................
9. Documentation and Training..........................................................................................................................
1. Class diagram of the E-Space Solutions (Pvt.) Ltd (DBMS)..............................................................................
1. flow charts of the E-Space Solutions (Pvt.) Ltd (DBMS)..................................................................................
Assess the developed database by suggesting future enhancements to ensure the effectiveness of
the system...........................................................................................................................................................

Pearson Higher
Sithmi sadalika Database Design and Development
E189241 18

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Nationals in
Computing

Unit 03: Database Design and Dvelopment

Assignment 01
1.2 Activity 1

The UK Data Service -We need to establish both user and system requirements in creating the E-Space
Solution System. This means working out what the system needs to do and also, how it will be
implemented on a technical level in order to meet those requirements.

A. User Requirements

I. Data Entry Operators:

• Enter colonist and dependent information.
• Enter information on your e-jet: jet code, type of engine, passenger capacity.
• Capture the details of every trip, such as trip ID, jet code, and launch date.
Assign jobs and housing to colonists.

I. . System Administrators:
Sithmi sadalika Database Design and Development
E189241 19

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

I. Requirements
• Generate More Detailed Reports • Manage user access
and data integrity.
• Store data accurately and consistently.

II. Colony Superintendent:

I. Requirements:
• Access housing information and manage colonist housing assignments.
• Generate colony management reports (e.g., housing assignments, job assignments).

III. Pilots

I. Requirements
• View trip data; access passenger lists.
• Details of Assigned E-Jets.

I. System Requirements
I. Data Integrity & Consistency:

• Provide accurate recording and updating of information for colonists and their
dependents.
• The data is consistent and free of errors in all database tables.

II. Scalability:

• Can cater to an increasing number of colonists, dependents, and trips as the

engagement with the project goes on.

III. Security:

• User authentication and access control will be applied while safeguarding

critical data.
• Report generation and data management are done only by authorized
personnel.

Sithmi sadalika Database Design and Development

E189241 20

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

IV. Performance:

• Handle huge datasets efÏciently, specifically when generating reports or retrieving the
trip data.
• Provide easy and fast access to data for decision-making.

V. Reporting:

• The system shall be designed for reporting purposes; however, this is a later phase in the
implementation process.

1. ) B.
Table 1-the table of attributes and entitys

ENTITY PRIMARY KEY ATTRIBUTES FOREIGN KEY

Colonist MarsColonizationID FirstName,
MiddleName,
Surname, DateOfBirth,
Qualification, Age,
EarthAddress, Gender,
ContactNo, CivilStatus,
FamilyMembersCount.
Dependent DependentID Name, DateOfBirth, ColonizationID
Age, Gender,
RelationshipToColonist,
E-jet JetCode NumberOfSeats,
EngineType, MadeYear,
Weight, PowerSource.
Trip TripID LaunchDate, JetCode,
ReturnDate. ColonizationID,
PilotID
Pilot PilotID Name, Qualifications, JetCode
Rank
House ColonyLotNumber NumberOfRooms, ColonizationID
SquareFeet,
Job JobID JobType ColonizationID

Sithmi sadalika Database Design and Development

E189241 21

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Relationships
1. Colonist ↔ Dependent:
• Relationship: One-to-Many
• Participation: Colonist (Mandatory), Dependent (Optional).

2. Colonist ↔ Trip:

• Relationship: Many-to-One
• Participation: Colonist (Mandatory), Trip (Mandatory).

3. Pilot ↔ Trip:

• Relationship: One-to-Many
• Participation: Pilot (Mandatory), Trip (Mandatory).

4. E-Jet ↔ Trip:
• Relationship: One-to-Many
• Participation: E-Jet (Mandatory), Trip (Mandatory).

5. Colonist ↔ House:
• Relationship: Many-to-One
• Participation: Colonist (Mandatory), House (Mandatory).

6. Colonist ↔ Job:
• Relationship: Many-to-Many
• Participation: Colonist (Mandatory), Job (Mandatory). ER Diagram

Sithmi sadalika Database Design and Development

E189241 22

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 1-ER diagram

C. Logical database system

Sithmi sadalika Database Design and Development

E189241 23

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 2-logical er diagram

D. Key

• In a database management system, an attribute or a group of attributescan act as a

key that uniquely or partially identifies a tuple in a relation. In a relational
database,keys also define the relationships between different tables and columns.
Key values referto the various values within a key.

Types of keys in database management systems: -

• Primary Key
• Foreign Key
• Candidate Key
• Super Key
• Composite Key
• Alternate Key
Sithmi sadalika Database Design and Development
E189241 24

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

• Unique Key
(BUT PRIMARY KEY AND THE FOREIGN KEY IS THE MAIN KEYS IN A DATABASE)

2. Primary Key ✓ Primary keys are columns or columns that store values that uniquely
identify each row in a
table. The table must have a primary key for time to insert, update, restore, or delete
data from a database table.

3. Foreign Key ✓ Foreign keys are the keys that uniquely connect two tables together.
A foreign key requires that
every value in one column or set of columns must appear in the Primary Key of the
referential table.
Foreign keys are useful for data and referential
integrity.

4. Transitive Functional Dependency

✓ “Transitive dependency” refers to an indirect relationship between table data that
causes functional dependency. All transitive dependency must be removed to satisfy
the normalization requirement of Third Normal Form.

I. What is a Database?

• Well, a database is simply a file cabinet on your computer for storing information
in such a way that what we want to retrieve at a later time is easily retrievable.
For example, consider the school library catalog of books.

II. What is Normalization?

• Normalization is a process that helps design a database so that it does not have any
duplicated records which are related and, therefore, maintained separately. This
eventually minimizes data redundancy and avoids errors, enabling ease of use and
economy of space.

Why Do We Need Normalization?

• Avoiding Duplicates: Think about a student list and his favorite book. It becomes
tricky and cumbersome if you have the same student listed again and again for
different books that he likes. Normalization keeps this type of information neat and
avoids repetition of the same data over and over again.

Sithmi sadalika Database Design and Development

E189241 25

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

• Making Changes Easier: Suppose a student changes his favorite book. Because of
normalization, we only need to update in one place. This is because it ensures we
are not forced to find the same information over and over and change it in many
different spots.

• Organizing Data Logically: Much as in a library, where books are organized on topics
or by author, normalization groups related information together. You would not mix
information about students with information about books in the same place.

The database normalization requirement

• First Normal Form (1 NF)

• Second Normal Form (2 NF)
• Third Normal Form (3 NF)
• Boyce Codd Normal Form or Fourth Normal Form (BCNF or 4 NF)
• Fifth Normal Form (5 NF)
• Sixth Normal Form (6 NF)

Figure 3-Normalization

Sithmi sadalika Database Design and Development

E189241 26

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

e. Wireframes that above output the above scenario

Figure 4-welcome form

Sithmi sadalika Database Design and Development

E189241 27

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 5-colonist form

Sithmi sadalika Database Design and Development

E189241 28

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 6-dependent 28nrolme

Sithmi sadalika Database Design and Development

E189241 29

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 7-e jet form

Sithmi sadalika Database Design and Development

E189241 30

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 8-astronomer form

Sithmi sadalika Database Design and Development

E189241 31

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 9-Trip details

Sithmi sadalika Database Design and Development

E189241 32

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 10-Job form

f. Evaluate the effectiveness of the design provided (ERD and Logical design) in light
of the identified user and system requirements.

The effectiveness of a framework is weighed on the way it responds to the questions by

the customers. Accountability in the process of the designing of the framework is
developing into a generic ethic in the provisioning of framework convenience based on
customer
requirements spotted in connection with their business vision. The end-user network
isn’t inclined to hang around for long periods of time waiting for new/redesigned
framework
constraints to go live. Hence frameworks/methods such as “handy”
enhancement have gained immense popularity. These methods require the
framework capability implementation to be proven as the framework evolves.
Framework
development parties prove their advancement during brief social meetings
a scrum," In Ih a logical picture emerges of the course of events In the
provisioning of framework limits that match customer needs.
III. Activity 2

Sithmi sadalika Database Design and Development

E189241 33

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

a. An SQL DDL statement allows for the development of a relational database system according to
an ER diagram by creating tables and defining the relationships, adding data validation rules.
Normally, in the ER diagram, there will be entities that will turn into tables, attributes turning
into columns, and relationships into foreign keys.

Figure 11-colonist query

Figure 12-ejet query

Sithmi sadalika Database Design and Development

E189241 34

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 13-dependent query

Figure 14-house query

Sithmi sadalika Database Design and Development

E189241 35

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 15-welcome form

Sithmi sadalika Database Design and Development

E189241 36

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 16-Colonist detailed form

Figure 17-Ejet details forms

Sithmi sadalika Database Design and Development

E189241 37

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 18-Colony house Detailed form

Figure 19-dependedent form

Sithmi sadalika Database Design and Development

E189241 38

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 20-Astronomer info form

Figure 21-Job details form

b. To present the evidence of using an appropriate IDE, we can develop a simple interface for
interaction to implement a database. In this regard, we consider such popular IDE as Microsoft
Sithmi sadalika Database Design and Development
E189241 39

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

SQL Server Management Studio, MySQL Workbench, or for PostgreSQL. These IDEs have
graphical interfaces for inserting, updating, and deleting database data.

Figure 22-Insert values colonist

Figure 23-Insert value pilot

Sithmi sadalika Database Design and Development

E189241 40

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 24-Update values

Figure 25-delete values

Sithmi sadalika Database Design and Development

E189241 41

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

c. This involves the creation of user groups, setÝng up their access permissions, and ensuring
security and maintenance measures of the database in a Microsoft SQL Server database. Let’s
consider the following aspects when trying to implement proper security mechanisms:

5. Database Solution Evaluation

I. Effectiveness in Relation to User and System Requirements:

• User Requirements: The solution in this database is tailored for specific user
requirements, basically assigning roles and permissions based on user requirements.
Students may only need SELECT access to their grades, while an administrator would
have broader access to modify student information.

• System Requirements: Structuring roles and permissions within the database optimizes
performance and security. This helps in ensuring that only users with the right
credentials have the capability to view or modify data, thereby reducing undesired
access or breaches.

II. System Security Mechanisms:

• It will create database users, roles, and server roles to provide fine-grained access
control over ‘who has access to what’. The idea here is going to be provision of highly
sensitive information only to those who need it, while at the same time ensuring the
principle of least privilege is enforced.

• Permissions: Instead of directly assigning permissions to users, assign them to the roles
that the users belong to. This manages and secures the permissions so that they remain
constant for each similar type of user.

III. Database Maintenance:

• Ongoing Management: This is a structured approach to the management of users and

permissions, whereby updates and changes are easily done. This comes in quite handy
in this dynamic environment where sometimes the role of the user or the requirements
for access could be subject to frequent change.

• Audit Trails and Monitoring: This security measure keeps monitoring of the groups,
users, and roles for tracking actions throughout the database in much detail. This
ensures that possible security threats and unauthorized access can be detected quickly.

Sithmi sadalika Database Design and Development

E189241 42

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

d. DML stands for Data Manipulation Language. It’s a subset of SQL—Structured Query
Language—used to manipulate the data within a database. Core DML commands
include SELECT, INSERT, UPDATE, and DELETE, allowing a user to retrieve, add, modify,
and remove data in database tables.

I. INSERT

Sithmi sadalika Database Design and Development

E189241 43

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

II. Update

III. WHERE AND DELETE

Sithmi sadalika Database Design and Development

E189241 44

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

e. To assess the usage of the SQL statements provided earlier and demonstrate that the
data extracted is meaningful and relevant to the given scenario

6. SQL SELECT Statement: Extracting Data I. Purpose and Relevance:

• The query should result in basic information about
students, such as ID, FirstName, LastName.
• Relevance: The latter action aids in facilitating a student
list that can be used for functions like sending of notices
or in the administration for colonist records.

7. .SQL INSERT Statement: Adding Data

• Purpose and Relevance:

• The provided query will create a new student named Emma Johnson in the
table colonist.

• Relevance: The school database requires the regular addition of new students
to update their relevant records.
8. SQL DELETE Statement: Removing Data

• Purpose and Relevance:

• This query updates the email address of the student with colonistD .

• Relevance: Updating contact information ensures continuous effectiveness

and accuracy of communication, very important to any school administration.

1. Evaluation of SQL Statement Usage

• The above-mentioned SQL statements illustrate some key scenarios of

data management in the colonist:

I. Relevance and Accuracy of Data:

• The extracting information, like students’ lists or 45nrolment info,

agrees with the school administration and teacher needs.
The accuracy of data maintains the relevance by updating and deleting the
database.

Sithmi sadalika Database Design and Development

E189241 45

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

II. Fulfillment of System Requirements:

• These queries meet several of the system’s requirements, including

providing 46nrolment management, proper maintenance of the
students’ documents, and flexible manipulation of the data.
• The rich functionality of SQL, especially JOINs and GROUP BY, enhances
the capability in producing complex reports and insight generation for
effective school management.
III. Security and Maintenance:

• Constraints and implementing user-specific permissions follow the best

security practices.
• It added protection for sensitive data by making sure that only relevant
information is made available to each user, enhancing the security of
the overall system.

IV. Activity 3

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

Table 2-test cases 1

Test No Procedure Test Data Rusalt

Colonist lot number

Number of rooms
1 Insert a new colony Assign to colonist id
hous Square feet
Details
Pass

Sithmi sadalika Database Design and Development

E189241 46

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 26-Update button

Table 3-test cases 2

Test No Procedure Test Data Rusalt

Job name description

2 Updated,saved deleted Pass

in job

Figure 27-Job form updated, deleted, saved buttons

Table 4-test cases 3

Test No Procedure Test Data Rusalt

Astronomer name
Designation
Sithmi sadalika Database Design and Development
E189241 47

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

3 Delete Astronomer Qualifications Pass

Space hours Jet
code

Figure 28-Astromer form deleted

Sithmi sadalika Database Design and Development

E189241 48

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

V. Activity 4

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.

Figure 29-feedback form

Sithmi sadalika Database Design and Development

E189241 49

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 30-feedback form 1

Sithmi sadalika Database Design and Development

E189241 50

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 31-Feedback form 2

Sithmi sadalika Database Design and Development

E189241 51

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 32-feedback form 3

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

Step 1

• You can choose any of this

Sithmi sadalika Database Design and Development

E189241 52

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Step 2-
• If you go to the colonist form you can find their informations like this and save it edit
it and delete it.

Sithmi sadalika Database Design and Development

E189241 53

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Step 3-

• If you want to know about dependent information you can choose dependent
info form and you can just save their information in that form

Step 4-

• You can choose e jet form and check their info and save,edit and deleted it.

Sithmi sadalika Database Design and Development

E189241 54

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

•
Step 5-

• In this form you can find their info an sav delete and edit
it

Step 6-

Sithmi sadalika Database Design and Development

E189241 55

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Step 7

• Like this can you can just save delete and edit their info like that

Sithmi sadalika Database Design and Development

E189241 56

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Sithmi sadalika Database Design and Development

E189241 57

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Welcome form

Sithmi sadalika Database Design and Development

E189241 58

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Colonist form

Sithmi sadalika Database Design and Development

E189241 59

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Sithmi sadalika Database Design and Development

E189241 60

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Dependent form

Sithmi sadalika Database Design and Development

E189241 61

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Sithmi sadalika Database Design and Development

E189241 62

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Astronomer inf

Sithmi sadalika Database Design and Development

E189241 63

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Sithmi sadalika Database Design and Development

E189241 64

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

E jet form

Sithmi sadalika Database Design and Development

E189241 65

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Sithmi sadalika Database Design and Development

E189241 66

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Sithmi sadalika Database Design and Development

E189241 67

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Sithmi sadalika Database Design and Development

E189241 68

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Colonist job form

Sithmi sadalika Database Design and Development

E189241 69

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Colony house form

Sithmi sadalika Database Design and Development

E189241 70

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Sithmi sadalika Database Design and Development

E189241 71

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Job form

Sithmi sadalika Database Design and Development

E189241 72

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Sithmi sadalika Database Design and Development

E189241 73

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Sithmi sadalika Database Design and Development

E189241 74

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

d. There are some future enhancements planned that can ensure the effectiveness
of the developed database in the management of a Mars colonization project. It
targets optimizing the performance and scalability, as well as data integrity and
security of the system. Some such suggestions for future enhancement are
hereafter stated:

1. Normalization of Database Tables

• Purpose: Reduction in redundancy and improving data integrity.

Improvement: Ensure that the database has all its tables normalized to at least 3NF.
This systematizes columns and tables of a database to minimize data redundancy
and dependency. This helps in avoiding anomalies during insertion, update, and
deletion of data.

2. Optimizing Indexes

• Purpose: The purpose is to improve the performance of queries.

Indexing of frequently used columns in queries, mainly occurring in the WHERE,
JOIN, and ORDER BY clauses. However, too many indexes slow down operations like
INSERT, UPDATE, and DELETE, hence care should be exercised. Indexes are to be
reviewed on a regular basis for optimization according to query performance
analysis.

3. Data Security Enhancements

• Purpose: Protect sensitive data and meet compliance requirements on legislation

relating to data protection.
Security Improvement: Introduce encryption mechanisms on sensitive data, such as
personal information on the colonists or confidential mission data. Ensure RBAC is
in place to limit access, based on user roles, to make certain that access or
modification can be done only by relevant personnel. Audit and monitor access of
databases continuously to trace unauthorized activities.

4. Backup and Disaster Recovery Plans

• Purpose: The plans are aimed at reducing the chances of system downtowns in case
of failures, ensuring that data is always available.
Improvement: Create a robust backup and disaster recovery strategy by getÝng
frequent backups of data and define a process regarding how data will be restored
in case of loss or corruption. Consider running automatic backups, storing those

Sithmi sadalika Database Design and Development

E189241 75

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

backups in different geographic locations to protect them from physical damage or

any natural disasters.
5. Scalability Considerations

• Purpose: The database is to be prepared to cater to future growth and enhanced

volume of data.
Improvement: Devise a database schema and infrastructure that is ready to scale,
either vertically (more resources on current servers) or horizontally (more servers
to share load). This may include sharding, which is basically the breakup of the
database into smaller, more feasible pieces, or, if need be, distributed databases.

6. Data Quality and Validation

• Purpose: To ensure that data is valid and consistent.

Improvement: Check data validity to be applied at the database level using
constraints such as CHECK, FOREIGN KEY, and UNIQUE to enforce integrity. Stored
procedures or triggers can also be developed to validate data for insertion and
updating, making sure that they match the criteria set.

7. Improved Reporting and Analytics

• Purpose: Deliver improved insights into data to support better decision-making.

Improvement: Follow-up of complex queries, stored procedures, and views for
detailed reporting and analytics should be developed. SetÝng up data visualization
tools that make the insights more digestible for the stakeholders. The database may
further be integrated with a business intelligence tool that provides more
businessrelated data analysis features.

8. Automated Maintenance and Monitoring

• Purpose: For the best database performance to be available always.

Improvement: Provided indexing, update of statistics, database health checking
through automated maintenance tasks. Track database performance to spot any
potential problems and alert the administrator in case of unusual activity or
performance degradation using monitoring tools.

9. Documentation and Training

• Purpose: The system has to be well understood and maintainable. Improvement:

Plan and develop an entire system document of the database schema, data
models, relation ships, stored procedures, and common queries. Provide the

Sithmi sadalika Database Design and Development

E189241 76

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

required training to the database administrator and the end users so that they
appropriately learn the use and maintenance of the system.
10. Regular Updates and Audits
Purpose: Updating the database system on regular basis to keep it safe and secure.
• Improvement: The DBMS shall be upgraded regularly to its latest version with new
features, security patches, and performance enhancement. The database shall be
regularly audited with respect to security policies, performance standards,

Data Entry operates

You can insert

dependent
information

You can insert E-Jet

information

You can insert

House information

Assign a trip

Assign a

Sithmi sadalika Database Design and Development

E189241 77

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

System Administrators

View trip details

Pilots

Make trip report

1. Class diagram of the E-Space Solutions (Pvt.) Ltd (DBMS)

Sithmi sadalika Database Design and Development

E189241 78

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

1. flow charts of the E-Space Solutions (Pvt.) Ltd (DBMS)

Submit colonist Information

Figure 33-submit colonist flowchart

Sithmi sadalika Database Design and Development

E189241 79

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 34-Update colonist details

Sithmi sadalika Database Design and Development

E189241 80

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

Figure 35-Delete colonist details

Assess the developed database by suggesting future enhancements to ensure the

effectiveness of the system.

However, to enhance the efÏciency of a developed database system, there are two planes that need
consideration in the process: The first plane or the remedies that may be applied forthwith in enhancing
the efÏciency of a developed database system The second plane, which, in the long run, is considered
Below are some suggestions

1. Data Integrity and Validation

• Validation Rules

For all the input fields, verify that there are input validations set such that erroneous inputs cannot be
entered, such as email address fields, date fields, etc.

• Referential Integrity

Use such integrity constraints as referential integrity like foreign keys to ensure proper
relational integrity in the database. • Data Normalization
You also need to assess the database schema with regards to normalization so that minimum
redundancy occurs while storing information.

Sithmi sadalika Database Design and Development

E189241 81

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

2. Performance Optimization

• Indexing

Improve queries by using indexes on the sought columns that are searched more often. But remember
not to drown in it since it complicates writing as well.

• performance Optimization

Continuously review and fine tune the SQL statements that are used so as to enhance the performance
of the queries particularly in large queries or if it is a repetitive query.

• Caching

Employ caching techniques for any data called frequently by various programs but rarely updated.

3. Scalability and Futureproofing

• Partitioning

It is also observed that partitioning of large tables will enable queries to be quicker and more
manageable when the size of the database grows.

• Scalable Architecture

It is, therefore, of importance that the design of the database architecture be done in a way that allows
for future growth. This would imply that one can scale vertically-for instance, through upgrading
hardware-or horizontally, by running multiple instances and hence distributing the load across many
servers.

• Cloud Integration

If not considered, it might be worth considering the migration of the database to the cloud for scaling.

CONCLUSION
This database design therefore provides a formalized and efÏcient management
of data about the Mars colonization project. It structures information on
colonists, dependents, E-Jets, trips, housing, and jobs in a systematic format that
ensures that the data is accurate, integrated, and accessible. The relation
between entities has been done in a manner that provides ease of access and
updating of data, while constraints enforce consistency of data throughout the
system. In addition, this design is scalable; further improvements can be added in
the future for reporting, which will assist decision-makers throughout the
project. This database would form the basis of a proper and efÏcient system for
Sithmi sadalika Database Design and Development
E189241 82

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

data management and thus act as the foundation for the entire task of colonizing
Mars.

Sithmi sadalika Database Design and Development

E189241 83

Downloaded by Jazaf Ahd ([email protected])

 lOMoARcPSD|46108701

REFERENCING

(W3Schools (2019). SQL Syntax. [online] W3schools.com. Available at:

https://www.w3schools.com/SQl/sql_syntax.asp.)

(https://www.grammarly.com/plagiarism-checker?msockid=25d9811fdf146ad1030495d6de516b22)

(https://www.bing.com/search?q=different+problem+solving+techniques&qs=SC&pq=different+proble
m+solving+tea&sc=1-
29&cvid=55EE250A3F0847C09A8B42978D9CCDE1&FORM=QBRE&sp=1&ghc=1&lq=0&ntref=1#)

( Codecademy. (n.d.). SQL Commands. [online] Available at:

https://www.codecademy.com/article/sqlcommands.)

Sithmi sadalika Database Design and Development

E189241 84

Downloaded by Jazaf Ahd ([email protected])