Database Design and Development for Celtic Caterers

Learning in the Digital Era Assessment 2
1

Contents
Introduction......................................................................................................................................3
Problem Identification.....................................................................................................................3
Company requirements....................................................................................................................3
Conceptual design for the given case study.....................................................................................4
ER Diagram.....................................................................................................................................4
System Development (MS – Access)..............................................................................................5
Queries and reports related to the case study...................................................................................6
Screenshots of Database Tables.......................................................................................................9
Test Plan........................................................................................................................................12
Lessons learned from databases design and development.............................................................14
Conclusion.....................................................................................................................................14
References......................................................................................................................................15
2

Introduction
A database is based on the collection of data or information that is mainly organised in proper
manner. It is becoming easier for database designer to accessed, managed and update
information [2]. Usually. Computer database typically contain an aggregation of data records,
files and containing information about the store manager, order, product and payment with
specific customers.
In this report, it is mainly focused on the “Celtic Caterers” as one of the successful catering
business. It provides the various kind of catering services to local areas or place. This report will
produce Entity relationship diagram in order to identify the suitable entities and their attributes.
Moreover, it will develop a database system for “Celtic Caterers” and handle the various kind of
tasks.
Problem Identification
Celtic Caterers is mainly located in the Wales which provides the catering services for local
community on the basis of occasion. In order to make suitable plan and expand services in other
cities. In recently, enterprise do not have computerised system to manage various kind of
business functions. Generally, company has been used the manual-paper based system to handle
orders, other operations. In this way, it has been identified the certain kind of issues as they are
getting lots of order cancels, struggle to handle the overall business complexity. In this way,
company has been decided to design or develop online based database and provide the better
solution.
Company requirements
There are different kind of requirements for organisation as follows:-
 Store manager in organisation is focused on taking order from different consumers.
 At the time of conversation, manager of Celtic caterers will explain each and every thing
about events, product and available dates, price for the client.
3

 In case, if consumer agrees, store manager will identify the certain issues an invoice to
the client, based on the price. In this way, potential client will pay full amount and book
for particular events.
 Manager will pass all orders details to the kitchen staff members and then it will prepare
everything for events.
Conceptual design for the given case study
In the given conceptual design of given case study all the entities and attributes of
database will be explained in detail. Following are main entities and their attributes of database:
 Customer (Customer_id (primary key), Customer_name, Customer_PhoneNo,
Customer_email, Customer_type, Payment_id, order_id, event_id)
 Trore Manager (Manager_id (primary key), Manager_name, Manager_contactNo, Order_id,
Staff_id, payment_id, Supplier_id)
 Order (Order_id (primary key), Order_date, Order_time, order_type, Product_id)
 Staff (Staff_id (primary key), Staff_name, Staff_PhoneNo, Staff_email, Staff_designation,
Product_id)
 Payment (Payment_id (primary key), Payment_status, Payment_amount, Payment_date)
 Product (Product_id (primary key), product_name, Product_quantity, product_price,
product_ingridients)
 Event (Event_id (primary key), event_date, event_time, event_type)
 Supplier (Supplier_id (primary key), supplier_name, supplier_PhoneNo, Supplier_email,
product_id, Ingredient_id)
 Ingredient (Ingredient_ id (primary key), Ingredient_name. Ingredient_quantiy)
ER Diagram
ER diagram is also known as Entity relationship diagram that helps in describing overall
structure of database with the help of a diagram. It further helps in understanding main entities,
4