Higher Nationals - Summative Assignment Feedback Form

Student Name/ID [Link] Gamage ID-E207278

Unit Title Unit 04 – Database Design & Development
Assignment Number 1 Assessor [Link] Sivakumar
Date Received 1st
Submission Date submission
2026/01/17 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.
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

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 submitting evidence for assessment, each student must sign a declaration confirming that
the work is their own.
Student name: Assessor name:

Issue date: Submission date: Submitted on:

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: Date:

Unit 04: Database Design & Development
ASSIGNMENT BRIEF
Student 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

Issue Date
Submission Date

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

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.
 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
meaningful & relevant to the given scenario.
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.
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.
Weblinks:
[Link] (2022) Database design basics [online] Available at:
[Link]
8084-bd4f9c9ca1f5 [Accessed 1 August 2022]

[Link] (2022) Database (Data) Testing Tutorial with Sample Test Cases
[online] Available at: [Link] [Accessed 1 August 2022]

[Link] (2022) Database Design in DBMS Tutorial: Learn Data Modeling

[online] Available at: [Link] [Accessed 1 August
2022]

[Link] (2021) Complete Guide to Database Schema Design [online]

Available at: [Link]
designguide/[Accessed 1 August 2022]

[Link] (2022) Database Structure and Design Tutorial [online]

Available at: [Link]
[Accessed 1 August 2022]

