Let’s take a real-life example that could use a database in its daily operations. A friend started a small business of selling lady undergarments. It being a small business, an excel sheet was enough to keep her records where she updated the data weekly. The data looked a little something like this.
Overtime with the growth of the business, data grew and was too much to be input on an excel sheet weekly. Some transactions were not recorded hence the math was not adding up. With the help of a database, her work would have been easier.
So, back to the main question …what a database is and what does it do?
Here is some of the required information she needs from the data in the spreadsheet:
- Current stock.
- Frequent customers.
- The fast moving product.
Let’s have a relational diagram
Using SQL commands to retrieve information.
Let’s create a database called garments:
CREATE DATABASE garments;
After creating the database we are going to create various tables under the database.
These are the tables we are going to create; products, transactions, customers;
Create Products table
CREATE TABLE products (
product_id serial primary key,
product_name varchar(50) not null,
stock_status integer not null,
product_price real not null
);
Inserting data in the Products table
INSERT INTO products (product_id, product_name, stock_status, product_price) VALUES
(1, 'Cotton Underwear', 1, 200),
(2, 'Lace Underwear', 1, 300),
(3, 'Brassier', 1, 400);
Create Customers table
CREATE TABLE customers (
customer_id serial primary key,
customer_name varchar(50) not null,
details text
);
Inserting data in customers table
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Mary'),
(2, 'Joyce'),
(3, 'Esther');
Creating the Purchases table
CREATE TABLE purchases (
purchase_id serial primary key,
product_id integer references products,
quantity integer not null,
purchase_price real not null,
purchase_date date default current_date
);
Inserting data in purchases table
INSERT into purchases(product_id, quantity, purchase_price, purchase_date) VALUES
(3, 40, 650, '2018-08-09'),
(2, 60, 500, '2018-03-11'),
(1, 70, 150, '2018-04-20');
Creating the sales table
CREATE TABLE sales (
sale_id serial primary key,
product_id integer references products,
customer_id integer references customers,
quantity integer not null,
sale_price real not null,
sale_date date default current_date
);
Inserting data in the sales table
INSERT into sales(product_id, customer_id, quantity, sale_price, sale_date) VALUES
(3, 3, 3, 200, '2018-09-20'),
(1, 2, 5, 500, '2018-10-13'),
(2, 1, 5, 350, '2018-10-15');
From this information we want solve some of the business persons’ issues
For instance knowing;
- customer variation
- Product Variation
- Current Stock
</em
Businesses would be healthier and more productive if they invested more in having a database. We should embrace digitization in our businesses especially when it comes to the storing of data and records.
Great Idea