Question 2 36 Marks Question Based Section 2 Block 2 Assesses Ability Transform Relational Q43828150
Question 2 (36 marks)
This question is based on Section 2 of Block 2. It assesses yourability to transform a relational representation into an E–Rconceptual model.
For the relational model below, give the correspondingentity–relationship diagram showing the relationships with theircardinality and participation conditions, the corresponding entitytype definitions, and the constraints. An entity type should not beused to represent a relationship.
relational model EventPlanner
Domains EventCodes = {e0001…e9999}
VenueCodes = {v01…v99}
CatererCodes = {ca01…ca99}
EntertainerCodes = {en01…en99}
CustomerCodes = {cu0001…cu9999}
InvoiceCodes = {0001…9999}
Dates Times = {0:00…23:59}
PeopleCount = {1…9999}
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 CatererCodereferences 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 CatererCatererCode: 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) foreignkey EventCode references Event foreign key EntertainerCodereferences Entertainer
relation Invoice
InvoiceCode: InvoiceCodes EventCode: EventCodes DatePresented:Dates primary key InvoiceCode alternate key EventCode foreign keyEventCode references Event (36 marks)
Question 3 (24 marks)
This question is based on Sections 3 and 4 of Block 2. It assessesyour ability to understand and write representations ofconstraints, and to manipulate relations and relationaloperators.
This question refers back to the relational model of Question2.
(a) Assuming the relation Event has alternate key (CustomerCode,Date), express the corresponding constraint in natural language. (2marks)
(b) Write relational expressions to express the followingconstraints:
(i) A venue used for a particular event must have a capacitygreater than or equal to the number of people expected at thatevent. (4 marks)
(ii) An event cannot have a luxury caterer in an economy venue. (5marks)
(iii) An entertainer cannot perform at more than one event on thesame date (6 marks)
(c) Write relational expressions which give:
(i) The code of each event where either there is no entertainer orwhere the number of people expected is fewer than 50. (3marks)
(ii) The code of each comedian who has not yet participated in anevent. (4 marks)
Expert Answer
Answer to Question 2 (36 marks) This question is based on Section 2 of Block 2. It assesses your ability to transform a relational…
OR