Assessment 2: Database Design and Development for Celtic Caterers

This report details the development of a database system for Celtic Caterers, a catering business. The report begins with problem identification, highlighting the need for a computerized system to manage orders and other business functions. It then outlines the company's requirements, including the ability to manage orders, events, and customer information. The conceptual design includes entities such as Customer, Store Manager, Order, Staff, Payment, Product, Event, Supplier, and Ingredient, along with their attributes. An ER diagram is provided to visualize the relationships between these entities. The system was developed using MS Access, and various SQL queries are presented to handle tasks such as viewing food menus, order details, available dates, ingredient availability, supplier details, and sales data. Screenshots of the database tables are included, along with a test plan to ensure the system's functionality. The report concludes with lessons learned from the database design and development process and a summary of the benefits of the developed database.

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

their attributes and relationship between all the entities in an accurate manner [1]. Main entities
that together helped in development of proper and appropriate tables of database. In the below
ER diagram rectangular bracket helps in describing entities of database, ovel shape helps in
describing attributes of those entities, and diamond shape in ER diagram helps in explaining
relationship between those entities. There are eight main entities in below drawn Entity
relationship diagram.
Third Normal Form (3NF) - it refers to the database schema design approach in term of
relational databases which uses as normalizing principles to reduce the duplication of data or
information. It helps for designer to anomalies ensure referential integrity, and also simplify data
management. All the tables created in this database are in their third normal form.
5

System Development (MS – Access)
MS Access- Microsoft Access is a type of database management system and launched by
Microsoft that combine with various database engines with graphical user interface [3]. It is one
of the most suitable software in order to develop a commercial based software. That’s why,
Celtic Caterers business will use MS access for creating a database. There are various kind of
operations performed in step by step manner.
Queries and reports related to the case study
SQL- it stands for structure Query language, designed for managing large amount of data or
information in relational database management system. SQL is one of the most commonly used
domain specific language in term of programming. It is designed for managing data held in
database [4]. By using SQL, it can be performed the different kind of operations such as create,
delete, fetch rows, update and modify the rows etc. as per develop more understanding towards
SQL statements which means that acquire or gain more information, data. By using SQL
command, it is helping for designer to access information in easily.
Reports- Reports in MS access which become consider as great way to organise or present data
from access. It is merely present information but do not alter the underlying data in different DB
tables. Each time a report is opened, access display all recent data. Whenever creating a report in
which establish one or more relationship between tables.
On the basis of case study relevant tables were created and as per the needs and
requirement of viewing data following SQL queries were used to design required view or for
extracting required information or data.
Query 1: Customer should be able to view the food menu, the products and type of events offered
by the company.
6

Query 2: Kitchen staff should be able to view the order details (event details and the menu and
the products) ordered by customers.
Query 3: Customer should be able to view the available dates
7

Query 4: Kitchen staff should be able to view the availability of the ingredients.
Query 5: Store Manger should be able to view the supplier details and what type of ingredients
which they supply
8

Query 6: Store manager should be able to view the sales of last month.
Screenshots of Database Tables
Tables are becoming consider as database objects that contain all specific information. in
tables, data is stored or collected in logical manner. in order to organise in row and column
format similar to spreadsheet [5]. Each and every rows represents as unique record and each
column represents a field in record.
In this report, it has been created the different database tables such as customers, Suppliers,
Event, order, customer, product and staff. These are different tables created according to the
business scenario. in this way, it become easier for database developer to execute the different
operations in proper manner. Through database tables, consumer can easily accessible
information about the payment and also check the actual status of payment. In case, if any doubt
9

in regards of events product’s or food menus. Potential client will be accessible the database and
then find out accurate result or outcome.
 Customer table
 Event table
 Ingredients Table
 Order table
10

 Payment table
 Product table
 Staff table
11

 Store manager table
 Supplier table
Test Plan
Test Cases Expected results Actual results Test results
Test the
Dashboard
When the user wants
to navigate to a
relevant form by
clicking appropriate
As expected
12