[Link] (2022) Database Testing Complete Guide (Why, What,

And How To Test Data) [online] Available at:
[Link] [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. [Link]
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
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:
[Link]

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

[Link]

HN Global (2021) Textbooks. Available at:

[Link]

LEARNING OUTCOMES AND ASSESSMENT CRITERIA

Pass Merit Distinction

LO1 Use an appropriate design tool to design a relational D1 Evaluate the effectiveness of the
database system for a substantial problem design in relation to user and system
 P1 Design a relational
M1 Produce a comprehensive
database system using
design for a fully-functional
appropriate design tools &
system, which includes
techniques, containing at least requirements.
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

on an existing system design

P2 Develop the database M2 Implement

a fully system with evidence of user functional
interface, output and data
database system, 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
database solution in relation to user
to produce
and system requirements and
appropriate management
suggest improvements.
information.

LO3 Test the system against user and system requirements

P4 Test the system against user

M4 Assess the effectiveness of
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 improvements needed to ensure the
fullyfunctional system, continued effectiveness of the
including data flow diagrams system.
and flowcharts, describing how
the system works.

CONTENTS
Plagiarism........................................................................................................................................4
Student Declaration..........................................................................................................................4
Assignment Brief.........................................................................................................................5
Learning Outcomes and Assessment Criteria............................................................................18
Contents.........................................................................................................................................20
Introduction....................................................................................................................................23
Activity 01 – Introduction to Database Management Systems......................................................24
1.1 Introduction of Database Management System...................................................................24
1.2. Non-Functional and System Requirements of E-Space Solution.......................................39
1.4 Exploration of the Entity Relationship (ER) Model............................................................46
1.5 So WHERE to insert the ER diagram and the logical Artefacts?........................................55
1.6. Relationship Types..............................................................................................................56
1.7. Entities and Attributes from the Scenario...........................................................................57
1.8. Relational Schema for the ER Diagram in the scenario.....................................................58
1.9. Dependencies and Anomalies.............................................................................................61
1.10. Normalization...................................................................................................................64
1.11 Evaluation of ER Diagram and Logical Design Effectiveness..........................................66
Activity 02.....................................................................................................................................66
2.1 Tools for Development.........................................................................................................66
2.2. Uses of queries n SQL Server Management Studio (SSMS).............................................71
2.3 Assessment of Meaningful Data Extraction Using Queries.................................................75
2.4. Indexing and Performance Consider...................................................................................78
2.5. Security and Roles.............................................................................................................78
2.6 Database Security and Maintenance Evaluation..................................................................79
2.7 Data Validation and Business Rules....................................................................................82
2.8 UI Integration and Evidence of Implementation.................................................................82
2.9 Database ARTIFACT’S Location in the report....................................................................83
Activity 03.....................................................................................................................................83
3.1 Test Plan Table.....................................................................................................................83
3.3 Evaluation of Testing Effectiveness and Test Data Selection..............................................84
 3.4 Conclusion of Testing..........................................................................................................85
Activity 04.....................................................................................................................................85
4.1. User Manual for Mars Colonization System......................................................................85
4.2 Technical Documentation Overview (DFDs and Flowcharts).......................................90
4.2.0. Diagrams.........................................................................................................................90
4.3 STRENGTHS & WEAKNESSES.......................................................................................97
4.4 RECOMMENDations: MAINTENance..............................................................................98
[Link]...........................................................................................................................99
5.1 Future Improvements and Enhancements....................................................................100

Figure 1 PostgreSQL......................................................................................................................26
Figure 2 Oracle..............................................................................................................................26
Figure 3 MongoDB........................................................................................................................26
Figure 4 Cassandra.........................................................................................................................27
Figure 5 ObjectDB.........................................................................................................................27
Figure 6 One-Tier Architecture......................................................................................................44
Figure 7 Two-Tier Architecture....................................................................................................44
Figure 8 Three-Tier Architecture...................................................................................................45
Figure 9 CARDINALITY NOTATIONS.......................................................................................51
Figure 10 ER DIAGRAM..............................................................................................................56
Figure 11 ER Diagram for the Scenario.........................................................................................59
Figure 12 Insertion Anomaly.........................................................................................................63
Figure 13 Deletion Anomaly..........................................................................................................64
Figure 14 Update Anomaly............................................................................................................64
Figure 15 SQL Server....................................................................................................................68
Figure 16 SQL Server management Studio...................................................................................69
Figure 17 Server Management Studio Object Explorer.................................................................70
Figure 18 Number of Colonists Assigned to Each Job..................................................................74
Figure 19 Colonist Details with Assigned Jobs............................................................................75
Figure 20 Trip Details with Assigned Jet Information...................................................................76
Figure 21 Display of All Colonist Records Using SELECT Query...............................................77
Figure 22 Filtering Colonists Using WHERE Clause....................................................................77
Figure 23 Colonist Details with Assigned Jobs Using JOIN Query..............................................78
Figure 24 Number of Colonists Assigned to Each Job..................................................................78
Figure 25 Sorted Colonist Records Using ORDER BY Clause.....................................................79
Figure 26 SQL Server Database.....................................................................................................81
Figure 27 Creation of Database User Mapped to SQL Server Login...........................................81
Figure 28........................................................................................................................................82
Figure 29........................................................................................................................................82
Figure 30........................................................................................................................................82
Figure 31........................................................................................................................................83
Figure 32........................................................................................................................................83
Figure 33 Use Case Diagram.........................................................................................................93
Figure 34 Flow Chart.....................................................................................................................95
Figure 35 Housing Assignment Flowchart....................................................................................96
Figure 36 Job Assignment Flowchart............................................................................................97

INTRODUCTION
Space Solutions database system aims to store fundamental operational information of a
proposed project of settling Mars. The main objective of the system is to offer a dependable and
uniform and expandable storage of information relating to colonists, their dependents,
accommodation, employment, aircraft (E-Jets), pilots, and planned trips. In this regard the
database serves as the authoritative body that assists the staff functions like personnel
management, scheduling trips, assignment and reporting of housing.

This report captures the design and implementation of Espace Solutions starting with the
requirements elicitation process up to logical modelling and physical implementation. Data
integrity and scalability have been made the main objectives in the system: referential integrity is
ensured through foreign keys, many-to-many relationships are implemented through junction
tables, domain constraints (ENUMs / CHECKs) take care of invalid data. The relational data
structure is indicated by the need to have structured queries and transactional consistency which
are typical of the operational systems.

The database supports CRUD operations on the colonists, dependents, jets, pilots, trips, houses
and jobs as well as supports complex queries to aid in reporting (e.g. colonists per job,
passengers per trip). Such non-functional requirements as usability (clear table structure and keys
to create a simple UI), maintainability (normalized schema to prevent redundancy), and simple
security (separation of roles and least privilege principle) are considered. Activity 1
(requirements and logical design), activity 2 (physical design and implementation) and other
testing and maintenance material, are presented below and attached to this report.
ACTIVITY 01 – INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS
1.1 INTRODUCTION OF DATABASE MANAGEMENT SYSTEM
A Database Management System (DBMS) is a software system designed to efficiently store,
manage, organize, and retrieve large volumes of data in a structured manner. It acts as an
interface between users and databases, enabling reliable data handling while ensuring
consistency, security, and accessibility. In modern computing environments, DBMS solutions are
fundamental to information systems, supporting data-driven decision-making and improving
operational efficiency across organizations ranging from small businesses to large enterprises.

1.1.1 What is a DBMS?

Database Management System enables users to create, modify, remove, and query databases, and
ensures integrity of data and protection of data. A DBMS will consist of concurrency control,
transaction management, backup and recovery; unlike the traditional file-based systems, it will
reduce data redundancy, minimize inconsistency and ease data management.

DBMS structures data in structured elements which include tables, schemas, views, and reports
and offers a systematic and scalable method of handling information.

Key Features of a DBMS

 Data Modeling: It is a tool used in defining data structure and relationships within the
database.
 Storage and Retrieval: storage is efficient and fast query execution.
 Concurrency Control: Multiple users can access this without any conflict.
 Data Integrity and Security: Imposes access rules and controls to maintain the accuracy
and confidentiality of data.
 Backup and Recovery: Provides data safety and recovery of data in the event of system
failures.
1.1.2 Types of DBMS

Database Management Systems have been divided into types depending on data structure,
scalability and application requirements. The main types include:

1. Hierarchical DBMS

A hierarchical database model entails the arrangement of data in the form of a tree.
Parentrecords may have many child records, however, children have single parent records.

Characteristics:

 One-to-many relationships
 Parent–child structure
 One can automatically delete the children of a parent.
 Browses using pointers to other records.

2. Relational Data Base Management System (RDBMS)

RDBMS is a data store whereby it stores data in table format comprising of rows and
columns. Tables have primary keys and there are foreign keys that are used to establish
relations among the tables. SQL (Structured Query Language) is applied to manipulate and
control data.

FIGURE 1 POSTGRESQL
 FIGURE 2 ORACLE

3. NoSQL DBMS

NoSQL databases are configured to be highly scaled and performant, particularly when
large amounts of unstructured or semi-structured data are required. They are based on
flexible data models, including key-value, document-based, column-oriented, or graph
data structures.

FIGURE 3 MONGODB

FIGURE 4 CASSANDRA
 4. Object-Oriented DBMS (OODBMS)

OODBMS incorporates object-oriented programming principles where data can be

represented using objects. It provides the opportunity to store complicated data and
inheritance, which is why it can be used in complex applications and simulations (Geeks,
2025).

FIGURE 5 OBJECTDB

5. Entity- Relationship (ER) Data Model.

The ER model is used to represent data by the entities, attributes, and relationships. It
gives a conceptual perspective on the database and therefore it is easier to understand the
system requirements by the stakeholders.

The ER diagrams provide the logical framework of a database in a visual manner and act
as a model to transform conceptual designs into relational database designs.

1.1.3 What is Data?

Data is raw materials or raw facts, be it numbers and symbols, textual information and images.
These elements, when gathered and presented absent interpretation, are just data points, discrete
and disordered things having no natural sense or meaning.

Whenever these pieces are analyzed and put into context, however, they provide actionable
insights and knowledge. Such refinement and interpretation uncover the true value of data and
makes it possible to make informed decisions.
In our Mars colonization project data consists of:

• Colonist Data: Name, ages, qualifications, health history, and family history.
• Spacecraft Data: model numbers, capacities, fuel level, maintenance history.
• Trip Data: Dates, times, durations, distances and passenger lists.
• Job Data: Job titles, description, necessary qualification, colonist assigned.

Housing Data such as House IDs, capacities, locations, and assigned colonists.

1.1.4. What Is Information?

Information is received when data is processed, organized, interpreted and structured. The
understandable product of raw data assists in making decisions, strategies and actions.

Data that becomes valuable and accessible, a part and parcel of the decision-making process is
information.

To give an example, when data points contain the temperature readings of the day during a year,
the information is making sense of the trend of the temperatures, getting to know about the
changes in seasons and anticipating weather patterns.

To transform data to information, the following steps are required:

 Processing: This is the cleaning and preparation of data to be analyzed. It may involve
elimination of duplicates; correction of mistakes and data being converted to a standard
format.
 Organizing: The data is organized and divided into categories that are easy to
comprehend and retrieve. This may include the establishment of databases, taxonomies or
ontologies.
 Interpreting This stage translates data into actionable insights by discovering patterns,
trends, and connections. This can be done through the process of statistical analysis, good
data visualization and professional knowledge to come up with meaningful conclusions.
 structuring: it is presented concisely and has accessibility. This might include the
production of reports, dashboards, articles of knowledge, or other formats that are easily
understood and can be used to make decisions. (Bloom fire, 2025)
1.1.5 Components of a DBMS

Database Management System is a compilation of various integrated components that

individually guarantee effective data storage, retrieval, security, and management. All the
components play a designated role, and the correct coordination of these components helps the
DBMS to become a data management system that can be trusted.

1. Hardware

Hardware can be defined as the physical devices on which the database system is run. This
consists of computers, servers, storage equipment, and networking equipment. The storage
of databases is generally secondary storage like hard disk drive (HDD) or solid-state drive
(SSD) or even on cloud storage systems. Database speed, scalability, and availability
depend solely on the performance and capacity of the hardware. DBMS can be either a
personal computer or a high-end enterprise server and distributed systems.

2. Software

Software is the main element which serves as a bridge between the users and the physical
database. The DBMS software handles all data access, manipulation, as well as
administration activities. It understands user queries, imposes constraints, administers a
transaction, and ensures data integrity. MySQL, Oracle Database, PostgreSQL and
Microsoft SQL Server are examples of DBMS software.

3. Data

The most important element of a DBMS is data. It is the unprocessed facts contained in the
database e.g. records, tables and relations. This data needs to be stored, retrieved, updated
and deleted efficiently and this is the role of the DBMS. Well-structured data means a
minimum of redundancy, better accuracy, and utmost retrieval of information.

4. Users

Users are those individuals or applications that interrelate with DBMS. They could be
divided into various types:

• Database Administrators (DBA): operate the database system, regulate access,

carry out backups and maintain security.
• Application Developers: Design and create applications to connect to the
database.
 • End Users: find or use the data in applications or query interfaces to report and
perform operational activities.

5. Procedures

Rules and instructions describing the way to use and maintain the database system are
recorded and are called procedures. They contain data entry guidelines, backup policies,
security practice, recovery measures, and performance tuning. This is the so-called
procedure guaranteeing consistent and secure usage of the DBMS throughout the
organization.

6. Database Access Language

DBMS is communicated with Database Access Languages. The most popular language is
SQL (Structured Query Language) enabling users to define, manipulate, and retrieve the
data. SQL has Data Definition Language (DDL), Data Manipulation Language (DML) and
Data Control Language (DCL) that allow full interaction with the database.

7. Query Processor

The query processor deciphers and handles user queries. It translates high-level SQL
statements into low-level codes that the database engine can execute effectively. This part
also manages queries in the best way possible, thus enhancing performance.

8. Runtime Database Manager

This component also gets referred to as the database control manager and it is what controls
the database operations in a runtime operating scenario. It manages concurrency,
transaction and authorization of access. It can guarantee data consistency and integrity in a
multi-user environment by implementing locking mechanisms and rules of transactions.

9. Data Manager

The storage or cache manager is also known as the data manager, and it deals with data
storage and memory buffers. It manages writing as well as reading data to disk and efficient
utilization of the memory. Also, it facilitates recovery processes to recover the database in
case of failure or crashes.
 10. Database Engine

The key service of a DBMS is the database engine. It has the duty of storing, letting out
and processing data and ensuring security and integrity of transactions. The engine supports
ACID (Atomicity, Consistency, Isolation, Durability) properties of database operations and
thus can be used in both a transactional and analytical application.

11. Data Dictionary

Data Dictionary is a central repository which is data containing metadata about the
database. It holds data on tables, columns, data types, constraints, indexes, users and
privileges. The data dictionary will make the data consistent, will aid in query validation
and will enable the administrators to comprehend and work with the database
infrastructure.

12. Writer

The utility is the report writer, which gathers the information in the database and represents
it in an easy-to-use format. It allows one to create reports, summaries and graphic
representations like charts and tables. This element comes in handy especially in making
decisions, analysis and management reporting.

DBMS Concepts as applied to the Project.

• Naming: Raw data concerning colonists, spacecrafts, trips, jobs, and housing will be
collected.
• Data Storing: The collected data are stored in form of tables within a database.
• Data Processing: Processing similar data to produce useful outputs, including trip
passenger lists.
• Information Management: Updating and keeping proper records.
• Decision Support: Supporting the mission planning, job allocation and housing
management with the help of stored information.

1.1.6 Operating System File Systems.

Computer files refer to a group of connected information stored digitally in one of the secondary
storage devices. The operating systems utilize files as units of permanently storing and handling
information. A file system offers order and policies that regulate the creation, storage, access,
modification and deletion of files in a computer system.

Any operating system has file systems as its critical component. They provide a connection
between the operating system and physical storage, e.g. hard disk drives (HDDs), solid state
drives (SSDs) and removable storage devices. File systems help users and applications to store
data efficiently in an orderly manner and retrieve when needed by organizing data effectively.

1.1.7. What is a File System?

A file system refers to the mechanism that an operating system deploys to arrange files and
directories of a storage medium. It determines the method of storage, naming, access and
protection of data. Operating systems have different types of file systems that are supported by
different operating systems and have their advantages.

Common file systems are:

• FAT (File Allocation Table): An outdated file system which is utilized in early windows
and in portable drives.
• NTFS (New Technology File System): This is a recent Windows file system supported by
permissions, encryption, compression, and huge file size.
• ext (Extended File System): This is utilized in Linux and Unix-based operating
systems.
• HFS (Hierarchical File System): A MacOS file system that was older.
• V. APFS (Apple File System): A new Apple file system that is optimized to use SSD and
improved security.

1.1.7 Features of the File System

File systems possess several significant features that allow managing data efficiently:

1. Space Management
File systems are allocating storage space in fixed sized blocks or clusters. Slack space
is the unused space that is formed when the file is not completely taking the spaces it
was allocated. When the file system is created, the size of the allocation units is
determined, and the appropriate unit size is chosen to minimize wasted storage.
2. Leviathan Data Management.
The files are arranged into directories and subdirectories to create either flat or
hierarchies. Hierarchical structure enhances the organization of files, navigation and
access control where large storage systems are involved.
 3. Metadata Management
Along with file content, file systems contain metadata information like file name,
size, date of creation, location and permissions. Such metadata is placed in directory
tables or special structures (in Unix-based systems, inodes).

4. Integrity Management
File systems provide consistency in data stored even in case of termination of
theprograms or failure of the system. They also update the metadata, directory, and
the data stored in the cache to avoid corruption.

1.1.8 File System vs. DBMS Difference.

A File System and a Database Management System (DBMS) both store data but they do not
operate equally in terms of functionality, efficiency, and complexity.

A file system: It is a structure of organizing data into files and folders and performing simple
tasks like reading, writing, and erasing data. It can be used in basic applications where
information relationships and querying intricacy are not obligatory.

A DBMS is a special software system created to handle volumes of structured information in

large quantities. It supports advanced functions including indexing, transactions, concurrency
control, security implementation as well as data integrity constraints.

Comparison between File System and DBMS

Feature File System DBMS

Structure Organizes files in folders Manages structured databases

Data Redundancy High redundancy possible Redundancy minimized

Backup & Recovery No built-in support In-built backup and recovery

Query Processing Limited Efficient query processing

Consistency Low High (normalization)

Complexity Simple More complex

 Security Limited Advanced security mechanisms

Cost Low Higher

Data Independence Not supported Logical & Physical independence

User Access Single user Multiple concurrent users

Data Sharing Difficult Easy

Data Abstraction Exposes storage details Hides internal details

Integrity Difficult Easy to implement

Constraints

Example COBOL file handling Oracle, SQL Server

1.1.9 E- Space Solutions Introduction.

E-Space Solutions (Pvt) Ltd. is a progressive company that is going to facilitate the mission of
mankind to colonize Mars by the year 2040. The business needs a scalable and trusted Database
Management System to coordinate the complicated logistics of such a huge undertaking.

This assignment aims at creating a database that can be used to maintain the records of colonists,
spacecraft logistics, trip scheduling, housing placements, and job assignments. The database will
maintain data accuracy, consistency, security and accessibility hence enable operations efficiency
and informed decision making throughout the project lifecycle.

DBMS Concepts to Our Project Before Fully Implementing

1. Data Collection
The first step is to collect raw data from various sources. This data includes personal details of
colonists, specifications of spacecraft, schedules of trips, job descriptions, and housing details.

2. Data Storage
Using a DBMS, you store this raw data in a structured format within a database. The database is
designed to handle large volumes of data and to ensure that data is stored efficiently and
securely.

3. Data Processing
The raw data is processed to generate meaningful information. For example, by combining
colonist data with trip data, you can generate a passenger list for a specific trip. By analyzing
qualifications and job descriptions, you can assign jobs to colonists.

4. Information Management
The DBMS allows you to manage and update information. For example, if a colonist’s
qualifications change, you can update their record in the database. If a trip is rescheduled, you
can update the trip information.

5. Decision Support
The information stored in the database supports decision-making processes. For example,
mission planners can use the database to schedule trips, assign jobs and allocate housing. The
database ensures that all decisions are based on accurate and up-to-date information.

1.2. What are Requirements

A comprehensive set of requirements is crucial for any software project.

Requirements identify the product's business needs and purposes at a high level. They also
clarify the features, functionality, behaviors, and performance that stakeholders expect.

Software requirements are a way to identify and clarify the why, what and how of a business's
application. When researched and documented properly, software requirements form a roadmap
that leads a development team to build the right product quickly with minimal costly rework.

The actual types of software requirements and documents an IT organization produces for a
given project depend on the audience and the maturity of the project. In fact, organizations often
draft several requirements documents, each to suit the specific needs of business leaders, project
managers and application developers.
Let's start with a look at the main types of requirements in software engineering: business
requirements, user requirements, and software requirements. Then, explore common types of
software requirements documentation as well as tried-and-true characteristics to define
requirements.

1. User requirements

User requirements reflect the specific needs or expectations of the software's customers.
Organizations sometimes incorporate these requirements into a BRD, but an application that
poses extensive user functionality or complex UI issues might justify a separate document
specific to the needs of the intended user. User requirements, much like user stories, highlight
the ways in which customers interact with software.

There is no universally accepted standard for user requirements statements, but this is one
common format: "The [user type] shall [interact with the software] in order to [meet a business
goal or achieve a result]."

