— an event management database
drop table registration cascade constraint;
drop table participant cascade constraint;
drop table event cascade constraints;
drop table organizer cascade constraints;
drop table location cascade constraints;
create table location
(lid int, —- location id
lname varchar(50), — location name
capacity int, — capacity of the location
primary key (lid));
insert into location values (1, ‘ITE 459’, 40);
insert into location values (2, ‘UC ballroom’, 300);
insert into location values (3, ‘Event Center’,3000);
create table organizer
(oid int, — organizer id
oname varchar(50), — organizer name
oemail varchar(50), — organizer’s email
otype int, — 1 university, 2 department, 3 student
primary key(oid));
insert into organizer
values (1, ‘Dr. B’, ‘ccc@abc.edu’, 2);
insert into organizer
values (2, ‘Ms. C’, ‘mmm@abc.edu’, 1);
insert into organizer
values (3, ‘Ellen’, ‘eee@abc.edu’,3);
create table event
(eid int, — event id
oid int, — organizer id
ename varchar(50), — event name
lid int, — location id
start_time timestamp, — start time of event
duration interval day to second, — duration of event,
status int, — 1 scheduled, 2 canceled, 3 finished
primary key(eid),
foreign key (lid) references location,
foreign key (oid) references organizer);
insert into event values (1, 1,’Department seminar’, 1, timestamp ‘2018-9-6 10:00:00.00’,interval ‘2’ hour, 3);
insert into event values(2,2, ‘2018 Graduate Commencement’, 3,
timestamp ‘2018-12-19 10:00:00.00’, interval ‘2’ hour, 1);
insert into event values(3, 3,’Ellen dissertation defenes’, 1,
timestamp ‘2018-11-16 14:00:00.00’, interval ‘2:30′ hour to minute, 1);
insert into event values(4, 2,’Teaching Seminar’, 2,
timestamp ‘2018-11-10 12:00:00.00’, interval ‘1:30’ hour to minute, 2);
create table participant
(pid int, — participant id
pname varchar(50), — participant name
pemail varchar(50), — participant email
primary key(pid));
insert into participant values(1, ‘Jeff’, ‘jeff@abc.com’);
insert into participant values(2, ‘Grace’, ‘grace@abc.edu’);
insert into participant values(3, ‘Ethan’, ‘ethan@abc.edu’);
insert into participant values(4, ‘Susan’,’susan@abc.edu’);
create table registration
(pid int, — participant id
eid int, — event id
status int, — 1 registered, 2 canceled
primary key(pid, eid),
foreign key(pid) references participant,
foreign key(eid) references event);
insert into registration values(1, 1, 1);
–insert into registration values(4, 1, 1);
insert into registration values(2, 2, 1);
insert into registration values(3, 2, 2);
insert into registration values(4, 2, 1);
insert into registration values(3, 3, 1);
insert into registration values(4, 3, 2);
insert into registration values(1,4,2);
insert into registration values(3,4,2);
commit;
Please write an anonymous PL/SQL program to print out the email of organizer of the event ‘Department seminar’ as well as start_time and end time of the event. Please use implicit cursor and handle exception. You will lose 10 points if you use an explicit cursor.
Hint: The end time of an event equals start_time + duration.
