StudyDaddy Computer Science

Answered You can hire a professional tutor to get the answer.

QUESTION

Jun 23, 2019

Task 1: Return names of all courses in Information Systems undergraduate program. Task 2: Return titles and prices of textbooks for IS 420 section 1...

Task 1: Return names of all courses in Information Systems undergraduate program.

Task 2: Return titles and prices of textbooks for IS 420 section 1 in Summer 2019.

Task 3: Return total number of course sections offered by each program along with program name and program type in Summer 2019.

Task 4: Return names of courses offered in 2019 Summerand have at least 2 sections.

Task 5: Return names of authors who are co-authors of Jeff Hoffer (i.e., they are both authors of the same textbook).

Task 6: Increase quantity of textbooks used in IS 420 in Summer 2019 by 10.

Code to create the tables.

drop table textbook_schedule cascade constraints;

drop table textbook_author cascade constraints;

drop table schedule cascade constraints;

drop table course cascade constraints;

drop table textbook cascade constraints;

drop table author cascade constraints;

drop table teacher cascade constraints;

drop table program cascade constraints;

create table program

(pid int, --- program id

pname varchar(50), --- program name

ptype int, --- program type (1: undergraduate, 2: graduate)

primary key (pid));

insert into program values(1, 'Information Systems', 1);

insert into program values(2, 'Information Systems', 2);

insert into program values(3, 'Human Centered Computing', 2);

create table teacher

(tid int,

tname varchar(50),

primary key (tid)

);

insert into teacher values(1,'Dr. Chen');

insert into teacher values(2, 'Dr. Karabatis');

insert into teacher values(3,'Komlodi');

insert into teacher values(4, 'Kuber');

insert into teacher values(5, 'Dr. Janeja');

create table author

(

aid int,

aname varchar(50),

primary key (aid)

);

insert into author values

(1, 'Joel Murach');

insert into author values

(2, 'Dan Sullivan');

insert into author values

(3, 'Jeff Hoffer');

insert into author values

(4, 'Ramesh Venkataraman');

insert into author values

(5, 'Heikki Topi');

insert into author values

(6,'YVonne Rogers');

insert into author values

(7,'Helen Sharp');

insert into author values

(8,'Jenny Preece');

insert into author values

(9,'Ramez Elmasri');

insert into author values

(10,'Shamkant B. Navathe');

--- add more

create table textbook

(

tbid int, --- text book

title varchar(50), ---- title of book

publisher varchar(50), --- publisher name

edition int, --- edition, e.g., 3 means 3rd edition

quantity int, --- number of copies in store

price number, --- price of the book

primary key (tbid)

);

insert into textbook values

(1, 'Oracle SQL and PL/SQL', 'Murach Press',2,100,50);

insert into textbook values

(2, 'NoSQL for Mere Mortals', 'Addison-Wesley',1,100,40);

insert into textbook values

(3, 'Modern Database Management','Pearson',12,100,225);

insert into textbook values

(4, 'Interaction Design','Wiley',3,30,45);

insert into textbook values

(5, 'Fundamentals of Database Systems', 'Pearson',7,30,153);

create table course

(

cid int, --- course id

cname varchar(50), --- course name

pid int, --- program id

primary key (cid),

foreign key(pid) references program

);

insert into course values(1, 'IS 420: Database Application Development',1);

insert into course values(2, 'IS 410: Introduction to Database Design',1);

insert into course values(3, 'IS 620: Advanced Database Project',2);

insert into course values(4, 'IS 722: Database Integration',2);

insert into course values(5, 'HCC 629: Fundamentals of Human-Centered Computing',3);

insert into course values(6, 'HCC 729: Human-Centered Design',3);

--- insert some HCC courses

create table schedule

(sid int, --- schedule id

cid int, --- course id

snumber int, --- section number

semester varchar(10), --- Summer, fall, summer, winter,

year int, --- year of the class

tid int, --- teach id

num_registered int, --- number of registered students for this class section

primary key (sid),

foreign key (cid) references course,

foreign key(tid) references teacher

);

-- IS 420 scheduled fall 18 by Dr. Karabatis, 2 sections

insert into schedule values

(1,1,1, 'fall',2018,2,30);

insert into schedule values

(2,1,2, 'fall',2018,2,30);

--Summer 19 2 sections by Dr. Chen

insert into schedule values

(3,1,1, 'Summer',2019,1,30);

insert into schedule values

(4,1,2, 'Summer',2019,1,30);

--- IS 620 scheduled for each semester one section

insert into schedule values

(5,3,1, 'fall',2018,1,40);

insert into schedule values

(6,3,1, 'Summer',2019,2,40);

-- IS 410 scheduled for each semester

insert into schedule values

(7,2,1,'Summer',2019,5,30);

insert into schedule values

(8,2,2,'Summer',2019,5,30);

-- HCC 629 scheduled each semester

insert into schedule values

(9,5,1,'fall',2018,4,30);

insert into schedule values

(10,5,1,'Summer',2019,3,30);

-- HCC 729 scheduled each year

insert into schedule values

(11,6,1,'fall',2018,4,20);

create table textbook_author

(

aid int, --- author id

tbid int, --- text book id

primary key(aid, tbid),

foreign key(aid) references author,

foreign key(tbid) references textbook

);