IS1060 Database Computing Project: Warehouse Database Design

This database computing project details the development of a warehouse management system using MS Access. The project begins with a problem description, followed by use case and class diagrams to represent the system's functionality and structure. The core of the project involves database normalization, ensuring data integrity and efficiency. The solution progresses through un-normalized, first, second, and third normal forms, culminating in a final database design. The implemented database includes tables for products, suppliers, purchase orders, and product purchases, with defined relationships and data types. The project also covers the design of data entry forms and reports, and concludes with a discussion of the software development methodology. The report highlights the benefits of the proposed system for managing warehouse operations and emphasizes the use of MS Access as a user-friendly database management tool. Finally, the project demonstrates the understanding of database concepts, normalization, and the application of software development principles.

Running head: DATABASE COMPUTING
Database Computing
Name of the Student
Name of the University
Author’s note:

1DATABASE COMPUTING
Table of Contents
Introduction:....................................................................................................................................2
Problem Description:.......................................................................................................................2
Use Case and Class Diagram:..........................................................................................................4
Use Case:.....................................................................................................................................4
Class Diagram:.............................................................................................................................5
Normalization:.................................................................................................................................5
Designing and Implementing Database:..........................................................................................7
Conclusion:....................................................................................................................................14
Bibliography:.................................................................................................................................16

2DATABASE COMPUTING
Introduction:
Database can be referred to the organized set of information. These information are
stored in a specific computer location so that information can be accessed later. Each database
allows four operations such as create, update, read and delete (Eckstein and Schultz 2018).
Database management system is a piece of application that provides an interface to the user
through which database operations can be executed. Each operation in database is either write or
read. Read operations does not affect database but write operations update database schema.
Structured data has a single structure for all the instance of a particular entity. Relational
databases uses these structured data to create information storage that establishes defined link
between data. MS Access is a relational database developed and maintained by Microsoft.
Unlike other database management systems like MySQL, Oracle, SQL Server, MS Access
provides various features to generate forms and reports to show data a comprehensive way
without using any external technology (Arulraj and Pavlo 2017).
This report will consists of database system development for a warehouse. Details
description of the warehouse will be provided in the report. Each development decision made in
this project will be based on the issues and requirements of warehouse. UML diagrams like Use
Case and Class diagrams will be provided to graphically represent the dynamic behavior and
static structure of the database respectively (Sahoo and Mohanty 2017). Finally, a database will
be developed using MS Access.
Problem Description:
My family runs a vegetable shop that sales fresh vegetables to local customers. High
quality fresh vegetables are hard to come by these days. That’s why our family shop had

3DATABASE COMPUTING
experienced increase in sales from last year. Customers can book their order through phone. One
person responsible for accepting the order will write down the lists of item into a piece of paper.
As papers based information is hard to manage, often orders related information goes missing. If
it happens, then customer has to reorder after coming to shop and wait until their order is ready.
Manual record of information is often error prone. Staff may write wrong item or quantity which
may lead to wrong order. These issues are affecting customer experiences. Our shop is facing
difficult time in maintaining customer relationship due to inaccurate order related issues.
Business is experiencing delay in processes.
Another major issue my family business facing is that no data related to warehouse is
recorded into any reliable location. At time of accepting order from customer, shop employee
does not have any idea of stock available in warehouse. It results into promising delivery of
products that are not available in warehouse. Often due to miss information of predictable sales,
warehouse store more stock of a particular item or items that required. This results into waste of
product which in-turn reduce profit margin of business. If details of products along with
available stock can be stored then it will be easy to maintain business.

4DATABASE COMPUTING
Use Case and Class Diagram:
Use Case:
Figure 1: Use Case Diagram
(Source: Created by Author)
Proposed use case diagram has total three actors such as receptionist, supplier and admin.
In order to access the system everyone has to login to it. Only the supplier details will be stored
in the database as business needs to show supplier details. Staff and admin details does not affect

5DATABASE COMPUTING
business processes of warehouse. Receptionist will be responsible for communicating with
customers and accepting orders on their behalf. Receptionist can also update order status.
Receptionist will generate invoice for customer based on which payment will be collected.
Suppliers will enter products delivered in a particular purchase order. Supplier can generate
invoice based on which they will receive payment. Admin will track each data stored in
database. Admin can check purchased products, sold products, customer list and many more.
Class Diagram:
Figure 2: Class Diagram before Normalization
(Source: Created by Author)
Normalization:
ONF: Un-normalized form is a database state where database can experience all type
data anomalies. This is the most dangerous state of the database. The database with ONF looks
like as following.
Warehouse (productID, name, description, price, availableUnits, numberOfUnit, unitCost
supplierID, name, address, email, dateOfPruchase, dateOfDelivery, totalCost)

