Disclaimer: This is an example of a student written essay.
Click here for sample essays written by our professional writers.

Any opinions, findings, conclusions or recommendations expressed in this material are those of the authors and do not necessarily reflect the views of UKEssays.com.

Project Title: Database for a Supermarket Problem

Paper Type: Free Essay Subject: Marketing
Wordcount: 5225 words Published: 1st Jan 2015

Reference this

Shoprite is a big shopping mall in the city of Ibadan, Capital of Oyo State Nigeria; it is located at the center of the City, making it easily accessible to customers coming from any part of the City, Well known for selling good quality products at affordable prices.

Shoprite sells goods of different types and from different brands and sizes which comprises of automobiles, clothing, Household Electronics, Office Electronics, Computer and its Accessories, toys, toiletries, Beverages, Furniture, Cosmetics, Musical Instruments and almost everything needed in an average home

Shoprite has over three hundred (300) employees, working in different units of the company; these employees help to keep the business moving, through making sure customers are satisfied in every transaction made with the mall.

Business Activities within Shoprite flows in this description,

Goods are supplied to Shoprite in bulk, which are recorded into the INVENTORY BOOK

Payments for the goods supplied, if goods are in good condition, payment is made.

Shoprite sells goods supplied, to its customers in units.

For every transaction, an Employee is dedicated to completing the transaction, which is recorded into a book called the SALES BOOK.

Shoprite has discovered that some anomalies are brewing up in the company; like

Goods are exhausted in a very short time and revenue made does not match expected income.

Shoprite want a database system to manage the following

Inventory

Customer Details

Supplier

Courier

What Is the Problem?

Inventory Management: Shoprite like any shopping mall is affected by the varying market trends and as such it is a herculean task to properly ascertain the stock and restock levels. For instance the quantity of toys sold in July-August is considerably higher than other months. Currently, the management of product lines is hectic and the warehouse manager is hard-pressed to get the ordering times and quantities right.

Get Help With Your Essay

If you need assistance with writing your essay, our professional essay writing service is here to help!

Essay Writing Service

Queue: Shoprite is faced with long queue at the mall, because sales representatives cannot provide accurate information about a client’s order. For instance a customer approaches a sales representative and places an order; the sales representative calls the store department to check for the availability of the product, the store employee receives the request and passes the request unto another employee “B” in the store department for information retrieval, Employee “B” checks the store for the availability of the order and finds out the product is unavailable, passes the message to employee A, employee A calls the sales representative and delivers the message to the customer. If this is done for all customers, time taken to process an order has always been Slow and Time consuming.

Why is it considered a problem?

Inventory Management: For instance the quantity of toys sold in July-August is considerably higher than other months. Currently, the management of product lines is hectic and the warehouse manager is hard-pressed to get the ordering times and quantities right.

Queue: For instance a customer approaches a sales representative and places an order; the sales representative calls the store department to check for the availability of the product, the store employee receives the request and passes the request unto another employee “B” in the store department for information retrieval, Employee “B” checks the store for the availability of the order and finds out the product is unavailable, passes the message to employee A, employee A calls the sales representative and delivers the message to the customer. If this is done for all customers, time taken to process an order has always been Slow and Time consuming.

What will be the Benefits Solving it?

The benefits of this project are both non-financial and financial. The benefits of developing a relational database management system (RDBMS) are as follows.

Better productivity: The deployment of a working relational database management system will increase staff productivity because customers will no longer have to queue up for a longer time to buy goods, Transactions will be prompt.

Prompt Transaction: Customers will prefer to buy goods from Shoprite because transactions with Shoprite are prompt.

Good Customer Relationship: Contact details of customers can be stored and retrieved for future references example promos and raffle draws.

Search: Customers will no longer queue up anymore for a product that is out of stock, In other words, searching for a product will be easy an instant, so there will be no wasting of customer’s precious time.

Staff Strength: There will be no need to Increase staff strength to cater for customer request because the relational database management system will ensure that transaction are done effectively and efficiently.

Software Developers who might want to build user interface software for the Shoprite need not spend time constructing a database, because this serves most of its database needs and could even serve as a form of data requirement

What might the solution look like?

This solution will look like a complete shopping center database, which would enable the management staff of Shoprite Supermarket to manage the company effectively and efficiently.

What specifically, do you aim to deliver?

I will deliver the following,

