Problem: Database Querying
Garden(GardenID, GardenName, Sunligh, Water, GardenerID)
Gardener(GardenerID, GardenerName, Age)
Plant(PlantID, PlantName, Sunlight, Water, Weight_Expected)
Planted(PlantID, GardenID, Date, Seeds)
Picked(PlantID, GardenID, Date, Amount, Weight_Actual)
1. The database is for a small gardener association. The keys are underlined.
2. The gardeners plant their garden in the spring and pick their garden in the summer
3. The sunlight attribute refers to the percentage of a 24 hour/day that the location gets sunlight and the plant optimally wants sunlight.
4. The water attribute refers to the percentage of average rainfall that makes it to the root level for a location or is optimal for a plant. The plant (average expected) and picked (actual) weight is in pound
5. The picked amount is the number of items (one carrot, one beet, an ear of corn, radish) picked.
6. Express queries 1 and 4 in Algebra
7. Express all the queries in SQL.
• List the plants in John's garden
• List the gardens that have at least 1 plant in common with John's garden.
• List the gardens that have none of the plants in John's garden.
• Which plants are found in every garden?
• Give the total number of plants per garden
• List pairs of gardens that have exactly the same plants.