Q$4.$ In Enhanced ER modelling, new notations are introduced to support inheritance of attributes

and relationships. Suppose the entertainment talent management company called

IrelandTalentProductions seeks to setup a database for the entertainers and broadcasters

which it manages. The database needs to store information about entertainers such as date of

birth, name, contact address and phone number. USING INHERITANCE, the database must

model different kinds of entertainers and their associated information. The first kind of

entertainer is of are Comedians, with attributes KindOfComedian $($e$.$g$.$ Standup Comedian,

Comic Actor, Circus Clown etc.$)$ and preferred types of work venues $($e$.$g$.$ physical events, TV$,$

Radio, Film$).$ Note a Comedian can perform as several kinds of comedian and can have several

preferred types of work venues. For Musicians the database can store the $($possibly multiple$)$

types of instruments they can play, the age ranges of the augiences for which they are most

suited and the genre$($s$)$ of music they can perform. For Broadcasters, the database stores the

kind of radio or TV show for which they are suitable. IrelandTalentProductions focuses on only

five kinds of broadcasting programmes namely current affairs, day time casual, news,

documentary and sports.

$($i$)$ Using the EER notations given in this course, develop an Enhanced Entity

Relational $($ER$)$ Model for the above database, stating any assumptions you

make.

$[8$ Marks$]$

$($ii$)$ Develop an Relational Model giving table definitions for this database and

explaining your choice of tables, primary keys and foreign keys for this

database.

$[8$ Marks$]$

$($iii$)$ MyAssistant is a virtual assistant $($conversational bot$)$ which can carry out

specific actions $($or what are termed MyAssistant Skills$).$ For example, one skill

that MyAssistant has is the ability to make room bookings for meetings. If a

user makes a request for a meeting in a particular room, MyAssistant will try

and make a room booking for that meeting. If MyAssistant succeeds, it will email all attendees $($identified as part of original meeting request$)$ to confirm

the time and room number for the meeting. Thus for each booking

MyAssistant stores the room number $($unique$)$ for the booking, the start and

stop time and the date of the booked meeting, the email address of the

requesting person who booked the room and the email addresses of those

who are attending the meeting. A second skill for MyAssistant is a reminder

service. A user can request a reminder email to be sent to him$/$her $($or if

required, to a set of users$)$ about a particular event. The reminder can only be

set to occur on a particular requested time and date.

Suppose the resultant database is modelled as the following tables :

Booking $($ BookingID, RoomNum, MeetingRequestor,

StartDateAndTime, StopDateAndTime, ReminderID$)$

BookingAttendees $($ BookingID, MeetingAttendees $)$

Reminder $($ ReminderID, ReminderMessage, ReminderDateAndTime $)$

ReminderRecipients $($ ReminderID, ReminderRecipients$)$

Give the SQL commands for the following operations

a$.$ Suppose a constraint was made whereby no meeting rooms can be booked

before $7$am or after $9$pm$.$ Give the SQL command to ensure that this

constraint is met from the time when the database was first created.

$[2$ Marks$]$

b$.$ Create a view of all the room bookings for room $101,$ their

StartDateAndTime and the attendees who were invited to attend those

room bookings

$[3$ Marks$]$

c$.$ Suppose a MeetingAdmin table is created which consists of two attributes,

BookingID and MeetingAttendeeNumbers. Write a trigger command to

insert a row into the MeetingAdmin table when any meeting booking has

been inserted with or updated with more than $20$ Meeting Attendees

$[4$ Marks$]$