UNIVERSITI MALAYSIA SARAWAK

Faculty of Computer Science and Information Technology

Project Cover Sheet

Student Name (lecture group: G06) Student ID Number Group Signature

Course Code: TMF2034 Course Name: DATABASE CONCEPT AND DESIGN

Project Title: Project Lecturer: DR. LIEW SIAW HONG

Due Date: 16 January 2023, 12pm Date Submitted: 16 January 2023

This cover sheet must be completed, signed and firmly attached to the front of the submission. All work must be
submitted by the due date. If an extension of work is granted, an extension acknowledgement slip must be
signed by the lecturer/tutor and attached to the assignment/project/report. Please note that it is your
responsibility to retain copies of your assignment.

Plagiarism and Collusion are methods of cheating that are addressed in the Peraturan Akademik Universiti
Malaysia Sarawak para 11: Etika Akademik

Plagiarism
Plagiarism is the presentation of work which has been copied in whole or in part from another person’s work, or fro m any
other source such as the Internet, published books or periodicals without due acknowledgement given in the text.

Collusion
Collusion is the presentation of work that is the result in whole or in part of unauthorized collaboration with another person or
persons.

Where there are reasonable grounds for believing that cheating has occurred, the only action that may be taken when
plagiarism or collusion is detected is for the staff member not to mark the item of work and to report or refer the matter to
the Dean. This may result in work being disallowed and given a fail grade or if the circumstances warrant, the matter may
be referred to a Committee of inquiry for investigation. Such investigation may result in the matter being referred to the
University Discipline Committee, which has the power to exclude a student.

Upon placing signature above, I certify that I have not plagiarized the work of others or participated
in unauthorized collusion when preparing this assignment.

I also certify that I have taken proper case in safeguarding my work and have made all reasonable
efforts to ensure that my work is not able to be copied.

MARK :

1
Table of Contents

Finalized ERD .............................................................................................................. 3

Command line interface (functions and data) ........................................................ 4

Creation of Simple Database and Tables ....................................................................4

Add, Update, Delete and View Menu Items ................................................................6

Create, Update, and Delete Orders ..............................................................................9

Generate Reports ....................................................................................................... 12

All Customers List.......................................................................................................... 12

Simple Reports................................................................................................................ 14

2
Finalized ERD

The figure below shows the Entity Relationship Diagram (ERD) based on Assignment 2.

Figure 1. Finalized Entity Relationship Diagram

3
Command line interface (functions and data)

Creation of Simple Database and Tables

The database ‘db_tmb’ (The Midnight Baker MY’s database) and 4 tables ‘customers’,
‘items’, ‘bills’, and ‘purchases’ within the database were created. The figures below show
the screenshots of the functions that were involved in creating the database and tables.

Figure 2. Database 'db_tmb' is created

Figure 3. Table 'items' is created

4
 Figure 4. Table 'bills' is created

Figure 5. Table ‘purchases’ is created

The figures below show the usage of ‘ALTER TABLE’ function to add foreign keys and
primary keys.

Figure 6. Addition of ‘bill_no’ as foreign key

5
 Figure 7. Addition of 'item_id' as foreign keys

Figure 8. Setting 'bill_no' and 'item_id' as primary keys under table 'purchases'

Add, Update, Delete and View Menu Items

The figures below show the usage of ‘ADD’, ‘UPDATE’, ‘DELETE’, ‘VIEW’ in the tables
created. The function ‘INSERT INTO’ is used to add new records.

1. Table ‘Items’:

Figure 9. Adding and deleting values to 'Items' table

6
 Figure 10. Addition of more values for 'Items' table

Figure 11. Usage of 'UPDATE' function to update the item’s name

7
2. Table ‘Customers’:

Figure 12. Addition of values to 'Customers' table

Figure 13. Usage of 'UPDATE' to update customer’s address

Figure 14. Addition of a trial demo (‘INSERT’ and ‘DELETE’ functions)

8
Create, Update, and Delete Orders

The figures below show the usage of ‘CREATE’, ‘UPDATE’, and ‘DELETE’ in the tables
created.

3. Table ‘Bills’:

Figure 15. Creating new data entry for ‘Bills’ table

Figure 16. Usage of 'UPDATE' and 'DELETE'

9
4. Table ‘Purchases’:

Figure 17. Creating new data entry for ‘Purchases’ table

Figure 18. Display of values in ‘Purchases’ table

10
Figures below show the usage of ‘UPDATE’ function to update the ‘quantity’.

Figure 19. Shows the initial value for bill_no = B090

Figure 20. Usage of 'UPDATE' function

11
Generate Reports

All Customers List

Figure 21. List of TMB’s customers

All Bill Orders

Figure 22. List of bills with their respective items and quantity ordered

12
 Figure 23. List of bills with the respective customers, delivery details and pay status

All Menu Items

Figure 24. List of TMB’s menu items

13
Simple Reports

i. Monthly Sales Order List for October

Figure 25. Monthly Sales Order List (Paid)

ii. List of Customers’ Names and their Areas

Figure 26. Customers' Names and Areas with their respective Orders Made

14
iii. Most-Ordered Desserts / Dessert Combo

Figure 27. Most-Ordered Desserts based on Item_ID

iv. Ranking of Highest Paying Customers

Figure 28. Ranking of Highest Paying Customers (filtered by Paid bills)

15