CSC 4480: Principles of Database Systems
Project Proposal: Grow Flower Farm
Grow Flower Farm, a farm committed to sustainable agricultural practices, has to record its network of transactions between its vendors, products, and distributors.
Business Rules:
1. A vendor supplies seed bundles and/or material bundles to the farm and a vendor has one or many farmer contacts.
2. A vendor has a vendor ID, name, address, and phone number.
3. A farmer is a worker on the farm. A farmer may be responsible for any of the following duties: contacting/ordering from vendors, planting seeds, tending to fields, harvesting crops, and selling them to distributors.
4. A farmer has an ID, name, address, phone number, salary, and hire date. At least one farmer must be on the farm at any given time.
5. Seed bundles contain only one type of seed and are provided by a unique vendor. A bundle of seeds is unique to only one type of plant. Seed bundles have a Date Purchased, quantity, Return date, and price.
6. A field is a plot of land for one and only one type of seed at a time.
7. A field has a location ID, size, current crop type, date that crop was planted, and soil type. Soil properties determine what types of seeds can be planted.
8. When a field’s crops have matured, a harvest occurs. A harvest can be identified by a harvest date and field location it came from it also has a plant type, plant count, and expiration date.
9. One or more fields can be harvested at a time.
10. A farmer tends to zero or more fields, but each field must have at least one farmer responsible for it.
11. Harvested crops are sold to a distributor. A distributor has an ID, name, address, and phone number.
12. Each harvest can only be sold to one distributor and the distributor has a unique farmer contact.
13. In addition to seeds, vendors supply material bundles to the farm. Material bundles have a date purchased , price, quantity, and return date.
14. A seed has a seed type
15. A material bundle contains only one type of material and is unique to only one vendor. A material has a material type and quantity(how much of material farm has).
Exceptions:
1. When an individual crop dies, it will be removed from the field to preserve the lives of the surrounding crops. Hence there will be more seeds planted than actual crops harvested.
2. A farmer can return bad seed bundles or material bundles to the vendor. A seed bundle or material bundle has a return date in this case.
3. A distributor can refuse to buy a bad harvest.
Questions:
1. Which farmers are contacts with which vendors?
2. Which farmers are responsible for which fields?
3. What harvests were sold in 2013 and to which distributors?
4. What materials has the farm purchased and from whom?
5. What seeds did the farm purchase in 2012 and from whom?
6. Which farmers were responsible for each harvest sold?
Project Proposal: Grow Flower Farm
Grow Flower Farm, a farm committed to sustainable agricultural practices, has to record its network of transactions between its vendors, products, and distributors.
Business Rules:
1. A vendor supplies seed bundles and/or material bundles to the farm and a vendor has one or many farmer contacts.
2. A vendor has a vendor ID, name, address, and phone number.
3. A farmer is a worker on the farm. A farmer may be responsible for any of the following duties: contacting/ordering from vendors, planting seeds, tending to fields, harvesting crops, and selling them to distributors.
4. A farmer has an ID, name, address, phone number, salary, and hire date. At least one farmer must be on the farm at any given time.
5. Seed bundles contain only one type of seed and are provided by a unique vendor. A bundle of seeds is unique to only one type of plant. Seed bundles have a Date Purchased, quantity, Return date, and price.
6. A field is a plot of land for one and only one type of seed at a time.
7. A field has a location ID, size, current crop type, date that crop was planted, and soil type. Soil properties determine what types of seeds can be planted.
8. When a field’s crops have matured, a harvest occurs. A harvest can be identified by a harvest date and field location it came from it also has a plant type, plant count, and expiration date.
9. One or more fields can be harvested at a time.
10. A farmer tends to zero or more fields, but each field must have at least one farmer responsible for it.
11. Harvested crops are sold to a distributor. A distributor has an ID, name, address, and phone number.
12. Each harvest can only be sold to one distributor and the distributor has a unique farmer contact.
13. In addition to seeds, vendors supply material bundles to the farm. Material bundles have a date purchased , price, quantity, and return date.
14. A seed has a seed type
15. A material bundle contains only one type of material and is unique to only one vendor. A material has a material type and quantity(how much of material farm has).
Exceptions:
1. When an individual crop dies, it will be removed from the field to preserve the lives of the surrounding crops. Hence there will be more seeds planted than actual crops harvested.
2. A farmer can return bad seed bundles or material bundles to the vendor. A seed bundle or material bundle has a return date in this case.
3. A distributor can refuse to buy a bad harvest.
Questions:
1. Which farmers are contacts with which vendors?
2. Which farmers are responsible for which fields?
3. What harvests were sold in 2013 and to which distributors?
4. What materials has the farm purchased and from whom?
5. What seeds did the farm purchase in 2012 and from whom?
6. Which farmers were responsible for each harvest sold?