Database Tables for a Convenience Shop: The Five Normal Forms (Part 5)

This is the fifth and last part of the series, The Five Normal Forms. In this part of the tutorial series, all the tables are presented with a modification on the Products table. How the profit or loss is calculated from the database is also explained.

This tutorial series follows a story line which is as follows: A father has just died and left some money for his son. The son decided to invest the money in a convenience shop. The shop is already stocked, and selling to customers has already begun.

At the beginning of the business, both the son who is the proprietor and his employees, called clerks in this tutorial series, knew nothing about normal forms.

You, the reader, has completed this Five Normal Form tutorial series and you are also a database developer. The proprietor is your friend. You visited the shop four times, once per day, in the past four days. On the first three days, you taught both the proprietor and his employees about the 1NF, 2NF, and 3NF.

Yesterday, you visited only the proprietor in his office and trained him on the BCNF, 4NF, and 5NF. Today, you visited only the proprietor to improve on the Products table, present all the tables, and explain how to calculate the profit or loss.

First, you recap the rules for all the normal forms.

Normal Form Rules

In theory, a table can be created and happens to be in the fifth normal form, since each normal form has rules that should not be violated as opposed to the rules that should be implemented. However, a table is checked from the first, second, third, Boyce Codd, fourth, and to the fifth normal forms.

A table is in 1NF if none of the following rules is violated:

1. All the columns in a table should have unique header names.
2. Each cell must have only a single value.
3. The values stored in a column should be of the same type.
4. The rows should be distinct.
5. The order of the columns or order of rows do not matter.

A table is in 2NF if none of the following rules is violated:

1. The table must already be in the First Normal Form.
2. There must be no partial dependency.

A table is in 3NF if none of the following rules is violated:

1. It should already be in the Second Normal Form.
2. It should not have Transitive Dependency.

A table is in BCNF if none of the following rules is violated:

1. The table should already be in the Third Normal Form.
2. No non-prime attribute (column) should depend on part of the composite primary key.

A table is in 4NF if none of the following rules is violated:

1. It is already in Boyce-Codd Normal Form.
2. The table does not have any Multi-Valued Dependency.

A table is in 5NF if none of the following rules is violated:

1. It is already in the Fourth Normal Form.
2. The table does not have Join Dependency.

At any stage, if the table does not conform to the normal form, the table is split to conform.

The Tables

Products Table

The products table that we arrived so far is:

categoryID and supplierID are foreign keys that are the same as the primary keys of the Categories table and the Suppliers table, respectively.

This table is in 5NF if the unitPrice and the quantityInStock are not changing with time. Even the supplier of a particular product can change. However, for simplicity, the changing of supplier will not be addressed in this tutorial. The unit price for any product changes over time, though slowly. Right now, the unit price for many products all over the world have increased because of the Russia-Ukraine war. Since individual customers buy from a shop, the quantity in stock changes (reduces first before being replenished). The quantity of a particular product in stock is the sum of what is on the shelf and what is in the store (backup). For simplicity, this separation is not made in this tutorial series.

With these changing columns, this table is now in 1NF. The table has to be split into two, with the changing group going one way while the rest of the table remains.

The children tables become:

and

The new table is called Product-States. It needs its own ID as well as the Product ID. The table should actually be:

The productStateID is auto-incremented, beginning from 1. A date column is added to indicate when the change took place. The primary key for the ProductStates table is a composite key consisting of productStateID and productID. The two tables are linked by the productID column. The ProductStates table is in 2NF since there is no partial dependency. In each of its rows, either the values of date, unitPrice, or quantityInStock determines both keys. In fact, both tables are now in 5NF, as none violates any of the normal form rules.

If the previous two tables are not kept in a computer with a backup, they should be kept in a ledger. The first half of the ledger can have the Products Table and the second half can have the ProductStates Table. The ProductStates table grows with time.

A discontinued column of Boolean type can be added to the right end of the Products table to indicate whether the product has been discontinued; that is, the shop no longer deals with that particular product.

The product tables and the rest of the tables in the database are now in the Fifth Normal Form.

Suppliers Table

The table notation for the Suppliers table is:

If this table is not kept in a computer with backup, it should be kept in a ledger. A date column can be added to indicate the first day that the supplier became engaged with the convenience shop. Discontinued column can also be added.

Customers Table

The table notation for the Customers table is:

If this table is not kept in a computer with backup, it should be kept in a ledger. A date column can be added to indicate the first day that the customer became engaged with the convenience shop.

One customerID can be for passers-by: people who visited the town and are staying in hotels, holiday makers, tourists, etc.

Selling Tables

The three Selling tables are:

A lending column of Boolean type is introduced in the SaleDetails table, in case the customer can be trusted. Only the proprietor should authorize the lending. In the CategoryDelivery table, a column for the cost of transportation to-and-fro for delivery is added.

