1/24/24, 8:10 AM Assignment 4 Database System and Information Retrieval

STID 3014

ASSIGNMENT 4

Please use the following GARDEN database to answer all questions below:

Location Gardener
-------- --------
LocationID Name Sunlight Water | GardenerID Name Age
---------- ---- -------- ----- | ---------- ---- ---
0 East .28 .80 | 0 Mother 36
1 North .17 .84 | 1 Father 38
2 West .38 .48 | 2 Tim 15
3 South .45 .66 | 3 Erin 12

Plant
-----
PlantID Name Sunlight Water Weight
------- ---- -------- ----- -----
0 Carrot .26 .82 .08
1 Beet .44 .80 .04
2 Corn .44 .76 .26
3 Tomato .42 .80 .16
4 Radish .28 .84 .02

Planted
-------
PlantFK GardenerFK LocationFK Date Seeds
------- ----------- ---------- --------- -----
0 0 0 04-18-2005 28
0 1 1 04-14-2005 14
1 0 2 04-18-2005 36
2 1 3 04-14-2005 20
2 2 2 04-19-2005 12
3 3 3 04-25-2005 38
4 2 0 04-30-2005 30

Picked
------
PlantFK GardenerFK LocationFK Date Amount Weight
------- ---------- ---------- --------- ------ ------
0 2 0 08-18-2005 28 2.32
0 3 1 08-16-2005 12 1.02
2 1 3 08-22-2005 52 12.96
2 2 2 08-28-2005 18 4.58
3 3 3 08-22-2005 15 3.84
4 2 0 07-16-2005 23 0.52

Some notes on terms:

 The database is for a simple garden kept by a small family

 They plant their garden in the spring and pick their garden in summer
 The sunlight attribute refers to the percentage of a 24-hour day that the location gets
sunlight and the plant optimally wants sunlight.
 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 kilograms

about:blank 1/3
1/24/24, 8:10 AM Assignment 4 Database System and Information Retrieval

 The picked amount is the number of items (one carrot, one beet, an ear of corn, one tomato,
and one radish) picked.

a) Based on the given tables, identify ONE (1) entity and ONE (1) relationship.
(2 marks)
Entity : Plant
Relationship : Planted

b) Write a valid SQL statement to create the location table.

(3 marks)

CREATE TABLE Location (LocationID INTEGER, Name VARCHAR(20), Sunlight

DECIMAL(2.2), Water DECIMAL(2,2))

c) Write a valid SQL statement to add a new plant to the plant table.
(2 marks)

INSERT INTO Plant (PlantID, Name, Sunlight, Water, Weight) VALUES (5, 'Spinach',
.45, .74, .19)

d) For some reason, the beet crop did not succeed in producing edible beets. The family wants
to eliminate beets from their garden forever. Write a valid SQL statement to delete the beet
plant from the plant table.
(1 mark)

DELETE FROM Plant WHERE PlantID= 1

e) Write a valid SQL statement that calculates the total weight of all corns that were picked
from the garden.
(2 marks)

about:blank 2/3
1/24/24, 8:10 AM Assignment 4 Database System and Information Retrieval

about:blank 3/3