This discussion is archived
2 Replies Latest reply: Jan 13, 2012 7:06 AM by 910926 RSS

PLEASE ANY ONE CAN ASSIST ME ON THIS QUESTION

910926 Newbie
Currently Being Moderated
Hello am finding it difficult in

The relational schema below describes part of a database used by a hospitality company which specialises in organising business events for customers in London.

CUSTOMER(customer#, cname, address, tel, company-name)
EVENT (event#, event-name, event-type, event-date, event-time, customer#, cost)
EQUIPMENT_FOR_EVENT (event#, equipment#)
EQUIPMENT (equipment#, equipment-type, price, supplier#)
SUPPLIER (supplier#, sname, address, tel)

The following assumptions are made:

•     Customers may choose their own event name which is recorded in the ‘event-name’ attribute.
•     The ‘event-type’ attribute identifies event categories specified by the hospitality company (e.g., ‘birthday party’, ‘retirement party’).
•     Each piece of equipment has a unique number, its type (e.g. ‘camera’, ‘sound system’), and its price and supplier.


a)      Specify the sequence (order) in which the above five relations should be created and populated (you don’t need to write the actual CREATE and INSERT statements). Indicate which relational integrity rule is applicable in deciding such a sequence.

b)      Provide SQL statements to express the following queries:

(i)     Find the names of those customers who have booked an event categorised as a ‘birthday party’ for the month of July 2011.
                              
(ii)     Find the names of those customers whose events require any ‘video-cameras’.

(iii)     Find the names of those suppliers who have supplied any equipment for events which took place during December 2010.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points