Mini case study: bookshop web application

The main purpose of the project is to develop a web application that allows us to
browse the available books and their quantities, order books online, pay online, and
get delivery. Other bookshops also can order books directly and pay later. (retail and
wholesale operations)

1. Phase One: Preliminary Study

1.1 Definition of the problem

Students waste a lot of time searching for their academic books in several
libraries. It would be convenient to reserve or buy their books online. We
interviewed the principal person in the bookshop, and he explained the
following facts:
- The bookshop sells not only in retail to students, but also to other shops as
wholesale. In this case the client is not obliged to pay immediately, but he
can pay later.
- He has a big load of work at the beginning of each academic year.
- They cannot organize the purchased books, so it becomes difficult to find a
certain book, and sometimes they miss to refill stock in time.
All these problems make delay, lead to dissatisfaction, reduce the overall
efficiency, and decrease the revenue.
The principal person expressed his need for a computer application to facilitate
his work and declared that he is ready to invest up to 5,000$ to lunch the
application and 700$ monthly.

1.2 Alternative solutions

1- We searched and found a stock-management software “stock”. It cost 300$

and 200$ installation & training and is simple to use, but it’s limited to follow
up to stock quantities inside the book store, no online management for the

1
 clients, it is intended for any stock (common purpose) and it can’t store
specific information about books such as ISBN (International Serial Book
Number), etc…
2- Develop a Microsoft access application, this will cost 3000$ for analysis &
development. It will contain all specific information about books and will
allow make effective search. Though no online accessibility to data and no
online ordering.
3- Develop a powerful web application. It will cost 7,000$ and 180$/year for the
ISP (internet service provider.

Feasibility study is done for each alternative. For example, for the third
alternative:
Economic feasibility:
Cost: 7000$ software & hardware
500$ /year maintenance
180$ /year for ISP
2 computers + one printer 2500$
Software 4500$
Total 7000$
Technical feasibility:
May contain offers from different hardware providers.
Operational feasibility: who are the operators that will be direct users of the
system and if necessary, hiring professional operators.

1.3 The report

Contains more detailed info about the suggested alternatives with

recommendation.
According to the scale of the business and the requirements they choose one of the
alternatives, assume that the optimal is the third choice.

2
2. Phase Two: Detailed Analysis

2.1 Gather detailed information (data)

We mentioned in the introduction that we interview the working personnel to

point out the existing problems and formulate objectives and goals of the new
system.

2.2 Existing problems and constraints

i) Difficult to find a certain book

ii) Miss ordering new books which results in shortage
iii) Delay in preparing orders
iv) Difficult to follow up wholesale clients’ accounts which results in delay of
collecting invoices

2.3 Objectives and goals of the new system

i) Index books so that it’s easy to search for a certain book by different criteria
(title, ISBN, class, subject, author, etc…)
ii) Produce a daily report about items under threshold (min quantity) to be
ordered in time
iii) Facilitate and speed up preparing clients orders
iv) Permit clients to place online orders and pay online
v) Follow up clients’ balances
vi) Issue different useful reports such as client statement, inventory, total sales
of each book, sales by subject, …
And other statistical reports that can be useful for the top-level management
for future planning and decision making.

3
 2.4 Scope of the developed system

i) Do we need to develop a software for the wholesale and retail sale or for
only the retail sale?
ii) Do we have to develop only web application, or there should be modules for
instore local use in a form of windows application?
iii) Is there a necessity to keep records about suppliers’ accounts?

2.5 Requirements of the new systems

While functional requirements define what the system does or must not
do, non-functional requirements specify how the system should do it and are
important for the system usability.

Functional requirements:

 Online ordering and payment subsystem for retail sales: The system should
contain a component to allow the user to create an account and, when
needed, login, place an order, then pay for the ordered books using visa or
master cards, after which he expects the delivery of the books within 3 days.
 Direct ordering, invoicing, and payment for wholesale clients: This
subsystem allows the employee to enter an order according to the wish of a
wholesale client. Wholesale clients can be served for credit and no
immediate payment is required, but with a credit limit. The client can settle
his balance on several payments. As well, the order can be split into several
deliveries.
 Purchasing books and follow up suppliers’ accounts subsystem: This
