In a company database, details of the customers, the products sold and the orders made by customers are stored in the relations Customer, Product and Order, respectively:
Customer
| cid | cname | county |
| c001 | Jones | Essex |
| c002 | Smith | Surrey |
| c003 | Green | Kent |
| c004 | Williams | Kent |
Product
| pid | pname | county | stock | price |
| p01 | paint | Sussex | 1114 | 0.50 |
| p02 | wood | Kent | 2030 | 0.50 |
| p03 | nails | Surrey | 1506 | 1.00 |
| p04 | hammer | Kent | 1253 | 1.00 |
| p05 | screws | Essex | 2214 | 1.00 |
Order
| ordno | month | cid | pid | quantity |
| 1011 | jan | c001 | p01 | 100 |
| 1012 | jan | c001 | p02 | 100 |
| 1019 | feb | c001 | p04 | 40 |
| 1023 | mar | c001 | p03 | 50 |
| 1013 | jan | c002 | p03 | 100 |
| 1026 | may | c002 | p03 | 80 |
| 1015 | jan | c003 | p04 | 120 |
| 1014 | jan | c003 | p02 | 120 |
| 1021 | feb | c004 | p01 | 100 |
1. Explain the following terms in the context of the relational model.
(a) Candidate key
(b) Primary key
(c) Alternate key
(d) Foreign key
2. One of the integrity rules of relational models is called referential integrity.
(a) Explain what it refers to
(b) Using the given company database, discuss why it is desirable to enforce the referential integrity.
3. For each of the following queries in relational algebra:
a - c: see image.
4. Write cach query in relational algebra using the given relations Customer, Product and Order:
(a) List all orders made in January
(b) Find the names and prices of all products made in Kent.
(c) Retrieve the order numbers, months, customer IDs and quantities of all orders placed on the product 'hammer'.