SIT103 Database Fundamentals Case Study Sample

SIT103 Database Fundamentals Case Study

Assignment Brief

Case scenario

A new local pizza store, called Pizza Palace,wants to keep track of its front inventory and customer orders. You and your team were asked to consider the front logistics of the business. Then, design a new data solution that is capable of tracking customer orders made online or directly in the store.

On the outset, your team can immediately identify a few key entities: Pizza, Inventory and Sales. Your team will need to identify and fill in the related attributes as well in the analysis stage. For example, Pizza { pizza_ID, pizza_name, category, price }

The database needs to be capable of tracking data about the product availability, the staff, the sales, sales items, and other related information. Your team must evaluate and propose at least another five entities to track (not mentioned in the scenario) to ensure the smooth running of the pizza store.
Pizza Palace staff comprises of a crew of ten and they are all paid the hourly wage. Some available positions include the manager, chef, kitchen helper, delivery driver and general crew member. The employee data that should be tracked include staff ID, first name and last name, date of birth, email, contact number, position, and starting date.

If any customers want to order via the website or mobile app, they must complete an online registration. The customer information stored should include the name, e-mail, contact number, password, security question, date of registration (default is the current date), account status (default is ACTIVE), and a customer ID, which is uniquely generated and assigned when the account is first created. The customer can opt for paperless invoice for online orders. Whenever a customer completed an order, a sales order is created. When the order is paid, an invoice is generated and issued. If the customer has opted for paperless, the invoice will be emailed to the customer.

What you need to do

Q1 • Analysis

Analyse and identify from the case scenario:

I. all key stakeholders (entities) and their attributes.

II. all business rules that define the relationship(s) between them.

III. all business constraints/validation lists that the data model may include.

Requirement: You must use ONLY the prescribed set notations in your analysis

Q2 • Design

Based on your completed analysis (Q1), design an E-R diagram to represent a data model of the case scenario.

Components to consider and include:

• Entities and attributes

• Primary keys

• Foreign keys

• Cardinalities

• Consistency and completeness of the diagram using only one of the suggested tools.

Requirement: Use Crow’s foot notation ONLY.

Solutions

Introduction

The pizza company, ‘Pizza Palace’, needs an investigation of internal inventory and the customer's orders. This examination process involves the way to find out the fundamental entities, and their attributes. Relational mapping highlights the relationship between multiple entities. The investigation of the system assists to understand the drawback of the business and provides suitable explanations for mitigating those issues.

Q1: Analysis

All key stakeholders (entities) and their attributes

Table 1: Entities, and attributes
(Source: Created own)

The entities and their attributes. In the above people, it has been found that for the customers There are several attributes such as the ID of the “customer name, email id, phone number, password, security question, date of joining, and account status' '. The other entity is staff in which there are several attributes such as “staff ID, staff first name, last name, date of birth, and staff email ID”. However, the pizza store also has attributes such as “store ID, store name, location, staff ID, staff number, and pizza id”. The pizza also has different attributes such as “pizza ID, pizza name, category, price, and platform id”. The booking platform has different attributes such as “platform ID, customer ID, and platform type”, whereas the order has different attributes such as “order ID, pizza ID, pizza name, quantity, and amount” (Orobia et al., 2020). Payment has different attributes such as “payment ID, order ID, amount, and payment date, whereas invoice has also different IDs such as “invoice ID, payment ID, invoice number, customer name, pizza name, total amount, invoice date, and staff ID”.

Customer { cust_id, cust_name, cust_email, cust_phone, cust_password, security_question, DOG, account_status }
Staff { staff_id, staff_first_name, staff_last_name, DOB, staff_email, staff_phone, staff_role }
Pizza Store { store_id, store_name, location, staff_id, staff_number, pizza_id }
Pizza { pizza_id, pizza_name, category, price, platform_id }
Booking Platform { platform_id, cust_id, platform_type }
Order { order_id, pizza_id, pizza_name, quantity, amount }
Payment { payment_id, order_id, amount, payment_date, payment_type, card_type }
Invoice { invoice_id, payment_id, invoice_number, customer_name, pizza_name, total_anount, invoice_date, staff_id }

It has been identified that the demonstration of different attributes and their entities are shown in the above section that contains the information about customer staff pizza store pizza booking platform order payment and invoice (Pantoja et al., 2023).

Relation

Customer:Booking Platform (1:1)

Booking Platform:Pizza (1:M)

Pizza:Order (M:1)

Order:Payment (1:1)

Payment:Invoice (1:1)

Staff:Pizza Store (M:1)

Staff:Invoice (M:M)

