The financial resources of the organization

Course Code:
Student Name:
Lecturer Name:
Submission Date:

Introduction
In this task, we shall be designing and documenting a database for a wholesale
management system. We shall be creating the database tables in Oracle database.
An overview on the case scenario has be provided illustrating the business
operations of the case study and the database requirements have also been
captured. An entity relationship diagram (ERD) has also been supplied to provide a
visual overview of the database structure. Sample data has been populated in the
database tables and sample queries have also been written and executed against
the data to test the database.

Stage 1: Scenario and Conceptual Database Design
Task 1.1: Selection of the case upon which the database design and
implementation is to be based
(a) Background information on the organization
Premium Furnitures is a furniture company that sales its furniture to its clients at a
whole sale price. The company gets its furniture from various competent carpenters
who deliver the furniture to the company upon the company’s orders. These
company is organized into 3 departments: the admin department, finance
department and the Casual workers department.
The admin department is responsible for the overall running of the company’s
activities including making orders for furniture from the carpenters and ensuring
that all the customers are well served and satisfied. The finance department is
responsible for accounting for all the financial resources of the organization
including paying for the carpenters’ furniture deliveries and receiving money from
the customers. The Casual workers department is made up of casual workers who
are responsible for offloading and loading furniture for carpenters and customers
respectively.
(b) An overview of what operations a database would need to support
The database is supposed to maintain records for all the company departments,
employees, its customers, carpenters’ deliveries, furniture details and customer
orders.
Department ID and Name details are captured under every department. For every
employee, the database is expected to maintain the details of employee ID, Name,
Gender, Date of Birth, Department, Phone number, Job Description, Salary. For
every customer details of Customer id, name and phone number are maintained.
For each Furniture, Furniture ID, Description, Category, Price and current stock
details are maintained. The furniture is categorized into categories e.g. tables, beds
etc. and the details captured per category include Category id and category name.
Details of Carpenter ID, Name, phone number and workshop name are recorded
under every carpenter. Details of Delivery ID, Carpenter ID, Delivery date and

Employee id of the employee who receives the furniture are recorded for every
carpenter’s delivery.
All the customer purchases are recorded and the details recorded are Purchase ID,
Purchase date and Customer id. Every customer purchase comprises of one or
many furniture.
Task 1.2: A conceptual database design for this scenario and the list of
enterprise rules being modelled.
(a)Enterprise rules
i. A department is composed of many employees but every employee
belongs to one and only one department.
ii. A carpenter can supply many furniture to the company and every single
furniture can be supplied by many carpenters.
iii. A customer can make many purchases and each customer purchase can
have one or many furniture.
iv. Every furniture category is composed of many furniture but a furniture
belongs to one and only one furniture.
v. An employee can receive many carpenter deliveries, as outlined by
Captain, (Captain, 2013).
(b)Entity Relationship Diagram (ERD)
The entity relationship diagram below shows the relationship between different
entities. Entity attributes and keys have also been indicated.