A database schema: This is a description of a database which is a collection of database objects, such as tables, views, clusters, procedures and it is generated using data definition language.

MS SQL Database Scripts: This is Structured Query Language which is the language relational database management understands, It will contain chunks codes for the creation of database objects like Tables, Indexes, Views etc.

Account of Literature

Theory

These are materials to support database for a Shopping Mall:

NIIT (Microsoft SQL Server 2000 Database Design)

This gave me a good understanding of

What really a database is

How to design a database

Constructing a database

Managing a database

Managing MSSQL Server

The following Topics are very useful in the construction of the database and its objects

Implementing Indexes: Indexes enhance the performance of queries, they speed up data retrieval, and it also enforces the uniqueness of rows, Indexes is an internal table structure that relational databases use to provide quick access to the rows of a table based on the values of one or more columns, they are likened to the index of a book. There are two types of indexes NONCLUSTERED or CLUSTERED

NONCLUSTERED: Nonclustered indexes are typically created on columns used in joins and where clauses and whose values are modified frequently.

CLUSTERED: Data is physically sorted and only one clustered index can be created per table, so it should be created on columns that have a high percentage of uniqueness values and that are not modified often.

An index can be creating using the syntax below

CREATE NONCLUSTERED INDEX idxNameofindex ON tablename (columnname)

CREATE CLUSTERED INDEX idxNameofindex ON tablename (columnname)

Where

Create is the keyword to create indexes

IdxNameofindex specifies the name of the index

Columnname is the column where index will be created

CLUSTERED or NONCLUSTERED specifies the type of index.

Creating Views: View are virtual tables which give access to a subset of columns from one or more tables, views are query stored as an object in the database, views retrieve its data from one or more tables called base or underlying tables,

Views help to simplify queries,

Views are also a security mechanism; users can only retrieve and modify only the data seen by them, thereby not affecting the underlying table(s)

View can be created using the syntax below

CREATE VIEW vwNameOfView

AS

Select statement

Where

Create is the keyword to create Views

vwNameOfView specifies the name of the View

select statement specifies the query

Implementing Stored Procedures: Stored procedures are collection of T-SQL statements or control-of-flow language that is stored under one name and executed as a single unit.

Stored procedure helps in the following

Improved Performance of queries

Reduction in network congestion

CREATE PROCEDURE proc_name

AS

BEGIN

Sql_statement1

Sql_statement2

END

Where

Create is the keyword to create Stored Procedures

proc_name specifies the name of the Stored Procedures

BEGIN begins the statements

END ends the statements

Creating Triggers: Triggers are a block of code that constitutes a set of T-Sql Statement that is activated in response to certain actions. They are like special stored procedures that are activated in response to an event, such as modification takes place. Trigger are always defined on a table and is fired whenever Data Manipulation Language (DML) statements like INSERT, UPDATE or DELETE executed, Triggers help in maintaining consistency, reliable and correct data in tables.

CREATE TRIGGER triggername

ON tablename

FOR (INSERT|UPDATE|DELETE)

AS

Sql_Statement

Create is the keyword to create Triggers.

Triggername specifies the name of the trigger

tablename specifies the name of the trigger

Yvonne Rogers, Helen Sharp and Jenny Preece (2002) Interaction design, John Wiley & Sons. ISBN 0-471-49278-7.

This helped a lot about how to gather requirements, how to conduct interviews, giving out questionnaires to staff, naturalistic observation etc.

Methods

Creating Views:

CREATE VIEW dbo.VIEW1

AS

SELECT dbo.orders.OrderDate, dbo.orders.ProductID, dbo.orders.EmployeeID, dbo.orders.OrderID, dbo.orders.CustomerID, dbo.products.ProductID AS Expr1,

dbo.products.ProductName, dbo.products.SupplierID, dbo.products.CatID, dbo.shippers.ShipperID, dbo.shippers.CompanyName,

dbo.shippers.ContactPerson,

dbo.shippers.CompanyAddress, dbo.shippers.State, dbo.shippers.City, orders_1.OrderID AS Expr2, orders_1.CustomerID AS Expr3,

orders_1.EmployeeID AS Expr4,

orders_1.ProductID AS Expr5

FROM dbo.orders INNER JOIN

dbo.products ON dbo.orders.ProductID = dbo.products.ProductID INNER JOIN

dbo.orders orders_1 ON dbo.products.ProductID = orders_1.ProductID INNER JOIN

