Appendix D: Database design for milk marketing system
CASE STUDY - IV
DATABASE DESIGN FOR MILK MARKETING SYSTEM
D.1 Problem description
Management of a dairy plant is interested to computerize its milk marketing operations to bring efficiency in the system. After discussion with the plant management and milk marketing section in-charge, a report of system investigation prepared by system analyst is given below:
Plant is manufacturing and selling 10 types of milk products in addition to liquid milk of two types namely full cream milk and toned milk. There are two types of sale outlets one owned and maintained by employees of the plant and others maintained by agents on commission basis. The agents have to deposit some token amount as security money in advance to the plant for running sale outlets. Few sale outlets are selling only liquid milk while others are selling liquid milk as well as milk products. Milk and products are dispatched to sale outlets daily in the morning through plant owned vehicles. Unsold liquid milk is collected back from sale outlets for reprocessing. Vehicles operate on different specified routes defined by plant covering all sale outlets. Milk market section produces following reports for management of the plant:
I. Daily sale proceeds of liquid milk and milk products route wise and sale outlet wise.
II. Commission to be paid to agents.
III. Product wise sale proceeds in a given time period.
As a database designer, designed a suitable and effective database to meet the present and future requirements of the plant. A sample of database design for milk marketing section is given below considering the requirement of dairy plant. The rest process is same as followed in previous case study.
Table D.1 Detail of sale point employees
|
Fields |
Data type |
Data size |
|
Sempno |
Text |
3 |
|
Sempname |
Text |
50 |
|
Amntdipos |
Number |
Integer |
|
Empstatus |
Yes/No |
Logical |
|
Empaddrs1 |
Text |
50 |
|
Empaddrs2 |
Text |
20 |
|
Empphone |
Text |
20 |
Table D.2 Product information
|
Pcode |
Text |
3 |
|
Pname |
Text |
30 |
|
Prate |
Number |
Real 2 decimal place |
|
Pcommission |
Number |
Integer |
Table D.3 Route information
|
Rcode |
Text |
3 |
|
Rdesc |
Text |
50 |
Table D.4 Sale point information
|
Spcode |
Text |
3 |
|
Sempno |
Text |
3 |
|
Spname |
Text |
50 |
|
Spstatus |
Yes/No |
Logical |
|
Prdsale Products |
Number |
Type of sale 1-Milk; 2-Milk |
|
Spadd fields) |
Text |
100 (can be broken into 2/3 |
|
Spphone |
Text |
20 |
Table D.5 Route and sale point interaction
|
Rcode |
Text |
3 |
|
Spcode |
Text |
3 |
Table D.6 Detail of per day sale
|
Saledt |
Date |
Short |
|
Spcode |
Text |
3 |
|
Pcode |
Text |
3 |
|
Qtysupplied |
Number |
Integer |
|
Qtyreturned |
Number |
Integer |
|
Qtysold |
Number |
Integer |
Students are expected to identify the primary key for each table, data validation checks for different attributes, reports, queries etc. themselves to make this database workable and useful to users.