BTEC A1 Unit 04: Database Design & Development Assignment for FoodHub Company Sep24-25

Assignment Brief

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, 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-off Recognise and analyse criteria and specifications appropriate to specific problems, and plan strategies for their solutions
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 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:

1. Intellectual skills: critical thinking; making a case; numeracy and literacy
2. Self-management: self-awareness and reflection; goal setting and action planning
3. Independence and adaptability; acting on initiative; innovation and creativity
4. 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 located in the United States of America. It was started 14 years ago, and 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 and consistency throughout the data life cycle.

FoodHub Company only delivers food to the customers and does not offer or sell food in the outlets. For delivery, they have separate staff, who are known as Riders. There are other types of employees as well, but their details are not recorded in the database in the initial level. In the initial phase, the company plans to keep records of food deliveries and related information only. If the system succeeds, they will enhance it for Human Resource Management and Finance Management integration with the initial Delivery Management system.

In this system, the Customer will be identified by a unique Customer ID, Customer Name, NIC, Date of Birth, Contact Number, and Address with Location No., Lane, Street, and City. The customer’s address will be considered as the order delivery address. One customer can place many orders, but one order will have only one customer. The order can be placed over the phone, and it will be entered by a staff member of the company. Orders will be identified by unique Order No, Order Date, Order Time, Order Status, Payment Method, and Order Amount according to the order items. One order can have many items, and one food item can be available in many orders. Items will be identified by Item Number, Item Name, Item Category, and Price. The company facilitates customers to know the ingredients of a particular food item. For that, food items will contain ingredients as well, and every food of the company will be made with at least three ingredients. If the customer needs to know the ingredients of a particular food item, they can request it while placing the order. The staff member who takes the order will be able to provide the ingredients when it is available in the system. When the order is dispatched from the outlet, the dispatched time will be recorded in the database.

In FoodHub Company, all food orders will be delivered by Riders, who are identified by Employee Number, Employee Name (First Name, Middle Name, and Last Name), NIC, Date of Birth, Contact Number, License Number, Address, and Age in the system. The company keeps records about the dependents of the employees as well. However, in the initial phase of the project, they are willing to keep only the dependents’ records of the riders. Dependents will be identified by the dependent’s name, relationship to the rider, and Date of Birth. But they don’t have their own unique field, so the company expects a solution for this problem. As the database administrator, you must provide the most appropriate solution according to the theories and techniques of relational databases.

Each rider will deliver food items for the customers by using the company motorbikes, and they are not assigned to a specific rider. When they attend work according to their shift, the available motorbike will be assigned to the rider for that shift. The motorbikes will be identified by a unique vehicle registration number, brand, model, engine number, vehicle registration date, and the colors of the particular motorbike. None of the motorbikes is colored with a single color; the company uses different themes to color the bikes. According to the database theories, you must provide the facility to identify the records of which bike is used by which rider on a specific date. Additionally, you need to keep records of the motorbike meter readings when it is assigned to the rider and when it is returned by the rider.

In the system development level, the company doesn’t need the report generation part initially; it will be included in the next level of development. However, the Database designs and system designs should include the reporting processes.

Assignment Activity and Guidance

Based on the FoodHub Company scenario, you will need to:

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

 Identify the user and system requirements of the FoodHub Company System.

According to the above requirements that are identified, design a database and design a relational database system using conceptual design (ER Model) including identifiers (primary key) of entities and attributes, cardinalities, and participations of relationships

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.

Check whether the provided logical design is normalized. 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 designs

1.  Design a 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.)
2. Design Flow charts for 04 major processes of the given scenario. Those 04 major processes can be selected from the same module of the system or different modules of the system according to the student’s preference.
3.  Design a Use Case Diagram for the given scenario including all available processes of the system.
    According to the scenario, design a class diagram considering the basic requirements of the system. (No need to mention the access levels of the class attributes.)
4.  Design Data Flow Diagrams with Context Level & Level 01, to demonstrate the flow of data within the system. Use standard notations of Gane & Sarson model, and each level should contain the correct components as well.
5.  Evaluate the effectiveness of the given designs (Each & every design should be evaluated) in terms of the identified user and system requirements.

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:

• Microsoft (2022) Database design basics [online] Available at: https://support.microsoft.com/en-us/office/database-design-basics-eb2159cf-1e30-401a8084-bd4f9c9ca1f5 [Accessed 1 August 2022]
• Guru99 (2022) Database Design in DBMS Tutorial: Learn Data Modeling [online] Available at: https://www.guru99.com/database-design.html [Accessed 1 August 2022]
• 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]
• Lucidchart (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/0020-7373(92)90092-Y.
• Gunjal, B. (2003). Database System: Concepts and Design. Proceedings of 24th IASLIC–SIG2003.
• 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 (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