ASSESSMENT 01:
Mr. Emmanuel Kwame Andoh wants to start a mobile money vending business with 6 branches across the country. He wants to be able to have information on all the transactions that happen in any of his branches. He describes a typical daily activity per worker as follows:
A customer or a person walks in and requests to do either a withdrawal or a deposit. Then his worker would record the name and number of the person, the type of transaction, the amount involved in the transaction, and the transaction reference number.
Mr. Andoh provided all the information above and more to a database designer who simply supplied just the SQL script that would implement the system. But the designer did not explain how the system would work to Mr. Andoh.
# Below is the SQL Script: You can copy and paste to run
CREATE DATABASE pharmacy;
USE pharmacy;
CREATE TABLE branch (
id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE transaction_type (
id INT(1) NOT NULL AUTO_INCREMENT PRIMARY KEY,
type VARCHAR(15) NOT NULL
);
CREATE TABLE network (
id INT(2) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL
);
CREATE TABLE worker (
id INT(3) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(30) NOT NULL,
lname VARCHAR(30) NOT NULL,
contact VARCHAR(13) NOT NULL
);
CREATE TABLE worker_branch (
worker_id INT(3) NOT NULL REFERENCES worker(id),
branch_id INT(3) NOT NULL REFERENCES branch(id),
PRIMARY KEY (worker_id, branch_id)
);
CREATE TABLE transaction (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
worker_id INT(3) NOT NULL REFERENCES worker(id),
branch_id INT(3) NOT NULL,
customer_no VARCHAR(13) NOT NULL,
customer_id INT(25)
);
You have been tasked to use the SQL script provided above by the database designer to explain how the system works by providing the following:
1. The list of entities identified by the database designer
2. Provide an ER diagram indicating all the relationships using UML
3. Write a query to insert the following records:
a. Deposit and Withdrawal into the transaction_type table as types
b. Kumasi, Accra, Tamale, Bono Ahafo, and Tema as branch names in the branch table
