Thirdly, the report shows fully documented SQL programs that have been used to develop the system itself. All the appropriate programs used to create tables and populate them have been showed and an explanation given.
Entity and Attribute Analysis
Assumptions
The whole design and implementation of the current systems has been based on the following assumption and of course the users requirements:
- A given country always has many shops despite the financial position of the company
- It has been assumed that employee can only work in one shop. Area managers have not been considered as they can work in many shops in real life
- A shop cannot function with only one employee hence many at all times.
Identified Entities and Attributes
Following below is a list of identified entities and attributes. These have been clearly indicated in the ER diagram seen below. For easy reading all the attributes have been shown inside the entities on the diagram. All the link entities have also been shown so that all the referential integrity constraints can be followed.
Main Entities without link entities:
Shops, Country, Employees, Products, Supplier, Products
Link Entities:
Country/Shops
ER Diagram for the Frothy Coffee System
Data Dictionary
2.3 SQL Scripts For Creating Tables
Table Country
create table country
( country_id number(3) primary key,
country_name varchar2(20),
location varchar2 (20),
address varchar2 (30))
Table Shops
Table Employees
create table employees
( employee_id number(3) primary key,
employee_name varchar(15),
employee_address varchar2(30),
employee_rank varchar2(15),
employee_salary number(5),
employee_joining_date date );
Table Products
create table product
( product_id number(3) primary key,
product_name varchar2(15),
product_type varchar2(15),
product_quantity number(3),
product_origin varchar2(15),
Product_price number(5),
total_sales number (5),
shop_id number(3),
supplier_id number(3),
constraint fk_prod_shop_id foreign key (shop_id) references shops,
constraint fk_prod_supp_id foreign key (supplier_id) references supplier);
Table Supplier
create table supplier
( supplier_id number(3) primary key,
supplier_name varchar2(15),
supplier_address varchar2(30),
supplier_speciality varchar2(15) )
Table Report
create table report
( report_number number (3) primary key,
total_sales number (5),
currency varchar2 (10),
exchange_rate number(10),
report_date date,
shop_id number(3),
constraint fk_shop_id foreign key (shop_id) references shops);
2.4 SQL Scripts Used to Insert Values in the Tables
Insert into Shops
insert into shop values(05,'Malawi',8,'12-mar-89','Coffee',10,56);
insert into shop values(06,'Belgium',9,'13-apr-99','Tea',11,55);
insert into shop values(07,'Germany',8,'14-jun-89','Coffee',12,57);
insert into shop values(08,'Zambia',8,'12-jul-98','Banana',13,58);
insert into shop values(09,'Tanzania',8,'12-mar-68','Sugar',14,59);
Insert Employees
insert into employees values(57,'Hurry','109 Brook Hill rd','Manager',10000,'12-sep-99');
insert into employees values(58,'Anderson','11 Oxford Street','Assistant',5000,'12-jun-80');
insert into employees values(59,'Nedson','15 Hill side','Manager',15000,'15-sep-78');
Insert Products
insert into product values(20,'Tea','Green',46,'America',89,5000,1,00);
insert into product values(21,'Coffee','Strong',146,'Brazil',189,800,5,01);
insert into product values(22,'Sugar','Brown',546,'Malawi',50,2000,6,02);
insert into product values(23,'Coffee','Normal',746,'Scotland',40,800,7,03);
insert into product values(24,'Banana','Plantain',946,'Wales',15,5000,8,04);
Insert Supplier
insert into suppliers values(00,'Brown','23-mar-99','Coffee');
insert into suppliers values(01,'JOhn','25-mar-80','Tea');
insert into suppliers values(02,'AK','23-mar-70','Sugar');
insert into suppliers values(03,'Smith','21-sep-79','Banana');
insert into suppliers values(04,'Kenwood','11-jun-89','Coffee
Insert Report
Insert into
Values (100,’dollars’,15,’12-Dec-89’05);
Insert into
Values (101,’Pounds’,10,’15-Apr-99’06);
Insert into
Values (102,’Pounds’,20,’31-OCT-88’07);
Insert into
Values (103,’dollars’,18,’12-Jul-80’08);
Insert Country
insert into country values(10,'Malawi','Africa','21 Hebderson st');
insert into country values(11,'America','America','21 woolwich st');
insert into country values(12,'Brazil','S.America','45 victoria st');
insert into country values(13,'Zambia','Africa','21 Henderson st');
insert into country values(14,'S.Africa','Africa','Brookhill RD');
2.5 SQL Scripts for Several Queries
A. List of shops and their location
SQL> select
2 shop_name, country_name,location
3 from shops, country
4 where
5 country.country_id = shops.country_id;
SHOP_NAME COUNTRY_NAME LOCATION
--------------- -------------------- ---------
clarks malawi air port,
Malawi malawi air port,
Belgium America America
Germany Brazil S.America
Zambia Zambia Africa
Tanzania S.Africa Africa
6 rows selected.
SQL>
B. List of Employees Working in a Particular Shop
SQL> select
2 employee_name
3 from employees, shops
4 where
5 shops.shop_id = 1;
EMPLOYEE_NAME
---------------
kadzombe
Andy
Hurry
Nedson
Anderson
(Limited to one shop. A & can be used to prompt several entries)
C. Total number of Shops in Each Country
SQL> select shop_name
2 from shops, country
3 where country.country_id = shops.country_id;
SHOP_NAME
---------------
clarks
Malawi
Belgium
Germany
Zambia
Tanzania
6 rows selected.
SQL>
D. List of Employees Working for the Company for more that 12 Months
SQL> select employee_name
2 from employees
3 where employee_joining_date - sysdate >=
EMPLOYEE_NAME
---------------
kadzombe
Hurry
Nedson
Anderson
SQL>
F. Average Taking of a given shop on a given day
Totol_sales is a calculate field in the database so that it always has a value depending on the sales.
SQL> SELECT TOTAL_SALES FROM SHOPS, PRODUCT
2 WHERE SHOPS.SHOP_ID = PRODUCT.SHOP_ID;
TOTAL_SALES
-----------
5000
800
2000
800
5000
2.6 Form One Showing List of Employees Working in a Particular Shop
2.7 Report One Showing All Shops in Each Country.
