Database Design and Development for Celtic Caterers

This report focuses on the database design and development process for Celtic Caterers, a successful catering business. It covers the conceptual design, ER diagram, system development using MS Access, queries and reports, and lessons learned. The report also discusses the company's requirements and problem identification.

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

buttons, the system
should refer user to
the relevant form.
Figure 01 shows that the
dashboard is fully functional
Creating required
queries
Customer should be
able to view the
available dates
As expected,
Figure 02 shows customers
can easily view availability of
dates.
Testing forms
created in MS
Access
When customer
wants to fill a form
and wants to provide
all of his main details
then he should be
able to fill his
information in the
form
Output as expected
Figure 03 shows that
customer can edit and fill
information in forms
Testing integrity
between two
tables
Admin should be
able to extract data
from two data and
for that it is
important to check
whether two tables
are integrated with
each other or not
As expected output
Figure 04 shows that all the
tables are integrated with
each other in an appropriate
and expected manner.
13

Lessons learned from databases design and development
Database designing and development has helped in learning while creating a database it
is extremely important to define relationship between all the entities on the basis of which tables
within database will be created. It further helps in understanding that fully integrated database
helps in extracting required information in an accurate manner with the help of SQL queries.
This database designing and development further helped me in understanding that there is a
provision in MS Access where user can create report of required queries and tables so that
required merged information can be easily viewed and analysed.
Conclusion
From the above report it has been concluded that database development helps a firm in
easily storing and organising their data or information within a single system in proper, adequate
and safe manner. It can further help an organization to easily record and store information
automatically. It has further been concluded that if a database is developed in an appropriate
manner without any kind of redundancy, then in such case chances of error creation are
extremely less.
14

References
Books and Journals
[1] Chen, H.L., Mao, H. and Chen, Q., 2018. Database development and Calphad calculations for
high entropy alloys: Challenges, strategies, and tips. Materials Chemistry and Physics. 210.
pp.279-290.
[2] Desselle, S.P. and et.al., 2021. Design fundamentals of mentoring programs for pharmacy
professionals (Part 1): considerations for organizations. Research in Social and Administrative
Pharmacy. 17(2). pp.441-448.
[3] Fachada, N., 2018. Teaching database concepts to video game design and development
students.
[4] Gorskis, H., 2018, October. SQL query construction from database concepts. In 2018 59th
International Scientific Conference on Information Technology and Management Science of Riga
Technical University (ITMS) (pp. 1-4). IEEE.
[5] Vyas, M., 2020. Index design for information retrieval applications using database
concepts (Doctoral dissertation).
15

SQL Statement-
Query-1
SELECT Customer.customer_name, Event.Food_menu, Event.event_type
FROM Event INNER JOIN (Customer INNER JOIN Product ON Customer.product_id =
Product.product_id) ON Event.event_id = Customer.event_id;
Query-2
SELECT Staff.Staff_name, order.order_id, Event.event_date, Event.event_time,
Event.event_type, Event.Food_menu, Customer.customer_id
FROM (Product INNER JOIN ([order] INNER JOIN (Event INNER JOIN Customer ON
Event.event_id = Customer.event_id) ON order.order_id = Customer.order_id) ON
(Customer.product_id = Product.product_id) AND (Product.product_id = order.product_id))
INNER JOIN Staff ON Product.product_id = Staff.product_id;
Query-3
SELECT Customer.customer_id, Customer.customer_name, Event.event_date, Event.event_time
FROM Event INNER JOIN Customer ON Event.event_id = Customer.event_id;
Query-4
SELECT ingredients.ingredient_Id, ingredients.ingredients_name,
ingredients.ingredients_quantity
FROM ingredients;
16

Query-5
SELECT Supplier.supplier_id, Supplier.supplier_name, Supplier.supplier_PhoneNo,
Supplier.supplier_email, ingredients.ingredients_name
FROM ingredients INNER JOIN Supplier ON ingredients.ingredient_Id =
Supplier.ingredient_id;
Query-6
SELECT Count(order.order_id) AS ['total orders'], Sum(Payment.payment_amount) AS ['sales
of last month']
FROM Payment INNER JOIN [order] ON Payment.payment_id = order.Payment_id;
17