6DATABASE COMPUTING
1NF: To prepare the database by following the rules of first normal form. In this step, it
will be made sure that database does not have any multivalued attributes.
2NF: Partial dependency prevents a database from following second normal form rules.
Partial dependencies in the database are as following.
productID  name, description, price, availableUnits, numberOfUnit
supplierID  name, address, email
productID, supplierID  numberOfUnit, unitCost, dateOfPruchase, dateOfDelivery,
totalCost
Therefore, if partial dependencies are removed, database will be decomposed into
following tables.
Product (ID, name, description, price, availableUnits, numberOfUnit)
Supplier (ID, name, address, email)
PurchaseOrder (productID, supplierID, numberOfUnit, unitCost, dateOfPruchase,
dateOfDelivery, totalCost)
3NF: Transitive dependency eliminates the possibility for a database to be in third
normal form. Transitive dependency in the above decomposed database remains within
productID, numberOfUnit and unitCost. If this dependency is resolved then following tables are
created. This is the final database.
PurchaseOrder (ID, supplierID, dateOfPruchase, dateOfDelivery, totalCost)
ProductPurchase (purchaseOrderID, productID, numberOfUnit, unitCost)

7DATABASE COMPUTING
Product (ID, name, description, price, availableUnits, numberOfUnit)
Supplier (ID, name, address, email)
The final class diagrams will look like as following.
Designing and Implementing Database:
Proposed warehouse system concentrate on purchase order side of the business which is
related to warehouse directly. In order to design the database, all the tables normalized up to
third normal form. Each table prevents existence of multivalued attributes, partial dependency
and transitive dependency. Tables are designed such way that inert, update or delete anomalies
can be prevented easily. Tables of the database has been shown in the following section.
Table Name: Products
Field Names Data Type Length Primary Key Foreign Key

8DATABASE COMPUTING
ID AutoNumber
(Increment)
Long Integer Yes
name Short Text 255
description Long Text 1000
price Currency Up to two
decimal places
availableUnits Number Long Integer
Table Name: Suppliers
Field Names Data Type Length Primary Key Foreign Key
ID AutoNumber
(Increment)
Long Integer Yes
name Short Text 255
address Short Text 255
email Short Text 255

9DATABASE COMPUTING
Table Name: PurchaseOrder
Field Names Data Type Length Primary Key Foreign Key
ID AutoNumber
(Increment)
Long Integer Yes
supplierID Number Long Integer Reference
Suppliers (ID)
dateOfPurchase Date/Time
dateOfDelivery Date/Time
totalCost Currency Up to two
decimal places
Table Name: ProductPurchase
Field Names Data Type Length Primary Key Foreign Key
purchaseOrderID AutoNumber
(Increment)
Long Integer Yes Reference
PurchaseOrder

10DATABASE COMPUTING
(ID)
productID Number Long Integer Yes Reference
Products (ID)
numberOfUnit Number Long Integer
cost Currency Up to two
decimal places
The foreign key of each table represents the relation among the database entities.
Products table is related to Purchase product table. One product can be purchase more than one
time but a product cannot be added more than once to an order list. Therefore, relation between
products PurchaseProduct table is one to many and optionality is zero to one. Zero to one
because a product may not added to any order list but products in order list must be in products
table.
Each purchaseOrderID in the product purchase refer to the primary key of PurchaseOrder
table. A PurchaseOrder table instance can only be added if at least one product is added to order
list. As more than one product can be purchased against an order, primary key of PurchaseOrder

11DATABASE COMPUTING
can appear in ProductPurchase table multiple times. Therefore, Relation between PurchaseOrder
and ProductPurchase is many to one and optionality is one to one.
Each purchase order is fulfilled by a specific supplier. Supplier information should be
stored into database to specify it is delivering the order. It is not mandatory that a supplier must
fulfill at least a single order. Therefore, multiplicity of relation between supplier and purchase
order is one to many and optionality is zero to one. This means that one supplier can fulfill many
order or may to delivery any order.
First data entry form is product’s form. This form accepts values for all attributes except
ID because ID is auto increment value.

12DATABASE COMPUTING
Second form is for suppliers. This form accepts name, address and email of supplier. As
ID is auto increment value, form does not accept it. It is auto generated when provided values are
stored
This form accepts data for both PurchaseOrder and ProductPurchase tables.

13DATABASE COMPUTING
Management will access three reports from the database. First report is product report
which shows all the product related information.
Second report is about suppliers. Managers can access all the supplier details from this
report.