If these tables are not kept in a computer with backup, they should be kept in a ledger called the Sales Ledger. The left half of the ledger can be used for the Sales and SaleDetails tables (see below). The right half of the ledger can be used for CategoryDelivery table.

If the tables are kept in a ledger, the SaleDetails table would be:

With the columns of totalSold and totalLent added. These columns should be filled daily at the end of the day or weekly at the end of the week by you, the proprietor.

If the tables are kept in a computer with backup, the database software (program) would calculate the totals.

The SaleDetails rows corresponding to the Sales row are usually more than one. So, for the left half of the sales ledger, the left page of the opened ledger has the sales data while the right page has the sale-details data, as follows:

Sales/SaleDetails Table

If a customer came and paid for certain products and borrowed the others, those should be two saleDetails. total_Sold = U.S.P x quantity. A similar calculation is done for total_Lent. The grand totals for totaSold and totaLent, for the bottom of the table can be calculated at the end of the day or at the end of the week or end of the month, depending on the frequency of sales. All totals should be calculated by you, the proprietor, who doubles as manager.

Orders Tables

The order tables are:

If these tables are not kept in a computer with backup, they should be kept in a ledger called the Purchase Ledger. In this case, the OrderDetails table should be:

If a shop paid for some orders and borrowed some, those should be two orderDetails.

There is no corresponding delivery table here. The records for the orders and orderDetails should be made in the Purchase ledger in a similar way that the sales and saleDetails are made in the Sales ledger.

All Tables in 5NF

Products Ledger

Suppliers Ledger

Customers Ledger

Sales Ledger

Orders Ledger

Profit and Loss Statement

A Profit and Loss Statement is a financial statement that shows the firm’s income and expenditures. It also shows whether the firm is making profit or loss for a given period. A Profit and Loss Statement is made monthly or quarterly. Quarterly means every three months: March, June, September, December. This statement should be kept in its own ledger.

Major Components for Profit and Loss Statement

These components are described here with reference to the convenience shop.

Revenue

Another name for revenue is Sales. This is the first section of the Profit and Loss Statement. It is the amount of all what was sold for that period (Grand Total for totalSold column).

Cost of Products Sold

This should be the second section of the Profit and Loss Statement. It is the amount of cost of all the products sold for the period (Grand Total for totalBought column).

Gross Profit

This is:

for the period.

Gains

If an unused vehicle is sold, that is a gain (see depreciation in the following illustration). The profit of an unforeseen contract made by ordering and supplying drinks and biscuits to a party is a gain. The business activities of such a contract is not recorded in the previous ledgers.

Expenses

The commission that you give to an employee for selling more is an example of expense. Pension contribution for an employee is an example of expense. The debts that the customers owe you can go here. The debts that you owe the suppliers can also go here.

Advertising Expenses

The money paid to advertise the convenience shop to local radio and TV stations, social media, etc.

Administrative Expenses

The rents for the shop apartment, staff salaries including yours, the proprietor (manager), office supplies (computers, phones, ledgers, pens, etc.), delivery transportation, and other travel expenses, etc.

Depreciation

A computer can be bought, a shelf was bought, a table was bought, etc. Depreciation means you divide the cost of each of such equipment over the lifetime of the equipment (until when you no longer need the asset).

Loses

The settlement of a customer lawsuit is an example of lose.

Earning(s) Before Taxes

Taxes

Any tax paid in the period.

Net Income

To grow the business, savings should come from here.

Example of Profit and Loss Statement

The net income in this hypothetical table is $1000 which is very good!

Conclusion

The minimum number of tables with minimum number of columns in 5NF are given in the example for the convenience shop. How to determine the profit or loss is also given.

You, the database developer says, “We have come to the end of the training program. Both of us are 30 years old. We still have our lives (adult) in front of us. While on vacation, I trained you and your guys. I am saving money to start my own software company in about ten years from now in maximum. I can also do installation and networking of computers.”

The proprietor, the son of the late man, reacts, “God willing, I will computerize my business in six months from now. If a profit of $1000 per month continues after removing my salary, I will be saving. In a maximum of ten months from now, I will own a supermarket in the commercial center. Hopefully in about six months from now, I will give you the contract to buy and install the computers, put the network, and do the programming.”

The proprietor continues, “You know me. I reserved some of the money that my father left for me to computerize the shop. Now, the British say, “Work without play makes Jack a dull boy”. So, I also reserved some money for entertainment. This Saturday evening, I am inviting you to the most expensive restaurant in town to eat the most expensive food and to drink the most expensive drink to celebrate our friendship and our current achievement (training).”

In the evening, they go to the restaurant. Upon departing, they did a high-five to communicate through emails, at least.