CASE STUDY - III

DATABASE DESIGN FOR MILK PROCUREMENT AND BILLING SYSTEM

C.1 Description of the problem

A Model dairy plant is procuring milk from milk shed area near the plant. Dairy plant has established a number of milk collection centers in the area. Each collection center is covered by a specified route. Milk is collected only in the morning and is directly transported to dairy plant. Only fresh milk of either buffalo or cow is accepted and payment is made on the basis of Fat% and SNF% content of milk. Collection centers are maintained by dairy plant and various technical inputs in the form of veterinary aid, artificial insemination facilities, feed subsidy, etc. are provided to the societies for smooth collection of milk. Management is interested to computerize the milk procurement and billing system in order to bring efficiency in the system and to provide better services to societies/ members. Following reports are generated for the societies and management of the plant:

Milk collection report for each collection center.
Bill for each collection center for given period.
Summary report of total milk procured and Transportation cost route wise.
Milk collection cost collection center wise for given period.
Milk procurement cost route wise and overall procurement cost. Procurement cost includes all kind of expenditures incurred to maintain collection centers and transportation.

After studying the system and in order to produce above mentioned reports, various input data has been collected and placed in different files/tables as given below:

Table C.1: Collection Centers
	Field Name	Data Type	Field Size / Format	Description/ Validation Checks
*	ccno	Text	3	Collection Centre Number
	ccName	Text	20	Collection centre name
	inchargeName	Text	20	Incharge / Supervisor name
	ccAddress	Text	20	Collection centre address

Table C.2: Expenditure Details
	Field Name	Data Type	Field Size/ Format	Description/ Validation Checks
*	ccno	Text	3	Values for this field should be taken from Table Collection Centers
*	expenditureDate	Date/Time	Short Date	Date of Expenditure Incurred
	salary	Number	Integer	Salary of Employee
	maintenance	Number	Integer	Expenditure on chemicals, consumables, fixed cost, etc.
	vetaid	Number	Integer	Expenditure on veterinary aid
	ai	Number	Integer	Expenditure on artificial insemination
	feedSubsidy	Number	Integer	Expenditure on feed subsidy given
	extensionActivity	Number	Integer	Expenditure on ext. activity like educational shows, trade fairs, visits etc.
	otherExpenditure	Number	Integer	Other Expenditure on unforeseen activities

Table C.3: Milk Receipts
	Field Name	Data Type	Field Size/ Format	Description/ Validation Checks
*	ccno	Text	3	Values for this field should be taken from Table Collection Centers
*	receiptDate	Date	Short Date	Higher than 1/1/2012
*	milkType	Text	2	CM- Cow Milk; BM-Buffalo Milk
	quantity	Number	Integer	Between 0 to 2000, Default value 0
	fat	Number	Single (Decimal: 1)	Between 3.0 equal to 10.0
	snf	Number	Decimal (1 digit)	Between 7.0 equal to 12.0
	qualityNormal	Logical	Yes/ No	Quality of milk is normal or not (Yes/No)

Table C.4: Route Details
	Field Name	Data Type	Field Size/ Format	Description/ Validation Checks
*	routeno	Text	2	Route number
	description	Text	20	Route description
	rate	Number	Single (Decimal: 2)	Running expenditure per km for running a vehicle on the route

Table C.5: Distance Covered
	Field Name	Data Type	Field Size/ Format	Description/ Validation Checks
*	routeno	Text	2	Values for this field should be taken from table Route Details
*	rtDate	Date/Time	Short Date	Date of transporting milk. Transportation date be checked from milk receipt date.
	distance	Number	Integer	Distance traveled. It cannot be more than 100 km on single route in one day.

Table C.6: Route CC
	Field Name	Data Type	Field Size/ Format	Description/ Validation Checks
*	routeno	Text	2	Values for this field should be taken from table Route Details
*	ccno	Text	3	Collection centre falling on the specific route. Values for this field should exist in table Collection Centers

1. Design a database using above mentioned tables with proper labels for each input data and following validation checks:

a) Collection centre and Route numbers should be valid numbers i.e. before entering these numbers in transaction files they must exist in master files.

b) Type of milk should be either BM or CM.

c) Fat% and SNF%, rate per kilometer and distance traveled must be in a specific range.

2. Define suitable relationship among the created tables based on key field.

3. Create forms for data entry for each table. Use the concept of sub-form if needed for display of data/ data entry.

4. Enter data in tables for at least 15 days of the receipt of milk from 7 societies distributed on 3 routes. There is one entry of expenditure in a month for each society.

5. Sort data (from milk receipt table) on collection centre number, date of receipt of milk etc.

6. Filter data (from milk receipt table) by selection/ form for a particular collection centre number or date of receipt of milk.

7. Create following queries for retrieving data from above tables:

a) Ddisplay data on the fields ccno, inchargeName, receiptDate, milkType, quantity, fat, snf, fat-kg, snf-kg for given dates and/or ccno.

b) Display data on fields ccno, ccName, inchargeName, routeNo, and description from Collection Centers and Route Details tables for which In-charge name starts with alphabet “R”. Arrange the In-charge names alphabetically (i.e. ascending order) in the output.

c) Summation/ summary of milk received Collection Center wise.

d) Summation/ summary of milk received route wise.

e) Total expenditure report collection center wise with center name, address and expenditure date for given period.

f) Cross tabulate quantity of milk received by collection centre wise and type of milk.

g) Create new table with fields ccno, ccname, routeno, route description from existing tables.

h) Create a delete query for deleting records from milk receipt table for a given ccno.

i) Update the field ratekm with ratekm +2.0 for all routes in Route Details table.

8. Generate the above said reports with suitable formats.