Pizza Store:Pizza (1:M)

Has been found that the booking platform and the customer have a one-to-one relationship whereas the booking platform and pizza have a one-to-many relationship. The pizza and order have a many-to-one relationship whereas order and payment have one-to-one (Song et al., 2020). The staff and invoice have a many-to-many relationship, and the staff versus the pizza store has a many-to-one relationship. On the other hand, pizza stores and pizza have a one-to-many relationship.

All business rules that define the relationship(s) between them

Figure 1: Business rule
(Source: Created in draw.io)

The above figure has demonstrated the rule of the business in which at first the customer is required to log in followed by selecting pizza and ordering a pizza. After the customer orders pizza, it goes to the order-taking section where the order gets confirmed. In the pizza store section there are three sections, i. Order taking (staff), ii. Pizza chef, and iii. Delivery guy. The order-taking section is used to take the order from the customer and communicate with the customer for delivery (Schuster et al., 2019). It is also used to solve delivery and order-related queries. The confirmed order is then processed to the pizza-making section which is connected with the delivery section. The pizza is delivered with the help of a delivery boy. After giving the pizza the delivery boy takes payment, and provides a receipt to the customer. In this way, the order sections of the pizza store are completed. On the other hand, after the completion of the payment, the requirement of the customer is fulfilled for MBA assignment expert.

All business constraints/validation lists that the data model may include

Table 2: Business construction/validation lists
(Source: Created own)

The table demonstrates the business construction/validation lists which are the important factors of the pizza store.

Q2: Design

The above diagram has been found to be demonstrating different tables such as customer booking platform pizza store staff pizza order invoices and payment. The customer and booking platform have a one-to-one relationship whereas looking platform and pizza have a one-to-many relationship. The pizza table and order table have a many-to-one relationship whereas order and payment have one to one relationship (Azzahra & Anggoro, 2022). The payment and invoice have a one-to-one relationship whereas invoice and staff have many-to-many relationships whereas the pizza store and staff have one-to-many relationships.

Conclusion

It can be concluded that the business company is going to be started for the purpose of meeting all the needs and requirements of the customers. The examination process has been found to be involving the fundamental entries and their attributes. It can also be concluded that all the tables and entity relationship diagrams have been shown along with demonstrating the “Crow’s foot notations”. The inventory management process is implemented to check the checklist on a daily basis. The business rules help to understand the flow of the pizza ordering process. Invoice investigation helps to understand the sales part of the company. The profit of the company depends on the order given by the customers. 

References

Orobia, L. A., Nakibuuka, J., Bananuka, J., & Akisimire, R. (2020). Inventory management, managerial competence and financial performance of small businesses. Journal of Accounting in Emerging Economies, 10(3), 379-398. Retrieve on: 23rd July 2023, from: https://nru.uncst.go.ug/xmlui/bitstream/handle/123456789/5211/Inventory%20management%2C.pdf?sequence=1&isAllowed=y

Song, J. S., Van Houtum, G. J., & Van Mieghem, J. A. (2020). Capacity and inventory management: Review, trends, and projections. Manufacturing & Service Operations Management, 22(1), 36-46. Retrieve on: 23rd July 2023, from: https://pubsonline.informs.org/doi/pdf/10.1287/msom.2019.0798

Schuster, B. D., Ferry, M., Fulbright, J., Jellison, D. C., Basu, K., & Gable, S. M. (2019). U.S. Patent No. 10,229,438. Washington, DC: U.S. Patent and Trademark Office. Retrieve on: 23rd July 2023, from: https://patentimages.storage.googleapis.com/7e/b9/0a/8d423601acb30b/US10229438.pdf

Azzahra, Z. F., & Anggoro, A. D. (2022). Analisis Teknik Entity-Relationship Diagram dalam Perancangan Database Sebuah Literature Review. INTECH (Informatika dan Teknologi), 3(1), 8-11. Retrieve on: 23rd July 2023, from: https://www.journal.unbara.ac.id/index.php/INTECH/article/download/1261/831

Pantoja, C. E., da Silva Alexandre, T., Viterbo, J., & Guinelli, J. V. (2023). A Model-Driven Development Framework for Geographical and Relational Databases Systems. Retrieve on: 23rd July 2023, from: https://www.researchgate.net/profile/Carlos-Pantoja-3/publication/371988010_A_Model-Driven_Development_Framework_for_Geographical_and_Relational_Databases_Systems/links/64a1f89fb9ed6874a5ee839d/A-Model-Driven-Development-Framework-for-Geographical-and-Relational-Databases-Systems.pdf