An example of user requirement in that mold for the industrial laser marking software looks like,
"The production floor manager shall be able to upload new marking files as needed in order to
maintain a current and complete library of laser marking images for production use."

There might be many user requirements for any software project, each reflecting an expectation,
goal or user story. In most cases, user requirements are high-level goals that reflect what the
software should be able to do. They typically avoid any technical details related to how they
accomplish the goals. User requirements frequently form the foundation for specific software
requirements.

2. System Requirements

These specify the technical needs to build and operate the system. They plan what the system
should be capable of doing from a technical perspective and what hardware and software
resources are required. System requirements are further divided into hardware and software
specifications.
 3. Functional requirements

Functional requirements are statements or goals that teams use to define system behavior.
Functional requirements define what a software system must or must not do. They are typically
expressed as responses interactions or outputs generated in response to inputs or conditions.
Common functional requirements include data input, data access, user authentication, alerting
and reporting, online payments, and integrations with other software.

A functional requirement can express an if/then relationship, as in the following example: "If an
alarm is received from a sensor, the system will report the alarm and halt until the alarm is
acknowledged and cleared."

Functional requirements might detail specific types of data inputs such as names, addresses,
dimensions and distances. These requirements often include an array of calculations vital to the
software working correctly.

Functional requirements are relatively straightforward to test because they define how the
system behaves. A test fails when the system does not function as expected.

4. Non-Functional Requirement

Nonfunctional requirements relate to software usability. Nonfunctional software requirements

define how the system must operate or perform. A system can meet its functional requirements
and fail to meet its nonfunctional requirements.

NFRs define the software's characteristics and expected user experience (UX). They cover the
following:

• Performance - These measures show how quickly the software should respond or render
an output.
 • Usability - The level of difficulty that users experience in understanding or using a UI or
software system.
• Scalability - The software's ability to handle more simultaneous users or transactions.
• Security - The ways in which the software safeguards processing and data against loss or
theft.
• Maintainability - The ability of the software to be patched or upgraded over time.
• Compatibility - Compatibility involves the hardware, OS and other platform
requirements for the software.
• Portability - The ability of the software to operate on different systems or platforms.

An example nonfunctional requirement related to performance and UX could be, "The pages of
this web portal must load within 0.5 seconds."

Domain requirements. Domain requirements are expectations related to a particular type of

software, purpose, or industry vertically. Domain requirements can be functional or
nonfunctional. The common factor for domain requirements is that they meet established
standards or widely accepted feature sets for that category of software project.

Domain requirements can vary wildly but typically arise in military, medical and financial
industry sectors. An example of a domain requirement for software in medical equipment is,
"The software must be developed in accordance with IEC 60601 regarding the basic safety and
performance for medical electrical equipment."

Another example of a domain requirement for the financial industry is, "The software should
adhere to current Generally Accepted Accounting Principles standards for financial accounting
and reporting."

Software can be functional and usable but not acceptable for production because it fails to meet
domain requirements. (Target, 2024)
1.2. NON-FUNCTIONAL AND SYSTEM REQUIREMENTS OF E-SPACE SOLUTION

Functional Requirements:

• Colonist Registration - The system should allow users to enter personal information
of colonists including name, id, date of birth, age, address, gender, contact information,
civil status and family details (dependents).

• Dependent Details - The system should allow operators to input the details of
dependents such as their name, date of birth, gender, relationship to the colonist and link
them to colonist.

• Jet Registration - System must store details of the E-jets, including jet code, number
of passenger seats, engine type, nuclear engine power, weight, power source and year
made.

• Trip Scheduling - System should support the scheduling of trips to Mars and
allowing operators to assign colonists and their families to specific trips while linking
them to the corresponding E-jet.

• Housing Assignment - The system should allocate housing for colonists and their
families once they arrive on Mars and ensure that either a family gets one house or
multiple colonists share a house based on predefined criteria.

• Job Assignment - System should allow colonists to be assigned to jobs that support
Mars colonization, including construct, power generation, administration, education,
 medical, research etc. colonists can hold multiple jobs, and one job can be assigned to
multiple colonists.

• E-jet Pilot Assignment - The system should assign pilots to specific E-jets based on
their qualification and rank.
Non-Functional Requirements:

• Performance - System should be capable of handling large number of colonists,

family members, E-jet details, and trip records without affecting performance. It should
be able to manage at least 10,000 records simultaneously.

• Security - The system must ensure that sensitive data such as colonist personal
information and trip details is protected using encryption and access control.

• Usability - The system should have an in-built interface that is easy to navigate,
especially for data operators, pilots, and administrators.

• Scalability - Must be able to expand as the number of colonists, E-jets, and trips
increase over time.

• Reliability - System should have high up time to ensure continuous access and
prevent data loss during critical periods.

System Requirements:
Hardware Requirements:

• Processor - multi-core (at least 16-core) processors for handling large datasets. •
Memory - Minimum 64 GB of RAM for fast data processing and handling of
simultaneous operations.
• Storage - At least 1 TB SSD for fast read/write operations, and additional 10 TB for
data storage related to colonists, E-Jet trips, and housing.
 • Network Connectivity - High-speed internet with failover redundancy to ensure
connectivity during operations.
• Backup System - Daily automated backup system to ensure data integrity and
redundancy.

Software Requirements:
These define the software platforms needed for the system to function correctly.
Operating Systems:
• Servers: Linux-based servers for hosting the database and application server.
• Workstations: Windows 10/11 or Linux for data entry operator systems
Database Management System (DBMS):
• DBMS: MySQL, PostgreSQL, or Oracle for handling large amounts of data and
ensuring referential integrity between colonists, E-Jets, trips, jobs, and housing.

By ensuring that these requirements are met the system will be able to efficiently manage
colonist data, facilitate Mars colonization trips, and support the mission of E-Space
Solutions.

1.2.1 User Requirements

User requirements describe what different users expect from the E-Space Solutions database
system.
These requirements ensure that the system supports all operational and managerial activities
of the Mars colonization project.

User Type User Requirement

System Add, update and delete all records related to colonists, jets,
Administrator pilots, trips, houses and jobs
 System Manage user roles, access permissions and database security
Administrator

Data Entry Operator Insert and update colonist, dependent and trip details accurately

Data Entry Operator Assign colonists to trips and jobs using the system

Colony View house allocation details and colonists assigned to each

Superintendent house

Colony View job assignments and generate colony-related information

Superintendent

Pilot View assigned jet details and trip schedules

Pilot View passenger details related to assigned trips

1.2.2 Functional Requirements

• Add, update and delete colonist records

• Assign colonists to trips and jobs
• Store jet, pilot and trip information
• Maintain house allocation details

1.2.3 Non-Functional Requirements

• Data integrity using primary and foreign keys

• Security using user roles and permissions
• High reliability and data consistency
• Scalability for future expansion

1.3 Database Architecture?

Database architecture can be defined as the structured design of a database system that defines
the way data is stored, organized, accessed, as well as managed in a Database Management
System (DBMS). It is the foundation of any database-based application and has a direct effect on
system performance, scalability, security and maintainability. The database architecture should
be well designed to provide efficient and reliable data operations like data storage, data retrieval,
updating, and data control.

Practically, database architecture is a description of the integration of DBMS with application

components and users. The decisions taken in design at this stage impact on the way the
applications will be developed, the way data will travel between the components of the system as
well as the way future improvement can be accommodated. So, there is no single database
architecture that can be said to fit all types of organizations, data volumes, number of people
using the system at the same time, and complexity of the system.

Database Deployment types.

1) One-Tier Architecture
In one-tier architecture, the user interface, application logic and the database are all
on the same system. The model is not complicated and is relatively inexpensive;
therefore, it can be used in small applications or standalone projects. There is no
network communication between layers hence fast data access. But it is not scalable
and would not fit well in multi-user enterprise.

FIGURE 6 ONE-TIER ARCHITECTURE

2) Two-Tier Architecture
Two-tier architecture is based on client-server architecture; clients are numerous
applications that interact directly with a database server. It was extremely popular as
an architecture in early desktop-based systems, e.g. in-house administration
applications to a centralized database. Although it is better-separated compared to
one-tier architecture, it presents a security and scalability drawback due to direct
access to the database by the clients.
 FIGURE 7 TWO-TIER ARCHITECTURE

3) Three-Tier Architecture
Most modern web applications use three-tier architecture. In this architecture, the
clients connect to a back end, which in turn connects to the database. Using this
approach has many benefits:

• Security: Keeping the database connection open to a single back end reduces the
risks of being hacked.
• Scalability: Because each layer operates independently, it is easier to scale parts
of the application.
• Faster deployment: Having multiple tiers makes it easier to have a separation of
concerns and to follow cloud-native best practices, including better continuous
delivery processes.
 FIGURE 8 THREE-TIER ARCHITECTURE

