Kahuna Cleaning Supply Database Design

This document presents a solution to a database design project for Kahuna Cleaning Supply, a company that sells cleaning products and services. The solution includes a fact-finding plan outlining methods like interviews, document reviews, observations, and questionnaires to gather requirements. Specific interview questions are provided for various stakeholders, including sales representatives, accounting clerks, the technical team, and the warehouse manager. A sample questionnaire for customers is also included. The core of the solution is a detailed database design, presented as a series of tables (Payment_Data, Payment_Type, Payment_Details, Shipment, Customer, City, Service_Type, Status_catalog, Shipment_Status, Shipment_Type, Shipment_Details, Product, Product_Type, Stock) with their respective columns, data types, and constraints. The design aims to eliminate data redundancy and ensure data integrity. The tables are designed to handle customer information, product details, orders, payments, shipments, and inventory. The solution confirms that the database is in 3NF (Third Normal Form), indicating a well-structured and efficient design.

KAHUNA CLEANING SUPPLY
Student Name:
Student ID:
Course Name:
Course ID:
Semester:
Faculty Name:
University Name:

1
Contents
Part A...........................................................................................................................................................2
Part B...........................................................................................................................................................5
Systems Design and Analysis...........................................................................................................6
Abstract.......................................................................................................................................................6
Introduction.................................................................................................................................................6
Task 1:................................................................................................................................................7
Task2:.................................................................................................................................................8
Task 3:..............................................................................................................................................11
1

2
Part A
Task 1: Fact-finding Plan
The aim of the fact finding plan is to gather information that is relevant to the development of
new system so that no mistake happens and the implementation is successful. The fact finding
plan will help in answering the questions like Who, What, Where, When, How, and Why?
 For finding the facts, interviews will be taken that will help in gathering the information
from the particular people who will be involved in establishment and implementation of
this system. The interview will contain the open ended questions as they will give a
detailed explanation about the stem and its requirements.
 Then, the document review will be done. This will help in determining that how the
current system should be and how the new one should work. In this, the copies of the
actual forms will be obtained and the operating documents will be scanned that are in use
currently.
 Observation will be used for verifying the statements that were made in the interviews so
that the validity of the procedures is determined.
 Questionnaires will be formulated for obtaining the input from a large group of people. It
will have a range of relevant questions.
 Finally, research will be done using the internet sources, books, IT magazines etc. This
will help in secondary research.
2

3
Task 2: Objectives for the interview
The list of individuals who will be interviewed and the aims of interview with each of them is
given as follows:
1. Sales and customer service representatives: To get idea for the new information system
2. Accounting clerks: To get an idea about the budget that is required to be invested in the
project.
3. The technical team: To get an idea about how they plan to develop the online presence
and what is the expected ROI from the online presence. Also, to know that how the
technology can reduce the amount of human errors made in the systems.
4. The warehouse manager: To know about the availability of space and to inquire about
requirement of more space.
Task 3: List of specific questions
1. Sales and customer service representatives:
a. What are the current major issues of customers?
b. How comfortable are you with the systems that you use for dealing with the clients?
c. Out of 10 queries of customers. How many are you able to solve?
d. Are the customers satisfied with your service?
2. Accounting clerks:
a. What is the current budget of the system?
3

4
b. What is the expected budget of the new system?
c. How will you manage the finances?
d. How will you source the finances?
e. How can you cut the costs?
3. The technical team:
a. What information should be included in the information system?
b. What do we get put it in the right manner?
c. What processes needs to be managed?
d. What are the business needs that the new system will fulfill?
4. The warehouse manager: To know about the availability of space and to inquire about
requirement of more space.
a. How much space is currently available?
b. How much space gets underutilized?
c. How much more space is required?
Task 4: Questionnaire
The questionnaire has been designed for the customers and they will be selected on the basis of
cluster sampling method. In this, the heterogeneous group of people will be selected so that the
views and opinion of all kinds of people could be gathered.
4

5
5
Name:
Age:
Gender:
Occupation:
1. Why do you buy from our store?
a. Reasonable price
b. Good quality
c. Attractive coupons and discounts
d. Good reviews
2. How did you find the store?
a. Social networking
b. Google
c. Advertising campaigns
d. Other
3. Are you satisfied with the ordering process?
a. Yes
b. No
c. Maybe
4. Did the Overall Customer Experience Meet your Expectations?
a. Yes
b. No
c. To an extent
5. What Is your Preferred Method of Connecting with Us?
a. Online website
b. Physical store
c. Delivery boys
d. Other (Please specify)
6. Do the sales representatives answer your queries and are able to solve your problems?
a. Yes
b. No
c. Sometimes
7. Please give your suggestions for further improvements