dbo.shippers ON orders_1.ShipperID = dbo.shippers.ShipperID

Implementing Stored Procedures: Stored procedures are collection of T-SQL statements or control-of-flow language that is stored under one name and executed as a single unit.

Stored procedure helps in the following

Improved Performance of queries

Reduction in network congestion

CREATE PROCEDURE proc_name

AS

BEGIN

Sql_statement1

Sql_statement2

END

Where

Create is the keyword to create Stored Procedures

proc_name specifies the name of the Stored Procedures

BEGIN begins the statements

END ends the statements

Creating Triggers:

CREATE TRIGGER [del] ON [dbo].[orders]

FOR DELETE

AS

Print ‘Deletion Of Orders is not Allowed’

ROLLBACK TRANSACTION

RETURN

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Account of Outcome

Analysis

I made use of interviewing users, so I consulted a friend who works in Shoprite and invited me over. I was able to experience how business transaction takes place in the shopping, recorded the process it takes a customer to make a purchase and how sales representative grant customer’s request. I recorded a scene where they were complaining about one of their suppliers who have failed to supply goods and they lost his/her contact.

I was able to interview a sales representative and The Store Keeper, The Manager of the Shopping mall, asked them about the following.

Description of their daily function: How they do their jobs, what must be done, what must be available to achieve results?

Exchange of information: How they exchange information within the mall, how tedious is working with other departments of the mall.

What they dislike about their functions

Based on my interview/research, I was able to gather the following requirements for Shoprite.

The data requirement of database development for Shoprite is listed below

Product Categories: The database development should manage product categories, this portion of the development should group products in categories, and all products must be in one category.

Customer’s details: The database development should manage customer details; this portion of the development should store customer details, which can be useful in tracking sales.

Order details: The database development should manage order details; this portion of the development should manage sales made by a particular employee.

Orders: The database development should manage orders, this portion of the development should manage daily sales, the employee responsible for the sale, the customer’s identity, freight details et al.

Products: The database development should manage product details; this portion of the development should manage the products and their details including their categories.

Courier services: The database development should manage courier partners of Shoprite customer details; this portion of the development should store courier details, which is useful in dispatching orders.

Suppliers: The database development should manage supplier details; this portion of the development should store supplier’s details, which is useful in managing the inventory.

The development of database for Shoprite should implement the following

Speedy retrieval of results from the database.

These are virtual tables which give access to a subset of columns from one or more tables.

The database should ensure consistency, should be used for validation, and should reject deletion of any order.

The database should automate sales.

Based on all these requirements, I was able to identify the following entity turned tables with their attributes (columns)

Categories – Columns

CatID: This specifies the unique identifier for the categories table.

CatName: This specifies the Category Name that products must belong.

Description: This specifies the description of the category.

Img: this specifies the category image.

Customer – Columns.

customerid: This specifies the unique identifier for the customer table

customername: This specifies the customer name.

companyname: This specifies the company name where customer works.

salutation: This specifies customer’s salutation.

gender: This specifies the customer’s gender.

dob: This specifies the customer’s date of birth.

address: This specifies the customer’s address.

city: This specifies the customer’s city.

state: This specifies the customer’s state.

phone: This specifies the customer’s phone number.

email: This specifies the customer’s email.

Order details – Columns.

odID: This specifies the unique identifier for the order details table.

EmployeeID: This specifies the Employee Id who was responsible for order.

OrderID: This specifies the order identity for the order.

ProductID: this specifies the product identity for the order.

UnitPrice: This specifies the price for each unit of product.

Quantity: This specifies the quantity of products sold in each order.

Discount: This specifies the discount on each order.

Orders – Columns.

OrderID: This specifies the unique identifier for the orders table.

CustomerID: This specifies the customer who makes purchase.

EmployeeID: This specifies the Employee Id who was responsible for a sale.

ProductID: this specifies the product identity for the product purchased.

OrderDate: This specifies the date an order was made.

RequiredDate: This specifies the date an order is required by a customer. This is determined by the customer.

ShipperID: This specifies the identity of the shipper (courier) that is responsible or would be responsible for the delivery of an order.

ShippedDate: This specifies the date an order is shipped to customer’s address.

Freight: This specifies the charge transporting the order.

Products – Columns.

ProductID: This specifies the unique identifier for the product table.

ProductName: This specifies the name of the product.

SupplierID: This specifies the Supplier Id who supplied the product.