Along with tier-based architecture, this system is also compliant with ANSI/SPARC architecture
of three levels of data abstraction known as the three level architecture:

• External Schema (View Level): It is the way the data is perceived by various groups of
users. A personalized view can be generated to each user or role to conceal unwanted
information.

• Conceptual Schema (Logical Level): describes the overall logical structure of the
database, comprising of entities, attributes, as well as relationships, without reference to
physical storage.

• Internal Schema (Physical Level): This is the way data is physically stored in the storage
medium such as files, indexes and the access paths.
Advantages and Limitations
Advantages Disadvantages
One of the main advantages of DBMS’s three This method can be difficult and expensive for
schemas is its data independence. All three layers big companies because it takes a lot of work to set
are distinct from each other. So, we can make up and maintain.
changes to one layer without affecting other
layers.

Each schema can scale independently, which can It can also cause slow-downs and mistakes if the
enhance the performance of the database and data is not converted correctly between the
manage more traffic at the same time. different parts.
It is simpler to maintain and change each layer Sometimes, it can also be hard to make sure only
individually in a three-schema design due to the the right people can access sensitive information.
separation of the layers.

1.4 EXPLORATION OF THE ENTITY RELATIONSHIP (ER) MODEL.

1.4. Introduction of ER Model

The Entity Relationship Model is a model for identifying entities (like student, car or company)
to be represented in the database and representation of how those entities are related. The ER
data model specifies enterprise schema that represents the overall logical structure of a database
graphically.

We typically follow the steps below for designing a database for an application.

• Gather the requirements (functional and data) by asking questions to the database
users.
• Create a logical or conceptual design of the database. This is where ER model
plays a role. It is the most used graphical representation of the conceptual design of a
database.
• After this, focus on Physical Database Design (like indexing) and external design
(like views)

Why Use ER Diagrams In DBMS

• ER diagrams represent the E-R model in a database, making them easy to convert
into relations (tables).
• ER diagrams serve the purpose of real-world modeling of objects which makes
them intently useful.
• ER diagrams require no technical knowledge of the underlying DBMS used.
• It gives a standard solution for visualizing the data logically.

Symbols Used in ER Model

ER Model is used to model the logical view of the system from a data perspective which consists
of these symbols:

• Rectangles: Rectangles represent entities in the ER Model.

• Ellipses: Ellipses represent attributes in the ER Model.
• Diamond: Diamonds represent relationships among Entities.
• Lines: Lines represent attributes to entities and entities sets with other
relationship types.
• Double Ellipse: Double ellipses represent multi-valued Attributes.
• Double Rectangle: Double rectangle represents a weak entity.

Components of ER Diagram
ER Model consists of Entities, Attributes, and Relationships among Entities in a Database
System

1.4.1. What is an Entity

An Entity may be an object with physical existence: a particular person, car, house, or employee
or it may be an object with a conceptual existence a company, a job, or a university course.

What is an Entity Set

An entity refers to an individual object of an entity type, and the collection of all entities of a
particular type is called an entity set. For example, E1 is an entity that belongs to the entity type
“Student,” and the group of all students forms the entity set.

Types of Entity
1. Strong Entity
A Strong Entity is a type of entity that has a key Attribute. Strong Entity does not depend on
other Entity in the Schema. It has a primary key, that helps in identifying it uniquely, and it is
represented by a retangle. These are called Strong Entity Types.
2. Weak Entity
An Entity type has a key attribute that uniquely identifies each entity in the entity set. But some
entity type exists for which key attributes can’t be defined. These are called Weak Entity Types.

For Example, A company may store the information of dependents (Parents, Children, Spouse)
of an
Employee. But the dependents can’t exist without the employee. So dependent will be a Weak
Entity
Type and Employee will be identifying entity type for dependent, which means it is Strong
Entity Type.

A weak entity type is represented by a double rectangle. The participation of weak entity types is
always total. The relationship between the weak entity types and its identifying strong entity
type is called identifying relationship and it is represented by a double diamond.

1.4.2. What are Attributes

Attributes are the properties that define the entity type. For example, Rollin, Name, DOB, Age,
Address, and Mobile No are the attributes that define entity type Student. In ER diagram, the
attribute is represented by an oval.

Types of Attributes
1. Key Attribute
The attribute which uniquely identifies each entity in the entity set is called the key attribute.
For example, Rollin will be unique for each student. In ER diagram, the key attribute is
represented by an oval with underlying lines.

2. Composite Attribute
An attribute composed of many other attributes is called a composite attribute. For example,
the Address attribute of the student Entity type consists of Street, City, State, and Country. In ER
diagram, the composite attribute is represented by an oval comprising of ovals.
 3. Multivalued Attribute

An attribute consisting of more than one value for a given entity. For example, Phone_No (can
be more than one for a given student). In ER diagram, a multivalued attribute is represented by a
double oval.

4. Derived Attribute
An attribute that can be derived from other attributes of the entity type is known as a derived
attribute.
e.g., Age (can be derived from DOB). In ER diagram, the derived attribute is represented by a
dashed oval.

1.4.3. What is Cardinality

The maximum number of times an entity of an entity set participates in a relationship set is
known as cardinality. Cardinality can be of different types:

1. One-to-One: When each entity in each entity set can take part only once in the
relationship, the cardinality is one-to-one. Let us assume that a male can marry one female and a
female can marry one male. So, the relationship will be one-to-one.

2. One-to-Many: In one-to-many mapping as well where each entity can be related to more
than one entity. Let us assume that one surgeon department can accommodate many doctors. So,
the Cardinality will be 1 to M. It means one department has many Doctors.

3. Many-to-One: When entities in one entity set can take part only once in the relationship
set and entities in other entity sets can take part more than once in the relationship set,
cardinality is many to one. Let us assume that a student can take only one course, but one course
can be taken by many students. So, the cardinality will be n to 1. It means that for one course
there can be no students but for one student, there will be only one course.
4. Many-to-Many: When entities in all entity sets can take part more than once in the
relationship cardinality is many to many. Let us assume that a student can take more than one
course and one course can be taken by many students. So, the relationship will be many to many.
(Geeks, 2025) Cardinality Notations

• New Notations (Kaushik Style) – These follow 1, n, m structure only and are in fashion of
being used. • Old Notations (Bachman Style) – These follow pictorial representation of
relationship without using 1, n, m. You can find examples below; however, these are unpopular
these days.
 FIGURE 9 CARDINALITY NOTATIONS

Cardinality views: Cardinality can be shown as look-across or same-side, depending on where

the symbols are shown. Cardinality constraints: The minimum or maximum numbers that apply
to a relationship.

Conceptual, logical, and physical data models

ER models and data models are typically drawn at up to three levels of detail:

Conceptual data model

The conceptual data model can be considered the “whiteboard” data model. It does not address
the

For this model, it’s important to focus on capturing all the types of data (or “entities”) that the
system will need. In addition to entities, a conceptual data model will also capture:

• Attributes: Individual properties of an entity. For instance, a “person” entity may

have “name” and “shoe size.” An “address” entity may have “zip code” and “city.”

• Relationships: How an entity connects to other entities. For instance, a “person”

entity may have one or more “addresses.”

Along with the entities, their attributes, and relationships, a conceptual model can also:

• Organize scope: Details which entities are included, and which are not included.
• Define business rules: For instance, are person entities allowed to have multiple
addresses?
What about multiple emails? Do they need to have a unique identifier?

Architects often create conceptual data models with business stakeholders and domain experts.

Logical data model

A logical data model is decided upon after stakeholders agree on a conceptual model.

This stage involves filling in the details of the conceptual model. It’s still too early to pick a
database management system (DBMS), but this stage can help you decide which database to use
(relational, document, etc.). For instance, if you choose relational, you’ll need to decide which
tables to create. If you choose documents, you’ll need to define the collections.
During this step, you should also decide the details of each field or column and relationship.
These details include data types, sizes, lengths, arrays, nested objects, etc.

Architects and business analysts typically create the logical model.

Physical data model

Once you’ve defined a logical model, it’s time to implement it into a database.

If you decide on a relational model, some options you can choose from include Microsoft SQL
Server, Oracle, PostgreSQL, or MySQL. However, if your modeling process reveals that your
data model will likely change frequently to adapt to new requirements, you should consider
using a document database. Couchbase, a NoSQL document database, supports relational
concepts like JOINs, ACID transactions, and flexible JSON data.

The physical data model should include:

• A specific DBMS
• Specifications for storing data (e.g., on disk, RAM, or hybrid)
• Instructions for accommodating replications, shards, partitions, etc.

Database administrators (DBAs) and developers typically create the physical data model.
(Couchbase, 2022)
Limitations of ER diagrams and models

• Only for relational data: Understand that the purpose is to show relationships. ER
diagrams show only that relational structure.
• Not for unstructured data: Unless the data is cleanly delineated into different fields,
rows or columns, ER diagrams are probably of limited use. The same is true of semi-
structured data, because only some of the data will be useful.
• Difficulty integrating with an existing database: Using ER Models to integrate with an
existing database can be a challenge because of the different architectures.
How to draw a basic ER diagram

• Purpose and scope: Define the purpose and scope of what you’re analyzing or
modeling.
• Entities: Identify the entities that are involved. When you’re ready, start drawing them in
rectangles (or your system’s choice of shape) and labeling them as nouns.
• Relationships: Determine how the entities are all related. Draw lines between them to
signify relationships and label them. Some entities may not be related, and that’s fine. In
different notation systems, the relationship could be labeled in a diamond, another rectangle
or directly on top of the connecting line.
• Attributes: Layer in more detail by adding key attributes of entities. Attributes are often
shown as ovals.
• Cardinality: Show whether the relationship is 1-1, 1-many or many-to-many.

Relational Schema

A relational schema is a data model for logically representing the structure of a database.
Relational schema is a type of database schema specifically designed to represent relational
databases.

Relational schemas can have a name and consist of the three main components below:

• Relations: In a relational schema, they are called tables.

• Attributes: Each attribute has a domain that specifies the type of data or limits the
value an attribute can take. Each table can have many attributes. In a relational schema,
they are the columns of a table.
• Tuples: They can be seen as instances of an entity. Each table can have several
instances. In a relational schema, they are the rows contained in a table.
A relational database definition is a collection of named relations, each with its attributes and
tuples.
In a relational schema, an attribute can also be a key:

• A primary key represents the identifying attribute of each instance of a table. It

cannot be NULL or accept duplicate values.
• A foreign key links two tables in a relationship. It contains a value that references
the primary key of another table. It can be a single attribute or a set of attributes.