subsystem allows an employee to enter purchase invoice, view and settle
supplier’s account.
 Inventory and ordering subsystem: the local terminal shall automatically
print a list of books to order every morning.

4
 Nonfunctional requirements

 Non-functional requirements define system behavior, features, and general

characteristics that affect the user experience. How well non-functional
requirements are defined and executed determines how easy the system is
to use and is used to judge system performance. Non-functional
requirements are product properties and focus on user expectations.
 Usability. This focuses on the appearance of the user interface and how
people interact with it. What color are the screens? How big are the buttons?
For ex:
o The layout shall allow users to reach their profile data from any page
within 3 clicks.
o The background colour for all screens shall be #fff4b6.
 Reliability / Availability. What are the uptime requirements?
o Does it need to function 24/7/365?
o The server room shall be accessible by authorized employees 24 hours
per day.
o The system must accommodate a minimum of 100 concurrent users.
 Scalability. As needs grow, can the system handle it? For physical
installations, this includes spare hardware or space to install it in the future.
 Performance. How fast does it need to operate? For ex:
o All web pages shall load within 4 seconds.
 Security. What are the security requirements, both for the physical
installation and from a cyber perspective? For ex:
o Database security shall meet strict requirement regarding the
password length and formats.
o If a user has not changed their password for 56 days, then the system
shall require a password change upon login.
2.6 Documents

input documents contain data to be processed by the IS and it is entered using

different input devices such as keyboard, touch screen, barcode reader.
-internal

5
 + Sales invoice (bill)
+ Client order
+ Receipt
-external
+purchase invoice
+purchase order
Output documents are the results of processing data. It can be of two forms:
1- Soft copy (screen)
2- Hard copy (printed)
Ex:
List of available books
List of clients
Client orders
Inventory (quantities of items)

Data also can be gathered by interviewing each of the working personnel using a
set of prepared questions, or by questionaries in a written form.

2.7 Analyze data

We should understand and explain the content of each document as well the
circulation of data. It is highly recommended to use different kinds of diagrams,
such as data flow diagram (DFD) of different levels.
Example: sales invoice
We get a copy of a sales invoice and analyze its contents in detail. As well, we
need to know how the invoice is issued, to whom it is given, who prepares the
order, who verifies if the prepared books are as in the invoice, …
The organization chart is important and helps to understand better the data
flow between different types of users. It represents the administrative structure
in a hierarchical way. A short job description is also useful to understand the
role of each type users in the IS.

6
 manager

sales accounting stock

department department management

Another useful type of diagrams is the use case diagram. It shows all the cases
that a certain user accesses in the information system. Each use case diagram
can be detailed to reflect the interaction between the user and the IS.

7
1. Don’t forget to document your work: prepare the report about
analysis.

8
3. Phase three: System design

The design is the most important step in the system analysis and design phase.
Throughout this step we must design two components of the future IS.
- Processing (treatment)
- Data

3.1 Business rules

We formulate the rules of running the business in a more formal way. These
rules in a high degree determine the future structure (model) of the database
and its processing.
BR1: when a retail client purchases books in cash, we do not register info about
him (no need)
BR2: for wholesale clients we register detailed information about them (name,
address, phone, email, enterprise type)
BR3: for online ordering we also need information about the clients
BR4: for wholesale clients there is a limit (ceil) of the debt
BR5: online clients should pay also online via visa card or master card
BR6: wholesale order can be delivered in several batches (one invoice à several
deliveries)
BR7: online order should be delivered at once
BR8: several online orders are delivered using one vehicle trip
BR9: online orders have a limit of 30 books
BR10: refund (or replacement) is allowed within a condition that the book is still
completely new.
BR11: the minimum level of availability of each book is 10, under this quantity
we must order.
BR12: delivery is done within 3 days
BR13: we buy books directly from the local publisher or importer

9
 3.2 Data dictionary
