The following computer science department and undergraduate course descriptions schema are extracted from an university with some modifications to make suitable for this homework. This information includes Staff and courses. The descriptions of faculties from computer science department includes their position, phone, email address, education, research interests, and selected publications.
To find the time when the final exam for a course is scheduled, do the following:
1. Determine the beginning time for a course. Use lecture beginning time rather than the recitation times.
2. Read to find the time and day for your final exam using the beginning time for the course.
The database you created should contain the following ten tables:
table department : department information
deptabbre varchar(10) : department abbreviation such as CSci and Phys
location varchar(20) : building name such as EE/CSci Bldg
office varchar (20) : room number
table faculty : faculty information
ssn char (9) : social security number
fname varchar(10) : first name
mname varchar(10) : middle name
lname varchar(10) : last name
emailaddr varchar(20) : email address
deptcode varchar(10) : department abbreviation such as CSci and Phys
deptposition varchar (20) : faculty position such as assistant professor
phone char(7) : phone number
table course : course information
course char(4) : course id number
title varchar(255) : course title
deptabbre varchar(10) : department abbreviation such as CSci and Phys
description varchar(255) : course description
table section: course sections
call char(6) : course call number
section_no int : section number
quarter varchar(6) : quarter which a course is offered
year varchar(4) : year which a course is offered
lweekday varchar (5) : lecture meeting days
llocation varchar(20) : lecture meeting place (building)
lroom varchar(6) : lecture meeting room
lstart char(4) : lecture starting time
lend char(4) : lecture ending time
rweekday varchar(5) : recitation meeting days
rlocation varchar(20) : recitation meeting place (building)
rroom varchar(6) : recitation meeting room
rstart char(4) : recitation starting time
rend char(4) : recitation ending time
deptabbre varchar(10) : department abbreviation such as CSci and Phys
course char(4) : course id number
fssn char(9) : faculty social security number
fid int : final exam id
finalschedule : final exam schedules
fid int : final exam id
quarter varchar(6) : quarter which a course is offered
year varchar(4) : year which a course is offered
timebegin varchar(4) : lecture starting time
timeend varchar(4) : lecture ending time
daybegin varchar(3) : lecture days
examtime char(9) : final exam time
examdate varchar(15) : final exam date
publication : publication list
pid int : publication id
title varchar(255) : publication title
journal varchar(50) : journal that a paper is appeared
volume int : volume number
month char(4) : month of the publication
year char(4) : year of the publication
prerequisite : prerequiistes of course information
deptabbre varchar(10) : department abbreviation such as CSci and Phys
courses char(4) : course id number
preabbre varchar(10) : prerequisite's department abbreviation
precourse char(4) : prerequisite course id number
publishedby : relationships between faculty and publication
pid int : publication id
fssn char(9) : faculty social security number
education : education background of each faculty
fssn char(9) : faculty social security number
degree varchar(10) : degree received
major varchar(20) : major study area
year char(4) : year
school varchar(50) : school that a degree is received
research : research interests of the faculties
fssn char(9) : faculty social security number
researcharea varchar(255) : faculty research area
description varchar(255) : description on research area. not filled.
Using the above table information and the corresponding ER Diagram, create a DB2 database with the tables described above. Handle in soft and hardcopy of the SQL DDL statements as well as all the DML statements that propulate sample data.
Develop SQL statements to implement the following queries.
Verify your queries. There are no partial credits.
1. List all the courses (course number and title) offered by computer science department.
2. List all course sections taught by Professor Sinn.
3. Give the final examination date and time for CSci 160 section 1.
4. List all the prerequisite course(s) for CSci 135.
5. List all the course sections of CSci 590.
6. List all computer science course(s) which is taught by the professor who teach more than one section of a course.
7. List all the courses which have at least one common prerequisite course(s).
8. List all the course sections which have the same final examination date and time.
9. List all the name(s) of the faculty who teaches at least one course.
10. List all the name(s) of faculty who teach exactly two courses. (Note that if a faculty teaches two sections of the same course, count it as one course.)
11. List all the faculty member's name(s) who do not teach.
12. List all the name(s) of the faculty who work for computer science department.
13. List all the name(s) of the faculty who teach at least one course located in the building whose department is not located.
14. List all the faculty name(s) who teach at least one course section and total count of course section(s) teaching.
15. List all the faculty name(s) who teach a course and total count of course(s) teaching. Exclude a course(s) with more than one section which is taught by the same instructor from counting.
16. List the name of the faculty who teaches every course in computer science department.
17. List all the name(s) of the faculty who has no publication.
18. List all the papers published (authored or co-authored) by Professor Sinn.
19. List all the name(s) of the faculty who co-author for at least one paper
20. List all the name(s) of the faculty who co-author for at least one paper and taught for at least one common course (different section).
Handle in both soft and hardcopy of SQL queries with sample output. Also, resubmit part a.