6
Part B
Systems Design and Analysis
This is assumed that, kahuna cleaning company provides cleaning product and ship them to clients. The
client payment details are saved into database for client. So that payment details will not asked to client
every time they purchase some things. There can be more than one client. This is assumed that
company has many cleaning products to sell and they all are stocked according to their category. Stock is
updated time to time as required. There could be many services. That a client can prefer. This company
provide services to clients over the cities. The clients could be from any cities. So this is assumed that all
tables are designed as so that no duplicate data stored in database.
Abstract
This is an online website project. Where customer can fill the form to order the cleaning products or
service type which they want and then submit that details on line. The payment system can be
different for different customer. Some customer can pay online after getting confirmation from the
company for their availability for the service. The customers can access this websites from anywhere.
This system is restricted for few cities. This system not belongs to all over country or world. So this
company sell the all type of cleaning products to customers and provides some different type of services
to customers. This company has many products. All product details are saved into database so that user
can view online and can fill the form for these products.
Introduction
There is a company with the name “KAHUNA CLEANING SUPPLY”. This company supply the all cleaning
products to customers. Currently there are different departments to handle each operation in the
company. Some are as manager, sales, accounting. Currently the all work is paper based because of this,
the company staff not able to manage the all operations properly and timely. The company losses many
of contracts because to this lack of system. So this company want to make a system where the large
amount of data for all the operations can be handled and recorded in a proper way.
6

7
Task 1:
7

8
Task2:
Table: Payment_Data
Column Name Data Type Constraints
Id INT PK
Payment_type_id INT FK
Data_name VARCHAR(100)
Data_type VARCHAR(100)
Table: Payment_Type
Column Name Data Type Constraints
Id INT PK
Type_name VARCHAR(84)
Table: Payment_Details
Column Name Data Type Constraints
Id INT PK
Shipment_id INT FK
Payment_data_id INT FK
value VARCHAR(100)
Table: Shipment
Column Name Data Type Constraints
Id INT PK
custID INT FK
Time_created TIMESTAMP
Shipment_type_id INT FK
Payment_type_id INT FK
Service_type_id INT FK
Time_created TEXT
Shipment_address TEXT
Products_price DECIMAL(8,2)
Delivery_cost DECIMAL(8,2)
Discount DECIMAL(8,2)
Final_price DECIMAL(8,2)
Table:Customer
8

9
Column Name Data Type Constraints
Id INT PK
fName VARCHAR(10)
lName VARCHAR(10)
cityID INT FK
address VARCHAR(100)
Table:City
Column Name Data Type Constraints
Id INT PK
City_name VARCHAR(130)
Table:Service_Type
Column Name Data Type Constraints
Id INT PK
Service_name VARCHAR(60)
description VARCHAR(100)
Table: Status_catalog
Column Name Data Type Constraints
Id INT PK
Status_name VARCHAR(255)
Table: Shipment_Status
Column Name Data Type Constraints
Id INT PK
Shipment_id INT FK
Status_catalog_id INT FK
Status_time TIMESTAMP
notes TEXT
Table: Shipment_Type
Column Name Data Type Constraints
Id INT PK
9

10
Type_name VARCHAR(64)
Table:Shipment_Details
Column Name Data Type Constraints
Id INT PK
Shipment_id INT FK
Product_id INT FK
Quantity DECIMAL(8,2)
Price_per-unit DECIMAL(8,2)
price DECIMAL(8,2)
Table: Product
Column Name Data Type Constraints
Id INT PK
Product_name VARCHAR(84)
Product_type_id INT FK
Product_description VARCHAR(255)
Unit VARCHAR(16)
Price_per_unit DECIMAL(8,2)
10

11
Table: Product_Type
Column Name Data Type Constraints
Id INT PK
Type_name VARCHAR(84)
Table: Stock
Column Name Data Type Constraints
stockID INT PK FK
inStock DECIMAL(8,2)
Last_update_time TIMESTAMP
Task 3:
There are total 14 tables and they are already in third normal form so do not required to modify them
further.
11