You now know what the ER model is, and what ER diagrams and relational database schemas
are.
Let’s try to understand the difference between the three concepts.

1.4.4. ER Models and Relational Schemas

A relational schema (or model) resembles an actual database model of the information stored in
the database. The terminology is a critical difference between an ER model and a relational
schema. For example, an ER model deals with entities and their relationship, whereas a
relational schema talks about tuples and attributes.

Moreover, an ER model may be easier to understand than a relational schema because we map
the cardinalities explicitly (one-to-one, many-to-one, etc.). In contrast, the relationship between
two tables in a relational schema is more subtle. You must figure out the foreign and primary
keys and their overall connection to really understand a schema. (Dzone, 2022)

1.5 SO WHERE TO INSERT THE ER DIAGRAM AND THE LOGICAL ARTEFACTS?

Insert the image of the ER diagram:
 FIGURE 10 ER DIAGRAM

1.8.1 ER Diagram Explanation

• The ER diagram was created using the requirements of E-Space Solutions scenario.
The large real-world objects were recognized as an entity like Colonist, Jet, Pilot, Trip, Job
and House.
 • Many to many relationships like Colonist and Job were solved with the help of junction
table. to do away with redundancy and keep normalization.
• Primary keys are used to identify each entity uniquely and the foreign keys are used to
determine relationships among tables.
• The accuracy and integrity of data are ensured by cardinality and participation
constraints.

1.6. RELATIONSHIP TYPES

Foreign keys are used in the implementation of relationships. Remember, as I have mentioned
above in point 1). In case of identifying relationships, then the primary key of an entity type
should be extended to the relation of a weak entity type. It is necessary to consider the
cardinality and the degree of the relationship. In subsequent examples. Examples 1 to 3 have to
do with binary relationships and example 4 deals with n-ary relationships.

1. Binary One-To-One

Generally, in one-to-one relationship, a designer has an option of where to apply the

relationship. The option of having a foreign key in either one of the two relations or
both is optional. It is worth thinking of locating the foreign key in a manner that
would reduce the nulls. In case there are any attributes to the relationship, the
attributes may be in either relation.

2. Binary One-To-Many

Under one-to-many relationships, the designer will need to include a foreign key in
the relation that will represent the many sides of the relationship. The other attributes
that are defined on the relationship are also found on many sides.

3. Binary Many-To-Many

Many to many relationships will have to be implemented with a different relation.

This new relation will contain a composite primary key, which contains the primary
keys of the involved types of entity as well as any discriminator attribute, and
additional attributes of the relationship provided they are present.

4. n-ary, n>2
An n-Ary relationship is a relation that is created with a new relation. This newly
created relation has a composite primary key which is constituted by the primary keys
of the participating entity types and any discriminator attribute, and any other
 attribute. The formation of the PK has one exception: when the cardinality is
associated with either of the entity type, the primary key of the entity type is not
included in the primary key of the new relation but rather comprises of foreign key.

5. Attributes

Attributes (except the derived and composite ones) are also required to be present in
relations. You decide to add derived attributes in case they will enhance performance.
Next, we take attributes based on their simple, atomic, multi-valued or composite
attributes.

1. Simple, atomic

They are added to the relation that is formed to the relevant entity type, many-to-
many relationship or n-ary relationship.

2. multi-valued

A new relation is used to implement each of the multi-valued attributes. This

relation will contain the primary key of the initial entity type. The key to the new
relation will be the key to the entity type and the multi-valued attribute. Remark
that the attribute in the new relation is a multi-valued attribute no longer.

1.7. ENTITIES AND ATTRIBUTES FROM THE SCENARIO

Each table was implemented with the following important details:

 Colonist: ColonistID (INT AUTO_INCREMENT PK), personal fields (FirstName,

Surname, DOB), Gender ENUM, contact info, and FamilyCount. Age is included for
convenience but can be derived from DOB in maintenance tasks. Checks guard numeric
values.

 Dependent: DependentID (INT AUTO_INCREMENT PK), ColonistID FK referencing

Colonist(ColonistID).

 Jet: JetCode (VARCHAR PK), Seats, EngineType, PowerSource, YearMade, and Weight.

 Pilot: PilotID (INT AUTO_INCREMENT PK), qualification, rank, JetCode FK to

indicate assignment/qualification. Deleting a jet will be prevented if pilots or trips
reference it (FK protection).
  Trip: TripID (INT AUTO_INCREMENT PK), JetCode FK, LaunchDate, ReturnDate.

 House: LotNo (VARCHAR PK), NoOfRooms, SqFeet, AllocationType

ENUM('Family','Shared'). House occupancy rules are enforced at application layer or via
triggers if implemented.

 Job: JobID (INT AUTO_INCREMENT PK), JobName, JobDescription.

 ColonistJob: Composite PK (ColonistID, JobID), FKs to Colonist and Job.

 TripPassenger: Composite PK (TripID, ColonistID), FKs to Trip and Colonist.

Foreign key constraints ensure referential integrity and prevent orphaned records. Composite
primary keys on junction tables prevent duplicate assignments.

1.8. RELATIONAL SCHEMA FOR THE ER DIAGRAM IN THE SCENARIO

Each strong entity in the ER diagram will have its own relational table, and the primary key of
the entity will become the primary key of the table.

FIGURE 11 ER DIAGRAM FOR THE SCENARIO

• COLONIST (ColonistID PK, FirstName, Surname, DOB, Gender, Age)

• DEPENDENT (DependentID PK, ColonistID FK, Name, DOB, Relationship)
• JET (JetCode PK, Seats, EngineType, PowerSource, YearMade, Weight)
 • PILOT (PilotID PK, Name, Rank, JetCode FK)
• TRIP (TripID PK, JetCode FK, LaunchDate, ReturnDate)
• JOB (JobID PK, JobName, Description)
• COLONIST_JOB (ColonistID FK, JobID FK)
• TRIP_PASSENGER (TripID FK, ColonistID FK)
• 9. Role (Strong Entity)
• Role (RoleID, RoleName)

1.8.1 Data Dictionary

Table Name Attribute Data Type Key Description

Name

Colonist ColonistID INT PK Unique identifier for each

colonist

Colonist FirstName VARCHAR(50) — First name of the colonist

Colonist Surname VARCHAR(50) — Last name of the colonist

Colonist DOB DATE — Date of birth of the colonist

Colonist Gender ENUM — Gender of the colonist

(Male/Female)

Colonist Age INT — Age of the colonist

Dependent DependentID INT PK Unique identifier for each

 dependent

Dependent ColonistID INT FK References Colonist(ColonistID)

Dependent Name VARCHAR(100) — Name of the dependent

Dependent Relationship VARCHAR(50) — Relationship to the colonist

Jet JetCode VARCHAR(20) PK Unique code identifying each jet

Jet Seats INT — Number of passenger seats

Jet EngineType VARCHAR(50) — Type of engine used by the jet

Jet PowerSource VARCHAR(50) — Power source of the jet

Trip TripID INT PK Unique identifier for each trip

Trip JetCode VARCHAR(20) FK References Jet(JetCode)

Trip LaunchDate DATE — Launch date of the trip

Trip ReturnDate DATE — Return date of the jet

Job JobID INT PK Unique identifier for each job

Job JobName VARCHAR(100) — Name of the job assigned to

colonists

ColonistJob ColonistID INT FK References Colonist(ColonistID)

ColonistJob JobID INT FK References Job(JobID)

TripPassenger TripID INT FK References Trip(TripID)

TripPassenger ColonistID INT FK References Colonist(ColonistID)

1.9. DEPENDENCIES AND ANOMALIES
Dependence on database design is statements that define how attributes in a table are connected
whereas anomalies in database design are issues that occur because of unsuitable database
design particularly when the data is not correctly normalized. Dependences, especially
functional dependencies, emphasize dependencies where the value of one attribute influences the
value of another attribute. Anomalies that may be because of these relationships include
insertion, update and deletion anomalies which create data inconsistency and redundancy.

What are the various Database Dependencies?

• Functional dependency - A functional dependency is where the information that has been
stored in a table is able to identify other information in the same table in a unique way.
Consider it a correlation of two attributes of the same relation.
 ColonistID is a functional dependency in the Colonist table of First Name,
LastName, and DateOfBirth. It is a functional dependency.

 Full functional dependency - A full functional dependency occurs when you already
satisfy the conditions of a functional dependency and the set of attributes on the left hand
side of the functional dependency statement cannot be further simplified.
• Trivial functional dependency - When you explain a functional dependency of an attribute
on a set of attributes that contains the original attribute it is known as a trivial functional
dependency. It is trivial as it is rooted on common sense.
• Transitive dependency - Transitive dependencies include the existence of an indirect
relationship that results in a functional dependency.

 In the Pilot table, TotalSpaceHours may be dependent on RankID that is dependent

on PilotID. This leads to transitive dependency.

• Multi valued dependency - It is said to be a multivalued dependency when a row in a table

implies the existence of another row or row in the same table.
• Partial dependency - The partial dependency is a dependency that occurs when a non-
prime attribute depends functionally on some portion of the candidate key.
  LaunchDate only depends on TripID this would be a partial dependency.

What is the DBMS way of Causing Anomalies?

Anomalies are often caused by poorly developed tables within databases. What does it mean by
poor construction? A poorly designed table is evident when a designer planning to create the
database does not recognize the entities that are tied to each other, like the rooms of a hostel and
the hostel and then reduce the opportunities of one entity being independent of another.

Database anomaly is a database fault, which may arise due to improper planning or when all the
information is stored in flat database. This is typically eliminated via a normalization process,
and this consolidates as well as separates tables. Normalization of the database helps in
minimizing the chances of the creation of tables that lead to anomalies.

Type of Anomalies in DBMS

Various forms of anomalies may arise in a database. There are also the problems of redundancy
anomalies, as in the case of tests when you are a student, and when you are seeking employment,
there is the employment interview. They however, can be easily identified and resolved. These is
what we should look at:

a. Insertion Anomaly

When we cannot add data without the availability of another attribute.

 FIGURE 12 INSERTION ANOMALY

b. Deletion Anomaly

The deletion of a record also deletes unwanted information.

FIGURE 13 DELETION ANOMALY

c. Update Anomaly

Arise when it is necessary to modify data in one location and it is necessary to modify
the data in many locations.

FIGURE 14 UPDATE ANOMALY

1.10. NORMALIZATION
Normalization of the database is a process of arranging the information in tables so that the
outcome of utilizing the database is never ambiguous or not as anticipated. This type of
normalization is inherent to the relational database theory. It can even duplicate data in the
database and normally leads to generation of more tables.

