Fast Food Hub Database Report (Lab Report Sample)

Fast Food Hub Database Design ReportBy {student name}InstitutionDepartmentCourse code and nameLecturerSubmission Date Contents TOC \o "1-3" \h \z \u Introduction PAGEREF _Toc89216728 \h 3Problem Identification/Problem statement PAGEREF _Toc89216729 \h 3Company Requirements PAGEREF _Toc89216730 \h 3Conceptual Design for the given case study PAGEREF _Toc89216731 \h 4Entities and Attributes PAGEREF _Toc89216732 \h 4Degree of Relationships PAGEREF _Toc89216733 \h 5Annotated descriptions for each relationship PAGEREF _Toc89216734 \h 5Entity-Relationship Diagram PAGEREF _Toc89216735 \h 6Logical Design PAGEREF _Toc89216736 \h 7System Development using MS-Access PAGEREF _Toc89216737 \h 8Relationships in MS-Access PAGEREF _Toc89216738 \h 8Created Queries and Reports related to the case study PAGEREF _Toc89216739 \h 9Total Sales Query PAGEREF _Toc89216740 \h 9Total purchases query PAGEREF _Toc89216741 \h 11Total sales per client query PAGEREF _Toc89216742 \h 13Query total expenses PAGEREF _Toc89216743 \h 14Edgbaston menu query PAGEREF _Toc89216744 \h 15Aston menu query PAGEREF _Toc89216745 \h 17Reports PAGEREF _Toc89216746 \h 19Purchase orders report PAGEREF _Toc89216747 \h 19Edgbaston menu report PAGEREF _Toc89216748 \h 20Birmingham menu report PAGEREF _Toc89216749 \h 21Aston Menu report PAGEREF _Toc89216750 \h 22Official Receipt PAGEREF _Toc89216751 \h 23Client sales report PAGEREF _Toc89216752 \h 23Total sales report PAGEREF _Toc89216753 \h 24Screenshots of tables PAGEREF _Toc89216754 \h 24Suppliers table PAGEREF _Toc89216755 \h 24Store branches table PAGEREF _Toc89216756 \h 25Stock usage table PAGEREF _Toc89216757 \h 26Stock table PAGEREF _Toc89216758 \h 27Staff table PAGEREF _Toc89216759 \h 28Ordering table PAGEREF _Toc89216760 \h 29Purchase table PAGEREF _Toc89216761 \h 29Purchases table PAGEREF _Toc89216762 \h 30Payment table PAGEREF _Toc89216763 \h 31Payment means table PAGEREF _Toc89216764 \h 31Ordering Table PAGEREF _Toc89216765 \h 32Clients table PAGEREF _Toc89216766 \h 35Dashboard (Menu) Forms PAGEREF _Toc89216767 \h 36Stock recording form PAGEREF _Toc89216768 \h 36Ordering form PAGEREF _Toc89216769 \h 37Stock purchasing form PAGEREF _Toc89216770 \h 38Menu entry form PAGEREF _Toc89216771 \h 39Branches/ staff registration form PAGEREF _Toc89216772 \h 40Test Plan PAGEREF _Toc89216773 \h 41Lessons learned from database design and development PAGEREF _Toc89216774 \h 41Conclusion PAGEREF _Toc89216775 \h 41References PAGEREF _Toc89216776 \h 42 Introduction Proper management of business data and information is paramount [1]. This has been in existence since time immemorial, with the earliest forms of databases being manuscripts and scrolls. The most recent forms of manual databases, which some businesses and individuals still use is the filing system. The filing system is not only disadvantageous because of the long hours needed to retrieve information, but also ensures less security for data. Database management systems, came in handy to solve the issues that the manual system face. The main uses of DBMS include business automation as well as secure storage of records, which at the same time ensures availability and faster retrieval [1] [2]. Fast Food Hub, implemented a database management system suing Microsoft Access database, which is a very powerful relational database. Problem Identification/Problem statement The rapid growth of Fast Food Hub to include two more branches in Aston and Edgbaston is the main reason why the enterprise is seeking to advance its database system. The current database is largely manual; records are manually recorded on books of accounts and receipts written manually. The manual processes are very slow and highly unreliable, hence making business operations difficult and sometimes costly. To avoid losing data, there is need to have in place a computer-based database system, where all records done at the three stores are recorded individually and stored centrally. This will ensure the business carry out their activities faster and securely. Company Requirements The main requirement put forward by Fast Food Hub is to develop a database system that would automate customer ordering processes, inquiries, sales recording payment module as well as ingredient and other stock purchase recordings. In addition to these automated processes, the system should also be able to provide reports such as invoices, receipts, monthly financial reports, branch based expenditure and revenue reports among other vital information relevant for effective decision-making processes. Conceptual Design for the given case study Entities and Attributes * tbl_client ( client_id, manager_id, client_name, client_city, client_address, client_tel_no client_email) * tbl_order_details (order_id, menu_item_id, item_quantity, order_date, branch_id) * tbl_delivery_service (del_service_id, order_id, client_id, manager_id, del_date, del_details) * tbl_menu_categories (menu_category_id, menu_category_name) * tbl_order_details (order_details_id, order_id, customer_id, order_date) * tbl_payment (receipt_id, order_id, payment_date, paid_amount) * tbl_purchase_orders (purchase_id, supplier_id, material_id, p_qty, pur_date, store_id) * tbl_Purchasetransactions (expenditure_id, PO_no, exp_date, exp_amount, form_payment) * tbl_staff (staff_no, staff_name, gender, staff_contact, staff_email, store_id) * tbl_stock (stock_id, stock_name, stoct_type, stock_expiry_date, branch-store, unit_price) * tbl_store (store_id, Store_name, Store_address, Store_city) * tbl_supplier (supplier_id, Supplier_name, Supplier_address, Supplier_contact, Supplier_location) * tbl_menu (menu_id, menu_item, menu_cat, Item_Price, branch_id) * tbl_ordering (order_id, customer_id, order_date) Degree of Relationships Annotated descriptions for each relationship Relationship between tables in databases is a necessity to ensure that table communicate [3]. By creating tables complete with primary keys and foreign keys, one is ensuring that the tables will be able to link and communicate. Fast Food Hub database has entirely One-to-Many type of relationship. There are three types of relationships. This depends on the fields allowed to link up together between the relating tables. The relationship types include * One-to-Many or Many-to-One: In this type of relationship, it means that a record in one table can have several instances in a different table. * One-to-One: the relationship means that one record can have exactly one related record on the other table. * Many-to-Many: relationship where both table can have several instances of same record. Entity-Relationship Diagram Logical Design 74903034345400 Menu_category_id category_name 3599233520330024308754476100 Menu_ID Menu_item cat_id Unit_Price Branch_id 2071721182745 33074033468600 order_id customer_id menu_id quantity order_date branch_id user_id 201295034371 payment_id order_id p_amount form_pay p_date System Development using MS-Access Relationships in MS-Access Created Queries and Reports related to the case study Total Sales Query Design View Query datasheet view Total purchases query Query Design view Query datasheet view Total sales per client query Query design view Query datasheet view Query total expenses Summary purchases Query Query datasheet view Edgbaston menu query Query design view Query datasheet view Aston menu query Design view ...