CatID: This specifies the category id that the product belongs.

stockPerUnit: This specifies the number of packs or dozens of a product.

UnitPrice: This specifies the price of a unit of a product.

UnitsInStock: This specifies the total number of units of a product available.

ReorderLevel: This specifies the limit where new stock is purchased for the store.

Discontinued: This specifies

Courier services – Columns.

ShipperID: This specifies the unique identifier for the shippers table.

CompanyName: This specifies the shipper’s company name.

ContactPerson: This specifies the name of a contact person working for the courier company (Shipper).

ProductID: this specifies the product identity for the product purchased.

CompanyAddress: This specifies the company’s address (Shipper’s Address).

City: This specifies the city, where the courier company is situated.

State: This specifies the state where the courier company is situated.

Country: This specifies the country where the courier company is situated.

phone: This specifies the company’s phone number.

email: This specifies the company’s email.

Suppliers – Columns.

SupplierID: This specifies the unique identifier for the Supplier’s table.

CompanyName: This specifies the Supplier’s company name.

ContactName: This specifies the name of a contact person working for the supplier company.

ContactTitle: This specifies the position that the contact person holds.

Address: This specifies the company’s address (Supplier’s Address).

City: This specifies the city, where the supplier company is situated.

State: This specifies the state where the supplier company is situated.

Zipcode: This specifies the zip code of the city the supplier company is situated.

Country: This specifies the country where the supplier company is situated.

phone: This specifies the company’s phone number.

email: This specifies the company’s email.

Views: These are virtual tables which give access to a subset of columns from one or more tables.

Indexes: This speeds up query retrieval of any table in the database.

Triggers: This ensures that no order is deleted in the orders table.

Synthesis

After the analysis, the tables were created, below are the syntaxes used to synthesis the requirements

Product Categories: The syntax for the creation of the categories table can be found in the appendices labeled

/****** Object: Table [dbo].[categories] ******/

Customer’s details:

The syntax for the creation of the Customers table can be found in the appendices labeled

/****** Object: Table [dbo].[customers] ******/

Order details

The syntax for the creation of the Order details table can be found in the appendices labeled

/****** Object: Table [dbo].[order_details] ******/

Orders:

The syntax for the creation of the Order table can be found in the appendices labeled

/****** Object: Table [dbo].[orders] ******/

Products

The syntax for the creation of the Order table can be found in the appendices labeled

/****** Object: Table [dbo].[orders] ******/

Courier services

The syntax for the creation of the courier table can be found in the appendices labeled

/****** Object: Table [dbo].[shippers] ******/

Suppliers

The syntax for the creation of the Supplier table can be found in the appendices labeled

/****** Object: Table [dbo].[suppliers] ******/

Views

The syntax for the creation of the view table can be found in the appendices labeled

/****** Object: View dbo.vieworders ******/

Indexes

The syntax for the creation of the Indexes can be found in the appendices labeled

/****** Indexes *****/

Triggers

The syntax for the creation of the trigger can be found in the appendices labeled

/****** Object: Trigger dbo.del ******/

Stored Procedures

The syntax for the creation of the Stored Procedure can be found in the appendices labeled

/****** Object: Stored Procedure dbo.insertOrders ******/

Evaluation

My evaluation is based on the data requirements specified above

Expert Evaluator’s Name: Olayode Ezekiel

Session Date: 08 01, 2010.

Session Start Time: 12:15 pm.

Session End Time: 3:00 pm.

Task being performed

(if relevant)

Heuristic being assessed

If heuristic is violated, usability defect description

Expert Evaluator’s comments or recommendations regarding the usability defect when the heuristic is violated.

OR

Note here if

the heuristic cannot be assessed (justify why it can’t be assessed)

OR

the heuristic is not violated when applied (justify why it is not violated)

The database development should manage product categories

The Database must manage product categories.

Heuristic not violated.

A table named categories has been created to manage all product categories in the shopping mall. A product must belong to a category; in others words a product must have a CATID.

The database development should manage customer details

The Database must manage customer details.

Heuristic not violated.

A table named Customer has been created to manage all customer details, a unique Id is given to each customer and it is required for any purchase the customer wants to make in the future.

The database development should manage order details

The Database must manage Order details.

Heuristic not violated.

A table named orders has been created to manage all customer orders, customer unique Id is required to complete every order.

The database development should manage product details

The Database must manage Products details

Heuristic not violated.

