IT417: Relational Database Design & Implementation for Gary Café

This project outlines the design and implementation of a database management system for Gary Café, a restaurant located in Singapore's National Library. The project includes an analysis of current business activities and operations such as collecting orders, managing raw materials, and maintaining storage. It features an initial ER model, a detailed design specification covering entity, attribute, relationship, and constraint specifications, and business rules. The database design undergoes normalization to ensure data integrity and efficiency. The final entity-relationship model is presented, followed by a critical appraisal of the project's success in meeting its objectives. The system aims to efficiently store and process business data for Gary Café, integrating the proposed database into the system's core.

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System
Name of the System
Name of the University

1DATABASE MANAGEMENT SYSTEM
Table of Contents
1. Current Business Activities:........................................................................................................2
2. Description of Operations:..........................................................................................................2
3. Initial ER Model:.........................................................................................................................3
4. Design Specification:...................................................................................................................3
4.1 Entity Specification:..............................................................................................................3
4.2 Attributes Specification:........................................................................................................6
4.3 Relationship Specification:........................................................................................................8
4.4 Constraints Specification:..........................................................................................................9
5. Business Rules:............................................................................................................................9
6. Normalization:...........................................................................................................................10
7. Entity Relationship Model:........................................................................................................11
8. Critical Appraisal:......................................................................................................................12
Bibliography:.................................................................................................................................13

2DATABASE MANAGEMENT SYSTEM
1. Current Business Activities:
Gary Café is located at National Library in Singapore. This was opened in early of 2014.
The Café server various consumers each day. A person takes orders from the customers. After
the food or café is complete, the order is served to the customer. The organization collects the
information of the consumers for future use. The staff of the organization work in specific days
of a week. A person maintain the information regarding the attendance of staff. The cashier takes
the payment from the customers against their orders. The report generation process is a crucial
activity in the current business process.
2. Description of Operations:
There are mainly three operations at Gary Café. These operations are described in the
following section.
i. Collecting Order: In this operation, the waiter collect orders from the customers.
The customers can order for a single person or a group. The orders can have one
or multiple menu items. The Café will store all the orders against a single person.
ii. Collecting Raw Material: The café will buy raw materials from local farms. The
contact numbers and address of the local farm is an important data to have. The
café will have the relevant information of the raw materials collected.
iii. Maintaining Storage: Maintaining the storage of the Café is a vital operation.
The café will determine how much raw material is required for single day. The
Café will order new stock of raw materials if it is not present in required quantity.

3DATABASE MANAGEMENT SYSTEM
3. Initial ER Model:
Figure 1: The Initial ER Model of Gary Café
(Source: Created by Author)
4. Design Specification:
4.1 Entity Specification:
Owner:

4DATABASE MANAGEMENT SYSTEM
Restaurant:
Manager:
Customer:

5DATABASE MANAGEMENT SYSTEM
Payment:
Invoice:
Menu:
Food_Material:

6DATABASE MANAGEMENT SYSTEM
Raw_Material:
Supplier:
4.2 Attributes Specification:
Table Name: Restaurant
Attribute Specification
Rest_name The name of the restaurant
Location The address of the restaurant
R_Contact The contact number of the restaurant
Opening_Closing_Time The opening and closing time of restaurant
Details Description of the restaurant

7DATABASE MANAGEMENT SYSTEM
Table Name: Supplier
Attribute Specification
S_Name The name of the Supplier
S_Address The address of the Supplier
S_Contact The contact number of the Supplier
S_Details The description of the Supplier
Rest_Name The name of the restaurant
Table Name: Menu
Attribute Specification
Name The name of the menu item
Price The cost of the menu item
Category In which category the menu item reside
Ingredients Ingredients used in creating menu item
Reciepe The recipe