Database normalization as a concept is usually attributed to E.F. Codd, an IBM researcher who
in 1970 wrote a paper detailing the relational database model. The relational technique required
a key component to be what Codd termed as a normal form of database relations. The
normalization of such data had a ready market in the 1970s and 1980s, a period when disk drives
were relatively expensive and a much-needed means of data storage was very much required.
Other techniques such as denormalization have also been used since then.

Data normalization rules

Although there are rules of data normalization that propensity tend to multiply the data, it does
not bring in data redundancy, which is thus unnecessary duplication. The process of database
normalization is generally an upgrading process by the already performed exercise of identifying
the data objects to be in the relational database.

Relational database tables have normalization degrees that have been defined and they are:

First normal form (1NF) - It is the most basic form of database normalization, and it usually
represents the definition of any database, i.e.:

• It has two-dimensional tables that have rows and columns.

• Each column is identified with a sub object or an attribute of the object that is embodied
by the whole table.
• Each row is an instance of that object or attribute and must differ in some way with any
other row.
• Everything in any column should be of the same type.
Second normal form - At this stage of normalization, every column in a table that is not a
determiner of the contents of some other column must be itself a function of the rest of the
columns in the table.

Third normal form (3NF) - At the second normal form, it is still possible to make changes since
a change on a single row of a table can also impact on the information that is referenced by
another table.

Basic normal forms can be extended to the domain/key normalized form where the key is a
unique identifier of a row in a table and the Boyce-Codd normal form (BCNF) which is an
improvement and refinement of the methods of the 3NF to deal with certain forms of anomalies.

The fact that database normalization will prevent or minimize data anomalies as well as data
redundancies and data duplications as well as enhance data integrity has made it a valuable tool
in the data developer repertoire over the years. This has been among the characteristics of the
relational data model.

1.11 EVALUATION OF ER DIAGRAM AND LOGICAL DESIGN EFFECTIVENESS

The ER diagram and logical database design created to address E-Space Solutions is useful in
fulfilling the identified user and system requirements. The user need like registration of
colonists, dependent management, taking of trips, assigning of jobs and housing is provided
directly using well-structured entities and relationships. Primary key and the foreign key are
used to be sure that records are not duplicated and are linked correctly.

System-wise, the design enhances data integrity by having referential constraints and
normalizing to the Three Normal Form (3NF) which lessens redundancy and eliminates
insertion, update and deletion anomalies. Junction tables are appropriate in many to many
relationships like in the case of colonists and jobs, which enhance flexibility and scalability.
The ER diagram clearly illustrates entity relationships and cardinalities in accordance with real-
life principles in the Mars colonization situation. The logical design is efficient in querying and
further extension, since more colonists, jobs and trips can be added without significant
alterations in the structure. In general, the database design is efficient and can be utilized in the
long-term operation.

The database design directly supports key user requirements such as colonist registration,
dependent management, trip scheduling, job assignment and housing allocation. Each
requirement is mapped to a dedicated entity, ensuring accurate and efficient data handling.
System requirements such as scalability, data integrity and security are supported through
normalization, use of foreign key constraints and role-based access control. This demonstrates
that the design is not only structurally correct but also practically aligned with both user and
system needs.

ACTIVITY 02
2.1 TOOLS FOR DEVELOPMENT
The application of a modern database-driven application is largely successful in how it selects
the right tools for managing databases, design and integration of the interface. These instruments
are important in guaranteeing data accuracy, system reliability, security, and easily maintained.
Various tools of industry standard have been chosen in this project to aid in design,
development, implementation, and management of database management system (DBMS) and
its user interfaces. The main tools to be utilized are Microsoft SQL Server, SQL Server
Management Studio (SSMS), Figma as well as Visual Studio. Each of the tools has a specific
purpose in the system development lifecycle and leads to efficiency and strength of the solution
in general.

A database management and development application has been developed based on database
management and data model principles. <|human|>2.1.1 Database Management and
Development A database management and data model application has been built through
database management and data model principles.

2.1.1. SQL server management studio and Microsoft SQL Server.

 FIGURE 15 SQL SERVER

This project uses the Microsoft SQL Server as the central database management system. SQL
server is a proprietary relational database management system (RDBMS) created by Microsoft
and this is used in enterprise level applications because of its reliability and performance and
large level of features. It aims at storing, retrieving, managing and securing a huge amount of
structured data and it offers support to numerous applications and users simultaneously.

The main interface that is used to interact with Microsoft SQL Server is SQL Server
Management Studio (SSMS). SSMS is a Microsoft developed built-in environment that offers
database administrators, developers, and system analysts with a robust collection of tools to
manage the SQL server infrastructure effectively.

2.1.2. Introduction to SQL Server management Studio (SSMS).

 FIGURE 16 SQL SERVER MANAGEMENT STUDIO

The SQL server management studio provides a rich and easy to use graphical interface which
eases the complex database operations. It is the fusion technology of graphical design tools and
advanced scripting capacity, which makes it applicable to both the novice and the professional.
SSMS facilitates the entire database lifecycle of development such as creating a database,
schema design, data manipulation, security setup, performance tuning, and maintenance.

The other major strength of SSMS is that it can operate a wide variety of SQL Server
environments through one unified interface. This guarantees coherence and productivity in
operating with hybrid or cloud database solutions.

Principal Interfaces of SQL Server Management Studio.

As shown in -Server Management Studio Object Explorer, SSMS is composed of several

integrated features, each being tailored to suit particular database development and
administration processes.

Object Explorer

Object Explorer is used to show all database objects in a hierarchical manner in one or more
SQL server instances. These objects are databases, tables, views, stored procedures, functions,
triggers, indexes, and security items; in the form of logins and roles. Object Explorer is an
important part of SSMS because it allows the administrators to create, modify, delete and
monitor database objects easily through it.

FIGURE 17 SERVER MANAGEMENT STUDIO OBJECT EXPLORER

1. Template Explorer

Template Explorer provides the developers with ready-to-use SQL scripts templates
of child tasks like creating tables, views, stored procedures and backups. The
templates assist in saving time of development, enhance the level of consistency and
minimizing syntax error by availing standardized forms of scripts.

2. Solution Explorer

Solution Explorer may be used to group scripts and administrative objects into
projects although it has been degraded. The feature can be applied in handling
projective database undertakings whereby several scripts and set ups are involved.
 3. Visual Database Tools

The SSMS is designed with visual design functionality allowing users to create and
edit database tables, relationships, and diagrams without having to write large
amounts of SQL code. The tools can also be very helpful in the initial phases of the
database design and visualize how the tables relate to each other.

4. Query and Text Editors

The SSMS Query and Text Editors offer an effective platform to type, execute, and
debug SQL queries and scripts. Syntactic highlighting, IntelliSense, error reporting,
and query execution plans are some of the features that improve productivity and
minimize the development errors.

The Strengths and Weaknesses of SQL server and SSMS.

SQL Server Management Studio (SSMS)

Advantages Disadvantages
User-Friendly Interface: SSMS provides a Resource-Intensive: SSMS can be demanding on
graphical interface, making database system resources, slowing down performance on
management easier for beginners and less powerful computers.
experienced users alike.

Comprehensive Toolset: It includes tools for Limited Cross-Platform Support: Primarily

writing queries, configuring security, monitoring designed for Windows, SSMS has limited
performance, and managing databases. compatibility with macOS and Linux.

Built-In Performance Monitoring: SSMS has Complex Configuration: Some features, like
integrated tools for real-time performance server-level security and roles, require complex
tracking and optimization. configurations.

Script Generation: Easily generate SQL scripts Dependency on SQL Server: SSMS functionality
for backups, restores, and migrations, supporting is mainly tailored for SQL Server, limiting
automated tasks. support for other database systems.

Debugging Support: SSMS includes debugging Steep Learning Curve for New Users: While
tools that allow developers to troubleshoot SQL powerful, SSMS’s feature-rich environment can
code directly. be overwhelming for beginners.
Microsoft SQL Server
Advantages Disadvantages
High Performance: SQL Server provides High Licensing Costs: SQL Server licensing,
highspeed transactions and optimized especially for enterprise versions, can be costly
performance for large databases. for smaller organizations.

Scalability: Capable of handling databases of all Windows-Centric: Although available for

sizes, SQL Server supports enterprise-scale Linux, SQL Server is primarily optimized for
applications. Windows environments.

Robust Security Features: Advanced security Hardware Demands: High-performance

options like encryption, row-level security, and features may require powerful hardware, raising
data masking protect sensitive data. infrastructure costs.

High Availability and Disaster Recovery: SQL Limited Native Support for Non-Relational
Server offers features like Always on Data: Although SQL Server supports JSON and
XML, it’s primarily built for relational data.
Availability Groups, replication, and automated
backups.

Integration with Microsoft Ecosystem: SQL Complex Setup and Maintenance: SQL
Server integrates seamlessly with other Microsoft Server’s advanced features require careful setup
products like Azure, Power BI, and Active and ongoing maintenance, needing skilled
Directory. administrators.

2.2. USES OF QUERIES N SQL SERVER MANAGEMENT STUDIO (SSMS).

SQL queries are also important in my Mars Colonization System in the process of data
management and manipulation. The SSMS queries can effectively operate in tables e.g. Colonist,
House, Pilot and Trip, to retrieve, update or delete information. These queries are used to ensure
data integrity and ease the administration of your complicated Mars colonization database.
SQL Query Advantages of SSMS.

• Data Manipulation: Queries allow the manipulation of data flexibly of inserting, updating
and deleting data across tables.
 • Data Retrieval: Queries such as SELECT can be used to retrieve a particular data which
may be filtered and sorted according to the necessity.
• Data Integrity and Security: Data is only altered by the authorized users with permissions
to minimize error and unauthorized access by users.
• Complex Operations: Operations such as GROUP BY and HAVING allow more complex
operations on data, which are necessary for reports and insights.
 Whereas efficiency With WHERE and ORDER BY, it can shrink and organize large data
sets, thus data management can be significantly quicker.

Common SQL Queries

a. INSERT
Purpose: Adding new records in tables, such as adding new pilots or houses.
b. SELECT
Purpose: To access information, which is commonly used to see colonists employed
in a certain house.
c. UPDATE
Purpose: To change some existing records, e.g., changing the contact details of a
colonist.
d. DELETE
Intention: To delete records, e.g. to delete a dependent of the database.
e. WERE
Purpose: Other queries can be used to refines the results such as locating pilots of a
certain rank.
f. ALTER
Purpose: To alter table structure, such as inserting a new column on columnist Pet.
g. DROP
Purpose: To destroy a table or column permanently like the deletion of an outdated
column.
h. BETWEEN
Purpose: It is used to filter data within a range, such as colonists of a certain age.
I. IN
 Purpose: To define various possible values, such as locating colonists based on the
specific qualification.
j. GROUP BY
Purpose: To refer to various possible values such as identifying colonists based on
certain qualifications.
k. ORDER BY
Purpose: To classify by result, which is also helpful to provide colonists based on
their age.
l. HAVING
Purpose: Like WHERE, but on aggregates, such as find qualifications having more
than 10 colonists.

FIGURE 18 NUMBER OF COLONISTS ASSIGNED TO EACH JOB

shows the output of a GROUP BY query that displays the number of colonists assigned to each
job. This information helps administrators understand workforce distribution and supports
effective job planning.
FIGURE 19 COLONIST DETAILS WITH ASSIGNED JOBS

This query combines data from multiple tables to display colonists along with their assigned
jobs. The extracted information supports operational planning and role allocation.
FIGURE 20 TRIP DETAILS WITH ASSIGNED JET INFORMATION

This query provides consolidated trip and jet information, assisting administrators in monitoring
flight schedules and resource allocation.

2.3 ASSESSMENT OF MEANINGFUL DATA EXTRACTION USING QUERIES

The implemented SQL queries in the system generate useful and relevant information that can be
used by the management in decision-making. As an example, the trip information with colonist
assignments and jet data are displayed with the assistance of JOIN queries, which may be
utilized by administrators to manage flights. Group BY statements are also utilized to determine
the number of colonists per job, which can be used to plan the workforce in the Mars colony.
These queries convert raw data into valuable information, and this shows that the database is an
effective tool in meeting operational and managerial requirements.
FIGURE 21 DISPLAY OF ALL COLONIST RECORDS USING SELECT QUERY

This SELECT query retrieves all colonist records stored in the database. The output provides
administrators with complete visibility of colonist information for monitoring and verification
purposes.

FIGURE 22 FILTERING COLONISTS USING WHERE CLAUSE

FIGURE 23 COLONIST DETAILS WITH ASSIGNED JOBS USING JOIN QUERY

FIGURE 24 NUMBER OF COLONISTS ASSIGNED TO EACH JOB

FIGURE 25 SORTED COLONIST RECORDS USING ORDER BY CLAUSE

2.4. INDEXING AND PERFORMANCE CONSIDER.

The database has been indexed to enhance query performance and efficient data retrieval which
is particularly effective when many records are involved. Primary key columns which include
Colonist ID, Trip ID, Job ID and Jet Code and foreign key columns which are used in the JOIN
operations were created as indexes. The indexes are useful in making query execution time much
faster because the location of the records in the database engine can be done at a high rate
without needing to run a complete scan of the table.
Another indexing has been applied to the common searching attributes such as columns that are
used in the WHERE, ORDER By, and JOIN statements. This strategy improves the functionality
of reporting queries like retrieving passengers on a trip, colonists to a given job or occupancy of
housing. The indexing policy was well calculated to harmonize performance increase and storage
as well as maintenance cost. It was not over-indexed to avoid causing unwarranted performance
degradation in the process of an insert operation, update operation and delete operation.
Generally, the indexing plan promotes the aspect of scalability and maintains a steady
performance when the database is expanded.
2.5. SECURITY AND ROLES

Role based access control ensures security in the database system to ensure that sensitive
information is not tampered with, and that manipulation of data is not carried out by
unauthorized people. The roles in which the users operated were determined according to their
responsibilities in operation like administrative users, operational users and read only users. The
principle of least privilege was implemented by issuing each role the absolute minimum
privilege to perform its assigned work.
Database objects, such as creation of tables, creation of constraints, and user management, are
allowed to be handled by administrative roles. Operational roles are permitted to make controlled
CRUD actions to core tables like Colonist, Trip and Job and reporting roles are only permitted to
make SELECT queries. Authentication and permission settings guarantee that only authorized
users would manage to access or edit the data. This security model improves the data
confidentiality, integrity and accountability in the system.

• Database security was implemented using SQL Server authentication and role-based
access control.
• Different user roles were created such as Admin and Read-Only users.
• Permissions were granted to restrict unauthorized access to sensitive data.

2.6 DATABASE SECURITY AND MAINTENANCE EVALUATION

Role-based access control has been applied in the implementation of system security where users
are only allowed to carry out tasks that are relevant to their duties. DB roles like Administrator,
Data Entry Operator and Pilot are assigned varying access permissions. Each of the
administrators has complete control of the system and Data Entry Operators can only input the
records and make alterations. Pilots can only look at trips and passenger information. This will
enhance security of data and avoid unauthorized access. Also, the database maintenance is
reinforced by regular database backups and indexing of more actively used tables, enhancing the
performance and making the data reliable.
Figure 26 SQL Server Database

FIGURE 27 CREATION OF DATABASE USER MAPPED TO SQL SERVER LOGIN

FIGURE 28

FIGURE 29

FIGURE 30
FIGURE 31

FIGURE 32

2.7 DATA VALIDATION AND BUSINESS RULES.

Both a database level and an application level are implemented to guarantee that the data stored
was accurate, consistent and reliable. At the database level, incorrect data is prevented using
constraints like PRIMARY KEY, FOREIGN KEY, Check and ENUM. As an example, gender
values are limited to a list of allowed values, numeric values are limited to reasonable limits, and
referential integrity is ensured by foreign key associations.
Application layer implements business rules whose logical validation is required e.g. occupancy
limits of housing or job assignment rules. This validation and verification system is layered to
provide consistency in application of complex rules as well as flexibility in system behavior. All
these validation mechanisms help in minimizing data anomalies and improve the reliability of
the entire system.
2.8 UI INTEGRATION AND EVIDENCE OF IMPLEMENTATION
The database was coupled with a simple user interface to show that it was successfully
implemented with database operations. The interface will enable the required CRUD
functionality enabling users to add, modify, remove and read the records concerning colonists,
jobs, trips and housing. The UI was also tested so that all the database constraints and validations
are implemented when the user interacts with the database.
Some of the pieces of evidence of implementation are screenshots of ran SQL scripts, the sample
data insertion results, and the output of queries presented in the interface. These text snapshots
demonstrate that the database schema, constraints and relationships are working appropriately.
The UI integration assures that the database system is practically usable in a real-world
operational environment.

2.9 DATABASE ARTIFACT’S LOCATION IN THE REPORT.

Database artifacts may be placed in the right place within the report to ensure that they are
readable. Important design components like ER diagram and relational schema are to be
positioned right after the corresponding conceptual or logical design sections. The appendices
should contain detailed SQL commands such as the CREATE TABLE commands and the sample
INSERT queries so that they do not interfere with the flow of the main report.
The database execution screenshots, test results, and UI interactions should also be provided in
the appendices and be mentioned in the main text. All the appendices must be well labeled and
cross-referenced to enable the readers to find supporting evidence easily. Such orderly
arrangement of the artifacts of database enhances the general order and professionalism of the
report.

ACTIVITY 03
3.1 TEST PLAN TABLE
The database system was tested using a structured test plan to verify correct functionality,
enforcement of constraints, and accuracy of query results. The test cases were designed to cover
all major CRUD operations as well as validation and relationship handling.
Tes Test Test Data / SQL Query Expected Actual Statu
t Descriptio Result Result s
No n
1 Insert a new INSERT INTO Colonist (FirstName, Colonist Record Pass
Colonist Surname, DOB, Age, Gender) record inserted
VALUES ('Emma','Watson','1995-05- inserted
15',30,'Female'); successfull
y
2 Insert a INSERT INTO Dependent (Name, Dependent Record Pass
Dependent DOB, Age, Gender, Relationship, linked to inserted
ColonistID) VALUES ('Lily','2020-03- ColonistID
10',5,'Female','Daughter',4); 4
3 Update UPDATE Colonist SET Age = 31 Age Update Pass
Colonist WHERE ColonistID = 4; updated d
 Age correctly
4 Delete a Jet DELETE FROM Jet WHERE Deletion Error Pass
with FK JetCode='J200'; restricted shown
due to FK
5 Select SELECT * FROM Colonist WHERE List of Correct Pass
Colonists Age > 40; colonists > results
above 40 40
6 Assign INSERT INTO ColonistJob Job Inserted Pass
Colonist to (ColonistID, JobID) VALUES (2,2); assigned
Job
7 Assign Trip INSERT INTO TripPassenger (TripID, Passenger Inserted Pass
Passenger ColonistID) VALUES (2,2); added to
trip
8 Invalid INSERT INTO Colonist ENUM Error Pass
Gender Test (FirstName,Surname,DOB,Age,Gender violation shown
) VALUES ('Test','User','2000-01- error
01',23,'Unknown');
9 Invalid Age INSERT INTO Colonist CHECK Error Pass
Test (FirstName,Surname,DOB,Age,Gender constraint shown
) VALUES ('Test','Age','2000-01-01',- error
5,'Male');
10 JOIN SELECT [Link], [Link] Correct Correct Pass
Query Test FROM Colonist c JOIN TripPassenger passenger output
tp ON [Link]=[Link] list
JOIN Trip t ON [Link]=[Link];

3.3 EVALUATION OF TESTING EFFECTIVENESS AND TEST DATA SELECTION

The testing process confirms that the database system functions correctly according to the
defined requirements. All CRUD operations were executed successfully, and database constraints
such as primary keys, foreign keys, ENUM, and CHECK constraints were properly enforced.
Invalid data entries were rejected by the system, demonstrating strong data validation and
integrity control.
JOIN queries and many-to-many relationship handling through junction tables produced accurate
and consistent results. The testing outcomes indicate that the database design is reliable, stable,
and suitable for real-world usage.
Existence of valid and invalid test data was used to enhance the effectiveness of testing. The
confirmation was made that valid data was used to ensure that the system works appropriately
under normal conditions whereas invalid data was used to test constraint enforcement and error
handling. Boundary values were also tested including maximum passenger capacity to make sure
that business rules were implemented. This mode of testing also assisted in discovering possible
problems at the initial stage and the database would act in different situations in a dependable
manner.
3.4 CONCLUSION OF TESTING
In conclusion, the testing phase verified that the E-Space Solutions database system meets its
functional and non-functional requirements. All test cases passed successfully, confirming
correct constraint enforcement, data accuracy, and relationship management. The system can
handle realistic operational scenarios and provides a dependable foundation for future expansion
and optimization.

ACTIVITY 04

4.1. USER MANUAL FOR MARS COLONIZATION SYSTEM

1. Introduction
Overview of the Mars Colonization System

The Mars Colonization System is a cutting-edge application designed to streamline the complex
operations involved in managing colonist data, trip scheduling, housing assignments, and more
for our Mars colonization initiative. The system aims to enhance operational efficiency and
ensure a seamless experience for users engaged in this pioneering endeavor.

Purpose of the User Manual

This user manual is intended to provide comprehensive guidance on the features and
functionalities of the Mars Colonization System. It serves as a resource for administrators and
users, outlining each aspect of the system to facilitate its effective use. By following this manual,
you will gain a clear understanding of how to navigate the system and leverage its capabilities to
manage various tasks associated with Mars colonization.

Current Status of User Roles

As of now, the system is equipped to support only one user role: Admin. This role has full access
to all features and functionalities of the system. I am currently working on developing additional
user roles to improve usability and ensure that other personnel can interact with the system in a
more specialized manner.

2. Getting Started
System Requirements

To run the Mars Colonization System effectively, ensure that your hardware and software meet
the following requirements:

• Windows 10 or later
• Minimum 4 GB RAM
• .NET Framework 8.0 installed
• Access to a database server (SQL Server)

Installation Guide
To install the Mars Colonization System:

• Download the installation package from the designated repository.

• Following the installation prompts us to complete the setup.
• Ensure all system requirements are met before proceeding with installation.
Logging In to the System
• Launch the Mars Colonization System application.
• Enter your admin username and password in the login form.
• Click the Login button to access the main dashboard.

3. User Roles and Permissions

Overview of User Roles
Currently, the system only has an Admin user role, which is designed to manage and oversee all
functionalities within the application. The admin is responsible for maintaining the system's
integrity and performance.

Admin Role Functions

As an admin, you have the following functions available to you:
 • User management (creating, editing, and deleting user accounts)
• Data entry and management for colonists, jets, trips, and housing
• System configuration and settings adjustment

4. Navigating the User Interface

Overview of the Main Dashboard
Upon logging in, you will be greeted with the main dashboard. This interface is designed to
provide a quick overview of the system's functions and navigation options.

Key Navigation Elements

The main dashboard includes several key elements:

• Navigation Bar: Access different sections of the system, including colonist

management, jet registration, trip scheduling, and housing assignments.
• Quick Action Buttons: Direct access to frequently used features for efficiency.
• Understanding the Layout
The layout is intuitive, allowing you to easily find and access the required functionalities.
Sections are clearly labelled, and each feature is organized logically to ensure a smooth user
experience.

5. System Features
a) . Colonist Management
Adding a New Colonist
• Go to the Colonist Management section.
• Click on Add New Colonist.
• Fill in all relevant details, including name, date of birth, and contact information.
• Save the information by adding the colonist to the database.
Editing Colonist Information
• Locate the colonist you wish to edit in the list.
• Click on the Edit button next to their name.
• Make the necessary changes and save the updates.
Deleting a Colonist
• Find the colonist in the list.
• Click the Delete button and confirm the action.
b) Dependent Management
Adding Dependents
• Navigate to the Dependent Management section.
• Click Add Dependent.
• Fill in the dependent's information and save.

Editing Dependent Information

• Locate the dependent you wish to edit.
• Click on the Edit button.
• Update the information and save the changes.
c) Jet Registration
Steps for Registering an E-Jet
• Go to the Jet Registration section.
• Click on Register E-Jet.
• Enter all required details, such as jet code and specifications.
• Submit the information to register the jet.

d) Trip Scheduling
Scheduling a Trip to Mars
• Access the Trip Scheduling section.
• Click on Schedule Trip.
• Fill in the trip details, including date and jet information.
• Save the trip to confirm scheduling.

Assigning Colonists to Trips

• After scheduling a trip, go to the assigned trip's details.
• Click on Assign Colonists.
 • Select colonists from the list and confirm their assignment.

e) Housing Assignment
Assigning Housing to Colonists
• Navigate to the Housing Assignment section.
• Select the colonist to assign housing.
• Choose the appropriate housing option and save the assignment.

f) Job Assignment
Assigning Jobs to Colonists
• Go to the Job Assignment section.
• Select a colonist from the list.
• Choose a job from the available options and save the assignment.
[Link] Functions
• Overview of Admin Controls
As the admin, you have access to various controls that allow you to manage the system's
overall functionality. This includes user management, data entry, and system settings.

• Managing User Accounts

You can create, edit, and delete user accounts, ensuring that only authorized personnel have
access to the system.

• Data Backup and Maintenance

Regular data backups are crucial for maintaining system integrity. Ensure that backups are
performed frequently to prevent data loss.

[Link]
Common Issues and Solutions
• Login Issues: Ensure that your username and password are correct. If forgotten, use the
password recovery option.
• Slow Performance: Check your internet connection and ensure system requirements are
met.
 4.2 TECHNICAL DOCUMENTATION OVERVIEW (DFDS AND FLOWCHARTS)
The database has its technical documentation in terms of system diagrams to aid in
understanding and maintenance. The Level 0 Data flow diagram is a high-level summary of the
interaction between the users and the database system. Level 1 DFDs also describe inner
processes like the registration of the colonists, trip assignment and job allocation. Flowcharts too
are provided to provide a pictorial representation of process logic. The documents assist the non-
technical and technical users in their knowledge of how the system functions.

4.2.0. DIAGRAMS
Class Diagram
The Level 0 Data Flow Diagram provides a high-level overview of the system. It shows how
external users interact with the central database and outlines the main data flows within the
system.

The Level 1 Data Flow Diagram breaks down the main processes into detailed sub-processes
such as colonist registration and job allocation, showing how data flows internally.

Figure 29 - Class Diagram

Use Case Diagram
The use case diagram illustrates system functionality from a user perspective. It identifies
different actors and their permitted interactions with the system.

FIGURE 33 USE CASE DIAGRAM

Flow Chart
FIGURE 34 FLOW CHART
FIGURE 35 HOUSING ASSIGNMENT FLOWCHART
FIGURE 36 JOB ASSIGNMENT FLOWCHART
4.3 STRENGTHS & WEAKNESSES
Strengths

• Espace Solutions database has shown several strengths that are contributing to its
operational efficiency and sustainability:
• An efficient Many-to-Many Relationship Management.
• Junction tables are used to represent many-to-many relationships, and to guarantee a
correct representation of complex relationships, like the situation of multiple jobs in a
colonist or multiple passengers in a trip.

Referential Integrity

• Foreign key constraints are used to maintain consistency of related records in different
tables, and this prevents orphaned or invalid records.

Data Validation

• EMU and CHECK constraints are used to check the critical fields, so only acceptable
values can be put into the database, which minimizes the possibility of error.

Normalized Schema

• Redundancy is also kept to minimum through a 3NF schema, and this makes maintenance
easier as well as storage more efficient.

Indexed Columns

• Indexing of the columns most frequently asked boosts the performance of the database
particularly where large amounts of data are being handled.

Role-Based Access Control

• The assigning of privileges based on user roles guarantees security and controlled access,
and the absence of unauthorized operations.
Weaknesses / Limitations

• Although strong, the system has some weaknesses:

• Deletion Constraint as Foreign Key Constraints.
• To take an example, a jet cannot be erased when it is mentioned in trips or by pilots and
this can lead to inflexibility in some administrative situations.
• Unimplemented Triggers and Stored Procedures.

Reporting Limitations

• Now, reporting is based on direct queries and there are no pre-defined views or automatic
reports.
• Business Rules Enforced on Application, but not Database.
• The application layer, which may be improved in the database itself, contains rules like
maximum occupancy per house.

4.4 RECOMMENDATIONS: MAINTENANCE.

Performance, scalability and long-term reliability are guaranteed, so several maintenance
recommendations are proposed:

1. Regular Backups

• To avoid loss of data, it is important to perform backups on a daily or weekly basis with
the help of mysqldump or automated scripts.
• Backup of the stores should be done and restoration measures checked regularly.

2. Data Washing and Checking.

• Periodically check the database records to eliminate duplicates and old records.
• Check some of the most important data fields; the contact numbers, dates and IDs to
maintain data that is consistent.

3. Indexing and Performance Tuning.

• Optimize to optimize the performance of queries that are frequently run.

• Add indexes to the high-traffic columns and take into consideration the materialized
views or partitioning in case of the large datasets.
• Check query execution plans and tune it towards read-intensive operations to reduce
latency.
4. Audit Logging

• Use logging to facilitate in-insert, update, and delete operations to trace every one of the
changes.
• Accountability The audit logs will also help in detecting unwarranted or malicious
activity.

5. Security Updates

• Grant user privileges according to least privilege principle to restrict access to sensitive
data.
• Set up the use of strong passwords and revise user roles.
• Check permissions and access logs of the review databases and ensure that they comply
with the security standards.

6. Future Enhancements

• Derived fields, e.g. date of birth plus age calculation, should be automated.
• Create stored procedures/views that automatically produce routine reports.
• Enhance the front-end interface to minimize manual errors of data entry.
• Use automated scripts to perform backup and restore procedures and carry on business.

[Link]
Espace Solutions database system has been designed successfully and implemented and tested to
support Mars colonization project. It is efficient in controlling key information, such as colonists,
dependents, housing, jobs, jets, pilots, and planned trips. The relational database model and the
use of primary keys, foreign keys and many too many relationships using junction tables would
guarantee data integrity, consistency and reduction of duplication.

Foreign keys, ENUMs, and CHECK conditions are properly enforced by the system to ensure
that there is no entry of invalid data and that there is relational integrity. Functional cases such as
colonist registration, job assignment, trip passenger and housing management have been done
successfully with correct results.

MySQL has been proven to be scalable, maintainable, and secure about its physical
implementation. Efficiency and performance are guaranteed by role-based access, normalized
schema and indexing strategies. The future can be characterized by the further automation of the
age calculation and the implementation of the stored procedures to create the reporting, thus
making the use more convenient.
In general, Espace Solutions database system is a powerful, reliable, and scalable system to
support the operational needs of a Mars colonization project. If it is properly maintained,
regularly backed up, and constant reviews are conducted, it will remain a valuable resource to
the administrators and staff, facilitating day-to-day operations as well as long-term strategic
targets.

5.1 FUTURE IMPROVEMENTS AND ENHANCEMENTS

There are several features that can be added into the subsequent iterations of the database system.
It can be configured to have automated reporting features that are useful in management decision
making. Encryption of data can be introduced to improve the safety of personal data that is
sensitive. Besides, transferring the system to a cloud setup would enhance scalability and
availability. Such improvements will make the database system to be effective and sustainable in
the long term.