42009 Coursework

Deadline: Friday, 28. March, 12:00 pm.

General Instructions

Write SQL statements for the database specification that is described below. Your SQL code should be well formatted and have comments where appropriate. There should be no other text in your file apart from SQL code or comments. Note that you will receive more points if your SQL code can be executed without errors but does not achieve everything that is asked for, than if you attempt to implement everything but your code cannot be executed.

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 u.priss@napier.ac.uk. 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.)

The Database Specification

A recipe database contains 5 recipes:

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.)

Specific Instructions

1. Create the types as required. [15 points]

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' 

instead of

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.