Save your SQL statements as a textfile. (If you should save it from Word, save it as "text with linebreaks"). Your file must be called your_matric_number.txt and must be emailed as an attachment to firstname.lastname@example.org. You will receive a confirmation that the email was received within 24 hours or by 16:00 pm if you submit it on the 28. If you have not received a confirmation by Friday, 28. March, 16:00 pm, resend your email and contact either Gordon Russell or Uta Priss by phone. (If there should be some problems with Napier's email on that Friday, alternative instructions will be posted on the module webpage.)
|cake||225g sugar, 275g flour, 175g butter, 3 eggs|
|apple cake||225g flour, 175g butter, 1 eggs, 3 apples|
|walnut cake||225g sugar, 275g flour, 175g butter, 3 eggs, 1 cup walnuts|
|fruit salad||3 apples, 3 banana, 1 cup walnuts, 2 oranges|
|fudge||1 cup milk, 500g sugar, 25g butter|
This data is stored in three object tables: dessert_table, ingredient_table and unit_table.
The unit_table consists of one column and contains the following data: 'g', 'cup', ' '. (The last one in the list is a single character space for unit-less items, such as an egg.)
The ingredient_table consists of two columns. One column for the name of the ingredient ('sugar', 'flour', 'butter', 'eggs', 'apples', 'walnuts', 'banana', 'oranges', 'milk'). The other column is a reference to the unit that is used with the ingredient. (Eggs, bananas, apples, oranges have ' ' as a unit).
The dessert_table has two columns. One contains the name of the dessert ('cake', 'apple cake', 'walnut cake', 'fruit salad' and 'fudge'). The other column is a nested table of two columns: the name of the ingredient ('sugar' etc) and the multiplier (225, etc). The objects in this table are of two types, Dessert or Cake, where Cake is a subtype of Dessert.
These three tables must be object tables. They must have primary keys. There must be no other tables apart from these three in the database. Any number of object types can be used.
The dessert type has a method "count_ingredients", which returns the number of ingredients of a dessert. (PL/SQL Hint: Nested_table.count returns the number of rows in a nested table called Nested_table.)
2. Write SQL to answer the following questions. The SQL should produce output which is de-nested and de-referenced. That means, for example, that output should be in the format
NAME ------ 'John'
NAME ----------------- name(first('John'))
Each FROM-clause should use as few tables as possible. Supply only the SQL, not the output of executing the SQL.
The SQL should produce ...
2.1 the names, ingredients and amounts used for desserts [3 points]
2.2 the names of cakes with their number of ingredients [3 points]
2.3 the names of ingredients and their units which are used to make 'apple cake' [3 points]
3. Write a sequence of drop statements that if executed one after the other deletes all of the tables and types used in this assessment. [1 point]
In total: 25 points can be achieved.