8DATABASE MANAGEMENT SYSTEM
4.3 Relationship Specification:
Relationship Specification
Attribute Name Attribute Name Relationship
Owner Restaurant Many-to-One Many people can be owner of a
restaurant
Restaurant Supplier One-to-Many One restaurant can order from many
suppliers
Supplier Raw_Material One-to-Many One supplier can sell various raw
ingredients
Raw_Material Food_Material One-to-many One raw food can be used in making
many menu items
Food_Material Menu Many-to-One Many raw materials can used in one
menu item
Menu Invoice Many-to-One Many menu item can be in one
invoice
Invoice Payment One-to-One One invoice will be generated by one
payment
Payment Custmer Many-to-One Many payment can be done by one
customer
Restaurant Manager One-to-One One restaurant has one manager

9DATABASE MANAGEMENT SYSTEM
4.4 Constraints Specification:
Constraints Specification
Primary Foreign Table
Rest_name Restaurant Uniquely identify each
restaurant
S_Name Supplier Uniquely identify each
supplier
Rest_Name Supplier The foreign key of
Restaurant table primary
key
Material_ID Raw_Material Uniquely identify each Raw
material
S_Name Raw_Material The foreign key of Supplier
table primary key
Cust_ID Custmer Uniquely identify each
customer

10DATABASE MANAGEMENT SYSTEM
5. Business Rules:
The business rules of the database are as following.
i. Restaurant---has---owners
ii. Restaurant----buys---from supplier
iii. Supplier---sell---Raw_Material
iv. Raw_Material---linked to--- Food_Material
v. Food_Material---used in--- Menu
vi. Invoice>>>has>>>Menu
vii. Invoice---generated through---Payment
viii. Custmer---make---Payment
ix. Restaurant---has---Manager
6. Normalization:
1st Normal Form: In case of first normal form, every attribute must be atomic and each
entity should have primary key. The raw material foreign key on Menu table is a multivalued
attribute. In order to make the table, a bridge table is created with menu and raw_material
primary key as foreign key.
2nd and 3rd Normal Form: The database attributes does not have either partial
dependency or transitive dependency. Hence, the tables are in 2nd and 3rd normal form.

11DATABASE MANAGEMENT SYSTEM
7. Entity Relationship Model:
Figure 2: The Entity Relational Model of Gary Café
(Source: Created by Author)

12DATABASE MANAGEMENT SYSTEM
8. Critical Appraisal:
The project has successfully met the objectives. The database design is able to provide
data solutions to Gary Café business process. The organization can store and process the
business data efficiently and effectively. The business can develop a system that will integrate
the proposed database into system’s core. In order to create the database, the idea of database
constraints and attributes of the database.

13DATABASE MANAGEMENT SYSTEM
Bibliography:
Arulraj, J. and Pavlo, A., 2017, May. How to build a non-volatile memory database management
system. In Proceedings of the 2017 ACM International Conference on Management of Data (pp.
1753-1758). ACM.
Gouhar, A., 2017. Database Management System. International Journal of Engineering Science,
11766.
Hababeh, I., Khalil, I. and Khreishah, A., 2015. Designing high performance web-based
computing services to promote telemedicine database management system. IEEE transactions on
services computing, 8(1), pp.47-64.
Lee, H., Chapiro, J., Schernthaner, R., Duran, R., Wang, Z., Gorodetski, B., Geschwind, J.F. and
Lin, M., 2015. How I do it: a practical database management system to assist clinical research
teams with data collection, organization, and reporting. Academic radiology, 22(4), pp.527-533.
Lien, F.L., 2015. The Application of Database Management System to Air Pollution Control for
Construction in Kaohsiung City.
Nidzwetzki, J.K. and Güting, R.H., 2016. DISTRIBUTED SECONDO: An extensible highly
available and scalable database management system. FernUniversität, Fakultät für Mathematik
und Informatik.
Reddy, T.B., Thomas, A.D., Stamatis, D., Bertsch, J., Isbandi, M., Jansson, J., Mallajosyula, J.,
Pagani, I., Lobos, E.A. and Kyrpides, N.C., 2014. The Genomes OnLine Database (GOLD) v. 5:
a metadata management system based on a four level (meta) genome project classification.
Nucleic acids research, 43(D1), pp.D1099-D1106.