CS 448/648 Assignment 2 Solutions

(a)

select firstnme, midinit, lastname, salary, year(hiredate-birthdate) as age
from employee
where job = 'MANAGER'
order by lastname, firstnme, midinit

select M.lastname, deptname, E.job, count(E.empno) as num_emp
from employee M, department, employee E 
where M.empno = mgrno and E.workdept = deptno 
	and E.job != 'MANAGER' 
group by M.empno, M.lastname, deptname, E.job

select projname, R.lastname as responsible, 
		E.lastname as most_senior, M.lastname as manager 
from project P, employee R, department D, employee E, employee M 
where respemp = R.empno 
and D.deptno = P.deptno and respemp != mgrno and mgrno = M.empno 
and respemp != E.empno and E.empno in 
	(select E2.empno 
	 from employee E2, emp_act A 
	 where E2.empno = A.empno 
	 and A.projno = P.projno 
	 and E2.hiredate <= all 
		(select E3.hiredate 
		 from employee E3, emp_act X 
		 where E3.empno = X.empno 
		 and X.projno = A.projno) 
	)

(select P.projname, 
	integer(P.prstaff*(year(P.prendate-P.prstdate)*12 + 
			   month(P.prendate-P.prstdate))) as p_months, 
	sum (integer(S.prstaff*(year(S.prendate-S.prstdate)*12 + 
			   month(S.prendate-S.prstdate)))) as s_months 
 from project P, project S 
 where P.projno = S.majproj 
 group by P.projno,P.projname,P.prstaff,P.prendate,P.prstdate 
 having count(*) < 3) 
union all 
(select projname, 
	integer(prstaff*(year(prendate-prstdate)*12 + 
			   month(prendate-prstdate))) as p_months, 
		0 as s_months 
 from project 
 where projno in 
	((select A.projno 
	 from project A) 
	 except 
	(select M.majproj 
	 from project M)) 
)

drop table time_slot
create table time_slot ( 
  id char(6) not null primary key, 
  start_time time not null, 
  end_time time not null, 
  constraint valid_times check ( 
	second(start_time) = 0 and (minute(start_time) in (0,15,30,45)) 
	and second(end_time) = 0 and (minute(end_time) in (0,15,30,45)) 
  ), 
  constraint other_key unique(start_time, end_time) 
)

drop table network
create table network ( 
  name char(3) not null primary key 
)

drop table tv_channel
create table tv_channel ( 
  number smallint not null primary key, 
  city char(20), 
  type char(1) not null, 
  owner char(15), 
  network_name char(3) references network, 
  constraint number_range check (number between 2 and 99), 
  constraint type_val check ( 
	(upper(type) in ('N','I')) 
	and (upper(type) = 'I' or network_name is not null) 
  ) 
)

drop table person
create table person ( 
  name char(20) not null primary key, 
  date_of_birth date, 
  sex char(1), 
  constraint valid_sex check ( 
	upper(sex) in ('M','F') 
  ) 
)

drop table actor
create table actor ( 
  name char(20) not null primary key references person, 
  exclusive_network char(3) references network 
)

drop table tv_program
create table tv_program ( 
  name char(25) not null primary key, 
  genre char(10), 
  intended_audience char(10), 
  rating smallint, 
  length smallint, 
  constraint valid_rating check (rating between 1 and 5), 
  constraint valid_length check (length > 0) 
)

drop table program_slot
create table program_slot ( 
  slot char(6) not null references time_slot, 
  channel smallint not null references tv_channel, 
  constraint slot_key primary key (slot,channel) 
)

drop table appears_on
create table appears_on ( 
  slot char(6) not null, 
  channel smallint not null, 
  program_name char(25) not null references tv_program, 
  constraint slot_key primary key (slot,channel,program_name), 
  constraint slot_ref foreign key (slot,channel) references program_slot 
)

drop table stars_in
create table stars_in ( 
  program_name char(25) not null references tv_program, 
  actor_name char(20) not null references actor, 
  constraint appears_key primary key (program_name,actor_name) 
)

delete from stars_in
delete from appears_on
delete from program_slot
delete from tv_program
delete from actor
delete from person
delete from tv_channel
delete from network
delete from time_slot