Attribute Explanation Data type constraint
Clname Char(30) Optional, contains 2
spaces
Claddress Char(30) Not null if clName exists
format city street
building
Booktitle Char(40) Not null
ISBN Int(10) Exactly 10 digits,
unique(no 2 books have
same isbn)
Bookgrade Education level int Between.1 and 10
Bookeddition int >=1
Publishername Char(20) Not null
Authorname Char(30) Not null
Bookprice currency >=2000, >purchase cost
Bookavqtty Available quantity Int >=0
on stock
Purchaseinvno Int >0, external
Purchasedate date <= system date
Purchaseqty int >0
Purchasecost currency >0
salesinvno int >0 serial number
salesdate date =system date
salesqty int >0
deliveryno int >0 serial no
deliverydate date >= salesdate
…..
This data dictionary lacks codes that will be necessary as identifiers of entities

*clientcode Int >0,serial number

*publishcode Int >0,serial number
*authorcode Int >0,serial number

10
*purchaseinvid Int Isbn as unique id
*suppliercode Int >0 serial
Suppliername Char(20) Not null

Note: when we design the CDM we may need to add more attributes, the design
process is an iterative process that passes through many refinements

11
 o Data dictionary: refined
Attribute Data type constraint
1 ClFirstName Char(15) Optional in the invoice
2 ClFatherName Char(15) Optional in the invoice
3 ClfFmilyName Char(15) Optional in the invoice
4 clAddress Char(30) Not null if clName exists, format: city street
building
5 bookTitle Char(40) Not null
6 ISBN LongInt(10) Exactly 10 digits, unique(no 2 books have same
isbn)
7 bookGrade Byte Between.1 and 10
8 bookEddition Int >=1
9 publisherName Char(20) Not null
10 authorName Char(30) Optional
11 bookSubject Char(20)
12 bookPrice currency >=2000, with 2 decimal digits, >purchase cost
13 bookAvQty Int >0
14 bookMinQty Int =10
15 purchaseInvNo Int >0, external
16 purchaseDate Date <= system date
17 purchaseQty Int >0
18 purchaseCost currency >0
19 orderNo Int Serial
20 orderDate Date = system date
21 ordQty Int >0
22 orderType Char(5) Online or Direct
23 onLinePaymentNo Int Serial
24 onLinePaymentDate Date >=orderDate
25 onLinePaymentAmount Currency >0
26 onLinePaymentMethod Char(6) Visa or Master
27 salesInvNo Int >0, serial number
28 salesDate Date = system date, >= orderDate
29 salesQty Int >0
30 deliveryNo Int >0 and serial no
31 deliveryDate Date >= salesDate
32 cashRecieptNo Int Serial
33 cashRecieptDate Date = system date
34 cashRecieptAmount Currency >0

12
35 clientCode Int >0, serial number
36 publisherCode Int >0, serial number
37 authorCode Int >0, serial number
38 purchaseInvId Int internal
39 supplierCode Int >0, serial
40 supplierName Char(20) Not null

3.3 Graph of functional dependencies

We say that B is functionally dependent on A if a value of A determines one and only

one value of B

Examples
Book author à book title X
Isbn à book title // true
Isbn à book price //true
Salesinvno à sales date //true
Salesinvno à book qty X
Salesinvno + isbn à book qty // true
The functional dependencies graph shows all the dependencies of the attribute in the
data dictionary on a common diagram, it is an important step toward the CDM but not
always necessary, it helps in constructing a correct data model

Based on the below refined Data Dictionary and on the requirements and business
rules stated in the Bookshop case study, you are asked to draw the graph of Functional
Dependencies that shows the FD relationships between all the attributes listed in the
DD.

13
 purchaseInvId

supplierCode +

supplierName purchaseCost

purchaseQty
purchaseInvNo

purchaseDate
+
salesInvNo
salesQty
ISBN
salesDate
bookTitle
cashRecieptNo +
bookGrade
cashRecieptDate
deliveryNo bookSubject

cashRecieptAmount
bookAvQty
clientCode deliveryDate

ordQty bookPrice
orderNo
ClFirstName
clAddress orderType authorCode

ClFatherName
orderDate
authorName
ClfFmilyName
bookEddition

onLinePaymentNo publisherCode

onLinePaymentDate
publisherName
onLinePaymentAmount

bookMinQty
onLinePaymentMethod

14
Conceptual Data Model (CDM)

15
Logical Data Model (LDM)

16