HW3 Solution - Information and Database Management Systems...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
Information and Database Management Systems I (CIS 4301) (Spring 2008) Instructor: Dr. Markus Schneider TA: Ganesh Viswanathan Wenjie Yuan Homework 3 Solutions Name: UFID Email Address: Pledge (Must be signed according to UF Honor Code) On my honor, I have neither given nor received unauthorized aid in doing this assignment. _______________________________________________Signature For scoring use only: Maximum Received Exercise 1 30 Exercise 2 30 Exercise 3 40 Total 100
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Exercise 1 (SQL) [30 points] Consider the AGRI-FIELD database 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. The picked amount is the number of items (one carrot, one beet, an ear of corn, one tomato, one radish) picked. I. Create the following instance of the database schema in Oracle SQL. Refer to the class webpage for instructions on connecting to the CISE Oracle database. Use appropriate data-types to match the provided schema. Provide the spool log file showing all SQL commands. [6 points] 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 Halley 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 Date1 Seeds ------- ---------- ---------- ---- ----- 0 0 0 03-18-2008 28 0 1 1 03-14-2008 14 1 0 2 03-18-2008 36 2 1 3 03-14-2008 20 2 2 2 03-19-2008 12 3 3 3 03-25-2008 38 4 2 0 03-28-2008 30
Background image of page 2
Picked ------ PlantFK GardenerFK LocationFK Date2 Amount Weight ------- ---------- ---------- ---- ------ ------ 0 2 0 03-18-2008 28 2.32 0 3 1 03-16-2008 12 1.02 2 1 3 03-22-2008 52 12.96 2 2 2 03-28-2008 18 4.58 3 3 3 03-22-2008 15 3.84 4 2 0 03-16-2008 23 0.52 Create the tables using the appropriate SQL commands and apply the referential integrity constraints. DATE may be custom formatted using the command: SQL> ALTER SESSION SET NLS_DATE_FORMAT='mm-dd-yyyy'; II. Now answer the following queries, based on the above schema [1-4: 3 pts each. 5-7: 4 pts each.]: 1. Write a valid SQL statement that calculates the total weight of all ears of corn that were picked from the garden. Show the result set. SQL> SELECT
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 09/10/2008 for the course CIS 4301 taught by Professor Schneider during the Spring '08 term at University of Florida.

Page1 / 10

HW3 Solution - Information and Database Management Systems...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online