BTEC Level 4 Unit 04 Database Design & Development Assignment Brief

Higher Nationals – Summative Assignment Feedback Form

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 Create a Design document for the total system of the given scenario.

Pass, Merit & Distinction                             P2 M2 D1                                                                                                         

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.

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 REFERRAL. 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 pag This is useful if individual sheets become detached for any reason.

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.

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

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.
• This document should include all the tasks given in the brief with the relevant design diagrams.
• A conceptual relational database. This should contain the all types of required designs of the total system according to the given scenario.
• 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

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

LO2 Create a Design document for the total system of the given scenario.

Transferable skills and competencies developed

Computing-related cognitive skills :

• Demonstrate knowledge and understanding of essential facts, concepts, principles andtheories relating to computing and computer applications
• Use such knowledge and understanding in the modelling and design of computer-basedsystems for the purposes of comprehension, communication, prediction and theunderstanding of trade-offs
• Recognise and analyse criteria and specifications appropriate to specific problems, andplan strategies for their solutions

Computing-related practical skills :

• The ability to specify, design and construct reliable, secure and usable computer-basedsystems
• The ability to evaluate systems in terms of quality attributes and possible trade-offspresented within the given problem
• The ability to critically evaluate and analyse complex problems, including those withincomplete information, and devise appropriate solutions, within the constraints of a

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  

FoodHub Company is a well-known fast food company locate in United State of America. It was started 14 years ago & the company management decided to open branches worldwide. So they are going to open a new branch in Sri Lanka as well. The company decided to hire a local database expert to develop a Database Management system to assist in this project for decision making. Therefore, the data should maintain accuracy & the consistency throughout the data life cycle.

FoodHub Company only delivering foods for the customers & they are not offer or selling food in the outlets. For delivery they have separate staff, who are known as Riders. There are other type of employees as well, but their details are not recorded in the database in the initial level. In the initial level the company consider to keep records of food deliveries & related information only. If the system success, they will enhanced for Human Resource Management & Finance Management integrate with initial system of Delivery Management system.

In this system the Customer will identify with unique Customer ID, Customer Name, NIC, Date of Birth, Contact Number & Address with Location No., Lane, Street & city. The customers’ address will consider as the order delivery address. One customer can place many orders. But one order will have only one customer. The order can be place over the phone & it will enter by a staff member of the company. Orders will identify by unique Order No, Order Date, Order Time, Order Status, Payment method that customer going to pay & order amount according to the order items. One order can have many items & one food item can be available in many orders. Items can be identify by Item Number, item name, Item Category & Price. Company facilitate the customers to know what the ingredients of the particular food item are. For that food items will contain ingredients as well, every food of the company will make with minimally with three ingredients. If the customer needs to know the ingredients of a particular food item, they can request while placing the order. The staff member who takes the order will able to provide the ingredients when it available in the system. When the order is dispatch from the outlet, the dispatched time should record in the database.

In FoodHub company all food orders will be deliver by Riders who are identify by Employee number, Employee name with First Name, Middle Name & Last Name, NIC, Date of Birth, Contact Number, License number, Address & Age in the system. The company keeps records about the dependents of the employees as well. But in the initial level of the project they are willing to keep only the dependents records of the riders.  Dependents will be identify by the dependent name, the relationship to the rider & Date of Birth. But they don’t have their own unique field, so the company expect a solution for this problem as well. So as the database administrator you have to provide the most appropriate solution according to the theories & techniques of the relational database.

Each rider will deliver the food items for the customers by using the company motor bikes

& they are not assign to a specific rider. When they attend to work according to their shift the available motor bike will be assign to the rider for that shift. The motor bikes will be identify by unique vehicle registration number, brand, model, engine number, vehicle registered date & the colours of the particular motorbike. None of the motor bike is coloured with a single colour, the company use different themes to colour the bikes. According to the database theories you have to provide the facility to identify the records of that which bike is used by which rider on a specific date. Also need to keep records of the motor bike meter readings when it assigned to the rider & when it return by the rider. 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 FoodHub Company scenario you will need to:

1. Use an appropriate design tool to design a relational database system for a substantial problema. Identify the user and system requirements of the FoodHub Company 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

2. Prepare a Design Document with the following designsa. 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.)
   
   b. Design Flow charts for 04 major processes of the given scenario. Those 04 major processes can be select from the same module of the system or different modules of the system according to the student’s preference.
   
   c. Design a Use Case Diagram for the given scenario with including all available processes of the system.d. According to the scenario design a class diagram with considering the basic requirements of the system. (No need to mention the access levels of the class attributes)

   e. Design a Data Flow Diagrams with Context Level & Level 01, to demonstrate the flow of data within the system. Use standard notations of Gane & Sarson model & each level should contain the correct components as well.

   f. Evaluate the effectiveness of the given designs (Each & every design should be evaluated) in terms of the identified user and system requirements.

(** Create a design document inside of the same submission document for the above a,b,c,d & e activities.)

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 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-designguide/[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]

Journal articles:

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

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

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

2003.

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

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

Kaur, Taranpreet & Sehra, Sumeet Kaur. (2015). Designing and Development of Database Testing Tool. International Journal of Computer Applications (0975 – 8887) Volume 120 –

No.19. 14. doi:10.5120/21334-4330.

Kraleva, Radoslava & Kralev, Velin & Sinyagina, Nina & Koprinkova-Hristova, Petia & Bocheva, Nadejda. (2018). Design and Analysis of a Relational Database for Behavioral Experiments Data 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:

zone/reading

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

libraries

HN Global (2021) Textbooks. Available at:

textbooks

Learning Outcomes and Assessment Criteria