Question Three [30 marks]

Ahalia sports club stores information about the sports activities they conduct regularly. The members of the club use items such as helmets, pads etc. supplied by the center. These items belong to different types and categories and they are supplied by different suppliers. Every sports activity is identified by an activity code, activity name, and activity description. The supplier details such as supplier code, supplier name, supplier address and supplier email are stored by the centre. Every category of item is identified by an Item Category code and Item Category name. Every item type is identified by an Item Type code, Item Type name and Item Category code. Every item is identified by an Item id, Item name, Item Type code, and Item description. Every item is supplied by one or more supplier, and a supplier supplies one or more items. When a supplier supplies an item, the quantity, price and the data of supply is recorded by the club. Every item is used in one or more sports activity and in a sports activity, one or more items are used. When an item is used in a sports activity, the hours of usage must be considered.

Explain each query in your own words.

1. Write the schema of the appropriate relations for the above scenario. Identify the primary key and the foreign key in each of the relations. [6 marks]
2. Use the designed relations to write SQL statements for the following questions: -

1. Display all the items having the same price from the supply relation in the ascending order of Item price. [4 marks]
2. List the number of item types in each category. Only include categories with more than 3 items. [4 marks]

3. Display the details of items that is not yet used in a sports activity. [4 marks]

4. Display all the items supplied by the supplier Waleed. [5 marks]

5. Display the names of all item types with their category names. [4 marks]

6. Display the details of sports activities with activity name starting with other than any letter within 'M' and 'Z'. [3 marks]