14DATABASE COMPUTING
Third report is on Product Purchase. All the purchase order and product purchase related
details are shown through this report.
Conclusion:
From the above study it can be concluded that proposed system can enhance the current
situation our family business. In order to create this project, many other things needed to be
understood first. Learning only syntax and UI of MS Access was not enough, basic information
of databases and relation among database entities was a big factor in database. Understanding the
relations was one of the biggest tasks in this project. Reports and forms are based on how each
table establish relations among those. Proposed database is capable of handling the warehouse
operations. At the time of developing this project, comprehensive idea of software development
methodology have been gathered. To complete this project, system design, analysis and
development phases has been used significantly. In the analysis phase, database related ideas like
business rules, possible entities and other information have been identified. In the design phase,

15DATABASE COMPUTING
normalization has been applied to prevent data anomalies. Moreover, UML diagrams like Use
Case and Class diagrams have been created to represent the database graphically. Finally, in
development phase, database has been developed in MS Access.
MS access is a great database management tool that provides comprehensive features to
implement database and UI of the system. Business does not need additional technology to create
an UI that a normal person can understand.

16DATABASE COMPUTING
Bibliography:
Albarak, M., Alrazgan, M. and Bahsoon, R., 2017. Identifying and Managing Technical Debt in
Database Normalization Using Machine Learning and Trade-off Analysis. arXiv preprint
arXiv:1711.06109.
Alotaibi, Y. and Ramadan, B., 2017. A Novel Normalization Forms for Relational Database
Design throughout Matching Related Data Attribute. International Journal of Engineering and
Manufacturing, 7(5), p.65.
Arulraj, J. and Pavlo, A., 2017. How to build a non-volatile memory database management
system. In Proceedings of the 2017 ACM International Conference on Management of Data (pp.
1753-1758).
Barber, R.M., Fullman, N., Sorensen, R.J., Bollyky, T., McKee, M., Nolte, E., Abajobir, A.A.,
Abate, K.H., Abbafati, C., Abbas, K.M. and Abd-Allah, F., 2017. Healthcare Access and Quality
Index based on mortality from causes amenable to personal health care in 195 countries and
territories, 1990–2015: a novel analysis from the Global Burden of Disease Study 2015. The
Lancet, 390(10091), pp.231-266.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Eckstein, J. and Schultz, B.R., 2018. Introductory Relational Database Design for Business, with
Microsoft Access. Newark: Wiley.
Eessaar, E., 2016. The database normalization theory and the theory of normalized systems:
finding a common ground. Baltic Journal of Modern Computing, 4(1), p.5.

17DATABASE COMPUTING
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems (Vol. 7). Pearson.
Jochim, M., 2017, August. Extending the EMU Speech Database Management System: Cloud
Hosting, Team Collaboration, Automatic Revision Control. In INTERSPEECH (pp. 813-814).
Lewis, K.A., Tzilivakis, J., Warner, D.J. and Green, A., 2016. An international database for
pesticide risk assessments and management. Human and Ecological Risk Assessment: An
International Journal, 22(4), pp.1050-1064.
Newman, A.M., Gentles, A.J., Liu, C.L., Diehn, M. and Alizadeh, A.A., 2017. Data
normalization considerations for digital tumor dissection. Genome biology, 18(1), p.128.
Noh, H.N., Bahari, M. and Zakaria, N.H., 2018. A Conceptual Model of Database Normalization
Courseware Using Gamification Approach. In PROCEEDINGS OF NEW ACADEMIA
LEARNING INNOVATION (NALI) SYMPOSIUM 2018 (p. 23).
Sahoo, P. and Mohanty, J.R., 2017. Early test effort prediction using UML diagrams. Indonesian
Journal of Electrical Engineering and Computer Science, 5(1), pp.220-228.
Senterre, B., Baguette, F. and Chong-Seng, L., 2018. Silhouette Adaptive Conservation
Management Plan 2018-2022 using Bioma: a simple MS Access database. Consultancy Report,
Island Conservation Society, Victoria, Seychelles, 97.
Shevtsova, L.N., Bronov, S.A., Zobova, N.V., Shmeleva, Z.N. and Paturinskiy, A.V., 2020,
January. The development of the educational and applied database in MS Access for selection
and genetic research. In IOP Conference Series: Earth and Environmental Science (Vol. 421,
No. 4, p. 042014). IOP Publishing.

18DATABASE COMPUTING
Sultan, M., 2019. IoT Microsoft access based database management system for health care and
clinical settings/Sultan Mahmud (Doctoral dissertation, University of Malaya).