A table named products has been created to manage all products and their details; a unique Id is given to each product, which distinguishes it from every other product. It also serves as the inventory manager, they sale of a product decreases the quantity in the product in the product table.

The database development should manage courier partners of Shoprite customer details

The Database must manage Shoprite’s courier partners.

Heuristic not violated.

A table named shipper has been created to manage all courier partners.

The database development should manage supplier details

The Database must manage Shoprite’s supplier partners.

Heuristic not violated.

A table named Supplier has been created to manage all product suppliers, In case a products has reach re-order level, the supplier is contacted to make supply.

Speedy retrieval of results from the database.

Does this meet Data Requirement?

The heuristic is not violated.

Indexes has been created for all tables in the database to ease retrieval of data in the databases

These are virtual tables which give access to a subset of columns from one or more tables.

Does this meet Data Requirement?

The heuristic is not violated.

Views named vieworders has been created to display sales.

The database should ensure consistency, should be used for validation, and should reject deletion of any order.

Does this meet Data Requirement?

The heuristic is not violated.

A trigger “del” has been created to ensure consistency and reject any deletion to the orders tables.

The database should automate sales.

Does this meet Data Requirement?

The heuristic is not violated.

A Transaction to facilitate sales has been created.

Review Stage of Development

The development of the database is almost completed, to be expressed in term of percentage; its percentage of completion is about 75%.

The requirement of the database is almost fulfilled, the following has been created

Tables

Views

Indexes

Triggers

Constraints

The database can now manage

Employees

Products

Orders

Shippers (Courier)

Suppliers

Review Project Management

Getting data requirement was tedious, so I implemented the Incremental Model; I broke down the requirement in to various functional units, and implemented as I was able to gather more requirements.

I prioritize the functional units with various activities to be performed in each of them,

Each functional unit was implemented in an increment and the final design was achieved

This was achieved by implementing the three phases of the increment model

Design: In this phase the top most activity was selected and design.

Implementation: After the design it was implemented

Analysis: After implementation the partial functional unit was analyzed.

For every functional unit, they were all passed through these phases.

Review of personal development

I personally found the project interesting, made me understand what it takes to build an information technology product, from planning to implementation; It was both challenging and interesting.

I had to visit Shoprite interview them to gather requirement, that was a challenge, visited other stores to gain more knowledge

Read books to come solve every challenge identified,

The project extended my knowledge of project management, How to Analyze, Synthesize and evaluate projects,

The project also extend my knowledge of in database design

I improved in creating transactions and creating triggers.

Epilogue

For any place I didn’t include the creation of a stored procedure,

A stored procedure was created named insertorders to automate sales and also decrease the units of a product in stock once the transaction is completed.

Appendices

/****** Object: Table [dbo].[categories] ******/

CREATE TABLE [dbo].[categories] (

[CatID] [int] IDENTITY (1, 1) NOT NULL ,

[CatName] [char] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Description] [char] (1500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[img] [image] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/****** Object: Table [dbo].[customers] ******/

CREATE TABLE [dbo].[customers] (

[customerid] [int] IDENTITY (1, 1) NOT NULL ,

[customername] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[companyname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[salutation] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[gender] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[dob] [datetime] NOT NULL ,

[address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[State] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[phone] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[employees] ******/

CREATE TABLE [dbo].[employees] (

[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,

[LastName] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Title] [varchar] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[TitleOfCourtesy] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Gender] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[BirthDate] [datetime] NOT NULL ,

[HireDate] [datetime] NULL ,

[Address] [varchar] (180) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[PostalCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Phone] [char] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Extension] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[img] [image] NULL ,

[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ReportsTo] [int] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/****** Object: Table [dbo].[shippers] ******/

CREATE TABLE [dbo].[shippers] (

[ShipperID] [int] IDENTITY (1, 1) NOT NULL ,

[CompanyName] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[ContactPerson] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[CompanyAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[State] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Phone] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

/****** Object: Tabl

 

Cite This Work

To export a reference to this article please select a referencing stye below:

Reference Copied to Clipboard.
Reference Copied to Clipboard.
Reference Copied to Clipboard.
Reference Copied to Clipboard.
Reference Copied to Clipboard.
Reference Copied to Clipboard.
Reference Copied to Clipboard.

Related Services

View all

DMCA / Removal Request

If you are the original writer of this essay and no longer wish to have your work published on UKEssays.com then please: