Assessor Feedback:

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

Resubmission Feedback:

• Please note resubmission feedback is focussed only on the resubmitted work

Assignment number and title:

Database System for E-Space Solutions

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:

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

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.

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.

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.

Based on E-Space Solutions scenario you will need to:

Activity 1 – User Requirements and Logical Design

1. Identify the user and system requirements of the E-Space Solutions System.
2. 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.
3. 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.

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

2. 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.)
3.  Evaluate the effectiveness of the given design (ERD and Logical design) in terms of the identified user and system requirements.

Activity 2 – Physical Design

1. 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.
   1. Provide evidence of the use of a suitable IDE to create a simple interface to insert, update and delete data in the database.
   2. 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.

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

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

1. Provide a suitable test plan to test the system against user & system requirements.
2. Provide relevant test cases for the database you have implemented.

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

1. 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.
2. 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.)
3. 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.
4. 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:

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

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

https://www.guru99.com/ (2022) Database Design in DBMS Tutorial: Learn Data Modeling [online] Available at: https://www.guru99.com/database-design.html [Accessed 1 August 2022]

https://www.integrate.io/ (2021) Complete Guide to Database Schema Design [online] Available at: https://www.integrate.io/blog/complete-guide-to-database-schema-design- guide/[Accessed 1 August 2022]

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

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

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

Journal articles:

Batra, D. & Davis, J. (1992). Conceptual data modelling in database design: similarities and differences between expert and novice designers. International Journal of Man-Machine

Studies, Volume 37, Issue 1, 1992, pp. 83-101. https://doi.org/10.1016/0020- 7373(92)90092-Y.

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

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

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

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

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

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

Reading:

Captain, F. (2013) Six-Step Relational Database Design™: A step by step approach to relational database design and development, 2nd edn, CreateSpace Independent Publishing Platform

Hernandez, M. (2003) Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design, 2nd edn, Addison Wesley

Stephens, R. (2008) Begin Database Design W / WS (Wrox Programmer to Programmer), 1st edn, Jossey-Bass

HN Global:

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

HN Global (2021) Student Resource Library. Available at: https://hnglobal.highernationals.com/subjects/resource-libraries

HN Global (2021) Textbooks. Available at: https://hnglobal.highernationals.com/textbooks

D1 Evaluate the effectiveness of the design in relation to user and system requirements.

LO2 & LO3

D2 Evaluate the effectiveness of the database solution in relation to user and system requirements and suggest improvements.

P2 Develop the database system with evidence of user interface, output and data validations, and querying across multiple tables.

P3 Implement a query language into the relational database system.

M2 Implement a fully functional database system, which includes system security and database maintenance.

M3 Assess whether meaningful data has been extracted through the use of query tools to produce appropriate management information.

D3 Evaluate the database in terms of improvements needed to ensure the continued effectiveness of the system.