What is a conceptual data model? With examples!

Course Code and ID:
Student Number and Name:
Lecturer Name:
Submission Date:

Table of Contents
INTRODUCTION....................................................................................................... 3
Stage 1: Scenario and Conceptual Database Design...............................................4
Task 1.1: Case Scenario upon which the database design and implementation is
to be based.......................................................................................................... 4
Task 1.2: Conceptual database design for the case scenario & the list of
enterprise rules being modelled...........................................................................4
Stage 2: Logical Database Design and Oracle SQL Implementation/querying........7
Task 2.1: Logical Database Design.......................................................................7
Task 2.2: Creation of the tables using Oracle DBMS............................................7
Task 2.3: Four indexes on tables..........................................................................8
Task 2.4: Data Population.................................................................................... 9
TASK 2.5: SQL Query writing..............................................................................17
Conclusion............................................................................................................. 19
References............................................................................................................ 20

INTRODUCTION
Databases play a very important role in maintaining organization’s data. Among the few
advantages that databases offer to the organizations include easy storage and retrieval of data,
reduction of data redundancy, improved data consistency, data security among others. In this
context, database design and implementation focus on a restaurant management system. We
shall be implementing the database using Oracle database management system for Sweet Pot
restaurant. The database will maintain the restaurants information about the employees,
products sold, suppliers, products the suppliers deliver, customers and their respective orders.
An entity relationship diagram has also been supplied to provide a visual understanding of the
database structure. Sample data has also been populated to test the database.

Stage 1: Scenario and Conceptual Database Design
Task 1.1: Case Scenario upon which the database design and
implementation is to be based
(a) Background information on the organisation
In this case scenario, a restaurant management system has been selected. Sweet pot is a
fictious restaurant which offers food and beverage services to its customers. The restaurant is
structured into 5 departments, the Management department, Accounting and finance
department, Sanitation department, Kitchen department and Reception and service department.
The management department is responsible for managing the day to day activities of the
restaurant and includes the Manager, Assistant manager, Marketing director and other
employees employed in that department. The accounting and finance department is responsible
for managing all the financial resources of the restaurant. This includes ordering and paying for
items brought in by suppliers. The sanitation department is responsible for maintaining the
overall cleanliness of the restaurant. The kitchen department is responsible for preparing meals
according to the customer orders. The reception and service department offer services to the
customers including receiving customers and serving them with their orders.
Basically, the restaurant gets most of its goods from suppliers who deliver them upon an order
made by the restaurant through the Accounting and finance department. Upon the arrival of a
customer, employees from the Reception and service department receives them and the
customer proceeds to the Accounting and finance department to make an order and make
payments. A customer is then issued with a receipt indicating the meals ordered. Employees
from the reception and service department liaises with the kitchen department to deliver the
customer order.
(b) An overview of what operations a database would need to
support
The database is expected to maintain the following information:
i. Departments details – Department ID, Department name
ii. Employees Details – Employee ID, First Name, Last Name, Date employed,
Department, Address, Street, City and Phone number.
iii. Product details – Product ID, Product Description, Price
iv. Supplier details – Supplier ID, Name, Address, Street, City and Phone number.
v. Supplier deliverables – Delivery ID, Supplier ID, Delivery Date.
vi. Supplier Products – ID, Product ID, Price and Quantity.
vii. Customer details – Customer ID, First Name, Last Name, Gender, Phone number.
viii. Customer Orders – Order ID, Order date, Customer ID.
ix. Customer Ordelines – Line ID, Order ID, Product ID, Price and Quantity.