insert into time_slot ( 
   values ('1200h','12.00.00','12.30.00'), 
   	('1200f','12.00.00','13.00.00'), 
   	('1230f','12.30.00','13.00.00'), 
   	('1800f','18.00.00','19.00.00'), 
   	('2000f3','20.00.00','23.00.00') 
)
select * from time_slot

insert into network ( 
   values ('cbc'),('ctv'),('tvo'),('abc'),('nbc'),('cbs') 
)
select * from network

insert into tv_channel ( 
   values (2,'toronto','n',null,'tvo'), 
	(3,'toronto','i','global',null), 
	(5,'toronto','n',null,'cbc'), 
	(10,'hamilton','i','CHCH',null), 
	(12,'kitchener','n',null,'ctv') 
)
select * from tv_channel

insert into person ( 
  values ('robert redford','1945-10-12','m'), 
	('judi dench','1943-03-30','f'), 
	('joan rivers','1938-01-21','f'), 
	('johnny depp','1965-12-01','m') 
)
select * from person

insert into tv_program ( 
  values ('Lost revengers','drama','adult',3,180), 
	('Kids no more','education','children',5,60), 
	('Who did it?','mystery','all',2,60), 
	('Kids know less','cartoon','children',1,30) 
)
select * from tv_program

drop view slot_lengths
create view slot_lengths as 
  select id, start_time, 
		hour(end_time-start_time)*60 + minute(end_time-start_time) as length 
  from time_slot
select * from slot_lengths

rop view slot_lengths
create view slot_lengths as 
  (select id, start_time, 
       hour(end_time-start_time)*60 + minute(end_time-start_time) as length 
  from time_slot 
  where hour(end_time-start_time)*60+minute(end_time-start_time) >=0) 
union 
  (select id, start_time, 
     hour(end_time-start_time)*60 + minute(end_time-start_time)+1440 as length 
  from time_slot 
  where hour(end_time-start_time)*60+minute(end_time-start_time) <0 )

drop view exclusive_slots
create view exclusive_slots as 
  select * from slot_lengths 
    where id not in ( 
	select I.id 
  	from time_slot O, time_slot I 
  	where (I.start_time > O.start_time and I.start_time < O.end_time 
  		or I.start_time = O.start_time and I.end_time < O.end_time) 
	and	I.id != O.id )
select * from exclusive_slots

delete from appears_on
delete from program_slot

insert into program_slot ( 
  select id, number 
  from time_slot, tv_channel 
)
select * from program_slot

insert into appears_on ( 
  select distinct program_slot.*,name 
  from program_slot, tv_program 
  where intended_audience != 'children' and slot in ( 
		select L.id from exclusive_slots L 
			where start_time >= '17.00.00' 
			and	L.length >= tv_program.length 
			and	L.length < tv_program.length + 30) 
)
insert into appears_on ( 
  select distinct program_slot.*,name 
  from program_slot, tv_program 
  where intended_audience = 'children' and slot in ( 
		select L.id from time_slot, exclusive_slots L 
			where end_time <= '20.00.00' 
			and	L.length >= tv_program.length 
			and	L.length < tv_program.length + 30) 
)
select * from appears_on

delete from stars_in
delete from actor

insert into actor ( 
  select name, 'ctv' from person 
)
update actor 
  set exclusive_network = null
update actor 
  set exclusive_network = 'cbc' 
  where name in ( 
	select name from person 
	where date_of_birth < '1950-01-01' 
)
update actor 
  set exclusive_network = 'nbc' 
  where upper(name) like '%R%'
select * from actor

insert into stars_in ( 
  select p.name,a.name 
  from tv_program p, actor a 
  where (exclusive_network != 'cbc' or exclusive_network is null) 
  and	rating >3 
)
select * from stars_in

drop view appears
create view appears as 
  select actor_name, channel, start_time 
  from stars_in, appears_on, slot_lengths 
  where stars_in.program_name = appears_on.program_name 
  and	slot = id
select * from appears

select name, exclusive_network, start_time, channel, network_name as wrongly 
  from actor, appears, tv_channel 
  where	name = actor_name 
  and	channel = number 
  and	exclusive_network != network_name 
  order by name, channel