Question 2 (36 marks) This question is based on Section 2 of Block 2. It assesses your ability to transform a relational representation into an ER conceptual model. For the relational model below, give the corresponding entityrelationship diagram showing the relationships with their cardinality and participation conditions, the corresponding entity type definitions, and the constraints. An entity type should not be used to represent a relationship. relational model EventPlanner Domains EventCodes = {e0001...e9999}

VenueCodes = {v01v99}

CatererCodes = {ca01ca99}

EntertainerCodes = {en01en99}

CustomerCodes = {cu0001cu9999}

InvoiceCodes = {0001...9999}

Dates Times = {0:0023:59}

PeopleCount = {19999}

PriceBands = {economy, midprice, luxury}

EntertainerTypes = {singer, band, magician, comedian} Names = string relation Event EventCode: EventCodes CustomerCode: CustomerCodes Date: Dates StartTime: Times EndTime: Times NumberOfPeopleExpected: PeopleCount VenueCode: VenueCodes CatererCode: CatererCodes primary key EventCode foreign key VenueCode references Venue foreign key CatererCode references Caterer relation Venue VenueCode:VenueCodes Capacity: PeopleCount PriceBand : PriceBands primary key VenueCode constraint ((project Venue over VenueCode) difference (project Event over VenueCode)) is empty relation Caterer CatererCode: CatererCodes MaximumNumberOfPeopleCanCaterFor: PeopleCount PriceBand: PriceBands primary key CatererCode 3 relation Entertainer EntertainerCode: EntertainerCodes Name:Names EntertainerType: EntertainerTypes primary key EntertainerCode relation EntertainedBy EventCode: EventCodes EntertainerCode: EntertainerCodes primary key (EventCode, EntertainerCode) foreign key EventCode references Event foreign key EntertainerCode references Entertainer relation Invoice InvoiceCode: InvoiceCodes EventCode: EventCodes DatePresented: Dates primary key InvoiceCode alternate key EventCode foreign key EventCode references Event (36 marks) Question 3 (24 marks) This question is based on Sections 3 and 4 of Block 2. It assesses your ability to understand and write representations of constraints, and to manipulate relations and relational operators. This question refers back to the relational model of Question 2. (a) Assuming the relation Event has alternate key (CustomerCode, Date), express the corresponding constraint in natural language. (2 marks) (b) Write relational expressions to express the following constraints: (i) A venue used for a particular event must have a capacity greater than or equal to the number of people expected at that event. (4 marks) (ii) An event cannot have a luxury caterer in an economy venue. (5 marks) (iii) An entertainer cannot perform at more than one event on the same date (6 marks) (c) Write relational expressions which give: (i) The code of each event where either there is no entertainer or where the number of people expected is fewer than 50. (3 marks) (ii) The code of each comedian who has not yet participated in an event. (4 marks)